Re: Strange discrepancy in query performance...

Tom-right-as-usual:

Yep - you were right about the query plan for the prepared statement (a
sequential scan of the table) differed a bit from the directly-executed
version :)

For reference, when using JasperReports .jrxml files as the basis for
the query, I only had to do to the following to 'force' postgres to
treat the jasper report parameter as a number and not text, thereby
allowing the correct index to be used:

select * from city summary where city_master_id =
$P{city_master_id}::bigint ...

Query times went from 300+ seconds back down to ~100ms.

-jason

Tom Lane wrote:
> "Jason L. Buberel" <jason(at)buberel(dot)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
>