Re: slow queries on system tables

On 8/23/19 2:47 PM, PegoraroF10 wrote:
> Coming back to my problem, today happened again, that search comes slow.
> Explaining again, auto vacuum and auto analyse for pg_* tables are not
> configured one by one, so its using that defaults of scale_factor = 0.2
> and threshold = 50. Today, when that problem of speed came back, the
> view pg_stat_sys_tables for pg_class table shows that it ran 415 times
> autovacuum only this week and there are 75187 updates on this table. How
> can I have been updated 75000 times this table only in a week ? What

If you are still doing this:

https://www.postgresql.org/message-id/1563909633976-0.post%40n3.nabble.com

then it is not surprising.

> kind of statement does an update on this table ? And why did it run 415
> times autovacuum and only 1 autoanalyse ?

Can you show the actual settings in postgresql.conf for:

autovacuum_analyze_threshold

autovacuum_analyze_scale_factor

Also do you have an include in postgresql.conf pointing to another file?

I know you said they are using the defaults but the scale_factor you
show above is the default for autovacuum_vacuum_scale_factor. For
autovacuum_analyze_scale_factor the default is 0.1, which makes wonder.

> 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
> pg_class 5395512 38598962913 616674711 2219978581 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
>
>
> ------------------------------------------------------------------------
> Sent from the PostgreSQL - general mailing list archive
> <https://www.postgresql-archive.org/PostgreSQL-general-f1843780.html> at
> Nabble.com.

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com