From: Franck Routier <franck.routier@axege.com>
To: pgsql-performance@postgresql.org
Subject: Re: What happens between end of explain analyze and end of query execution ?
Date: Thu, 04 Apr 2013 20:49:24 +0200
Message-ID: <515DCB34.6040801@axege.com> (raw)
In-Reply-To: <18910.1365092713@sss.pgh.pa.us>
References: <515D92BF.5000309@axege.com>
<CAPmjWd100eemBz1RTw=r24zgh8+YO8iq86fThqsALznrPuyWqg@mail.gmail.com>
<515D9FC5.7030105@axege.com>
<18910.1365092713@sss.pgh.pa.us>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
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
Attachments:
smime.p7s (application/pkcs7-signature, 4.4K) download
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: franck.routier@axege.com
Subject: Re: What happens between end of explain analyze and end of query execution ?
In-Reply-To: <515DCB34.6040801@axege.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