"Tom Lane" <[EMAIL PROTECTED]> writes: > Alvaro Herrera <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
