Postgres 11

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