This is with PostgreSQL 9.5.6 on Debian Linux.
I noticed that according to pg_stat_user_tables autoanalyze has never
run on a lot of tables. Here is one example:
wdsah=> select * from pg_stat_user_tables where schemaname='public' and relname='facttable_wds_indexstats';
─[ RECORD 1 ]───────┬─────────────────────────
relid │ 112723
schemaname │ public
relname │ facttable_wds_indexstats
seq_scan │ 569
seq_tup_read │ 474779212
idx_scan │ 59184
idx_tup_fetch │ 59184
n_tup_ins │ 47128
n_tup_upd │ 0
n_tup_del │ 0
n_tup_hot_upd │ 0
n_live_tup │ 47128
n_dead_tup │ 0
n_mod_since_analyze │ 47128
last_vacuum │ (∅)
last_autovacuum │ (∅)
last_analyze │ (∅)
last_autoanalyze │ (∅)
vacuum_count │ 0
autovacuum_count │ 0
analyze_count │ 0
autoanalyze_count │ 0
wdsah=> select count(*) from facttable_wds_indexstats;
count
────────
857992
(1 row)
So, n_live_tup is way off, and n_tup_ins and n_mod_since_analyze also
seem to be wrong. Looks like this hasn't been updated in a year or so.
But track_counts is on:
wdsah=> show track_counts;
track_counts
──────────────
on
(1 row)
And even if it wasn't, shouldn't the autovacuum daemon notice that
n_mod_since_analyze is greater than n_live_tup *
autovacuum_analyze_scale_factor and run an autoanalyze?
But the really weird thing is that pg_stats seems to be reasonably
current: I see entries in most_common_vals which were only inserted in
January. Is it possible that autoanalyze runs without updating
pg_stat_user_tables?
hp
--
_ | Peter J. Holzer | A coding theorist is someone who doesn't
|_|_) | | think Alice is crazy.
| | | hjp@xxxxxx | -- John Gordon
__/ | http://www.hjp.at/ | http://downlode.org/Etext/alicebob.html
Attachment:
signature.asc
Description: Digital signature
