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 CF674D1B209 for ; Tue, 22 Jun 2004 02:12:50 -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 13626-06 for ; Tue, 22 Jun 2004 05:12:50 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 7B59CD1B1CF for ; Tue, 22 Jun 2004 02:12:48 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i5M5CmQC046504 for ; Tue, 22 Jun 2004 05:12:48 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i5M4ki8m040101 for pgsql-performance@postgresql.org; Tue, 22 Jun 2004 04:46:44 GMT From: "Mischa Sandberg" X-Newsgroups: comp.databases.postgresql.performance References: <20040618132554.72968.qmail@web25103.mail.ukl.yahoo.com> <20412.1087570627@sss.pgh.pa.us> <20040618163003.GM11196@ns.snowman.net> <22514.1087578111@sss.pgh.pa.us> Subject: Re: Major differences between oracle and postgres performance - what can I do ? Lines: 31 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1106 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106 Message-ID: Date: Tue, 22 Jun 2004 04:46:44 GMT To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.8 tagged_above=0.0 required=5.0 tests=PRIORITY_NO_NAME X-Spam-Level: X-Archive-Number: 200406/296 X-Sequence-Number: 7354 Don't know about Oracle, but select-distinct in MSSQL2K will indeed throw away duplicates, which chops the CPU time. Very easy to see in the graphic query plan, both in terms of CPU and the number of rows retrieved from a single-node or nested-loop subtree. Definitely a worthwhile optimization. "Tom Lane" wrote in message news:22514.1087578111@sss.pgh.pa.us... > Stephen Frost writes: > > * Tom Lane (tgl@sss.pgh.pa.us) wrote: > >> [... 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. > > > Could this optimization be added to PostgreSQL? It sounds like a very > > reasonable thing to do. > > That's what I was wondering about too. But first I'd like to get > some kind of reading on how effective it would be. If someone can > demonstrate that Oracle can do sort-and-drop-dups a lot faster than > it can do a straight sort of the same amount of input data, that > would be a strong indication that it's worth doing. At this point > we don't know if that's the source of their win or not. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >