Postgres 16

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 (data jsonb);

INSERT INTO tbl VALUES
  ('[{"value": "Sales"}]')
, ('[{"value": "CEO Staff"}]')
, ('[{"value": "Procurement"}]')
, ('[{"value": "CEO Staff"}]')
;

CREATE TABLE

INSERT 0 4

-- works for ==
SELECT *
FROM   tbl 
WHERE  jsonb_path_exists(data
                       , '$[*].value ? (@ == $foo)'
                       , jsonb_build_object('foo', 'CEO Staff'));

data

[{"value": "CEO Staff"}]

[{"value": "CEO Staff"}]

SELECT 2

-- FAILS for ilike_regex
SELECT *
FROM   tbl 
WHERE  jsonb_path_exists(data
                       , '$[*].value ? (@ like_regex $foo flag "i")'
                       , jsonb_build_object('foo', 'CEO'));

ERROR:  syntax error at or near "$foo" of jsonpath input
LINE 5:                        , '$[*].value ? (@ like_regex $foo fl...
                                 ^

-- cross-check: works without parameter substitution
SELECT *
FROM   tbl 
WHERE  jsonb_path_exists(data
                       , '$[*].value ? (@ like_regex "CEO" flag "i")');

data

[{"value": "CEO Staff"}]

[{"value": "CEO Staff"}]

SELECT 2

-- superior workaround: this can use an index!
SELECT *
FROM   tbl 
WHERE  data @? format('$[*].value ? (@ like_regex %s flag "i")', '"CEO"')::jsonpath;

data

[{"value": "CEO Staff"}]

[{"value": "CEO Staff"}]

SELECT 2

-- applicable index (!)
CREATE INDEX ON tbl USING gin (data jsonb_path_ops);
-- index usable?
SET enable_seqscan = off;

CREATE INDEX

SET

-- YES
EXPLAIN
SELECT *
FROM   tbl 
WHERE  data @? format('$[*].value ? (@ like_regex %s flag "i")', '"CEO"')::jsonpath;

QUERY PLAN

Bitmap Heap Scan on tbl  (cost=17.30..21.32 rows=1 width=32)

  Recheck Cond: (data @? (format('$[*].value ? (@ like_regex %s flag "i")'::text, '"CEO"'))::jsonpath)

  ->  Bitmap Index Scan on tbl_data_idx  (cost=0.00..17.30 rows=1 width=0)

        Index Cond: (data @? (format('$[*].value ? (@ like_regex %s flag "i")'::text, '"CEO"'))::jsonpath)

EXPLAIN

-- NO
EXPLAIN
SELECT *
FROM   tbl 
WHERE  jsonb_path_exists(data
                       , '$[*].value ? (@ == $foo)'
                       , jsonb_build_object('foo', 'CEO Staff'));

QUERY PLAN

Seq Scan on tbl  (cost=10000000000.00..10000000001.06 rows=1 width=32)

  Filter: jsonb_path_exists(data, '$[*]."value"?(@ == $"foo")'::jsonpath, jsonb_build_object('foo', 'CEO Staff'), false)

JIT:

  Functions: 2

  Options: Inlining true, Optimization true, Expressions true, Deforming true

EXPLAIN