Re: DISTINCT vs. GROUP BY

From: Tom Lane
Subject: Re: DISTINCT vs. GROUP BY
Date: ,
Msg-id: 25105.1265755101@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: DISTINCT vs. GROUP BY  (Dimi Paun)
Responses: Re: DISTINCT vs. GROUP BY  (Dimi Paun)
List: pgsql-performance

Tree view

DISTINCT vs. GROUP BY  (Dimi Paun, )
 Re: DISTINCT vs. GROUP BY  (Thom Brown, )
 Re: DISTINCT vs. GROUP BY  (Tom Lane, )
  Re: DISTINCT vs. GROUP BY  (Dimi Paun, )

Dimi Paun <> 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


pgsql-performance by date:

From: Bryce Nesbitt
Date:
Subject: 512,600ms query becomes 7500ms... but why? Postgres 8.3 query planner quirk?
From: Amitabh Kant
Date:
Subject: Re: PostgreSQL - case studies