Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id 5B7929FB3C0 for ; Tue, 30 Jan 2007 12:34:24 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-new, port 10024) with ESMTP id 54092-10 for ; Tue, 30 Jan 2007 12:34:24 -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 8CEF39FA0E7 for ; Tue, 30 Jan 2007 12:34:22 -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 08:34:16 PST Received: from 172.16.1.92 by mail.mi8.com with ESMTP (- Welcome to Mi8 Corporation www.Mi8.com (D4)); Tue, 30 Jan 2007 11:34:07 -0500 X-Server-Uuid: C8FB4D43-1108-484A-A898-3CBCC7906230 Received: from MI8NYCMAIL08.Mi8.com ([172.16.1.164]) by d01smtp08.Mi8.com with Microsoft SMTPSVC(6.0.3790.1830); Tue, 30 Jan 2007 11:34:04 -0500 Received: from 63.80.24.130 ([63.80.24.130]) by MI8NYCMAIL08.Mi8.com ( [172.16.1.190]) via Exchange Front-End Server mi8owa.mi8.com ( [172.16.1.134]) with Microsoft Exchange Server HTTP-DAV ; Tue, 30 Jan 2007 16:34:04 +0000 User-Agent: Microsoft-Entourage/11.3.3.061214 Date: Tue, 30 Jan 2007 08:34:03 -0800 Subject: Re: Querying distinct values from a large table From: "Luke Lonergan" To: "Chad Wagner" cc: "Simon Riggs" , "Igor Lobanov" , "Richard Huxton" , pgsql-performance@postgresql.org Message-ID: Thread-Topic: [PERFORM] Querying distinct values from a large table Thread-Index: AcdEjHOqsiZFKbB/EduYNwAWy4o9DA== In-Reply-To: <81961ff50701300703r538cfd58o511ef68ee90a90e5@mail.gmail.com> MIME-Version: 1.0 X-OriginalArrivalTime: 30 Jan 2007 16:34:04.0692 (UTC) FILETIME=[74AC7D40:01C7448C] X-WSS-ID: 69A1ACF63C810826886-02-01 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200701/429 X-Sequence-Number: 23030 Chad, On 1/30/07 7:03 AM, "Chad Wagner" wrote: > On 1/30/07, Luke Lonergan wrote: >> 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. > > Was that integrated back into PostgreSQL, or is that part of Greenplum's > offering? Not yet, we will submit to PostgreSQL along with other executor node enhancements like hybrid hash agg (fixes the memory overflow problem with hash agg) and some other great sort work. These are all "cooked" and in the Greenplum DBMS, and have proven themselves significant on customer workloads with tens of terabytes already. For now it seems that the "Group By" trick Brian suggested in this thread combined with lots of work_mem may speed things up for this case if HashAgg is chosen. Watch out for misestimates of stats though - hash agg may overallocate RAM in some cases. >> I can't think of a way that indexing would help this situation given the >> required visibility check of each tuple. > > I agree, using indexes as a "skinny" table is a whole other feature that would > be nice. Yah - I like Hannu's ideas to make visibility less of a problem. We're thinking about this too. - Luke