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
AND
    fourth.id > 30000


count


7819


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


count


8959


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
AND
    fourth.id > 30000
ORDER BY first.title


QUERY PLAN


Sort  (cost=31016.75..31139.56 rows=49123 width=234) (actual time=413.205..437.987 rows=35134 loops=1)

  Sort Key: first.title

  Sort Method: external merge  Disk: 8824kB

  ->  Hash Join  (cost=12112.92..21645.06 rows=49123 width=234) (actual time=114.659..322.080 rows=35134 loops=1)

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

        ->  Hash Join  (cost=11803.92..21207.06 rows=49123 width=164) (actual time=111.393..303.192 rows=35134 loops=1)

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

              ->  Hash Join  (cost=7739.93..14311.11 rows=49123 width=123) (actual time=72.293..206.748 rows=35134 loops=1)

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

                    ->  Hash Join  (cost=3675.93..7895.16 rows=49123 width=82) (actual time=35.712..110.706 rows=35134 loops=1)

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

                          ->  Seq Scan on fifth  (cost=0.00..2185.00 rows=70182 width=41) (actual time=4.057..26.182 rows=70000 loops=1)

                                Filter: (id > 30000)

                                Rows Removed by Filter: 30000

                          ->  Hash  (cost=2185.00..2185.00 rows=69994 width=41) (actual time=31.504..31.505 rows=70000 loops=1)

                                Buckets: 65536  Batches: 2  Memory Usage: 3004kB

                                ->  Seq Scan on fourth  (cost=0.00..2185.00 rows=69994 width=41) (actual time=4.012..17.651 rows=70000 loops=1)

                                      Filter: (id > 30000)

                                      Rows Removed by Filter: 30000

                    ->  Hash  (cost=1935.00..1935.00 rows=100000 width=41) (actual time=36.447..36.448 rows=100000 loops=1)

                          Buckets: 65536  Batches: 4  Memory Usage: 2296kB

                          ->  Seq Scan on third  (cost=0.00..1935.00 rows=100000 width=41) (actual time=0.015..15.515 rows=100000 loops=1)

              ->  Hash  (cost=1935.00..1935.00 rows=100000 width=41) (actual time=37.045..37.045 rows=100000 loops=1)

                    Buckets: 65536  Batches: 4  Memory Usage: 2296kB

                    ->  Seq Scan on second  (cost=0.00..1935.00 rows=100000 width=41) (actual time=0.013..15.887 rows=100000 loops=1)

        ->  Hash  (cost=184.00..184.00 rows=10000 width=37) (actual time=3.244..3.245 rows=10000 loops=1)

              Buckets: 16384  Batches: 1  Memory Usage: 802kB

              ->  Seq Scan on first  (cost=0.00..184.00 rows=10000 width=37) (actual time=0.012..1.753 rows=10000 loops=1)

Planning Time: 2.734 ms

Execution Time: 445.302 ms


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


QUERY PLAN


Sort  (cost=3201.32..3201.33 rows=1 width=68) (actual time=514.285..526.707 rows=8959 loops=1)

  Sort Key: ((data -> 'title'::text))

  Sort Method: external merge  Disk: 7168kB

  ->  Seq Scan on all_json  (cost=0.00..3201.31 rows=1 width=68) (actual time=0.121..454.346 rows=8959 loops=1)

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

        Rows Removed by Filter: 1041

Planning Time: 0.171 ms

Execution Time: 528.293 ms