explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 84wL : OSM US West - Trees by County Back to Original with Subquery

Settings
# exclusive inclusive rows x rows loops node
1. 0.643 625.675 ↑ 1.0 459 1

Sort (cost=50,332,387.92..50,332,389.06 rows=459 width=8,022) (actual time=625.641..625.675 rows=459 loops=1)

  • Output: b_1.osm_id, (count(*)), b.name, b.way
  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 986kB
  • Buffers: shared hit=31403
2. 0.200 625.032 ↑ 1.0 459 1

Nested Loop (cost=50,331,385.40..50,332,367.62 rows=459 width=8,022) (actual time=623.672..625.032 rows=459 loops=1)

  • Output: b_1.osm_id, (count(*)), b.name, b.way
  • Buffers: shared hit=31403
3. 0.000 623.914 ↑ 1.0 459 1

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

  • Output: b_1.osm_id, count(*)
  • Group Key: b_1.osm_id
  • Buffers: shared hit=30026
4. 14.803 628.637 ↓ 1.2 551 1

Gather Merge (cost=50,331,385.12..50,331,437.91 rows=459 width=16) (actual time=623.598..628.637 rows=551 loops=1)

  • Output: b_1.osm_id, (PARTIAL count(*))
  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=75526
5. 0.112 613.834 ↑ 1.7 276 2 / 2

Sort (cost=50,330,385.11..50,330,386.26 rows=459 width=16) (actual time=613.813..613.834 rows=276 loops=2)

  • Output: b_1.osm_id, (PARTIAL count(*))
  • Sort Key: b_1.osm_id
  • Sort Method: quicksort Memory: 34kB
  • Worker 0: Sort Method: quicksort Memory: 41kB
  • Buffers: shared hit=75526
  • Worker 0: actual time=605.448..605.474 rows=356 loops=1
  • Buffers: shared hit=45500
6. 62.982 613.722 ↑ 1.7 276 2 / 2

Partial HashAggregate (cost=50,330,360.23..50,330,364.82 rows=459 width=16) (actual time=613.671..613.722 rows=276 loops=2)

  • Output: b_1.osm_id, PARTIAL count(*)
  • Group Key: b_1.osm_id
  • Buffers: shared hit=75519
  • Worker 0: actual time=605.248..605.313 rows=356 loops=1
  • Buffers: shared hit=45493
7. 40.056 550.740 ↑ 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.172..550.740 rows=268,469 loops=2)

  • Output: b_1.osm_id
  • Buffers: shared hit=75519
  • Worker 0: actual time=0.253..554.569 rows=224445 loops=1
  • Buffers: shared hit=45493
8. 2.222 2.222 ↑ 1.2 2,906 2 / 2

Parallel Seq Scan on osm.boundary_polygon_subdivide b_1 (cost=0.00..1,689.18 rows=3,418 width=1,898) (actual time=0.020..2.222 rows=2,906 loops=2)

  • Output: b_1.osm_id, b_1.way
  • Buffers: shared hit=1655
  • Worker 0: actual time=0.020..3.368 rows=3823 loops=1
  • Buffers: shared hit=1095
9. 508.462 508.462 ↓ 1.7 91 5,811 / 2

Index Scan using gix_osm_natural_point on osm.natural_point t (cost=0.29..14,715.13 rows=53 width=32) (actual time=0.050..0.175 rows=91 loops=5,811)

  • Output: t.osm_id, t.name, t."natural", t.elevation, t.way, t.code
  • Index Cond: (t.way @ b_1.way)
  • Filter: ((t."natural" = 'tree'::text) AND st_contains(b_1.way, t.way))
  • Rows Removed by Filter: 28
  • Buffers: shared hit=73864
  • Worker 0: actual time=0.051..0.136 rows=58 loops=3823
  • Buffers: shared hit=44398
10. 0.918 0.918 ↑ 1.0 1 459

Index Scan using ix_osm_boundary_poly_osm_id on osm.boundary_polygon b (cost=0.28..1.99 rows=1 width=8,014) (actual time=0.002..0.002 rows=1 loops=459)

  • Output: b.osm_id, b.name, b.admin_level, b.boundary, b.way, b.code
  • Index Cond: (b.osm_id = b_1.osm_id)
  • Buffers: shared hit=1377
Planning time : 1.308 ms
Execution time : 630.730 ms