Postgres 15

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 things (
  id serial PRIMARY KEY
, thing text NOT NULL
, blueprint jsonb NOT NULL DEFAULT '{}'
);
	
INSERT INTO things(thing, blueprint) VALUES
  ('thing 1', '{}')
, ('thing 2', '{"1":{"name":"Iskapola","wight":"2"}}')
, ('thing 3', '{"1":{"name":"Azamund","weight":"3"}, "2":{"name":"Iskapola","weight":"1"}}')
, ('thing 4', '{"1":{"name":"Ulamir","weight":"1"},  "2":{"name":"Azamund","weight":"1"}}')
, ('thing 5', '{"3":{"name":"azaMund","weight":"1"}}')  -- different capitalization
, ('thing 6', '{"4":{"name":"azamund no match","weight":"1"}}')  -- no match
;

-- for JSON/PATH expression
CREATE INDEX things_blueprint_gin_idx ON things USING gin (blueprint);

CREATE TABLE

INSERT 0 6

CREATE INDEX

-- your org. query
select *
from (select id, (jsonb_each(blueprint)).value::json->>'name' as name
      from things) as t
where t.name ~* '^azamund$';

id
name

3
Azamund

4
Azamund

5
azaMund

SELECT 3

-- my audited version
SELECT *
FROM   things t
WHERE  EXISTS (
   SELECT FROM jsonb_each(t.blueprint) b
   WHERE  b.value->>'name' ILIKE 'azamund'
   );

id
thing
blueprint

3
thing 3
{"1": {"name": "Azamund", "weight": "3"}, "2": {"name": "Iskapola", "weight": "1"}}

4
thing 4
{"1": {"name": "Ulamir", "weight": "1"}, "2": {"name": "Azamund", "weight": "1"}}

5
thing 5
{"3": {"name": "azaMund", "weight": "1"}}

SELECT 3

-- SQL JSON/PATH expression in pg 12+
SELECT *
FROM   things t
WHERE  t.blueprint @? '$.* ? (@.name == "Azamund")';

-- case insensitive
SELECT *
FROM   things t
WHERE  t.blueprint @? '$.* ? (@.name like_regex "^azamund$" flag "i")';

id
thing
blueprint

3
thing 3
{"1": {"name": "Azamund", "weight": "3"}, "2": {"name": "Iskapola", "weight": "1"}}

4
thing 4
{"1": {"name": "Ulamir", "weight": "1"}, "2": {"name": "Azamund", "weight": "1"}}

SELECT 2

id
thing
blueprint

3
thing 3
{"1": {"name": "Azamund", "weight": "3"}, "2": {"name": "Iskapola", "weight": "1"}}

4
thing 4
{"1": {"name": "Ulamir", "weight": "1"}, "2": {"name": "Azamund", "weight": "1"}}

5
thing 5
{"3": {"name": "azaMund", "weight": "1"}}

SELECT 3

-- plan for old solution
EXPLAIN
SELECT *
FROM   things t
WHERE  EXISTS (
   SELECT FROM jsonb_each(t.blueprint) b
   WHERE  b.value->>'name' ILIKE 'azamund'
   );

QUERY PLAN

Seq Scan on things t  (cost=0.00..10.07 rows=3 width=68)

  Filter: (SubPlan 1)

  SubPlan 1

    ->  Function Scan on jsonb_each b  (cost=0.00..1.50 rows=1 width=0)

          Filter: ((value ->> 'name'::text) ~~* 'azamund'::text)

EXPLAIN

-- to force index for demo with small sample if possible
SET enable_seqscan = OFF;

SET

-- old solution cannot use index
EXPLAIN
SELECT *
FROM   things t
WHERE  EXISTS (
   SELECT FROM jsonb_each(t.blueprint) b
   WHERE  b.value->>'name' ILIKE 'azamund'
   );

QUERY PLAN

Seq Scan on things t  (cost=10000000000.00..10000000010.08 rows=3 width=68)

  Filter: (SubPlan 1)

  SubPlan 1

    ->  Function Scan on jsonb_each b  (cost=0.00..1.50 rows=1 width=0)

          Filter: ((value ->> 'name'::text) ~~* 'azamund'::text)

EXPLAIN

-- But JSON/PATH expression can use above index "things_blueprint_gin_idx"!
EXPLAIN
SELECT *
FROM   things t
WHERE  t.blueprint @? '$.* ? (@.name == "Azamund")';

-- case insensitive: index less effective
EXPLAIN
SELECT *
FROM   things t
WHERE  t.blueprint @? '$.* ? (@.name like_regex "^azamund$" flag "i")';

QUERY PLAN

Bitmap Heap Scan on things t  (cost=16.00..20.01 rows=1 width=68)

  Recheck Cond: (blueprint @? '$.*?(@."name" == "Azamund")'::jsonpath)

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

        Index Cond: (blueprint @? '$.*?(@."name" == "Azamund")'::jsonpath)

