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.
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 ?
