Re: [JDBC] Bad plan for queries with IN clause - Mailing list pgsql-general

OK, found the source of the problem: our code is setting nulls via:
PreparedStatement.setNull(int parameterIndex, int sqlType)
using Types.NUMERIC as the type, but the data base type is actually
BIGINT. Using Types.BIGINT in this method makes the problem go away.
Hopefully there are no more bad surprises for us coming from the
oracle/postgres incompatibility twilight zone... (crossing fingers).

Cheers,
Csaba.


On Fri, 2005-07-29 at 12:29, Oliver Jowett wrote:
> 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