explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RZ8M : OSM US West - Trees by County ST_Subdivide - Simple Join to Original

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 36,093.069 ↑ 4,008.0 459 1

Finalize GroupAggregate (cost=108,258,112.49..108,594,713.40 rows=1,839,672 width=8,024) (actual time=20,572.626..36,093.069 rows=459 loops=1)

  • Group Key: b.osm_id, b_orig.name, b_orig.way
  • Buffers: shared hit=10830492, temp read=190575 written=190783
  • Settings: jit = 'off', max_parallel_workers_per_gather = '3', random_page_cost = '1.9'
2. 5,487.011 36,111.069 ↑ 4,008.0 459 1

Gather Merge (cost=108,258,112.49..108,557,919.96 rows=1,839,672 width=8,024) (actual time=20,572.542..36,111.069 rows=459 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=21217448, temp read=228290 written=228629
3. 7,934.479 30,624.058 ↑ 7,998.6 230 2 / 2

Partial GroupAggregate (cost=108,257,112.48..108,349,956.85 rows=1,839,672 width=8,024) (actual time=19,018.979..30,624.058 rows=230 loops=2)

  • Group Key: b.osm_id, b_orig.name, b_orig.way
  • Buffers: shared hit=21217448, temp read=228290 written=228629
4. 22,142.158 22,689.579 ↑ 22.2 268,580 2 / 2

Sort (cost=108,257,112.48..108,272,002.01 rows=5,955,812 width=8,016) (actual time=19,018.932..22,689.579 rows=268,580 loops=2)

  • Sort Key: b.osm_id, b_orig.name, b_orig.way
  • Sort Method: external merge Disk: 545664kB
  • Worker 0: Sort Method: external merge Disk: 126808kB
  • Buffers: shared hit=15407260, temp read=228290 written=228629
5. 42.369 547.421 ↑ 22.2 268,580 2 / 2

Nested Loop Left Join (cost=0.57..50,302,929.72 rows=5,955,812 width=8,016) (actual time=0.168..547.421 rows=268,580 loops=2)

  • Buffers: shared hit=80725
6. 1.079 8.212 ↑ 1.2 2,906 2 / 2

Nested Loop (cost=0.28..4,819.74 rows=3,418 width=9,905) (actual time=0.043..8.212 rows=2,906 loops=2)

  • Buffers: shared hit=11472
7. 1.121 1.121 ↑ 1.2 2,004 2 / 2

Parallel Seq Scan on boundary_polygon b_orig (cost=0.00..1,450.58 rows=2,358 width=8,016) (actual time=0.009..1.121 rows=2,004 loops=2)

  • Buffers: shared hit=1427
8. 6.012 6.012 ↑ 13.0 1 4,008 / 2

Index Scan using ix_osm_boundary_poly_subdivide_osm_id on boundary_polygon_subdivide b (cost=0.28..1.30 rows=13 width=1,897) (actual time=0.001..0.003 rows=1 loops=4,008)

  • Index Cond: (osm_id = b_orig.osm_id)
  • Buffers: shared hit=10045
9. 496.841 496.841 ↓ 1.7 92 5,811 / 2

Index Scan using gix_osm_natural_point on natural_point t (cost=0.29..14,715.13 rows=53 width=32) (actual time=0.043..0.171 rows=92 loops=5,811)

  • Index Cond: (way @ b.way)
  • Filter: (("natural" = 'tree'::text) AND st_contains(b.way, way))
  • Rows Removed by Filter: 28
  • Buffers: shared hit=69253
Planning time : 1.132 ms
Execution time : 36,252.573 ms