explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OqEO5 : Pg12 PostGIS3 Points within Buffer

Settings
# exclusive inclusive rows x rows loops node
1. 18.734 69.250 ↑ 1.5 706 1

Gather (cost=1,035.31..53,571.03 rows=1,064 width=37) (actual time=37.632..69.250 rows=706 loops=1)

  • Output: t."natural", t.way
  • Workers Planned: 2
  • Workers Launched: 2
  • Buffers: shared hit=21117
2. 0.000 50.516 ↑ 1.9 235 3

Nested Loop (cost=35.31..52,464.63 rows=443 width=37) (actual time=42.065..50.516 rows=235 loops=3)

  • Output: t."natural", t.way
  • Buffers: shared hit=21117
  • Worker 0: actual time=45.172..45.172 rows=0 loops=1
  • Buffers: shared hit=6612
  • Worker 1: actual time=44.890..44.890 rows=0 loops=1
  • Buffers: shared hit=6536
3. 42.037 42.037 ↓ 0.0 0 3

Parallel Seq Scan on osm.building_polygon b (cost=0.00..23,999.87 rows=2 width=188) (actual time=41.873..42.037 rows=0 loops=3)

  • Output: b.osm_id, b.housename, b.housenumber, b.building, b.name, b.place, b.levels, b.height, b.tags, b.way, b.code
  • Filter: (b.name = 'Pangea Coffee Roasters'::text)
  • Rows Removed by Filter: 185847
  • Buffers: shared hit=21096
  • Worker 0: actual time=45.170..45.170 rows=0 loops=1
  • Buffers: shared hit=6612
  • Worker 1: actual time=44.888..44.888 rows=0 loops=1
  • Buffers: shared hit=6536
4. 24.975 25.198 ↓ 26.1 706 1

Bitmap Heap Scan on osm.natural_point t (cost=35.31..14,232.11 rows=27 width=37) (actual time=0.557..25.198 rows=706 loops=1)

  • Output: t.osm_id, t.name, t."natural", t.elevation, t.way, t.code
  • Filter: ((t."natural" = 'tree'::text) AND st_contains(st_buffer(b.way, '1000'::double precision, ''::text), t.way))
  • Rows Removed by Filter: 8
  • Heap Blocks: exact=12
  • Buffers: shared hit=21
5. 0.223 0.223 ↓ 2.6 714 1

Bitmap Index Scan on gix_osm_natural_point (cost=0.00..35.31 rows=270 width=0) (actual time=0.223..0.223 rows=714 loops=1)

  • Index Cond: (t.way @ st_buffer(b.way, '1000'::double precision, ''::text))
  • Buffers: shared hit=9
Planning time : 0.211 ms
Execution time : 69.407 ms