Search Postgresql Archives

Re: Is auto-analyze as thorough as manual analyze?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Sat, Sep 23, 2017 at 6:10 PM, Jack Christensen <jack@xxxxxxxxxxxxxxxxxxx> 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@xxxxxxxxxxxxxx)
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.


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux