explain.depesz.com

PostgreSQL's explain analyze made readable

Result: gAmA

Settings
# exclusive inclusive rows x rows loops node
1. 0.126 1,559.697 ↑ 3.8 11 1

Sort (cost=29,392.49..29,392.60 rows=42 width=2,041) (actual time=1,559.693..1,559.697 rows=11 loops=1)

  • Sort Key: ((SubPlan 15)), r.""order"
  • Sort Method: quicksort Memory: 41kB
2.          

CTE meta_routes

3. 411.966 482.546 ↓ 6.8 846 1

Hash Left Join (cost=426.12..3,566.48 rows=125 width=68) (actual time=9.041..482.546 rows=846 loops=1)

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

Initplan (for Hash Left Join)

5. 0.000 2.753 ↑ 1.0 1 1

Aggregate (cost=422.66..422.67 rows=1 width=4) (actual time=2.753..2.753 rows=1 loops=1)

6.          

Initplan (for Aggregate)

7. 0.010 0.010 ↑ 1.0 1 1

Index Scan using deliveries_cars_tasks_pkey on deliveries_cars_tasks (cost=0.29..8.31 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((id)::integer = 94,533)
8. 0.007 0.007 ↑ 1.0 1 1

Index Scan using delivery_pkey on deliveries (cost=0.29..8.30 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: ((id)::integer = 227,786)
9. 2.737 2.737 ↑ 144.8 49 1

Seq Scan on deliveries deliveries_1 (cost=0.00..388.31 rows=7,094 width=4) (actual time=0.059..2.737 rows=49 loops=1)

  • Filter: CASE WHEN (NOT COALESCE((($3)::integer = 3), true)) THEN (((datetime)::timestamp without time zone = ($4)::timestamp without time zone) AND accepted) ELSE ((id)::integer = 227,786) END
  • Rows Removed by Filter: 14,140
10. 0.670 6.052 ↓ 6.8 846 1

Hash Left Join (cost=2.19..553.90 rows=125 width=67) (actual time=2.866..6.052 rows=846 loops=1)

  • Hash Cond: ((r_1.car_model_id)::integer = (cm.id)::integer)
11. 1.188 5.369 ↓ 6.8 846 1

Nested Loop (cost=0.71..550.77 rows=125 width=67) (actual time=2.844..5.369 rows=846 loops=1)

12. 2.858 2.858 ↓ 4.9 49 1

Index Scan using delivery_pkey on deliveries d (cost=0.29..47.44 rows=10 width=8) (actual time=2.769..2.858 rows=49 loops=1)

  • Index Cond: ((id)::integer = ANY ($5))
13. 1.323 1.323 ↓ 1.3 17 49

Index Scan using routes_delivery_id_for_transits_idx on routes r_1 (cost=0.43..50.20 rows=13 width=63) (actual time=0.003..0.027 rows=17 loops=49)

  • Index Cond: ((delivery_id)::integer = (d.id)::integer)
14. 0.005 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
15. 0.008 0.008 ↑ 1.0 21 1

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

16. 0.003 0.008 ↑ 1.0 12 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
17. 0.005 0.005 ↑ 1.0 12 1

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

18.          

SubPlan (for Hash Left Join)

19. 2.538 61.758 ↑ 1.0 1 846

Aggregate (cost=11.48..11.49 rows=1 width=26) (actual time=0.073..0.073 rows=1 loops=846)

20. 59.220 59.220 ↓ 0.0 0 846

Seq Scan on distribution_centers dc_1 (cost=0.00..11.45 rows=10 width=26) (actual time=0.034..0.070 rows=0 loops=846)

  • Filter: ((id)::integer = ANY ((string_to_array(r_1.directions_dc_id, ','::text))::integer[]))
  • Rows Removed by Filter: 115
21. 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
22. 0.009 0.009 ↓ 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.009..0.009 rows=0 loops=1)

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

CTE markets_points

24. 0.003 7.180 ↓ 0.0 0 1

WindowAgg (cost=1,201.37..1,201.75 rows=15 width=58) (actual time=7.180..7.180 rows=0 loops=1)

25. 0.025 7.177 ↓ 0.0 0 1

Sort (cost=1,201.37..1,201.41 rows=15 width=58) (actual time=7.177..7.177 rows=0 loops=1)

  • Sort Key: rpa.route_id, ((rpa.sched_begin_time IS NOT NULL)) DESC, rpa.order_num DESC
  • Sort Method: quicksort Memory: 25kB
26. 0.074 7.152 ↓ 0.0 0 1

Nested Loop (cost=3.68..1,201.08 rows=15 width=58) (actual time=7.152..7.152 rows=0 loops=1)

27. 0.670 3.694 ↓ 6.8 846 1

Nested Loop (cost=3.25..1,061.56 rows=125 width=13) (actual time=0.399..3.694 rows=846 loops=1)

28. 0.326 0.486 ↓ 6.8 846 1

HashAggregate (cost=2.81..4.06 rows=125 width=4) (actual time=0.360..0.486 rows=846 loops=1)

  • Group Key: (mmr.route_id)::integer
29. 0.160 0.160 ↓ 6.8 846 1

CTE Scan on meta_routes mmr (cost=0.00..2.50 rows=125 width=4) (actual time=0.012..0.160 rows=846 loops=1)

30. 2.538 2.538 ↑ 1.0 1 846

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

  • Index Cond: ((id)::integer = (mmr.route_id)::integer)
31. 3.384 3.384 ↓ 0.0 0 846

Index Scan using routes_points_all_idx2 on routes_points_all rpa (cost=0.43..1.11 rows=1 width=63) (actual time=0.004..0.004 rows=0 loops=846)

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

CTE unvisited_points

33. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=0.31..0.32 rows=1 width=20) (never executed)

  • Group Key: mp.route_id
