explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 68Ev : OSM US West - Waterways near Roads MV and PreBuffer CTE

Settings
# exclusive inclusive rows x rows loops node
1. 3,167.786 133,989.061 ↓ 1.1 423,748 1

Hash Join (cost=10,936,169,993.89..10,936,480,808.97 rows=403,194 width=402) (actual time=130,046.642..133,989.061 rows=423,748 loops=1)

  • Hash Cond: (wl.osm_id = wl_1.osm_id)
  • Buffers: shared hit=18023306 read=470395 dirtied=442475 written=440421, temp read=79542 written=79555
  • I/O Timings: read=2222.393 write=6688.018
  • Settings: jit = 'off', max_parallel_workers_per_gather = '3', random_page_cost = '1.9'
2. 775.775 775.775 ↑ 1.0 1,763,285 1

Seq Scan on waterway_line wl (cost=0.00..118,479.58 rows=1,764,758 width=402) (actual time=0.045..775.775 rows=1,763,285 loops=1)

  • Buffers: shared hit=100832
3. 82.432 130,045.500 ↓ 1.1 423,501 1

Hash (cost=10,936,163,378.96..10,936,163,378.96 rows=403,194 width=8) (actual time=130,045.499..130,045.500 rows=423,501 loops=1)

  • Buckets: 131072 Batches: 8 Memory Usage: 3075kB
  • Buffers: shared hit=17922474 read=470395 dirtied=442475 written=440421, temp read=1686 written=2962
  • I/O Timings: read=2222.393 write=6688.018
4. 119.115 129,963.068 ↓ 1.1 423,501 1

Unique (cost=10,923,283,229.57..10,936,159,347.02 rows=403,194 width=8) (actual time=129,725.064..129,963.068 rows=423,501 loops=1)

  • Buffers: shared hit=17922474 read=470395 dirtied=442475 written=440421, temp read=1686 written=1699
  • I/O Timings: read=2222.393 write=6688.018
5. 545.993 129,843.953 ↑ 3,384.3 760,924 1

Sort (cost=10,923,283,229.57..10,929,721,288.30 rows=2,575,223,491 width=8) (actual time=129,725.061..129,843.953 rows=760,924 loops=1)

  • Sort Key: wl_1.osm_id
  • Sort Method: external merge Disk: 13488kB
  • Buffers: shared hit=17922474 read=470395 dirtied=442475 written=440421, temp read=1686 written=1699
  • I/O Timings: read=2222.393 write=6688.018
6. 0.000 129,297.960 ↑ 3,384.3 760,924 1

Gather (cost=1,000.41..10,422,166,618.90 rows=2,575,223,491 width=8) (actual time=1.585..129,297.960 rows=760,924 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=17922471 read=470395 dirtied=442475 written=440421
  • I/O Timings: read=2222.393 write=6688.018
7. 942.802 129,336.676 ↑ 4,366.9 190,231 4 / 4

Nested Loop (cost=0.41..10,164,643,269.80 rows=830,717,255 width=8) (actual time=1.182..129,336.676 rows=190,231 loops=4)

  • Buffers: shared hit=17922471 read=470395 dirtied=442475 written=440421
  • I/O Timings: read=2222.393 write=6688.018
8. 3,083.454 3,083.454 ↑ 1.3 519,960 4 / 4

Parallel Seq Scan on waterway_buffer wl_1 (cost=0.00..479,232.41 rows=675,741 width=1,559) (actual time=0.072..3,083.454 rows=519,960 loops=4)

  • Buffers: shared hit=2080 read=470395 dirtied=442475 written=440421
  • I/O Timings: read=2222.393 write=6688.018
9. 125,310.420 125,310.420 ↓ 0.0 0 2,079,841 / 4

Index Scan using gix_osm_roads_mv_subdivide on roads_mv r (cost=0.41..15,035.63 rows=588 width=244) (actual time=0.223..0.241 rows=0 loops=2,079,841)

  • Index Cond: (way && wl_1.way_15m)
  • Filter: st_intersects(way, wl_1.way_15m)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=17920391
Planning time : 18.854 ms
Execution time : 134,030.726 ms