cockroachdb / cockroachdb/cockroach
sql: FK check reads during insert fast path are not included in `index_usage_statistics`
- 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
Assessment
This issue has not been assessed yet.