Alban Hertroys <alban@magproductions.nl> writes:
> Ordering the data before aggregating should do the trick though, but
> you'll need to order your records in the subquery. Something like this,
> I think:
> Select a, aggregate(b)
> from (
> select a, b
> from c
> order by a,b
> ) d
> group by a
Right. The key point there is that the subquery is already delivering
outputs that are suitably sorted for grouping by A, and so even if the
planner wants to do it via group rather than hash aggregation, it will
not need to put in another sort step. You can't just "order by B" in
the subquery, or it won't work.
This should work reliably since [ checks CVS... ] PG 7.4. Just to be
sure, you might try looking at the query plan with "set enable_hashagg =
off" to verify that the planner recognizes it doesn't need a second sort
step.
regards, tom lane