34. 0.000 0.000 ↓ 0.0 0

CTE Scan on markets_points mp (cost=0.00..0.30 rows=1 width=20) (never executed)

  • Filter: (gps_arrival_time IS NULL)
35.          

CTE last_market

36. 0.006 7.221 ↓ 0.0 0 1

HashAggregate (cost=1.07..1.22 rows=15 width=55) (actual time=7.221..7.221 rows=0 loops=1)

  • Group Key: mp_1.route_id, first_value(mp_1.settle_name) OVER (?)
37. 0.002 7.215 ↓ 0.0 0 1

WindowAgg (cost=0.69..0.99 rows=15 width=55) (actual time=7.215..7.215 rows=0 loops=1)

38. 0.014 7.213 ↓ 0.0 0 1

Sort (cost=0.69..0.73 rows=15 width=55) (actual time=7.213..7.213 rows=0 loops=1)

  • Sort Key: mp_1.route_id, (CASE WHEN (mp_1.last_sched_cheked AND (up.has_main IS NOT TRUE) AND (up.has_sub IS NOT TRUE)) THEN mp_1.gps_arrival_time ELSE mp_1.arrival_time END) DESC NULLS LAST
  • Sort Method: quicksort Memory: 25kB
39. 0.002 7.199 ↓ 0.0 0 1

Hash Left Join (cost=0.03..0.40 rows=15 width=55) (actual time=7.199..7.199 rows=0 loops=1)

  • Hash Cond: ((mp_1.route_id)::integer = (up.route_id)::integer)
40. 7.197 7.197 ↓ 0.0 0 1

CTE Scan on markets_points mp_1 (cost=0.00..0.30 rows=15 width=53) (actual time=7.197..7.197 rows=0 loops=1)

41. 0.000 0.000 ↓ 0.0 0

Hash (cost=0.02..0.02 rows=1 width=6) (never executed)

42. 0.000 0.000 ↓ 0.0 0

CTE Scan on unvisited_points up (cost=0.00..0.02 rows=1 width=6) (never executed)

43.          

CTE meta_routes_drivers

44. 0.489 8.048 ↓ 4.9 836 1

HashAggregate (cost=4,010.21..4,011.92 rows=171 width=73) (actual time=7.939..8.048 rows=836 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))
45. 0.061 7.559 ↓ 4.9 836 1

Append (cost=641.50..4,008.93 rows=171 width=73) (actual time=0.950..7.559 rows=836 loops=1)

46. 0.767 1.723 ↓ 1.9 209 1

GroupAggregate (cost=641.50..644.50 rows=109 width=74) (actual time=0.949..1.723 rows=209 loops=1)

  • Group Key: rd.route_id
47. 0.122 0.956 ↓ 1.9 209 1

Sort (cost=641.50..641.77 rows=109 width=74) (actual time=0.924..0.956 rows=209 loops=1)

  • Sort Key: rd.route_id
  • Sort Method: quicksort Memory: 54kB
48. 0.061 0.834 ↓ 1.9 209 1

Nested Loop (cost=0.43..637.81 rows=109 width=74) (actual time=0.029..0.834 rows=209 loops=1)

49. 0.116 0.116 ↓ 3.5 219 1

CTE Scan on meta_routes mr2 (cost=0.00..2.50 rows=62 width=4) (actual time=0.004..0.116 rows=219 loops=1)

  • Filter: (opened IS NOT TRUE)
  • Rows Removed by Filter: 627
50. 0.657 0.657 ↑ 2.0 1 219

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

  • Index Cond: ((route_id)::integer = (mr2.route_id)::integer)
51. 2.215 5.775 ↓ 10.1 627 1

GroupAggregate (cost=3,354.04..3,362.72 rows=62 width=72) (actual time=3.520..5.775 rows=627 loops=1)

  • Group Key: mr3.route_id
52. 0.336 3.560 ↓ 1.0 627 1

Sort (cost=3,354.04..3,355.59 rows=620 width=72) (actual time=3.486..3.560 rows=627 loops=1)

  • Sort Key: mr3.route_id
  • Sort Method: quicksort Memory: 113kB
53. 0.562 3.224 ↓ 1.0 627 1

Nested Loop (cost=0.42..3,325.29 rows=620 width=72) (actual time=0.027..3.224 rows=627 loops=1)

54. 0.154 0.154 ↓ 10.1 627 1

CTE Scan on meta_routes mr3 (cost=0.00..2.50 rows=62 width=36) (actual time=0.002..0.154 rows=627 loops=1)

  • Filter: (opened IS TRUE)
  • Rows Removed by Filter: 219
