Re: strange buildfarm failure on lionfish

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> Gregory Stark wrote:
>>> What really has to happen is it should run analyze on all tables
>>> together in a single transaction and commit all the new stats together.
>>> Out-of-sync stats can be worse than out-of-date stats.
>
>> One problem with that is that it will keep the locks on each table until
>> the end of all analyzes.
>
> Yeah, that seems entirely infeasible, even if I agreed with the premise
> which I don't think I do.

Well that's just what ANALYZE with no arguments at all does. It's also only a
ShareUpdateExclusiveLock which prevents other vacuums and DDL but not any
other DML. And ANALYZE goes by pretty quickly even on large tables.

Another idea is that perhaps it should only do this for all never-analyzed
tables together. That's where the out-of-sync stats is most likely to hurt.
But I'm not sure where to go with that since there's no guarantee that all the
never-analyzed tables will be the small ones.

Really it seems like having autovacuum touch never-analyzed tables and having
"reasonable default stats" for never-analyzed tables don't mix well together.

The "reasonable default stats" are there because if you analyze a fresh empty
table you'll get some degenerate plans which will behave terribly when you
start loading even a few records into it. The reasonable default stats give
you something akin to a rule-based plan until you have some reasonable data
loaded to analyze.

In a perfect world I would say autovacuum shouldn't analyze never-analyzed
tables, just print a warning for the DBA. But we get questions all the time
about bad plans that show tables which have never been analyzed so that
doesn't sound palatable either.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com