public inbox for [email protected]  

help / color / mirror / Atom feed

From: Peter Geoghegan <[email protected]>
To: Chris Cogdon <[email protected]>
Cc: [email protected]
Subject: Re: Adding a ROLLUP switches to GroupAggregate unexpectedly
Date: Sat, 9 Apr 2016 17:41:14 -0700
Message-ID: <CAH2-Wzmim=8+-NgnVMudZJuso2h2k-btxzK=Pe=uooURz7-s9g@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
List-Unsubscribe:  <mailto:[email protected]?body=unsub%20pgsql-performance>

On Thu, Mar 31, 2016 at 10:03 AM, Chris Cogdon <[email protected]> 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


-- 
Sent via pgsql-performance mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Adding a ROLLUP switches to GroupAggregate unexpectedly
  In-Reply-To: <CAH2-Wzmim=8+-NgnVMudZJuso2h2k-btxzK=Pe=uooURz7-s9g@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox