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?.
select setseed(.42);

create table orders(id int primary key generated by default as identity,
  data jsonb);
insert into orders(data) values 
('{"data": [{"items": [{"name": "Peter"},{"name": "John"}]}]}'),
('{"data": [{"items": []}]}'),
(jsonb_build_object(
  'data',
  jsonb_build_array(
      jsonb_build_object(
          'items',
          jsonb_build_array(
              jsonb_build_object('name','Bob'),
              jsonb_build_object('name','Ted'),
              jsonb_build_object('name','Dan')
          )
       )
  )
));
insert into orders(data) select
jsonb_build_object(
  'data',
  jsonb_build_array(
      jsonb_build_object(
          'items',
          case when random() > .7 then
          jsonb_build_array(
              jsonb_build_object('name',left(gen_random_uuid()::text,8)),
              jsonb_build_object('name',left(gen_random_uuid()::text,8)),
              jsonb_build_object('not_a_name',left(gen_random_uuid()::text,8)),
              jsonb_build_object('name',left(gen_random_uuid()::text,8))
          )
          when random()>.4 then 
               jsonb_build_array('not_a_name',left(gen_random_uuid()::text,8))
          else jsonb_build_array() end,
         left(gen_random_uuid()::text,12),
         random(),
         left(gen_random_uuid()::text,12),
         array[random(),random(),random()],
         left(gen_random_uuid()::text,12),
         true
       )
  )
)
from generate_series(1,6e4);

select data from orders tablesample bernoulli(.2) limit 29;

setseed

SELECT 1

CREATE TABLE

INSERT 0 3

INSERT 0 60000

data

{"data": [{"items": ["not_a_name", "6b3f0b28"], "11324f29-cf2": [0.936254613468892, 0.004193258418375256, 0.7692525384775546], "1cfd4468-50f": 0.7314150473636467, "c413dcf2-3bc": true}]}

{"data": [{"items": [], "07b6bbdc-88f": 0.24570184634405368, "84f15306-f2e": true, "98631a3c-9df": [0.6553825544050655, 0.0912615468683251, 0.37358855254217804]}]}

{"data": [{"items": [], "3e975c6b-b1f": [0.20291919541642622, 0.27955930670261786, 0.7102935131013399], "a88abea1-cd8": 0.1338862783381849, "b7566f26-36d": true}]}

{"data": [{"items": [{"name": "5bbd2ee4"}, {"name": "9dedef8b"}, {"not_a_name": "fc280517"}, {"name": "be38ee05"}], "3b9d6466-151": true, "5d3ab1de-7d5": 0.4398145065795387, "df9889a7-c96": [0.16252835873002835, 0.2842904973835805, 0.8796559817317173]}]}

{"data": [{"items": ["not_a_name", "f553df43"], "1c0eaaea-506": true, "5d3786f9-f0a": 0.34060392943131257, "c9943b58-e71": [0.20775329380597385, 0.09122430387226022, 0.30622496744379]}]}

{"data": [{"items": [{"name": "94b58e29"}, {"name": "e055b698"}, {"not_a_name": "909757bf"}, {"name": "f1849592"}], "4c124e66-d50": [0.7891800831176716, 0.8751445310595514, 0.7862138788470361], "b6b75755-681": 0.0805634031738216, "dcc06e9e-737": true}]}

{"data": [{"items": [{"name": "e077851c"}, {"name": "d52283d2"}, {"not_a_name": "9b81615d"}, {"name": "fdf3287e"}], "20cdc048-e96": 0.584041448142369, "92423f1a-82d": [0.9466820558334674, 0.22393687014934716, 0.11684235393261955], "f984449e-498": true}]}

{"data": [{"items": [], "070a5a14-7ef": true, "0f0bfb8c-fa2": [0.34011447351392077, 0.8882970904371137, 0.7093511890013395], "98a639ed-0cd": 0.3608837100459734}]}

{"data": [{"items": [{"name": "c5c06b50"}, {"name": "cd0e0fad"}, {"not_a_name": "740f00e7"}, {"name": "83a53521"}], "58c94805-dca": [0.11105281273338452, 0.8248831320204593, 0.22851286971527807], "801012ec-c15": true, "8da2827e-0ee": 0.7583714581431431}]}

