Re: slow "select count(*) from information_schema.tables;" in some cases

>From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>

>Sent: Monday, February 7, 2022 8:02 PM

>To: Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no>

>Cc: Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com>; Pgsql Performance <pgsql-performance(at)lists(dot)postgresql(dot)org>

>Subject: Re: slow "select count(*) from information_schema.tables;" in some cases

>

>Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)no> writes:

>>> SELECT relname, pg_size_pretty(pg_relation_size(C.oid)) FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname = 'pg_catalog' ORDER BY 2 DESC LIMIT 20; can you show the output of this query

>

>"ORDER BY 2" is giving you a textual sort of the sizes, which is entirely

>unhelpful. Try

>

>SELECT relname, pg_size_pretty(pg_relation_size(C.oid)) FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname = 'pg_catalog' ORDER BY pg_relation_size(C.oid) DESC LIMIT 20;

>

> regards, tom lane

>

Hi

Then pg_attribute show up yes. I have to vacuum full later when server is free.

SELECT relname, pg_size_pretty(pg_relation_size(C.oid)) FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname = 'pg_catalog' ORDER BY pg_relation_size(C.oid) DESC LIMIT 20;

relname | pg_size_pretty

-----------------------------------+----------------

pg_largeobject | 17 GB

pg_attribute | 1452 MB

pg_statistic | 1103 MB

pg_class | 364 MB

pg_attribute_relid_attnam_index | 307 MB

pg_depend | 285 MB

pg_largeobject_loid_pn_index | 279 MB

pg_attribute_relid_attnum_index | 230 MB

pg_depend_reference_index | 207 MB

pg_depend_depender_index | 198 MB

pg_class_relname_nsp_index | 133 MB

pg_index | 111 MB

pg_statistic_relid_att_inh_index | 101 MB

pg_class_oid_index | 52 MB

pg_class_tblspc_relfilenode_index | 46 MB

pg_shdepend | 38 MB

pg_shdepend_depender_index | 25 MB

pg_index_indexrelid_index | 24 MB

pg_shdepend_reference_index | 21 MB

pg_index_indrelid_index | 18 MB

(20 rows)

Thanks