Re: Aggregates with non-commutative transition functions

I said:
> A better alternative is to get the planner to notice in the context of
> the outer query that the inner query's result is already sorted by
> recnum. Then it wouldn't do the unwanted sort in any case. This has
> been on the to-do list for awhile, but hasn't risen to the top ...

Now it has ... as of CVS tip, you can do

regression=# create table tab(foo int, bar int, baz float);
CREATE TABLE
regression=# explain select foo, avg(baz) from
regression-# (select foo,baz from tab order by foo, bar) ss
regression-# group by foo;
QUERY PLAN
--------------------------------------------------------------------------
GroupAggregate (cost=69.83..77.83 rows=200 width=16)
-> Subquery Scan ss (cost=69.83..72.33 rows=1000 width=16)
-> Sort (cost=69.83..72.33 rows=1000 width=16)
Sort Key: foo, bar
-> Seq Scan on tab (cost=0.00..20.00 rows=1000 width=16)
(5 rows)

Note the lack of an extra sort above the subquery. This provides a
general technique for controlling the ordering of inputs to a
user-written aggregate function, even when grouping.

regards, tom lane