Re: slow "select count(*) from information_schema.tables;" in some cases - Mailing list pgsql-performance

>Vijaykumar Jain <vijaykumarjain.github@gmail.com>

>Mon 2/7/2022 6:49 PM

>

>On Mon, Feb 7, 2022, 10:26 PM Lars Aksel Opsahl <Lars.Opsahl@nibio.no> wrote:

>Hi

>

Hi

>Can you share the output of the below query?

>

>From the past threads I have learnt that too many templates objects may add to bloat of system catalogs and may in start resulting in impacting performance.

>Make a note especially  around

>

>pg_attribute

>pg_depends

>and check for bloat, if required, vacuum full? these objects to speed up.

>

>

>

>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

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;

            relname             | pg_size_pretty 

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

 pg_attrdef_oid_index           | 9744 kB

 pg_attrdef_adrelid_adnum_index | 9712 kB

 pg_type_typname_nsp_index      | 87 MB

 pg_sequence_seqrelid_index     | 8224 kB

 pg_foreign_table_relid_index   | 8192 bytes

 pg_enum_typid_sortorder_index  | 8192 bytes

 pg_largeobject_metadata        | 8192 bytes

 pg_event_trigger_oid_index     | 8192 bytes

 pg_extension                   | 8192 bytes

 pg_event_trigger_evtname_index | 8192 bytes

 pg_am                          | 8192 bytes

 pg_foreign_data_wrapper        | 8192 bytes

 pg_foreign_server_name_index   | 8192 bytes

 pg_enum_typid_label_index      | 8192 bytes

 pg_default_acl                 | 8192 bytes

 pg_foreign_server_oid_index    | 8192 bytes

 pg_db_role_setting             | 8192 bytes

 pg_database                    | 8192 bytes

 pg_enum_oid_index              | 8192 bytes

 pg_language                    | 8192 bytes

(20 rows)

Time: 22.354 ms

VACUUM full pg_attribute;

40P01: deadlock detected

VACUUM full pg_depends;

40P01: deadlock detected

I have to test those later

This works ok

VACUUM pg_attribute;

VACUUM pg_depends;

VACUUM full pg_attrdef;

VACUUM full pg_type ;

VACUUM full pg_sequence;

VACUUM full pg_type;

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;

               relname                | pg_size_pretty 

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

 pg_type_oid_index                    | 960 kB

 pg_language                          | 8192 bytes

 pg_enum_typid_label_index            | 8192 bytes

 pg_pltemplate                        | 8192 bytes

 pg_event_trigger_oid_index           | 8192 bytes

 pg_foreign_server_oid_index          | 8192 bytes

 pg_foreign_server_name_index         | 8192 bytes

 pg_enum_oid_index                    | 8192 bytes

 pg_largeobject_metadata              | 8192 bytes

 pg_foreign_table_relid_index         | 8192 bytes

 pg_am                                | 8192 bytes

 pg_database                          | 8192 bytes

 pg_event_trigger_evtname_index       | 8192 bytes

 pg_extension                         | 8192 bytes

 pg_partitioned_table_partrelid_index | 8192 bytes

 pg_enum_typid_sortorder_index        | 8192 bytes

 pg_db_role_setting                   | 8192 bytes

 pg_default_acl                       | 8192 bytes

 pg_foreign_data_wrapper              | 8192 bytes

 pg_publication_oid_index             | 8192 bytes

Still slow.

Lars