cockroachdb / cockroachdb/cockroach
Expand SimplifyNotInSingleElement to work with larger NOT IN sets
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Currently we have a rule, SimplifyNotInSingleElement that turns a NOT IN clause with a single element into an inequality. A customer ran into a case where their IN SET had two elements, which was optimized into a scan + filter. With a single element, this query is turned into two range scans, which is much more efficient in this case. It shouldn't be too hard to sort the IN SET and then create a set of range scans, obviously with some practical upper limit.
Given the schema:
```
CREATE TABLE t(
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
location_identifier STRING NOT NULL,
sub_task_name STRING NOT NULL,
status STRING NOT NULL,
task_number INT NOT NULL);
CREATE INDEX task_location_identifier_sub_task_name_status_idx ON t(location_identifier, sub_task_name, status, task_number);
```
And query:
```
EXPLAIN SELECT * FROM t
WHERE location_identifier = '5150'
AND sub_task_name = 'ALLOCATION_DRIVEN'
AND status NOT IN ('CANCELLED', 'COMPLETED');
```
We currently give:
```
info
planning time: 666µs
execution time: 35ms
distribution: local
vectorized: true
rows read from KV: 1,542 (106 KiB, 1 gRPC calls)
cumulative time spent in KV: 35ms
maximum memory usage: 230 KiB
network usage: 0 B (0 messages)
regions: us-south1
sql cpu time: 398µs
• limit
│ count: 10001
│
└── • filter
│ nodes: n3
│ regions: us-south1
│ actual row count: 209
│ sql cpu time: 22µs
│ estimated row count: 498
│ filter: status NOT IN ('CANCELLED', 'COMPLETED')
│
└── • scan
nodes: n3
regions: us-south1
actual row count: 1,542
KV time: 35ms
KV contention time: 0µs
KV rows read: 1,542
KV bytes read: 106 KiB
KV gRPC calls: 1
estimated max memory allocated: 230 KiB
sql cpu time: 376µs
estimated row count: 1,493 (15% of the table; stats collected 8 minutes ago; using stats forecast for 3 minutes ago)
table: task@task_location_identifier_sub_task_name_status_idx
spans: [/'5150'/'ALLOCATION_DRIVEN' - /'5150'/'ALLOCATION_DRIVEN']
```
But something like this would be better:
```
info
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
distribution: local
vectorized: true
• scan
missing stats
table: t@task_location_identifier_sub_task_name_status_idx
spans: [/'5150'/'ALLOCATION_DRIVEN' - /'5150'/'ALLOCATION_DRIVEN'/'CANCELLED') [/'5150'/'ALLOCATION_DRIVEN'/e'CANCELLED\x00' - /'5150'/'ALLOCATION_DRIVEN']
```
Jira issue: CRDB-52818
Contributor guide
Assessment
This issue has not been assessed yet.