explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings
# exclusive inclusive rows x rows loops node
1. 0.612 629.792 ↑ 1.0 459 1

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

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 986kB
  • Buffers: shared hit=44147 read=8
  • I/O Timings: read=0.077
2. 1.281 629.180 ↑ 1.0 459 1

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

  • Buffers: shared hit=44147 read=8
  • I/O Timings: read=0.077
3. 0.000 627.899 ↑ 1.0 459 1

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

  • Group Key: b_1.osm_id
  • Buffers: shared hit=42778
4. 21.237 632.778 ↓ 1.1 519 1

Gather Merge (cost=50,331,385.12..50,331,437.91 rows=459 width=16) (actual time=627.618..632.778 rows=519 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=75526
5. 0.102 611.541 ↑ 1.8 260 2 / 2

Sort (cost=50,330,385.11..50,330,386.26 rows=459 width=16) (actual time=611.520..611.541 rows=260 loops=2)

  • Sort Key: b_1.osm_id
  • Sort Method: quicksort Memory: 39kB
  • Worker 0: Sort Method: quicksort Memory: 35kB
  • Buffers: shared hit=75526
6. 63.826 611.439 ↑ 1.8 260 2 / 2

Partial HashAggregate (cost=50,330,360.23..50,330,364.82 rows=459 width=16) (actual time=611.383..611.439 rows=260 loops=2)

  • Group Key: b_1.osm_id
  • Buffers: shared hit=75519
7. 40.139 547.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.165..547.613 rows=268,469 loops=2)

  • Buffers: shared hit=75519
8. 1.917 1.917 ↑ 1.2 2,906 2 / 2

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

  • Buffers: shared hit=1655
9. 505.557 505.557 ↓ 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.050..0.174 rows=91 loops=5,811)

  • Index Cond: (way @ b_1.way)
  • Filter: (("natural" = 'tree'::text) AND st_contains(b_1.way, way))