EXPLAIN

QUERY PLAN

Bitmap Heap Scan on things t  (cost=68.00..72.01 rows=1 width=68)

  Recheck Cond: (blueprint @? '$.*?(@."name" like_regex "^azamund$" flag "i")'::jsonpath)

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

        Index Cond: (blueprint @? '$.*?(@."name" like_regex "^azamund$" flag "i")'::jsonpath)

EXPLAIN

-- alternative with json ARRAY
CREATE TABLE things_arr (
  id serial PRIMARY KEY
, thing text NOT NULL
, blueprint jsonb NOT NULL DEFAULT '{}'
);

INSERT INTO things_arr(thing, blueprint) VALUES
  ('thing 1', '[]')
, ('thing 2', '[{"name":"Iskapola","wight":"2"}]')
, ('thing 3', '[{"name":"Azamund","weight":"3"}, {"name":"Iskapola","weight":"1"}]')
, ('thing 4', '[{"name":"Ulamir","weight":"1"}, {"name":"Azamund","weight":"1"}]')
, ('thing 5', '[{"name":"azaMund","weight":"1"}]')  -- different capitalization
, ('thing 6', '[{"name":"azamund no match","weight":"1"}]')  -- no match
;

-- for JSON/PATH expression
CREATE INDEX things_arr_foo_path_ops ON things_arr USING gin (blueprint jsonb_path_ops);

CREATE TABLE

INSERT 0 6

CREATE INDEX

-- based on JSON array
SELECT t.*
FROM   things_arr t
WHERE  EXISTS (
   SELECT FROM jsonb_array_elements(t.blueprint) elem
   WHERE  elem->>'name' ILIKE 'azamund'
   );

id
thing
blueprint

3
thing 3
[{"name": "Azamund", "weight": "3"}, {"name": "Iskapola", "weight": "1"}]

4
thing 4
[{"name": "Ulamir", "weight": "1"}, {"name": "Azamund", "weight": "1"}]

5
thing 5
[{"name": "azaMund", "weight": "1"}]

SELECT 3

-- SQL JSON/PATH expression in pg 12+
SELECT *
FROM   things_arr t
WHERE  t.blueprint @? '$[*] ? (@.name == "Azamund")';

-- case insensitive
SELECT *
FROM   things_arr t
WHERE  t.blueprint @? '$[*] ? (@.name like_regex "^azamund$" flag "i")';

id
thing
blueprint

3
thing 3
[{"name": "Azamund", "weight": "3"}, {"name": "Iskapola", "weight": "1"}]

4
thing 4
[{"name": "Ulamir", "weight": "1"}, {"name": "Azamund", "weight": "1"}]

SELECT 2

id
thing
blueprint

3
thing 3
[{"name": "Azamund", "weight": "3"}, {"name": "Iskapola", "weight": "1"}]

4
thing 4
[{"name": "Ulamir", "weight": "1"}, {"name": "Azamund", "weight": "1"}]

5
thing 5
[{"name": "azaMund", "weight": "1"}]

SELECT 3

-- SQL JSON/PATH expression in pg 12+
EXPLAIN
SELECT *
FROM   things_arr t
WHERE  t.blueprint @? '$[*] ? (@.name == "Azamund")';

-- case insensitive
EXPLAIN
SELECT *
FROM   things_arr t
WHERE  t.blueprint @? '$[*] ? (@.name like_regex "^azamund$" flag "i")';

QUERY PLAN

Bitmap Heap Scan on things_arr t  (cost=8.00..12.01 rows=1 width=68)

  Recheck Cond: (blueprint @? '$[*]?(@."name" == "Azamund")'::jsonpath)

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

        Index Cond: (blueprint @? '$[*]?(@."name" == "Azamund")'::jsonpath)

EXPLAIN

QUERY PLAN

Bitmap Heap Scan on things_arr t  (cost=40.00..44.01 rows=1 width=68)

  Recheck Cond: (blueprint @? '$[*]?(@."name" like_regex "^azamund$" flag "i")'::jsonpath)

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

        Index Cond: (blueprint @? '$[*]?(@."name" like_regex "^azamund$" flag "i")'::jsonpath)

EXPLAIN
id	thing	blueprint
3	thing 3	{"1": {"name": "Azamund", "weight": "3"}, "2": {"name": "Iskapola", "weight": "1"}}
4	thing 4	{"1": {"name": "Ulamir", "weight": "1"}, "2": {"name": "Azamund", "weight": "1"}}
5	thing 5	{"3": {"name": "azaMund", "weight": "1"}}
id	thing	blueprint
3	thing 3	[{"name": "Azamund", "weight": "3"}, {"name": "Iskapola", "weight": "1"}]
4	thing 4	[{"name": "Ulamir", "weight": "1"}, {"name": "Azamund", "weight": "1"}]
5	thing 5	[{"name": "azaMund", "weight": "1"}]