cockroachdb / cockroachdb/cockroach

sql: index recs can include system columns

Open
#142,247 1 comment 0 reactions 0 assignees View on GitHub
branch-master branch-release-23.1 branch-release-23.2 branch-release-24.1 branch-release-24.2 branch-release-24.3 branch-release-25.1 branch-release-25.2 C-bug T-sql-queries
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

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.