dolthub / dolthub/dolt

Join planner chooses suboptimal plan for lookup join

Open
#8,924 0 comments 0 reactions 0 assignees View on GitHub
analyzer
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.