explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 1R1i : OSM Jefferson County Benches near Trees - Pg11, PostGIS 3 - Additional Indexes

Settings
# exclusive inclusive rows x rows loops node
1. 0.004 123.469 ↓ 10.0 10 1

Limit (cost=4,643.88..4,643.88 rows=1 width=48) (actual time=123.462..123.469 rows=10 loops=1)

  • Output: p.osm_id, p.way, (count(t.osm_id))
  • Buffers: shared hit=8,704
2. 0.102 123.465 ↓ 10.0 10 1

Sort (cost=4,643.88..4,643.88 rows=1 width=48) (actual time=123.460..123.465 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=8,704
3. 0.193 123.363 ↓ 159.0 159 1

GroupAggregate (cost=4,643.84..4,643.87 rows=1 width=48) (actual time=123.157..123.363 rows=159 loops=1)

  • Output: p.osm_id, p.way, count(t.osm_id)
  • Group Key: p.osm_id, p.way
  • Buffers: shared hit=8,704
4. 0.336 123.170 ↓ 320.0 320 1

Sort (cost=4,643.84..4,643.85 rows=1 width=48) (actual time=123.142..123.170 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=8,704
5. 1.433 122.834 ↓ 320.0 320 1

Nested Loop (cost=16.22..4,643.83 rows=1 width=48) (actual time=18.599..122.834 rows=320 loops=1)

  • Output: p.osm_id, p.way, t.osm_id
  • Buffers: shared hit=8,704
6. 0.175 73.376 ↓ 106.2 425 1

Nested Loop (cost=15.93..4,599.54 rows=4 width=40) (actual time=14.983..73.376 rows=425 loops=1)

  • Output: p.osm_id, p.way
  • Buffers: shared hit=3,824
7. 0.031 0.031 ↑ 4.0 1 1

Index Scan using ix_planet_osm_polygon_name on public.planet_osm_polygon b (cost=0.42..9.92 rows=4 width=230) (actual time=0.027..0.031 rows=1 loops=1)

  • 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.name = 'Jefferson County'::text)
  • Buffers: shared hit=4
8. 58.616 73.170 ↓ 425.0 425 1

Bitmap Heap Scan on public.planet_osm_point p (cost=15.51..1,147.40 rows=1 width=40) (actual time=14.950..73.170 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
  • Recheck Cond: (b.way ~ p.way)
  • Filter: ((p.amenity = 'bench'::text) AND _st_contains(b.way, p.way))
  • Rows Removed by Filter: 98,811
  • Heap Blocks: exact=1,207
  • Buffers: shared hit=3,820
9. 14.554 14.554 ↓ 130.1 99,236 1

Bitmap Index Scan on planet_osm_point_way_idx (cost=0.00..15.51 rows=763 width=0) (actual time=14.554..14.554 rows=99,236 loops=1)

  • Index Cond: (b.way ~ p.way)
  • Buffers: shared hit=689
10. 48.025 48.025 ↑ 1.0 1 425

Index Scan using planet_osm_point_way_idx on public.planet_osm_point t (cost=0.29..11.06 rows=1 width=40) (actual time=0.102..0.113 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 (p.way && st_expand(t.way, '10'::double precision)) AND _st_dwithin(p.way, t.way, '10'::double precision))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=4,880
Planning time : 0.514 ms
Execution time : 123.597 ms