55. 2.508 2.508 ↑ 10.0 1 627

Index Scan using drivers_pkey1 on drivers d_1 (cost=0.42..53.49 rows=10 width=68) (actual time=0.004..0.004 rows=1 loops=627)

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

Initplan (for Sort)

57. 0.008 0.008 ↑ 1.0 1 1

Seq Scan on auto_open_route_types (cost=0.00..1.02 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)

  • Filter: ((code)::text = 'future_auto_open'::text)
  • Rows Removed by Filter: 1
58. 5.655 1,559.563 ↑ 3.8 11 1

Hash Left Join (cost=13,191.17..20,608.64 rows=42 width=2,041) (actual time=798.714..1,559.563 rows=11 loops=1)

  • Hash Cond: (r.scheme_id = rs.id)
59. 0.027 1,547.500 ↑ 3.8 11 1

Nested Loop Left Join (cost=13,190.11..16,607.22 rows=42 width=2,032) (actual time=792.231..1,547.500 rows=11 loops=1)

60. 1.847 1,547.440 ↑ 3.8 11 1

Nested Loop Left Join (cost=13,189.82..16,594.19 rows=42 width=1,972) (actual time=792.211..1,547.440 rows=11 loops=1)

  • Filter: CASE WHEN mr.is_transit THEN CASE WHEN COALESCE(dct.formed, false) THEN (((r.deliveries_cars_tasks_id)::integer = (dct.id)::integer) OR ((COALESCE((r.functional)::integer, '-1'::integer) = ANY (dctt.functional_ext)) AND (r.functional_transferred IS NULL)) OR (COALESCE((r.functional_transferred)::integer, '-1'::integer) = ANY (dctt.functional_transferred_ext))) ELSE CASE WHEN (((dctt.id)::integer <> 4) AND (r.functional_transferred IS NULL) AND (r.functional IS NULL)) THEN (dctt.default_car_type_delivery = COALESCE((c.type_delivery)::integer, '-1'::integer)) ELSE CASE WHEN (r.functional_transferred IS NULL) THEN (COALESCE((r.functional)::integer, '-1'::integer) = ANY (dctt.functional_ext)) ELSE (r.functional_transferred = ANY (dctt.functional_transferred_ext)) END END END ELSE true END
  • Rows Removed by Filter: 835
61. 0.971 1,545.593 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,189.53..16,578.20 rows=85 width=1,979) (actual time=739.347..1,545.593 rows=846 loops=1)

62. 0.492 1,542.930 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,189.11..16,539.99 rows=85 width=1,916) (actual time=739.328..1,542.930 rows=846 loops=1)

  • Join Filter: mr.is_transit
63. 0.938 1,539.900 ↓ 10.0 846 1

Hash Left Join (cost=13,188.69..16,501.73 rows=85 width=1,845) (actual time=739.303..1,539.900 rows=846 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)
64. 0.859 1,538.952 ↓ 10.0 846 1

Hash Left Join (cost=13,187.49..16,499.88 rows=85 width=1,801) (actual time=739.278..1,538.952 rows=846 loops=1)

  • Hash Cond: ((r.delivery_acception_status = das.id) AND (r.general_delivery_acception = das.accepted))
65. 1.264 1,538.080 ↓ 10.0 846 1

Hash Left Join (cost=13,186.27..16,498.01 rows=85 width=1,758) (actual time=739.247..1,538.080 rows=846 loops=1)

  • Hash Cond: ((r.id)::integer = (mrd.route_id)::integer)
66. 1.362 1,528.266 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,180.71..16,492.12 rows=85 width=1,694) (actual time=730.682..1,528.266 rows=846 loops=1)

67. 1.142 1,522.674 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,180.27..15,615.79 rows=85 width=1,622) (actual time=730.650..1,522.674 rows=846 loops=1)

  • Join Filter: (NOT mr.is_transit)
  • Rows Removed by Join Filter: 846
68. 0.589 784.666 ↓ 10.0 846 1

Hash Left Join (cost=13,169.77..15,603.59 rows=85 width=1,462) (actual time=726.168..784.666 rows=846 loops=1)

  • Hash Cond: ((mr.route_id)::integer = (lm.route_id)::integer)
  • Join Filter: (NOT mr.is_transit)
69. 0.589 776.852 ↓ 10.0 846 1

Hash Left Join (cost=13,169.28..15,602.68 rows=85 width=1,434) (actual time=718.928..776.852 rows=846 loops=1)

  • Hash Cond: (cch.comment_template = cct.id)
70. 0.510 776.243 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,167.36..15,599.89 rows=85 width=1,412) (actual time=718.892..776.243 rows=846 loops=1)

71. 0.896 774.041 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,167.07..15,570.51 rows=85 width=1,344) (actual time=718.872..774.041 rows=846 loops=1)

72. 0.753 770.607 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,166.64..15,531.62 rows=85 width=1,327) (actual time=718.851..770.607 rows=846 loops=1)

73. 0.774 769.854 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,166.50..15,516.74 rows=85 width=1,326) (actual time=718.839..769.854 rows=846 loops=1)

