From: Greg Stark <gsstark@mit.edu>
To: patrick ~ <sidsrr@yahoo.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: vacuum analyze slows sql query
Date: 03 Nov 2004 14:20:32 -0500
Message-ID: <87breepwn3.fsf@stark.xeocode.com> (raw)
In-Reply-To: <20041103025031.43795.qmail@web52103.mail.yahoo.com>
References: <20041103025031.43795.qmail@web52103.mail.yahoo.com>
patrick ~ <sidsrr@yahoo.com> writes:
> I noticed that a freshly created db with freshly inserted data (from
> a previous pg_dump) would result in quite fast results. However,
> after running 'vacuum analyze' the very same query slowed down about
> 1250x (Time: 1080688.921 ms vs Time: 864.522 ms).
If it gets slower immediately after a single vacuum analyze then the problem
is that one of the queries is getting planned wrong when stats are available.
If it takes a while to slow down then it could be other problems such as index
bloat etc.
Don't use "explain verbose" use "explain analyze". I don't know if it was in
7.1 but you say you reproduced the problem with 7.4. It would be helpful to
see the results of "explain analyze select ..." on the query before and after
the vacuum analyze.
--
greg
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gsstark@mit.edu, sidsrr@yahoo.com
Subject: Re: vacuum analyze slows sql query
In-Reply-To: <87breepwn3.fsf@stark.xeocode.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox