explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bssa : tunnel

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.002 39.654 ↓ 0.0 0 1

Limit (cost=2,502.45..2,559.39 rows=1 width=338) (actual time=39.654..39.654 rows=0 loops=1)

2. 0.453 39.652 ↓ 0.0 0 1

Nested Loop (cost=2,502.45..2,559.39 rows=1 width=338) (actual time=39.652..39.652 rows=0 loops=1)

  • Join Filter: (((geography(st_transform(st_endpoint(l.way), 4326)) && _st_expand((po.po)::geography, '1000'::double precision)) AND ((po.po)::geography && _st_expand(geography(st_transform(st_endpoint(l.way), 4326)), '1000'::double precision)) AND _st_dwithin(geography(st_transform(st_endpoint(l.way), 4326)), (po.po)::geography, '1000'::double precision, true)) OR ((geography(st_transform(st_startpoint(l.way), 4326)) && _st_expand((po.po)::geography, '1000'::double precision)) AND ((po.po)::geography && _st_expand(geography(st_transform(st_startpoint(l.way), 4326)), '1000'::double precision)) AND _st_dwithin(geography(st_transform(st_startpoint(l.way), 4326)), (po.po)::geography, '1000'::double precision, true)))
  • Rows Removed by Join Filter: 15
3. 3.872 39.184 ↓ 15.0 15 1

Bitmap Heap Scan on north_america_line l (cost=2,502.45..2,558.03 rows=1 width=306) (actual time=36.188..39.184 rows=15 loops=1)

  • Recheck Cond: ((st_transform(way, 4326) && '0103000020E61000000100000005000000C30FCEA78E4753C05F7D3CF4DD674340C30FCEA78E4753C016A6EF35047B4340F67D3848884153C016A6EF35047B4340F67D3848884153C05F7D3CF4DD674340C30FCEA78E4753C05F7D3CF4DD674340'::geometry) AND (tunnel IS NOT NULL))
  • Rows Removed by Index Recheck: 359
  • Filter: ((railway = ANY ('{subway,light_rail,funicular,rail,tram}'::text[])) AND (st_length(st_transform(way, 4326)) > '0.001'::double precision) AND _st_intersects(st_transform(way, 4326), '0103000020E61000000100000005000000C30FCEA78E4753C05F7D3CF4DD674340C30FCEA78E4753C016A6EF35047B4340F67D3848884153C016A6EF35047B4340F67D3848884153C05F7D3CF4DD674340C30FCEA78E4753C05F7D3CF4DD674340'::geometry))
  • Rows Removed by Filter: 141
  • Heap Blocks: exact=267
4. 2.175 35.312 ↓ 0.0 0 1

BitmapAnd (cost=2,502.45..2,502.45 rows=13 width=0) (actual time=35.312..35.312 rows=0 loops=1)

5. 5.078 5.078 ↓ 4.6 17,110 1

Bitmap Index Scan on north_america_way_geom (cost=0.00..388.48 rows=3,741 width=0) (actual time=5.078..5.078 rows=17,110 loops=1)

  • Index Cond: (st_transform(way, 4326) && '0103000020E61000000100000005000000C30FCEA78E4753C05F7D3CF4DD674340C30FCEA78E4753C016A6EF35047B4340F67D3848884153C016A6EF35047B4340F67D3848884153C05F7D3CF4DD674340C30FCEA78E4753C05F7D3CF4DD674340'::geometry)
6. 28.059 28.059 ↑ 1.0 114,211 1

Bitmap Index Scan on north_america_line_tunnel (cost=0.00..2,113.72 rows=114,288 width=0) (actual time=28.059..28.059 rows=114,211 loops=1)

  • Index Cond: (tunnel IS NOT NULL)
7. 0.015 0.015 ↑ 1.0 1 15

Function Scan on po (cost=0.00..0.01 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=15)