explain.depesz.com

PostgreSQL's explain analyze made readable

Result: sw2v

Settings
# exclusive inclusive rows x rows loops node
1. 22.274 40,182.646 ↑ 1.0 1 1

Aggregate (cost=48,770,310.83..48,770,311.03 rows=1 width=32) (actual time=40,182.646..40,182.646 rows=1 loops=1)

  • Functions: 274
  • Options: Inlining true, Optimization true, Expressions true, Deforming true
  • Timing: Generation 25.782 ms, Inlining 32.277 ms, Optimization 852.150 ms, Emission 507.992 ms, Total 1418.201 ms"Execution Time: 40280.062 ms
2. 3.757 40,160.372 ↑ 2.5 3,432 1

Subquery Scan on q (cost=48,754,701.35..48,770,286.89 rows=8,578 width=44) (actual time=27,121.615..40,160.372 rows=3,432 loops=1)

3. 12,706.629 40,156.615 ↑ 2.5 3,432 1

GroupAggregate (cost=48,754,701.35..48,768,571.29 rows=8,578 width=255) (actual time=27,121.605..40,156.615 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. 5,692.572 27,449.986 ↓ 16.5 3,493,140 1

Sort (cost=48,754,701.35..48,755,231.40 rows=212,020 width=211) (actual time=27,121.492..27,449.986 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
5. 3,200.832 21,757.414 ↓ 16.5 3,493,140 1

Nested Loop (cost=1.43..48,735,944.11 rows=212,020 width=211) (actual time=1,399.824..21,757.414 rows=3,493,140 loops=1)

6. 364.288 11,570.302 ↓ 16.5 3,493,140 1

Nested Loop (cost=1.00..48,236,715.85 rows=212,020 width=195) (actual time=1,399.806..11,570.302 rows=3,493,140 loops=1)

7. 1,397.308 1,630.734 ↓ 11.0 3,432 1

Nested Loop (cost=0.57..55.17 rows=311 width=187) (actual time=1,399.742..1,630.734 rows=3,432 loops=1)

8. 0.062 0.062 ↑ 1.0 1 1

Index Scan using idx_v_transit_boundaries_code on v_transit_boundaries (cost=0.28..2.48 rows=1 width=78,632) (actual time=0.061..0.062 rows=1 loops=1)

  • Index Cond: (code = ANY ('{LGA_34580}'::text[]))
9. 233.364 233.364 ↓ 3,432.0 3,432 1

Index Scan using v_transit_roads_geometry_geom_idx on v_transit_roads (cost=0.29..52.49 rows=1 width=187) (actual time=4.305..233.364 rows=3,432 loops=1)

  • Index Cond: ((geometry && v_transit_boundaries.geometry) AND (geometry && '0103000020110F000001000000050000003D0A977A991F7041CDCCCC56F6E247C13D0A977A991F7041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B5327041CDCCCC56F6E247C13D0A977A991F7041CDCCCC56F6E247C1'::geometry))
  • Filter: (st_intersects(geometry, '0103000020110F000001000000050000003D0A977A991F7041CDCCCC56F6E247C13D0A977A991F7041EC51F818D67B48C1E17ADC72B5327041EC51F818D67B48C1E17ADC72B5327041CDCCCC56F6E247C13D0A977A991F7041CDCCCC56F6E247C1'::geometry) AND st_intersects(geometry, v_transit_boundaries.geometry))
  • Rows Removed by Filter: 1564
10. 311.876 9,575.280 ↑ 111.2 1,018 3,432

Append (cost=0.43..132,454.20 rows=113,238 width=16) (actual time=0.026..2.790 rows=1,018 loops=3,432)

11. 2,847.152 2,847.152 ↑ 1.4 1,578 56

Index Scan using road_segments_partitioned_0_link_id_idx on road_segments_partitioned_0 (cost=0.43..2,438.24 rows=2,279 width=16) (actual time=0.044..50.842 rows=1,578 loops=56)

  • Index Cond: (link_id = v_transit_roads.link_id)
12. 107.760 107.760 ↑ 2.2 976 60

Index Scan using road_segments_partitioned_1_link_id_idx on road_segments_partitioned_1 (cost=0.43..2,313.20 rows=2,163 width=16) (actual time=0.022..1.796 rows=976 loops=60)

  • Index Cond: (link_id = v_transit_roads.link_id)
13. 96.258 96.258 ↑ 4.3 516 61

Index Scan using road_segments_partitioned_2_link_id_idx on road_segments_partitioned_2 (cost=0.43..2,361.29 rows=2,206 width=16) (actual time=0.024..1.578 rows=516 loops=61)

  • Index Cond: (link_id = v_transit_roads.link_id)
14. 131.316 131.316 ↑ 1.9 1,206 62

Index Scan using road_segments_partitioned_3_link_id_idx on road_segments_partitioned_3 (cost=0.43..2,407.39 rows=2,250 width=16) (actual time=0.022..2.118 rows=1,206 loops=62)

  • Index Cond: (link_id = v_transit_roads.link_id)
15. 126.160 126.160 ↑ 1.5 1,529 80

Index Scan using road_segments_partitioned_4_link_id_idx on road_segments_partitioned_4 (cost=0.43..2,470.23 rows=2,309 width=16) (actual time=0.021..1.577 rows=1,529 loops=80)

  • Index Cond: (link_id = v_transit_roads.link_id)
16. 129.930 129.930 ↑ 2.5 895 61

Index Scan using road_segments_partitioned_5_link_id_idx on road_segments_partitioned_5 (cost=0.43..2,360.60 rows=2,208 width=16) (actual time=0.021..2.130 rows=895 loops=61)

  • Index Cond: (link_id = v_transit_roads.link_id)
17. 147.024 147.024 ↑ 3.4 652 72

Index Scan using road_segments_partitioned_6_link_id_idx on road_segments_partitioned_6 (cost=0.43..2,368.71 rows=2,214 width=16) (actual time=0.021..2.042 rows=652 loops=72)

  • Index Cond: (link_id = v_transit_roads.link_id)
18. 118.560 118.560 ↑ 3.2 696 76

Index Scan using road_segments_partitioned_7_link_id_idx on road_segments_partitioned_7 (cost=0.43..2,361.05 rows=2,209 width=16) (actual time=0.022..1.560 rows=696 loops=76)

  • Index Cond: (link_id = v_transit_roads.link_id)
19. 105.197 105.197 ↑ 1.5 1,468 59

Index Scan using road_segments_partitioned_8_link_id_idx on road_segments_partitioned_8 (cost=0.43..2,280.42 rows=2,132 width=16) (actual time=0.023..1.783 rows=1,468 loops=59)

  • Index Cond: (link_id = v_transit_roads.link_id)
20. 212.189 212.189 ↑ 2.2 1,088 67

Index Scan using road_segments_partitioned_9_link_id_idx on road_segments_partitioned_9 (cost=0.43..2,516.85 rows=2,352 width=16) (actual time=0.021..3.167 rows=1,088 loops=67)

  • Index Cond: (link_id = v_transit_roads.link_id)
21. 101.563 101.563 ↑ 3.7 589 77

Index Scan using road_segments_partitioned_10_link_id_idx on road_segments_partitioned_10 (cost=0.43..2,344.21 rows=2,191 width=16) (actual time=0.023..1.319 rows=589 loops=77)

  • Index Cond: (link_id = v_transit_roads.link_id)
22. 107.847 107.847 ↑ 1.8 1,294 69

Index Scan using road_segments_partitioned_11_link_id_idx on road_segments_partitioned_11 (cost=0.43..2,529.57 rows=2,365 width=16) (actual time=0.021..1.563 rows=1,294 loops=69)

  • Index Cond: (link_id = v_transit_roads.link_id)
23. 135.720 135.720 ↑ 2.5 946 78

Index Scan using road_segments_partitioned_12_link_id_idx on road_segments_partitioned_12 (cost=0.43..2,486.21 rows=2,325 width=16) (actual time=0.021..1.740 rows=946 loops=78)

  • Index Cond: (link_id = v_transit_roads.link_id)
24. 131.604 131.604 ↑ 2.2 1,058 66

Index Scan using road_segments_partitioned_13_link_id_idx on road_segments_partitioned_13 (cost=0.43..2,469.06 rows=2,311 width=16) (actual time=0.022..1.994 rows=1,058 loops=66)

  • Index Cond: (link_id = v_transit_roads.link_id)
25. 131.238 131.238 ↑ 2.2 1,030 69

Index Scan using road_segments_partitioned_14_link_id_idx on road_segments_partitioned_14 (cost=0.43..2,473.47 rows=2,311 width=16) (actual time=0.021..1.902 rows=1,030 loops=69)

  • Index Cond: (link_id = v_transit_roads.link_id)
26. 108.982 108.982 ↑ 1.4 1,583 58

Index Scan using road_segments_partitioned_15_link_id_idx on road_segments_partitioned_15 (cost=0.43..2,394.70 rows=2,237 width=16) (actual time=0.022..1.879 rows=1,583 loops=58)

  • Index Cond: (link_id = v_transit_roads.link_id)
27. 156.512 156.512 ↑ 3.3 693 73

Index Scan using road_segments_partitioned_16_link_id_idx on road_segments_partitioned_16 (cost=0.43..2,456.96 rows=2,296 width=16) (actual time=0.021..2.144 rows=693 loops=73)

  • Index Cond: (link_id = v_transit_roads.link_id)
28. 103.113 103.113 ↑ 1.6 1,390 67

Index Scan using road_segments_partitioned_17_link_id_idx on road_segments_partitioned_17 (cost=0.43..2,453.58 rows=2,292 width=16) (actual time=0.022..1.539 rows=1,390 loops=67)

  • Index Cond: (link_id = v_transit_roads.link_id)
29. 123.200 123.200 ↑ 1.9 1,183 56

Index Scan using road_segments_partitioned_18_link_id_idx on road_segments_partitioned_18 (cost=0.43..2,451.05 rows=2,294 width=16) (actual time=0.022..2.200 rows=1,183 loops=56)

  • Index Cond: (link_id = v_transit_roads.link_id)
30. 98.346 98.346 ↑ 5.7 392 74

Index Scan using road_segments_partitioned_19_link_id_idx on road_segments_partitioned_19 (cost=0.43..2,406.43 rows=2,249 width=16) (actual time=0.021..1.329 rows=392 loops=74)

  • Index Cond: (link_id = v_transit_roads.link_id)
31. 110.770 110.770 ↑ 3.0 752 55

Index Scan using road_segments_partitioned_20_link_id_idx on road_segments_partitioned_20 (cost=0.43..2,430.00 rows=2,271 width=16) (actual time=0.032..2.014 rows=752 loops=55)

  • Index Cond: (link_id = v_transit_roads.link_id)
32. 104.118 104.118 ↑ 4.9 462 74

Index Scan using road_segments_partitioned_21_link_id_idx on road_segments_partitioned_21 (cost=0.43..2,436.22 rows=2,277 width=16) (actual time=0.022..1.407 rows=462 loops=74)

  • Index Cond: (link_id = v_transit_roads.link_id)
33. 142.480 142.480 ↑ 1.3 1,823 80

Index Scan using road_segments_partitioned_22_link_id_idx on road_segments_partitioned_22 (cost=0.43..2,447.24 rows=2,289 width=16) (actual time=0.022..1.781 rows=1,823 loops=80)

  • Index Cond: (link_id = v_transit_roads.link_id)
34. 125.370 125.370 ↑ 2.4 975 63

Index Scan using road_segments_partitioned_23_link_id_idx on road_segments_partitioned_23 (cost=0.43..2,459.63 rows=2,299 width=16) (actual time=0.020..1.990 rows=975 loops=63)

  • Index Cond: (link_id = v_transit_roads.link_id)
35. 120.704 120.704 ↑ 2.6 895 64

Index Scan using road_segments_partitioned_24_link_id_idx on road_segments_partitioned_24 (cost=0.43..2,490.48 rows=2,328 width=16) (actual time=0.022..1.886 rows=895 loops=64)

  • Index Cond: (link_id = v_transit_roads.link_id)
36. 170.508 170.508 ↑ 2.3 979 78

Index Scan using road_segments_partitioned_25_link_id_idx on road_segments_partitioned_25 (cost=0.43..2,371.40 rows=2,217 width=16) (actual time=0.022..2.186 rows=979 loops=78)

  • Index Cond: (link_id = v_transit_roads.link_id)
37. 133.330 133.330 ↑ 2.2 1,015 67

Index Scan using road_segments_partitioned_26_link_id_idx on road_segments_partitioned_26 (cost=0.43..2,425.98 rows=2,267 width=16) (actual time=0.022..1.990 rows=1,015 loops=67)

  • Index Cond: (link_id = v_transit_roads.link_id)
38. 105.994 105.994 ↑ 1.7 1,378 67

Index Scan using road_segments_partitioned_27_link_id_idx on road_segments_partitioned_27 (cost=0.43..2,461.93 rows=2,305 width=16) (actual time=0.023..1.582 rows=1,378 loops=67)

  • Index Cond: (link_id = v_transit_roads.link_id)
39. 317.988 317.988 ↑ 2.3 956 66

Index Scan using road_segments_partitioned_28_link_id_idx on road_segments_partitioned_28 (cost=0.43..2,352.86 rows=2,198 width=16) (actual time=0.022..4.818 rows=956 loops=66)

  • Index Cond: (link_id = v_transit_roads.link_id)
40. 124.676 124.676 ↑ 1.3 1,708 71

Index Scan using road_segments_partitioned_29_link_id_idx on road_segments_partitioned_29 (cost=0.43..2,433.49 rows=2,274 width=16) (actual time=0.021..1.756 rows=1,708 loops=71)

  • Index Cond: (link_id = v_transit_roads.link_id)
41. 48.576 48.576 ↑ 7.0 325 69

Index Scan using road_segments_partitioned_30_link_id_idx on road_segments_partitioned_30 (cost=0.43..2,428.46 rows=2,270 width=16) (actual time=0.022..0.704 rows=325 loops=69)

  • Index Cond: (link_id = v_transit_roads.link_id)
42. 129.438 129.438 ↑ 2.3 974 81

Index Scan using road_segments_partitioned_31_link_id_idx on road_segments_partitioned_31 (cost=0.43..2,358.28 rows=2,206 width=16) (actual time=0.020..1.598 rows=974 loops=81)

  • Index Cond: (link_id = v_transit_roads.link_id)
43. 141.598 141.598 ↑ 2.3 983 83

Index Scan using road_segments_partitioned_32_link_id_idx on road_segments_partitioned_32 (cost=0.43..2,438.69 rows=2,281 width=16) (actual time=0.022..1.706 rows=983 loops=83)

  • Index Cond: (link_id = v_transit_roads.link_id)
44. 102.240 102.240 ↑ 3.4 652 60

Index Scan using road_segments_partitioned_33_link_id_idx on road_segments_partitioned_33 (cost=0.43..2,363.46 rows=2,208 width=16) (actual time=0.022..1.704 rows=652 loops=60)

  • Index Cond: (link_id = v_transit_roads.link_id)
45. 161.952 161.952 ↑ 1.8 1,333 84

Index Scan using road_segments_partitioned_34_link_id_idx on road_segments_partitioned_34 (cost=0.43..2,497.86 rows=2,335 width=16) (actual time=0.022..1.928 rows=1,333 loops=84)

  • Index Cond: (link_id = v_transit_roads.link_id)
46. 104.958 104.958 ↑ 4.0 549 63

Index Scan using road_segments_partitioned_35_link_id_idx on road_segments_partitioned_35 (cost=0.43..2,357.62 rows=2,204 width=16) (actual time=0.023..1.666 rows=549 loops=63)

  • Index Cond: (link_id = v_transit_roads.link_id)
47. 110.500 110.500 ↑ 3.3 679 68

Index Scan using road_segments_partitioned_36_link_id_idx on road_segments_partitioned_36 (cost=0.43..2,393.10 rows=2,238 width=16) (actual time=0.021..1.625 rows=679 loops=68)

  • Index Cond: (link_id = v_transit_roads.link_id)
48. 187.620 187.620 ↑ 4.0 565 60

Index Scan using road_segments_partitioned_37_link_id_idx on road_segments_partitioned_37 (cost=0.43..2,391.96 rows=2,236 width=16) (actual time=0.022..3.127 rows=565 loops=60)

  • Index Cond: (link_id = v_transit_roads.link_id)
49. 139.018 139.018 ↑ 2.5 899 71

Index Scan using road_segments_partitioned_38_link_id_idx on road_segments_partitioned_38 (cost=0.43..2,416.10 rows=2,260 width=16) (actual time=0.022..1.958 rows=899 loops=71)

  • Index Cond: (link_id = v_transit_roads.link_id)
50. 106.795 106.795 ↑ 3.5 646 65

Index Scan using road_segments_partitioned_39_link_id_idx on road_segments_partitioned_39 (cost=0.43..2,437.39 rows=2,278 width=16) (actual time=0.026..1.643 rows=646 loops=65)

  • Index Cond: (link_id = v_transit_roads.link_id)
51. 149.520 149.520 ↑ 1.8 1,310 70

Index Scan using road_segments_partitioned_40_link_id_idx on road_segments_partitioned_40 (cost=0.43..2,464.24 rows=2,303 width=16) (actual time=0.020..2.136 rows=1,310 loops=70)

  • Index Cond: (link_id = v_transit_roads.link_id)
52. 106.785 106.785 ↑ 1.6 1,495 63

Index Scan using road_segments_partitioned_41_link_id_idx on road_segments_partitioned_41 (cost=0.43..2,509.43 rows=2,345 width=16) (actual time=0.023..1.695 rows=1,495 loops=63)

  • Index Cond: (link_id = v_transit_roads.link_id)
53. 125.715 125.715 ↑ 2.7 851 85

Index Scan using road_segments_partitioned_42_link_id_idx on road_segments_partitioned_42 (cost=0.43..2,439.29 rows=2,279 width=16) (actual time=0.021..1.479 rows=851 loops=85)

  • Index Cond: (link_id = v_transit_roads.link_id)
54. 130.200 130.200 ↑ 2.2 1,075 60

Index Scan using road_segments_partitioned_43_link_id_idx on road_segments_partitioned_43 (cost=0.43..2,520.24 rows=2,358 width=16) (actual time=0.024..2.170 rows=1,075 loops=60)

  • Index Cond: (link_id = v_transit_roads.link_id)
55. 198.429 198.429 ↑ 1.8 1,213 77

Index Scan using road_segments_partitioned_44_link_id_idx on road_segments_partitioned_44 (cost=0.43..2,382.04 rows=2,226 width=16) (actual time=0.021..2.577 rows=1,213 loops=77)

  • Index Cond: (link_id = v_transit_roads.link_id)
56. 133.272 133.272 ↑ 2.0 1,145 72

Index Scan using road_segments_partitioned_45_link_id_idx on road_segments_partitioned_45 (cost=0.43..2,451.59 rows=2,291 width=16) (actual time=0.021..1.851 rows=1,145 loops=72)

  • Index Cond: (link_id = v_transit_roads.link_id)
57. 121.992 121.992 ↑ 2.3 979 68

Index Scan using road_segments_partitioned_46_link_id_idx on road_segments_partitioned_46 (cost=0.43..2,397.14 rows=2,242 width=16) (actual time=0.022..1.794 rows=979 loops=68)

  • Index Cond: (link_id = v_transit_roads.link_id)
58. 133.848 133.848 ↑ 2.0 1,116 78

Index Scan using road_segments_partitioned_47_link_id_idx on road_segments_partitioned_47 (cost=0.43..2,387.68 rows=2,232 width=16) (actual time=0.022..1.716 rows=1,116 loops=78)

  • Index Cond: (link_id = v_transit_roads.link_id)
59. 113.297 113.297 ↑ 2.7 822 67

Index Scan using road_segments_partitioned_48_link_id_idx on road_segments_partitioned_48 (cost=0.43..2,354.34 rows=2,200 width=16) (actual time=0.022..1.691 rows=822 loops=67)

  • Index Cond: (link_id = v_transit_roads.link_id)
60. 142.042 142.042 ↑ 1.4 1,619 62

Index Scan using road_segments_partitioned_49_link_id_idx on road_segments_partitioned_49 (cost=0.43..2,489.03 rows=2,328 width=16) (actual time=0.022..2.291 rows=1,619 loops=62)

  • Index Cond: (link_id = v_transit_roads.link_id)
61. 6,986.280 6,986.280 ↑ 1.0 1 3,493,140

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

  • Index Cond: (trip_id = road_segments_partitioned_0.trip_id)