Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 6D69D9FB402 for ; Tue, 30 Jan 2007 10:57:29 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-new, port 10024) with ESMTP id 00630-01 for ; Tue, 30 Jan 2007 10:57:23 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from exprod6og50.obsmtp.com (exprod6og50.obsmtp.com [64.18.1.181]) by postgresql.org (Postfix) with SMTP id 601229FB404 for ; Tue, 30 Jan 2007 10:57:28 -0400 (AST) Received: from source ([63.240.6.44]) (using TLSv1) by exprod6ob50.postini.com ([64.18.5.12]) with SMTP; Tue, 30 Jan 2007 06:57:15 PST Received: from 172.16.1.239 by mail.mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D4)); Tue, 30 Jan 2007 09:57:01 -0500 X-Server-Uuid: C8FB4D43-1108-484A-A898-3CBCC7906230 Received: from MI8NYCMAIL08.Mi8.com ([172.16.1.164]) by d01smtp06.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Tue, 30 Jan 2007 09:56:59 -0500 Received: from 69.181.100.71 ([69.181.100.71]) by MI8NYCMAIL08.Mi8.com ( [172.16.1.190]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.106]) with Microsoft Exchange Server HTTP-DAV ; Tue, 30 Jan 2007 14:56:58 +0000 User-Agent: Microsoft-Entourage/11.3.3.061214 Date: Tue, 30 Jan 2007 06:56:57 -0800 Subject: Re: Querying distinct values from a large table From: "Luke Lonergan" To: "Chad Wagner" , "Simon Riggs" cc: "Igor Lobanov" , "Richard Huxton" , pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] Querying distinct values from a large table Thread-Index: AcdEfuMZIfD/ebByEduYNwAWy4o9DA== In-Reply-To: <81961ff50701300613g25ea4ce6jb357c82fb1ed6733@mail.gmail.com> MIME-Version: 1.0 X-OriginalArrivalTime: 30 Jan 2007 14:56:59.0106 (UTC) FILETIME=[E45A9C20:01C7447E] X-WSS-ID: 69A183363C810744787-01-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/427 X-Sequence-Number: 23028 Chad, On 1/30/07 6:13 AM, "Chad Wagner" wrote: > Sounds like an opportunity to implement a "Sort Unique" (sort of like a hash, > I guess), there is no need to push 3M rows through a sort algorithm to only > shave it down to 1848 unique records. > > I am assuming this optimization just isn't implemented in PostgreSQL? Not that it helps Igor, but we've implemented single pass sort/unique, grouping and limit optimizations and it speeds things up to a single seqscan over the data, from 2-5 times faster than a typical external sort. I can't think of a way that indexing would help this situation given the required visibility check of each tuple. - Luke