cockroachdb / cockroachdb/cockroach

sql: inequality filter on indexed computed column does not always become constrained scan

Open
#119,833 2 comments 0 reactions 0 assignees View on GitHub
A-sql-optimizer C-performance T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

Usually `GenerateConstrainedScans` is able to turn a filter on a computed column into a constrained scan of an index on that computed column. Here's a simple example:

```sql
CREATE TABLE ab (a INT PRIMARY KEY, b INT NOT NULL AS (a % 10) VIRTUAL, INDEX (b));
EXPLAIN (OPT) SELECT * FROM ab@ab_b_idx WHERE b > 5;
```

Produces the constrained scan we expect:

```
project
├── scan ab@ab_b_idx
│ ├── constraint: /2/1: [/6 - ]
│ └── flags: force-index=ab_b_idx
└── projections
└── a % 10
```

But if I change the expression from `PK % 10` to `PK + 1`, then `NormalizeCmpPlusConst` simplifies the filter to a constant `c > 4` and this messes up some part of `GenerateConstrainedScans`:

```sql
CREATE TABLE cd (c INT PRIMARY KEY, d INT NOT NULL AS (c + 1) VIRTUAL, INDEX (d));
EXPLAIN (OPT) SELECT * FROM cd@cd_d_idx WHERE d > 5;
```

Produces a full scan and a filter:

```
project
├── select
│ ├── scan cd@cd_d_idx
│ │ └── flags: force-index=cd_d_idx
│ └── filters
│ └── c > 4
└── projections
└── c + 1
```

Interestingly, if I change the query to use equality instead of an inequality it _is_ able to generate a constrained scan:

```sql
EXPLAIN (OPT) SELECT * FROM cd@cd_d_idx WHERE d = 5;
```

Produces:

```
project
├── scan cd@cd_d_idx
│ ├── constraint: /2/1: [/5/4 - /5/4]
│ └── flags: force-index=cd_d_idx
└── projections
└── c + 1
```

This behavior reproduces on both v23.2.0 and tip of master (v24.1.0-alpha.2).

Jira issue: CRDB-36337

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.