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

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(at)truviso(dot)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
>