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 7BAEF9DCB0B
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 20 Jan 2006 12:19:39 -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 00821-04
	for <pgsql-performance-postgresql.org@localhost.postgresql.org>;
	Fri, 20 Jan 2006 12:19:37 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from candle.pha.pa.us (candle.pha.pa.us [70.90.9.53])
	by postgresql.org (Postfix) with ESMTP id 394E69DC804
	for <pgsql-performance@postgresql.org>;
	Fri, 20 Jan 2006 12:19:35 -0400 (AST)
Received: (from pgman@localhost)
	by candle.pha.pa.us (8.11.6/8.11.6) id k0KGJWE19919;
	Fri, 20 Jan 2006 11:19:32 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200601201619.k0KGJWE19919@candle.pha.pa.us>
Subject: Re: Extremely irregular query performance
In-Reply-To: <1137059321.3180.34.camel@localhost.localdomain>
To: Simon Riggs <simon@2ndquadrant.com>
Date: Fri, 20 Jan 2006 11:19:32 -0500 (EST)
CC: Tom Lane <tgl@sss.pgh.pa.us>, =?ISO-8859-1?Q?Jean-Philippe_C=F4t=E9?=
	<jean-philippe.cote@crt.umontreal.ca>, pgsql-performance@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL121 (25)]
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary=ELM1137773972-28690-2_
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=0.123 required=5 tests=[AWL=0.123]
X-Spam-Score: 0.123
X-Spam-Level: 
X-Archive-Number: 200601/340
X-Sequence-Number: 16818


--ELM1137773972-28690-2_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain; charset=US-ASCII

Simon Riggs wrote:
> On Wed, 2006-01-11 at 22:23 -0500, Tom Lane wrote:
> > =?iso-8859-1?Q?Jean-Philippe_C=F4t=E9?= <jean-philippe.cote@crt.umontreal.ca> 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.

I thought the best solution would be to replace "QUERY PLAN" with "GEQO
QUERY PLAN" when GEQO was in use.  However, looking at the code, I see
no way to do that cleanly.

Instead, I added documentation to EXPLAIN to highlight the fact the
execution plan will change when GEQO is in use.

(I also removed a documentation mention of the pre-7.3 EXPLAIN output
behavior.)

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

--ELM1137773972-28690-2_
Content-Transfer-Encoding: 7bit
Content-Type: text/plain
Content-Disposition: inline; filename="/bjm/diff"

Index: doc/src/sgml/ref/explain.sgml
===================================================================
RCS file: /cvsroot/pgsql/doc/src/sgml/ref/explain.sgml,v
retrieving revision 1.35
diff -c -c -r1.35 explain.sgml
*** doc/src/sgml/ref/explain.sgml	4 Jan 2005 00:39:53 -0000	1.35
--- doc/src/sgml/ref/explain.sgml	20 Jan 2006 16:18:53 -0000
***************
*** 151,161 ****
    </para>
  
    <para>
!    Prior to <productname>PostgreSQL</productname> 7.3, the plan was
!    emitted in the form of a <literal>NOTICE</literal> message.  Now it
!    appears as a query result (formatted like a table with a single
!    text column).
    </para>
   </refsect1>
  
   <refsect1>
--- 151,162 ----
    </para>
  
    <para>
!    Genetic query optimization (<acronym>GEQO</acronym>) randomly 
!    tests execution plans.  Therefore, when the number of tables 
!    exceeds <varname>geqo</> and genetic query optimization is in use,
!    the execution plan will change each time the statement is executed.
    </para>
+ 
   </refsect1>
  
   <refsect1>

--ELM1137773972-28690-2_--