Re: Performance aggregates

snpe <snpe(at)infosky(dot)net> writes:
> select roba,sum(izn)
> from e_kalkn k,e_kalkns ks
> where k.id=ks.id
> group by roba
> order by roba
>
> is 2.5 times faster on one commercial database

That other DBMS is probably implementing the GROUP BY + aggregate
using a hash table to compute all the aggregates in parallel,
rather than sorting first as Postgres does. You still need to sort
in the end to meet the ORDER BY spec, but you are only sorting the
aggregate results not the inputs. Disadvantage: if there are a lot
of distinct values of ROBA then your hash table may overrun memory.

We have a TODO item to implement hashed aggregation. Teaching the
planner to make an intelligent choice between sorted and hashed
aggregation will probably be harder than the actual execution code.

regards, tom lane