{"data": [{"items": [], "2f38b803-579": 0.7961963257916775, "b45c1dbb-f6f": true, "e5fa2092-a30": [0.30007420699010323, 0.9595009905784158, 0.9978550471304601]}]}

{"data": [{"items": [], "24c50a56-3d9": [0.5208010047769347, 0.5460156919095458, 0.887556995851067], "51348ebf-95b": 0.3158148068947424, "dad904b2-134": true}]}

{"data": [{"items": [{"name": "090b399b"}, {"name": "788883f9"}, {"not_a_name": "17c1246b"}, {"name": "c3294f9a"}], "04e46427-61c": 0.2549638890758066, "c03d1d7e-60a": [0.9721694755779982, 0.18560340856717072, 0.52401706211076], "fef8fba5-5a5": true}]}

{"data": [{"items": ["not_a_name", "573f47f5"], "6b156470-17c": [0.29879770180630527, 0.04287008974384787, 0.4384524822715745], "7bd986f0-905": true, "8e0a79ec-c53": 0.878278924520643}]}

{"data": [{"items": [{"name": "c768939e"}, {"name": "afcf1b45"}, {"not_a_name": "aa0ebe39"}, {"name": "3d79bc56"}], "943b7482-d8c": 0.06037508525769786, "ae294458-513": true, "d4dd7ac0-ded": [0.6795879865990648, 0.8114449819284486, 0.4441203967458047]}]}

{"data": [{"items": [{"name": "a65d6af4"}, {"name": "32a69500"}, {"not_a_name": "50afc46c"}, {"name": "01653e16"}], "3c65ed08-413": [0.8547881228944345, 0.5961348901535437, 0.6175888824390499], "db1b54f7-687": 0.9024895594504518, "f6bb2a5a-1d6": true}]}

{"data": [{"items": ["not_a_name", "3efe4bdf"], "46afb92a-0a0": true, "b2b8c6e9-c20": [0.9798532443585832, 0.6956287623209372, 0.5435163687734446], "b3b219aa-37c": 0.6103782939629419}]}

{"data": [{"items": ["not_a_name", "ba1b35e3"], "29a177a5-2b7": 0.6899370433615353, "69275147-cd5": [0.48708824474268275, 0.2181103665563775, 0.27475163189345264], "91d72e03-258": true}]}

{"data": [{"items": ["not_a_name", "41deb2e3"], "e785f75c-337": true, "f0f4af05-6a0": [0.6046517251479306, 0.5688592327660367, 0.1762929328634275], "f586e5c4-8fd": 0.526566733602283}]}

{"data": [{"items": [], "b449718f-47d": [0.22812154011568775, 0.42700875464384325, 0.6046783138933829], "e474286c-b02": true, "f2c42f8a-508": 0.25667540399131283}]}

{"data": [{"items": [], "1fb7c1e9-f22": [0.38695438642114066, 0.5865600863826208, 0.7114431933616427], "4885846a-2e9": 0.16728326754078449, "b73dccb3-829": true}]}

{"data": [{"items": [], "8a118f45-488": [0.8471769508238067, 0.11293433435224398, 0.28710705629634625], "8ca7a938-905": 0.4206287300005571, "edf16ebd-8f7": true}]}

{"data": [{"items": ["not_a_name", "ed4f11a2"], "09394d9a-c79": true, "276ce561-6e3": 0.9212624613012383, "ff143a47-267": [0.3334929837234162, 0.9811451615608546, 0.7433206915103159]}]}

{"data": [{"items": ["not_a_name", "d499d279"], "13a6ade4-c81": 0.19767815012124657, "1bdb1119-67a": [0.10537320158005747, 0.7863949600205882, 0.7047808071350135], "814bb153-718": true}]}

{"data": [{"items": ["not_a_name", "4fb48534"], "4cfae415-138": [0.4820480337398525, 0.4637158187633039, 0.5318326691500146], "c7b20fde-0cf": 0.7717945858137008, "e757a24b-d53": true}]}

{"data": [{"items": ["not_a_name", "b686d389"], "3edbc36c-ae8": true, "e89738b4-fab": [0.7758739406391735, 0.3114561454290341, 0.8130629000457186], "f892bd95-613": 0.32015438437795196}]}