74. 0.750 769.080 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,166.22..15,490.81 rows=85 width=1,288) (actual time=718.824..769.080 rows=846 loops=1)

75. 0.598 768.330 ↓ 10.0 846 1

Hash Left Join (cost=13,165.94..15,352.51 rows=85 width=1,242) (actual time=718.812..768.330 rows=846 loops=1)

  • Hash Cond: ((prikom_ak.autocades_managers_id)::integer = (am_prikom.id)::integer)
76. 0.820 767.622 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,153.39..15,339.38 rows=85 width=1,242) (actual time=718.687..767.622 rows=846 loops=1)

77. 0.699 766.802 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,153.24..15,324.45 rows=85 width=1,242) (actual time=718.673..766.802 rows=846 loops=1)

78. 0.771 764.411 ↓ 10.0 846 1

Hash Left Join (cost=13,152.96..15,186.15 rows=85 width=1,196) (actual time=718.654..764.411 rows=846 loops=1)

  • Hash Cond: ((r.responsible_dc_id)::integer = (dc.id)::integer)
79. 0.482 763.584 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,142.38..15,175.07 rows=85 width=1,174) (actual time=718.574..763.584 rows=846 loops=1)

80. 1.170 759.718 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,141.94..15,128.98 rows=85 width=1,150) (actual time=718.550..759.718 rows=846 loops=1)

81. 0.863 756.856 ↓ 10.0 846 1

Hash Left Join (cost=13,141.66..15,102.14 rows=85 width=1,150) (actual time=718.525..756.856 rows=846 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))
82. 1.511 755.979 ↓ 10.0 846 1

Hash Left Join (cost=13,140.42..15,099.19 rows=85 width=1,157) (actual time=718.492..755.979 rows=846 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))
83. 0.633 754.451 ↓ 10.0 846 1

Hash Left Join (cost=13,139.03..15,094.39 rows=85 width=1,160) (actual time=718.420..754.451 rows=846 loops=1)

  • Hash Cond: ((r.id)::integer = (haor.route_id)::integer)
84. 1.228 753.695 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,127.10..15,082.03 rows=85 width=1,156) (actual time=718.282..753.695 rows=846 loops=1)

85. 0.429 749.929 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,118.65..13,732.91 rows=85 width=1,144) (actual time=718.250..749.929 rows=846 loops=1)

86. 0.513 746.962 ↓ 10.0 846 1

Hash Left Join (cost=13,118.37..13,706.55 rows=85 width=1,122) (actual time=718.224..746.962 rows=846 loops=1)

  • Hash Cond: ((vr.route_cause_id)::integer = (de_erc.id)::integer)
87. 0.624 746.442 ↓ 10.0 846 1

Hash Left Join (cost=13,117.30..13,705.15 rows=85 width=1,085) (actual time=718.192..746.442 rows=846 loops=1)

  • Hash Cond: ((vr.delivery_cause_id)::integer = (de_ercd.id)::integer)
88. 0.526 745.803 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,116.21..13,703.72 rows=85 width=1,057) (actual time=718.161..745.803 rows=846 loops=1)

89. 0.556 741.893 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,115.78..13,658.55 rows=85 width=1,046) (actual time=718.134..741.893 rows=846 loops=1)

90. 1.069 737.953 ↓ 10.0 846 1

Nested Loop Left Join (cost=13,115.34..13,612.05 rows=85 width=1,025) (actual time=718.092..737.953 rows=846 loops=1)

91. 1.059 725.040 ↓ 10.0 846 1

Hash Left Join (cost=13,114.23..13,504.63 rows=85 width=961) (actual time=717.979..725.040 rows=846 loops=1)

  • Hash Cond: (((CASE WHEN ((cs.state_car)::integer = 12) THEN 12 WHEN ((ts.state_trailer)::integer = 12) THEN 34 WHEN ((cs.state_car)::integer = 33) THEN 33 WHEN ((ts.state_trailer)::integer = 33) THEN 35 ELSE (cs.state_car)::integer END)::identifier)::integer = (vst2.id)::integer)
92. 0.428 723.956 ↓ 10.0 846 1

Nested Loop (cost=13,112.42..13,501.93 rows=85 width=965) (actual time=717.929..723.956 rows=846 loops=1)

93. 0.002 0.009 ↑ 1.0 1 1

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

94. 0.007 0.007 ↑ 1.0 36 1

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

95. 4.059 723.519 ↓ 10.0 846 1

Hash Right Join (cost=13,111.06..13,499.70 rows=85 width=965) (actual time=717.913..723.519 rows=846 loops=1)

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

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

97. 1.777 717.882 ↓ 10.0 846 1

