explain.depesz.com

PostgreSQL's explain analyze made readable

Result: hTUe : OSM US West - Trees by County

Settings
# exclusive inclusive rows x rows loops node
1. 0.621 22,379.279 ↑ 1.0 459 1

Sort (cost=4,002,772.41..4,002,773.56 rows=459 width=8,016) (actual time=22,379.213..22,379.279 rows=459 loops=1)

  • Sort Key: (count(*)) DESC
  • Sort Method: quicksort Memory: 979kB
  • Buffers: shared hit=6440172
2. 9.796 22,378.658 ↑ 1.0 459 1

Finalize GroupAggregate (cost=4,002,690.15..4,002,752.12 rows=459 width=8,016) (actual time=22,365.479..22,378.658 rows=459 loops=1)

  • Group Key: b.admin_level, b.name, b.way
  • Buffers: shared hit=6440172
3. 21.413 22,368.862 ↑ 1.0 459 1

Gather Merge (cost=4,002,690.15..4,002,742.94 rows=459 width=8,016) (actual time=22,365.414..22,368.862 rows=459 loops=1)

  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=13075028
4. 1.281 22,347.449 ↑ 2.0 230 2 / 2

Sort (cost=4,001,690.14..4,001,691.29 rows=459 width=8,016) (actual time=22,347.393..22,347.449 rows=230 loops=2)

  • Sort Key: b.name, b.way
  • Sort Method: quicksort Memory: 719kB
  • Worker 0: Sort Method: quicksort Memory: 285kB
  • Buffers: shared hit=13074753
5. 15,220.572 22,346.168 ↑ 2.0 230 2 / 2

Partial HashAggregate (cost=4,001,665.26..4,001,669.85 rows=459 width=8,016) (actual time=22,346.085..22,346.168 rows=230 loops=2)

  • Group Key: b.admin_level, b.name, b.way
  • Buffers: shared hit=13074218
6. 72.922 7,125.596 ↑ 7.5 265,950 2 / 2

Nested Loop Left Join (cost=0.29..3,981,757.44 rows=1,990,782 width=8,008) (actual time=0.385..7,125.596 rows=265,950 loops=2)

  • Buffers: shared hit=4679979
7. 1.975 1.975 ↑ 1.2 230 2 / 2

Parallel Seq Scan on boundary_polygon b (cost=0.00..1,451.49 rows=270 width=8,008) (actual time=0.025..1.975 rows=230 loops=2)

  • Filter: (admin_level = '6'::text)
  • Rows Removed by Filter: 1776
  • Buffers: shared hit=1422
8. 7,050.699 7,050.699 ↓ 21.8 1,158 459 / 2

Index Scan using gix_osm_natural_point on natural_point t (cost=0.29..14,741.34 rows=53 width=32) (actual time=1.503..30.722 rows=1,158 loops=459)

  • Index Cond: (way @ b.way)
  • Filter: (("natural" = 'tree'::text) AND st_contains(b.way, way))
  • Rows Removed by Filter: 988
  • Buffers: shared hit=4676688
Planning time : 0.685 ms
Execution time : 22,381.526 ms