From: | "Jason L(dot) Buberel" <jason(at)buberel(dot)org> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Strange discrepancy in query performance... |
Date: | 2007-10-02 05:32:14 |
Message-ID: | 4701D7DE.8070007@buberel.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Albe Laurenz | 2007-10-02 07:13:39 | Re: Find out encoding of data |
Previous Message | Goboxe | 2007-10-02 00:28:46 | Re: Partitioned table limitation |