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

Hi Andres,

The log for the test you suggested is as follows in PostgreSQL8.2.4,
but I cannot find a clue to prove or prove not PostgreSQL is doing
plan caching.

Best regards,
Ning

-----
job=# prepare test_query as 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=100 and JobView.assignedTo_=1) AND nameId
in (6)) x RIGHT OUTER JOIN (SELECT oid_ FROM JobView WHERE
JobView.JobId=100 and JobView.assignedTo_=1) y ON attributeof = oid_
FOR READ ONLY
;
PREPARE
job=# execute test_query;
oid_ | void | nameid | tag | intval | lowerbound | upperbound |
crossfeeddir | feeddir | units | opqval | bigval | strval
------+------+--------+-----+--------+------------+------------+--------------+---------+-------+--------+--------+--------
101 | | | | | | |
| | | | |
(1 row)

job=# execute test_query;
oid_ | void | nameid | tag | intval | lowerbound | upperbound |
crossfeeddir | feeddir | units | opqval | bigval | strval
------+------+--------+-----+--------+------------+------------+--------------+---------+-------+--------+--------+--------
101 | | | | | | |
| | | | |
(1 row)
-----

On Thu, Jul 16, 2009 at 7:51 AM, Andres Freund<andres(at)anarazel(dot)de> wrote:
> On Wednesday 15 July 2009 10:27:50 Craig Ringer wrote:
>> On Wed, 2009-07-15 at 12:10 +0900, ning wrote:
>> > First execution: PostgreSQL 0.006277 seconds / DB2 0.009028 seconds
>> > Second execution: PostgreSQL 0.005932 seconds / DB2 0.000332 seconds
>>
>> Actually, on second thoughts that looks a lot like DB2 is caching the
>> query results and is just returning the cached results when you repeat
>> the query.
> Are you sure getting the query *result* is causing the delay? If my faint
> memory serves right DB2 does plan caching - PG does not.
> To test this theory you could prepare it and execute it twice.
>
> Prepare it:
> PREPARE test_query AS SELECT void,nameId,tag FROM (SELECT void,nameId,tag,....
> FROM Attr
> WHERE attributeof IN (SELECT oid_ FROM ItemView WHERE
> ItemView.ItemId=?)) x RIGHT OUTER JOIN (SELECT oid_ FROM ItemView
> WHERE ItemView.ItemId=? and ItemView.assignedTo_=?) y ON attributeof =
> oid_ FOR READ ONLY;
>
>
> Execute it:
> EXECUTE test_query;
> EXECUTE test_query;
>
> Greetings,
>
> Andres
>