explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yehe

Settings
# exclusive inclusive rows x rows loops node
1. 0.750 563.677 ↓ 3.4 110 1

Sort (cost=21,908.28..21,908.36 rows=32 width=2,039) (actual time=563.658..563.677 rows=110 loops=1)

  • Sort Key: r.""order"
  • Sort Method: quicksort Memory: 145kB
2.          

CTE meta_routes

3. 22.464 24.304 ↓ 2.4 111 1

Hash Left Join (cost=1,743.45..2,912.79 rows=47 width=68) (actual time=3.776..24.304 rows=111 loops=1)

  • Hash Cond: ((r_1.trailer_model_id)::integer = (tm.id)::integer)
4.          

Initplan (for Hash Left Join)

5. 0.001 0.001 ↓ 0.0 0 1

Result (cost=0.00..1,686.92 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1)

  • One-Time Filter: NULL::boolean
6. 0.000 0.000 ↓ 0.0 0

Seq Scan on deliveries_cars_tasks (cost=0.00..1,686.92 rows=1 width=4) (never executed)

7. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=53.06..53.07 rows=1 width=4) (never executed)

8.          

Initplan (for Aggregate)

9. 0.000 0.000 ↓ 0.0 0

Index Scan using delivery_pkey on deliveries (cost=0.29..8.30 rows=1 width=8) (never executed)

  • Index Cond: ((id)::integer = 218,571)
10. 0.000 0.000 ↓ 0.0 0

Index Scan using deliveries_idx1 on deliveries deliveries_1 (cost=0.29..44.72 rows=13 width=4) (never executed)

  • Index Cond: (((datetime)::timestamp without time zone = ($4)::timestamp without time zone) AND (accepted = true))
  • Filter: accepted
11. 0.140 1.787 ↓ 2.4 111 1

Hash Left Join (cost=2.19..221.70 rows=47 width=67) (actual time=0.111..1.787 rows=111 loops=1)

  • Hash Cond: ((r_1.car_model_id)::integer = (cm.id)::integer)
12. 0.243 1.634 ↓ 2.4 111 1

Nested Loop (cost=0.72..219.60 rows=47 width=67) (actual time=0.083..1.634 rows=111 loops=1)

13. 0.033 0.033 ↑ 1.0 1 1

Index Scan using delivery_pkey on deliveries d (cost=0.29..8.32 rows=1 width=8) (actual time=0.032..0.033 rows=1 loops=1)

  • Index Cond: ((id)::integer = 218,571)
  • Filter: CASE WHEN (NOT COALESCE((($3)::integer = 3), true)) THEN ((id)::integer = ANY ($5)) ELSE true END
14. 1.358 1.358 ↓ 2.4 111 1

Index Scan using routes_idx4 on routes r_1 (cost=0.43..210.82 rows=47 width=63) (actual time=0.046..1.358 rows=111 loops=1)

  • Index Cond: ((delivery_id)::integer = 218,571)
  • Filter: (routes_type_transits IS NULL)
