From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Simon Riggs <simon@2ndquadrant.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Jean-Philippe Côté <jean-philippe.cote@crt.umontreal.ca>
Cc: pgsql-performance@postgresql.org
Subject: Re: Extremely irregular query performance
Date: Fri, 20 Jan 2006 11:19:32 -0500 (EST)
Message-ID: <200601201619.k0KGJWE19919@candle.pha.pa.us> (raw)
In-Reply-To: <1137059321.3180.34.camel@localhost.localdomain>
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
Attachments:/bjm/diff (text/plain, 1.0K) download
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>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: pgman@candle.pha.pa.us, simon@2ndquadrant.com, tgl@sss.pgh.pa.us, jean-philippe.cote@crt.umontreal.ca
Subject: Re: Extremely irregular query performance
In-Reply-To: <200601201619.k0KGJWE19919@candle.pha.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox