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 7852A9DC995
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 12 Jan 2006 17:05:56 -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 12010-04
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Thu, 12 Jan 2006 17:05:57 -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 056E49DC81A
	for <pgsql-performance@postgresql.org>;
	Thu, 12 Jan 2006 17:05:53 -0400 (AST)
Received: from scan2.mail.rice.edu (scan2.mail.rice.edu [128.42.59.161])
	by is.rice.edu (Postfix) with ESMTP id 0ABD3418B0;
	Thu, 12 Jan 2006 15:05:55 -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 26620-06; Thu, 12 Jan 2006 15:05:54 -0600 (CST)
Received: by is.rice.edu (Postfix, from userid 18612)
	id 555D741878; Thu, 12 Jan 2006 15:05:50 -0600 (CST)
Date: Thu, 12 Jan 2006 15:05:50 -0600
From: Kenneth Marshall <ktm@it.is.rice.edu>
To: Jean-Philippe Cote <jean-philippe.cote@crt.umontreal.ca>
Cc: 'Simon Riggs' <simon@2ndquadrant.com>, pgsql-performance@postgresql.org
Subject: Re: Extremely irregular query performance
Message-ID: <20060112210550.GI81@it.is.rice.edu>
References: <20060112191618.GB81@it.is.rice.edu>
	<20060112201353.5C39138409A@crt0.crt.umontreal.ca>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <20060112201353.5C39138409A@crt0.crt.umontreal.ca>
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.027 required=5 tests=[AWL=0.027]
X-Spam-Score: 0.027
X-Spam-Level: 
X-Archive-Number: 200601/182
X-Sequence-Number: 16660

On Thu, Jan 12, 2006 at 03:23:14PM -0500, Jean-Philippe Cote wrote:
> 
> 
> Can I actully know whether a given plan is excuted with GEQO on ?
> In other words, if I launch 'explain <query>', I'll get a given plan, but if I re-launch
> the <query> (withtout the 'explain' keyword), could I get a different
> plan given that GEQO induces some randomness ?
> 
> >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?
> 
GEQO will be used if the number of joins is over the GEQO limit in
the configuration file. The GEQO process is an iterative random
process to find an query plan. The EXPLAIN results are the plan for that
query, but not neccessarily for subsequent runs. GEQO's advantage is a
much faster plan time than the exhaustive search method normally used.
If the resulting plan time is less than the exhaustive search plan time,
for short queries you can have the GECO run more quickly than the
exhaustive search result. Of course, if you PREPARE the query the plan
time drops out.

Ken