explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CUqV

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

CTE Scan on serviceorders_collectorintervals_harvesterdata (cost=1,498,893.64..1,499,021.64 rows=6,400 width=240) (actual rows= loops=)

2.          

CTE arguments

3. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=214) (actual rows= loops=)

4.          

CTE params_pre

5. 0.000 0.000 ↓ 0.0

CTE Scan on arguments arguments_6 (cost=0.12..1.79 rows=1 width=508) (actual rows= loops=)

6.          

Initplan (forCTE Scan)

7. 0.000 0.000 ↓ 0.0

CTE Scan on arguments (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

CTE Scan on arguments arguments_1 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

CTE Scan on arguments arguments_2 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

CTE Scan on arguments arguments_3 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

CTE Scan on arguments arguments_4 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

CTE Scan on arguments arguments_5 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

13.          

CTE params

14. 0.000 0.000 ↓ 0.0

CTE Scan on params_pre (cost=0.00..0.02 rows=1 width=516) (actual rows= loops=)

15.          

CTE tractors_used

16. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=3.11..5,379.05 rows=109 width=4) (actual rows= loops=)

  • Hash Cond: (m.configuration_type_id = (unnest(params_3.configuration_type_tractors)))
17.          

Initplan (forHash Semi Join)

18. 0.000 0.000 ↓ 0.0

CTE Scan on params (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

CTE Scan on params params_1 (cost=0.00..0.02 rows=1 width=1) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

CTE Scan on params params_2 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.27..5,374.71 rows=109 width=8) (actual rows= loops=)

  • Join Filter: (m.entity_id = ube.entity_id)
22. 0.000 0.000 ↓ 0.0

Seq Scan on mobiles m (cost=0.00..5,329.28 rows=1,389 width=12) (actual rows= loops=)

  • Filter: ((NOT $10) OR (id = ANY ($11)))
23. 0.000 0.000 ↓ 0.0

Materialize (cost=0.27..4.32 rows=2 width=4) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Index Only Scan using users_by_entities_pkey on users_by_entities ube (cost=0.27..4.31 rows=2 width=4) (actual rows= loops=)

  • Index Cond: (user_id = $9)
25. 0.000 0.000 ↓ 0.0

Hash (cost=1.53..1.53 rows=100 width=4) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

CTE Scan on params params_3 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

28.          

CTE trucks_all

29. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=7.13..5,313.29 rows=218 width=4) (actual rows= loops=)

  • Hash Cond: (m_1.configuration_type_id = (unnest(params_5.configuration_type_trucks)))
30.          

Initplan (forHash Semi Join)

31. 0.000 0.000 ↓ 0.0

CTE Scan on params params_4 (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash Join (cost=4.33..5,307.49 rows=218 width=8) (actual rows= loops=)

  • Hash Cond: (m_1.entity_id = ube_1.entity_id)
33. 0.000 0.000 ↓ 0.0

Seq Scan on mobiles m_1 (cost=0.00..5,294.68 rows=2,768 width=12) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash (cost=4.31..4.31 rows=2 width=4) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Index Only Scan using users_by_entities_pkey on users_by_entities ube_1 (cost=0.27..4.31 rows=2 width=4) (actual rows= loops=)

  • Index Cond: (user_id = $13)
36. 0.000 0.000 ↓ 0.0

Hash (cost=1.53..1.53 rows=100 width=4) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

CTE Scan on params params_5 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

39.          

CTE trucks_used

40. 0.000 0.000 ↓ 0.0

CTE Scan on trucks_all (cost=0.04..7.12 rows=114 width=4) (actual rows= loops=)

  • Filter: ((NOT $15) OR (id = ANY ($16)))
41.          

Initplan (forCTE Scan)

42. 0.000 0.000 ↓ 0.0

CTE Scan on params params_6 (cost=0.00..0.02 rows=1 width=1) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

CTE Scan on params params_7 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

44.          

CTE harvesters_used

45. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=3.11..5,379.05 rows=109 width=4) (actual rows= loops=)

  • Hash Cond: (m_2.configuration_type_id = (unnest(params_11.configuration_type_harvesters)))
