cockroachdb / cockroachdb/cockroach
opt: FORCE_ZIGZAG hint doesn't work with expression indexes
- 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
Assessment
This issue has not been assessed yet.