explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 6fZA : OSM Colorado - Waterways near Roads MV and PreBuffer CTE - Parallel Disabled

Settings
# exclusive inclusive rows x rows loops node
1. 310.448 33,994.130 ↑ 3.2 79,404 1

Hash Join (cost=693,523,278.20..693,591,032.23 rows=256,673 width=383) (actual time=33,602.534..33,994.130 rows=79,404 loops=1)

  • Output: wl_1.osm_id, wl.way
  • Inner Unique: true
  • Hash Cond: (wl.osm_id = wl_1.osm_id)
  • Buffers: shared hit=3,536,261, temp read=14,653 written=14,655
  • Settings: jit = 'off', max_parallel_workers_per_gather = '0', random_page_cost = '1.5'
2. 81.290 81.290 ↑ 1.0 402,457 1

Seq Scan on osm.waterway_line wl (cost=0.00..25,604.57 rows=402,457 width=383) (actual time=0.009..81.290 rows=402,457 loops=1)

  • Output: wl.way, wl.osm_id
  • Buffers: shared hit=21,580
3. 8.975 33,602.392 ↑ 3.2 79,376 1

Hash (cost=693,519,066.78..693,519,066.78 rows=256,673 width=8) (actual time=33,602.392..33,602.392 rows=79,376 loops=1)

  • Output: wl_1.osm_id
  • Buckets: 131,072 Batches: 4 Memory Usage: 1,797kB
  • Buffers: shared hit=3,514,681, temp read=291 written=496
4. 12.783 33,593.417 ↑ 3.2 79,376 1

Unique (cost=693,276,514.78..693,516,500.05 rows=256,673 width=8) (actual time=33,568.610..33,593.417 rows=79,376 loops=1)

  • Output: wl_1.osm_id
  • Buffers: shared hit=3,514,681, temp read=291 written=293
5. 65.376 33,580.634 ↑ 366.1 131,101 1

Sort (cost=693,276,514.78..693,396,507.42 rows=47,997,054 width=8) (actual time=33,568.609..33,580.634 rows=131,101 loops=1)

  • Output: wl_1.osm_id
  • Sort Key: wl_1.osm_id
  • Sort Method: external merge Disk: 2,328kB
  • Buffers: shared hit=3,514,681, temp read=291 written=293
6. 562.757 33,515.258 ↑ 366.1 131,101 1

Nested Loop (cost=0.29..685,887,393.68 rows=47,997,054 width=8) (actual time=0.705..33,515.258 rows=131,101 loops=1)

  • Output: wl_1.osm_id
  • Buffers: shared hit=3,514,681
7. 82.809 82.809 ↑ 1.0 608,698 1

Seq Scan on osm.roads_mv r (cost=0.00..27,356.98 rows=608,698 width=236) (actual time=0.004..82.809 rows=608,698 loops=1)

  • Output: r.osm_id, r.way
  • Buffers: shared hit=21,270
8. 32,869.692 32,869.692 ↓ 0.0 0 608,698

Index Scan using gix_osm_waterway_buffer_15m on osm.waterway_buffer wl_1 (cost=0.29..1,126.32 rows=45 width=1,496) (actual time=0.048..0.054 rows=0 loops=608,698)

  • Output: 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=3,493,411
Planning time : 4.504 ms
Execution time : 33,998.968 ms