{"data": [{"items": [], "01b64983-04b": 0.4403274801861983, "126d732d-775": [0.8906580581313752, 0.661896194256149, 0.27090833437184436], "559c7f40-fba": true}]}

{"data": [{"items": ["not_a_name", "3b72a8e0"], "11c01210-65a": [0.7398167464321732, 0.3810565061051512, 0.20186688913378092], "2760dc30-8dd": true, "88eb5626-b40": 0.05867666015529638}]}

{"data": [{"items": [], "1a04f308-f97": 0.6681809798762415, "54cf82fb-672": true, "f248a914-b89": [0.2871752448593998, 0.4160952758687928, 0.009030133285803377]}]}

{"data": [{"items": ["not_a_name", "25683c7d"], "34821d08-2a8": [0.5171015085976316, 0.9847984417673401, 0.19683166045400458], "c864dd1d-dca": 0.8288659468123458, "cce59e87-a52": true}]}

SELECT 29

explain analyze verbose 
select distinct jsonb_path_query(data,'$.data[*].items[*].name')
from orders
where data @? '$.data[*].items[*].name';

QUERY PLAN

HashAggregate  (cost=12851.09..12883.50 rows=2593 width=32) (actual time=155.881..165.098 rows=54419 loops=1)

  Output: (jsonb_path_query(data, '$."data"[*]."items"[*]."name"'::jsonpath, '{}'::jsonb, false))

  Group Key: (jsonb_path_query(orders.data, '$."data"[*]."items"[*]."name"'::jsonpath, '{}'::jsonb, false))

  Batches: 1  Memory Usage: 6929kB

  ->  Gather  (cost=1000.00..12844.61 rows=2593 width=32) (actual time=4.863..96.647 rows=54419 loops=1)

        Output: (jsonb_path_query(data, '$."data"[*]."items"[*]."name"'::jsonpath, '{}'::jsonb, false))

        Workers Planned: 1

        Workers Launched: 1

        ->  ProjectSet  (cost=0.00..11585.31 rows=1525000 width=32) (actual time=0.021..61.165 rows=27210 loops=2)

              Output: jsonb_path_query(data, '$."data"[*]."items"[*]."name"'::jsonpath, '{}'::jsonb, false)

              Worker 0:  actual time=0.028..72.625 rows=33833 loops=1

              ->  Parallel Seq Scan on public.orders  (cost=0.00..3948.87 rows=1525 width=32) (actual time=0.016..32.589 rows=9070 loops=2)

                    Output: id, data

                    Filter: (orders.data @? '$."data"[*]."items"[*]."name"'::jsonpath)

                    Rows Removed by Filter: 20932

                    Worker 0:  actual time=0.022..38.432 rows=11278 loops=1

Planning Time: 1.926 ms

Execution Time: 168.259 ms

EXPLAIN

create index on orders((data @? '$.data[*].items[*].name'));

CREATE INDEX

vacuum analyze orders;

VACUUM

explain analyze verbose 
select distinct jsonb_path_query(data,'$.data[*].items[*].name')
from orders
where data @? '$.data[*].items[*].name';

select distinct jsonb_path_query(data,'$.data[*].items[*].name') uniqueName
from orders
where data @? '$.data[*].items[*].name'
order by uniqueName
limit 10;

QUERY PLAN

