>I can't imagine why that would even happen.
That can happen in case backend uses "bad plan" for server-prepared statement.
Here are more details:
http://www.postgresql.org/docs/9.4/static/sql-prepare.html
As far as I remember, backend can switch plan on 5th or 6th execution
of server-prepared statement.
>PG DOC: If a prepared statement is executed enough times, the server may eventually decide to save and re-use a
genericplan rather than re-planning each time. This will occur immediately if the prepared statement has no parameters
In other words, even server-prepared statements can behave differently
from one execution to another.
I've seen a couple of times when a query was fast "the first 5 times",
then backend (9.4) switched to much slower plan.
That happened with exactly the same input value.
The resolution for me was to fix query plan as desired (add offset 0
here and there) so the join order was specific.
On the other hand, I've seen impressive performance improvements for
long queries that take much longer to plan than to execute. Common
wisdom is to hide long SQL into stored procedures (they have
transparent statement cache too), however it can't easily be done for
existing application.
Vladimir