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

>Vijaykumar Jain <vijaykumarjain(dot)github(at)gmail(dot)com>

>Mon 2/7/2022 6:49 PM

>

>On Mon, Feb 7, 2022, 10:26 PM Lars Aksel Opsahl <Lars(dot)Opsahl(at)nibio(dot)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