HashAggregate  (cost=56952.18..57171.89 rows=17577 width=32) (actual time=86.490..95.816 rows=54419 loops=1)

  Output: (jsonb_path_query(data, '$."data"[*]."items"[*]."name"'::jsonpath, '{}'::jsonb, false))

  Group Key: (jsonb_path_query(orders.data, '$."data"[*]."items"[*]."name"'::jsonpath, '{}'::jsonb, false))

  Batches: 1  Memory Usage: 6929kB

  ->  Gather  (cost=1203.66..56908.24 rows=17577 width=32) (actual time=6.747..62.039 rows=54419 loops=1)

        Output: (jsonb_path_query(data, '$."data"[*]."items"[*]."name"'::jsonpath, '{}'::jsonb, false))

        Workers Planned: 1

        Workers Launched: 1

        ->  ProjectSet  (cost=203.66..54150.54 rows=10339000 width=32) (actual time=0.681..25.877 rows=27210 loops=2)

              Output: jsonb_path_query(data, '$."data"[*]."items"[*]."name"'::jsonpath, '{}'::jsonb, false)

              Worker 0:  actual time=1.325..28.436 rows=32711 loops=1

              ->  Parallel Bitmap Heap Scan on public.orders  (cost=203.66..2377.99 rows=10339 width=238) (actual time=0.669..5.795 rows=9070 loops=2)

                    Output: id, data

                    Recheck Cond: (orders.data @? '$."data"[*]."items"[*]."name"'::jsonpath)

                    Heap Blocks: exact=822

                    Worker 0:  actual time=1.308..7.002 rows=10904 loops=1

                    ->  Bitmap Index Scan on orders_expr_idx  (cost=0.00..199.27 rows=17997 width=0) (actual time=1.026..1.026 rows=18140 loops=1)

                          Index Cond: ((orders.data @? '$."data"[*]."items"[*]."name"'::jsonpath) = true)

                          Worker 0:  actual time=1.026..1.026 rows=18140 loops=1

Planning Time: 0.389 ms

Execution Time: 97.957 ms

EXPLAIN

uniquename

"00050bb3"

"0005e13c"

"0006beff"

"0007040d"

"000781cd"

"00081adb"

"000accb2"

"000b2c3f"

"000bb3d6"

"000c2a71"

SELECT 10

--OP query with a few fixes
select setseed(.42);

explain analyze verbose
SELECT distinct NameList->'name' AS uniqueNames
FROM orders
         CROSS JOIN jsonb_array_elements(data->'data')_(itemsData)
         CROSS JOIN jsonb_array_elements(itemsData->'items')__(NameList)
WHERE (itemsData->'items') != '[]';

SELECT distinct NameList->'name' AS uniqueNames
FROM orders
         CROSS JOIN jsonb_array_elements(data->'data')_(itemsData)
         CROSS JOIN jsonb_array_elements(itemsData->'items')__(NameList)
WHERE (itemsData->'items') != '[]'
order by uniqueNames
limit 10;

setseed

SELECT 1

QUERY PLAN

Unique  (cost=6250086.65..6250087.15 rows=100 width=32) (actual time=1151.703..1179.905 rows=54420 loops=1)

  Output: ((__.namelist -> 'name'::text))

  ->  Sort  (cost=6250086.65..6250086.90 rows=100 width=32) (actual time=1151.700..1156.507 rows=54421 loops=1)

        Output: ((__.namelist -> 'name'::text))

        Sort Key: ((__.namelist -> 'name'::text))

        Sort Method: quicksort  Memory: 3237kB

        ->  Gather  (cost=6250072.08..6250083.33 rows=100 width=32) (actual time=936.407..957.843 rows=54421 loops=1)

              Output: ((__.namelist -> 'name'::text))

              Workers Planned: 1

              Workers Launched: 1

              ->  HashAggregate  (cost=6249072.08..6249073.33 rows=100 width=32) (actual time=899.760..905.189 rows=27210 loops=2)

                    Output: ((__.namelist -> 'name'::text))

                    Group Key: (__.namelist -> 'name'::text)

                    Batches: 1  Memory Usage: 3617kB

                    Worker 0:  actual time=869.006..873.907 rows=27304 loops=1

                      Batches: 1  Memory Usage: 3617kB

                      JIT:

                        Functions: 16

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

                        Timing: Generation 1.331 ms, Inlining 188.190 ms, Optimization 156.578 ms, Emission 122.191 ms, Total 468.291 ms

                    ->  Nested Loop  (cost=0.02..5375498.99 rows=349429235 width=32) (actual time=501.790..850.484 rows=61586 loops=2)

                          Output: (__.namelist -> 'name'::text)

                          Worker 0:  actual time=467.371..821.711 rows=61708 loops=1

                          ->  Nested Loop  (cost=0.01..90282.00 rows=3494292 width=32) (actual time=501.761..627.577 rows=21724 loops=2)

                                Output: _.itemsdata

                                Worker 0:  actual time=467.333..600.067 rows=21753 loops=1

                                ->  Parallel Seq Scan on public.orders  (cost=0.00..2394.96 rows=35296 width=238) (actual time=0.006..4.872 rows=30002 loops=2)

                                      Output: orders.id, orders.data

                                      Worker 0:  actual time=0.007..4.992 rows=30051 loops=1

                                ->  Function Scan on pg_catalog.jsonb_array_elements _  (cost=0.01..1.50 rows=99 width=32) (actual time=0.020..0.020 rows=1 loops=60003)

                                      Output: _.itemsdata

                                      Function Call: jsonb_array_elements((orders.data -> 'data'::text))

                                      Filter: ((_.itemsdata -> 'items'::text) <> '[]'::jsonb)

                                      Rows Removed by Filter: 0

                                      Worker 0:  actual time=0.019..0.019 rows=1 loops=30051

                          ->  Memoize  (cost=0.01..1.01 rows=100 width=32) (actual time=0.006..0.008 rows=3 loops=43447)

                                Output: __.namelist

                                Cache Key: _.itemsdata

                                Cache Mode: binary

                                Hits: 0  Misses: 21694  Evictions: 3146  Overflows: 0  Memory Usage: 8193kB

                                Worker 0:  actual time=0.005..0.008 rows=3 loops=21753

                                  Hits: 0  Misses: 21753  Evictions: 3253  Overflows: 0  Memory Usage: 8193kB

                                ->  Function Scan on pg_catalog.jsonb_array_elements __  (cost=0.01..1.00 rows=100 width=32) (actual time=0.003..0.003 rows=3 loops=43447)

                                      Output: __.namelist

                                      Function Call: jsonb_array_elements((_.itemsdata -> 'items'::text))

                                      Worker 0:  actual time=0.002..0.004 rows=3 loops=21753

