Re: Extremely irregular query performance

От: Bruce Momjian
Тема: Re: Extremely irregular query performance
Дата: ,
Msg-id: 200601201619.k0KGJWE19919@candle.pha.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Extremely irregular query performance  (Simon Riggs)
Ответы: Re: Extremely irregular query performance  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

Extremely irregular query performance  (Jean-Philippe Côté<>, )
 Re: Extremely irregular query performance  (Tom Lane, )
  Re: Extremely irregular query performance  (Jean-Philippe Côté<>, )
   Re: Extremely irregular query performance  (Mark Lewis, )
   Re: Extremely irregular query performance  (Tom Lane, )
    Re: Extremely irregular query performance  (Simon Riggs, )
     Re: Extremely irregular query performance  (Kenneth Marshall, )
     Re: Extremely irregular query performance  (Bruce Momjian, )
      Re: Extremely irregular query performance  (Tom Lane, )
       Re: Extremely irregular query performance  (Bruce Momjian, )
 Re: Extremely irregular query performance  (Scott Marlowe, )
 Re: Extremely irregular query performance  (Jean-Philippe Cote, )
  Re: Extremely irregular query performance  (Bruce Momjian, )
  Re: Extremely irregular query performance  (Kenneth Marshall, )

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.

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
                 |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
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>


В списке pgsql-performance по дате сообщения:

От: "Steinar H. Gunderson"
Дата:
Сообщение: Re: [GENERAL] Creation of tsearch2 index is very slow
От: Ron
Дата:
Сообщение: Re: [GENERAL] Creation of tsearch2 index is very slow