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

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