explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YzI0 : OSM Colorado - Waterways near Roads MV and PreBuffer CTE

Settings
# exclusive inclusive rows x rows loops node
1. 325.470 9,715.537 ↑ 3.2 79,404 1

Hash Join (cost=233,704,778.50..233,772,532.53 rows=256,673 width=383) (actual time=9,305.078..9,715.537 rows=79,404 loops=1)

  • Output: wl_1.osm_id, wl.way
  • Inner Unique: true
  • Hash Cond: (wl.osm_id = wl_1.osm_id)
  • Buffers: shared hit=3536333, temp read=14653 written=14655
  • Settings: jit = 'off', max_parallel_workers_per_gather = '3', random_page_cost = '1.5'
2. 85.141 85.141 ↑ 1.0 402,457 1

Seq Scan on osm.waterway_line wl (cost=0.00..25,604.57 rows=402,457 width=383) (actual time=0.010..85.141 rows=402,457 loops=1)

  • Output: wl.way, wl.osm_id
  • Buffers: shared hit=21580
3. 9.828 9,304.926 ↑ 3.2 79,376 1

Hash (cost=233,700,567.09..233,700,567.09 rows=256,673 width=8) (actual time=9,304.926..9,304.926 rows=79,376 loops=1)

  • Output: wl_1.osm_id
  • Buckets: 131072 Batches: 4 Memory Usage: 1797kB
  • Buffers: shared hit=3514753, temp read=291 written=496
4. 13.480 9,295.098 ↑ 3.2 79,376 1

Unique (cost=233,458,015.09..233,698,000.36 rows=256,673 width=8) (actual time=9,268.611..9,295.098 rows=79,376 loops=1)

  • Output: wl_1.osm_id
  • Buffers: shared hit=3514753, temp read=291 written=293
5. 54.302 9,281.618 ↑ 366.1 131,101 1

Sort (cost=233,458,015.09..233,578,007.72 rows=47,997,054 width=8) (actual time=9,268.610..9,281.618 rows=131,101 loops=1)

  • Output: wl_1.osm_id
  • Sort Key: wl_1.osm_id
  • Sort Method: external merge Disk: 2328kB
  • Buffers: shared hit=3514753, temp read=291 written=293
6. 16.169 9,227.316 ↑ 366.1 131,101 1

Gather (cost=1,000.28..226,068,893.99 rows=47,997,054 width=8) (actual time=1.140..9,227.316 rows=131,101 loops=1)

  • Output: wl_1.osm_id
  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=3514753
7. 46.394 9,211.147 ↑ 472.4 32,775 4 / 4

Nested Loop (cost=0.29..221,268,188.59 rows=15,482,921 width=8) (actual time=0.687..9,211.147 rows=32,775 loops=4)

  • Output: wl_1.osm_id
  • Buffers: shared hit=3514753
  • Worker 0: actual time=0.952..9211.266 rows=32328 loops=1
  • Buffers: shared hit=870060
  • Worker 1: actual time=0.446..9221.661 rows=32954 loops=1
  • Buffers: shared hit=881298
  • Worker 2: actual time=0.450..9211.604 rows=32841 loops=1
  • Buffers: shared hit=880588
8. 34.283 34.283 ↑ 1.3 152,174 4 / 4

Parallel Seq Scan on osm.roads_mv r (cost=0.00..23,233.54 rows=196,354 width=236) (actual time=0.011..34.283 rows=152,174 loops=4)

  • Output: r.osm_id, r.way
  • Buffers: shared hit=21270
  • Worker 0: actual time=0.011..36.488 rows=150594 loops=1
  • Buffers: shared hit=5258
  • Worker 1: actual time=0.009..35.777 rows=152781 loops=1
  • Buffers: shared hit=5337
  • Worker 2: actual time=0.019..36.099 rows=152640 loops=1
  • Buffers: shared hit=5292
9. 9,130.470 9,130.470 ↓ 0.0 0 608,698 / 4

Index Scan using gix_osm_waterway_buffer_15m on osm.waterway_buffer wl_1 (cost=0.29..1,126.32 rows=45 width=1,496) (actual time=0.052..0.060 rows=0 loops=608,698)

  • Output: wl_1.osm_id, wl_1.way_15m
  • Index Cond: (wl_1.way_15m && r.way)
  • Filter: st_intersects(r.way, wl_1.way_15m)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=3493483
  • Worker 0: actual time=0.053..0.060 rows=0 loops=150594
  • Buffers: shared hit=864802
  • Worker 1: actual time=0.052..0.060 rows=0 loops=152781
  • Buffers: shared hit=875961
  • Worker 2: actual time=0.052..0.060 rows=0 loops=152640
  • Buffers: shared hit=875296
Planning time : 4.578 ms
Execution time : 9,720.440 ms