cockroachdb / cockroachdb/cockroach

sql: not using inverted expression indexes for json equality in some cases

Open
#112,978 3 comments 0 reactions 0 assignees View on GitHub
A-sql-json A-sql-optimizer C-performance O-support P-3 T-sql-queries
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

Open the contributing 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

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.