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 ED597D1B757 for ; Fri, 18 Jun 2004 11:57:07 -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 78657-08 for ; Fri, 18 Jun 2004 14:57:09 +0000 (GMT) Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by svr1.postgresql.org (Postfix) with ESMTP id 0876AD1B749 for ; Fri, 18 Jun 2004 11:57:03 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.12.11/8.12.11) with ESMTP id i5IEv7nI020413; Fri, 18 Jun 2004 10:57:07 -0400 (EDT) To: =?iso-8859-1?q?Gary=20Cowell?= Cc: pgsql-performance@postgresql.org Subject: Re: Major differences between oracle and postgres performance - what can I do ? In-reply-to: <20040618132554.72968.qmail@web25103.mail.ukl.yahoo.com> References: <20040618132554.72968.qmail@web25103.mail.ukl.yahoo.com> Comments: In-reply-to =?iso-8859-1?q?Gary=20Cowell?= message dated "Fri, 18 Jun 2004 14:25:54 +0100" Date: Fri, 18 Jun 2004 10:57:07 -0400 Message-ID: <20412.1087570627@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/192 X-Sequence-Number: 7250 =?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. regards, tom lane