|
>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
|
