explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Q5Tf : OSM US West - Waterways near Roads MV and PreBuffer CTE Work Mem 50MB

Settings
# exclusive inclusive rows x rows loops node
1. 883.052 126,325.082 ↓ 1.0 423,748 1

Hash Join (cost=10,365,437,735.03..10,365,560,752.40 rows=413,121 width=417) (actual time=125,184.972..126,325.082 rows=423,748 loops=1)

  • Hash Cond: (wl.osm_id = wl_1.osm_id)
  • Buffers: shared hit=18023663 read=470011
  • I/O Timings: read=3646.202
2. 257.791 257.791 ↓ 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.024..257.791 rows=1,763,285 loops=1)

  • Buffers: shared hit=100832
3. 125.407 125,184.239 ↓ 1.0 423,501 1

Hash (cost=10,365,432,571.02..10,365,432,571.02 rows=413,121 width=8) (actual time=125,184.239..125,184.239 rows=423,501 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 20640kB
  • Buffers: shared hit=17922831 read=470011
  • I/O Timings: read=3646.202
4. 928.808 125,058.832 ↓ 1.0 423,501 1

HashAggregate (cost=10,365,424,308.60..10,365,428,439.81 rows=413,121 width=8) (actual time=124,930.071..125,058.832 rows=423,501 loops=1)

  • Group Key: wl_1.osm_id
  • Buffers: shared hit=17922831 read=470011
  • I/O Timings: read=3646.202
5. 0.000 124,130.024 ↑ 3,266.4 760,924 1

Gather (cost=1,000.41..10,359,210,624.82 rows=2,485,473,511 width=8) (actual time=0.821..124,130.024 rows=760,924 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=17922831 read=470011
  • I/O Timings: read=3646.202
6. 735.046 124,465.694 ↑ 4,214.7 190,231 4 / 4

Nested Loop (cost=0.41..10,110,662,273.72 rows=801,765,649 width=8) (actual time=0.698..124,465.694 rows=190,231 loops=4)

  • Buffers: shared hit=17922831 read=470011
  • I/O Timings: read=3646.202
7. 1,539.989 1,539.989 ↑ 1.3 519,960 4 / 4

Parallel Seq Scan on waterway_buffer wl_1 (cost=0.00..479,207.97 rows=673,297 width=1,569) (actual time=0.073..1,539.989 rows=519,960 loops=4)

  • Buffers: shared hit=2464 read=470011
  • I/O Timings: read=3646.202
8. 122,190.659 122,190.659 ↓ 0.0 0 2,079,841 / 4

Index Scan using gix_osm_roads_mv_subdivide on roads_mv r (cost=0.41..15,010.06 rows=587 width=241) (actual time=0.217..0.235 rows=0 loops=2,079,841)

  • Index Cond: (way && wl_1.way_15m)
  • Filter: st_intersects(way, wl_1.way_15m)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=17920367
Planning time : 3.143 ms
Execution time : 126,363.739 ms