Azure / Azure/usql

Query Execution Puzzle Joins and aggregation

Open
#71 0 comments 0 reactions 0 assignees View on GitHub
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.