Query Execution Puzzle Joins and aggregation
- Dominant language
- C#
- Stars
- 231
- Forks
- 666
- PR merge metrics
- No merged PRs in 30d
Description
My files are large PUMS population files from the US Census bureau (https://www.census.gov/programs-surveys/acs/data/pums.html). There is a St (state code column). Each file is handled by multiple vertices. The relevant part of the query is:
@sum = SELECT St, SUM(Adjinc) AS TotalIncome, COUNT(*) AS NumRows FROM @tbl
GROUP BY St;
When I run this query, the execution engine behaves as expected, creating hash tables when processing each vertex and writing between 12 and 16 rows for each vertex and finally combining the hash tables into one hash table output with 52 rows (50 states plus DC and PR).
However if I use an INNER JOIN the plan is different.
@sum =SELECT St, s.statenum, SUM(Adjinc) AS TotalIncome, COUNT(*) AS NumRows FROM @tbl t
INNER JOIN @state s
ON St = s.statenum
GROUP BY St, s.statenum;
Each vertex passes to the vertex that combines the results a result set that contains several hundred thousand rows. My contention is that the inner join can only reduce the number of rows output, it cannot increase the number of rows output, thus I would suggest that the optimizer apply the hashing in the vertices that first touch the data. The join to statenum would then potentially reduce the number of rows output.
By the way, the following works as I had hoped:
@sum = SELECT St, SUM(Adjinc) AS TotalIncome, COUNT(*) AS NumRows FROM @tbl
GROUP BY St;
@out = SELECT * FROM @sum t
INNER JOIN @state s
ON t.St = s.statenum;
Contributor guide
Assessment
This issue has not been assessed yet.