Am Mittwoch, 9. April 2008 10:11 schrieb David Wilson: > On Wed, Apr 9, 2008 at 3:29 AM, <mljv@xxxxxxxxxxxx> 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. This is not the case at my place. We are not producing lots of data. Just few data with small INSERTs all the time. I don't think that the statistic are really changing quite often as the tables are rather fixed and just some data is added. > 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. In release 8.3: http://www.postgresql.org/docs/8.3/interactive/release-8-3.html "Automatically re-plan cached queries when table definitions change or statistics are updated" So no reconnection is necessary in 8.3 to replan prepared statements. Maybe it helps you. kind regards, janning
