By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE tbl (id int, data jsonb);
INSERT INTO tbl VALUES
(1, '{"foo": 20, "bar": "hello", "baz": null}') -- bar matches
, (2, '{"foo": "hello", "bar": "bye", "baz": "urg"}') -- foo matches
, (3, '{"foo": 20, "bar": "bye", "baz": null}') -- no matching value
, (4, '{"x": 20, "y": "bye", "z": null}') -- no matching key
, (5, '["x", "y", "z", null]') -- sth else entirely
;
-- Basic GIN index
-- DROP INDEX tbl_data_idx;
-- CREATE INDEX tbl_data_idx ON tbl USING gin (data);
-- Optimized GIN index
-- DROP INDEX tbl_data_pathops_idx;
CREATE INDEX tbl_data_pathops_idx ON tbl USING gin (data jsonb_path_ops);
CREATE TABLE
INSERT 0 5
CREATE INDEX
SELECT *
FROM tbl t
WHERE data @? '$.keyvalue() ? (@."key" like_regex "foo|bar") ? (@."value" == "hello")';
| id | data |
|---|---|
| 1 | {"bar": "hello", "baz": null, "foo": 20} |
| 2 | {"bar": "bye", "baz": "urg", "foo": "hello"} |
SELECT 2
-- force index for tiny table if possible
SET enable_seqscan = off;
EXPLAIN
SELECT *
FROM tbl t
WHERE data @? '$.keyvalue() ? (@."key" like_regex "foo|bar") ? (@."value" == "hello")';
SET
| QUERY PLAN |
|---|
| Bitmap Heap Scan on tbl t (cost=56.00..60.01 rows=1 width=36) |
| Recheck Cond: (data @? '$.keyvalue()?(@."key" like_regex "foo|bar")?(@."value" == "hello")'::jsonpath) |
| -> Bitmap Index Scan on tbl_data_pathops_idx (cost=0.00..56.00 rows=1 width=0) |
| Index Cond: (data @? '$.keyvalue()?(@."key" like_regex "foo|bar")?(@."value" == "hello")'::jsonpath) |
EXPLAIN