Re: [GENERAL] 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.klaver@aklaver.com