explain.depesz.com

PostgreSQL's explain analyze made readable

Result: o36j : OSM Performance - Waterways near Roads MV and PreBuffer

Settings
# exclusive inclusive rows x rows loops node
1. 266.291 83,169.373 ↑ 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.925..83,169.373 rows=760,336 loops=1)

  • Output: wl.osm_id
  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=25973526 read=29195
  • I/O Timings: read=292.714
2. 282.206 82,903.082 ↑ 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=4.109..82,903.082 rows=190,084 loops=4)

  • Output: wl.osm_id
  • Buffers: shared hit=25973526 read=29195
  • I/O Timings: read=292.714
  • Worker 0: actual time=3.374..82928.770 rows=183016 loops=1
  • Buffers: shared hit=6205000 read=7407
  • I/O Timings: read=76.946
  • Worker 1: actual time=8.090..82940.264 rows=199529 loops=1
  • Buffers: shared hit=6730859 read=7501
  • I/O Timings: read=71.811
  • Worker 2: actual time=4.349..82929.569 rows=184156 loops=1
  • Buffers: shared hit=6342815 read=7084
  • I/O Timings: read=74.816
3. 448.758 448.758 ↑ 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.018..448.758 rows=1,467,359 loops=4)

  • Output: r.osm_id, r.way
  • Buffers: shared hit=213534
  • Worker 0: actual time=0.026..490.270 rows=1399943 loops=1
  • Buffers: shared hit=50943
  • Worker 1: actual time=0.022..453.104 rows=1520164 loops=1
  • Buffers: shared hit=55666
  • Worker 2: actual time=0.019..488.001 rows=1434418 loops=1
  • Buffers: shared hit=51711
4. 82,172.118 82,172.118 ↓ 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.051..0.056 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=25759992 read=29195
  • I/O Timings: read=292.714
  • Worker 0: actual time=0.053..0.058 rows=0 loops=1399943
  • Buffers: shared hit=6154057 read=7407
  • I/O Timings: read=76.946
  • Worker 1: actual time=0.049..0.054 rows=0 loops=1520164
  • Buffers: shared hit=6675193 read=7501
  • I/O Timings: read=71.811
  • Worker 2: actual time=0.052..0.057 rows=0 loops=1434418
  • Buffers: shared hit=6291104 read=7084
  • I/O Timings: read=74.816
Planning time : 2.092 ms
Execution time : 83,225.310 ms