From: | Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> |
---|---|
To: | Simon Riggs <simon(at)2ndquadrant(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Jean-Philippe Côté <jean-philippe(dot)cote(at)crt(dot)umontreal(dot)ca>, pgsql-performance(at)postgresql(dot)org |
Subject: | Re: Extremely irregular query performance |
Date: | 2006-01-20 16:19:32 |
Message-ID: | 200601201619.k0KGJWE19919@candle.pha.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
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(dot)cote(at)crt(dot)umontreal(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
Attachment | Content-Type | Size |
---|---|---|
unknown_filename | text/plain | 1.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2006-01-20 16:34:49 | Re: Extremely irregular query performance |
Previous Message | Tom Lane | 2006-01-20 15:35:21 | Re: Creation of tsearch2 index is very slow |