<mljv@planwerk6.de> 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!
