X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 7A5559DC829 for ; Thu, 12 Jan 2006 15:16:21 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 15514-01 for ; Thu, 12 Jan 2006 15:16:21 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from is.rice.edu (is.rice.edu [128.42.42.24]) by postgresql.org (Postfix) with ESMTP id 3331B9DC847 for ; Thu, 12 Jan 2006 15:16:19 -0400 (AST) Received: from scan2.mail.rice.edu (scan2.mail.rice.edu [128.42.59.161]) by is.rice.edu (Postfix) with ESMTP id E09F241878; Thu, 12 Jan 2006 13:16:19 -0600 (CST) Received: from is.rice.edu ([128.42.42.24]) by scan2.mail.rice.edu (scan2.mail.rice.edu [128.42.59.161]) (amavisd-new, port 10024) with ESMTP id 25072-03; Thu, 12 Jan 2006 13:16:19 -0600 (CST) Received: by is.rice.edu (Postfix, from userid 18612) id 2B079418DC; Thu, 12 Jan 2006 13:16:19 -0600 (CST) Date: Thu, 12 Jan 2006 13:16:18 -0600 From: Kenneth Marshall To: Simon Riggs Cc: Tom Lane , Jean-Philippe C?t? , pgsql-performance@postgresql.org Subject: Re: Extremely irregular query performance Message-ID: <20060112191618.GB81@it.is.rice.edu> References: <20060111234149.05DEA384099@crt0.crt.umontreal.ca> <480.1137036235@sss.pgh.pa.us> <1137059321.3180.34.camel@localhost.localdomain> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <1137059321.3180.34.camel@localhost.localdomain> User-Agent: Mutt/1.4.2i X-Virus-Scanned: by amavis-2.2.1 at scan2.mail.rice.edu X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.03 required=5 tests=[AWL=0.030] X-Spam-Score: 0.03 X-Spam-Level: X-Archive-Number: 200601/181 X-Sequence-Number: 16659 On Thu, Jan 12, 2006 at 09:48:41AM +0000, Simon Riggs wrote: > On Wed, 2006-01-11 at 22:23 -0500, Tom Lane wrote: > > =?iso-8859-1?Q?Jean-Philippe_C=F4t=E9?= writes: > > > Thanks a lot for this info, I was indeed exceeding the genetic > > > optimizer's threshold. Now that it is turned off, I get > > > a very stable response time of 435ms (more or less 5ms) for > > > the same query. It is about three times slower than the best > > > I got with the genetic optimizer on, but the overall average > > > is much lower. > > > > Hmm. It would be interesting to use EXPLAIN ANALYZE to confirm that the > > plan found this way is the same as the best plan found by GEQO, and > > the extra couple hundred msec is the price you pay for the exhaustive > > plan search. If GEQO is managing to find a plan better than the regular > > planner then we need to look into why ... > > It seems worth noting in the EXPLAIN whether GEQO has been used to find > the plan, possibly along with other factors influencing the plan such as > enable_* settings. > Is it the plan that is different in the fastest case with GEQO or is it the time needed to plan that is causing the GEQO to beat the exhaustive search? Ken