cockroachdb / cockroachdb/cockroach

Flag duplicate indexes in tables in Index Recommendations and Schema Insights

Open
#114,082 0 comments 0 reactions 0 assignees View on GitHub
A-cluster-observability C-enhancement T-observability
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.**
CRDB currently gives index recommendations for various scenarios including adding missing indexes or removing unused indexes. I think it would be helpful (and probably pretty simple) to flag these:

**Describe the solution you'd like**
If a table exists like this:
```
CREATE TABLE x (
id int primary key,
type string,
INDEX ix1 ( type ASC ),
INDEX ix2 ( type ASC )
);
```
(i.e., where there are two or more indexes with the exact same index definition) that we could flag these and recommend that these duplicates be dropped.

**Describe alternatives you've considered**
I suppose one could write a query to hit crdb_internal tables and find these and run it every once in a while.

Jira issue: CRDB-33328

Contributor guide

Open the contributing guide

Research direction

The issue names Index Recommendations and Schema Insights but no files or tests. Start by locating those components and their existing missing- and unused-index recommendation paths. Done means tables with exact duplicate index definitions are flagged and duplicate indexes are recommended for removal.

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
Stale
Clarity
Mostly clear
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.