The ability to exploit hashed aggregation within sorted groups, when the order of the input stream can be exploited
thisway, is potentially a useful way to improve aggregation performance more generally. This would potentially be
beneficialwhen the input size is expected to be larger than the amount of working memory available for hashed
aggregation,but where there is enough memory to hash-aggregate just the unsorted grouping key combinations, and when
thecumulative cost of rebuilding the hash table for each sorted subgroup is less than the cost of sorting the entire
input. In other words, if most of the grouping key combinations are already segregated by virtue of the input order,
thenhashing the remaining combinations within each sorted group might be done in memory, at the cost of rebuilding the
hashtable for each sorted subgroup.
I haven’t looked at the code for this change yet (I hope I will have the time to do that). Ideally the decision to
choosethe aggregation method as sorted, hashed, or mixed hash/sort should be integrated into the cost model, but given
thenotorious difficulty of estimating intermediate cardinalities accurately it would be difficult to develop a
cardinalitymodel and a cost model accurate enough to choose among these options consistently well.
Jim Finnerty
Amazon Corp.
On 1/10/17, 10:22 AM, "pgsql-hackers-owner@postgresql.org on behalf of Robert Haas" <pgsql-hackers-owner@postgresql.org
onbehalf of robertmhaas@gmail.com> wrote:
On Thu, Jan 5, 2017 at 10:48 PM, Andrew Gierth <andrew@tao11.riddles.org.uk> wrote: > Herewith a patch for doing
groupingsets via hashing or mixed hashing > and sorting. Cool. -- Robert Haas EnterpriseDB:
http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list
(pgsql-hackers@postgresql.org) To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers