explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sRsa : OSM US West - Trees by County Back to Original with CTE

Settings
# exclusive inclusive rows x rows loops node
1. 0.598 580.902 ↑ 1.0 459 1

Sort (cost=50,332,387.92..50,332,389.06 rows=459 width=8,022) (actual time=580.867..580.902 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=33624
2. 0.246 580.304 ↑ 1.0 459 1

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

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

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

  • Output: b_1.osm_id, count(*)
  • Group Key: b_1.osm_id
  • Buffers: shared hit=32247
4. 13.709 583.268 ↓ 1.2 544 1

Gather Merge (cost=50,331,385.12..50,331,437.91 rows=459 width=16) (actual time=578.808..583.268 rows=544 loops=1)

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

Sort (cost=50,330,385.11..50,330,386.26 rows=459 width=16) (actual time=569.539..569.559 rows=272 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: 40kB
  • Buffers: shared hit=75526
  • Worker 0: actual time=562.194..562.217 rows=334 loops=1
  • Buffers: shared hit=43279
6. 59.294 569.451 ↑ 1.7 272 2 / 2

Partial HashAggregate (cost=50,330,360.23..50,330,364.82 rows=459 width=16) (actual time=569.400..569.451 rows=272 loops=2)

  • Output: b_1.osm_id, PARTIAL count(*)
  • Group Key: b_1.osm_id
  • Buffers: shared hit=75519
  • Worker 0: actual time=562.009..562.071 rows=334 loops=1
  • Buffers: shared hit=43272
7. 37.557 510.157 ↑ 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.125..510.157 rows=268,469 loops=2)

  • Output: b_1.osm_id
  • Buffers: shared hit=75519
  • Worker 0: actual time=0.189..511.189 rows=227382 loops=1
  • Buffers: shared hit=43272
8. 1.909 1.909 ↑ 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.011..1.909 rows=2,906 loops=2)

  • Output: b_1.osm_id, b_1.way
  • Buffers: shared hit=1655
  • Worker 0: actual time=0.013..2.801 rows=3616 loops=1
  • Buffers: shared hit=1046
9. 470.691 470.691 ↓ 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.046..0.162 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.047..0.132 rows=62 loops=3616
  • Buffers: shared hit=42226
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.445 ms
Execution time : 585.395 ms