Upgrading JDBC driver from 7.3 to 8.4 affected execution plans

From: Boris <boris(dot)partensky(at)gmail(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Cc: boris(dot)partensky(at)gmail(dot)com
Subject: Upgrading JDBC driver from 7.3 to 8.4 affected execution plans
Date: 2010-10-03 13:33:13
Message-ID: 9c7afd41-61f0-4acb-bc14-bb10aeaf395f@d17g2000yqm.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

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

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Oliver Jowett 2010-10-04 00:27:45 Re: Upgrading JDBC driver from 7.3 to 8.4 affected execution plans
Previous Message Oliver Jowett 2010-10-03 03:10:45 Re: Upgrade to 9 questions