pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: pginfo <pginfo@t1.unisoftbg.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: Major differences between oracle and postgres performance
Date: Fri, 18 Jun 2004 15:59:02 +0200
Message-ID: <40D2F526.608@t1.unisoftbg.com> (raw)
References: <20040618132554.72968.qmail@web25103.mail.ukl.yahoo.com>
	<20412.1087570627@sss.pgh.pa.us>

Hi,

Tom Lane wrote:

>=?iso-8859-1?q?Gary=20Cowell?= <gary_cowell@yahoo.co.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
>specs.
>
>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,
ivan.

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

view thread (18+ messages)  latest in thread

Message-ID: <40D2F526.608@t1.unisoftbg.com>
Permalink:  ../40D2F526.608@t1.unisoftbg.com/
Also on:    postgresql.org/message-id/40D2F526.608@t1.unisoftbg.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: pginfo@t1.unisoftbg.com, tgl@sss.pgh.pa.us
  Subject: Re: Major differences between oracle and postgres performance
  In-Reply-To: <40D2F526.608@t1.unisoftbg.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox