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

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