46.          

Initplan (forHash Semi Join)

47. 0.000 0.000 ↓ 0.0

CTE Scan on params params_8 (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

CTE Scan on params params_9 (cost=0.00..0.02 rows=1 width=1) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

CTE Scan on params params_10 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.27..5,374.71 rows=109 width=8) (actual rows= loops=)

  • Join Filter: (m_2.entity_id = ube_2.entity_id)
51. 0.000 0.000 ↓ 0.0

Seq Scan on mobiles m_2 (cost=0.00..5,329.28 rows=1,389 width=12) (actual rows= loops=)

  • Filter: ((NOT $19) OR (id = ANY ($20)))
52. 0.000 0.000 ↓ 0.0

Materialize (cost=0.27..4.32 rows=2 width=4) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Index Only Scan using users_by_entities_pkey on users_by_entities ube_2 (cost=0.27..4.31 rows=2 width=4) (actual rows= loops=)

  • Index Cond: (user_id = $18)
54. 0.000 0.000 ↓ 0.0

Hash (cost=1.53..1.53 rows=100 width=4) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

CTE Scan on params params_11 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

57.          

CTE farms_used

58. 0.000 0.000 ↓ 0.0

Hash Join (cost=4.39..355.70 rows=14 width=4) (actual rows= loops=)

  • Hash Cond: (fa.entity_id = ube_3.entity_id)
59.          

Initplan (forHash Join)

60. 0.000 0.000 ↓ 0.0

CTE Scan on params params_12 (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

CTE Scan on params params_13 (cost=0.00..0.02 rows=1 width=1) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

CTE Scan on params params_14 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on farms fa (cost=0.00..349.94 rows=492 width=8) (actual rows= loops=)

  • Filter: ((NOT $23) OR (id = ANY ($24)))
64. 0.000 0.000 ↓ 0.0

Hash (cost=4.31..4.31 rows=2 width=4) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Index Only Scan using users_by_entities_pkey on users_by_entities ube_3 (cost=0.27..4.31 rows=2 width=4) (actual rows= loops=)

  • Index Cond: (user_id = $22)
66.          

CTE fields_used

67. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.95..5,844.89 rows=198 width=4) (actual rows= loops=)

68.          

Initplan (forNested Loop)

69. 0.000 0.000 ↓ 0.0

CTE Scan on params params_15 (cost=0.00..0.02 rows=1 width=1) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

CTE Scan on params params_16 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.63..5,776.72 rows=218 width=12) (actual rows= loops=)

  • Hash Cond: (fi.farm_id = farms_used.id)
72. 0.000 0.000 ↓ 0.0

Seq Scan on fields fi (cost=0.00..5,722.21 rows=13,787 width=8) (actual rows= loops=)

  • Filter: ((NOT $26) OR (id = ANY ($27)))
73. 0.000 0.000 ↓ 0.0

Hash (cost=0.46..0.46 rows=14 width=4) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

HashAggregate (cost=0.32..0.46 rows=14 width=4) (actual rows= loops=)

  • Group Key: farms_used.id
75. 0.000 0.000 ↓ 0.0

CTE Scan on farms_used (cost=0.00..0.28 rows=14 width=4) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Index Only Scan using farms_pk on farms fa_1 (cost=0.28..0.31 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = fi.farm_id)
77.          

CTE service_orders_used

78. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=52.22..500.30 rows=1 width=78) (actual rows= loops=)

79.          

Initplan (forNested Loop Left Join)

80. 0.000 0.000 ↓ 0.0

