Re: DISTINCT vs. GROUP BY

Neil Conway <neilc(at)samurai(dot)com> writes:

> On Mon, 2005-19-09 at 16:27 +0200, Hans-Jürgen Schönig wrote:
> > I was wondering whether it is possible to teach the planner to handle
> > DISTINCT in a more efficient way:
> [...]
> > Isn't it possible to perform the same operation using a
> > HashAggregate?
>
> One problem is that DISTINCT ON is defined to return the first unique
> row (according to the query's ORDER BY) for the set of DISTINCT ON
> columns, which can't easily be done via hashing.

Uhm. Sure it can.

DISTINCT is really just special a case of GROUP BY. Even DISTINCT ON is just
GROUP BY with a kind of "first()" aggregate function. What would be really
neat would be to teach GROUP BY about first() and last() and how it can skip
over some index entries and still satisfy the query. Then make DISTINCT and
DISTINCT ON be handled through the exact same code path.

For bonus points teach it that min() and max() can sometimes be treated the
same way if the path is presenting records sorted on that column.

--
greg