Vladimir Sitnikov schrieb am 12.01.2016 um 11:44:
>> And then suddenly the runtime shoots through the rough going up from 1ms to 40ms or even 60ms
>
> Can it be data-dependent?
Apparently the server chooses a bad execution plan when a server side prepared
statement is used - it is very likely because of the parameter placeholders
that don't allow Postgres to evaluate the correct cardinality and it
creates a "one size fits all plan"
My guess is that the plan that is generated using the prepared statement only
works for some input values, but not for all (a problem that Oracle has suffered
from for ages as well). As I have written previously we didn't bother to enable the
auto-explain module to see where Postgres goes wrong in the planning because using
prepareThreshold=0 is good enough for us.
Given my experience with bad plans for parametrized statements in Oracle I think it is
definitely the better choice to not use server side prepared statements - at least for
this application.
> Can you try using the same input values for multiple executions?
The tests we are running are pretty much pre-defined. Re-working them would probably be a bigger deal.
Plus I am not directly involved in that project, so I am actually relaying everything here ;)
Regards
Thomas