Suboptimal plan generated for DELETE on tables with multiple public key columns
- Dominant language
- Go
- Stars
- 24.4k
- Forks
- 873
- Avg merge
- 1d 5h
- Merged PRs (30d)
- 108
Description
If I have a set of rows that I want to remove from a table, a typical way to do that would be with a query like so:
```
create table one_pk(pk int primary key, c int);
delete from one_pk where pk in (0, 1, 2);
```
This generates a plan that looks like this:
```
Delete
└─ IndexedTableAccess(one_pk)
├─ index: [one_pk.pk]
└─ filters: [{[0, 0]}, {[1, 1]}, {[2, 2]}]
```
If the table has multiple primary keys, we might attempt to extend this pattern like so:
```
create table two_pk(pk1 int, pk2 int, c int, primary key (pk1, pk2));
delete from two_pk where (pk1, pk2) in ((0, 10), (1, 11), (2, 12));
```
However, unlike in the previous case, this does not produce an indexed table access. Instead, we get a full table scan:
```
Delete
└─ Filter
├─ ((two_pk.pk1, two_pk.pk2) HASH IN ((0, 10), (1, 11), (2, 12)))
└─ Table
└─ name: two_pk
```
Ideally, we want to generate a plan like this:
```
Delete
└─ IndexedTableAccess(two_pk)
├─ index: [two_pk.pk1,two_pk.pk2]
└─ filters: [{[0, 0], [10, 10]}, {[1, 1], [11, 11]}, {[2, 2], [12, 12]}]
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.