cockroachdb / cockroachdb/cockroach

opt/memo: improve estimation of anti join row count

Open
#137,379 0 comments 0 reactions 0 assignees View on GitHub
branch-master branch-release-22.2 branch-release-23.1 branch-release-23.2 branch-release-24.1 branch-release-24.2 branch-release-24.3 branch-release-25.1 C-bug O-support P-3 T-sql-queries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.