cockroachdb / cockroachdb/cockroach
Optimizer should always consider merge join query plans
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
**Is your feature request related to a problem? Please describe.**
For equijoin queries, currently the optimizer will not consider merge join query plans when both inputs to the join must be sorted. Recently some cases have been found where merge join with both inputs sorted is faster than hash join, e.g. from TPCH:
`select * from orders o1, orders o2 where o1.o_custkey = o2.o_custkey;`
**Describe the solution you'd like**
Extend function `GenerateMergeJoins` to consider merge join plans when a sort of both inputs is required.
**Describe alternatives you've considered**
Instead of always generating merge join plans for any equijoin, consider modifying the current heuristic rule which tries to predict the cases where it's thought hash join would always be better than merge join with a better heuristic, and do not generate merge joins in those cases.
Jira issue: CRDB-27218
Contributor guide
Research direction
Locate the GenerateMergeJoins function and read how it currently decides whether to generate merge join plans. Reproduce the TPCH-style equijoin from the issue and compare the generated plans, then verify that merge joins requiring sorts on both inputs are considered without losing existing plan choices.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100