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. 2555298 fiddles created (37428 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
count
8997
 hidden batch(es)


SELECT COUNT(id) FROM all_json WHERE data @? '$.second[*].third[*].fourth[*].fifth[*].id ? (@ > 30000)'
count
8997
 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
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
 hidden batch(es)


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