cockroachdb / cockroachdb/cockroach
opt/memo: improve estimation of anti join row count
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
We currently hard-code the row count produced by an anti-join as 1e-10:
https://github.com/cockroachdb/cockroach/blob/a55295b0222cf4b8aed60315603c0b627c5ee9da/pkg/sql/opt/memo/statistics_builder.go#L1331-L1335
This can be problematic because it could lead to choosing suboptimal plans. For example, in a [support](https://github.com/cockroachlabs/support/issues/3146) ticket we had a case where this poor estimate resulted in choosing a lookup into a secondary index (only based on `crdb_region` column) in the FK check even though we had the PK values available and should've performed the PK column.
We should improve the estimation overall. Perhaps as a smaller item we can improve how we propagate this estimate into the WithScan to avoid bad FK check plans.
Jira issue: CRDB-45543
Contributor guide
Assessment
This issue has not been assessed yet.