cockroachdb / cockroachdb/cockroach

opt: use existing partial indexes to recommend new partial indexes

Open
#143,425 1 comment 0 reactions 0 assignees View on GitHub
A-partial-indexes A-sql-explain C-enhancement E-quick-win T-sql-queries
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.**
The index recommendation engine currently does not recommend partial indexes, since it's not clear what predicates should be considered for the index predicate. However, if the table being used in a query already has a partial index defined on it, the index recommendation engine can start from this predicate and possibly consider changing the key columns or stored columns in the index.

**Describe the solution you'd like**
The index recommendation engine should create candidate partial indexes by using the predicates from any partial indexes already defined on the table. It may be possible to suggest a replacement for the existing partial indexes that would use different key columns to better constrain a scan, or avoid an index join with additional storing columns.

**Additional context**
Related slack thread: https://cockroachlabs.slack.com/archives/C01RX2G8LT1/p1742836553047329

Jira issue: CRDB-48827

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.