explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings
# exclusive inclusive rows x rows loops node
1. 617.321 105,851.703 ↑ 3,177.4 763,887 1

Gather (cost=145,143.34..10,376,511,318.35 rows=2,427,210,561 width=413) (actual time=104,897.854..105,851.703 rows=763,887 loops=1)

  • Output: wl_1.osm_id, wl.way
  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=33980335 read=17713, temp read=92248 written=94744
  • I/O Timings: read=133.980
2. 582.006 105,234.382 ↑ 4,099.9 190,972 4 / 4

Parallel Hash Join (cost=144,143.34..10,133,789,262.25 rows=782,971,149 width=413) (actual time=104,883.682..105,234.382 rows=190,972 loops=4)

  • Output: wl_1.osm_id, wl.way
  • Hash Cond: (wl_1.osm_id = wl.osm_id)
  • Buffers: shared hit=33980335 read=17713, temp read=92248 written=94744
  • I/O Timings: read=133.980
  • Worker 0: actual time=104882.068..105242.229 rows=191575 loops=1
  • Buffers: shared hit=8134268 read=4269, temp read=23125 written=23896
  • I/O Timings: read=35.193
  • Worker 1: actual time=104882.372..105238.540 rows=193262 loops=1
  • Buffers: shared hit=8607643 read=4437, temp read=23176 written=24016
  • I/O Timings: read=34.224
  • Worker 2: actual time=104874.211..105221.664 rows=191999 loops=1
  • Buffers: shared hit=8604352 read=4557, temp read=22809 written=21368
  • I/O Timings: read=34.985
3. 785.218 103,909.116 ↑ 4,119.1 190,084 4 / 4

Nested Loop (cost=0.41..10,122,035,756.85 rows=782,971,149 width=8) (actual time=0.731..103,909.116 rows=190,084 loops=4)

  • Output: wl_1.osm_id
  • Buffers: shared hit=33879438 read=17713
  • I/O Timings: read=133.980
  • Worker 0: actual time=0.502..103902.684 rows=180844 loops=1
  • Buffers: shared hit=8108820 read=4269
  • I/O Timings: read=35.193
  • Worker 1: actual time=0.711..103906.177 rows=192277 loops=1
  • Buffers: shared hit=8582016 read=4437
  • I/O Timings: read=34.224
  • Worker 2: actual time=0.767..103911.254 rows=192746 loops=1
  • Buffers: shared hit=8581781 read=4557
  • I/O Timings: read=34.985
4. 408.750 408.750 ↑ 1.3 1,467,359 4 / 4

Parallel Seq Scan on osm.roads_mv r (cost=0.00..238,334.67 rows=1,893,367 width=244) (actual time=0.022..408.750 rows=1,467,359 loops=4)

  • Output: r.gid, r.osm_id, r.way
  • Buffers: shared hit=219401
  • Worker 0: actual time=0.019..444.715 rows=1406415 loops=1
  • Buffers: shared hit=52354
  • Worker 1: actual time=0.018..429.359 rows=1483711 loops=1
  • Buffers: shared hit=55372
  • Worker 2: actual time=0.044..426.298 rows=1488986 loops=1
  • Buffers: shared hit=55724
5. 102,715.148 102,715.148 ↓ 0.0 0 5,869,437 / 4

Index Scan using gix_osm_waterway_buffer_15m on osm.waterway_buffer wl_1 (cost=0.41..5,343.84 rows=208 width=1,575) (actual time=0.065..0.070 rows=0 loops=5,869,437)

  • Output: wl_1.gid, wl_1.osm_id, wl_1.way_15m
  • Index Cond: (wl_1.way_15m && r.way)
  • Filter: st_intersects(r.way, wl_1.way_15m)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=33660037 read=17713
  • I/O Timings: read=133.980
  • Worker 0: actual time=0.067..0.073 rows=0 loops=1406415
  • Buffers: shared hit=8056466 read=4269
  • I/O Timings: read=35.193
  • Worker 1: actual time=0.064..0.069 rows=0 loops=1483711
  • Buffers: shared hit=8526644 read=4437
  • I/O Timings: read=34.224
  • Worker 2: actual time=0.064..0.069 rows=0 loops=1488986
  • Buffers: shared hit=8526057 read=4557
  • I/O Timings: read=34.985
6. 518.387 743.260 ↑ 1.3 440,714 4 / 4

Parallel Hash (cost=106,493.19..106,493.19 rows=568,619 width=413) (actual time=743.260..743.260 rows=440,714 loops=4)

  • Output: wl.way, wl.osm_id
  • Buckets: 16384 Batches: 256 Memory Usage: 3136kB
  • Buffers: shared hit=100807, temp written=90648
  • Worker 0: actual time=739.836..739.836 rows=447604 loops=1
  • Buffers: shared hit=25418, temp written=22872
  • Worker 1: actual time=739.803..739.803 rows=453399 loops=1
  • Buffers: shared hit=25597, temp written=22992
  • Worker 2: actual time=739.728..739.728 rows=396022 loops=1
  • Buffers: shared hit=22541, temp written=20344
7. 224.873 224.873 ↑ 1.3 440,714 4 / 4

Parallel Seq Scan on osm.waterway_line wl (cost=0.00..106,493.19 rows=568,619 width=413) (actual time=0.020..224.873 rows=440,714 loops=4)

  • Output: wl.way, wl.osm_id
  • Buffers: shared hit=100807
  • Worker 0: actual time=0.025..239.240 rows=447604 loops=1
  • Buffers: shared hit=25418
  • Worker 1: actual time=0.016..241.268 rows=453399 loops=1
  • Buffers: shared hit=25597
  • Worker 2: actual time=0.017..231.564 rows=396022 loops=1
  • Buffers: shared hit=22541
Planning time : 1.487 ms
Execution time : 105,889.675 ms