Re: Slow query after upgrades

On Dec 13, 2007 10:28 PM, Tom Duffey <tduffey(at)techbydesign(dot)com> wrote:
> 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.

Usual questions: did you analyze your database after loading the dump?

One of the problem you can meet is that the new driver uses protocol
v3 by default, which means that your query are prepared and planned
without the value of the parameters as it was the case using v2 of the
protocol. This can lead in bad plans in specific cases.
You may consider using protocol v2 again if it's your problem by
adding protocolVersion=2 to your JDBC connection URL.

> 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)?

Use log_min_duration_statement in your postgresql.conf (0 if you want
to log every queries).

--
Guillaume