Re: Upgrading JDBC driver from 7.3 to 8.4 affected execution plans

Oh, this issue should probably be mentioned in
http://jdbc.postgresql.org/documentation/faq.html#upgradeTo80
document. It's pretty subtle and can bite someone hard.

Boris

On Mon, Oct 4, 2010 at 9:38 AM, Boris Partensky
<boris.partensky@gmail.com> wrote:
> Thanks Maciek, you probably nailed it. Sounds like the problem is
> there somewhere :).
>
>> Like Oliver mentioned, I'd take a look at the
>> server first if you've upgraded that as well, but otherwise, this
>> could be the right path.
>
> Nothing changed server side. Again, I can reproduce the "faulty"
> behavior at will by switching the driver jars (after commenting out
> set enable_hashjoin = false and set enable_mergejoin = false from the
> server conf).
>
> Not sure what I can do about this behavior though other than switching
> off hash/merge joins, which we did. I looked through the source, and
> preparedThreshold is set to 5 by default in 8.4.
>
>
>
> On Sun, Oct 3, 2010 at 11:35 PM, Maciek Sakrejda <msakrejda@truviso.com> wrote:
>> Could this be related to how planning works with respect to
>> parameterized queries [1]? If memory serves, a prepared statement is
>> typically planned once and that plan is used each time the statement
>> is executed. There can be some issues around this because when you
>> have a query like "SELECT * FROM foo WHERE bar > ?", the planner has
>> no idea of the selectivity of that predicate if it has to plan the
>> query before it knows what the parameters will be. E.g., if it picks
>> an index scan and then a query is submitted where ? is 10, if 95% of
>> bars are greater than 10, the executor will waste a lot of time
>> mucking around with the index where it should just be doing a seq
>> scan.
>>
>> I think with prepared statements, the driver typically executes them
>> in an anonymous portal (i.e., more or less, a prepared statement
>> context) which means the planner waits until parameters are provided.
>> However, once prepareThreshold is reached, it uses a named portal,
>> which means a single parameter-agnostic plan (the benefit is that you
>> don't have to replan per-query, and planning can be moderately
>> expensive). The page linked below mentions that things have changed in
>> the driver in this area. Like Oliver mentioned, I'd take a look at the
>> server first if you've upgraded that as well, but otherwise, this
>> could be the right path.
>>
>> Note that the above only means that there could be differences in how
>> the 7.3 and 8.4 drivers are causing your query to be planned. If the
>> old driver you're using is using named portals for statement execution
>> right off the bat, but that's somehow coming up with better plans
>> (even though it has less information), something could be wonky with
>> your planner-related GUCS.
>>
>> [1]: http://jdbc.postgresql.org/documentation/84/server-prepare.html
>> ---
>> Maciek Sakrejda | System Architect | Truviso
>>
>> 1065 E. Hillsdale Blvd., Suite 215
>> Foster City, CA 94404
>> (650) 242-3500 Main
>> www.truviso.com
>>
>