Hash (cost=13,109.99..13,109.99 rows=85 width=965) (actual time=717.882..717.882 rows=846 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 563kB
98. 8.427 716.105 ↓ 10.0 846 1

Hash Join (cost=4,819.10..13,109.99 rows=85 width=965) (actual time=527.804..716.105 rows=846 loops=1)

  • Hash Cond: ((c.id)::integer = (cs.car_id)::integer)
99. 13.452 210.622 ↑ 1.0 60,468 1

Hash Left Join (cost=3,717.47..11,780.75 rows=60,468 width=268) (actual time=30.697..210.622 rows=60,468 loops=1)

  • Hash Cond: ((c.gps_device_id)::integer = (rusc.gps_device_id)::integer)
100. 16.761 194.646 ↑ 1.0 60,468 1

Hash Left Join (cost=3,293.55..11,080.41 rows=60,468 width=262) (actual time=28.143..194.646 rows=60,468 loops=1)

  • Hash Cond: ((c.warm_type_id)::integer = (wt_c.id)::integer)
101. 12.430 177.877 ↑ 1.0 60,468 1

Hash Left Join (cost=3,292.42..10,247.84 rows=60,468 width=255) (actual time=28.119..177.877 rows=60,468 loops=1)

  • Hash Cond: (c.temperature_sensor_model_id = tsm_c.id)
102. 10.780 165.435 ↑ 1.0 60,468 1

Hash Left Join (cost=3,290.90..10,003.55 rows=60,468 width=240) (actual time=28.092..165.435 rows=60,468 loops=1)

  • Hash Cond: ((c.receiver_autocade_id)::integer = (tr_a.id)::integer)
103. 11.000 154.540 ↑ 1.0 60,468 1

Hash Left Join (cost=3,274.99..9,760.83 rows=60,468 width=235) (actual time=27.964..154.540 rows=60,468 loops=1)

  • Hash Cond: ((c.receiver_owner_id)::integer = (tr_cto.id)::integer)
104. 11.444 143.146 ↑ 1.0 60,468 1

Hash Left Join (cost=3,199.41..9,457.65 rows=60,468 width=197) (actual time=27.547..143.146 rows=60,468 loops=1)

  • Hash Cond: ((c.autocades_id)::integer = (a.id)::integer)
105. 16.701 131.397 ↑ 1.0 60,468 1

Hash Left Join (cost=3,168.19..9,153.57 rows=60,468 width=192) (actual time=27.208..131.397 rows=60,468 loops=1)

  • Hash Cond: ((c.company_owner_id)::integer = (c_cto.id)::integer)
106. 13.188 114.310 ↑ 1.0 60,468 1

Hash Left Join (cost=3,092.60..8,246.55 rows=60,468 width=196) (actual time=26.801..114.310 rows=60,468 loops=1)

  • Hash Cond: ((c.car_trailer_brand_id)::integer = ctb_c.id)
107. 13.713 101.034 ↑ 1.0 60,468 1

Hash Left Join (cost=3,084.82..7,784.57 rows=60,468 width=172) (actual time=26.646..101.034 rows=60,468 loops=1)

  • Hash Cond: ((c.id)::integer = (cg.car_id)::integer)
108. 15.132 81.971 ↑ 1.0 60,468 1

Hash Left Join (cost=2,290.42..6,675.02 rows=60,468 width=148) (actual time=21.207..81.971 rows=60,468 loops=1)

  • Hash Cond: ((c.type_delivery)::integer = dtt.id)
109. 17.425 66.829 ↑ 1.0 60,468 1

Hash Left Join (cost=2,289.27..5,842.43 rows=60,468 width=131) (actual time=21.179..66.829 rows=60,468 loops=1)

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

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

  • Filter: (NOT archive)
  • Rows Removed by Filter: 28,131
111. 1.696 21.156 ↑ 1.0 11,244 1

Hash (cost=2,148.72..2,148.72 rows=11,244 width=16) (actual time=21.156..21.156 rows=11,244 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 647kB
112. 1.547 19.460 ↑ 1.0 11,244 1

Hash Left Join (cost=1,321.75..2,148.72 rows=11,244 width=16) (actual time=10.663..19.460 rows=11,244 loops=1)

  • Hash Cond: ((ph.simcard_abroad_id)::integer = (sim_abroad.id)::integer)
113. 5.112 10.445 ↑ 1.0 11,244 1

Hash Right Join (cost=362.99..1,146.73 rows=11,244 width=20) (actual time=3.146..10.445 rows=11,244 loops=1)

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

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

115. 1.405 3.124 ↑ 1.0 11,244 1

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

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

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

117. 3.839 7.468 ↑ 1.0 31,056 1

Hash (cost=570.56..570.56 rows=31,056 width=4) (actual time=7.468..7.468 rows=31,056 loops=1)

  • Buckets: 32,768 Batches: 1 Memory Usage: 1,348kB
118. 3.629 3.629 ↑ 1.0 31,056 1

Seq Scan on simcards sim_abroad (cost=0.00..570.56 rows=31,056 width=4) (actual time=0.004..3.629 rows=31,056 loops=1)

119. 0.006 0.010 ↑ 1.0 7 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
120. 0.004 0.004 ↑ 1.0 7 1

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

121. 2.093 5.350 ↑ 1.0 12,951 1

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

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

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

123. 0.030 0.088 ↑ 1.0 168 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 19kB
124. 0.058 0.058 ↑ 1.0 168 1

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

125. 0.146 0.386 ↑ 1.0 1,226 1

Hash (cost=60.26..60.26 rows=1,226 width=4) (actual time=0.386..0.386 rows=1,226 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 60kB
126. 0.240 0.240 ↑ 1.0 1,226 1

Seq Scan on cars_trailers_owners c_cto (cost=0.00..60.26 rows=1,226 width=4) (actual time=0.005..0.240 rows=1,226 loops=1)

127. 0.059 0.305 ↑ 1.0 396 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
128. 0.086 0.246 ↑ 1.0 396 1

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

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

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

130. 0.042 0.110 ↑ 1.0 291 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
131. 0.068 0.068 ↑ 1.0 291 1

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

132. 0.225 0.394 ↑ 1.0 1,226 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 113kB
133. 0.169 0.169 ↑ 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.002..0.169 rows=1,226 loops=1)

134. 0.053 0.115 ↑ 1.0 396 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 26kB
135. 0.062 0.062 ↑ 1.0 396 1

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

136. 0.008 0.012 ↑ 1.0 23 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
137. 0.004 0.004 ↑ 1.0 23 1

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

138. 0.004 0.008 ↑ 1.0 6 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
139. 0.004 0.004 ↑ 1.0 6 1

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

140. 1.071 2.524 ↑ 1.0 8,085 1

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

  • Buckets: 8,192 Batches: 1 Memory Usage: 423kB
141. 1.453 1.453 ↑ 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.010..1.453 rows=8,085 loops=1)

142. 3.189 497.056 ↓ 6.8 846 1

Hash (cost=1,100.07..1,100.07 rows=125 width=701) (actual time=497.056..497.056 rows=846 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 431kB
143. 1.244 493.867 ↓ 6.8 846 1

Nested Loop (cost=0.72..1,100.07 rows=125 width=701) (actual time=9.059..493.867 rows=846 loops=1)

144. 2.683 490.085 ↓ 6.8 846 1

Nested Loop (cost=0.43..1,060.00 rows=125 width=693) (actual time=9.051..490.085 rows=846 loops=1)

145. 484.018 484.018 ↓ 6.8 846 1

CTE Scan on meta_routes mr (cost=0.00..2.50 rows=125 width=104) (actual time=9.043..484.018 rows=846 loops=1)

146. 3.384 3.384 ↑ 1.0 1 846

Index Scan using routes_pkey on routes r (cost=0.43..8.45 rows=1 width=589) (actual time=0.004..0.004 rows=1 loops=846)

  • Index Cond: ((id)::integer = (mr.route_id)::integer)
147. 2.538 2.538 ↑ 1.0 1 846

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

  • Index Cond: ((car_id)::integer = (r.car_id)::integer)
148. 0.007 0.025 ↑ 1.0 36 1

Hash (cost=1.36..1.36 rows=36 width=4) (actual time=0.025..0.025 rows=36 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
149. 0.018 0.018 ↑ 1.0 36 1

Seq Scan on vehicles_statuses_types vst2 (cost=0.00..1.36 rows=36 width=4) (actual time=0.013..0.018 rows=36 loops=1)

150. 0.016 11.844 ↑ 1.0 1 846

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

151. 0.854 10.152 ↑ 1.0 1 846

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

152. 0.854 8.460 ↑ 1.0 1 846

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

153. 1.700 6.768 ↑ 1.0 1 846

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

154. 4.230 4.230 ↑ 1.0 1 846

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

  • Index Cond: ((id)::integer = (r.trailer_id)::integer)
155. 0.838 0.838 ↑ 1.0 1 838

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=838)

  • Index Cond: (id = (t.car_trailer_brand_id)::integer)
156. 0.838 0.838 ↑ 1.0 1 838

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.001..0.001 rows=1 loops=838)

  • Index Cond: (t.temperature_sensor_model_id = id)
157. 0.838 0.838 ↑ 1.0 1 838

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.001 rows=1 loops=838)

  • Index Cond: ((id)::integer = (t.warm_type_id)::integer)
