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. 2555464 fiddles created (37475 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, 200000) ) INSERT INTO fifth(id, fourth_id, title) SELECT numbers.generate_series, (round(random() * (60000 - 1)) + 1) AS fourth_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 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
8436
 hidden batch(es)


-- Количество записей одинково, значит фильтры работают одинаково SELECT COUNT(id) FROM all_json WHERE data @? '$.second[*].third[*].fourth[*] ? (@.id > 30000).fifth[*].id ? (@ > 30000)'
count
8436
 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
Gather Merge (cost=39326.79..47332.74 rows=69617 width=234) (actual time=706.262..769.067 rows=85149 loops=1)
Workers Planned: 1
Workers Launched: 1
-> Sort (cost=38326.78..38500.82 rows=69617 width=234) (actual time=444.950..472.459 rows=42574 loops=2)
Sort Key: first.title
Sort Method: external merge Disk: 21400kB
Worker 0: Sort Method: quicksort Memory: 25kB
-> Hash Join (cost=12108.26..24873.08 rows=69617 width=234) (actual time=145.166..314.966 rows=42574 loops=2)
Hash Cond: (second.first_id = first.id)
-> Hash Join (cost=11799.26..24381.26 rows=69617 width=164) (actual time=131.270..280.334 rows=42574 loops=2)
Hash Cond: (third.second_id = second.id)
-> Hash Join (cost=7735.26..16671.50 rows=69617 width=123) (actual time=78.991..177.602 rows=42574 loops=2)
Hash Cond: (fourth.third_id = third.id)
-> Hash Join (cost=3671.26..9641.74 rows=69617 width=82) (actual time=15.962..74.103 rows=42574 loops=2)
Hash Cond: (fifth.fourth_id = fourth.id)
-> Parallel Seq Scan on fifth (cost=0.00..3340.59 rows=99765 width=41) (actual time=1.797..18.034 rows=85000 loops=2)
Filter: (id > 30000)
Rows Removed by Filter: 15000
-> Hash (cost=2185.00..2185.00 rows=69781 width=41) (actual time=28.177..28.178 rows=70000 loops=1)
Buckets: 65536 Batches: 2 Memory Usage: 3004kB
-> Seq Scan on fourth (cost=0.00..2185.00 rows=69781 width=41) (actual time=3.953..15.894 rows=70000 loops=1)
Filter: (id > 30000)
Rows Removed by Filter: 30000
-> Hash (cost=1935.00..1935.00 rows=100000 width=41) (actual time=62.307..62.308 rows=100000 loops=2)
Buckets: 65536 Batches: 4 Memory Usage: 2296kB
-> Seq Scan on third (cost=0.00..1935.00 rows=100000 width=41) (actual time=0.030..20.208 rows=100000 loops=2)
-> Hash (cost=1935.00..1935.00 rows=100000 width=41) (actual time=46.424..46.424 rows=100000 loops=2)
Buckets: 65536 Batches: 4 Memory Usage: 2296kB
-> Seq Scan on second (cost=0.00..1935.00 rows=100000 width=41) (actual time=0.031..20.828 rows=100000 loops=2)
-> Hash (cost=184.00..184.00 rows=10000 width=37) (actual time=2.844..2.845 rows=10000 loops=2)
Buckets: 16384 Batches: 1 Memory Usage: 802kB
-> Seq Scan on first (cost=0.00..184.00 rows=10000 width=37) (actual time=0.020..1.309 rows=10000 loops=2)
Planning Time: 3.940 ms
Execution Time: 784.391 ms
 hidden batch(es)


EXPLAIN ANALYZE SELECT * FROM all_json WHERE data @? '$.second[*].third[*].fourth[*] ? (@.id > 30000).fifth[*].id ? (@ > 30000)' ORDER BY data->'title'
QUERY PLAN
Sort (cost=2091.77..2092.03 rows=102 width=68) (actual time=329.040..334.939 rows=8436 loops=1)
Sort Key: ((data -> 'title'::text))
Sort Method: external merge Disk: 4344kB
-> Seq Scan on all_json (cost=0.00..2088.37 rows=102 width=68) (actual time=0.104..285.578 rows=8436 loops=1)
Filter: (data @? '$."second"[*]."third"[*]."fourth"[*]?(@."id" > 30000)."fifth"[*]."id"?(@ > 30000)'::jsonpath)
Rows Removed by Filter: 1564
Planning Time: 0.133 ms
Execution Time: 336.065 ms
 hidden batch(es)


