Postgres 12

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 first (
    id INT PRIMARY KEY,
    title TEXT
);
CREATE TABLE second (
    id INT PRIMARY KEY,
    first_id INT,
    title TEXT
);
CREATE INDEX second_first_id ON second (first_id);
CREATE TABLE third (
    id INT PRIMARY KEY,
    second_id INT,
    title TEXT
);
CREATE INDEX third_second_id ON third (second_id);
CREATE TABLE fourth (
    id INT PRIMARY KEY,
    third_id INT,
    title TEXT
);
CREATE INDEX fourth_third_id ON fourth (third_id);
CREATE TABLE fifth (
    id INT PRIMARY KEY,
    fourth_id INT,
    title TEXT
);
CREATE INDEX fifth_fourth_id ON fifth (fourth_id);
WITH numbers AS (
  SELECT *
  FROM generate_series(1, 10000)
)

INSERT INTO first(id, title)
SELECT 
    numbers.generate_series,
    md5(random()::text)
FROM numbers;
10000 rows affected

WITH numbers AS (
  SELECT *
  FROM generate_series(1, 100000)
)

INSERT INTO second(id, first_id, title)
SELECT 
    numbers.generate_series,
    (round(random() * (10000 - 1)) + 1) AS first_id,
    md5(random()::text)
FROM numbers;
100000 rows affected

WITH numbers AS (
  SELECT *
  FROM generate_series(1, 100000)
)

INSERT INTO third(id, second_id, title)
SELECT 
    numbers.generate_series,
    (round(random() * (60000 - 1)) + 1) AS second_id,
    md5(random()::text)
FROM numbers;
100000 rows affected

WITH numbers AS (
  SELECT *
  FROM generate_series(1, 100000)
)

INSERT INTO fourth(id, third_id, title)
SELECT 
    numbers.generate_series,
    (round(random() * (60000 - 1)) + 1) AS third_id,
    md5(random()::text)
FROM numbers;
100000 rows affected

WITH numbers AS (
  SELECT *
  FROM generate_series(1, 100000)
)

INSERT INTO fifth(id, fourth_id, title)
SELECT 
    numbers.generate_series,
    (round(random() * (60000 - 1)) + 1) AS fourth_id,
    md5(random()::text)
FROM numbers;
100000 rows affected

CREATE TABLE all_json (
    id INTEGER NOT NULL,
    data JSONB DEFAULT '{}'
);
CREATE INDEX all_json_data ON all_json USING gin(data);
WITH fifth_json AS (
    SELECT 
        fourth_id,
        coalesce(json_agg(json_build_object(
            'id', id,
            'title', title
        )), '[]'::json) AS jsoned
        FROM fifth
        GROUP BY fourth_id
),
fourth_json AS (
    SELECT 
        third_id,
        coalesce(json_agg(json_build_object(
            'id', id,
            'title', title,
            'fifth', fifth_json.jsoned
        )), '[]'::json) AS jsoned
    FROM fourth
    LEFT JOIN fifth_json
    ON fifth_json.fourth_id = id
    GROUP BY third_id
),
third_json AS (
    SELECT 
        second_id,
        coalesce(json_agg(json_build_object(
            'id', id,
            'title', title,
            'fourth', fourth_json.jsoned
        )), '[]'::json) AS jsoned
    FROM third
    LEFT JOIN fourth_json
    ON fourth_json.third_id = id
    GROUP BY second_id
),
second_json AS (
    SELECT 
        first_id,
        coalesce(json_agg(json_build_object(
            'id', id,
            'title', title,
            'third', third_json.jsoned
        )), '[]'::json) AS jsoned
    FROM second
    LEFT JOIN third_json
    ON third_json.second_id = id
    GROUP BY first_id
)
INSERT INTO all_json(id, data)
SELECT
    id,
    json_build_object(
        'id', id,
        'title', title,
        'second', coalesce(json_agg(second_json.jsoned), '[]'::json)
    ) AS jsoned
FROM first
LEFT JOIN second_json
ON second_json.first_id = id
GROUP BY id
10000 rows affected

SELECT
    COUNT(DISTINCT first.id)
FROM first
LEFT JOIN second
    ON second.first_id = first.id
LEFT JOIN third
    ON third.second_id = second.id
