Bad plan for queries with IN clause - Mailing list pgsql-general

Hi all,

Sorry for the cross-post, I need the bigger exposure, and I'm not sure
if it's JDBC issue or not.

This is a follow-up from an earlier post, with a new twist.

We have a Java application which is designed initially to work with
Oracle, and then gradually adapted to also work with postgres, sometimes
even connecting at the same time to both oracle and postgres data bases.

Until now we didn't have any big postgres data-base, which has just
changed by moving a big one from oracle to postgres... so now we start
to notice bad query plans.

We have quite a lot of data import/exporting, and we use the following
type of query to chunk these:

... WHERE primarykey IN (?, ?, ...., ?) ...

We use JDBC prepared statements, and fill the parameters with chunks of
the looked up data. The last chunk is smaller of course, and the
remaining parameters are filled up with nulls.

On oracle this works just fine.

Before my last post (reporting bad plan on this type of query when nulls
are in the IN list) we were using the 7.4 JDBC driver, which AFAICT is
not actually using server side prepared statements, but it sends each
query with the parameters inlined. I guess that's why the nulls were
taken into account in the query plan.

Now we upgraded the JDBC driver (to 8.0), and I think this one is
actually using server side prepared statements for java
PreparedStatements. This eliminates the null problem, and indeed most of
our queries take now index scans.

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...

The sequential scan on those queries is even more surprising as the
execution time is so radically different, taking milliseconds for an
index scan, and even hours for the sequential scan (the involved tables
are from a few million rows to a few hundred million rows size).

BTW, I've tried to set "enable_seqscan = false", but those queries were
still going the sequential scan way. Could it be that the JDBC driver is
preparing with wrong parameter types ? I thought 8.0 is more forgiving
in this respect anyway.

I also tried to tweak the memory settings per
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html
, but it didn't help either.

I have enabled plan logging at one point, I can provide an actual plan
for one of those queries if that would help diagnose what's happening...
for me the only relevant thing was:
"DETAIL: {SEQSCAN :startup_cost 0.00 :total_cost 1832574.05 :plan_rows 1
:plan_width 8 ... "
the rest is too low level and I don't understand it.

I would appreciate any help, as I am mostly clueless what the problem
could be.

Thanks,
Csaba.