explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings
# exclusive inclusive rows x rows loops node
1. 784.123 96,206.458 ↑ 1.3 79,326 1

Hash Join (cost=33,354,758.06..33,477,775.42 rows=103,667 width=417) (actual time=95,119.840..96,206.458 rows=79,326 loops=1)

  • Hash Cond: (wl.osm_id = wl_1.osm_id)
  • Buffers: shared hit=10199319
  • Settings: jit = 'off', max_parallel_workers_per_gather = '3', random_page_cost = '1.9', work_mem = '50MB'
2. 324.087 324.087 ↓ 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.021..324.087 rows=1,763,285 loops=1)

  • Buffers: shared hit=100832
3. 20.324 95,098.248 ↑ 1.3 79,299 1

Hash (cost=33,353,462.22..33,353,462.22 rows=103,667 width=8) (actual time=95,098.248..95,098.248 rows=79,299 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 4122kB
  • Buffers: shared hit=10098487
4. 26.598 95,077.924 ↑ 1.3 79,299 1

Unique (cost=33,351,907.21..33,352,425.55 rows=103,667 width=8) (actual time=95,026.857..95,077.924 rows=79,299 loops=1)

  • Buffers: shared hit=10098487
5. 139.586 95,051.326 ↓ 1.3 131,009 1

Sort (cost=33,351,907.21..33,352,166.38 rows=103,667 width=8) (actual time=95,026.852..95,051.326 rows=131,009 loops=1)

  • Sort Key: wl_1.osm_id
  • Sort Method: quicksort Memory: 9214kB
  • Buffers: shared hit=10098487
6. 35.679 94,911.740 ↓ 1.3 131,009 1

Nested Loop (cost=0.29..33,343,270.93 rows=103,667 width=8) (actual time=2.046..94,911.740 rows=131,009 loops=1)

  • Buffers: shared hit=10098487
7. 2.485 2.485 ↑ 1.0 1 1

Seq Scan on boundary_polygon b (cost=0.00..1,477.10 rows=1 width=7,998) (actual time=0.412..2.485 rows=1 loops=1)

  • Filter: (name = 'Colorado'::text)
  • Rows Removed by Filter: 4007
  • Buffers: shared hit=1427
8. 7,292.536 94,873.576 ↑ 4.7 131,009 1

Nested Loop (cost=0.29..33,335,638.88 rows=615,495 width=1,489) (actual time=1.629..94,873.576 rows=131,009 loops=1)

  • Buffers: shared hit=10097060
9. 180.750 180.750 ↑ 1.0 615,495 1

Seq Scan on roads_mv r (cost=0.00..28,126.95 rows=615,495 width=233) (actual time=0.010..180.750 rows=615,495 loops=1)

  • Buffers: shared hit=21972
10. 87,400.290 87,400.290 ↓ 0.0 0 615,495

Index Scan using gix_osm_waterway_buffer_15m on waterway_buffer wl_1 (cost=0.29..54.10 rows=1 width=1,489) (actual time=0.117..0.142 rows=0 loops=615,495)

  • Index Cond: ((way_15m @ b.way) AND (way_15m && r.way))
  • Filter: (st_contains(b.way, way_15m) AND st_intersects(r.way, way_15m))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=6997613
Planning time : 3.665 ms
Execution time : 96,212.694 ms