Autoanalyze oddity

From: "Peter J(dot) Holzer" <hjp-pgsql(at)hjp(dot)at>
To: pgsql-general(at)postgresql(dot)org
Subject: Autoanalyze oddity
Date: 2017-03-03 08:33:52
Message-ID: 20170303083352.GA8216@hjp.at
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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(at)hjp(dot)at | -- John Gordon
__/ | http://www.hjp.at/ | http://downlode.org/Etext/alicebob.html

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Achilleas Mantzios 2017-03-03 09:24:11 Re: PG on SSD
Previous Message Alexander Farber 2017-03-03 07:33:13 Re: CentOS 7.3, PostgreSQL 9.6.2, PHP 5.4 deliver array as string