explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fmS2 : OSM Jefferson County Benches near Trees - Pg13 Beta 3, PostGIS 3

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 159.266 ↑ 1.0 10 1

Limit (cost=280,949.92..280,949.95 rows=10 width=48) (actual time=145.283..159.266 rows=10 loops=1)

  • Output: p.osm_id, p.way, (count(t.osm_id))
  • Buffers: shared hit=51,409
  • Buffers: shared hit=1
2. 0.211 159.261 ↑ 333.4 10 1

Sort (cost=280,949.92..280,958.26 rows=3,334 width=48) (actual time=145.281..159.261 rows=10 loops=1)

  • Output: p.osm_id, p.way, (count(t.osm_id))
  • Sort Key: (count(t.osm_id)) DESC, p.way
  • Sort Method: top-N heapsort Memory: 26kB
  • Buffers: shared hit=51,409
3. 0.175 159.050 ↑ 21.0 159 1

Finalize GroupAggregate (cost=280,016.54..280,877.88 rows=3,334 width=48) (actual time=144.872..159.050 rows=159 loops=1)

  • Output: p.osm_id, p.way, count(t.osm_id)
  • Group Key: p.osm_id, p.way
  • Buffers: shared hit=51,409
4. 27.284 158.875 ↑ 41.9 159 1

Gather Merge (cost=280,016.54..280,794.53 rows=6,668 width=48) (actual time=144.857..158.875 rows=159 loops=1)

  • Output: p.osm_id, p.way, (PARTIAL count(t.osm_id))
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=51,409
5. 0.085 131.591 ↑ 62.9 53 3 / 3

Sort (cost=279,016.51..279,024.85 rows=3,334 width=48) (actual time=131.581..131.591 rows=53 loops=3)

  • Output: p.osm_id, p.way, (PARTIAL count(t.osm_id))
  • Sort Key: p.osm_id, p.way
  • Sort Method: quicksort Memory: 37kB
  • Buffers: shared hit=51,409
  • Worker 0: actual time=125.720..125.724 rows=0 loops=1
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=16,244
  • Worker 1: actual time=124.980..124.983 rows=0 loops=1
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=15,272
6. 0.200 131.506 ↑ 62.9 53 3 / 3

Partial HashAggregate (cost=278,788.09..278,821.43 rows=3,334 width=48) (actual time=131.480..131.506 rows=53 loops=3)

  • Output: p.osm_id, p.way, PARTIAL count(t.osm_id)
  • Group Key: p.osm_id, p.way
  • Batches: 1 Memory Usage: 145kB
  • Buffers: shared hit=51,377
  • Worker 0: actual time=125.670..125.673 rows=0 loops=1
  • Batches: 1 Memory Usage: 121kB
  • Buffers: shared hit=16,228
  • Worker 1: actual time=124.893..124.895 rows=0 loops=1
  • Batches: 1 Memory Usage: 121kB
  • Buffers: shared hit=15,256
7. 0.253 131.306 ↑ 8,292.4 107 3 / 3

Nested Loop (cost=0.82..272,133.45 rows=887,285 width=48) (actual time=99.527..131.306 rows=107 loops=3)

  • Output: p.osm_id, p.way, t.osm_id
  • Buffers: shared hit=51,377
  • Worker 0: actual time=125.633..125.636 rows=0 loops=1
  • Buffers: shared hit=16,228
  • Worker 1: actual time=124.860..124.863 rows=0 loops=1
  • Buffers: shared hit=15,256
8. 0.090 120.711 ↓ 1.5 142 3 / 3

Nested Loop (cost=0.29..86,930.84 rows=97 width=40) (actual time=99.325..120.711 rows=142 loops=3)

  • Output: p.osm_id, p.way
  • Buffers: shared hit=47,197
  • Worker 0: actual time=125.632..125.634 rows=0 loops=1
  • Buffers: shared hit=16,228
  • Worker 1: actual time=124.859..124.861 rows=0 loops=1
  • Buffers: shared hit=15,256
9. 103.538 103.538 ↓ 0.0 0 3 / 3

