clear markdown compare help best fiddles feedback dbanow.uk
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 2555451 fiddles created (37472 in the last week).

CREATE TABLE first ( id INT PRIMARY KEY, title TEXT );
 hidden batch(es)


CREATE TABLE second ( id INT PRIMARY KEY, first_id INT, title TEXT ); CREATE INDEX second_first_id ON second (first_id);
 hidden batch(es)


CREATE TABLE third ( id INT PRIMARY KEY, second_id INT, title TEXT ); CREATE INDEX third_second_id ON third (second_id);
 hidden batch(es)


CREATE TABLE fourth ( id INT PRIMARY KEY, third_id INT, title TEXT ); CREATE INDEX fourth_third_id ON fourth (third_id);
 hidden batch(es)


CREATE TABLE fifth ( id INT PRIMARY KEY, fourth_id INT, title TEXT ); CREATE INDEX fifth_fourth_id ON fifth (fourth_id);
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


CREATE TABLE all_json ( id INTEGER NOT NULL, data JSONB DEFAULT '{}' ); CREATE INDEX all_json_data ON all_json USING gin(data);
 hidden batch(es)


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
 hidden batch(es)


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
 hidden batch(es)


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


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
 hidden batch(es)


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
 hidden batch(es)