Re: MAX/MIN optimization via rewrite (plus query rewrites generally)

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Greg Stark <gsstark@mit.edu> writes:
> > It would also make it possible to deprecate DISTINCT ON in favour of GROUP BY
> > with first() calls.
> 
> Oh?  How is a first() aggregate going to know what sort order you want
> within the group?  AFAICS first() is only useful when you honestly do
> not care which group member you get ... which is certainly not the case
> for applications of DISTINCT ON.

It would look something like

select x,first(a),first(b) from (select x,a,b from table order by x,y) group by x

which is equivalent to

select DISTINCT ON (x) x,a,b from table ORDER BY x,y

The group by can see that the subquery is already sorted by x and doesn't need
to be resorted. In fact I believe you added the smarts to detect that
condition in response to a user asking about precisely this type of scenario.

This is actually more general than DISTINCT ON since DISTINCT ON is basically
a degenerate case of the above where the _only_ aggregate allowed is first().
The more general case could have first() as well as other aggregates, though
obviously they would make it unlikely that any optimizations would be
applicable.

I do kind of like the DISTINCT ON syntax, but the inability to use any other
aggregate functions makes me often have to convert queries I originally wrote
to use it to use the more general GROUP BY and first() instead.

-- 
greg