X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id F110BD1B188 for ; Fri, 18 Jun 2004 12:15:04 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 04495-04 for ; Fri, 18 Jun 2004 15:15:01 +0000 (GMT) Received: from mail.unisoftbg.com (unknown [194.12.229.207]) by svr1.postgresql.org (Postfix) with ESMTP id E1892D1B4E5 for ; Fri, 18 Jun 2004 12:14:59 -0300 (ADT) Received: (qmail 29801 invoked by uid 507); 18 Jun 2004 15:21:57 -0000 Received: from unknown (HELO t1.unisoftbg.com) (194.12.229.193) by 0 with SMTP; 18 Jun 2004 15:21:57 -0000 Message-ID: <40D2F526.608@t1.unisoftbg.com> Date: Fri, 18 Jun 2004 15:59:02 +0200 From: pginfo User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.0.1) Gecko/20020823 Netscape/7.0 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tom Lane Cc: pgsql-performance@postgresql.org Subject: Re: Major differences between oracle and postgres performance References: <20040618132554.72968.qmail@web25103.mail.ukl.yahoo.com> <20412.1087570627@sss.pgh.pa.us> Content-Type: multipart/alternative; boundary="------------020302060900080700040301" X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=1.6 tagged_above=0.0 required=5.0 tests=HTML_MESSAGE, HTML_TITLE_EMPTY, RCVD_IN_DSBL X-Spam-Level: * X-Archive-Number: 200406/194 X-Sequence-Number: 7252 --------------020302060900080700040301 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit Hi, Tom Lane wrote: >=?iso-8859-1?q?Gary=20Cowell?= 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 > > > > --------------020302060900080700040301 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit 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


  

--------------020302060900080700040301--