Skip site navigation (1) Skip section navigation (2)

Re: Major differences between oracle and postgres performance

From: pginfo <pginfo(at)t1(dot)unisoftbg(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Major differences between oracle and postgres performance
Date: 2004-06-18 13:59:02
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance

Tom Lane wrote:

>=?iso-8859-1?q?Gary=20Cowell?= <gary_cowell(at)yahoo(dot)co(dot)uk> writes:
>>   ->  Sort  (cost=117865.77..119220.13 rows=541741
>>width=132) (actual time=63623.417..66127.641
>>rows=541741 loops=1)
>This is clearly where the time is going.
>>sort_mem = 16384     
>Probably not enough for this problem.  The estimated data size is
>upwards of 60 meg (132 bytes * half a mil rows); allowing for per-row
>overhead I suspect that you'd need sort_mem approaching 100 meg for
>a fully-in-memory sort.  (Also I'd take the width=132 with a *big*
>grain of salt, unless you have reason to know that it's accurate.)
>The on-disk sorting algorithm that we use is designed to favor minimum
>disk space consumption over speed.  It has a fairly nonrandom access
>pattern that can be pretty slow if your disks don't have good seek-time
>I don't know whether Oracle's performance advantage is because they're
>not swapping the sort to disk at all, or because they use a different
>on-disk sort method with a more sequential access pattern.
>[... thinks for awhile ...]  It seems possible that they may use sort
>code that knows it is performing a DISTINCT operation and discards
>duplicates on sight.  Given that there are only 534 distinct values,
>the sort would easily stay in memory if that were happening.
>It would be interesting to compare Oracle and PG times for a straight
>sort of half a million rows, without the DISTINCT part; that would
>give us a clue whether they simply have much better sort technology,
>or whether they have a special optimization for sort+unique.
I was tested this situation and found that oracle is working also in 
this case much faster (in some cases x10 ) compared to pg.
Also by in memory sort oracle is faster but the diferenc is not so big.
So I have oracle 8 and oracle 10 (also pg - it is my primary platform) 
installed and can run some tests.
I am ready to help in this direction or if you can send any example I 
will run it and post the result .


>			regards, tom lane
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend

In response to

pgsql-performance by date

Next:From: Tom LaneDate: 2004-06-18 14:29:25
Subject: Re: *very* inefficient choice made by the planner (regarding
Previous:From: Richard HuxtonDate: 2004-06-18 13:58:16
Subject: Re: memory allocation

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group