Re: slow queries on system tables

[PegoraroF10 <marcos@xxxxxxxxxx>]

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 the PostgreSQL - general mailing list archive at Nabble.com.