explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings
# exclusive inclusive rows x rows loops node
1. 0.252 8,177.900 ↓ 0.0 0 1

Hash Join (cost=33,354,758.06..33,477,775.42 rows=103,667 width=417) (actual time=8,177.899..8,177.900 rows=0 loops=1)

  • Hash Cond: (wl.osm_id = wl_1.osm_id)
  • Buffers: shared hit=3716370
  • Settings: jit = 'off', max_parallel_workers_per_gather = '3', random_page_cost = '1.9', work_mem = '50MB'
2. 0.017 0.017 ↑ 1,757,256.0 1 1

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

  • Buffers: shared hit=1
3. 0.001 8,177.631 ↓ 0.0 0 1

Hash (cost=33,353,462.22..33,353,462.22 rows=103,667 width=8) (actual time=8,177.631..8,177.631 rows=0 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1024kB
  • Buffers: shared hit=3716369
4. 0.001 8,177.630 ↓ 0.0 0 1

Unique (cost=33,351,907.21..33,352,425.55 rows=103,667 width=8) (actual time=8,177.630..8,177.630 rows=0 loops=1)

  • Buffers: shared hit=3716369
5. 0.007 8,177.629 ↓ 0.0 0 1

Sort (cost=33,351,907.21..33,352,166.38 rows=103,667 width=8) (actual time=8,177.629..8,177.629 rows=0 loops=1)

  • Sort Key: wl_1.osm_id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=3716369
6. 0.003 8,177.622 ↓ 0.0 0 1

Nested Loop (cost=0.29..33,343,270.93 rows=103,667 width=8) (actual time=8,177.622..8,177.622 rows=0 loops=1)

  • Buffers: shared hit=3716369
7. 2.051 2.051 ↑ 1.0 1 1

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

  • Filter: (name = 'Colorado'::text)
  • Rows Removed by Filter: 4007
  • Buffers: shared hit=1427
8. 6,808.363 8,175.568 ↓ 0.0 0 1

Nested Loop (cost=0.29..33,335,638.88 rows=615,495 width=1,489) (actual time=8,175.567..8,175.568 rows=0 loops=1)

  • Buffers: shared hit=3714942
9. 136.215 136.215 ↑ 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.007..136.215 rows=615,495 loops=1)

  • Buffers: shared hit=21972
10. 1,230.990 1,230.990 ↓ 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.002..0.002 rows=0 loops=615,495)

  • Index Cond: ((way_15m ~ b.way) AND (way_15m && r.way))
  • Filter: (st_contains(way_15m, b.way) AND st_intersects(r.way, way_15m))
  • Buffers: shared hit=615495
Planning time : 2.436 ms
Execution time : 8,177.971 ms