explain.depesz.com

PostgreSQL's explain analyze made readable

Result: mWVA

Settings
# exclusive inclusive rows x rows loops node
1. 6.128 43,764.445 ↑ 1.0 1 1

Aggregate (cost=6,670.14..6,670.15 rows=1 width=32) (actual time=43,764.445..43,764.445 rows=1 loops=1)

2. 1.292 43,758.317 ↓ 417.0 417 1

Subquery Scan on q (cost=6,632.54..6,670.13 rows=1 width=44) (actual time=25,291.740..43,758.317 rows=417 loops=1)

3. 18,157.829 43,757.025 ↓ 417.0 417 1

GroupAggregate (cost=6,632.54..6,670.12 rows=1 width=253) (actual time=25,291.723..43,757.025 rows=417 loops=1)

  • 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
4.          

CTE boundaries

5. 0.000 0.000 ↓ 0.0 0

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

6. 6,338.253 25,599.196 ↓ 5,599.9 3,819,120 1

Sort (cost=5,722.08..5,723.79 rows=682 width=209) (actual time=25,285.241..25,599.196 rows=3,819,120 loops=1)

  • 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
  • Sort Method: quicksort Memory: 1212089kB
7. 1,707.469 19,260.943 ↓ 5,599.9 3,819,120 1

Nested Loop Left Join (cost=5.73..5,689.98 rows=682 width=209) (actual time=0.603..19,260.943 rows=3,819,120 loops=1)

  • Join Filter: NULL::boolean
8. 6,762.751 17,553.474 ↓ 5,599.9 3,819,120 1

Nested Loop Left Join (cost=5.73..5,683.16 rows=682 width=209) (actual time=0.601..17,553.474 rows=3,819,120 loops=1)

9. 734.061 3,152.483 ↓ 5,599.9 3,819,120 1

Nested Loop (cost=5.31..463.10 rows=682 width=193) (actual time=0.578..3,152.483 rows=3,819,120 loops=1)

10. 27.626 27.761 ↓ 417.0 417 1

Bitmap Heap Scan on v_transit_roads (cost=4.73..174.86 rows=1 width=185) (actual time=0.285..27.761 rows=417 loops=1)

  • Recheck Cond: ((street_name)::text = ANY ('{"Gore Hwy"}'::text[]))
  • Filter: ((geometry && '0103000020110F00000100000005000000EC511814C3F26F41CDCCCC56F6E247C1EC511814C3F26F410AD723DBB51449C13D0A977A991F70410AD723DBB51449C13D0A977A991F7041CDCCCC56F6E247C1EC511814C3F26F41CDCCCC56F6E247C1'::geometry) AND _st_intersects(geometry, '0103000020110F00000100000005000000EC511814C3F26F41CDCCCC56F6E247C1EC511814C3F26F410AD723DBB51449C13D0A977A991F70410AD723DBB51449C13D0A977A991F7041CDCCCC56F6E247C1EC511814C3F26F41CDCCCC56F6E247C1'::geometry))
  • Rows Removed by Filter: 32
  • Heap Blocks: exact=119
11. 0.135 0.135 ↓ 11.0 449 1

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

  • Index Cond: ((street_name)::text = ANY ('{"Gore Hwy"}'::text[]))
12. 2,390.661 2,390.661 ↓ 1.8 9,159 417

Index Only Scan using idx_road_segments_key on road_segments (cost=0.57..236.00 rows=5,224 width=16) (actual time=0.034..5.733 rows=9,159 loops=417)

  • Index Cond: (link_id = v_transit_roads.link_id)
  • Heap Fetches: 0
13. 7,638.240 7,638.240 ↑ 1.0 1 3,819,120

Index Scan using trip on road_movements (cost=0.42..7.65 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=3,819,120)

  • Index Cond: (road_segments.trip_id = trip_id)
14. 0.000 0.000 ↓ 0.0 0 3,819,120

Result (cost=0.00..0.00 rows=0 width=0) (actual time=0.000..0.000 rows=0 loops=3,819,120)

  • One-Time Filter: false
Planning time : 13.242 ms