Re: Aggregates with non-commutative transition functions - Mailing list pgsql-general

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