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
INSERT 0 3
CREATE INDEX
id |
---|
3 |
5 |
SELECT 2
id |
---|
3 |
5 |
6 |
SELECT 3
menu_id | dishes |
---|---|
1 | {"desserts": [{"id": 1, "name": "chocolate cake"}, {"id": 2, "name": "banana split"}], "appetizers": [{"id": 3, "name": "nachos"}, {"id": 4, "name": "mozzarella sticks"}, {"id": 5, "name": "buffalo wings"}]} |
2 | {"main": [{"id": 6, "name": "duck"}]} |
SELECT 2
menu_id | jsonb_path_query_array |
---|---|
1 | [3, 5] |
2 | [6] |
3 | [] |
SELECT 3
SET
QUERY PLAN |
---|
Nested Loop Semi Join (cost=2.53..28.25 rows=1 width=4) |
-> Function Scan on unnest i (cost=0.00..0.04 rows=4 width=4) |
-> Bitmap Heap Scan on menus (cost=2.53..6.55 rows=1 width=32) |
Recheck Cond: (dishes @? (format('$.*[*].id ? (@ == %s)'::text, i.id))::jsonpath) |
-> Bitmap Index Scan on foo (cost=0.00..2.53 rows=1 width=0) |
Index Cond: (dishes @? (format('$.*[*].id ? (@ == %s)'::text, i.id))::jsonpath) |
EXPLAIN
QUERY PLAN |
---|
Bitmap Heap Scan on menus (cost=65.69..69.70 rows=1 width=36) |
Recheck Cond: (dishes @? '$.*[*]."id"?(((@ == 3 || @ == 5) || @ == 7) || @ == 6)'::jsonpath) |
-> Bitmap Index Scan on foo (cost=0.00..65.69 rows=1 width=0) |
Index Cond: (dishes @? '$.*[*]."id"?(((@ == 3 || @ == 5) || @ == 7) || @ == 6)'::jsonpath) |
EXPLAIN
QUERY PLAN |
---|
Nested Loop Semi Join (cost=10000000000.00..10000000001.20 rows=1 width=4) |
Join Filter: jsonb_path_exists(menus.dishes, '$.*[*]."id"?(@ == $"id")'::jsonpath, jsonb_build_object('id', i.id), false) |
-> Function Scan on unnest i (cost=0.00..0.03 rows=3 width=4) |
-> Materialize (cost=10000000000.00..10000000001.05 rows=3 width=32) |
-> Seq Scan on menus (cost=10000000000.00..10000000001.03 rows=3 width=32) |
JIT: |
Functions: 8 |
Options: Inlining true, Optimization true, Expressions true, Deforming true |
EXPLAIN