cockroachdb / cockroachdb/cockroach

opt: row width consideration in costing can push the optimizer into bad plans

Open
#153,207 4 comments 0 reactions 0 assignees View on GitHub
A-sql-optimizer branch-master branch-release-24.3 branch-release-25.1 branch-release-25.2 branch-release-25.3 C-bug O-support P-3 T-sql-queries
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.