158. 1.676 1.676 ↑ 1.0 1 838

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

  • Index Cond: ((gps_device_id)::integer = (t.gps_device_id)::integer)
159. 3.384 3.384 ↑ 1.0 1 846

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

  • Index Cond: ((route_id)::integer = (r.id)::integer)
160. 3.384 3.384 ↑ 1.0 1 846

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

  • Index Cond: ((route_id)::integer = (r.id)::integer)
161. 0.006 0.015 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
162. 0.009 0.009 ↑ 1.0 4 1

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

163. 0.002 0.007 ↑ 1.0 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
164. 0.005 0.005 ↑ 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.005 rows=3 loops=1)

165. 2.538 2.538 ↑ 1.0 1 846

Index Scan using settle_pkey on settles s (cost=0.28..0.30 rows=1 width=26) (actual time=0.002..0.003 rows=1 loops=846)

  • Index Cond: ((id)::integer = (r.settle_id)::integer)
166. 2.538 2.538 ↑ 1.0 1 846

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

  • Index Cond: ((id)::integer = (CASE WHEN mr.is_transit THEN s.waypoint_id ELSE (SubPlan 19) END)::integer)
167.          

SubPlan (for Index Scan)

168. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.14..8.16 rows=1 width=4) (never executed)

169. 0.000 0.000 ↓ 0.0 0