CTE Scan on params params_17 (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

CTE Scan on params params_18 (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

CTE Scan on params params_19 (cost=0.00..0.02 rows=1 width=1) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

CTE Scan on params params_20 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

CTE Scan on params params_21 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

CTE Scan on params params_22 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Nested Loop (cost=51.97..499.94 rows=1 width=80) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Nested Loop (cost=49.12..462.58 rows=4 width=76) (actual rows= loops=)

  • Join Filter: (tso.entity_id = ube_4.entity_id)
88. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on truck_service_orders tso (cost=48.85..453.40 rows=163 width=80) (actual rows= loops=)

  • Recheck Cond: (((start >= $33) AND (start <= $34)) OR (id = ANY ($36)))
89. 0.000 0.000 ↓ 0.0

BitmapOr (cost=48.85..48.85 rows=163 width=0) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on truck_service_orders_start_end_idx (cost=0.00..5.82 rows=153 width=0) (actual rows= loops=)

  • Index Cond: ((start >= $33) AND (start <= $34))
91. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on tracking_truck_service_order_pk (cost=0.00..42.95 rows=10 width=0) (actual rows= loops=)

  • Index Cond: (id = ANY ($36))
92. 0.000 0.000 ↓ 0.0

Materialize (cost=0.27..4.32 rows=2 width=4) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Index Only Scan using users_by_entities_pkey on users_by_entities ube_4 (cost=0.27..4.31 rows=2 width=4) (actual rows= loops=)

  • Index Cond: (user_id = $31)
94. 0.000 0.000 ↓ 0.0

Index Scan using mobiles_pk on mobiles m_3 (cost=2.85..9.33 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = tso.mobile_id)
  • Filter: (((NOT $32) AND (tso.start >= $33) AND (tso.start <= $34) AND (hashed SubPlan 35)) OR (tso.id = ANY ($36)))
95.          

SubPlan (forIndex Scan)

96. 0.000 0.000 ↓ 0.0

CTE Scan on trucks_used (cost=0.00..2.28 rows=114 width=4) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Index Scan using tracking_harvest_fronts_pk on harvest_fronts hfs (cost=0.14..0.23 rows=1 width=6) (actual rows= loops=)

  • Index Cond: (id = tso.harvest_front_id)
98.          

CTE boxes_by_service_order

99. 0.000 0.000 ↓ 0.0

Hash Join (cost=0.03..6.76 rows=1 width=8) (actual rows= loops=)

  • Hash Cond: (mbk.kit_id = sou.kit_id)
  • Join Filter: (((mbk."from" IS NULL) OR (mbk."from" <= sou.start)) AND ((mbk."to" IS NULL) OR (mbk."to" >= sou.start)))
100. 0.000 0.000 ↓ 0.0

Seq Scan on mobiles_by_kits mbk (cost=0.00..5.68 rows=268 width=24) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

Hash (cost=0.02..0.02 rows=1 width=16) (actual rows= loops=)

102. 0.000 0.000 ↓ 0.0

CTE Scan on service_orders_used sou (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)

103.          

CTE rfid_reads_by_service_order

104. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=65.32..91,802.07 rows=3,782 width=61) (actual rows= loops=)

  • Hash Cond: (co.type_id = (unnest(params_26.rfid_code_type_to_identify_trucks)))
105. 0.000 0.000 ↓ 0.0

Hash Join (cost=62.54..1,114.71 rows=5,036 width=49) (actual rows= loops=)

  • Hash Cond: (cr.code_id = co.id)
  • Join Filter: ((co.mobile_id = sou_1.truck_id) OR (SubPlan 42))
106. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..1,025.99 rows=10,054 width=24) (actual rows= loops=)

107. 0.000 0.000 ↓ 0.0

CTE Scan on service_orders_used sou_1 (cost=0.00..0.02 rows=1 width=24) (actual rows= loops=)

108. 0.000 0.000 ↓ 0.0

Index Scan using rfid_code_records_gps_time_idx on code_records cr (cost=0.29..925.43 rows=10,054 width=16) (actual rows= loops=)

  • Index Cond: ((gps_time >= sou_1.start) AND (gps_time <= sou_1."end"))
109. 0.000 0.000 ↓ 0.0

Hash (cost=42.11..42.11 rows=1,611 width=33) (actual rows= loops=)

110. 0.000 0.000 ↓ 0.0

Seq Scan on codes co (cost=0.00..42.11 rows=1,611 width=33) (actual rows= loops=)

