explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CcW8

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Aggregate (cost=21,792.23..21,792.24 rows=1 width=32) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Subquery Scan on q (cost=1,214.50..21,779.17 rows=5,224 width=1,366) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,214.50..21,726.93 rows=5,224 width=1,342) (actual rows= loops=)

4.          

CTE boundaries

5. 0.000 0.000 ↓ 0.0

Seq Scan on v_transit_boundaries (cost=0.00..910.46 rows=3,346 width=75,373) (actual rows= loops=)

6.          

CTE segments

7. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=174.88..174.98 rows=1 width=246) (actual rows= loops=)

  • Group Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry
8. 0.000 0.000 ↓ 0.0

Sort (cost=174.88..174.89 rows=1 width=206) (actual rows= loops=)

  • Sort Key: v_transit_roads.link_id, v_transit_roads.street_name, v_transit_roads.road_speed, v_transit_roads.road_rank, v_transit_roads.truck, v_transit_roads.surface, v_transit_roads.road_km, v_transit_roads.geometry
9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4.73..174.87 rows=1 width=206) (actual rows= loops=)

  • Join Filter: NULL::boolean
10. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on v_transit_roads (cost=4.73..174.86 rows=1 width=206) (actual rows= loops=)

  • Recheck Cond: ((street_name)::text = ANY ('{"Gore Hwy"}'::text[]))
  • Filter: ((geometry && '0103000020110F00000100000005000000EC511814C3F26F41CDCCCC56F6E247C1EC511814C3F26F410AD723DBB51449C13D0A977A991F70410AD723DBB51449C13D0A977A991F7041CDCCCC56F6E247C1EC511814C3F26F41CDCCCC56F6E247C1'::geometry) AND _st_intersects(geometry, '0103000020110F00000100000005000000EC511814C3F26F41CDCCCC56F6E247C1EC511814C3F26F410AD723DBB51449C13D0A977A991F70410AD723DBB51449C13D0A977A991F7041CDCCCC56F6E247C1EC511814C3F26F41CDCCCC56F6E247C1'::geometry))
11. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_v_transit_roads_street_name (cost=0.00..4.73 rows=41 width=0) (actual rows= loops=)

  • Index Cond: ((street_name)::text = ANY ('{"Gore Hwy"}'::text[]))
12. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=0) (actual rows= loops=)

  • One-Time Filter: false
13. 0.000 0.000 ↓ 0.0

CTE Scan on segments (cost=0.00..0.02 rows=1 width=1,310) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on road_segments (cost=129.06..20,589.23 rows=5,224 width=32) (actual rows= loops=)

  • Recheck Cond: (segments.link_id = link_id)
15. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_road_segments_link_id (cost=0.00..127.76 rows=5,224 width=0) (actual rows= loops=)