Date: Fri, 18 Apr 2008 12:35:04 +0200
To: "Gregory Stark" <stark@enterprisedb.com>,
	"Francisco Reyes" <lists@stringsutils.com>
Subject: Re: Group by more efficient than distinct?
From: PFC <lists@peufeu.com>
Cc: "Pgsql performance" <pgsql-performance@postgresql.org>
Content-Type: text/plain; format=flowed; delsp=yes; charset=utf-8
MIME-Version: 1.0
References: <cone.1208490368.136870.53107.1000@zoraida.natserv.net>
	<87ve2f7bel.fsf@oxford.xeocode.com>
Content-Transfer-Encoding: Quoted-Printable
Message-ID: <op.t9sycqo6cigqcu@apollo13.peufeu.com>
In-Reply-To: <87ve2f7bel.fsf@oxford.xeocode.com>
User-Agent: Opera Mail/9.24 (Linux)

On Fri, 18 Apr 2008 11:36:02 +0200, Gregory Stark <stark@enterprisedb.co=
m>  =

wrote:

> "Francisco Reyes" <lists@stringsutils.com> writes:
>
>> Is there any dissadvantage of using "group by" to obtain a unique lis=
t?
>>
>> On a small dataset the difference was about 20% percent.
>>
>> Group by
>> HashAggregate  (cost=3D369.61..381.12 rows=3D1151 width=3D8) (actual
>> time=3D76.641..85.167 rows=3D2890 loops=3D1)

	Basically :

	- If you process up to some percentage of your RAM worth of data, hashi=
ng  =

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 (dependi=
ng  =

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...