cockroachdb / cockroachdb/cockroach

sql: FK check reads during insert fast path are not included in `index_usage_statistics`

Open
#104,143 6 comments 0 reactions 0 assignees View on GitHub
C-bug T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

When an insert fast path performs a FK check using a foreign index, the read on that index is not tracked in `crdb_internal.index_usage_statistics`.

```sql
CREATE TABLE p (
id INT PRIMARY KEY
);

CREATE TABLE c (
id INT PRIMARY KEY,
p_id int REFERENCES p(id) ON UPDATE CASCADE,
INDEX (p_id)
);

INSERT INTO p VALUES (1);

INSERT INTO c VALUES (1, 1);

SELECT * FROM crdb_internal.index_usage_statistics;
-- table_id | index_id | total_reads | last_read
-- -----------+----------+-------------+------------
-- 104 | 1 | 0 | NULL
-- 105 | 1 | 0 | NULL
-- 105 | 2 | 0 | NULL
```

The primary index of `p` (`table_id = 104, index_id = 1`) should have `total_reads = 1` because it must be read in order to check that the row inserted into `c` references an existing row in `p`.

```sql
EXPLAIN
INSERT INTO c VALUES (1, 1);
-- info
-- ----------------------------
-- distribution: local
-- vectorized: true
--
-- • insert fast path
-- into: c(id, p_id)
-- auto commit
-- FK check: p@p_pkey
-- size: 2 columns, 1 row
-- (8 rows)
```

Adding @cockroachdb/cluster-observability for visibility.

Jira issue: CRDB-28370

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.