Sort-based grouping and aggregation

10/01/2020
by   Thanh Do, et al.
0

Database query processing requires algorithms for duplicate removal, grouping, and aggregation. Three algorithms exist: in-stream aggregation is most efficient by far but requires sorted input; sort-based aggregation relies on external merge sort; and hash aggregation relies on an in-memory hash table plus hash partitioning to temporary storage. Cost-based query optimization chooses which algorithm to use based on several factors including input and output sizes, the sort order of the input, and the need for sorted output. For example, hash-based aggregation is ideal for small output (e.g., TPC-H Query 1), whereas sorting the entire input and aggregating after sorting are preferable when both aggregation input and output are large and the output needs to be sorted for a subsequent operation such as a merge join. Unfortunately, the size information required for a sound choice is often inaccurate or unavailable during query optimization, leading to sub-optimal algorithm choices. To address this challenge, this paper introduces a new algorithm for sort-based duplicate removal, grouping, and aggregation. The new algorithm always performs at least as well as both traditional hash-based and traditional sort-based algorithms. It can serve as a system's only aggregation algorithm for unsorted inputs, thus preventing erroneous algorithm choices. Furthermore, the new algorithm produces sorted output that can speed up subsequent operations. Google's F1 Query uses the new algorithm in production workloads that aggregate petabytes of data every day.

READ FULL TEXT

page 1

page 2

page 3

page 4

research
09/30/2022

Offset-value coding in database query processing

Recent work shows how offset-value coding speeds up database query execu...
research
05/08/2023

Parallel External Sorting of ASCII Records Using Learned Models

External sorting is at the core of many operations in large-scale databa...
research
09/17/2022

Robust and Efficient Sorting with Offset-Value Coding

Sorting and searching are large parts of database query processing, e.g....
research
06/13/2019

Memory-Efficient Group-by Aggregates over Multi-Way Joins

Aggregate computation in relational databases has long been done using t...
research
08/21/2019

GeoBlocks: A Query-Driven Storage Layout for Geospatial Data

City authorities need to analyze urban geospatial data to improve transp...
research
08/08/2023

Defending Hash Tables from Subterfuge with Depth Charge

We consider the problem of defending a hash table against a Byzantine at...
research
05/26/2023

Aggregating over Dominated Points by Sorting, Scanning, Zip and Flat Maps

Prefix aggregation operation (also called scan), and its particular case...

Please sign up or login with your details

Forgot password? Click here to reset