explain.depesz.com

PostgreSQL's explain analyze made readable

Result: SG3F : OSM US West - Trees Near Benchs by County

Settings
# exclusive inclusive rows x rows loops node
1. 1.451 8,916.802 ↑ 15,034.0 5,094 1

Sort (cost=1,919,822,946.33..1,920,014,404.72 rows=76,583,358 width=58) (actual time=8,916.460..8,916.802 rows=5,094 loops=1)

  • Sort Key: (count(t.osm_id)) DESC
  • Sort Method: quicksort Memory: 909kB
2. 6.403 8,915.351 ↑ 15,034.0 5,094 1

GroupAggregate (cost=1,875,994,057.33..1,903,747,507.03 rows=76,583,358 width=58) (actual time=8,908.257..8,915.351 rows=5,094 loops=1)

  • Group Key: b.name, p.osm_id, p.way
3. 33.169 8,908.948 ↑ 224,149.6 9,632 1

Sort (cost=1,875,994,057.33..1,881,391,580.56 rows=2,159,009,289 width=58) (actual time=8,908.239..8,908.948 rows=9,632 loops=1)

  • Sort Key: b.name, p.osm_id, p.way
  • Sort Method: quicksort Memory: 1739kB
4. 89.855 8,875.779 ↑ 224,149.6 9,632 1

Nested Loop (cost=0.68..1,313,977,921.50 rows=2,159,009,289 width=58) (actual time=48.206..8,875.779 rows=9,632 loops=1)

5. 42.824 6,484.192 ↑ 13.1 67,698 1

Nested Loop (cost=0.15..2,328,950.22 rows=887,438 width=50) (actual time=0.101..6,484.192 rows=67,698 loops=1)

6. 33.043 33.043 ↓ 1.0 23,303 1

Seq Scan on miscpoi_point p (cost=0.00..2,286.20 rows=23,186 width=40) (actual time=0.025..33.043 rows=23,303 loops=1)

  • Filter: (code = '2902'::text)
  • Rows Removed by Filter: 80873
7. 6,408.325 6,408.325 ↓ 3.0 3 23,303

Index Scan using gix_osm_boundary_polygon on boundary_polygon b (cost=0.15..100.34 rows=1 width=8,006) (actual time=0.090..0.275 rows=3 loops=23,303)

  • Index Cond: (way ~ p.way)
  • Filter: st_contains(way, p.way)
  • Rows Removed by Filter: 1
8. 2,301.732 2,301.732 ↓ 0.0 0 67,698

Index Scan using gix_osm_natural_point on natural_point t (cost=0.54..1,477.49 rows=53 width=40) (actual time=0.033..0.034 rows=0 loops=67,698)

  • Index Cond: (way && st_expand(p.way, '10'::double precision))
  • Filter: (("natural" = 'tree'::text) AND st_dwithin(p.way, way, '10'::double precision))
  • Rows Removed by Filter: 0
Planning time : 2.289 ms
Execution time : 8,917.170 ms