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=33,980,335 read=17,713, temp read=92,248 written=94,744
  • I/O Timings: read=133.980
  • Settings: jit = 'off', max_parallel_workers_per_gather = '3', random_page_cost = '1.9'
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=33,980,335 read=17,713, temp read=92,248 written=94,744
  • I/O Timings: read=133.980
  • Worker 0: actual time=104,882.068..105242.229 rows=191,575 loops=1
  • Buffers: shared hit=8,134,268 read=4,269, temp read=23,125 written=23,896
  • I/O Timings: read=35.193
  • Worker 1: actual time=104,882.372..105238.540 rows=193,262 loops=1
  • Buffers: shared hit=8,607,643 read=4,437, temp read=23,176 written=24,016
  • I/O Timings: read=34.224
  • Worker 2: actual time=104,874.211..105221.664 rows=191,999 loops=1
  • Buffers: shared hit=8,604,352 read=4,557, temp read=22,809 written=21,368
  • 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=33,879,438 read=17,713
  • I/O Timings: read=133.980
  • Worker 0: actual time=0.502..103902.684 rows=180,844 loops=1
  • Buffers: shared hit=8,108,820 read=4,269
  • I/O Timings: read=35.193
  • Worker 1: actual time=0.711..103906.177 rows=192,277 loops=1
  • Buffers: shared hit=8,582,016 read=4,437
  • I/O Timings: read=34.224
  • Worker 2: actual time=0.767..103911.254 rows=192,746 loops=1
  • Buffers: shared hit=8,581,781 read=4,557
  • 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=219,401
  • Worker 0: actual time=0.019..444.715 rows=1,406,415 loops=1
  • Buffers: shared hit=52,354
  • Worker 1: actual time=0.018..429.359 rows=1,483,711 loops=1
  • Buffers: shared hit=55,372
  • Worker 2: actual time=0.044..426.298 rows=1,488,986 loops=1
  • Buffers: shared hit=55,724
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=33,660,037 read=17,713
  • I/O Timings: read=133.980
  • Worker 0: actual time=0.067..0.073 rows=0 loops=1,406,415
  • Buffers: shared hit=8,056,466 read=4,269
  • I/O Timings: read=35.193
  • Worker 1: actual time=0.064..0.069 rows=0 loops=1,483,711
  • Buffers: shared hit=8,526,644 read=4,437
  • I/O Timings: read=34.224
  • Worker 2: actual time=0.064..0.069 rows=0 loops=1,488,986
  • Buffers: shared hit=8,526,057 read=4,557
  • 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: 16,384 Batches: 256 Memory Usage: 3,136kB
  • Buffers: shared hit=100,807, temp written=90,648
  • Worker 0: actual time=739.836..739.836 rows=447,604 loops=1
  • Buffers: shared hit=25,418, temp written=22,872
  • Worker 1: actual time=739.803..739.803 rows=453,399 loops=1
  • Buffers: shared hit=25,597, temp written=22,992
  • Worker 2: actual time=739.728..739.728 rows=396,022 loops=1
  • Buffers: shared hit=22,541, temp written=20,344
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=100,807
  • Worker 0: actual time=0.025..239.240 rows=447,604 loops=1
  • Buffers: shared hit=25,418
  • Worker 1: actual time=0.016..241.268 rows=453,399 loops=1
  • Buffers: shared hit=25,597
  • Worker 2: actual time=0.017..231.564 rows=396,022 loops=1
  • Buffers: shared hit=22,541
Planning time : 1.487 ms
Execution time : 105,889.675 ms