Skip site navigation (1) Skip section navigation (2)

Re: Very strange performance decrease when reusing a PreparedStatement

From: Thomas Kellerer <spam_eater(at)gmx(dot)net>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Very strange performance decrease when reusing a PreparedStatement
Date: 2009-05-04 09:03:17
Message-ID: gtmb0l$s7p$1@ger.gmane.org (view raw or flat)
Thread:
Lists: pgsql-jdbc
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


In response to

pgsql-jdbc by date

Next:From: Mikko TiihonenDate: 2009-05-04 09:42:25
Subject: Re: Very strange performance decrease when reusing a PreparedStatement
Previous:From: Péter KovácsDate: 2009-05-04 08:27:02
Subject: Re: Very strange performance decrease when reusing a PreparedStatement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group