explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings
# exclusive inclusive rows x rows loops node
1. 577.145 12,376.360 ↓ 396.7 79,339 1

Hash Join (cost=252,005,192.56..252,128,209.93 rows=200 width=417) (actual time=11,593.143..12,376.360 rows=79,339 loops=1)

  • Hash Cond: (wl.osm_id = wl_1.osm_id)
  • Buffers: shared hit=3333544
2. 222.246 222.246 ↓ 1.0 1,763,285 1

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

  • Buffers: shared hit=100832
3. 13.424 11,576.969 ↓ 396.6 79,314 1

Hash (cost=252,005,190.06..252,005,190.06 rows=200 width=8) (actual time=11,576.969..11,576.969 rows=79,314 loops=1)

  • Buckets: 131072 (originally 1024) Batches: 1 (originally 1) Memory Usage: 4123kB
  • Buffers: shared hit=3232712
4. 126.019 11,563.545 ↓ 396.6 79,314 1

HashAggregate (cost=252,005,186.06..252,005,188.06 rows=200 width=8) (actual time=11,543.508..11,563.545 rows=79,314 loops=1)

  • Group Key: wl_1.osm_id
  • Buffers: shared hit=3232712
5. 0.000 11,437.526 ↑ 2,105.9 130,888 1

Gather (cost=1,000.28..251,316,108.64 rows=275,630,971 width=8) (actual time=0.829..11,437.526 rows=130,888 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
  • Buffers: shared hit=3232712
6. 177.918 11,475.444 ↑ 2,717.2 32,722 4 / 4

Nested Loop (cost=0.29..223,752,011.54 rows=88,913,216 width=8) (actual time=1.226..11,475.444 rows=32,722 loops=4)

  • Buffers: shared hit=3232712
7. 64.742 64.742 ↑ 1.3 153,874 4 / 4

Parallel Seq Scan on roads_mv r (cost=0.00..23,957.47 rows=198,547 width=234) (actual time=0.012..64.742 rows=153,874 loops=4)

  • Buffers: shared hit=21972
8. 11,232.784 11,232.784 ↓ 0.0 0 615,495 / 4

Index Scan using gix_osm_waterway_buffer_15m on waterway_buffer wl_1 (cost=0.29..1,126.38 rows=45 width=40) (actual time=0.063..0.073 rows=0 loops=615,495)

  • Index Cond: (way_15m && r.way)
  • Filter: st_intersects(r.way, way_15m)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=3210740
Planning time : 0.779 ms
Execution time : 12,380.865 ms