From: Luke Lonergan <llonergan@greenplum.com>
To: Chad Wagner <chad.wagner@gmail.com>
Cc: Simon Riggs <simon@2ndquadrant.com>
Cc: Igor Lobanov <ilobanov@swsoft.com>
Cc: Richard Huxton <dev@archonet.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Querying distinct values from a large table
Date: Tue, 30 Jan 2007 08:34:03 -0800
Message-ID: <C1E4B37B.199C4%llonergan@greenplum.com> (raw)
In-Reply-To: <81961ff50701300703r538cfd58o511ef68ee90a90e5@mail.gmail.com>
Chad,
On 1/30/07 7:03 AM, "Chad Wagner" <chad.wagner@gmail.com> wrote:
> On 1/30/07, Luke Lonergan <llonergan@greenplum.com> 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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: llonergan@greenplum.com, chad.wagner@gmail.com, simon@2ndquadrant.com, ilobanov@swsoft.com, dev@archonet.com
Subject: Re: Querying distinct values from a large table
In-Reply-To: <C1E4B37B.199C4%llonergan@greenplum.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox