X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 6D7CFD7E0D for ; Fri, 26 Aug 2005 22:41:30 -0300 (ADT) 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 53392-02 for ; Sat, 27 Aug 2005 01:41:28 +0000 (GMT) Received: from tigger.fuhr.org (tigger.fuhr.org [63.214.45.158]) by svr1.postgresql.org (Postfix) with ESMTP id 61445D7E0F for ; Fri, 26 Aug 2005 22:41:26 -0300 (ADT) Received: from winnie.fuhr.org (winnie.fuhr.org [10.1.0.1]) by tigger.fuhr.org (8.13.3/8.13.3) with ESMTP id j7R1fRE1019691 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK); Fri, 26 Aug 2005 19:41:29 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: from winnie.fuhr.org (localhost [127.0.0.1]) by winnie.fuhr.org (8.13.3/8.13.3) with ESMTP id j7R1fRG4033726; Fri, 26 Aug 2005 19:41:27 -0600 (MDT) (envelope-from mfuhr@winnie.fuhr.org) Received: (from mfuhr@localhost) by winnie.fuhr.org (8.13.3/8.13.3/Submit) id j7R1fQEn033725; Fri, 26 Aug 2005 19:41:26 -0600 (MDT) (envelope-from mfuhr) Date: Fri, 26 Aug 2005 19:41:26 -0600 From: Michael Fuhr To: asif ali Cc: pgsql-performance@postgresql.org Subject: Re: Weird performance drop after VACUUM Message-ID: <20050827014126.GA33637@winnie.fuhr.org> References: <20050826232641.GA19583@winnie.fuhr.org> <20050827001049.58959.qmail@web35210.mail.mud.yahoo.com> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <20050827001049.58959.qmail@web35210.mail.mud.yahoo.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.005 required=5 tests=[AWL=0.005] X-Spam-Level: X-Archive-Number: 200508/465 X-Sequence-Number: 14212 On Fri, Aug 26, 2005 at 05:10:49PM -0700, asif ali wrote: > "GroupAggregate (cost=195623.66..206672.52 rows=20132 > width=16) (actual time=8205.283..10139.369 rows=55291 > loops=1)" > " -> Sort (cost=195623.66..198360.71 rows=1094820 > width=16) (actual time=8205.114..9029.501 rows=863883 > loops=1)" > " Sort Key: keyword_id" > " -> Seq Scan on keyword_conversion_table c > (cost=0.00..29990.83 rows=1094820 width=16) (actual > time=0.057..1422.319 rows=863883 loops=1)" > " Filter: ((conversion_date >= > '2005-06-07'::date) AND (conversion_date <= > '2005-08-17'::date))" > "Total runtime: 14683.617 ms" What are your effective_cache_size and work_mem (8.x) or sort_mem (7.x) settings? How much RAM does the machine have? If you have enough memory then raising those variables should result in better plans; you might also want to experiment with random_page_cost. Be careful not to set work_mem/sort_mem too high, though. See "Run-time Configuration" in the "Server Run-time Environment" chapter of the documentation for more information about these variables. -- Michael Fuhr