X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 75F873A45D2 for ; Wed, 3 Nov 2004 19:20:39 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 59399-04 for ; Wed, 3 Nov 2004 19:20:36 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id D2B7B3A4583 for ; Wed, 3 Nov 2004 19:20:37 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1CPQgL-0008FO-00; Wed, 03 Nov 2004 14:20:33 -0500 To: patrick ~ Cc: pgsql-sql@postgresql.org Subject: Re: vacuum analyze slows sql query References: <20041103025031.43795.qmail@web52103.mail.yahoo.com> In-Reply-To: <20041103025031.43795.qmail@web52103.mail.yahoo.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 03 Nov 2004 14:20:32 -0500 Message-ID: <87breepwn3.fsf@stark.xeocode.com> Lines: 20 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200411/32 X-Sequence-Number: 19351 patrick ~ 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