Re: Problem after VACUUM ANALYZE

Am Dienstag, 8. April 2008 18:38 schrieb Scott Marlowe:
> It sounds to me like two possible problems, maybe combined.
>
> One possibility is that you have a data distribution that results in
> statistics being gathered that don't really represent your data. Try
> increasing the stats target for that column (or the whole db if you'd
> rather) and re-running analyze.
>
> The other possibility is that you've got some index bloat happening
> and you might benefit from reindexing the problematic table.

But if i do "VACUUM ANALYZE" without concurrent queries, everything runs fine
afterwards.

If i run "VACUUM ANALYZE" with few concurrent queries, it slows down to a
crawl.

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 wrongly planned statement is prepared so even after VACUUM ANALYZE is
done, the statement does not use the wrong plan.
- load raises triggers many concurrent queries with wrong plans. so everything
slows down.

kind regards,
janning