111.          

SubPlan (forHash Join)

112. 0.000 0.000 ↓ 0.0

CTE Scan on boxes_by_service_order bbs (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

  • Filter: (sou_1.id = service_order_id)
113. 0.000 0.000 ↓ 0.0

Hash (cost=1.53..1.53 rows=100 width=4) (actual rows= loops=)

114. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

115. 0.000 0.000 ↓ 0.0

CTE Scan on params params_26 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

116.          

SubPlan (forHash Semi Join)

117. 0.000 0.000 ↓ 0.0

Limit (cost=0.70..7.86 rows=1 width=8) (actual rows= loops=)

118. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=0.70..1,690.85 rows=236 width=8) (actual rows= loops=)

  • Join Filter: (co2.type_id = (unnest(params_23.rfid_code_type_to_identify_trucks)))
119. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.70..1,218.07 rows=314 width=12) (actual rows= loops=)

120. 0.000 0.000 ↓ 0.0

Index Scan using rfid_code_records_mobile_id_gps_time_idx on code_records cr2 (cost=0.42..981.44 rows=314 width=12) (actual rows= loops=)

  • Index Cond: ((mobile_id = cr.mobile_id) AND (gps_time > cr.gps_time))
121. 0.000 0.000 ↓ 0.0

Index Scan using rfid_codes_id_pk on codes co2 (cost=0.28..0.75 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (id = cr2.code_id)
122. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..2.03 rows=100 width=4) (actual rows= loops=)

123. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

124. 0.000 0.000 ↓ 0.0

CTE Scan on params params_23 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

125. 0.000 0.000 ↓ 0.0

Limit (cost=0.59..16.11 rows=1 width=8) (actual rows= loops=)

126.          

Initplan (forLimit)

127. 0.000 0.000 ↓ 0.0

CTE Scan on params params_24 (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)

128. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=0.57..4,204.90 rows=271 width=8) (actual rows= loops=)

  • Join Filter: (dr.status_id = (unnest(params_25.load_statuses)))
129. 0.000 0.000 ↓ 0.0

Index Scan using data_records2_mobile_time on data_records dr (cost=0.57..3,067.63 rows=757 width=12) (actual rows= loops=)

  • Index Cond: ((mobile_id = cr.mobile_id) AND (gps_time >= cr.gps_time) AND (gps_time < (cr.gps_time + $46)))
130. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..2.03 rows=100 width=4) (actual rows= loops=)

131. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

132. 0.000 0.000 ↓ 0.0

CTE Scan on params params_25 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

133.          

CTE load_by_rfid_first

134. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,329.32..5,456.32 rows=1,255 width=656) (actual rows= loops=)

  • Hash Cond: (rr.tractor_id = mobiles.id)
  • Join Filter: (((NOT mobiles.tracking_load_by_status) AND ((rr.next_gps_time IS NULL) OR (rr.next_gps_time > (rr.gps_time + $54)))) OR (mobiles.tracking_load_by_status AND ((rr.next_gps_time IS NULL) OR (rr.next_gps_time > rr.next_load_status_gps_time)) AND ((rr.next_load_status_gps_time IS NULL) OR (rr.next_load_status_gps_time < (rr.gps_time + $55)))))
135.          

Initplan (forHash Join)

136. 0.000 0.000 ↓ 0.0

CTE Scan on params params_27 (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)

137. 0.000 0.000 ↓ 0.0

CTE Scan on params params_28 (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)

138. 0.000 0.000 ↓ 0.0

CTE Scan on rfid_reads_by_service_order rr (cost=0.00..122.92 rows=1,558 width=672) (actual rows= loops=)

  • Filter: ((next_gps_time IS NULL) OR (next_gps_time > (gps_time + $54)) OR (((next_gps_time IS NULL) OR (next_gps_time > next_load_status_gps_time)) AND ((next_load_status_gps_time IS NULL) OR (next_load_status_gps_time < (gps_time + $55)))))
139. 0.000 0.000 ↓ 0.0

