Re: Problem after VACUUM ANALYZE

On Wed, Apr 9, 2008 at 3:29 AM, <mljv(at)planwerk6(dot)de> wrote:

> 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.
>

I've run into a very similar problem. I have some long-running
processes that generate a large amount of data and then query that
data that must periodically drop their connections and rebuild to
ensure that query plans get re-planned according to updated
statistics. This is especially true when a new DB is first being
brought up with an initial set of data (~1 week of compute time, ~30gb
of data and ~120m rows) with processes that live through the entire
process.

My solution, as mentioned above, is to rebuild the connection approx
every hour on my long-running processes. This is a tricky question,
because you want the benefit of not re-planning queries in 99.9% of
the cases- I'm not really opposed to the current system that requires
the reconnections, given that the overhead involved in them is
completely negligible in the grand scheme of things. There are
downsides to not replanning queries, and if you can't live with them
then you should either force re-planning at intervals or avoid
prepared statements.

--
- David T. Wilson
Princeton Satellite Systems
david(dot)t(dot)wilson(at)gmail(dot)com