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. 2555573 fiddles created (37483 in the last week).

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


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


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


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


CREATE TABLE fifth ( id INT PRIMARY KEY, fourth_id INT REFERENCES fourth(id), first_id INT REFERENCES first(id), title TEXT ); CREATE INDEX fifth_fourth_id ON fifth (fourth_id); CREATE INDEX fifth_first_id ON fifth (first_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, 200000) ) INSERT INTO fifth(id, fourth_id,first_id, title) SELECT numbers.generate_series, (round(random() * (60000 - 1)) + 1) AS fourth_id, (round(random() * (10000 - 1)) + 1) AS first_id, md5(random()::text) FROM numbers;
200000 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 fifth ON fifth.first_id = first.id 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 WHERE fifth.id > 30000
count
10000
 hidden batch(es)


-- Количество записей одинково, значит фильтры работают одинаково SELECT COUNT(id) FROM all_json WHERE data @? '$.second[*].third[*].fourth[*].fifth[*].id ? (@ > 30000)'
count
9346
 hidden batch(es)


EXPLAIN ANALYZE SELECT * FROM first LEFT JOIN fifth ON fifth.first_id = first.id 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 WHERE fifth.id > 30000 ORDER BY first.title LIMIT 400
QUERY PLAN
Limit (cost=1.58..3582.40 rows=400 width=232) (actual time=0.901..3.915 rows=400 loops=1)
-> Nested Loop (cost=1.58..6631833.85 rows=740817 width=232) (actual time=0.900..3.871 rows=400 loops=1)
-> Nested Loop Left Join (cost=1.16..3711337.27 rows=112200 width=156) (actual time=0.622..2.926 rows=20 loops=1)
-> Nested Loop Left Join (cost=0.87..1942561.27 rows=112200 width=116) (actual time=0.619..2.000 rows=14 loops=1)
-> Nested Loop Left Join (cost=0.58..173669.27 rows=112200 width=76) (actual time=0.445..0.607 rows=11 loops=1)
-> Index Scan using first_title on first (cost=0.29..872.30 rows=10668 width=36) (actual time=0.231..0.241 rows=2 loops=1)
-> Index Scan using second_first_id on second (cost=0.29..10.59 rows=561 width=40) (actual time=0.142..0.177 rows=6 loops=2)
Index Cond: (first_id = first.id)
-> Index Scan using third_second_id on third (cost=0.29..10.16 rows=561 width=40) (actual time=0.123..0.124 rows=1 loops=11)
Index Cond: (second_id = second.id)
-> Index Scan using fourth_third_id on fourth (cost=0.29..10.15 rows=561 width=40) (actual time=0.061..0.064 rows=1 loops=14)
Index Cond: (third_id = third.id)
-> Index Scan using fifth_first_id on fifth (cost=0.42..22.51 rows=352 width=44) (actual time=0.020..0.042 rows=20 loops=20)
Index Cond: (first_id = first.id)
Filter: (id > 30000)
Rows Removed by Filter: 3
Planning Time: 0.806 ms
Execution Time: 3.994 ms
 hidden batch(es)


EXPLAIN ANALYZE SELECT * FROM all_json WHERE data @? '$.second[*].third[*].fourth[*].fifth[*].id ? (@ > 30000)' ORDER BY data->'title' LIMIT 400
QUERY PLAN
Limit (cost=2112.56..2112.82 rows=104 width=68) (actual time=296.469..296.536 rows=400 loops=1)
-> Sort (cost=2112.56..2112.82 rows=104 width=68) (actual time=296.467..296.505 rows=400 loops=1)
Sort Key: ((data -> 'title'::text))
Sort Method: top-N heapsort Memory: 601kB
-> Seq Scan on all_json (cost=0.00..2109.07 rows=104 width=68) (actual time=0.039..276.074 rows=9346 loops=1)
Filter: (data @? '$."second"[*]."third"[*]."fourth"[*]."fifth"[*]."id"?(@ > 30000)'::jsonpath)
Rows Removed by Filter: 654
Planning Time: 0.101 ms
Execution Time: 296.587 ms
 hidden batch(es)