cockroachdb / cockroachdb/cockroach
sql: index recs can include system columns
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
It looks like some system columns are sneaking into an index recommendation here:
```sql
CREATE TABLE ab (a INT PRIMARY KEY, b INT);
CREATE TABLE cd (c INT, d INT);
INSERT INTO ab VALUES (1, 1);
INSERT INTO cd VALUES (1, 1), (1, 1);
EXPLAIN UPDATE ab SET b = b + 1 FROM cd WHERE a = c;
```
This shows:
```
demo@127.0.0.1:26257/demoapp/defaultdb> EXPLAIN UPDATE ab SET b = b + 1 FROM cd WHERE a = c;
info
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
distribution: local
vectorized: true
• update
│ table: ab
│ set: b
│ auto commit
│
└── • render
│
└── • distinct
│ estimated row count: 1
│ distinct on: a
│
└── • lookup join
│ estimated row count: 1
│ table: ab@ab_pkey
│ equality: (c) = (a)
│ equality cols are key
│
└── • scan
estimated row count: 1 (100% of the table; stats collected 3 seconds ago)
table: cd@cd_pkey
spans: FULL SCAN
index recommendations: 1
1. type: index creation
SQL command: CREATE INDEX ON defaultdb.public.cd (c) STORING (d, crdb_internal_mvcc_timestamp, tableoid, crdb_internal_origin_id, crdb_internal_origin_timestamp);
(28 rows)
```
Jira issue: CRDB-48238
Contributor guide
Assessment
This issue has not been assessed yet.