Join planner chooses suboptimal plan for lookup join
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 5h
- Merged PRs (30d)
- 108
Description
https://github.com/dolthub/go-mysql-server/pull/2868 exposed a potentially join costing bug. In each of the modified PlanTests, the engine is picking a strictly worse index than before.
Basically, the planner is evaluating a where clause that binds the `orgId`, `name`, and `val` columns to constants. So it needs to pick between two different indexes:
- The non-unique index `(orgId, name, val)`
- The prefix `(orgId, name)` on the unique index `(orgId, name, assetId)` (plus a filter for the `val` column.)
The former is the strictly better choice because it only matches the relevant rows and doesn't require an additional filter. But as we can see in the diffs of the `ExpectedEstimates` fields, the coster is computing a lower cost for the latter.
Prior to the linked PR, the planner was picking the correct index for the wrong reason: it was treating the index as unique and incorrectly determining that it would have at most one row, overriding the cost comparison. Fixing this issue allowed the planner to compare the costs and pick the worse index.
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.