cockroachdb / cockroachdb/cockroach

opt: eliminate unnecessary FK checks in multi-mutation CTEs

Open
#129,812 1 comment 0 reactions 0 assignees View on GitHub
A-sql-fks C-performance T-sql-queries
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

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.