The ability to exploit hashed aggregation within sorted groups, when the order
of the input stream can be exploited this way, is potentially a useful way to
improve aggregation performance more generally. This would potentially be
beneficial when 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
the cumulative 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,
then hashing the remaining combinations within each sorted group might be done
in memory, at the cost of rebuilding the hash table 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 choose the aggregation method as sorted,
hashed, or mixed hash/sort should be integrated into the cost model, but given
the notorious difficulty of estimating intermediate cardinalities accurately it
would be difficult to develop a cardinality model 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-ow...@postgresql.org on behalf of Robert
Haas" <pgsql-hackers-ow...@postgresql.org on behalf of robertmh...@gmail.com>
wrote:
On Thu, Jan 5, 2017 at 10:48 PM, Andrew Gierth
<and...@tao11.riddles.org.uk> wrote:
> Herewith a patch for doing grouping sets 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
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
