explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QVYJ : OSM US West - Trees by County ST_Subdivide

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 628.842 ↑ 1.0 459 1

Finalize GroupAggregate (cost=50,331,385.12..50,331,444.79 rows=459 width=16) (actual time=628.378..628.842 rows=459 loops=1)

  • Group Key: b.osm_id
  • Buffers: shared hit=31,849
2. 28.920 634.239 ↓ 1.1 526 1

Gather Merge (cost=50,331,385.12..50,331,437.91 rows=459 width=16) (actual time=628.368..634.239 rows=526 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=75,526
3. 0.112 605.319 ↑ 1.7 263 2 / 2

Sort (cost=50,330,385.11..50,330,386.26 rows=459 width=16) (actual time=605.297..605.319 rows=263 loops=2)

  • Sort Key: b.osm_id
  • Sort Method: quicksort Memory: 34kB
  • Worker 0: Sort Method: quicksort Memory: 40kB
  • Buffers: shared hit=75,526
4. 64.594 605.207 ↑ 1.7 263 2 / 2

Partial HashAggregate (cost=50,330,360.23..50,330,364.82 rows=459 width=16) (actual time=605.158..605.207 rows=263 loops=2)

  • Group Key: b.osm_id
  • Buffers: shared hit=75,519
5. 39.005 540.613 ↑ 22.8 268,469 2 / 2

Nested Loop Left Join (cost=0.29..50,299,806.98 rows=6,110,649 width=8) (actual time=0.239..540.613 rows=268,469 loops=2)

  • Buffers: shared hit=75,519
6. 1.862 1.862 ↑ 1.2 2,906 2 / 2

Parallel Seq Scan on boundary_polygon_subdivide b (cost=0.00..1,689.18 rows=3,418 width=1,898) (actual time=0.015..1.862 rows=2,906 loops=2)

  • Buffers: shared hit=1,655
7. 499.746 499.746 ↓ 1.7 91 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.046..0.172 rows=91 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=73,864
Planning time : 0.850 ms
Execution time : 634.642 ms