Re: slow queries on system tables

these values were collected before vacuum analyse:select (select reltuples
from pg_Class where relName = t.relName), * from pg_stat_sys_tables t where
schemaname = 'pg_catalog' and relname in
('pg_class','pg_attribute','pg_index');
reltuples relid schemaname relname seq_scan seq_tup_read idx_scan
idx_tup_fetch n_tup_ins n_tup_upd n_tup_del n_tup_hot_upd n_live_tup
n_dead_tup n_mod_since_analyze last_vacuum last_autovacuum last_analyze
last_autoanalyze vacuum_count autovacuum_count analyze_count
autoanalyze_count
930475 1249 pg_catalog pg_attribute 22462 61582178 646833988 2785143399
25381 776 15287 566 10094 15497 41444 NULL NULL NULL NULL 0 0 0 0
169186 1259 pg_catalog pg_class 5541140 39631813757 638041068 2316793709
2243 75187 625 44008 169891 5645 7435 NULL 2019-08-22 19:58:48.497317 NULL
2019-08-22 13:03:02.770579 0 415 0 1
87387 2610 pg_catalog pg_index 5263825 33638625440 246528384 590160274 1142
0 208 0 934 208 1350 NULL NULL NULL NULL 0 0 0 0
Then ... vacuum analyze pg_catalog.pg_index;vacuum analyze
pg_catalog.pg_attribute;vacuum analyze pg_catalog.pg_class;
reltuples relid schemaname relname seq_scan seq_tup_read idx_scan
idx_tup_fetch n_tup_ins n_tup_upd n_tup_del n_tup_hot_upd n_live_tup
n_dead_tup n_mod_since_analyze last_vacuum last_autovacuum last_analyze
last_autoanalyze vacuum_count autovacuum_count analyze_count
autoanalyze_count
940569 1249 pg_catalog pg_attribute 22550 61582570 646919000 2785430403
25381 776 15287 566 940569 0 0 2019-08-24 09:01:26.334472 NULL 2019-08-24
09:01:27.169883 NULL 1 0 1 0
150903 1259 pg_catalog pg_class 5542002 39640547448 638133339 2316941739
2243 75187 625 44008 150903 0 0 2019-08-24 09:01:33.130845 2019-08-22
19:58:48.497317 2019-08-24 09:01:33.441355 2019-08-22 13:03:02.770579 1 415
1 1
88321 2610 pg_catalog pg_index 5263994 33639781274 246576056 590229532 1142
0 208 0 88321 0 0 2019-08-24 09:01:18.080615 NULL 2019-08-24 09:01:18.275907
NULL 1 0 1 0
and this is explain before
https://explain.depesz.com/s/fUNB
and after
https://explain.depesz.com/s/1FZR

--
Sent from: https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html