15. 0.006 0.013 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=8) (actual time=0.013..0.013 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
16. 0.007 0.007 ↑ 1.0 21 1

Seq Scan on cars_models cm (cost=0.00..1.21 rows=21 width=8) (actual time=0.003..0.007 rows=21 loops=1)

17. 0.005 0.009 ↑ 1.0 12 1

Hash (cost=1.12..1.12 rows=12 width=9) (actual time=0.009..0.009 rows=12 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
18. 0.004 0.004 ↑ 1.0 12 1

Seq Scan on trailers_models tm (cost=0.00..1.12 rows=12 width=9) (actual time=0.002..0.004 rows=12 loops=1)

19.          

SubPlan (for Hash Left Join)

20. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=11.48..11.49 rows=1 width=26) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Seq Scan on distribution_centers dc_1 (cost=0.00..11.45 rows=10 width=26) (never executed)

  • Filter: ((id)::integer = ANY ((string_to_array(r_1.directions_dc_id, ','::text))::integer[]))
22. 0.000 0.000 ↓ 0.0 0

Index Only Scan using open_routes_change_sessions_route_state on open_routes_change_sessions orcs (cost=0.42..8.45 rows=1 width=0) (never executed)

  • Index Cond: ((state = 0) AND (route_id = (r_1.id)::integer))
  • Heap Fetches: 0
23. 0.043 0.043 ↓ 0.0 0 1

Index Only Scan using open_routes_change_sessions_route_state on open_routes_change_sessions orcs_1 (cost=0.42..8.44 rows=1 width=4) (actual time=0.043..0.043 rows=0 loops=1)

  • Index Cond: (state = 0)
  • Heap Fetches: 0
24.          

CTE markets_points

25. 0.288 4.496 ↓ 56.8 341 1

WindowAgg (cost=451.68..451.83 rows=6 width=58) (actual time=4.193..4.496 rows=341 loops=1)

26. 0.251 4.208 ↓ 56.8 341 1

Sort (cost=451.68..451.70 rows=6 width=58) (actual time=4.178..4.208 rows=341 loops=1)

  • Sort Key: rpa.route_id, ((rpa.sched_begin_time IS NOT NULL)), rpa.order_num DESC
  • Sort Method: quicksort Memory: 72kB
27. 0.129 3.957 ↓ 56.8 341 1

Nested Loop (cost=1.93..451.61 rows=6 width=58) (actual time=0.165..3.957 rows=341 loops=1)

28. 0.100 0.609 ↓ 2.4 111 1

Nested Loop (cost=1.49..399.15 rows=47 width=13) (actual time=0.066..0.609 rows=111 loops=1)

29. 0.052 0.065 ↓ 2.4 111 1

HashAggregate (cost=1.06..1.53 rows=47 width=4) (actual time=0.047..0.065 rows=111 loops=1)

  • Group Key: (mmr.route_id)::integer
30. 0.013 0.013 ↓ 2.4 111 1

CTE Scan on meta_routes mmr (cost=0.00..0.94 rows=47 width=4) (actual time=0.001..0.013 rows=111 loops=1)

31. 0.444 0.444 ↑ 1.0 1 111

Index Scan using routes_pkey on routes r_2 (cost=0.43..8.45 rows=1 width=9) (actual time=0.004..0.004 rows=1 loops=111)

  • Index Cond: ((id)::integer = (mmr.route_id)::integer)
32. 3.219 3.219 ↓ 3.0 3 111

Index Scan using routes_points_all_idx2 on routes_points_all rpa (cost=0.43..1.11 rows=1 width=63) (actual time=0.011..0.029 rows=3 loops=111)

  • Index Cond: ((route_id)::integer = (r_2.id)::integer)
  • Filter: (((type_id)::integer = ANY ('{1,2}'::integer[])) AND (object_type = 'market'::text) AND (COALESCE(r_2.opened, false) = COALESCE(open_route_point, false)) AND CASE WHEN (r_2.routes_type_transits IS NOT NULL) THEN ((type_id)::integer = ANY ('{3,4}'::integer[])) WHEN (r_2.routes_type = 0) THEN ((type_id)::integer = ANY ('{1,4}'::integer[])) WHEN (r_2.routes_type = 1) THEN ((type_id)::integer = ANY ('{2,4}'::integer[])) ELSE false END)
  • Rows Removed by Filter: 2
33.          

CTE missing_markets

34. 0.024 0.477 ↓ 9.0 9 1

HashAggregate (cost=0.13..0.14 rows=1 width=20) (actual time=0.475..0.477 rows=9 loops=1)

  • Group Key: mp.route_id
35. 0.453 0.453 ↓ 19.0 19 1

CTE Scan on markets_points mp (cost=0.00..0.12 rows=1 width=20) (actual time=0.035..0.453 rows=19 loops=1)

  • Filter: (gps_arrival_time IS NULL)
  • Rows Removed by Filter: 322
36.          

CTE last_market

37. 0.099 5.241 ↓ 11.0 66 1

HashAggregate (cost=0.41..0.47 rows=6 width=55) (actual time=5.232..5.241 rows=66 loops=1)

  • Group Key: mp_1.route_id, first_value(mp_1.settle_name) OVER (?)
38. 0.164 5.142 ↓ 56.8 341 1

WindowAgg (cost=0.26..0.38 rows=6 width=55) (actual time=4.958..5.142 rows=341 loops=1)

39. 0.155 4.978 ↓ 56.8 341 1

Sort (cost=0.26..0.28 rows=6 width=55) (actual time=4.950..4.978 rows=341 loops=1)

  • Sort Key: mp_1.route_id, (CASE WHEN (mp_1.last_sched_cheked AND (mm.main_id IS NOT TRUE) AND (mm.sub_id IS NOT TRUE)) THEN mp_1.gps_arrival_time ELSE mp_1.arrival_time END) DESC
  • Sort Method: quicksort Memory: 51kB
40. 0.077 4.823 ↓ 56.8 341 1

Hash Left Join (cost=0.03..0.19 rows=6 width=55) (actual time=4.703..4.823 rows=341 loops=1)

  • Hash Cond: ((mp_1.route_id)::integer = (mm.route_id)::integer)
41. 4.254 4.254 ↓ 56.8 341 1

CTE Scan on markets_points mp_1 (cost=0.00..0.12 rows=6 width=53) (actual time=4.196..4.254 rows=341 loops=1)

42. 0.010 0.492 ↓ 9.0 9 1

Hash (cost=0.02..0.02 rows=1 width=6) (actual time=0.492..0.492 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
43. 0.482 0.482 ↓ 9.0 9 1

CTE Scan on missing_markets mm (cost=0.00..0.02 rows=1 width=6) (actual time=0.477..0.482 rows=9 loops=1)

44.          

CTE meta_routes_drivers

45. 0.096 1.994 ↓ 1.6 107 1

HashAggregate (cost=1,743.39..1,744.05 rows=66 width=73) (actual time=1.977..1.994 rows=107 loops=1)

  • Group Key: rd.route_id, (string_agg(concat_ws(' '::text, rd.sname, rd.name, rd.pname), ', '::text ORDER BY rd.id)), (string_agg((COALESCE((rd.phones)::character varying, ''::character varying))::text, ', '::text ORDER BY rd.id))
46. 0.013 1.898 ↓ 1.6 107 1

Append (cost=248.00..1,742.90 rows=66 width=73) (actual time=0.482..1.898 rows=107 loops=1)

47. 0.156 0.623 ↑ 1.0 41 1

GroupAggregate (cost=248.00..249.15 rows=42 width=74) (actual time=0.481..0.623 rows=41 loops=1)

  • Group Key: rd.route_id
48. 0.034 0.467 ↑ 1.0 41 1

Sort (cost=248.00..248.10 rows=42 width=74) (actual time=0.460..0.467 rows=41 loops=1)

  • Sort Key: rd.route_id
  • Sort Method: quicksort Memory: 30kB
49. 0.011 0.433 ↑ 1.0 41 1

Nested Loop (cost=0.43..246.87 rows=42 width=74) (actual time=0.011..0.433 rows=41 loops=1)

50. 0.017 0.017 ↓ 1.9 45 1

CTE Scan on meta_routes mr2 (cost=0.00..0.94 rows=24 width=4) (actual time=0.002..0.017 rows=45 loops=1)

  • Filter: (opened IS NOT TRUE)
  • Rows Removed by Filter: 66
51. 0.405 0.405 ↑ 2.0 1 45

Index Scan using routes_drivers_idx on routes_drivers rd (cost=0.43..10.23 rows=2 width=74) (actual time=0.009..0.009 rows=1 loops=45)

  • Index Cond: ((route_id)::integer = (mr2.route_id)::integer)
52. 0.244 1.262 ↓ 2.8 66 1

GroupAggregate (cost=1,489.72..1,493.08 rows=24 width=72) (actual time=1.036..1.262 rows=66 loops=1)

  • Group Key: mr3.route_id
53. 0.038 1.018 ↑ 3.6 66 1

Sort (cost=1,489.72..1,490.32 rows=240 width=72) (actual time=1.012..1.018 rows=66 loops=1)

  • Sort Key: mr3.route_id
  • Sort Method: quicksort Memory: 34kB
54. 0.101 0.980 ↑ 3.6 66 1

Nested Loop (cost=0.42..1,480.23 rows=240 width=72) (actual time=0.049..0.980 rows=66 loops=1)

55. 0.021 0.021 ↓ 2.8 66 1

CTE Scan on meta_routes mr3 (cost=0.00..0.94 rows=24 width=36) (actual time=0.001..0.021 rows=66 loops=1)

  • Filter: (opened IS TRUE)
  • Rows Removed by Filter: 45
56. 0.858 0.858 ↑ 10.0 1 66

Index Scan using drivers_pkey1 on drivers d_1 (cost=0.42..61.54 rows=10 width=68) (actual time=0.013..0.013 rows=1 loops=66)

  • Index Cond: ((id)::integer = ANY (mr3.current_drivers_id))
57.          

Initplan (for Sort)

58. 0.000 0.000 ↓ 0.0 0

Index Scan using settings_variable_key on settings (cost=0.27..8.30 rows=1 width=43) (never executed)

  • Index Cond: ((var_name)::text = 'need_backlight_route_acception'::text)
59. 0.000 0.000 ↓ 0.0 0

Index Scan using settings_variable_key on settings settings_1 (cost=0.27..8.29 rows=1 width=43) (never executed)

  • Index Cond: ((var_name)::text = 'route_acception_color'::text)
60. 0.000 0.000 ↓ 0.0 0

Seq Scan on route_acception_statuses (cost=0.00..1.10 rows=1 width=4) (never executed)

  • Filter: ((code)::text = 'escalation_in_progress'::text)
61. 0.000 0.000 ↓ 0.0 0

Index Scan using settings_variable_key on settings settings_2 (cost=0.27..8.29 rows=1 width=43) (never executed)

  • Index Cond: ((var_name)::text = 'route_esc_in_progress_color'::text)
62. 0.000 0.000 ↓ 0.0 0

Index Scan using settings_variable_key on settings settings_3 (cost=0.27..8.29 rows=1 width=43) (never executed)

  • Index Cond: ((var_name)::text = 'route_non_acception_color'::text)
63. 0.000 0.000 ↓ 0.0 0

Seq Scan on auto_open_route_types (cost=0.00..1.02 rows=1 width=4) (never executed)

  • Filter: ((code)::text = 'future_auto_open'::text)
64. 110.355 562.927 ↓ 3.4 110 1

Hash Left Join (cost=11,628.99..16,762.90 rows=32 width=2,039) (actual time=339.946..562.927 rows=110 loops=1)

  • Hash Cond: (r.scheme_id = rs.id)
65. 0.130 442.658 ↓ 3.4 110 1

Nested Loop Left Join (cost=11,627.93..13,691.15 rows=32 width=2,030) (actual time=323.101..442.658 rows=110 loops=1)

66. 0.115 442.308 ↓ 3.4 110 1

Nested Loop Left Join (cost=11,627.64..13,681.23 rows=32 width=1,970) (actual time=323.089..442.308 rows=110 loops=1)

  • Join Filter: NULL::boolean
67. 0.039 442.193 ↓ 3.4 110 1

Nested Loop Left Join (cost=11,627.64..13,681.23 rows=32 width=1,969) (actual time=323.084..442.193 rows=110 loops=1)

68. 0.119 441.824 ↓ 3.4 110 1

Nested Loop Left Join (cost=11,627.22..13,666.84 rows=32 width=1,906) (actual time=323.066..441.824 rows=110 loops=1)

  • Join Filter: mr.is_transit_route
69. 0.095 441.375 ↓ 3.4 110 1

Hash Left Join (cost=11,626.80..13,652.44 rows=32 width=1,835) (actual time=323.047..441.375 rows=110 loops=1)

  • Hash Cond: ((r.route_acception_status = ras.id) AND (COALESCE(r.general_route_acception, false) = ras.accepted))
  • Join Filter: (r.opened IS TRUE)
70. 0.102 441.272 ↓ 3.4 110 1

Hash Left Join (cost=11,625.60..13,650.99 rows=32 width=1,791) (actual time=323.032..441.272 rows=110 loops=1)

  • Hash Cond: ((r.delivery_acception_status = das.id) AND (r.general_delivery_acception = das.accepted))
71. 0.171 441.159 ↓ 3.4 110 1

Hash Left Join (cost=11,624.38..13,649.51 rows=32 width=1,748) (actual time=323.011..441.159 rows=110 loops=1)

  • Hash Cond: ((r.id)::integer = (mrd.route_id)::integer)
72. 0.129 438.923 ↓ 3.4 110 1

Nested Loop Left Join (cost=11,622.23..13,647.24 rows=32 width=1,684) (actual time=320.937..438.923 rows=110 loops=1)

73. 0.258 437.144 ↓ 3.4 110 1

Nested Loop Left Join (cost=11,621.80..13,317.32 rows=32 width=1,612) (actual time=320.881..437.144 rows=110 loops=1)

  • Join Filter: (NOT mr.is_transit_route)
74. 0.133 316.546 ↓ 3.4 110 1

Hash Left Join (cost=11,611.29..13,306.18 rows=32 width=1,452) (actual time=314.910..316.546 rows=110 loops=1)

  • Hash Cond: ((mr.route_id)::integer = (lm.route_id)::integer)
  • Join Filter: (NOT mr.is_transit_route)
75. 0.109 311.134 ↓ 3.4 110 1

Hash Left Join (cost=11,611.10..13,305.82 rows=32 width=1,424) (actual time=309.615..311.134 rows=110 loops=1)

  • Hash Cond: (cch.comment_template = cct.id)
76. 0.128 311.003 ↓ 3.4 110 1

Nested Loop Left Join (cost=11,609.18..13,303.57 rows=32 width=1,402) (actual time=309.576..311.003 rows=110 loops=1)

77. 0.105 310.435 ↓ 3.4 110 1

Nested Loop Left Join (cost=11,608.75..13,288.93 rows=32 width=1,385) (actual time=309.547..310.435 rows=110 loops=1)

78. 0.111 310.330 ↓ 3.4 110 1

Nested Loop Left Join (cost=11,608.60..13,283.33 rows=32 width=1,384) (actual time=309.543..310.330 rows=110 loops=1)

79. 0.101 310.219 ↓ 3.4 110 1

Nested Loop Left Join (cost=11,608.33..13,273.57 rows=32 width=1,346) (actual time=309.539..310.219 rows=110 loops=1)

80. 0.099 310.118 ↓ 3.4 110 1

Nested Loop Left Join (cost=11,608.05..13,221.50 rows=32 width=1,300) (actual time=309.534..310.118 rows=110 loops=1)

81. 0.213 310.019 ↓ 3.4 110 1

Nested Loop Left Join (cost=11,607.90..13,213.32 rows=32 width=1,300) (actual time=309.530..310.019 rows=110 loops=1)

82. 11.432 309.806 ↓ 3.4 110 1

Hash Right Join (cost=11,607.75..13,207.70 rows=32 width=1,300) (actual time=309.524..309.806 rows=110 loops=1)

  • Hash Cond: (cch.route_id = (r.id)::integer)
83. 8.149 8.149 ↑ 1.0 46,759 1

Seq Scan on car_comments_history cch (cost=0.00..1,424.59 rows=46,759 width=72) (actual time=0.008..8.149 rows=46,759 loops=1)

84. 0.346 290.225 ↓ 3.4 110 1

Hash (cost=11,607.35..11,607.35 rows=32 width=1,232) (actual time=290.225..290.225 rows=110 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 83kB
85. 0.123 289.879 ↓ 3.4 110 1

Nested Loop Left Join (cost=10,516.94..11,607.35 rows=32 width=1,232) (actual time=278.095..289.879 rows=110 loops=1)

86. 0.109 289.536 ↓ 3.4 110 1

Nested Loop Left Join (cost=10,516.66..11,555.28 rows=32 width=1,186) (actual time=278.059..289.536 rows=110 loops=1)

87. 0.121 289.427 ↓ 3.4 110 1

Nested Loop Left Join (cost=10,516.52..11,549.84 rows=32 width=1,164) (actual time=278.054..289.427 rows=110 loops=1)

88. 0.126 288.426 ↓ 3.4 110 1

Nested Loop Left Join (cost=10,516.08..11,532.49 rows=32 width=1,140) (actual time=278.022..288.426 rows=110 loops=1)

89. 0.096 287.750 ↓ 3.4 110 1

Hash Left Join (cost=10,515.80..11,522.39 rows=32 width=1,140) (actual time=277.996..287.750 rows=110 loops=1)

  • Hash Cond: ((COALESCE(r.opened, false) = frs.is_opened) AND (r.auto_open = frs.is_auto_open) AND ((haor.auto_open_route_id IS NOT NULL) = frs.has_future_route) AND (COALESCE(ref.status_future_route_opened, '-1'::integer) = frs.open_type))
90. 0.268 287.637 ↓ 3.4 110 1

Hash Left Join (cost=10,514.56..11,520.50 rows=32 width=1,147) (actual time=277.966..287.637 rows=110 loops=1)

  • Hash Cond: ((COALESCE(r.opened, false) = ors.is_opened) AND (ref.auto_open_rc = ors.is_auto_open) AND (COALESCE((r.route_status = 1), false) = ors.is_finished) AND (COALESCE((r.open_type)::integer, '-1'::integer) = ors.open_type))
91. 0.127 287.350 ↓ 3.4 110 1

Nested Loop Left Join (cost=10,513.17..11,517.82 rows=32 width=1,150) (actual time=277.856..287.350 rows=110 loops=1)

92. 0.484 287.113 ↓ 3.4 110 1

Nested Loop Left Join (cost=10,512.90..11,508.14 rows=32 width=1,146) (actual time=277.846..287.113 rows=110 loops=1)

93. 0.105 286.299 ↓ 3.4 110 1

Nested Loop Left Join (cost=10,504.45..10,986.22 rows=32 width=1,134) (actual time=277.779..286.299 rows=110 loops=1)

94. 0.081 286.194 ↓ 3.4 110 1

Hash Left Join (cost=10,504.17..10,976.30 rows=32 width=1,112) (actual time=277.774..286.194 rows=110 loops=1)

  • Hash Cond: ((vr.route_cause_id)::integer = (de_erc.id)::integer)
95. 0.102 286.105 ↓ 3.4 110 1

Hash Left Join (cost=10,503.10..10,975.10 rows=32 width=1,075) (actual time=277.755..286.105 rows=110 loops=1)

  • Hash Cond: ((vr.delivery_cause_id)::integer = (de_ercd.id)::integer)
96. 0.116 285.994 ↓ 3.4 110 1

Nested Loop Left Join (cost=10,502.01..10,973.88 rows=32 width=1,047) (actual time=277.719..285.994 rows=110 loops=1)

97. 0.131 284.998 ↓ 3.4 110 1

Nested Loop Left Join (cost=10,501.57..10,956.88 rows=32 width=1,036) (actual time=277.686..284.998 rows=110 loops=1)

98. 0.076 284.097 ↓ 3.4 110 1

Nested Loop Left Join (cost=10,501.14..10,939.37 rows=32 width=1,015) (actual time=277.647..284.097 rows=110 loops=1)

99. 0.049 282.591 ↓ 3.4 110 1

Nested Loop (cost=10,500.02..10,898.93 rows=32 width=951) (actual time=277.575..282.591 rows=110 loops=1)

100. 0.004 0.018 ↑ 1.0 1 1

Aggregate (cost=1.36..1.37 rows=1 width=0) (actual time=0.017..0.018 rows=1 loops=1)

101. 0.014 0.014 ↑ 1.0 36 1

Seq Scan on vehicles_statuses_types vst (cost=0.00..1.36 rows=36 width=0) (actual time=0.012..0.014 rows=36 loops=1)

102. 0.094 282.524 ↓ 3.4 110 1

Nested Loop (cost=10,498.66..10,897.23 rows=32 width=951) (actual time=277.555..282.524 rows=110 loops=1)

  • Join Filter: ((c.id)::integer = (cs.car_id)::integer)
103. 1.800 281.550 ↓ 3.4 110 1

Hash Right Join (cost=10,498.37..10,886.89 rows=32 width=951) (actual time=277.520..281.550 rows=110 loops=1)

  • Hash Cond: ((ts.trailer_id)::integer = (c.trailer_id)::integer)
104. 2.394 2.394 ↑ 1.0 19,960 1

Seq Scan on trailers_statuses ts (cost=0.00..313.60 rows=19,960 width=8) (actual time=0.015..2.394 rows=19,960 loops=1)

105. 0.302 277.356 ↓ 3.4 110 1

Hash (cost=10,497.97..10,497.97 rows=32 width=951) (actual time=277.356..277.356 rows=110 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 71kB
106. 6.280 277.054 ↓ 3.4 110 1

Hash Join (cost=3,039.04..10,497.97 rows=32 width=951) (actual time=75.554..277.054 rows=110 loops=1)

  • Hash Cond: ((c.id)::integer = (r.car_id)::integer)
107. 14.854 244.749 ↑ 1.0 60,468 1

Hash Left Join (cost=2,639.90..9,871.75 rows=60,468 width=264) (actual time=48.516..244.749 rows=60,468 loops=1)

  • Hash Cond: ((c.gps_device_id)::integer = (rusc.gps_device_id)::integer)
108. 19.090 223.936 ↑ 1.0 60,468 1

Hash Left Join (cost=2,215.98..9,171.40 rows=60,468 width=258) (actual time=42.501..223.936 rows=60,468 loops=1)

  • Hash Cond: ((c.warm_type_id)::integer = (wt_c.id)::integer)
109. 13.117 204.834 ↑ 1.0 60,468 1

Hash Left Join (cost=2,214.85..8,338.83 rows=60,468 width=251) (actual time=42.466..204.834 rows=60,468 loops=1)

  • Hash Cond: (c.temperature_sensor_model_id = tsm_c.id)
110. 11.766 191.696 ↑ 1.0 60,468 1

Hash Left Join (cost=2,213.33..8,094.55 rows=60,468 width=236) (actual time=42.428..191.696 rows=60,468 loops=1)

  • Hash Cond: ((c.receiver_autocade_id)::integer = (tr_a.id)::integer)
111. 10.875 179.759 ↑ 1.0 60,468 1

Hash Left Join (cost=2,197.42..7,851.82 rows=60,468 width=231) (actual time=42.241..179.759 rows=60,468 loops=1)

  • Hash Cond: ((c.receiver_owner_id)::integer = (tr_cto.id)::integer)
112. 11.768 167.627 ↑ 1.0 60,468 1

Hash Left Join (cost=2,121.84..7,548.64 rows=60,468 width=193) (actual time=40.961..167.627 rows=60,468 loops=1)

  • Hash Cond: ((c.autocades_id)::integer = (a.id)::integer)
113. 14.264 155.266 ↑ 1.0 60,468 1

Hash Left Join (cost=2,090.61..7,244.57 rows=60,468 width=188) (actual time=40.356..155.266 rows=60,468 loops=1)

  • Hash Cond: ((c.car_trailer_brand_id)::integer = ctb_c.id)
114. 14.204 140.857 ↑ 1.0 60,468 1

Hash Left Join (cost=2,082.84..6,782.58 rows=60,468 width=164) (actual time=40.199..140.857 rows=60,468 loops=1)

  • Hash Cond: ((c.id)::integer = (cg.car_id)::integer)
115. 16.691 114.318 ↑ 1.0 60,468 1

Hash Left Join (cost=1,288.44..5,673.04 rows=60,468 width=140) (actual time=27.775..114.318 rows=60,468 loops=1)

  • Hash Cond: ((c.type_delivery)::integer = dtt.id)
116. 18.710 97.610 ↑ 1.0 60,468 1

Hash Left Join (cost=1,287.28..4,840.44 rows=60,468 width=127) (actual time=27.746..97.610 rows=60,468 loops=1)

  • Hash Cond: ((c.phone_id)::integer = (ph.id)::integer)
117. 51.283 51.283 ↑ 1.0 60,468 1

Seq Scan on cars c (cost=0.00..3,292.99 rows=60,468 width=123) (actual time=0.026..51.283 rows=60,468 loops=1)

  • Filter: (NOT archive)
  • Rows Removed by Filter: 28,131
118. 2.924 27.617 ↑ 1.0 11,244 1

Hash (cost=1,146.73..1,146.73 rows=11,244 width=16) (actual time=27.617..27.617 rows=11,244 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 647kB
119. 9.177 24.693 ↑ 1.0 11,244 1

Hash Right Join (cost=362.99..1,146.73 rows=11,244 width=16) (actual time=7.958..24.693 rows=11,244 loops=1)

  • Hash Cond: ((sim.id)::integer = (ph.simcard_id)::integer)
120. 7.702 7.702 ↑ 1.0 31,056 1

Seq Scan on simcards sim (cost=0.00..570.56 rows=31,056 width=16) (actual time=0.023..7.702 rows=31,056 loops=1)

121. 3.117 7.814 ↑ 1.0 11,244 1

Hash (cost=222.44..222.44 rows=11,244 width=12) (actual time=7.814..7.814 rows=11,244 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 562kB
122. 4.697 4.697 ↑ 1.0 11,244 1

Seq Scan on phones ph (cost=0.00..222.44 rows=11,244 width=12) (actual time=0.019..4.697 rows=11,244 loops=1)

123. 0.010 0.017 ↑ 1.0 7 1

Hash (cost=1.07..1.07 rows=7 width=21) (actual time=0.017..0.017 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
124. 0.007 0.007 ↑ 1.0 7 1

Seq Scan on deliveries_types dtt (cost=0.00..1.07 rows=7 width=21) (actual time=0.006..0.007 rows=7 loops=1)

125. 3.707 12.335 ↑ 1.0 12,951 1

Hash (cost=632.51..632.51 rows=12,951 width=28) (actual time=12.335..12.335 rows=12,951 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 855kB
126. 8.628 8.628 ↑ 1.0 12,951 1

Seq Scan on cars_gps cg (cost=0.00..632.51 rows=12,951 width=28) (actual time=0.016..8.628 rows=12,951 loops=1)

127. 0.060 0.145 ↑ 1.0 168 1

Hash (cost=5.68..5.68 rows=168 width=32) (actual time=0.145..0.145 rows=168 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
128. 0.085 0.085 ↑ 1.0 168 1

Seq Scan on cars_trailers_brands ctb_c (cost=0.00..5.68 rows=168 width=32) (actual time=0.010..0.085 rows=168 loops=1)

129. 0.112 0.593 ↑ 1.0 396 1

Hash (cost=26.27..26.27 rows=396 width=13) (actual time=0.593..0.593 rows=396 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
130. 0.136 0.481 ↑ 1.0 396 1

Hash Left Join (cost=12.55..26.27 rows=396 width=13) (actual time=0.233..0.481 rows=396 loops=1)

  • Hash Cond: ((a.autocades_managers_id)::integer = (am.id)::integer)
131. 0.133 0.133 ↑ 1.0 396 1

Seq Scan on autocades a (cost=0.00..10.96 rows=396 width=13) (actual time=0.008..0.133 rows=396 loops=1)

132. 0.054 0.212 ↑ 1.0 291 1

Hash (cost=8.91..8.91 rows=291 width=8) (actual time=0.212..0.212 rows=291 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
133. 0.158 0.158 ↑ 1.0 291 1

Seq Scan on autocades_managers am (cost=0.00..8.91 rows=291 width=8) (actual time=0.006..0.158 rows=291 loops=1)

134. 0.406 1.257 ↑ 1.0 1,226 1

Hash (cost=60.26..60.26 rows=1,226 width=46) (actual time=1.257..1.257 rows=1,226 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 113kB
135. 0.851 0.851 ↑ 1.0 1,226 1

Seq Scan on cars_trailers_owners tr_cto (cost=0.00..60.26 rows=1,226 width=46) (actual time=0.016..0.851 rows=1,226 loops=1)

136. 0.093 0.171 ↑ 1.0 396 1

Hash (cost=10.96..10.96 rows=396 width=9) (actual time=0.171..0.171 rows=396 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
137. 0.078 0.078 ↑ 1.0 396 1

Seq Scan on autocades tr_a (cost=0.00..10.96 rows=396 width=9) (actual time=0.003..0.078 rows=396 loops=1)

138. 0.012 0.021 ↑ 1.0 23 1

Hash (cost=1.23..1.23 rows=23 width=23) (actual time=0.021..0.021 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
139. 0.009 0.009 ↑ 1.0 23 1

Seq Scan on temperature_sensor_models tsm_c (cost=0.00..1.23 rows=23 width=23) (actual time=0.006..0.009 rows=23 loops=1)

140. 0.006 0.012 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=15) (actual time=0.012..0.012 rows=6 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
141. 0.006 0.006 ↑ 1.0 6 1

Seq Scan on warm_types wt_c (cost=0.00..1.06 rows=6 width=15) (actual time=0.005..0.006 rows=6 loops=1)

142. 1.855 5.959 ↑ 1.0 8,085 1

Hash (cost=322.85..322.85 rows=8,085 width=14) (actual time=5.959..5.959 rows=8,085 loops=1)

  • Buckets: 8,192 Batches: 1 Memory Usage: 423kB
143. 4.104 4.104 ↑ 1.0 8,085 1

Seq Scan on refrigeration_unit_status rusc (cost=0.00..322.85 rows=8,085 width=14) (actual time=0.014..4.104 rows=8,085 loops=1)

144. 0.398 26.025 ↓ 2.4 111 1

Hash (cost=398.56..398.56 rows=47 width=687) (actual time=26.025..26.025 rows=111 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 56kB
145. 0.445 25.627 ↓ 2.4 111 1

Nested Loop (cost=0.43..398.56 rows=47 width=687) (actual time=3.787..25.627 rows=111 loops=1)

146. 24.516 24.516 ↓ 2.4 111 1

CTE Scan on meta_routes mr (cost=0.00..0.94 rows=47 width=102) (actual time=3.779..24.516 rows=111 loops=1)

147. 0.666 0.666 ↑ 1.0 1 111

Index Scan using routes_pkey on routes r (cost=0.43..8.45 rows=1 width=585) (actual time=0.006..0.006 rows=1 loops=111)

  • Index Cond: ((id)::integer = (mr.route_id)::integer)
148. 0.880 0.880 ↑ 1.0 1 110

Index Scan using cars_statuses_pkey on cars_statuses cs (cost=0.29..0.31 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=110)

  • Index Cond: ((car_id)::integer = (r.car_id)::integer)
149. 0.034 1.430 ↑ 1.0 1 110

Nested Loop Left Join (cost=1.12..1.25 rows=1 width=72) (actual time=0.012..0.013 rows=1 loops=110)

150. 0.096 1.210 ↑ 1.0 1 110

Nested Loop Left Join (cost=0.83..0.94 rows=1 width=66) (actual time=0.010..0.011 rows=1 loops=110)

151. 0.000 0.990 ↑ 1.0 1 110

Nested Loop Left Join (cost=0.70..0.78 rows=1 width=59) (actual time=0.009..0.009 rows=1 loops=110)

152. 0.158 0.990 ↑ 1.0 1 110

Nested Loop Left Join (cost=0.56..0.61 rows=1 width=44) (actual time=0.008..0.009 rows=1 loops=110)

153. 0.770 0.770 ↑ 1.0 1 110

Index Scan using trailers_pkey on trailers t (cost=0.42..0.44 rows=1 width=24) (actual time=0.007..0.007 rows=1 loops=110)

  • Index Cond: ((id)::integer = (r.trailer_id)::integer)
154. 0.062 0.062 ↑ 1.0 1 62

Index Scan using pk_cars_trailers_brands on cars_trailers_brands ctb_t (cost=0.14..0.16 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=62)

  • Index Cond: (id = (t.car_trailer_brand_id)::integer)
155. 0.000 0.000 ↓ 0.0 0 62

Index Scan using temperature_sensor_models_pkey on temperature_sensor_models tsm_t (cost=0.14..0.16 rows=1 width=23) (actual time=0.000..0.000 rows=0 loops=62)

  • Index Cond: (t.temperature_sensor_model_id = id)
156. 0.124 0.124 ↑ 1.0 1 62

Index Scan using warm_types_pkey on warm_types wt_t (cost=0.13..0.15 rows=1 width=15) (actual time=0.001..0.002 rows=1 loops=62)

  • Index Cond: ((id)::integer = (t.warm_type_id)::integer)
157. 0.186 0.186 ↓ 0.0 0 62

Index Scan using refrigeration_unit_status_pkey on refrigeration_unit_status rust (cost=0.28..0.30 rows=1 width=14) (actual time=0.003..0.003 rows=0 loops=62)

  • Index Cond: ((gps_device_id)::integer = (t.gps_device_id)::integer)
158. 0.770 0.770 ↑ 1.0 1 110

Index Scan using routes_extra_fields_idx on routes_extra_fields ref (cost=0.43..0.54 rows=1 width=25) (actual time=0.007..0.007 rows=1 loops=110)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
159. 0.880 0.880 ↑ 1.0 1 110

Index Scan using route_pkey on route vr (cost=0.43..0.52 rows=1 width=15) (actual time=0.008..0.008 rows=1 loops=110)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
160. 0.004 0.009 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=32) (actual time=0.009..0.009 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
161. 0.005 0.005 ↑ 1.0 4 1

Seq Scan on event_delivery_cause de_ercd (cost=0.00..1.04 rows=4 width=32) (actual time=0.004..0.005 rows=4 loops=1)

162. 0.004 0.008 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=41) (actual time=0.008..0.008 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
163. 0.004 0.004 ↑ 1.0 3 1

Seq Scan on event_route_cause de_erc (cost=0.00..1.03 rows=3 width=41) (actual time=0.003..0.004 rows=3 loops=1)

164. 0.000 0.000 ↓ 0.0 0 110

Index Scan using settle_pkey on settles s (cost=0.28..0.30 rows=1 width=26) (actual time=0.000..0.000 rows=0 loops=110)

  • Index Cond: ((id)::integer = (r.settle_id)::integer)
165. 0.000 0.330 ↑ 1.0 1 110

Index Scan using waypoints_pkey on waypoints w (cost=8.45..16.30 rows=1 width=20) (actual time=0.002..0.003 rows=1 loops=110)

  • Index Cond: ((id)::integer = (CASE WHEN mr.is_transit_route THEN s.waypoint_id ELSE (SubPlan 24) END)::integer)
166.          

SubPlan (for Index Scan)

167. 0.110 0.330 ↑ 1.0 1 110

Limit (cost=0.14..8.16 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=110)

168. 0.220 0.220 ↑ 1.0 1 110

Index Scan using distribution_center_pkey on distribution_centers (cost=0.14..8.16 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=110)

  • Index Cond: ((id)::integer = (mr.distribution_center_id)::integer)
169. 0.110 0.330 ↑ 1.0 1 110

Limit (cost=0.14..8.16 rows=1 width=4) (actual time=0.002..0.003 rows=1 loops=110)

170. 0.220 0.220 ↑ 1.0 1 110

Index Scan using distribution_center_pkey on distribution_centers (cost=0.14..8.16 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=110)

  • Index Cond: ((id)::integer = (mr.distribution_center_id)::integer)
171. 0.110 0.110 ↓ 0.0 0 110

Index Scan using history_auto_open_routes_idx on history_auto_open_routes haor (cost=0.27..0.29 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=110)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
  • Filter: (auto_open_type_id = $38)
172. 0.005 0.019 ↑ 1.0 13 1

Hash (cost=1.13..1.13 rows=13 width=7) (actual time=0.019..0.019 rows=13 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
173. 0.014 0.014 ↑ 1.0 13 1

Seq Scan on open_route_statuses ors (cost=0.00..1.13 rows=13 width=7) (actual time=0.011..0.014 rows=13 loops=1)

174. 0.005 0.017 ↑ 1.0 8 1

Hash (cost=1.08..1.08 rows=8 width=7) (actual time=0.017..0.017 rows=8 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
175. 0.012 0.012 ↑ 1.0 8 1

Seq Scan on future_route_statuses frs (cost=0.00..1.08 rows=8 width=7) (actual time=0.011..0.012 rows=8 loops=1)

176. 0.550 0.550 ↑ 1.0 1 110

Index Scan using arm_de_tablet_car_id_idx on tablet de_t (cost=0.28..0.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=110)

  • Index Cond: ((car_id)::integer = (c.id)::integer)
177. 0.880 0.880 ↑ 1.0 1 110

Index Scan using direct_routes_info_idx1 on direct_routes_info dri (cost=0.43..0.53 rows=1 width=28) (actual time=0.008..0.008 rows=1 loops=110)

  • Index Cond: ((r.id)::integer = route_id)
178. 0.000 0.000 ↓ 0.0 0 110

Index Scan using distribution_center_pkey on distribution_centers dc (cost=0.14..0.16 rows=1 width=30) (actual time=0.000..0.000 rows=0 loops=110)

  • Index Cond: ((id)::integer = (r.responsible_dc_id)::integer)
179. 0.220 0.220 ↑ 1.0 1 110

Index Scan using users2_pkey on users u (cost=0.28..1.62 rows=1 width=54) (actual time=0.002..0.002 rows=1 loops=110)

  • Index Cond: (user_oid = (am.user_oid)::oid)
180. 0.000 0.000 ↓ 0.0 0 110

Index Scan using autocades_pkey on autocades prikom_ak (cost=0.15..0.17 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=110)

  • Index Cond: ((id)::integer = (c.receiver_autocade_id)::integer)
181. 0.000 0.000 ↓ 0.0 0 110

Index Scan using autocades_managers_pkey on autocades_managers am_prikom (cost=0.15..0.25 rows=1 width=8) (actual time=0.000..0.000 rows=0 loops=110)

  • Index Cond: ((id)::integer = (prikom_ak.autocades_managers_id)::integer)
182. 0.000 0.000 ↓ 0.0 0 110

Index Scan using users2_pkey on users am_prikom_u (cost=0.28..1.62 rows=1 width=54) (actual time=0.000..0.000 rows=0 loops=110)

  • Index Cond: (user_oid = (am_prikom.user_oid)::oid)
183. 0.000 0.000 ↓ 0.0 0 110

Index Scan using cars_trailers_owners_pkey on cars_trailers_owners r_cto (cost=0.28..0.30 rows=1 width=46) (actual time=0.000..0.000 rows=0 loops=110)

  • Index Cond: ((id)::integer = ref.open_receiver_owner_id)
184. 0.000 0.000 ↓ 0.0 0 110

Index Scan using autocades_pkey on autocades r_au (cost=0.15..0.17 rows=1 width=9) (actual time=0.000..0.000 rows=0 loops=110)

  • Index Cond: ((id)::integer = ref.open_receiver_autocade_id)
185. 0.440 0.440 ↓ 0.0 0 110

Index Scan using routes_imp_extra_fields_route_id_key on routes_imp_extra_fields rief (cost=0.42..0.45 rows=1 width=21) (actual time=0.004..0.004 rows=0 loops=110)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
186. 0.010 0.022 ↑ 1.0 41 1

Hash (cost=1.41..1.41 rows=41 width=30) (actual time=0.022..0.022 rows=41 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
187. 0.012 0.012 ↑ 1.0 41 1

Seq Scan on car_comment_templates cct (cost=0.00..1.41 rows=41 width=30) (actual time=0.008..0.012 rows=41 loops=1)

188. 0.014 5.279 ↓ 11.0 66 1

Hash (cost=0.12..0.12 rows=6 width=36) (actual time=5.279..5.279 rows=66 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
189. 5.265 5.265 ↓ 11.0 66 1

CTE Scan on last_market lm (cost=0.00..0.12 rows=6 width=36) (actual time=5.234..5.265 rows=66 loops=1)

190. 119.130 120.340 ↑ 1.0 1 110

Function Scan on necessary_passes np (cost=10.50..10.51 rows=1 width=160) (actual time=1.094..1.094 rows=1 loops=110)

191.          

SubPlan (for Function Scan)

192. 0.330 1.210 ↑ 1.0 1 110

Aggregate (cost=10.24..10.25 rows=1 width=4) (actual time=0.011..0.011 rows=1 loops=110)

193. 0.880 0.880 ↑ 2.0 1 110

Index Only Scan using routes_drivers_idx on routes_drivers rd_1 (cost=0.43..10.23 rows=2 width=4) (actual time=0.008..0.008 rows=1 loops=110)

  • Index Cond: (route_id = (r.id)::integer)
  • Heap Fetches: 106
194. 0.110 1.650 ↑ 1.0 1 110

Subquery Scan on dc_info (cost=0.43..10.30 rows=1 width=76) (actual time=0.014..0.015 rows=1 loops=110)

  • Filter: ((dc_info.route_id)::integer = (r.id)::integer)
195. 0.330 1.540 ↑ 2.0 1 110

GroupAggregate (cost=0.43..10.27 rows=2 width=18) (actual time=0.014..0.014 rows=1 loops=110)

  • Group Key: rdtd.route_id
196. 1.210 1.210 ↑ 2.0 1 110

Index Scan using routes_dc_times_detail_idx on routes_dc_times_detail rdtd (cost=0.43..10.22 rows=2 width=18) (actual time=0.010..0.011 rows=1 loops=110)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
197. 0.025 2.065 ↓ 1.6 107 1

Hash (cost=1.32..1.32 rows=66 width=68) (actual time=2.065..2.065 rows=107 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
198. 2.040 2.040 ↓ 1.6 107 1

CTE Scan on meta_routes_drivers mrd (cost=0.00..1.32 rows=66 width=68) (actual time=1.979..2.040 rows=107 loops=1)

199. 0.004 0.011 ↑ 1.0 9 1

Hash (cost=1.09..1.09 rows=9 width=52) (actual time=0.011..0.011 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
200. 0.007 0.007 ↑ 1.0 9 1

Seq Scan on delivery_acception_statuses das (cost=0.00..1.09 rows=9 width=52) (actual time=0.006..0.007 rows=9 loops=1)

201. 0.002 0.008 ↑ 1.1 7 1

Hash (cost=1.08..1.08 rows=8 width=49) (actual time=0.008..0.008 rows=7 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
202. 0.006 0.006 ↑ 1.0 8 1

Seq Scan on route_acception_statuses ras (cost=0.00..1.08 rows=8 width=49) (actual time=0.004..0.006 rows=8 loops=1)

203. 0.330 0.330 ↓ 0.0 0 110

Index Scan using route_id on escalation_answers ea (cost=0.42..0.44 rows=1 width=75) (actual time=0.003..0.003 rows=0 loops=110)

  • Index Cond: (route_id = (r.id)::integer)
204. 0.330 0.330 ↓ 0.0 0 110

Index Scan using escalation_answers_route_pkey on escalation_answers_route ear (cost=0.42..0.44 rows=1 width=67) (actual time=0.003..0.003 rows=0 loops=110)

  • Index Cond: (route_id = (r.id)::integer)
205. 0.000 0.000 ↓ 0.0 0 110

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=110)

  • One-Time Filter: false
206. 0.220 0.220 ↓ 0.0 0 110

Index Scan using pg_authid_oid_index on pg_authid (cost=0.28..0.30 rows=1 width=68) (actual time=0.002..0.002 rows=0 loops=110)

  • Index Cond: (oid = r.user_oid)
  • Filter: rolcanlogin
207. 0.003 0.014 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=17) (actual time=0.014..0.014 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
208. 0.011 0.011 ↑ 1.0 3 1

Seq Scan on routes_schemes rs (cost=0.00..1.03 rows=3 width=17) (actual time=0.011..0.011 rows=3 loops=1)

209.          

SubPlan (for Hash Left Join)

210. 0.110 1.100 ↑ 1.0 1 110

Limit (cost=0.43..7.48 rows=1 width=12) (actual time=0.010..0.010 rows=1 loops=110)

211. 0.990 0.990 ↑ 5.0 1 110

Index Scan Backward using routes_points_manual_route_id_order_key on routes_points_manual rpm (cost=0.43..35.65 rows=5 width=12) (actual time=0.009..0.009 rows=1 loops=110)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
  • Filter: ((object_var)::text = 'market'::text)
  • Rows Removed by Filter: 1
212. 0.110 1.320 ↓ 0.0 0 110

Nested Loop (cost=0.87..31.95 rows=1 width=0) (actual time=0.012..0.012 rows=0 loops=110)

  • Join Filter: ((COALESCE(rpa_1.open_route_point, false) = COALESCE(r_3.opened, false)) AND CASE WHEN (r_3.routes_type_transits IS NOT NULL) THEN ((rpa_1.type_id)::integer = ANY ('{3,4}'::integer[])) WHEN (r_3.routes_type = 0) THEN ((rpa_1.type_id)::integer = ANY ('{1,4}'::integer[])) WHEN (r_3.routes_type = 1) THEN ((rpa_1.type_id)::integer = ANY ('{2,4}'::integer[])) ELSE false END)
213. 0.440 0.440 ↑ 1.0 1 110

Index Scan using routes_pkey on routes r_3 (cost=0.43..8.45 rows=1 width=9) (actual time=0.004..0.004 rows=1 loops=110)

  • Index Cond: ((id)::integer = (r.id)::integer)
214. 0.770 0.770 ↓ 0.0 0 110

Index Scan using routes_points_all_idx3 on routes_points_all rpa_1 (cost=0.43..23.42 rows=3 width=9) (actual time=0.007..0.007 rows=0 loops=110)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
  • Filter: (temperature_control_status > 0)
  • Rows Removed by Filter: 5
215. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=647,322.32..1,341,533.13 rows=676,563 width=4) (never executed)

  • Hash Cond: (((rpa_2.route_id)::integer = (r_4.id)::integer) AND (COALESCE(rpa_2.open_route_point, false) = COALESCE(r_4.opened, false)))
  • Join Filter: CASE WHEN (r_4.routes_type_transits IS NOT NULL) THEN ((rpa_2.type_id)::integer = ANY ('{3,4}'::integer[])) WHEN (r_4.routes_type = 0) THEN ((rpa_2.type_id)::integer = ANY ('{1,4}'::integer[])) WHEN (r_4.routes_type = 1) THEN ((rpa_2.type_id)::integer = ANY ('{2,4}'::integer[])) ELSE false END
216. 0.000 0.000 ↓ 0.0 0

Seq Scan on routes_points_all rpa_2 (cost=0.00..551,728.41 rows=2,706,253 width=9) (never executed)

  • Filter: (temperature_control_status > 0)
217. 0.000 0.000 ↓ 0.0 0

Hash (cost=519,127.93..519,127.93 rows=6,447,493 width=9) (never executed)

218. 0.000 0.000 ↓ 0.0 0

Seq Scan on routes r_4 (cost=0.00..519,127.93 rows=6,447,493 width=9) (never executed)

219. 0.000 0.770 ↓ 0.0 0 110

Nested Loop Left Join (cost=0.56..39.18 rows=200 width=642) (actual time=0.007..0.007 rows=0 loops=110)

220. 0.110 0.770 ↓ 0.0 0 110

Nested Loop (cost=0.28..21.56 rows=2 width=4) (actual time=0.007..0.007 rows=0 loops=110)

  • Join Filter: (s_1.status = ss.id)
221. 0.660 0.660 ↓ 0.0 0 110

Index Scan using fki_sessions_delivery_id_fk on sessions s_1 (cost=0.28..20.00 rows=4 width=8) (actual time=0.006..0.006 rows=0 loops=110)

  • Index Cond: (delivery_id = (mr.delivery_id)::integer)
222. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.00..1.16 rows=7 width=4) (never executed)

223. 0.000 0.000 ↓ 0.0 0

Seq Scan on session_statuses ss (cost=0.00..1.12 rows=7 width=4) (never executed)

  • Filter: is_active_session
224. 0.000 0.000 ↓ 0.0 0

Index Scan using session_id_fk_idx on sessions_params sp (cost=0.28..8.30 rows=1 width=646) (never executed)

  • Index Cond: (session_id = s_1.id)
225. 4.166 4.180 ↑ 1.0 1 110

Function Scan on geography_distance_between_km sq_distance (cost=1.36..2.13 rows=1 width=32) (actual time=0.038..0.038 rows=1 loops=110)

226.          

Initplan (for Function Scan)

227. 0.014 0.014 ↑ 1.0 1 1

Seq Scan on gps_process_data_settings (cost=0.00..1.11 rows=1 width=4) (actual time=0.013..0.014 rows=1 loops=1)

  • Filter: ((object_var)::text = 'distribution_center'::text)
  • Rows Removed by Filter: 8
228. 0.110 2.530 ↑ 1.0 1 110

Limit (cost=32.11..32.11 rows=1 width=16) (actual time=0.023..0.023 rows=1 loops=110)

229. 0.550 2.420 ↑ 2.0 1 110

Sort (cost=32.11..32.11 rows=2 width=16) (actual time=0.022..0.022 rows=1 loops=110)

  • Sort Key: rpa_3.type_id, rpa_3.order_num
  • Sort Method: top-N heapsort Memory: 25kB
230. 0.770 1.870 ↓ 2.5 5 110

Nested Loop (cost=0.87..32.10 rows=2 width=16) (actual time=0.011..0.017 rows=5 loops=110)

  • Join Filter: ((COALESCE(rpa_3.open_route_point, false) = COALESCE(r_5.opened, false)) AND CASE WHEN (r_5.routes_type_transits IS NOT NULL) THEN ((rpa_3.type_id)::integer = ANY ('{3,4}'::integer[])) WHEN (r_5.routes_type = 0) THEN ((rpa_3.type_id)::integer = ANY ('{1,4}'::integer[])) WHEN (r_5.routes_type = 1) THEN ((rpa_3.type_id)::integer = ANY ('{2,4}'::integer[])) ELSE false END)
231. 0.550 0.550 ↑ 1.0 1 110

Index Scan using routes_pkey on routes r_5 (cost=0.43..8.45 rows=1 width=9) (actual time=0.005..0.005 rows=1 loops=110)

  • Index Cond: ((id)::integer = (r.id)::integer)
232. 0.550 0.550 ↑ 2.0 5 110

Index Scan using routes_points_all_idx3 on routes_points_all rpa_3 (cost=0.43..23.40 rows=10 width=21) (actual time=0.003..0.005 rows=5 loops=110)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
Planning time : 85.980 ms
Execution time : 568.194 ms