Re: What happens between end of explain analyze and end of query execution ?

[Franck Routier <franck.routier@xxxxxxxxx>]

Right, explain alone takes 3.6 seconds, so the time really seems to go query planning...


Le 04/04/2013 17:01, Nikolas Everett a écrit :

We had a problem where the actual query planning time blew up and took way more time then the query execution.  We reproduced the problem by forming a new connection and then just explaining the query.  If that takes more than a couple of milliseconds you likely have the problem.  The second plan was fast.

We then dtraced the backend process for a new connection and found that opening files had become super slow.  In our case this was caused by running the database on nfs.

Good luck,

Nik

On Thu, Apr 4, 2013 at 10:48 AM, Franck Routier <franck.routier@xxxxxxxxx> wrote:

Hi,

I have query that should be quick, and used to be quick, but is not anymore... Explain analyze can be seen here http://explain.depesz.com/s/cpV
but it is fundamentaly quick : Total runtime: 0.545 ms.

But query execution takes 3.6 seconds. Only 12 rows are returned. Adding a limit 1 has no influence.

Postgresql client and server are on the same server, on localhost.

I wonder where my 3.5 seconds are going....

Notice that there is no fancy configuration to tell Postgresql to evaluate every query plan on earth, that is gecoxxxx settings are on their default values.

Any idea on what can be happening that takes so long ?

Franck

Attachment: smime.p7s
Description: Signature cryptographique S/MIME