LEFT JOIN fourth
    ON fourth.third_id = third.id
LEFT JOIN fifth
    ON fifth.fourth_id = fourth.id
WHERE
    fifth.id > 30000


count


8997


SELECT COUNT(id) 
FROM all_json
WHERE data @? '$.second[*].third[*].fourth[*].fifth[*].id ? (@ > 30000)'


count


8997


EXPLAIN ANALYZE
SELECT
    *
FROM first
LEFT JOIN second
    ON second.first_id = first.id
LEFT JOIN third
    ON third.second_id = second.id
LEFT JOIN fourth
    ON fourth.third_id = third.id
LEFT JOIN fifth
    ON fifth.fourth_id = fourth.id
WHERE
    fifth.id > 30000


QUERY PLAN


Hash Join  (cost=13991.67..21269.92 rows=37400 width=196) (actual time=101.851..323.456 rows=70000 loops=1)

  Hash Cond: (second.first_id = first.id)

  ->  Hash Join  (cost=13667.64..20847.67 rows=37400 width=160) (actual time=99.280..298.649 rows=70000 loops=1)

        Hash Cond: (third.second_id = second.id)

        ->  Hash Join  (cost=9331.14..14219.99 rows=37400 width=120) (actual time=67.424..192.355 rows=70000 loops=1)

              Hash Cond: (fourth.third_id = third.id)

              ->  Hash Join  (cost=4994.64..7958.32 rows=37400 width=80) (actual time=35.754..98.504 rows=70000 loops=1)

                    Hash Cond: (fifth.fourth_id = fourth.id)

                    ->  Bitmap Heap Scan on fifth  (cost=658.14..2060.64 rows=37400 width=40) (actual time=4.026..17.148 rows=70000 loops=1)

                          Recheck Cond: (id > 30000)

                          Heap Blocks: exact=655

                          ->  Bitmap Index Scan on fifth_pkey  (cost=0.00..648.79 rows=37400 width=0) (actual time=3.948..3.949 rows=70000 loops=1)

                                Index Cond: (id > 30000)

                    ->  Hash  (cost=2057.00..2057.00 rows=112200 width=40) (actual time=31.538..31.539 rows=100000 loops=1)

                          Buckets: 65536  Batches: 4  Memory Usage: 2296kB

                          ->  Seq Scan on fourth  (cost=0.00..2057.00 rows=112200 width=40) (actual time=0.019..13.785 rows=100000 loops=1)

              ->  Hash  (cost=2057.00..2057.00 rows=112200 width=40) (actual time=31.607..31.607 rows=100000 loops=1)

                    Buckets: 65536  Batches: 4  Memory Usage: 2296kB

                    ->  Seq Scan on third  (cost=0.00..2057.00 rows=112200 width=40) (actual time=0.020..13.969 rows=100000 loops=1)

        ->  Hash  (cost=2057.00..2057.00 rows=112200 width=40) (actual time=31.780..31.781 rows=100000 loops=1)

              Buckets: 65536  Batches: 4  Memory Usage: 2296kB

              ->  Seq Scan on second  (cost=0.00..2057.00 rows=112200 width=40) (actual time=0.017..14.041 rows=100000 loops=1)

  ->  Hash  (cost=190.68..190.68 rows=10668 width=36) (actual time=2.550..2.551 rows=10000 loops=1)

        Buckets: 16384  Batches: 1  Memory Usage: 802kB

        ->  Seq Scan on first  (cost=0.00..190.68 rows=10668 width=36) (actual time=0.023..1.317 rows=10000 loops=1)

Planning Time: 0.440 ms

Execution Time: 326.313 ms


EXPLAIN ANALYZE
SELECT * 
FROM all_json
WHERE data @? '$.second[*].third[*].fourth[*].fifth[*].id ? (@ > 30000)'


QUERY PLAN


Seq Scan on all_json  (cost=0.00..2799.68 rows=137 width=36) (actual time=0.038..139.652 rows=8997 loops=1)

  Filter: (data @? '$."second"[*]."third"[*]."fourth"[*]."fifth"[*]."id"?(@ > 30000)'::jsonpath)

  Rows Removed by Filter: 1003

Planning Time: 0.092 ms

Execution Time: 140.443 ms