Re: [JDBC] Bad plan for queries with IN clause

Csaba Nagy wrote:

> However, some of the queries still take the sequential scan route. The
> most puzzling in all this is that I've tried to "prepare" the same query
> in psql, and then "explain execute" the prepared query, and it gave me
> an index scan plan... so now I'm clueless, and have no idea why would
> the same query prepared by java yield a different plan than prepared
> manually... I thought that the query plan is created when you prepare
> the statement, and not on each execution, right ? And I would expect
> that the same query prepared multiple times would give the same plan,
> provided that the tables didn't change significantly...

One gotcha is that by default the JDBC driver will use an unnamed
statement for the first few executions of a particular
PreparedStatement, then switch to using a (reused) named statement
thereafter. The unnamed statement path can result in different plans to
what you'd get with PREPARE or a named statement, as it delays planning
until the first execution, then takes the actual parameter values into
account when doing selectivity estimates. In contrast PREPARE and named
statements plan immediately using placeholder estimates.

You can tweak the threshold for this on a per-connection or
per-statement basis via PGConnection.setPrepareThreshold() and
PGStatement.setPrepareThreshold() (statements inherit the connection's
value on creation by default). The connection default is also settable
via the prepareThreshold URL parameter. If you set it to 1, *every*
PreparedStatement execution uses a named statement. If you set it to 0,
named statements are never used.

> Could it be that the JDBC driver is
> preparing with wrong parameter types ? I thought 8.0 is more forgiving
> in this respect anyway.

One thing that may be useful for debugging this: if you set logLevel=2
as a URL parameter the JDBC driver will log the protocol messages it
sends and receives to the JDBC log writer (stderr by default) -- that
includes the type OIDs and whether it's using an unnamed or a named
statement.

-O