Re: [GENERAL] Is auto-analyze as thorough as manual analyze?

Поиск
Список
Период
Сортировка
От Melvin Davidson
Тема Re: [GENERAL] Is auto-analyze as thorough as manual analyze?
Дата
Msg-id CANu8FizTEsw6QmX5m_gomYJ2AZGLqwrMMbe4Smd8R2LOGte+5g@mail.gmail.com
обсуждение исходный текст
Ответ на [GENERAL] Is auto-analyze as thorough as manual analyze?  (Jack Christensen <jack@jackchristensen.com>)
Список 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.

https://www.postgresql.org/docs/9.6/static/runtime-config-autovacuum.html

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.

В списке pgsql-general по дате отправления:

Предыдущее
От: Jack Christensen
Дата:
Сообщение: [GENERAL] Is auto-analyze as thorough as manual analyze?
Следующее
От: "Frank Millman"
Дата:
Сообщение: Re: [GENERAL] a JOIN to a VIEW seems slow