cockroachdb / cockroachdb/cockroach
opt: row width consideration in costing can push the optimizer into bad plans
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
Recently there have been a couple of escalations where the optimizer has chosen a bad plan due to column cost considerations.
These escalations shared the following characteristics:
1. Statistics on the table are stale, either because it's an append-only table or because a large amount of new data was inserted.
2. `optimizer_min_row_count` is set to 1 (from the previous default of 0)
3. There is an obvious index choice that is not selected.
In the first instance, the schema looked like:
```
CREATE TABLE foo (
a INT,
b STRING,
c INT,
INDEX good (a, b),
INDEX bad (a, c))
```
A new value of `a` has been inserted, causing queries of the form:
```
UPDATE foo SET .. WHERE a = AND b = ;
```
to prefer index `bad` because `optimizer_min_row_count` clamps both plans to 1 row due to the stale stats, so the optimizer prefers to read from `bad` and filter instead of reading the long column `b`. This particular bad plan can be corrected by enabling `cost_scans_with_default_col_size`.
In the second case, the schema looked like:
```
CREATE TABLE bar (
a INT,
b TIMESTAMP,
INDEX good (a, b),
INDEX bad (b))
```
In this case, new values were constantly being inserted for `b`. The troublesome query was the form of:
```
SELECT ... FROM bar WHERE a = AND b > ;
```
Similar to the previous query, stale statistics caused the minimum row clamping to kick in, causing a scan on both indexes to seem equally good. In this case, the optimizer prefers the index read from the index with fewer columns, even though the other index is more fully constrained.
Jira issue: CRDB-54272
Contributor guide
Assessment
This issue has not been assessed yet.