cockroachdb / cockroachdb/cockroach

opt: FORCE_ZIGZAG hint doesn't work with expression indexes

Open
#103,753 1 comment 0 reactions 0 assignees View on GitHub
A-sql-optimizer C-enhancement T-sql-queries
Dominant language
Go
Stars
32.5k
Forks
4.1k
PR merge metrics
PR metrics pending

Description

When trying to use the `FORCE_ZIGZAG` hint with expression indexes, I always seem to get `ERROR: could not produce a query plan conforming to the FORCE_ZIGZAG hint`. Here's a demonstration:

```sql
CREATE TABLE ab (a INT, b INT, INDEX a1 (a), INDEX b1 (b), INDEX a2 ((a + 0)));
-- using indexes a1 and b1 works:
EXPLAIN SELECT * FROM ab@{FORCE_ZIGZAG=a1, FORCE_ZIGZAG=b1} WHERE a + 0 = 42 AND b = 99;
-- using index a2 does not work:
EXPLAIN SELECT * FROM ab@{FORCE_ZIGZAG=a2, FORCE_ZIGZAG=b1} WHERE a + 0 = 42 AND b = 99;
-- using index a2 in another kind of hint works:
EXPLAIN SELECT * FROM ab@{FORCE_INDEX=a2} WHERE a + 0 = 42 AND b = 99;
```

Here are the results on `cockroach demo` running `CockroachDB CCL v23.1.0-alpha.8-dev (darwin amd64, built , go1.19.4)`:

```
demo@127.0.0.1:26257/demoapp/defaultdb> EXPLAIN SELECT * FROM ab@{FORCE_ZIGZAG=a1, FORCE_ZIGZAG=b1} WHERE a + 0 = 42 AND b = 99;
info
----------------------------------
distribution: local
vectorized: true

• zigzag join
estimated row count: 1
pred: (a = 42) AND (b = 99)
left table: ab@a1
left columns: (a, rowid)
left fixed values: 1 column
right table: ab@b1
right columns: (b, rowid)
right fixed values: 1 column
(12 rows)

Time: 1ms total (execution 1ms / network 0ms)

demo@127.0.0.1:26257/demoapp/defaultdb> EXPLAIN SELECT * FROM ab@{FORCE_ZIGZAG=a2, FORCE_ZIGZAG=b1} WHERE a + 0 = 42 AND b = 99;
ERROR: could not produce a query plan conforming to the FORCE_ZIGZAG hint

demo@127.0.0.1:26257/demoapp/defaultdb> EXPLAIN SELECT * FROM ab@{FORCE_INDEX=a2} WHERE a + 0 = 42 AND b = 99;
info
--------------------------------------------------------------------------------------
distribution: local
vectorized: true

• filter
│ estimated row count: 1
│ filter: b = 99

└── • index join
│ estimated row count: 1
│ table: ab@ab_pkey

└── • scan
estimated row count: 1 (99% of the table; stats collected 0 seconds ago)
table: ab@a2
spans: [/42 - /42]
(15 rows)

Time: 1ms total (execution 1ms / network 0ms)
```

Jira issue: CRDB-28174

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.