Hi all, I am trying to understand an interesting side effect of our
recent JDBC driver upgrade (7.3 -> 8.4.701). Here is what I am seeing:
We use prepared statements to construct a multi inner/outer join (~ 15
joins, all on primary keys) query which brings back 0 or 1 raw.
With old driver, a query would take single digit milliseconds on
average when executed from java code. With new driver, I noticed that
the load on db server skyrocketed and all those queries take seconds
to execute. The reason is that the optimizer chooses merge joins
instead of nested loops. When I turn merge/hash joins off in
postgresql.conf, all went back to normal, queries are now executed
very fast.
Another interesting point is that, when I paste the slow query into
psql, and explain/analyze it, bad plan is chosen (like observed from
the code with new driver).
Any ideas? I'd be happy to provide more details if needed. Is the
issue somehow caused by the fact that 8.4 driver uses real server side
prepared statements vs the old one that didn't?
Cheers,
Boris