explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cpgF : OSM Jefferson County Benches near Trees - Pg12, PostGIS 3

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 156.208 ↑ 1.0 10 1

Limit (cost=209,413.07..209,413.09 rows=10 width=48) (actual time=143.951..156.208 rows=10 loops=1)

  • Output: p.osm_id, p.way, (count(t.osm_id))
  • Buffers: shared hit=51,384
2. 0.105 156.204 ↑ 341.0 10 1

Sort (cost=209,413.07..209,421.59 rows=3,410 width=48) (actual time=143.949..156.204 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,384
3. 0.232 156.099 ↑ 21.4 159 1

Finalize GroupAggregate (cost=208,458.40..209,339.38 rows=3,410 width=48) (actual time=143.587..156.099 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,384
4. 25.012 155.867 ↑ 42.9 159 1

Gather Merge (cost=208,458.40..209,254.13 rows=6,820 width=48) (actual time=143.565..155.867 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,384
5. 0.106 130.855 ↑ 64.3 53 3 / 3

Sort (cost=207,458.38..207,466.91 rows=3,410 width=48) (actual time=130.846..130.855 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: 25kB
  • Worker 0: Sort Method: quicksort Memory: 25kB
  • Worker 1: Sort Method: quicksort Memory: 37kB
  • Buffers: shared hit=51,384
  • Worker 0: actual time=126.129..126.131 rows=0 loops=1
  • Buffers: shared hit=17,482
  • Worker 1: actual time=123.931..123.953 rows=159 loops=1
  • Buffers: shared hit=15,849
6. 0.447 130.749 ↑ 64.3 53 3 / 3

Partial HashAggregate (cost=207,224.19..207,258.29 rows=3,410 width=48) (actual time=130.729..130.749 rows=53 loops=3)

  • Output: p.osm_id, p.way, PARTIAL count(t.osm_id)
  • Group Key: p.osm_id, p.way
  • Buffers: shared hit=51,352
  • Worker 0: actual time=126.084..126.086 rows=0 loops=1
  • Buffers: shared hit=17,466
  • Worker 1: actual time=27.456..123.249 rows=320 loops=1
  • Buffers: shared hit=15,833
7. 0.103 121.944 ↓ 2.3 142 3 / 3

Nested Loop (cost=0.29..86,927.32 rows=61 width=40) (actual time=98.572..121.944 rows=142 loops=3)

  • Output: p.osm_id, p.way
  • Buffers: shared hit=47,374
  • Worker 0: actual time=126.004..126.006 rows=0 loops=1
  • Buffers: shared hit=17,466
  • Worker 1: actual time=27.272..97.383 rows=425 loops=1
  • Buffers: shared hit=11,855
8. 103.619 103.619 ↓ 0.0 0 3 / 3

Parallel Seq Scan on public.planet_osm_polygon b (cost=0.00..46,218.26 rows=2 width=239) (actual time=98.304..103.619 rows=0 loops=3)

  • Output: b.osm_id, b.access, b."addr:housename", b."addr:housenumber", b."addr:interpolation", b.admin_level, b.aerialway,
  • Filter: (b.name = 'Jefferson County'::text)
  • Rows Removed by Filter: 335,921
  • Buffers: shared hit=40,973
  • Worker 0: actual time=126.003..126.004 rows=0 loops=1
  • Buffers: shared hit=17,466
  • Worker 1: actual time=26.470..42.415 rows=1 loops=1
  • Buffers: shared hit=5,454
9. 18.222 18.222 ↓ 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.635..54.667 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,366
  • Worker 1: actual time=0.635..54.667 rows=425 loops=1
  • Buffers: shared hit=6,366
10. 8.358 8.358 ↑ 28.0 1 425 / 3

Index Scan using planet_osm_point_way_idx on public.planet_osm_point t (cost=0.54..1,908.90 rows=28 width=40) (actual time=0.054..0.059 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=3,978
  • Worker 1: actual time=0.054..0.059 rows=1 loops=425
  • Buffers: shared hit=3,978
Planning time : 2.635 ms
Execution time : 156.346 ms