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 slices(id INT, facebook_results JSON)
CREATE TABLE
INSERT INTO slices VALUES(169, '{ "table":{ "matches": [ { "table":{ "nodes":[ { "table":{ "crawl_date":"2013-06-21", "url":"http://example.com" } } ], "size":7962624, "score":47.059, "width":3456, "format":"MP4", "domain":"example.com" } } ] }}')
INSERT 0 1
explain (analyze,buffers)
SELECT
id AS slice_id,
json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table'-> 'size' AS match_size,
json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'score' AS match_score,
json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'width' AS match_width,
json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'format' AS match_format,
json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'domain' AS match_domain,
json_array_elements(json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'nodes') -> 'table' -> 'crawl_date' AS node_crawl_date,
json_array_elements(json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'nodes') -> 'table' -> 'url' AS node_url
FROM slices
WHERE id = 169
| QUERY PLAN |
|---|
| Result (cost=0.00..7095.45 rows=60000 width=228) (actual time=0.199..0.203 rows=1 loops=1) |
| Buffers: shared hit=1 |
| -> ProjectSet (cost=0.00..345.45 rows=60000 width=68) (actual time=0.133..0.136 rows=1 loops=1) |
| Buffers: shared hit=1 |
| -> ProjectSet (cost=0.00..28.95 rows=600 width=36) (actual time=0.109..0.111 rows=1 loops=1) |
| Buffers: shared hit=1 |
| -> Seq Scan on slices (cost=0.00..25.88 rows=6 width=36) (actual time=0.013..0.015 rows=1 loops=1) |
| Filter: (id = 169) |
| Buffers: shared hit=1 |
| Planning Time: 0.435 ms |
| Execution Time: 0.577 ms |
EXPLAIN
explain (analyze,buffers)
SELECT
id AS slice_id,
s.t -> 'size' AS match_size,
s.t -> 'score' AS match_score,
s.t -> 'width' AS match_width,
s.t -> 'format' AS match_format,
s.t -> 'domain' AS match_domain,
s.t2-> 'crawl_date' AS node_crawl_date,
s.t2-> 'url' AS node_url
FROM slices
,LATERAL (SELECT json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table',
json_array_elements(json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' -> 'nodes') -> 'table'
) s(t,t2)
WHERE id = 169
| QUERY PLAN |
|---|
| Nested Loop (cost=0.00..4845.51 rows=60000 width=228) (actual time=0.104..0.109 rows=1 loops=1) |
| Buffers: shared hit=1 |
| -> Seq Scan on slices (cost=0.00..25.88 rows=6 width=36) (actual time=0.012..0.014 rows=1 loops=1) |
| Filter: (id = 169) |
| Buffers: shared hit=1 |
| -> Result (cost=0.00..428.27 rows=10000 width=64) (actual time=0.072..0.074 rows=1 loops=1) |
| -> ProjectSet (cost=0.00..53.27 rows=10000 width=64) (actual time=0.067..0.069 rows=1 loops=1) |
| -> ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.047..0.047 rows=1 loops=1) |
| -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1) |
| Planning Time: 0.140 ms |
| Execution Time: 0.183 ms |
EXPLAIN
explain (analyze,buffers)
SELECT
id AS slice_id,
s.t -> 'size' AS match_size,
s.t -> 'score' AS match_score,
s.t -> 'width' AS match_width,
s.t -> 'format' AS match_format,
s.t -> 'domain' AS match_domain,
s2.t2-> 'crawl_date' AS node_crawl_date,
s2.t2-> 'url' AS node_url
FROM slices
,LATERAL (SELECT json_array_elements(facebook_results -> 'table' -> 'matches') -> 'table' ) s(t)
,LATERAL(SELECT json_array_elements(s.t -> 'nodes') -> 'table') s2(t2)
WHERE id = 169
| QUERY PLAN |
|---|
| Nested Loop (cost=0.00..3665.01 rows=60000 width=228) (actual time=0.096..0.102 rows=1 loops=1) |
| Buffers: shared hit=1 |
| -> Nested Loop (cost=0.00..53.01 rows=600 width=36) (actual time=0.042..0.045 rows=1 loops=1) |
| Buffers: shared hit=1 |
| -> Seq Scan on slices (cost=0.00..25.88 rows=6 width=36) (actual time=0.010..0.012 rows=1 loops=1) |
| Filter: (id = 169) |
| Buffers: shared hit=1 |
| -> Result (cost=0.00..2.52 rows=100 width=32) (actual time=0.029..0.030 rows=1 loops=1) |
| -> ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.025..0.026 rows=1 loops=1) |
| -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1) |
| -> Result (cost=0.00..2.27 rows=100 width=32) (actual time=0.027..0.028 rows=1 loops=1) |
| -> ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.019..0.021 rows=1 loops=1) |
| -> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1) |
| Planning Time: 0.096 ms |
| Execution Time: 0.184 ms |
EXPLAIN