| 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: | Whole Thread | Raw Message | 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
| 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 |
| 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 |