-- Просто достаем все что нужно WITH fifth_level AS ( SELECT id, jsonb_path_query_array( data, '$.second[*].third[*].fourth[*] ? (@.id > 30000).fifth[*] ? (@.id > 30000)' ) AS data FROM all_json ORDER BY data->'title' LIMIT 10 ) SELECT * FROM fifth_level WHERE data != '[]'::JSONB
id data
6523 [{"id": 32608, "title": "2071ac4b80259e32a57feec4a5f46077"}, {"id": 65668, "title": "4dec7d7fc616baae29af9e5f60d260d8"}, {"id": 138176, "title": "ccc60ea5faf20ca6666ca59d255193bc"}, {"id": 183409, "title": "057c902c66e6c23ae94caf74b8e07163"}, {"id": 189337, "title": "63efd89c226b35f37041b44e3b59728b"}, {"id": 40404, "title": "02143c6c885f2e705868eec3bfb5d18d"}, {"id": 56330, "title": "c43f518b7db72885d57b038441293708"}, {"id": 76944, "title": "ee1fe10dc4d3bb3d9fb59dc50fc30e25"}, {"id": 86587, "title": "fcb6dc3ae8a4f0447c1a815c77437329"}, {"id": 135617, "title": "43426dbf5f12ea9cad68a20dd059b75e"}, {"id": 145929, "title": "73a108859e37a3d00f8a82eedc02eeff"}, {"id": 167408, "title": "f4c9e21d1746559fa0a040b67e3ef82d"}, {"id": 79814, "title": "b6a2ac780288e14be1049169b54e6408"}, {"id": 104792, "title": "e7dac2d8a9cf207dfd07daa33e0f945b"}, {"id": 138566, "title": "034a8bf5f3c71c3fb06842a8666bc80d"}, {"id": 194435, "title": "b9e7c1cfe3fb64c424a7619b888fa3be"}]
5810 [{"id": 47791, "title": "56c608cd8bd72999b2f3d36c52db82d0"}, {"id": 120785, "title": "bbbf17afcdfcb6748cad8f9beb0fa2c2"}, {"id": 173127, "title": "717052d3936eeb8f98346cefcdd346df"}, {"id": 126126, "title": "0a61e5fd2f9d80ea39a4aa6c307fd824"}, {"id": 62192, "title": "0dd2b66e4bd426077f4c281094e6a95a"}, {"id": 121196, "title": "f8af718d2a02a7d9f8f0f41a1d311f6f"}]
8529 [{"id": 115018, "title": "b9f5a56275f7123347b8ae0989b32168"}, {"id": 127756, "title": "cd9e3ab22c80b21417ee7c61d421058b"}, {"id": 173088, "title": "4b15623ce9cf5a0ec28ab9f81f4fec17"}]
4313 [{"id": 37753, "title": "a0bdea7fe1413976e5e7d5c54b964937"}, {"id": 95775, "title": "e39a34cd1c1b1dd27ac215441e4789fb"}, {"id": 144333, "title": "807aca447a64f0102350c1dad318ec29"}, {"id": 81724, "title": "61915a2dbd5cd99c1f5445b0b27a221c"}, {"id": 110497, "title": "b111848240c8290e553d72457a353d5e"}]
3354 [{"id": 85860, "title": "67891418eeb4c6301c1db06bee9530d7"}, {"id": 98546, "title": "df5baee61dd06a7b882d0c7eaf13da06"}, {"id": 108924, "title": "19c0b3b6b284b84f3fcafcb5a22bda53"}, {"id": 154904, "title": "33d5ac47a44984d06d3c9d074e5d7fbd"}, {"id": 158671, "title": "8e1f6b001099298fe0211f8dee9fb0e3"}, {"id": 189915, "title": "4e188a665d9f9ebf609530f76c2937e6"}, {"id": 199017, "title": "82ae95a1e7ea4ab4929085bd7ef3bb89"}, {"id": 32523, "title": "c6652d2939b4b6fb7546d008b920a94a"}, {"id": 84121, "title": "9988c1337bc05391ea3c401de2b838dd"}, {"id": 193471, "title": "a5379e9182566f03245d78fd96743ced"}]
6973 [{"id": 40606, "title": "4cd98e7df8a7fc4d21ad80fab5a5df9c"}, {"id": 67649, "title": "7ccd4c3cd3f88fbc803cd490c5e57643"}, {"id": 150616, "title": "16732d7f249b62f4ac47ad555faed9ed"}, {"id": 96571, "title": "4280fe2aa9a04781b3991984e3a2d4a5"}, {"id": 97061, "title": "5a461345222fe01ed3ef5a32f47cc072"}, {"id": 164366, "title": "84c1cb2cf19f78a7b0a77fbdf9c44c77"}, {"id": 164574, "title": "cff5cc4f4184e40b0605de1ed6b781a1"}, {"id": 196801, "title": "afdc7bb4511f16da7b16c056593e082e"}, {"id": 198556, "title": "d5e71f5f759831ecf75b632d12def37b"}, {"id": 48381, "title": "74a181f51c6e26a0acb786252df26335"}, {"id": 56341, "title": "d69a1e13f961c55334aad857f668dc9e"}, {"id": 60206, "title": "83eee86ba49a15ca43eb4f8606d03ef8"}, {"id": 97295, "title": "c2a9b7ee9a0d1d92c62e8e1db848a840"}, {"id": 155681, "title": "86e56d0d1925840c7e59855e33c6ae19"}, {"id": 154757, "title": "a63c410e6410d8bb11b144e9792c0b7a"}, {"id": 193904, "title": "54be8e383f4fb64c8869b2222cda22cb"}, {"id": 101602, "title": "0b17dac5ab5f5b907dc213775088b37e"}, {"id": 115870, "title": "53535c57171b9050a1a275528659ed90"}, {"id": 152193, "title": "d4894c797677c72f75a4afbe76b3d550"}, {"id": 179671, "title": "51a8d1573ae0240026e1cf990b3d6351"}, {"id": 185978, "title": "6b5ceb44081ca612361dbbcf1bcd85cd"}]
7949 [{"id": 68907, "title": "69a8e6768bdcc567b82e62319902d956"}, {"id": 95432, "title": "c60f7048adf50cc726d2f0819349fdc6"}, {"id": 105605, "title": "ad166c7972167456a2fed9c0a5f662a8"}, {"id": 157847, "title": "a5c6913c82c7e9b92f482c9d24e8daf6"}, {"id": 67839, "title": "eb41310b4fef166dea3d5c002c9f85f6"}, {"id": 122345, "title": "d00b8e79be2de875f8355782f3d6164e"}, {"id": 127765, "title": "7d5b9b120ffbc2f4d280c7b6fc7a97e4"}, {"id": 72582, "title": "02467ffc31bb5df0bca467b7d159fbc5"}, {"id": 133372, "title": "02071340a6352448bc4bafa0ced565b3"}, {"id": 60950, "title": "257d9476639560720b8999dd0110af4e"}, {"id": 113412, "title": "deb56c9af7719e9ff081e1a49b4bb222"}, {"id": 165889, "title": "5658ed1d4d15624107cc02af91d0386b"}, {"id": 184482, "title": "3a58947dd4fce6dd3e9af12bb9c7d78f"}, {"id": 74461, "title": "0e0ab7eeb65a966d4629361b68652658"}, {"id": 129546, "title": "5dbac23a616f021d5f5a05900adbca4e"}, {"id": 144185, "title": "47d4b08df3480778d87e9c039d07e721"}, {"id": 167395, "title": "f2d5f0578e3ab71d06ae3b1e3a3a07a1"}, {"id": 69395, "title": "9594d8117bf6bc14f3ecd8d659d48dfb"}, {"id": 71629, "title": "19ab21794a8d23f8d4d41cd84936ee22"}, {"id": 84854, "title": "61a18370836b62be458e1a4007aa30a0"}, {"id": 179555, "title": "98098c5c8cc7f1315e0eca700689898a"}]
5047 [{"id": 102955, "title": "f5ca7bba8de2969e52d2e4823757b4bd"}, {"id": 121674, "title": "cab3fa77b4870cbf5f70b5e89180dd4f"}, {"id": 158659, "title": "4e1630b7d71e72219566abc2243424fd"}, {"id": 69231, "title": "bd1c73dbde4427cb4db1d23c570d7b43"}, {"id": 101821, "title": "2366230b745a501d5ade46a0990090f6"}, {"id": 156732, "title": "43fe381076573487cc3eb5994df7e702"}, {"id": 187039, "title": "eb6a711b97fb9babe18d28786435f988"}, {"id": 56910, "title": "34cb045312946dc5c11ff3ab5a43cb8d"}, {"id": 156831, "title": "62c6901784a9faaa047683e520c96e34"}, {"id": 197814, "title": "8f1697d0e7350665cb57ff76667ca1c2"}, {"id": 61033, "title": "2327f309ba4eb20f7348963f5f2179a1"}, {"id": 158998, "title": "5230649d6b99fe51278957f2078b99f2"}, {"id": 161069, "title": "6b4e20cd96a51791ff44bb951e54b593"}, {"id": 76541, "title": "3f78719f45b9ddc23fa70033cfae6b7d"}, {"id": 178119, "title": "51420864b3509aa4bb8bcaf1be8e7a80"}, {"id": 179542, "title": "ae0a9b3b3a436d0ab108489503c46e93"}]
3015 [{"id": 36234, "title": "96c93c93b0c034aee63635f46268405e"}, {"id": 176140, "title": "eead8b154f5aa31e0e72d5a9f3bc2cf3"}, {"id": 33704, "title": "e3166aa420aa5919e494278b25a0ba7c"}, {"id": 51522, "title": "0ddb0c6cf923af9736a72bb91f0baaf3"}, {"id": 76018, "title": "87de06914cb06af2ff36e1547caaacee"}, {"id": 113711, "title": "81aab35654a1b8e2700d68b2ae3185c7"}, {"id": 149221, "title": "ecd830cb0a61cb327ccec978c46c3308"}, {"id": 185410, "title": "61e3481fc4806c845eacbba53f4af1a0"}, {"id": 67986, "title": "55229bafddac43ca5b42de9c1fa9f532"}, {"id": 140401, "title": "0d11ca5ac979789892bc3c4837d77e6b"}, {"id": 171441, "title": "a404058f412508c49737462fe8753ffc"}]
 hidden batch(es)


-- Смотри, долбоеб, ты нагенерил 350К строк своими джойнами. Найс греешь серверную. SELECT COUNT(*) 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
count
344821
 hidden batch(es)