Re: [GENERAL] Is auto-analyze as thorough as manual analyze? - Mailing list pgsql-general
On Sat, Sep 23, 2017 at 6:10 PM, Jack Christensen <jack@jackchristensen.com> wrote:
Just had an issue where a prepared query would occasionally choose a very bad plan in production. The same data set in a different environment consistently would choose the index scan. As would be expected, running analyze on that table in production resolved the issue.
However, before I ran the analyze I checked pg_stat_user_tables to see last_autoanalyze for that table. It had run today. But the problem existed before that. I would have expected that the auto-analyze would have corrected this (or prevented it entirely if run enough).
So that leaves me wondering: is an auto-analyze the same as manually running analyze or is a manual analyze more thorough? This is running version 9.6.3 on Heroku.
Thanks,
Jack
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
>is an auto-analyze the same as manually running analyze or is a manual analyze more thorough?
It's not that one is "more thorough" than the other, it's that autovacuum_analyze will only kick in when it meets
one of the following conditions:
autovacuum_analyze_scale_factor 0.1 #Number of tuple inserts, updates, or deletes prior to analyze as a fraction of reltuples.
autovacuum_analyze_threshold 50 #Minimum number of tuple inserts, updates, or deletes prior to analyze.
Note: You can adjust the settings for individual tables.
EG:
ALTER TABLE some_schema.your_table SET (autovacuum_vacuum_scale_factor = 0.5); ALTER TABLE some_schema.your_table SET (autovacuum_vacuum_threshold = 1000);
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: