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