Skip site navigation (1) Skip section navigation (2)

Re: Slow query after upgrades

From: "Guillaume Smet" <guillaume(dot)smet(at)gmail(dot)com>
To: "Tom Duffey" <tduffey(at)techbydesign(dot)com>
Cc: pgsql-jdbc(at)postgresql(dot)org
Subject: Re: Slow query after upgrades
Date: 2007-12-13 21:58:10
Message-ID: 1d4e0c10712131358m4bbc3f57t7e33c6c8ad2b3710@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
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

In response to

Responses

pgsql-jdbc by date

Next:From: Tom DuffeyDate: 2007-12-13 22:37:29
Subject: Re: Slow query after upgrades
Previous:From: Kris JurkaDate: 2007-12-13 21:31:12
Subject: Re: JDBC causing the connection to close after executing a COPY command

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group