cockroachdb / cockroachdb/cockroach
opt: skip superfluous FK checks within FK cascades
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
When we plan a FK cascade, the cascade plan includes a check of the FK that caused the cascade in the first place. I think we can safely elide this check.
Here's a demonstration using master (`v24.1.0-alpha.00000000-dev`):
```sql
CREATE TABLE p (id INT PRIMARY KEY);
CREATE TABLE c (id INT PRIMARY KEY, pid INT REFERENCES p (id) ON UPDATE CASCADE ON DELETE CASCADE);
INSERT INTO p VALUES (4);
INSERT INTO c VALUES (44, 4);
EXPLAIN ANALYZE UPDATE p SET id = 5 WHERE id = 4;
```
The update plan now includes the plan for the cascade to `c` which checks `p`. We can skip this check of `p`. (Of course, if `c` had other FK checks we should not skip those unless we can prove that they are also superfluous.)
```
demo@127.0.0.1:26257/demoapp/defaultdb> EXPLAIN ANALYZE UPDATE p SET id = 5 WHERE id = 4;
info
-------------------------------------------------------------------------------------------------------
planning time: 477µs
execution time: 4ms
distribution: local
vectorized: true
rows decoded from KV: 3 (53 B, 3 gRPC calls)
cumulative time spent in KV: 2ms
maximum memory usage: 90 KiB
network usage: 0 B (0 messages)
regions: us-east1
estimated RUs consumed: 0
isolation level: serializable
priority: normal
quality of service: regular
• root
│
├── • update
│ │ nodes: n1
│ │ regions: us-east1
│ │ actual row count: 1
│ │ table: p
│ │ set: id
│ │
│ └── • buffer
│ │ nodes: n1
│ │ regions: us-east1
│ │ actual row count: 1
│ │ label: buffer 1
│ │
│ └── • render
│ │
│ └── • scan
│ nodes: n1
│ regions: us-east1
│ actual row count: 1
│ KV time: 767µs
│ KV contention time: 0µs
│ KV rows decoded: 1
│ KV bytes read: 11 B
│ KV gRPC calls: 1
│ estimated max memory allocated: 20 KiB
│ estimated row count: 1 (100% of the table; stats collected 5 minutes ago)
│ table: p@p_pkey
│ spans: [/4 - /4]
│ locking strength: for update
│
└── • fk-cascade
│ fk: c_pid_fkey
│
└── • root
│
├── • update
│ │ nodes: n1
│ │ regions: us-east1
│ │ actual row count: 0
│ │ table: c
│ │ set: pid
│ │
│ └── • buffer
│ │ nodes: n1
│ │ regions: us-east1
│ │ actual row count: 1
│ │ label: buffer 1
│ │
│ └── • hash join
│ │ nodes: n1
│ │ regions: us-east1
│ │ actual row count: 1
│ │ estimated max memory allocated: 40 KiB
│ │ estimated max sql temp disk usage: 0 B
│ │ estimated row count: 0
│ │ equality: (pid) = (id)
│ │
│ ├── • scan
│ │ nodes: n1
│ │ regions: us-east1
│ │ actual row count: 1
│ │ KV time: 509µs
│ │ KV contention time: 0µs
│ │ KV rows decoded: 1
│ │ KV bytes read: 31 B
│ │ KV gRPC calls: 1
│ │ estimated max memory allocated: 20 KiB
│ │ estimated row count: 1 (100% of the table; stats collected 4 minutes ago)
│ │ table: c@c_pkey
│ │ spans: FULL SCAN
│ │
│ └── • filter
│ │ nodes: n1
│ │ regions: us-east1
│ │ actual row count: 1
│ │ estimated row count: 0
│ │ filter: id IS DISTINCT FROM id_new
│ │
│ └── • scan buffer
│ nodes: n1
│ regions: us-east1
│ actual row count: 1
│ estimated row count: 1
│ label: buffer 1000000
│
└── • constraint-check
│
└── • error if rows
│ nodes: n1
│ regions: us-east1
│ actual row count: 0
│
└── • lookup join (anti)
│ nodes: n1
│ regions: us-east1
│ actual row count: 0
│ KV time: 483µs
│ KV contention time: 0µs
│ KV rows decoded: 1
│ KV bytes read: 11 B
│ KV gRPC calls: 1
│ estimated max memory allocated: 30 KiB
│ estimated row count: 0
│ table: p@p_pkey
│ equality: (id_new) = (id)
│ equality cols are key
│
└── • filter
│ nodes: n1
│ regions: us-east1
│ actual row count: 1
│ estimated row count: 0
│ filter: id_new IS NOT NULL
│
└── • scan buffer
nodes: n1
regions: us-east1
actual row count: 1
estimated row count: 0
label: buffer 1
(136 rows)
```
Jira issue: CRDB-35830
Contributor guide
Assessment
This issue has not been assessed yet.