Re: Group by more efficient than distinct?

[PFC <lists(at)peufeu(dot)com>]

On Fri, 18 Apr 2008 11:36:02 +0200, Gregory Stark <stark(at)enterprisedb(dot)com>  
wrote:

> "Francisco Reyes" <lists(at)stringsutils(dot)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...