explain.depesz.com

PostgreSQL's explain analyze made readable

Result: T9HM

Settings
# exclusive inclusive rows x rows loops node
1. 1.054 595.884 ↑ 1.0 459 1

Sort (cost=50,331,803.08..50,331,804.22 rows=459 width=8,022) (actual time=595.847..595.884 rows=459 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 986kB
  • Buffers: shared hit=35156 read=8
  • I/O Timings: read=1.897
2. 0.134 594.830 ↑ 1.0 459 1

Nested Loop (cost=50,330,800.56..50,331,782.78 rows=459 width=8,022) (actual time=591.155..594.830 rows=459 loops=1)

  • Buffers: shared hit=35156 read=8
  • I/O Timings: read=1.897
3. 0.000 590.565 ↑ 1.0 459 1

Finalize GroupAggregate (cost=50,330,800.28..50,330,859.95 rows=459 width=16) (actual time=590.250..590.565 rows=459 loops=1)

  • Group Key: b_1.osm_id
  • Buffers: shared hit=33787
4. 13.150 591.386 ↓ 1.2 554 1

Gather Merge (cost=50,330,800.28..50,330,853.07 rows=459 width=16) (actual time=590.245..591.386 rows=554 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=75549
5. 0.121 578.236 ↑ 1.7 277 2 / 2

Sort (cost=50,329,800.27..50,329,801.42 rows=459 width=16) (actual time=578.212..578.236 rows=277 loops=2)

  • Sort Key: b_1.osm_id
  • Sort Method: quicksort Memory: 36kB
  • Worker 0: Sort Method: quicksort Memory: 39kB
  • Buffers: shared hit=75549
6. 57.559 578.115 ↑ 1.7 277 2 / 2

Partial HashAggregate (cost=50,329,775.39..50,329,779.98 rows=459 width=16) (actual time=578.064..578.115 rows=277 loops=2)

  • Group Key: b_1.osm_id
  • Buffers: shared hit=75542
7. 37.061 520.556 ↑ 22.3 268,469 2 / 2

Nested Loop Left Join (cost=0.29..50,299,805.98 rows=5,993,881 width=8) (actual time=0.279..520.556 rows=268,469 loops=2)

  • Buffers: shared hit=75542
8. 4.088 4.088 ↑ 1.2 2,906 2 / 2

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

  • Buffers: shared hit=1654
9. 479.408 479.408 ↓ 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.165 rows=91 loops=5,811)

  • Index Cond: (way @ b_1.way)
  • Filter: (("natural" = 'tree'::text) AND st_contains(b_1.way, way))
  • Rows Removed by Filter: 28
  • Buffers: shared hit=73888
10. 4.131 4.131 ↑ 1.0 1 459

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

  • Index Cond: (osm_id = b_1.osm_id)
  • Buffers: shared hit=1369 read=8
  • I/O Timings: read=1.897
Planning time : 11.970 ms
Execution time : 597.276 ms