pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: nik9000@gmail.com
To: Franck Routier <franck.routier@axege.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: What happens between end of explain analyze and end of query execution ?
Date: Thu, 4 Apr 2013 15:05:52 -0400
Message-ID: <341110C9-21FF-4379-9103-B58B60F81138@gmail.com> (raw)
In-Reply-To: <515DCB34.6040801@axege.com>
References: <515D92BF.5000309@axege.com>
	<CAPmjWd100eemBz1RTw=r24zgh8+YO8iq86fThqsALznrPuyWqg@mail.gmail.com>
	<515D9FC5.7030105@axege.com>
	<18910.1365092713@sss.pgh.pa.us>
	<515DCB34.6040801@axege.com>
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>



On Apr 4, 2013, at 2:49 PM, Franck Routier <franck.routier@axege.com> wrote:

> Le 04/04/2013 18:25, Tom Lane a écrit :
>> Franck Routier <franck.routier@axege.com> writes:
>>> Right, explain alone takes 3.6 seconds, so the time really seems to go 
>>> query planning...
>> Well, you've not shown us the query, so it's all going to be
>> speculation.  But maybe you have some extremely expensive function that
>> the planner is evaluating to fold to a constant, or something like that?
>> The generated plan isn't terribly complicated, but we can't see what
>> was required to produce it.
>> 
>> 			regards, tom lane
>> 
>> 
> The request is not using any function. It looks like this:
> 
> SELECT *
>          FROM sanrss
>          LEFT JOIN sanrum  ON sanrum.sanrum___rforefide = sanrss.sanrss___rforefide AND sanrum.sanrum___rfovsnide = sanrss.sanrss___rfovsnide AND sanrum.sanrum___sanrsside = sanrss.sanrsside
>             LEFT JOIN sanact  ON sanact.sanact___rforefide = sanrum.sanrum___rforefide AND sanact.sanact___rfovsnide = sanrum.sanrum___rfovsnide AND sanact.sanact___sanrsside = sanrum.sanrum___sanrsside AND sanact.sanact___sanrumide = sanrum.sanrumide AND sanact.sanact___sanrumide   IS NOT NULL AND sanact.sanact___rsanopide='CCAM'
>             LEFT JOIN sandia  ON sandia.sandia___rforefide = sanrum.sanrum___rforefide AND sandia.sandia___rfovsnide = sanrum.sanrum___rfovsnide AND sandia.sandia___sanrsside = sanrum.sanrum___sanrsside AND sandia.sandia___sanrumide = sanrum.sanrumide AND sandia.sandiasig=1
>             LEFT JOIN saneds  ON sanrss.sanrss___rforefide = saneds.saneds___rforefide AND sanrss.sanrss___rfovsnide = saneds.saneds___rfovsnide AND sanrss.sanrss___sanedside = saneds.sanedside
>             LEFT JOIN rsaidp  ON saneds.saneds___rforefide = rsaidp.rsaidp___rforefide AND saneds.saneds___rsaidpide = rsaidp.rsaidpide
>               WHERE sanrss.sanrss___rforefide =  'CHCL'  AND sanrss.sanrss___rfovsnide =  '201012_600'  AND sanrss.sanrsside =  '1188100'
>          ORDER BY sanrum.sanrumord, sanrum.sanrumide
> 
> Schema looks like this :
> rsaidp
>  |
>  v
> sanrss --------
>  |            |
>  v            v
> sanrum       sandia
>  |
>  v
> sanact
> 
> 
> Primary keys are most made of several varchars. Foreign keys do exist. Query is getting these data for one specific sanrss.
> This used to take around 50ms to execute, and is now taking 3.5 seconds. And it looks like this is spent computing a query plan...
> 
> I also tried: PREPARE qry(id) as select ....
> The prepare takes 3.5 seconds. Execute qry(value) takes a few milliseconds...
> 
> Regards,
> Franck

Is it only this query or all queries?=

view thread (21+ messages)  latest in thread

Message-ID: <341110C9-21FF-4379-9103-B58B60F81138@gmail.com>
Permalink:  ../341110C9-21FF-4379-9103-B58B60F81138@gmail.com/
Also on:    postgresql.org/message-id/341110C9-21FF-4379-9103-B58B60F81138@gmail.com

 · 

reply

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: nik9000@gmail.com, franck.routier@axege.com
  Subject: Re: What happens between end of explain analyze and end of query execution ?
  In-Reply-To: <341110C9-21FF-4379-9103-B58B60F81138@gmail.com>
  References: <515D92BF.5000309@axege.com> <CAPmjWd100eemBz1RTw=r24zgh8+YO8iq86fThqsALznrPuyWqg@mail.gmail.com> <515D9FC5.7030105@axege.com> <18910.1365092713@sss.pgh.pa.us> <515DCB34.6040801@axege.com> <341110C9-21FF-4379-9103-B58B60F81138@gmail.com>

* 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