explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings
# exclusive inclusive rows x rows loops node
1. 554.660 14,401.802 ↑ 3.1 79,474 1

Hash Join (cost=228,179,104.12..228,302,121.49 rows=247,513 width=417) (actual time=13,638.151..14,401.802 rows=79,474 loops=1)

  • Hash Cond: (wl.osm_id = wl_1.osm_id)
  • Buffers: shared hit=3,653,642 read=19,892
  • I/O Timings: read=329.389
  • Settings: jit = 'off', max_parallel_workers_per_gather = '3', random_page_cost = '1.9', work_mem = '50MB'
2. 224.782 224.782 ↓ 1.0 1,763,285 1

Seq Scan on waterway_line wl (cost=0.00..118,404.56 rows=1,757,256 width=417) (actual time=0.022..224.782 rows=1,763,285 loops=1)

  • Buffers: shared hit=100,832
3. 19.340 13,622.360 ↑ 3.1 79,446 1

Hash (cost=228,176,010.20..228,176,010.20 rows=247,513 width=8) (actual time=13,622.360..13,622.360 rows=79,446 loops=1)

  • Buckets: 262,144 Batches: 1 Memory Usage: 5,152kB
  • Buffers: shared hit=3,552,810 read=19,892
  • I/O Timings: read=329.389
4. 132.264 13,603.020 ↑ 3.1 79,446 1

HashAggregate (cost=228,171,059.94..228,173,535.07 rows=247,513 width=8) (actual time=13,580.684..13,603.020 rows=79,446 loops=1)

  • Group Key: wl_1.osm_id
  • Buffers: shared hit=3,552,810 read=19,892
  • I/O Timings: read=329.389
5. 0.000 13,470.756 ↑ 328.4 131,241 1

Gather (cost=1,000.28..228,063,314.88 rows=43,098,026 width=8) (actual time=1.030..13,470.756 rows=131,241 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=3,552,810 read=19,892
  • I/O Timings: read=329.389
6. 136.404 13,506.561 ↑ 423.7 32,810 4 / 4

Nested Loop (cost=0.29..223,752,512.28 rows=13,902,589 width=8) (actual time=1.013..13,506.561 rows=32,810 loops=4)

  • Buffers: shared hit=3,552,810 read=19,892
  • I/O Timings: read=329.389
7. 137.015 137.015 ↑ 1.3 153,874 4 / 4

Parallel Seq Scan on roads_mv r (cost=0.00..23,957.47 rows=198,547 width=233) (actual time=0.048..137.015 rows=153,874 loops=4)

  • Buffers: shared hit=2,080 read=19,892
  • I/O Timings: read=329.389
8. 13,233.142 13,233.142 ↓ 0.0 0 615,495 / 4

Index Scan using gix_osm_waterway_buffer_15m on waterway_buffer wl_1 (cost=0.29..1,126.38 rows=45 width=1,489) (actual time=0.075..0.086 rows=0 loops=615,495)

  • Index Cond: (way_15m && r.way)
  • Filter: st_intersects(r.way, way_15m)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=3,550,730
Planning time : 3.025 ms
Execution time : 14,408.736 ms