explain.depesz.com

PostgreSQL's explain analyze made readable

Result: koT : OSM Performance - Waterways near Roads MV and PreBuffer Waterway

Settings
# exclusive inclusive rows x rows loops node
1. 249.448 81,303.863 ↑ 3,088.1 760,336 1

Gather (cost=1,000.41..10,328,088,810.27 rows=2,348,007,153 width=8) (actual time=0.548..81,303.863 rows=760,336 loops=1)

  • Output: wl.osm_id
  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=26002697
  • Settings: jit = 'off', max_parallel_workers_per_gather = '3', random_page_cost = '1.9'
2. 1,396.459 81,054.415 ↑ 3,984.7 190,084 4 / 4

Nested Loop (cost=0.41..10,093,287,094.97 rows=757,421,662 width=8) (actual time=1.739..81,054.415 rows=190,084 loops=4)

  • Output: wl.osm_id
  • Buffers: shared hit=26002697
  • Worker 0: actual time=0.696..81088.809 rows=194659 loops=1
  • Buffers: shared hit=6669048
  • Worker 1: actual time=2.781..81087.144 rows=194779 loops=1
  • Buffers: shared hit=6624061
  • Worker 2: actual time=3.207..81082.149 rows=180730 loops=1
  • Buffers: shared hit=6205860
3. 420.556 420.556 ↑ 1.3 1,467,359 4 / 4

Parallel Seq Scan on osm.roads_mv r (cost=0.00..232,467.67 rows=1,893,367 width=243) (actual time=0.023..420.556 rows=1,467,359 loops=4)

  • Output: r.osm_id, r.way
  • Buffers: shared hit=213534
  • Worker 0: actual time=0.025..439.544 rows=1502220 loops=1
  • Buffers: shared hit=54718
  • Worker 1: actual time=0.033..443.933 rows=1495596 loops=1
  • Buffers: shared hit=54630
  • Worker 2: actual time=0.027..479.220 rows=1402450 loops=1
  • Buffers: shared hit=50876
4. 79,237.399 79,237.399 ↓ 0.0 0 5,869,437 / 4

Index Scan using gix_osm_waterway_buffer_15m on osm.waterway_buffer wl (cost=0.41..5,328.65 rows=209 width=1,565) (actual time=0.050..0.054 rows=0 loops=5,869,437)

  • Output: wl.osm_id, wl.way_15m
  • Index Cond: (wl.way_15m && r.way)
  • Filter: st_intersects(r.way, wl.way_15m)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=25789163
  • Worker 0: actual time=0.048..0.053 rows=0 loops=1502220
  • Buffers: shared hit=6614330
  • Worker 1: actual time=0.049..0.053 rows=0 loops=1495596
  • Buffers: shared hit=6569431
  • Worker 2: actual time=0.052..0.057 rows=0 loops=1402450
  • Buffers: shared hit=6154984
Planning time : 1.401 ms
Execution time : 81,361.629 ms