From: Luke Lonergan <llonergan@greenplum.com>
To: Francisco Reyes <lists@stringsutils.com>
To: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: Group by more efficient than distinct?
Date: Sun, 20 Apr 2008 22:35:58 -0700
Message-ID: <C43175CE.5B957%llonergan@greenplum.com> (raw)
In-Reply-To: <cone.1208490368.136870.53107.1000@zoraida.natserv.net>
Hi Francisco,
Generally, PG sorting is much slower than hash aggregation for performing
the distinct operation. There may be small sizes where this isn¹t true, but
for large amounts of data (in-memory or not), hash agg (used most often, but
not always by GROUP BY) is faster.
We¹ve implemented a special optimization to PG sorting that does the
distinct processing within the sort, instead of afterward, but it¹s limited
to some small-ish number (10,000) of distinct values due to it¹s use of a
memory and processing intensive heap.
So, you¹re better off using GROUP BY and making sure that the planner is
using hash agg to do the work.
- Luke
On 4/17/08 8:46 PM, "Francisco Reyes" <lists@stringsutils.com> wrote:
> I am trying to get a distinct set of rows from 2 tables.
> After looking at someone else's query I noticed they were doing a group by
> to obtain the unique list.
>
> After comparing on multiple machines with several tables, it seems using
> group by to obtain a distinct list is substantially faster than using
> select distinct.
>
> Is there any dissadvantage of using "group by" to obtain a unique list?
>
> On a small dataset the difference was about 20% percent.
>
> Group by
> HashAggregate (cost=369.61..381.12 rows=1151 width=8) (actual
> time=76.641..85.167 rows=2890 loops=1)
>
> Distinct
> Unique (cost=1088.23..1174.53 rows=1151 width=8) (actual
> time=90.516..140.123 rows=2890 loops=1)
>
> Although I don't have the numbers here with me, a simmilar result was
> obtaining against a query that would return 100,000 rows. 20% and more
> speed differnce between "group by" over "select distinct".
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
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, lists@stringsutils.com
Subject: Re: Group by more efficient than distinct?
In-Reply-To: <C43175CE.5B957%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