cockroachdb / cockroachdb/cockroach
opt: eliminate unnecessary FK checks in multi-mutation CTEs
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
We could improve the performance of CTEs that mutate multiple tables linked with FK constraints by eliminating unnecessary FK checks in some cases.
Consider the following schema and CTEs:
```sql
CREATE TABLE p (
id INT PRIMARY KEY,
s STRING
);
CREATE TABLE c (
id INT PRIMARY KEY,
p_id INT REFERENCES p(id)
);
EXPLAIN (OPT)
WITH insert_p AS (
INSERT INTO p VALUES (1, 'foo') RETURNING id
), insert_c AS (
INSERT INTO c VALUES (10, 1) RETURNING id
)
SELECT 1;
-- info
-- ---------------------------------------------------------
-- with &1 (insert_p)
-- ├── insert p
-- │ └── values
-- │ └── (1, 'foo')
-- └── with &3 (insert_c)
-- ├── insert c
-- │ ├── values
-- │ │ └── (10, 1)
-- │ └── f-k-checks
-- │ └── f-k-checks-item: c(p_id) -> p(id)
-- │ └── anti-join (lookup p)
-- │ ├── lookup columns are key
-- │ ├── with-scan &2
-- │ └── filters (true)
-- └── values
-- └── (1,)
-- (16 rows)
EXPLAIN (OPT)
WITH update_p AS (
UPDATE p SET s = 'bar' WHERE id = 1 RETURNING id
), insert_c AS (
INSERT INTO c VALUES (10, 1) RETURNING id
)
SELECT 1;
-- info
-- ---------------------------------------------------------
-- with &1 (update_p)
-- ├── update p
-- │ └── project
-- │ ├── scan p
-- │ │ └── constraint: /5: [/1 - /1]
-- │ └── projections
-- │ └── 'bar'
-- └── with &3 (insert_c)
-- ├── insert c
-- │ ├── values
-- │ │ └── (10, 1)
-- │ └── f-k-checks
-- │ └── f-k-checks-item: c(p_id) -> p(id)
-- │ └── anti-join (lookup p)
-- │ ├── lookup columns are key
-- │ ├── with-scan &2
-- │ └── filters (true)
-- └── values
-- └── (1,)
-- (19 rows)
```
In both CTEs, the FK checks for the inserts into `c` are unnecessary. In the first CTE, the same FK value is inserted into `p`, so we know it exists as long as the insert succeeds. In the second CTE, the same FK values being inserted into `c` is updated in `p`, so we know it exists as long as the update successfully updated a row.
For the `UPDATE` case, this optimization is only valid if:
1. The `UPDATE` does not alter the FK column (or it updates it to the same as the value being inserted).
2. No other mutations in the CTE alter the FK column of the parent table.
Jira issue: CRDB-41721
Contributor guide
Assessment
This issue has not been assessed yet.