explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ziK6W : OSM US West - Waterways near Roads MV and PreBuffer CTE - Jefferson County, Colorado

Settings
# exclusive inclusive rows x rows loops node
1. 115.484 821.365 ↓ 6.7 1,335 1

Gather (cost=332,998.19..441,006.75 rows=200 width=417) (actual time=589.620..821.365 rows=1,335 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=181037
2. 96.728 705.881 ↓ 5.1 334 4 / 4

Hash Join (cost=331,998.19..439,986.75 rows=65 width=417) (actual time=506.176..705.881 rows=334 loops=4)

  • Hash Cond: (wl.osm_id = wl_1.osm_id)
  • Buffers: shared hit=181037
3. 108.163 108.163 ↑ 1.3 440,821 4 / 4

Parallel Seq Scan on waterway_line wl (cost=0.00..106,500.57 rows=566,857 width=417) (actual time=0.020..108.163 rows=440,821 loops=4)

  • Buffers: shared hit=100832
4. 0.349 500.990 ↓ 6.7 1,335 4 / 4

Hash (cost=331,995.69..331,995.69 rows=200 width=8) (actual time=500.990..500.990 rows=1,335 loops=4)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 69kB
  • Buffers: shared hit=80196
5. 3.644 500.641 ↓ 6.7 1,335 4 / 4

HashAggregate (cost=331,991.69..331,993.69 rows=200 width=8) (actual time=500.342..500.641 rows=1,335 loops=4)

  • Group Key: wl_1.osm_id
  • Buffers: shared hit=80196
6. 3.346 496.997 ↑ 36.4 3,712 4 / 4

Nested Loop (cost=0.28..331,653.87 rows=135,127 width=8) (actual time=0.917..496.997 rows=3,712 loops=4)

  • Buffers: shared hit=80196
7. 2.327 2.327 ↑ 1.0 2,174 4 / 4

Seq Scan on waterway_buffer wl_1 (cost=0.00..400.74 rows=2,174 width=40) (actual time=0.019..2.327 rows=2,174 loops=4)

  • Buffers: shared hit=1516
8. 491.324 491.324 ↑ 3.0 2 8,696 / 4

Index Scan using gix_osm_roads_mv_subdivide on roads_mv r (cost=0.28..152.31 rows=6 width=172) (actual time=0.178..0.226 rows=2 loops=8,696)

  • Index Cond: (way && wl_1.way_15m)
  • Filter: st_intersects(way, wl_1.way_15m)
  • Rows Removed by Filter: 5
  • Buffers: shared hit=78680
Planning time : 0.762 ms
Execution time : 821.615 ms