Parallel Seq Scan on public.planet_osm_polygon b (cost=0.00..46,221.78 rows=2 width=230) (actual time=99.069..103.538 rows=0 loops=3)

  • Output: b.osm_id, b.access, b."addr:housename", b."addr:housenumber", b."addr:interpolation", b.admin_level, b.aerialway, b.aeroway, b.amenity, b.area, b.barrier, b.bicycle, b.brand, b.bridge, b.boundary, b.building, b.construction, b.covered, b.culvert, b.cutting, b.denomination, b.disused, b.embankment, b.foot, b."generator:source", b.harbour, b.highway, b.historic, b.horse, b.intermittent, b.junction, b.landuse, b.layer, b.leisure, b.lock, b.man_made, b.military, b.motorcar, b.name, b."natural", b.office, b.oneway, b.operator, b.place, b.population, b.power, b.power_source, b.public_transport, b.railway, b.ref, b.religion, b.route, b.service, b.shop, b.sport, b.surface, b.toll, b.tourism, b."tower:type", b.tracktype, b.tunnel, b.water, b.waterway, b.wetland, b.width, b.wood, b.z_order, b.way_area, b.tags, b.way
  • Filter: (b.name = 'Jefferson County'::text)
  • Rows Removed by Filter: 335,921
  • Buffers: shared hit=40,973
  • Worker 0: actual time=125.631..125.632 rows=0 loops=1
  • Buffers: shared hit=16,228
  • Worker 1: actual time=124.858..124.858 rows=0 loops=1
  • Buffers: shared hit=15,256
10. 17.083 17.083 ↓ 425.0 425 1 / 3

Index Scan using planet_osm_point_way_idx on public.planet_osm_point p (cost=0.29..20,354.52 rows=1 width=40) (actual time=0.650..51.249 rows=425 loops=1)

  • Output: p.osm_id, p.access, p."addr:housename", p."addr:housenumber", p."addr:interpolation", p.admin_level, p.aerialway, p.aeroway, p.amenity, p.area, p.barrier, p.bicycle, p.brand, p.bridge, p.boundary, p.building, p.capital, p.construction, p.covered, p.culvert, p.cutting, p.denomination, p.disused, p.ele, p.embankment, p.foot, p."generator:source", p.harbour, p.highway, p.historic, p.horse, p.intermittent, p.junction, p.landuse, p.layer, p.leisure, p.lock, p.man_made, p.military, p.motorcar, p.name, p."natural", p.office, p.oneway, p.operator, p.place, p.population, p.power, p.power_source, p.public_transport, p.railway, p.ref, p.religion, p.route, p.service, p.shop, p.sport, p.surface, p.toll, p.tourism, p."tower:type", p.tunnel, p.water, p.waterway, p.wetland, p.width, p.wood, p.z_order, p.tags, p.way
  • Index Cond: (p.way @ b.way)
  • Filter: ((p.amenity = 'bench'::text) AND st_contains(b.way, p.way))
  • Rows Removed by Filter: 98,811
  • Buffers: shared hit=6,220
11. 10.342 10.342 ↑ 28.0 1 425 / 3

Index Scan using planet_osm_point_way_idx on public.planet_osm_point t (cost=0.54..1,909.03 rows=28 width=40) (actual time=0.065..0.073 rows=1 loops=425)

  • Output: t.osm_id, t.access, t."addr:housename", t."addr:housenumber", t."addr:interpolation", t.admin_level, t.aerialway, t.aeroway, t.amenity, t.area, t.barrier, t.bicycle, t.brand, t.bridge, t.boundary, t.building, t.capital, t.construction, t.covered, t.culvert, t.cutting, t.denomination, t.disused, t.ele, t.embankment, t.foot, t."generator:source", t.harbour, t.highway, t.historic, t.horse, t.intermittent, t.junction, t.landuse, t.layer, t.leisure, t.lock, t.man_made, t.military, t.motorcar, t.name, t."natural", t.office, t.oneway, t.operator, t.place, t.population, t.power, t.power_source, t.public_transport, t.railway, t.ref, t.religion, t.route, t.service, t.shop, t.sport, t.surface, t.toll, t.tourism, t."tower:type", t.tunnel, t.water, t.waterway, t.wetland, t.width, t.wood, t.z_order, t.tags, t.way
  • Index Cond: (t.way && st_expand(p.way, '10'::double precision))
  • Filter: ((t."natural" = 'tree'::text) AND st_dwithin(p.way, t.way, '10'::double precision))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=4,180
Planning time : 2.574 ms
Execution time : 159.411 ms