explain.depesz.com

PostgreSQL's explain analyze made readable

Result: A75N : OSM Performance - Waterways near Roads Original

Settings
# exclusive inclusive rows x rows loops node
1. 45,325.485 509,192.857 ↑ 2,918.0 787,989 1

Gather (cost=1,000.66..9,350,896,526.85 rows=2,299,338,980 width=419) (actual time=1.544..509,192.857 rows=787,989 loops=1)

  • Output: wl.osm_id, wl.waterway, wl.way
  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=21174391
2. 516.912 463,867.372 ↑ 3,765.1 196,997 4 / 4

Nested Loop (cost=0.67..9,120,961,628.85 rows=741,722,252 width=419) (actual time=11.977..463,867.372 rows=196,997 loops=4)

  • Output: wl.osm_id, wl.waterway, wl.way
  • Buffers: shared hit=21174391
  • Worker 0: actual time=3.964..446528.576 rows=204869 loops=1
  • Buffers: shared hit=5244944
  • Worker 1: actual time=42.471..452720.283 rows=168795 loops=1
  • Buffers: shared hit=5100628
  • Worker 2: actual time=0.325..447926.563 rows=186403 loops=1
  • Buffers: shared hit=5014460
3. 160.572 160.572 ↑ 1.3 440,714 4 / 4

Parallel Seq Scan on osm.waterway_line wl (cost=0.00..106,509.83 rows=570,283 width=419) (actual time=0.023..160.572 rows=440,714 loops=4)

  • Output: wl.osm_id, wl.name, wl.waterway, wl."natural", wl.landuse, wl.way, wl.code
  • Buffers: shared hit=100807
  • Worker 0: actual time=0.032..168.054 rows=468925 loops=1
  • Buffers: shared hit=26529
  • Worker 1: actual time=0.019..147.463 rows=378032 loops=1
  • Buffers: shared hit=22201
  • Worker 2: actual time=0.016..161.934 rows=426650 loops=1
  • Buffers: shared hit=24402
4. 463,189.888 463,189.888 ↓ 0.0 0 1,762,854 / 4

Index Scan using gix_osm_road_line on osm.road_line r (cost=0.67..15,987.17 rows=639 width=237) (actual time=0.418..1.051 rows=0 loops=1,762,854)

  • Output: r.osm_id, r.name, r.traffic, r.highway, r.ref, r.maxspeed, r.oneway, r.tracktype, r.way, r.code
  • Index Cond: (r.way && st_expand(wl.way, '15'::double precision))
  • Filter: st_dwithin(r.way, wl.way, '15'::double precision)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=21057897
  • Worker 0: actual time=0.405..0.950 rows=0 loops=468925
  • Buffers: shared hit=5214614
  • Worker 1: actual time=0.459..1.196 rows=0 loops=378032
  • Buffers: shared hit=5074910
  • Worker 2: actual time=0.422..1.048 rows=0 loops=426650
  • Buffers: shared hit=4986038
Planning time : 2.637 ms
Execution time : 509,243.942 ms