Re: query optimization: aggregate and distinct

Jeff Davis <jdavis-pgsql@empires.org> writes:
> I had an idea about using aggregates: what if I made an aggregate function
> called "first" that just returned the value in the first tuple it
> encountered?

You could make that work in 7.4, but not in any existing releases.

The trouble is that you need something like

    SELECT first(foo) FROM (SELECT ... ORDER BY col1,col2) ss
    GROUP BY col1

and before 7.4 the optimizer doesn't realize that it can skip re-sorting
at the outer level.  So unless the sort is stable (which it won't be, on
most platforms anyway) the needed ordering by col2 within each group is
destroyed.

            regards, tom lane