Index Scan using distribution_center_pkey on distribution_centers (cost=0.14..8.16 rows=1 width=4) (never executed)

  • Index Cond: ((id)::integer = (mr.dc_id)::integer)
170. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.14..8.16 rows=1 width=4) (never executed)

171. 0.000 0.000 ↓ 0.0 0

Index Scan using distribution_center_pkey on distribution_centers (cost=0.14..8.16 rows=1 width=4) (never executed)

  • Index Cond: ((id)::integer = (mr.dc_id)::integer)
172. 0.031 0.123 ↓ 1.1 250 1

Hash (cost=8.95..8.95 rows=238 width=8) (actual time=0.123..0.123 rows=250 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 18kB
173. 0.092 0.092 ↓ 1.1 250 1

Seq Scan on history_auto_open_routes haor (cost=0.00..8.95 rows=238 width=8) (actual time=0.024..0.092 rows=250 loops=1)

  • Filter: (auto_open_type_id = $34)
  • Rows Removed by Filter: 226
174. 0.012 0.017 ↑ 1.0 13 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
175. 0.005 0.005 ↑ 1.0 13 1

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

176. 0.010 0.014 ↑ 1.0 8 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
177. 0.004 0.004 ↑ 1.0 8 1

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

178. 1.692 1.692 ↑ 1.0 1 846

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.002..0.002 rows=1 loops=846)

  • Index Cond: ((car_id)::integer = (c.id)::integer)
179. 3.384 3.384 ↑ 1.0 1 846

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

  • Index Cond: ((r.id)::integer = route_id)
180. 0.024 0.056 ↑ 1.0 115 1

Hash (cost=9.15..9.15 rows=115 width=30) (actual time=0.056..0.056 rows=115 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 16kB
181. 0.032 0.032 ↑ 1.0 115 1

Seq Scan on distribution_centers dc (cost=0.00..9.15 rows=115 width=30) (actual time=0.005..0.032 rows=115 loops=1)

182. 1.692 1.692 ↑ 1.0 1 846

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=846)

  • Index Cond: (user_oid = (am.user_oid)::oid)
183. 0.000 0.000 ↓ 0.0 0 846

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=846)

  • Index Cond: ((id)::integer = (c.receiver_autocade_id)::integer)
184. 0.047 0.110 ↑ 1.0 291 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 20kB
185. 0.063 0.063 ↑ 1.0 291 1

Seq Scan on autocades_managers am_prikom (cost=0.00..8.91 rows=291 width=8) (actual time=0.004..0.063 rows=291 loops=1)

186. 0.000 0.000 ↓ 0.0 0 846

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=846)

  • Index Cond: (user_oid = (am_prikom.user_oid)::oid)
187. 0.000 0.000 ↓ 0.0 0 846

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=846)

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

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=846)

  • Index Cond: ((id)::integer = ref.open_receiver_autocade_id)
189. 2.538 2.538 ↑ 1.0 1 846

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.003..0.003 rows=1 loops=846)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
190. 1.692 1.692 ↓ 0.0 0 846

Index Scan using cch_route_idx on car_comments_history cch (cost=0.29..0.33 rows=2 width=72) (actual time=0.002..0.002 rows=0 loops=846)

  • Index Cond: (route_id = (r.id)::integer)
191. 0.009 0.020 ↑ 1.0 41 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
192. 0.011 0.011 ↑ 1.0 41 1

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

193. 0.000 7.225 ↓ 0.0 0 1

Hash (cost=0.30..0.30 rows=15 width=36) (actual time=7.225..7.225 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
194. 7.225 7.225 ↓ 0.0 0 1

CTE Scan on last_market lm (cost=0.00..0.30 rows=15 width=36) (actual time=7.225..7.225 rows=0 loops=1)

195. 730.944 736.866 ↑ 1.0 1 846

Function Scan on necessary_passes np (cost=10.50..10.51 rows=1 width=160) (actual time=0.870..0.871 rows=1 loops=846)

196.          

SubPlan (for Function Scan)

197. 1.692 5.922 ↑ 1.0 1 846

Aggregate (cost=10.24..10.25 rows=1 width=4) (actual time=0.007..0.007 rows=1 loops=846)

198. 4.230 4.230 ↑ 2.0 1 846

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

  • Index Cond: (route_id = (r.id)::integer)
  • Heap Fetches: 835
199. 0.000 4.230 ↓ 0.0 0 846

Subquery Scan on dc_info (cost=0.43..10.30 rows=1 width=76) (actual time=0.005..0.005 rows=0 loops=846)

  • Filter: ((dc_info.route_id)::integer = (r.id)::integer)
200. 0.846 4.230 ↓ 0.0 0 846

GroupAggregate (cost=0.43..10.27 rows=2 width=18) (actual time=0.005..0.005 rows=0 loops=846)

  • Group Key: rdtd.route_id
201. 3.384 3.384 ↓ 0.0 0 846

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.004..0.004 rows=0 loops=846)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
202. 0.154 8.550 ↓ 4.9 836 1

