cockroachdb / cockroachdb/cockroach

opt: simplify DELETE USING with same constant

Open
#158,300 0 comments 0 reactions 0 assignees View on GitHub
A-sql-optimizer C-performance E-quick-win O-support P-3 T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

In the following example, the DELETE USING statement has a self-join on one of two PK columns. For the other PK column, it uses the same constant as the subquery. This prevents the optimizer from simplifying away one of the lookup joins:

```sql
CREATE TABLE mn (m INT NOT NULL, n INT NOT NULL, PRIMARY KEY (m, n));

EXPLAIN WITH s AS (SELECT n FROM mn WHERE m = 4 ORDER BY n LIMIT 10)
DELETE FROM mn AS t USING s WHERE t.m = 4 AND t.n = s.n;
-- • delete
-- │ from: mn
-- │ auto commit
-- │
-- └── • render
-- │
-- └── • lookup join
-- │ table: mn@mn_pkey
-- │ equality: (lookup_join_const_col_@13, n) = (m, n)
-- │ equality cols are key
-- │
-- └── • render
-- │
-- └── • scan
-- missing stats
-- table: mn@mn_pkey
-- spans: [/4 - /4]
-- limit: 10

-- if we change the constant to a column reference, the plan is better
EXPLAIN WITH s AS (SELECT m, n FROM mn WHERE m = 5 ORDER BY n LIMIT 10)
DELETE FROM mn AS t USING s WHERE t.m = s.m AND t.n = s.n;
-- • delete
-- │ from: mn
-- │ auto commit
-- │
-- └── • render
-- │
-- └── • scan
-- estimated row count: 1 (100% of the table; stats collected 19 seconds ago)
-- table: mn@mn_pkey
-- spans: [/5 - /5]
-- limit: 10
-- locking strength: for update
```

Jira issue: CRDB-57222

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.