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

From: ning <mailxiening(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Repeated Query is much slower in PostgreSQL8.2.4 than DB2 9.1
Date: 2009-07-15 03:10:44
Message-ID: 27f31620907142010u391841e2ha432f939d3acbb7e@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I am transplanting an application to use PostgreSQL8.2.4 instead of DB2 9.1.
CLI was used to connect to DB2, and ODBC is used to connect to PostgreSQL.
The query statement is as follows:

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

I tested the performance on PostgreSQL against DB2, and found that

First execution: PostgreSQL 0.006277 seconds / DB2 0.009028 seconds
Second execution: PostgreSQL 0.005932 seconds / DB2 0.000332 seconds

PostgreSQL cost nearly the same time but DB2 ran 30 times faster in
second execution.

I tried to adjust shared_buffers parameter in postgresql.conf, no speed up.
Actually my DB holds only several records so I don't think memory is the reason.

Could anybody give some advice to speed up in repeated execution in
PostgreSQL or
give an explanation why DB2 is so mush faster in repeated execution?

Thank you.
ning

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Scott Marlowe 2009-07-15 03:40:38 Re: Performance comparison between Postgres and Greenplum
Previous Message Heikki Linnakangas 2009-07-14 15:09:15 Re: BUG #4919: CREATE USER command slows down system performance