Re: DISTINCT vs. GROUP BY

Dimi Paun <dimi(at)lattica(dot)com> writes:
>> From what I've read on the net, these should be very similar,
> and should generate equivalent plans, in such cases:

> SELECT DISTINCT x FROM mytable
> SELECT x FROM mytable GROUP BY x

> However, in my case (postgresql-server-8.1.18-2.el5_4.1),
> they generated different results with quite different
> execution times (73ms vs 40ms for DISTINCT and GROUP BY
> respectively):

The results certainly ought to be the same (although perhaps not with
the same ordering) --- if they aren't, please provide a reproducible
test case.

As for efficiency, though, 8.1 didn't understand how to use hash
aggregation for DISTINCT. Less-obsolete versions do know how to do
that.

regards, tom lane