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;

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;

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;

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;

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;

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

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

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

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

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