Message-ID: <522639B2.4010309@krogh.cc>
Date: Tue, 03 Sep 2013 21:34:10 +0200
From: Jesper Krogh <jesper@krogh.cc>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:17.0) Gecko/20130330 Thunderbird/17.0.5
MIME-Version: 1.0
To: Craig Ringer <craig@2ndquadrant.com>
CC: "pgsql-performance@postgresql.org" <pgsql-performance@postgresql.org>
Subject: Re: Slow query-plan generation (fast query) PG 9.2
References: <e9ed24069c06d8b504fbef3e28df52d7.squirrel@shrek.krogh.cc>
 <522593FC.3010302@2ndquadrant.com>
In-Reply-To: <522593FC.3010302@2ndquadrant.com>
Content-Type: multipart/alternative;
 boundary="------------080501010904040205050005"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------080501010904040205050005
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

On 03/09/13 09:47, Craig Ringer wrote:
> On 09/03/2013 03:46 PM, jesper@krogh.cc wrote:
>> Hi.
>>
>> I have a strange situation where generating the query plan takes 6s+ and
>> executing it takes very little time.
> How do you determine that it's planning time at fault here?
Not that I'm sure, but the timing I send were only for "explain" not 
"explain analyze". The database is constantly updating and at the moment 
i cannot reproduce it any more. But at the time I picked the numbers it 
were very reproducible.. (tried 10+ times over 15 minutes).

    Please take separate timing for: PREPARE testq AS select table.id
    from db.table left join db.tablepro on db.id = tablepro.table_id
    where table.fts @@ to_tsquery('english','q12345') ; and then:
    EXPLAIN ANALYZE EXECUTE testq; 

I'll try to do that if i see the problem re-occour. I'm just very 
interested in what explain then does if it is not only the time for the 
query plan. When I did try the "PREPARE / EXECUTE" dance as you 
described .. i didnt see the prepare state take time, which seems to be 
consistent with that the planning time is in the EXECUTE step according 
to the documentation.

-- 
Jesper

--------------080501010904040205050005
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>
    <meta content="text/html; charset=ISO-8859-1"
      http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <div class="moz-cite-prefix">On 03/09/13 09:47, Craig Ringer wrote:<br>
    </div>
    <blockquote cite="mid:522593FC.3010302@2ndquadrant.com" type="cite">
      <pre wrap="">On 09/03/2013 03:46 PM, <a class="moz-txt-link-abbreviated" href="mailto:jesper@krogh.cc">jesper@krogh.cc</a> wrote:
</pre>
      <blockquote type="cite">
        <pre wrap="">Hi.

I have a strange situation where generating the query plan takes 6s+ and
executing it takes very little time.
</pre>
      </blockquote>
      <pre wrap="">
How do you determine that it's planning time at fault here?
</pre>
    </blockquote>
    Not that I'm sure, but the timing I send were only for "explain" not
    "explain analyze". The database is constantly updating and at the
    moment i cannot reproduce it any more. But at the time I picked the
    numbers it were very reproducible.. (tried 10+ times over 15
    minutes).<br>
    <br>
    <blockquote>Please take separate timing for:
      PREPARE testq AS select table.id from db.table left
      join db.tablepro on db.id = tablepro.table_id where table.fts @@
      to_tsquery('english','q12345') ;
      and then:
      EXPLAIN ANALYZE EXECUTE testq;
    </blockquote>
    <blockquote cite="mid:522593FC.3010302@2ndquadrant.com" type="cite">
    </blockquote>
    I'll try to do that if i see the problem re-occour. I'm just very
    interested in what explain then does if it is not only the time for
    the query plan. When I did try the "PREPARE / EXECUTE" dance as you
    described .. i didnt see the prepare state take time, which seems to
    be consistent with that the planning time is in the EXECUTE step
    according to the documentation. <br>
    <br>
    -- <br>
    Jesper<br>
  </body>
</html>

--------------080501010904040205050005--