Planning Time: 0.144 ms

JIT:

  Functions: 33

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

  Timing: Generation 2.981 ms, Inlining 365.990 ms, Optimization 358.375 ms, Emission 278.772 ms, Total 1006.118 ms

Execution Time: 1666.471 ms

EXPLAIN

uniquenames

"00050bb3"

"0005e13c"

"0006beff"

"0007040d"

"000781cd"

"00081adb"

"000accb2"

"000b2c3f"

"000bb3d6"

"000c2a71"

SELECT 10

create index on orders using gin(data jsonb_path_ops);

CREATE INDEX

vacuum analyze orders;

VACUUM

explain analyze verbose 
select distinct jsonb_path_query(data,'$.data[*].items[*].name')
from orders
where data @? '$.data[*].items[*].name?(@=="Dan")';

select distinct jsonb_path_query(data,'$.data[*].items[*].name') uniqueName
from orders
where data @? '$.data[*].items[*].name?(@=="Dan")'
limit 10;

QUERY PLAN

HashAggregate  (cost=85.27..160.27 rows=6000 width=32) (actual time=0.054..0.070 rows=3 loops=1)

  Output: (jsonb_path_query(data, '$."data"[*]."items"[*]."name"'::jsonpath, '{}'::jsonb, false))

  Group Key: jsonb_path_query(orders.data, '$."data"[*]."items"[*]."name"'::jsonpath, '{}'::jsonb, false)

  Batches: 1  Memory Usage: 217kB

  ->  ProjectSet  (cost=17.13..70.27 rows=6000 width=32) (actual time=0.038..0.041 rows=3 loops=1)

        Output: jsonb_path_query(data, '$."data"[*]."items"[*]."name"'::jsonpath, '{}'::jsonb, false)

        ->  Bitmap Heap Scan on public.orders  (cost=17.13..40.23 rows=6 width=238) (actual time=0.032..0.033 rows=1 loops=1)

              Output: id, data

              Recheck Cond: (orders.data @? '$."data"[*]."items"[*]."name"?(@ == "Dan")'::jsonpath)

              Heap Blocks: exact=1

              ->  Bitmap Index Scan on orders_data_idx  (cost=0.00..17.13 rows=6 width=0) (actual time=0.023..0.023 rows=1 loops=1)

                    Index Cond: (orders.data @? '$."data"[*]."items"[*]."name"?(@ == "Dan")'::jsonpath)

Planning Time: 0.350 ms

Execution Time: 0.127 ms

EXPLAIN

uniquename

"Ted"

"Bob"

"Dan"

SELECT 3