explain.depesz.com

PostgreSQL's explain analyze made readable

Result: qXRk

Settings
# exclusive inclusive rows x rows loops node
1. 40.172 192,066.162 ↑ 1.0 1 1

Aggregate (cost=7,236.72..7,236.82 rows=1 width=32) (actual time=192,066.162..192,066.162 rows=1 loops=1)

2. 4.658 192,025.990 ↓ 6.3 3,432 1

Subquery Scan on q (cost=7,065.99..7,235.36 rows=542 width=44) (actual time=175,216.545..192,025.990 rows=3,432 loops=1)

3. 16,483.223 192,021.332 ↓ 6.3 3,432 1

GroupAggregate (cost=7,065.99..7,181.16 rows=542 width=255) (actual time=175,216.503..192,021.332 rows=3,432 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.055 0.055 ↑ 1.0 1 1

Index Scan using idx_v_transit_boundaries_code on v_transit_boundaries (cost=0.28..2.38 rows=1 width=78,632) (actual time=0.051..0.055 rows=1 loops=1)

  • Index Cond: (code = ANY ('{LGA_34580}'::text[]))
6. 8,320.081 175,538.109 ↓ 6,444.9 3,493,140 1

Sort (cost=7,063.61..7,064.96 rows=542 width=211) (actual time=175,216.388..175,538.109 rows=3,493,140 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: 1141224kB
7. 6,762.754 167,218.028 ↓ 6,444.9 3,493,140 1

Nested Loop (cost=1.28..7,038.99 rows=542 width=211) (actual time=12.228..167,218.028 rows=3,493,140 loops=1)

8. 1,212.778 139,496.434 ↓ 6,444.9 3,493,140 1

Nested Loop (cost=0.86..5,782.48 rows=542 width=195) (actual time=12.193..139,496.434 rows=3,493,140 loops=1)

9. 12.616 5,719.224 ↓ 3,432.0 3,432 1

Nested Loop (cost=0.29..3.19 rows=1 width=187) (actual time=12.142..5,719.224 rows=3,432 loops=1)

10. 0.062 0.062 ↑ 1.0 1 1

CTE Scan on boundaries (cost=0.00..0.20 rows=1 width=32) (actual time=0.056..0.062 rows=1 loops=1)

11. 5,706.546 5,706.546 ↓ 3,432.0 3,432 1

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..2.89 rows=1 width=187) (actual time=2.830..5,706.546 rows=3,432 loops=1)

  • Index Cond: ((geometry && boundaries.geometry) AND (geometry && '0103000020110F000001000000050000003D0A977A991F7041CDCCCC56F6E247C13D0A977A991F7041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B5327041CDCCCC56F6E247C13D0A977A991F7041CDCCCC56F6E247C1'::geometry))
  • Filter: (_st_intersects(geometry, '0103000020110F000001000000050000003D0A977A991F7041CDCCCC56F6E247C13D0A977A991F7041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B5327041CDCCCC56F6E247C13D0A977A991F7041CDCCCC56F6E247C1'::geometry) AND _st_intersects(geometry, boundaries.geometry))
  • Rows Removed by Filter: 1564
12. 132,564.432 132,564.432 ↑ 5.0 1,018 3,432

Index Scan using idx_road_segments_link_id on road_segments (cost=0.57..5,266.99 rows=5,123 width=16) (actual time=0.194..38.626 rows=1,018 loops=3,432)

  • Index Cond: (link_id = v_transit_roads.link_id)
13. 20,958.840 20,958.840 ↑ 1.0 1 3,493,140

Index Scan using road_movements_pkey on road_movements (cost=0.42..2.32 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=3,493,140)

  • Index Cond: (trip_id = road_segments.trip_id)
Planning time : 889.342 ms