cockroachdb / cockroachdb/cockroach
opt: propagate histograms through Project operators in the statistics builder
- 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.**
`colStatProject` copies `DistinctCount` and `NullCount` from its input, but never propagates the `Histogram` — even for pure pass-through columns ([statistics_builder.go:1184](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/opt/memo/statistics_builder.go#L1184)). Any filter whose selectivity is estimated above a Project boundary loses access to histograms and falls back to distinct-count-based or default selectivity estimates. Unlike the join case ([statistics_builder.go:1618](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/opt/memo/statistics_builder.go#L1618), tracked in #41204), the omission has no explanatory comment and no tracking issue.
This gap is usually hidden because normalization pushes filters below projections, so selects typically end up directly on scans, where histograms are available (`colStatScan` propagates them, [statistics_builder.go:1100](https://github.com/cockroachdb/cockroach/blob/master/pkg/sql/opt/memo/statistics_builder.go#L1100)). It becomes visible whenever a projection boundary is pinned in place and columns are consumed above it:
- **Non-inlined CTEs whose binding is rooted at a Project.** #172206 added histogram propagation through WithScan, but that only helps when the bound expression's root exposes a histogram. A binding like `WITH foo AS MATERIALIZED (SELECT x+1 AS z, y FROM t)` that keeps a Project at its root still loses the histogram on the pass-through column `y` before the WithScan can propagate it.
- **FK and uniqueness check inputs.** Check-input WithScans are bound to the mutation input, which is almost always rooted at a Project (synthesizing insert/update values), so check plans never see histograms on the checked columns.
**Describe the solution you'd like**
For single-column colSets that are passed through unchanged, copy the input histogram in `colStatProject`, following the WithScan approach from #172206 (no column remapping is needed here since pass-through columns keep their IDs). Columns synthesized by projections would still have no histogram, matching the existing assumption that a synthesized column inherits its input's distinct count.
Since this changes cardinality estimates and therefore potentially plans, it should be gated behind a session setting (default on) per the usual `optimizer_use_improved_*` convention, so backports can default it off.
**Describe alternatives you've considered**
Continue relying on filter pushdown to hide the gap. That covers most queries but not the pinned-boundary cases above. The broader alternative is full histogram support across all operators (discussed in #41204), which subsumes this but is a much larger effort.
**Additional context**
Related: #41204 (histograms for joins / all operators), #172206 (histograms through WithScan), #49698 (multi-column histograms).
Jira issue: CRDB-65635
Contributor guide
Research direction
Start in pkg/sql/opt/memo/statistics_builder.go at colStatProject, then compare the histogram propagation approach in the WithScan work from #172206 and the join case near line 1618. Trace pass-through and synthesized columns, and identify the usual optimizer_use_improved_* setting pattern. Done means pass-through single-column projections retain input histograms while synthesized columns do not, with the behavior gated by a default-on session setting.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- go, sql
- Domain
- databases
- Issue type
- Feature
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 52/100