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

Hi Greg,

I am doing performance test by running unit test program to compare
time used on PostgreSQL and DB2. As you pointed out, there are cases
that PostgreSQL is faster. Actually in real world for my application,
repeatedly executing same query statement will hardly happen. I am
investigating this because on the performance test report
automatically generated by running unit test program, DB2 is 20-30
times faster than PostgreSQL in some test cases because of repeatedly
executed query.

I am thinking that ignoring these test cases for performance measure
is safe and acceptable, since PostgreSQL is quicker than DB2 for the
first execution.

Thank you.
Ning

On Wed, Jul 15, 2009 at 5:37 PM, Greg Stark<gsstark(at)mit(dot)edu> wrote:
> On Wed, Jul 15, 2009 at 9:27 AM, Craig
> Ringer<craig(at)postnewspapers(dot)com(dot)au> 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.
>
>
> Yeah, is 6ms really a problematic response time for your system?
>
> If so you might consider whether executing millions of small queries
> is really the best approach instead of absorbing them all into queries
> which operate on more records at a time. For example, it's a lot
> faster to join two large tables than look up matches for every record
> one by one in separate queries.
>
> There's no question if you match up results from DB2 and Postgres one
> to one there will be cases where DB2 is faster and hopefully cases
> where Postgres is faster. It's only interesting if the differences
> could cause problems, otherwise you'll be running around in circles
> hunting down every difference between two fundamentally different
> products.
>
> --
> greg
> http://mit.edu/~gsstark/resume.pdf
>