Re: Slow query after upgrades

On Dec 13, 2007, at 3:20 PM, Oliver Jowett wrote:

> Tom Duffey wrote:
>
>> I'm leaving the old JDBC driver there for now but am wondering if
>> anyone has any ideas what I should look for to find a solution to
>> this? I jProfiled the execution and it is indeed the JDBC query
>> that is taking significantly more time to execute with the newer
>> driver in place.
>
> Identify the slow query and build a testcase for that particular
> query and send it to the list?
>
> Also, beware of counting blocking I/O time as CPU time when doing
> Java profiling. Probably, the Java side is just blocking waiting on
> the server.
>
> There are server-side logging options you might want to look at too.
>
> The most likely cause is that more recent drivers ("recent" is very
> relative here, I think it went in for the 8.0 driver?) use the
> extended query protocol which can result in different plans for some
> queries.

The odd thing is that the same query is used in other components of
the application and we have not noticed any issues. This led me to
believe that something about this particular piece is causing the
problem, possibly external to the query or the JDBC driver. Running
the query in psql, for example, doesn't seem slow at all. So maybe
it's not a PostgreSQL problem at all but I thought someone might have
seen this and have some insight into where I should look.

Is there some logging I can use to compare what is happening on the
server side when this query is run by the app with the problem vs.
something else (psql or another of our applications)?

Tom