Re: Strange discrepancy in query performance... - Mailing list pgsql-general

From Tom Lane
Subject Re: Strange discrepancy in query performance...
Date
Msg-id 24648.1191283302@sss.pgh.pa.us
Whole thread Raw
In response to Strange discrepancy in query performance...  ("Jason L. Buberel" <jason@buberel.org>)
Responses Re: Strange discrepancy in query performance...  ("Jason L. Buberel" <jason@buberel.org>)
List pgsql-general
"Jason L. Buberel" <jason@buberel.org> writes:
> In my syslog output, I see entries indicating that the
> JDBC-driver-originated query on a table named 'city_summary' are taking
> upwards of 300 seconds:

> Oct  1 18:27:47 srv3 postgres-8.2[1625]: [12-1]
> LOG:  duration: 307077.037 ms  execute S_42: select * from city_summary
>       where  state = $1 and city_master_id = $2 and res_type =
> 'single_family' and date = $3
>       and range = 90 and zip = $4 and quartile  = '__ALL'
> DETAIL:  parameters: $1 = 'CA', $2 = '291', $3 = '2007-09-28', $4 = '__ALL'

> However, if I run the same query on the same host at the same time that
> the Java application is running, but from the psql  command line, it
> takes only 0.37 seconds:

>>> time /opt/postgres-8.2.4/bin/psql --port 54824  -U postgres -d
> altos_research  -c 'select fact_id from city_summary where state =
> \'CA\' and city_master_id = 291 and zip = \'__ALL\' and quartile =
> \'__ALL\' and res_type = \'single_family\' and range = \'90\' and date =
> \'2007-09-28\';'

This is not, in fact, the same query --- the JDBC-originated one is
parameterized, which means it very possibly has a different plan
(since the planner doesn't know the particular values to plan for).

Try using PREPARE and EXPLAIN EXECUTE to examine the plan that is
being produced for the parameterized query.

            regards, tom lane

pgsql-general by date:

Previous
From: "Jason L. Buberel"
Date:
Subject: Strange discrepancy in query performance...
Next
From: "Jason L. Buberel"
Date:
Subject: Re: Strange discrepancy in query performance...