Hash (cost=5,294.68..5,294.68 rows=2,768 width=5) (actual rows= loops=)

140. 0.000 0.000 ↓ 0.0

Seq Scan on mobiles (cost=0.00..5,294.68 rows=2,768 width=5) (actual rows= loops=)

141.          

CTE load_previous_first

142. 0.000 0.000 ↓ 0.0

Hash Join (cost=77.98..867,727.28 rows=36,694 width=680) (actual rows= loops=)

  • Hash Cond: (cr_1.code_id = co_1.id)
143.          

Initplan (forHash Join)

144. 0.000 0.000 ↓ 0.0

CTE Scan on params params_32 (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)

145. 0.000 0.000 ↓ 0.0

CTE Scan on params params_33 (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)

146. 0.000 0.000 ↓ 0.0

CTE Scan on params params_34 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

147. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.97..5,345.61 rows=63,088 width=672) (actual rows= loops=)

148. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=3.54..38.92 rows=628 width=660) (actual rows= loops=)

  • Hash Cond: (l.tractor_id = tractors_used.id)
149. 0.000 0.000 ↓ 0.0

CTE Scan on load_by_rfid_first l (cost=0.00..25.10 rows=1,255 width=656) (actual rows= loops=)

150. 0.000 0.000 ↓ 0.0

Hash (cost=2.18..2.18 rows=109 width=4) (actual rows= loops=)

151. 0.000 0.000 ↓ 0.0

CTE Scan on tractors_used (cost=0.00..2.18 rows=109 width=4) (actual rows= loops=)

152. 0.000 0.000 ↓ 0.0

Index Scan using rfid_code_records_mobile_id_gps_time_idx on code_records cr_1 (cost=0.42..7.40 rows=105 width=16) (actual rows= loops=)

  • Index Cond: ((mobile_id = l.tractor_id) AND (gps_time >= (l.load_time - $64)) AND (gps_time <= (l.load_time - $65)))
153. 0.000 0.000 ↓ 0.0

Hash (cost=62.25..62.25 rows=937 width=4) (actual rows= loops=)

154. 0.000 0.000 ↓ 0.0

Seq Scan on codes co_1 (cost=0.00..62.25 rows=937 width=4) (actual rows= loops=)

  • Filter: (type_id = ANY ($66))
155.          

SubPlan (forHash Join)

156. 0.000 0.000 ↓ 0.0

Limit (cost=0.72..7.39 rows=1 width=8) (actual rows= loops=)

157.          

Initplan (forLimit)

158. 0.000 0.000 ↓ 0.0

CTE Scan on params params_29 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

159. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.70..1,222.00 rows=183 width=8) (actual rows= loops=)

160. 0.000 0.000 ↓ 0.0

Index Scan using rfid_code_records_mobile_id_gps_time_idx on code_records cr2_1 (cost=0.42..981.44 rows=314 width=12) (actual rows= loops=)

  • Index Cond: ((mobile_id = cr_1.mobile_id) AND (gps_time > cr_1.gps_time))
161. 0.000 0.000 ↓ 0.0

Index Scan using rfid_codes_id_pk on codes co2_1 (cost=0.28..0.77 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = cr2_1.code_id)
  • Filter: (type_id = ANY ($57))
162. 0.000 0.000 ↓ 0.0

Limit (cost=0.59..16.11 rows=1 width=8) (actual rows= loops=)

163.          

Initplan (forLimit)

164. 0.000 0.000 ↓ 0.0

CTE Scan on params params_30 (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)

165. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=0.57..4,204.90 rows=271 width=8) (actual rows= loops=)

  • Join Filter: (dr_1.status_id = (unnest(params_31.load_statuses)))
166. 0.000 0.000 ↓ 0.0

Index Scan using data_records2_mobile_time on data_records dr_1 (cost=0.57..3,067.63 rows=757 width=12) (actual rows= loops=)

  • Index Cond: ((mobile_id = cr_1.mobile_id) AND (gps_time >= cr_1.gps_time) AND (gps_time < (cr_1.gps_time + $61)))
167. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..2.03 rows=100 width=4) (actual rows= loops=)

168. 0.000 0.000 ↓ 0.0

ProjectSet (cost=0.00..0.53 rows=100 width=4) (actual rows= loops=)

169. 0.000 0.000 ↓ 0.0

CTE Scan on params params_31 (cost=0.00..0.02 rows=1 width=32) (actual rows= loops=)

170.          

CTE load_previous_second

171. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,329.32..6,561.59 rows=12,177 width=664) (actual rows= loops=)

  • Hash Cond: (l_1.tractor_id = mobiles_1.id)
  • Join Filter: (((NOT mobiles_1.tracking_load_by_status) AND ((l_1.next_gps_time_prev IS NULL) OR (l_1.next_gps_time_prev > (l_1.load_time_previous + $70)))) OR (mobiles_1.tracking_load_by_status AND ((l_1.next_gps_time_prev IS NULL) OR (l_1.next_gps_time_prev > l_1.next_load_status_gps_time)) AND ((l_1.next_load_status_gps_time IS NULL) OR (l_1.next_load_status_gps_time < (l_1.load_time_previous + $71)))))
172.          

Initplan (forHash Join)

173. 0.000 0.000 ↓ 0.0

CTE Scan on params params_35 (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)

174. 0.000 0.000 ↓ 0.0

CTE Scan on params params_36 (cost=0.00..0.02 rows=1 width=16) (actual rows= loops=)

175. 0.000 0.000 ↓ 0.0

CTE Scan on load_previous_first l_1 (cost=0.00..1,192.56 rows=15,112 width=680) (actual rows= loops=)

  • Filter: ((next_gps_time_prev IS NULL) OR (next_gps_time_prev > (load_time_previous + $70)) OR (((next_gps_time_prev IS NULL) OR (next_gps_time_prev > next_load_status_gps_time)) AND ((next_load_status_gps_time IS NULL) OR (next_load_status_gps_time < (load_time_previous + $71)))))
176. 0.000 0.000 ↓ 0.0

Hash (cost=5,294.68..5,294.68 rows=2,768 width=5) (actual rows= loops=)

177. 0.000 0.000 ↓ 0.0

Seq Scan on mobiles mobiles_1 (cost=0.00..5,294.68 rows=2,768 width=5) (actual rows= loops=)

178.          

CTE load_data

179. 0.000 0.000 ↓ 0.0

Unique (cost=109,080.56..109,171.89 rows=1,218 width=696) (actual rows= loops=)

180. 0.000 0.000 ↓ 0.0

Sort (cost=109,080.56..109,111.01 rows=12,177 width=696) (actual rows= loops=)

  • Sort Key: l_2.tractor_id, l_2.load_time, l_2.load_time_previous DESC
181. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.57..104,505.74 rows=12,177 width=696) (actual rows= loops=)

182. 0.000 0.000 ↓ 0.0

CTE Scan on load_previous_second l_2 (cost=0.00..243.54 rows=12,177 width=664) (actual rows= loops=)

183. 0.000 0.000 ↓ 0.0

Index Scan using data_records2_idx on data_records dr_2 (cost=0.57..8.55 rows=1 width=44) (actual rows= loops=)

  • Index Cond: ((gps_time = l_2.load_time) AND (mobile_id = l_2.tractor_id))
184.          

CTE serviceorders_collectorsintervals

185. 0.000 0.000 ↓ 0.0

Unique (cost=182.79..219.36 rows=1,219 width=196) (actual rows= loops=)

186. 0.000 0.000 ↓ 0.0

Sort (cost=182.79..185.84 rows=1,219 width=196) (actual rows= loops=)

  • Sort Key: so.id, so.start, so."end", so.truck_id, (NULL::integer), (NULL::character varying), (NULL::character varying), (NULL::character varying), (NULL::character varying), (NULL::geometry), (NULL::bigint)
187. 0.000 0.000 ↓ 0.0

Append (cost=0.00..120.31 rows=1,219 width=196) (actual rows= loops=)

188. 0.000 0.000 ↓ 0.0

