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 AF689D1B298 for ; Fri, 18 Jun 2004 12:47:28 -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 33714-06 for ; Fri, 18 Jun 2004 15:47:24 +0000 (GMT) Received: from web25105.mail.ukl.yahoo.com (web25105.mail.ukl.yahoo.com [217.12.10.53]) by svr1.postgresql.org (Postfix) with SMTP id 6FD85D1B241 for ; Fri, 18 Jun 2004 12:47:22 -0300 (ADT) Message-ID: <20040618154721.37105.qmail@web25105.mail.ukl.yahoo.com> Received: from [217.158.107.104] by web25105.mail.ukl.yahoo.com via HTTP; Fri, 18 Jun 2004 16:47:21 BST Date: Fri, 18 Jun 2004 16:47:21 +0100 (BST) From: =?iso-8859-1?q?Gary=20Cowell?= Subject: Re: Major differences between oracle and postgres performance - what can I do ? To: pgsql-performance@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit 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/196 X-Sequence-Number: 7254 > Try increasing sort_mem temporarily, and see if that > makes a difference: > SET sort_mem = 64000; > EXPLAIN ANALYSE ... I did this (actualy 65536) and got the following: pvcsdb=# explain analyze select distinct version from vers where version is not null; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Unique (cost=117865.77..120574.48 rows=142 width=132) (actual time=81595.178..86573.228 rows=536 loops=1) -> Sort (cost=117865.77..119220.13 rows=541741 width=132) (actual time=81595.169..84412.069 rows=541741 loops=1) Sort Key: "version" -> Seq Scan on vers (cost=0.00..21367.41 rows=541741 width=132) (actual time=10.068..7397.374 rows=541741 loops=1) Filter: ("version" IS NOT NULL) Total runtime: 86647.495 ms (6 rows) In response to Tom Lane, I have compared a select/order by on the same data in Oracle and PG to see if this changes things: PG: Time: 67438.536 ms 541741 rows Oracle: After an hour and a half I canned it So it seems the idea that oracle is dropping duplicate rows prior to the sort when using distinct may indeed be the case.