On Thu, Mar 31, 2016 at 10:03 AM, Chris Cogdon <chris@cogdon.org> wrote:
> Description: Adding a summary row by changing “GROUP BY x” into “GROUP BY
> ROLLUP (x)” should not cause a switch from HashAggregate to GroupAggregate
While this restriction has not been lifted for PostgreSQL 9.6,
external sorting will be much faster in 9.6. During benchmarking,
there were 2x-3x speedups in overall query runtime for many common
cases. This new performance optimization should ameliorate your ROLLUP
problem on 9.6, simply because the sort operation will be so much
faster.
Similarly, we have yet to make HashAggregates spill when they exceed
work_mem, which is another restriction on their use that we should get
around to fixing. As you point out, this restriction continues to be a
major consideration during planning, sometimes resulting in a
GroupAggregate where a HashAggregate would have been faster (even with
spilling of the hash table). However, simply having significantly
faster external sorts once again makes that restriction less of a
problem.
I have noticed that the old replacement selection algorithm that the
external sort would have used here does quite badly on low cardinality
inputs, too. I bet that was a factor here.
--
Peter Geoghegan
