MIME-Version: 1.0
In-Reply-To: <09FB46A1-B2CA-4C65-9959-D4D03C81EBB1@cogdon.org>
References: <09FB46A1-B2CA-4C65-9959-D4D03C81EBB1@cogdon.org>
From: Peter Geoghegan <pg@bowt.ie>
Date: Sat, 9 Apr 2016 17:41:14 -0700
Message-ID: 
 <CAH2-Wzmim=8+-NgnVMudZJuso2h2k-btxzK=Pe=uooURz7-s9g@mail.gmail.com>
Subject: Re: Adding a ROLLUP switches to GroupAggregate unexpectedly
To: Chris Cogdon <chris@cogdon.org>
Cc: pgsql-performance@postgresql.org
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

On Thu, Mar 31, 2016 at 10:03 AM, Chris Cogdon <chris@cogdon.org> wrote:
> Description: Adding a summary row by changing =E2=80=9CGROUP BY x=E2=80=
=9D into =E2=80=9CGROUP BY
> ROLLUP (x)=E2=80=9D should not cause a switch from HashAggregate to Group=
Aggregate

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.

--=20
Peter Geoghegan


--=20
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance