Re: Very strange performance decrease when reusing a PreparedStatement

Roland Roberts, 04.05.2009 01:12:
> No, my point was that PostgreSQL is doing the same thing that Oracle is
> doing and in both cases it can bite you. Both are looking at the bind
> variables to come up with a plan but the plan is retained for reuse
> under the assumption that the bind variables will be, statistically at
> least, similar the next time. When that assumption is violated, you get
> stuck with a bad plan. The purpose of my Oracle example was to give a
> concrete example of such a violation.

Oracle 11 has introduced a feature called "Adaptive Cursor Sharing" which tries to solve this problem (Oracle 10 has some tweaks to address that problem manually, but they have their cornerstones as well)

Each time a prepared statement is re-used the optimizer compares the actuals with the stored assumptions (mainly estimated rows vs. actual rows, IIRC). If those figures deviate too much, a new plan is created and used from that point onwards _for that parameter combination_.

So each SQL actually has a map of prepared statements depending on the input parameters. Obviously there is still one statement execution (the first one) that is hit by the bad plan, but all subsequent executions will benefit from the new one.

Might be worth considering for PostgreSQL as well :)

Thomas