dolthub / dolthub/dolt

Suboptimal plan generated for DELETE on tables with multiple public key columns

Open
#11,003 1 comment 0 reactions 0 assignees View on GitHub
analyzer performance
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.