explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kKnJ : OSM Jefferson County Benches near Trees - Pg11, PostGIS 3

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 382.495 ↓ 10.0 10 1

Limit (cost=44,012.41..44,012.42 rows=1 width=48) (actual time=376.602..382.495 rows=10 loops=1)

  • Output: p.osm_id, p.way, (count(t.osm_id))
  • Buffers: shared hit=81,960
2. 0.086 382.492 ↓ 10.0 10 1

Sort (cost=44,012.41..44,012.42 rows=1 width=48) (actual time=376.600..382.492 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=81,960
3. 0.000 382.406 ↓ 159.0 159 1

GroupAggregate (cost=44,012.38..44,012.40 rows=1 width=48) (actual time=376.355..382.406 rows=159 loops=1)

  • Output: p.osm_id, p.way, count(t.osm_id)
  • Group Key: p.osm_id, p.way
  • Buffers: shared hit=81,960
  • Group Key: p.osm_id, p.way
  • Buffers: shared hit=81,960
4. 382.250 382.250 ↓ 320.0 320 1

Sort (cost=44,012.38..44,012.38 rows=1 width=48) (actual time=376.342..382.250 rows=320 loops=1)

  • Output: p.osm_id, p.way, t.osm_id
5. 0.246 382.250 ↓ 320.0 320 1

Sort (cost=44,012.38..44,012.38 rows=1 width=48) (actual time=376.342..382.250 rows=320 loops=1)

  • Output: p.osm_id, p.way, t.osm_id
  • Sort Key: p.osm_id, p.way
  • Sort Method: quicksort Memory: 50kB
  • Buffers: shared hit=81,960
6. 21.369 382.004 ↓ 320.0 320 1

Gather (cost=1,000.70..44,012.37 rows=1 width=48) (actual time=161.552..382.004 rows=320 loops=1)

  • Output: p.osm_id, p.way, t.osm_id
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=81,960
7. 0.672 360.635 ↓ 107.0 107 3 / 3

Nested Loop (cost=0.70..43,012.27 rows=1 width=48) (actual time=146.798..360.635 rows=107 loops=3)

  • Output: p.osm_id, p.way, t.osm_id
  • Buffers: shared hit=81,960
  • Worker 0: actual time=140.472..353.473 rows=82 loops=1
  • Buffers: shared hit=18,921
  • Worker 1: actual time=139.150..353.508 rows=109 loops=1
  • Buffers: shared hit=29,243
8. 3.119 123.067 ↓ 2.7 449 3 / 3

Nested Loop (cost=0.29..31,131.50 rows=168 width=48) (actual time=2.712..123.067 rows=449 loops=3)

  • Output: p.osm_id, p.way, t.osm_id
  • Buffers: shared hit=40,715
  • Worker 0: actual time=4.795..105.594 rows=342 loops=1
  • Buffers: shared hit=8,500
  • Worker 1: actual time=0.508..126.627 rows=467 loops=1
  • Buffers: shared hit=14,478
9. 38.137 38.137 ↑ 1.5 1,186 3 / 3

Parallel Seq Scan on public.planet_osm_point p (cost=0.00..13,772.39 rows=1,781 width=40) (actual time=0.257..38.137 rows=1,186 loops=3)

  • 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
  • Filter: (p.amenity = 'bench'::text)
  • Rows Removed by Filter: 253,303
  • Buffers: shared hit=9,796
  • Worker 0: actual time=0.048..31.077 rows=727 loops=1
  • Buffers: shared hit=1,768
  • Worker 1: actual time=0.041..43.429 rows=1,184 loops=1
  • Buffers: shared hit=3,807
10. 81.811 81.811 ↓ 0.0 0 3,557 / 3

Index Scan using planet_osm_point_way_idx on public.planet_osm_point t (cost=0.29..9.74 rows=1 width=40) (actual time=0.064..0.069 rows=0 loops=3,557)

  • Output: t.osm_id, t.access, t."addr:housename", t."addr:housenumber", t."addr:interpolation", t.admin_level, t.aerialway, t.aero
  • Index Cond: (t.way && st_expand(p.way, '10'::double precision))
  • Filter: ((t."natural" = 'tree'::text) AND (p.way && st_expand(t.way, '10'::double precision)) AND _st_dwithin(p.way, t.way, '10'::double precision))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=30,919
  • Worker 0: actual time=0.090..0.099 rows=0 loops=727
  • Buffers: shared hit=6,732
  • Worker 1: actual time=0.063..0.068 rows=0 loops=1,184
  • Buffers: shared hit=10,671
11. 236.896 236.896 ↓ 0.0 0 1,346 / 3

Index Scan using planet_osm_polygon_way_idx on public.planet_osm_polygon b (cost=0.41..70.71 rows=1 width=239) (actual time=0.500..0.528 rows=0 loops=1,346)

  • 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
  • Index Cond: (b.way ~ p.way)
  • Filter: ((b.name = 'Jefferson County'::text) AND _st_contains(b.way, p.way))
  • Rows Removed by Filter: 7
  • Buffers: shared hit=41,245
  • Worker 0: actual time=0.681..0.723 rows=0 loops=342
  • Buffers: shared hit=10,421
  • Worker 1: actual time=0.462..0.485 rows=0 loops=467
  • Buffers: shared hit=14,765
Planning time : 0.318 ms
Execution time : 382.600 ms