On 03/03/2017 12:33 AM, Peter J. Holzer wrote:
> 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)
What are your settings for autovacuum?:
https://www.postgresql.org/docs/9.5/static/runtime-config-autovacuum.html
Have the storage parameters for the table been altered?:
https://www.postgresql.org/docs/9.5/static/sql-createtable.html#SQL-CREATETABLE-STORAGE-PARAMETERS
>
> 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?
That value is added to autovacuum_analyze_threshold:
autovacuum_analyze_scale_factor (floating point)
Specifies a fraction of the table size to add to
autovacuum_analyze_threshold when deciding whether to trigger an
ANALYZE. The default is 0.1 (10% of table size). This parameter can only
be set in the postgresql.conf file or on the server command line; but
the setting can be overridden for individual tables by changing table
storage parameters.
>
> 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
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com