explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings
# exclusive inclusive rows x rows loops node
1. 16.414 598.386 ↑ 1.0 459 1

Sort (cost=50,332,387.92..50,332,389.06 rows=459 width=8,022) (actual time=598.350..598.386 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=40682
  • -> Nested Loop (cost=50331385.40..50332367.62 rows=459 width=8022) (actual time=596.288..597.714 rows=459 loops
  • Output: b_1.osm_id, (count(*)), b.name, b.way
  • Buffers: shared hit=40682
  • -> Finalize GroupAggregate (cost=50331385.12..50331444.79 rows=459 width=16) (actual time=596.267..596.53
  • Output: b_1.osm_id, count(*)
  • Group Key: b_1.osm_id
  • Buffers: shared hit=39305
  • -> Gather Merge (cost=50331385.12..50331437.91 rows=459 width=16) (actual time=596.259..600.976 row
  • Output: b_1.osm_id, (PARTIAL count(*))
  • Workers Planned: 1
  • Workers Launched: 1
  • Buffers: shared hit=75526
  • -> Sort (cost=50330385.11..50330386.26 rows=459 width=16) (actual time=581.600..581.619 rows=
  • Output: b_1.osm_id, (PARTIAL count(*))
  • Sort Key: b_1.osm_id
  • Sort Method: quicksort Memory: 39kB
  • Worker 0: Sort Method: quicksort Memory: 34kB
  • Buffers: shared hit=75526
  • Worker 0: actual time=569.162..569.177 rows=210 loops=1
  • Buffers: shared hit=36221
2. 59.817 581.513 ↑ 1.7 264 2 / 2

Partial HashAggregate (cost=50,330,360.23..50,330,364.82 rows=459 width=16) (actual time=581.465..581.513 rows=264 loops=2)

  • Output: b_1.osm_id, PARTIAL count(*)
  • Group Key: b_1.osm_id
  • Buffers: shared hit=75519
  • Worker 0: actual time=569.023..569.062 rows=210 loops=1
  • Buffers: shared hit=36214
3. 37.627 521.696 ↑ 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.267..521.696 rows=268,469 loops=2)

  • Output: b_1.osm_id
  • Buffers: shared hit=75519
  • Worker 0: actual time=0.490..508.684 rows=268802 loops=1
  • Buffers: shared hit=36214
4. 1.756 1.756 ↑ 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.017..1.756 rows=2,906 loops=2)

  • Output: b_1.osm_id, b_1.way
  • Buffers: shared hit=1655
  • Worker 0: actual time=0.027..2.062 rows=2959 loops=1
  • Buffers: shared hit=845
5. 482.313 482.313 ↓ 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.166 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.041..0.158 rows=90 loops=2959
  • Buffers: shared hit=35369
6. 0.459 0.459 ↑ 1.0 1 459 / 2

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 : 0.665 ms
Execution time : 603.095 ms