CTE Scan on service_orders_used so (cost=0.00..0.02 rows=1 width=196) (actual rows= loops=)

189. 0.000 0.000 ↓ 0.0

WindowAgg (cost=86.78..108.10 rows=1,218 width=700) (actual rows= loops=)

190. 0.000 0.000 ↓ 0.0

Sort (cost=86.78..89.83 rows=1,218 width=692) (actual rows= loops=)

  • Sort Key: ld.service_order_id
191. 0.000 0.000 ↓ 0.0

CTE Scan on load_data ld (cost=0.00..24.36 rows=1,218 width=692) (actual rows= loops=)

192.          

CTE serviceorders_collectorintervals_harvesterdata

193. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=393,674.31..395,167.14 rows=6,400 width=240) (actual rows= loops=)

  • Group Key: ci.service_order_id, ci.collector_id, ci.collector_start, ci.collector_end, p.mobile_id_from, p.field_from, ci.the_geom, ci.data_code, ci.data1, ci.data2, ci.data3, ci.number_of_loads, ci.load_box_id
  • Filter: (count(*) > $77)
194.          

Initplan (forGroupAggregate)

195. 0.000 0.000 ↓ 0.0

CTE Scan on params params_37 (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

196. 0.000 0.000 ↓ 0.0

Sort (cost=393,674.29..393,741.57 rows=26,911 width=220) (actual rows= loops=)

  • Sort Key: ci.service_order_id, ci.collector_id, ci.collector_start, ci.collector_end, p.mobile_id_from, p.field_from, ci.the_geom, ci.data_code, ci.data1, ci.data2, ci.data3, ci.number_of_loads, ci.load_box_id
197. 0.000 0.000 ↓ 0.0

Nested Loop (cost=11.02..388,841.69 rows=26,911 width=220) (actual rows= loops=)

198. 0.000 0.000 ↓ 0.0

CTE Scan on serviceorders_collectorsintervals ci (cost=0.00..24.38 rows=1,219 width=196) (actual rows= loops=)

199. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=11.02..329.76 rows=22 width=24) (actual rows= loops=)

  • Hash Cond: (p.mobile_id_from = harvesters_used.id)
200. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=7.48..325.91 rows=22 width=24) (actual rows= loops=)

201. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=6.91..136.99 rows=22 width=12) (actual rows= loops=)

  • Hash Cond: (p.field_from = fields_used.id)
202. 0.000 0.000 ↓ 0.0

Limit (cost=0.47..129.90 rows=32 width=68) (actual rows= loops=)

203.          

Initplan (forLimit)

204. 0.000 0.000 ↓ 0.0

CTE Scan on params params_38 (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

205. 0.000 0.000 ↓ 0.0

CTE Scan on params params_39 (cost=0.00..0.02 rows=1 width=4) (actual rows= loops=)

206. 0.000 0.000 ↓ 0.0

Index Scan using tracking_pairs_mobile_id_to_gps_time_to_idx on pairs p (cost=0.43..1,278.54 rows=316 width=68) (actual rows= loops=)

  • Index Cond: ((mobile_id_to = ci.collector_id) AND (gps_time_to >= ci.collector_start) AND (gps_time_to <= ci.collector_end))
  • Filter: (field_from <> $78)
207. 0.000 0.000 ↓ 0.0

Hash (cost=3.96..3.96 rows=198 width=4) (actual rows= loops=)

208. 0.000 0.000 ↓ 0.0

CTE Scan on fields_used (cost=0.00..3.96 rows=198 width=4) (actual rows= loops=)

209. 0.000 0.000 ↓ 0.0

Index Scan using data_records2_pk on data_records dr_3 (cost=0.57..8.59 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (id = p.data_records_id_to)
210. 0.000 0.000 ↓ 0.0

Hash (cost=2.18..2.18 rows=109 width=4) (actual rows= loops=)

211. 0.000 0.000 ↓ 0.0

CTE Scan on harvesters_used (cost=0.00..2.18 rows=109 width=4) (actual rows= loops=)