Re: Repeated Query is much slower in PostgreSQL8.2.4 than DB2 9.1

Hi Mike,

Thank you for your explanation.
The "explain analyze" command used is as follows, several integers are
bound to '?'.
-----
SELECT oid_,void,nameId,tag,intval,lowerbound,upperbound,crossfeeddir,feeddir,units,opqval,bigval,strval
FROM (SELECT attributeOf,void,nameId,tag,intval,lowerbound,upperbound,crossfeeddir,feeddir,units,opqval,bigval,strval
FROM DenormAttributePerf WHERE attributeof IN (SELECT oid_ FROM
JobView WHERE JobView.JobId=? and JobView.assignedTo_=?) AND nameId in
(?)) x RIGHT OUTER JOIN (SELECT oid_ FROM JobView WHERE
JobView.JobId=? and JobView.assignedTo_=?) y ON attributeof = oid_ FOR
READ ONLY
-----

The result of the command is
-----

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop Left Join (cost=575.60..1273.15 rows=81 width=568)
(actual time=0.018..0.018 rows=0 loops=1)
Join Filter: (x.attributeof = j1.oid_)
-> Index Scan using job_tc1 on job j1 (cost=0.00..8.27 rows=1
width=4) (actual time=0.016..0.016 rows=0 loops=1)
Index Cond: ((assignedto_ = 888) AND (jobid = 0))
-> Merge Left Join (cost=575.60..899.41 rows=16243 width=564)
(never executed)
Merge Cond: (v.void = b.void)
-> Merge Left Join (cost=470.77..504.87 rows=2152
width=556) (never executed)
Merge Cond: (v.void = res.void)
-> Sort (cost=373.61..374.39 rows=310 width=544)
(never executed)
Sort Key: v.void
-> Hash Left Join (cost=112.07..360.78 rows=310
width=544) (never executed)
Hash Cond: (v.void = i.void)
-> Hash Left Join (cost=65.40..303.17
rows=38 width=540) (never executed)
Hash Cond: (v.void = r.void)
-> Hash Left Join
(cost=21.42..257.86 rows=5 width=532) (never executed)
Hash Cond: (v.void = s.void)
-> Nested Loop Left Join
(cost=8.27..244.65 rows=5 width=16) (never executed)
Join Filter: (v.containedin = a.id)
-> Nested Loop
(cost=8.27..16.57 rows=1 width=12) (never executed)
-> HashAggregate
(cost=8.27..8.28 rows=1 width=4) (never executed)
-> Index
Scan using job_tc1 on job j1 (cost=0.00..8.27 rows=1 width=4) (never
executed)
Index
Cond: ((assignedto_ = 888) AND (jobid = 0))
-> Index Scan
using attribute_tc1 on attribute a (cost=0.00..8.27 rows=1 width=12)
(never executed)
Index Cond:
((a.attributeof = j1.oid_) AND (a.nameid = 6))
-> Append
(cost=0.00..137.60 rows=7239 width=12) (never executed)
-> Index Scan
using attribute_value_i on attribute_value v (cost=0.00..5.30 rows=9
width=12) (never executed)
Index Cond:
(v.containedin = a.id)
-> Seq Scan on
string_value v (cost=0.00..11.40 rows=140 width=12) (never executed)
-> Seq Scan on
integer_value v (cost=0.00..26.30 rows=1630 width=12) (never
executed)
-> Seq Scan on
bigint_value v (cost=0.00..25.10 rows=1510 width=12) (never executed)
-> Seq Scan on
rangeofint_value v (cost=0.00..25.10 rows=1510 width=12) (never
executed)
-> Seq Scan on
resolution_value v (cost=0.00..24.00 rows=1400 width=12) (never
executed)
-> Seq Scan on
opaque_value v (cost=0.00..20.40 rows=1040 width=12) (never executed)
-> Hash (cost=11.40..11.40
rows=140 width=520) (never executed)
-> Seq Scan on
string_value s (cost=0.00..11.40 rows=140 width=520) (never executed)
-> Hash (cost=25.10..25.10
rows=1510 width=12) (never executed)
-> Seq Scan on
rangeofint_value r (cost=0.00..25.10 rows=1510 width=12) (never
executed)
-> Hash (cost=26.30..26.30 rows=1630
width=8) (never executed)
-> Seq Scan on integer_value i
(cost=0.00..26.30 rows=1630 width=8) (never executed)
-> Sort (cost=97.16..100.66 rows=1400 width=16)
(never executed)
Sort Key: res.void
-> Seq Scan on resolution_value res
(cost=0.00..24.00 rows=1400 width=16) (never executed)
-> Sort (cost=104.83..108.61 rows=1510 width=12) (never executed)
Sort Key: b.void
-> Seq Scan on bigint_value b (cost=0.00..25.10
rows=1510 width=12) (never executed)
Total runtime: 0.479 ms
(46 rows)
-----

Best regards,
Ning

On Thu, Jul 16, 2009 at 7:37 AM, Mike Ivanov<mikei(at)activestate(dot)com> wrote:
> ning wrote:
>>
>> The log is really long,
>
> Which usually signals a problem with the query.
>
>> but I compared the result of "explain analyze"
>> for first and later executions, except for 3 "time=XXX" numbers, they
>> are identical.
>>
>
> They are supposed to be identical unless something is really badly broken.
>
>> I agree with you that PostgreSQL is doing different level of caching,
>> I just wonder if there is any way to speed up PostgreSQL in this
>> scenario,
>
> This is what EXPLAIN ANALYZE for. Could you post the results please?
>
> Cheers,
> Mike
>
>