>From: Tom Lane <tgl@sss.pgh.pa.us>
>Sent: Monday, February 7, 2022 8:02 PM
>To: Lars Aksel Opsahl <Lars.Opsahl@nibio.no>
>Cc: Vijaykumar Jain <vijaykumarjain.github@gmail.com>; Pgsql Performance <pgsql-performance@lists.postgresql.org>
>Subject: Re: slow "select count(*) from information_schema.tables;" in some cases
>
>Lars Aksel Opsahl <Lars.Opsahl@nibio.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
