cockroachdb / cockroachdb/cockroach
sql: not using inverted expression indexes for json equality in some cases
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
(Pulling out the part of #111963 that wasn't fixed by #112654.)
Inverted indexes on JSON can be used both for (a) matching within the value and (b) matching the entire value. For some reason, though, it appears that inverted indexes on JSON *expressions* can only be used for (a) matching within the value and not (b) matching the entire value when using **equality** as the comparison op. Here's a demonstration using 220954f3872272b1e100f49e1a0d56589ac429a6:
```sql
CREATE TABLE t (i INT PRIMARY KEY, j JSONB);
INSERT INTO t SELECT i, to_json(i % 10) FROM generate_series(0, 9999) AS s(i);
INSERT INTO t SELECT i, json_build_object('a', i % 10) FROM generate_series(10000, 19999) AS s(i);
INSERT INTO t SELECT i, json_build_object('a', json_build_object('b', i % 10)) FROM generate_series(20000, 29999) AS s(i);
-- First, test an inverted index on the column.
CREATE INVERTED INDEX ON t (j);
ANALYZE t;
-- These all use the inverted index.
EXPLAIN SELECT i FROM t WHERE j = '5';
EXPLAIN SELECT i FROM t WHERE j = '{"a": 5}';
EXPLAIN SELECT i FROM t WHERE j = '{"a": {"b": 5}}';
EXPLAIN SELECT i FROM t WHERE j->'a' = '5';
EXPLAIN SELECT i FROM t WHERE j->'a' = '{"b": 5}';
EXPLAIN SELECT i FROM t WHERE j->'a'->'b' = '5';
EXPLAIN SELECT i FROM t WHERE j @> '5';
EXPLAIN SELECT i FROM t WHERE j @> '{"a": 5}';
EXPLAIN SELECT i FROM t WHERE j @> '{"a": {"b": 5}}';
EXPLAIN SELECT i FROM t WHERE j->'a' @> '5';
EXPLAIN SELECT i FROM t WHERE j->'a' @> '{"b": 5}';
EXPLAIN SELECT i FROM t WHERE j->'a'->'b' @> '5';
-- Then, test an inverted index on (j->'a').
DROP INDEX t_j_idx;
CREATE INVERTED INDEX ON t ((j->'a'));
ANALYZE t;
-- These should all be able to use the inverted expression index, but only the last one can.
EXPLAIN SELECT i FROM t WHERE j->'a' = '5';
EXPLAIN SELECT i FROM t WHERE j->'a' = '{"b": 5}';
EXPLAIN SELECT i FROM t WHERE j->'a'->'b' = '5';
-- Interestingly, these cases using @> all seem to use the expression index correctly.
EXPLAIN SELECT i FROM t WHERE j->'a' @> '5';
EXPLAIN SELECT i FROM t WHERE j->'a' @> '{"b": 5}';
EXPLAIN SELECT i FROM t WHERE j->'a'->'b' @> '5';
```
Jira issue: CRDB-32702
Contributor guide
Research direction
Start by running the supplied CREATE TABLE, INSERT, index, ANALYZE, and EXPLAIN statements at commit 220954f3872272b1e100f49e1a0d56589ac429a6. Compare the column and (j->'a') inverted-index plans for equality and @> predicates; done means the equality cases use the expression index as expected without regressing the existing @> cases.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- sql
- Domain
- databases
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100