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

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

> As a real-world example of why I won't hold still for hard-wiring this:
> a complex-number data type might have btree opclasses allowing it to be
> sorted either by real part or by absolute value. One might then define
> max_real() and max_abs() aggregates on the type. It should be possible
> to optimize such aggregates the same way as any other max() aggregate.

So if the max_real() aggregate had a field that indicated that max_real(x)
could be satisfied with only the first record from the dataset as long as it's
sorted by "real(x)" that would be enough information.

The optimizer would still have a lot of work to combine this information for
all aggregates used and check the costs for providing sorted result sets to
the scan. There would also need new scans that could handle reading just one
record and then skipping to the next group.

It's a lot of work but it would make a lot of aggregates a lot more useful. It
would also make it possible to deprecate DISTINCT ON in favour of GROUP BY
with first() calls.

--
greg