From: PFC <lists@peufeu.com>
To: Gregory Stark <stark@enterprisedb.com>
To: Francisco Reyes <lists@stringsutils.com>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: Group by more efficient than distinct?
Date: Fri, 18 Apr 2008 12:35:04 +0200
Message-ID: <op.t9sycqo6cigqcu@apollo13.peufeu.com> (raw)
In-Reply-To: <87ve2f7bel.fsf@oxford.xeocode.com>
References: <cone.1208490368.136870.53107.1000@zoraida.natserv.net>
<87ve2f7bel.fsf@oxford.xeocode.com>
On Fri, 18 Apr 2008 11:36:02 +0200, Gregory Stark <stark@enterprisedb.com>
wrote:
> "Francisco Reyes" <lists@stringsutils.com> writes:
>
>> 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)
Basically :
- If you process up to some percentage of your RAM worth of data, hashing
is going to be a lot faster
- If the size of the hash grows larger than your RAM, hashing will fail
miserably and sorting will be much faster since PG's disksort is really
good
- GROUP BY knows this and acts accordingly
- DISTINCT doesn't know this, it only knows sorting, so it sorts
- If you need DISTINCT x ORDER BY x, sorting may be faster too (depending
on the % of distinct rows)
- If you need DISTINCT ON, well, you're stuck with the Sort
- So, for the time being, you can replace DISTINCT with GROUP BY...
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: lists@peufeu.com, stark@enterprisedb.com, lists@stringsutils.com
Subject: Re: Group by more efficient than distinct?
In-Reply-To: <op.t9sycqo6cigqcu@apollo13.peufeu.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