Hash (cost=3.42..3.42 rows=171 width=68) (actual time=8.550..8.550 rows=836 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 95kB
203. 8.396 8.396 ↓ 4.9 836 1

CTE Scan on meta_routes_drivers mrd (cost=0.00..3.42 rows=171 width=68) (actual time=7.943..8.396 rows=836 loops=1)

204. 0.008 0.013 ↑ 1.0 9 1

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

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

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

206. 0.004 0.010 ↑ 1.1 7 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
207. 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)

208. 2.538 2.538 ↓ 0.0 0 846

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=846)

  • Index Cond: (route_id = (r.id)::integer)
209. 1.692 1.692 ↓ 0.0 0 846

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

  • Index Cond: (route_id = (r.id)::integer)
210. 0.000 0.000 ↑ 1.0 1 846

Materialize (cost=0.29..9.41 rows=1 width=77) (actual time=0.000..0.000 rows=1 loops=846)

211. 0.019 0.039 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.29..9.40 rows=1 width=77) (actual time=0.038..0.039 rows=1 loops=1)

  • Join Filter: ((dctt.id)::integer = (dct.deliveries_cars_tasks_types_id)::integer)
  • Rows Removed by Join Filter: 3
212. 0.018 0.018 ↑ 1.0 1 1

Index Scan using deliveries_cars_tasks_pkey on deliveries_cars_tasks dct (cost=0.29..8.31 rows=1 width=9) (actual time=0.017..0.018 rows=1 loops=1)

  • Index Cond: ((id)::integer = 94,533)
213. 0.002 0.002 ↑ 1.0 4 1

Seq Scan on deliveries_cars_tasks_types dctt (cost=0.00..1.04 rows=4 width=72) (actual time=0.002..0.002 rows=4 loops=1)

214. 0.033 0.033 ↓ 0.0 0 11

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

  • Index Cond: (oid = r.user_oid)
  • Filter: rolcanlogin
215. 0.002 0.006 ↑ 1.0 3 1

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

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

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

217.          

SubPlan (for Hash Left Join)

218. 0.022 0.077 ↓ 0.0 0 11

Limit (cost=0.43..7.48 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=11)

219. 0.055 0.055 ↓ 0.0 0 11

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.005..0.005 rows=0 loops=11)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
  • Filter: ((object_var)::text = 'market'::text)
220. 0.044 0.154 ↓ 0.0 0 11

Nested Loop (cost=0.87..31.97 rows=1 width=0) (actual time=0.014..0.014 rows=0 loops=11)

  • 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)
221. 0.044 0.044 ↑ 1.0 1 11

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=11)

  • Index Cond: ((id)::integer = (r.id)::integer)
222. 0.066 0.066 ↓ 0.0 0 11

Index Scan using routes_points_all_idx3 on routes_points_all rpa_1 (cost=0.43..23.45 rows=3 width=9) (actual time=0.006..0.006 rows=0 loops=11)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
  • Filter: (COALESCE((temperature_control_status)::integer, 0) > 0)
  • Rows Removed by Filter: 1
223. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=647,322.32..1,366,943.77 rows=702,923 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
224. 0.000 0.000 ↓ 0.0 0

Seq Scan on routes_points_all rpa_2 (cost=0.00..572,816.09 rows=2,811,691 width=9) (never executed)

  • Filter: (COALESCE((temperature_control_status)::integer, 0) > 0)
225. 0.000 0.000 ↓ 0.0 0

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

226. 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)

227. 0.011 0.077 ↓ 0.0 0 11

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

228. 0.033 0.066 ↓ 0.0 0 11

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

  • Join Filter: (s_1.status = ss.id)
229. 0.033 0.033 ↓ 0.0 0 11

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

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

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

231. 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
232. 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)
233. 5.852 5.852 ↑ 1.0 1 11

Function Scan on geography_distance_between_km sq_distance (cost=1.36..1.89 rows=1 width=32) (actual time=0.532..0.532 rows=1 loops=11)

234.          

Initplan (for Function Scan)

235. 0.000 0.000 ↓ 0.0 0

Seq Scan on gps_process_data_settings (cost=0.00..1.11 rows=1 width=4) (never executed)

  • Filter: ((object_var)::text = 'distribution_center'::text)
236. 0.011 0.242 ↓ 0.0 0 11

Limit (cost=32.11..32.11 rows=1 width=16) (actual time=0.022..0.022 rows=0 loops=11)

237. 0.055 0.231 ↓ 0.0 0 11

Sort (cost=32.11..32.11 rows=2 width=16) (actual time=0.021..0.021 rows=0 loops=11)

  • Sort Key: rpa_3.type_id, rpa_3.order_num
  • Sort Method: quicksort Memory: 25kB
238. 0.077 0.176 ↑ 1.0 2 11

Nested Loop (cost=0.87..32.10 rows=2 width=16) (actual time=0.014..0.016 rows=2 loops=11)

  • 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)
239. 0.055 0.055 ↑ 1.0 1 11

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=11)

  • Index Cond: ((id)::integer = (r.id)::integer)
240. 0.044 0.044 ↑ 5.0 2 11

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.004 rows=2 loops=11)

  • Index Cond: ((route_id)::integer = (r.id)::integer)
Planning time : 46.680 ms
Execution time : 1,563.413 ms