Re: DISTINCT/Optimizer question

Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> On the other side of the coin, there's the analogy to GROUP BY that Greg
> points out --- there's some duplicated functionality there, but again it
> doesn't carry over to DISTINCT ON, AFAICS.

The equivalent query for:

SELECT DISTINCT ON (x,y) a,b,c FROM ... ORDER BY x,y,z

is:

SELECT x,y,z,first(a),first(b),first(c) FROM (
SELECT x,y,z,a,b,c FROM ... ORDER BY x,y,z
) GROUP BY x,y

Getting the optimizer to treat this as well as DISTINCT ON would be quite a
trick. It would probably require the same machinery as getting min() and max()
to take maximum advantage of indexes in the face of a GROUP BY clause. That is
some sort of field for aggregate functions indicating what subset of the
records is sufficient for them and what path they would need for that to be
the case.

In any case I don't see how you get DISTINCT ON to work without sorting. For
min() and max() they could indicate they only need the first field if the
input is sorted and the optimizer could decide it's cheaper to pass them every
record that do the sort. For first() and last() they would tell the optimizer
they only need the first or last record with no particular path but that only
works because the rewritten query has an explicit ORDER BY clause.

That's about as far as I've thought about it. At the time I thought it would
likely be too hard for a first project. I suspect it's too hard for a second
project for that matter.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com