cockroachdb / cockroachdb/cockroach
opt: simplify DELETE USING with same constant
- 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
Assessment
This issue has not been assessed yet.