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 mytable (
  id   serial PRIMARY KEY
, data jsonb
);

INSERT INTO mytable (data)
VALUES
  ('{"people": [{"age": "55", "name": "Bill"}], "another_key": "yes"}')
, ('{"people": [{"age": "73", "name": "Bob"}],  "another_key": "yes"}')
, ('{"people": [{"age": "73", "name": "Bob"}
               ,{"age": "77", "name": "Udo"}],  "another_key": "yes"}');

CREATE TABLE

INSERT 0 3

SELECT t.id, p.person
FROM   mytable t 
     , jsonb_array_elements(t.data->'people') p(person)  -- implicit LATERAL
WHERE (t.data->'people') @> '[{"name": "Bob"}]'
AND    p.person->>'name' = 'Bob'
AND   (p.person->>'age')::int > 30;

id
person

2
{"age": "73", "name": "Bob"}

3
{"age": "73", "name": "Bob"}

SELECT 2

CREATE INDEX mytable_people_gin_idx ON mytable
USING gin ((data->'people') jsonb_path_ops);

CREATE INDEX

set enable_seqscan = off;  -- to show index plan for tiny table

EXPLAIN
SELECT t.id, p.person
FROM   mytable t 
     , jsonb_array_elements(t.data->'people') p(person)  -- implicit LATERAL
WHERE (t.data->'people') @> '[{"name": "Bob"}]'
AND    p.person->>'name' = 'Bob'
AND   (p.person->>'age')::int > 30;

SET

QUERY PLAN

Nested Loop  (cost=8.01..14.53 rows=1 width=36)

  ->  Bitmap Heap Scan on mytable t  (cost=8.00..12.02 rows=1 width=36)

        Recheck Cond: ((data -> 'people'::text) @> '[{"name": "Bob"}]'::jsonb)

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

              Index Cond: ((data -> 'people'::text) @> '[{"name": "Bob"}]'::jsonb)

  ->  Function Scan on jsonb_array_elements p  (cost=0.01..2.50 rows=1 width=32)

        Filter: (((person ->> 'name'::text) = 'Bob'::text) AND (((person ->> 'age'::text))::integer > 30))

EXPLAIN