Repository navigation
Optimize inner joins to semi joins when possible #22594
Description
Is your feature request related to a problem or challenge?
We can convert inner joins to semi joins automatically in some cases. For example, we can convert an inner join to a left semi join if
- (1) Nothing above the join uses a column from the RHS of the join, and EITHER
- (2) (A) The plan above the join is duplicate-insensitive (e.g.,
DISTINCTor someGROUP BYs), OR - (2) (B) We can use functional dependencies to prove that the RHS is unique on the join keys
And symmetrically for inner -> right semi for LHS.
Many of the TPC-DS queries would benefit from this. Plans in 2(A) generally benefit more because we're able to avoid producing duplicate tuples in the first place; 2(B) is a smaller win but still useful.
Describe the solution you'd like
No response
Describe alternatives you've considered
No response
Additional context
No response
Activity
neilconway commentedon May 28, 2026
take
[-]Convert inner joins -> semi joins[/-][+]Convert inner joins to semi joins[/+]neilconway commentedon May 29, 2026
Working on this, there are some shortcomings we'll probably need to resign ourselves to in the initial implementation:
SELECT a.x, max(b.y) FROM a, b WHERE a.y = b.y GROUP BY a.x;should be optimizable, but we don't currently realize thatb.yis equivalent toa.y, and sobdoesn't contribute any columns to the parent plan.maxis duplicate-insensitive. Support identifying when an aggregate function is duplicate-insensitive #22644[-]Convert inner joins to semi joins[/-][+]Optimize inner joins to semi joins when possible[/+]perf: Convert inner joins to semi joins when equivalent (apache#22652)
perf: Convert inner joins to semi joins when equivalent (apache#22652)