add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
10000 rows affected
100000 rows affected
100000 rows affected
100000 rows affected
200000 rows affected
10000 rows affected
count
8436
count
8436
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
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
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"}]
count
344821