Re: Autoanalyze oddity

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(dot)klaver(at)aklaver(dot)com