Routine analyze of single column prevents standard autoanalyze from running at all

From: Tomasz Ostrowski <tometzky+pg(at)ato(dot)waw(dot)pl>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Routine analyze of single column prevents standard autoanalyze from running at all
Date: 2016-06-06 14:25:29
Message-ID: ef99c1bd-ff60-5f32-2733-c7b504eb960c@ato.waw.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Hi.

I'm routinely bulk inserting data to a PostgreSQL table and then
analyzing a single column of the table, because it contains data which
significantly changes histogram of this column values - for example
something like adding rows with "todo=true" column, when all rows before
bulk insert have "todo=false".

This column has rather small "statistics" value, so analyze of it is
fairly fast, which is important as I'm doing it often and also in
parallel (and analyze blocks - only one can run at the time). The full
analyze of this large table would take a lot of time (20 times more
actually), and I can't perform it after each bulk insert.

But I've noticed that a standard automatic analyze, which should work in
background, never runs. I've noticed that this fast analyze of one
column resets pg_stat_user_tables(n_mod_since_analyze) counter.

I suppose that the decision to analyze the whole table is based on these
values from pg_stat_user_tables and autovacuum_analyze_threshold and
autovacuum_analyze_scale_factor settings. And in this case this highly
updated table never reaches these values.

I suppose this is a bug - an analyze, which does not analyze all
columns, should not reset pg_stat_user_tables(n_mod_since_analyze). What
do you think?

--
Tomasz "Tometzky" Ostrowski

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Amit Langote 2016-06-06 14:44:05 Re: BUG #14177: ARRAYs in VIEWs are inconsistently cast
Previous Message Peter Geoghegan 2016-06-05 18:51:40 Re: BUG #14134: segmentation fault with large table with gist index

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2016-06-06 14:27:14 Re: installcheck failing on psql_crosstab
Previous Message Alvaro Herrera 2016-06-06 14:21:33 Re: COMMENT ON, psql and access methods