<mljv@xxxxxxxxxxxx> writes: > Could it be that something like this is happening: > - In the early morning a new DB connection is opened. > - While running VACUUM ANALYZE the planner uses different plans because some > index could not be used or the statistics are right in that moment not > present because they are updated... So the query gets a wrong plan. It uses a > seqScan instead of an index scan. This isn't supposed to happen. The old statistics are still visible until the new ones are visible. Nothing is locked or unavailable while analyze is running. However one of the things that can happen is the statistics for one table get updated and until the statistics for another table are updated the planner has skewed results. It has the new statistics for the first table but old statistics for the second table. Normally the solution to this is to run analyze more frequently so things don't change too drastically from one set of statistics to another. In some situations this isn't good enough, for example if you're truncating a bunch of tables together. You can also run analyze (but not vacuum analyze) inside a transaction so all the stats go into effect together when you commit. This does have downsides however. I think it will block a vacuum analyze which tries to update the same statistics, for example. Another thing to be aware of is that *empty* tables cause Postgres to default to a heuristic of assuming 1,000 records. That can cause strange things to happen if you truncate just some tables and run analyze on them. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!
