explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Iget

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 238.753 ↑ 1.0 1 1

Nested Loop Left Join (cost=93,279.30..93,287.43 rows=1 width=382) (actual time=238.746..238.753 rows=1 loops=1)

  • Join Filter: (((tt_2.exclusive_origin_id = wto.exclusive_origin_id) OR ((tt_2.exclusive_origin_id IS NULL) AND (wto.exclusive_origin_id IS NULL))) AND ((tt_2.sub_region_id = wto.sub_region_id) OR ((tt_2.sub_region_id IS NULL) AND (wto.sub_region_id IS NULL))) AND (o_1.worker_id = w.id))
2.          

CTE worker_state_machine_trail

3. 0.325 0.325 ↓ 24.4 244 1

Index Scan using idx_state_machine_trail_id_type_smid_created_date on state_machine_trail (cost=0.71..20.27 rows=10 width=124) (actual time=0.024..0.325 rows=244 loops=1)

  • Index Cond: ((entity_id = 396,701) AND (created_date >= (to_timestamp('2019-09-16 10:30:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text) - '2 days'::interval)) AND (created_date <= (to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text) + '2 days'::interval)))
4.          

CTE workers_turn_on

5. 0.000 0.168 ↑ 1.0 1 1

Nested Loop (cost=34.32..125.85 rows=1 width=28) (actual time=0.167..0.168 rows=1 loops=1)

  • Join Filter: (e_1.id = ea_2.state_machine_event_id)
6.          

Initplan (for Nested Loop)

7. 0.007 0.007 ↑ 1.0 1 1

Index Scan using idx_attribute_key_name on attribute_key (cost=0.14..4.25 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: ((name)::text = 'EXCLUSIVE_ORIGIN_ID'::text)
8. 0.007 0.007 ↑ 1.0 1 1

Index Scan using idx_attribute_key_name on attribute_key attribute_key_1 (cost=0.14..4.25 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: ((name)::text = 'SUB_REGION_ID'::text)
9. 0.009 0.009 ↑ 1.0 1 1

Index Scan using idx_attribute_key_name on attribute_key attribute_key_2 (cost=0.14..4.25 rows=1 width=8) (actual time=0.008..0.009 rows=1 loops=1)

  • Index Cond: ((name)::text = 'LOGISTICS_OPERATORS_ID'::text)
10. 0.010 0.010 ↑ 1.0 1 1

Index Scan using idx_subset_name on subset (cost=0.28..4.38 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((name)::text = 'Operador Nuvem'::text)
11. 0.007 0.007 ↑ 1.0 1 1

Index Scan using idx_attribute_key_name on attribute_key attribute_key_3 (cost=0.14..4.25 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: ((name)::text = 'TAG'::text)
12. 0.007 0.007 ↑ 1.0 1 1

Index Scan using idx_attribute_key_name on attribute_key attribute_key_4 (cost=0.14..4.25 rows=1 width=8) (actual time=0.006..0.007 rows=1 loops=1)

  • Index Cond: ((name)::text = 'SHIFT'::text)
13. 0.006 0.006 ↑ 1.0 1 1

Index Scan using idx_attribute_key_name on attribute_key attribute_key_5 (cost=0.14..4.25 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: ((name)::text = 'REGION_ID'::text)
14. 0.009 0.152 ↑ 1.0 1 1

Nested Loop (cost=3.85..16.29 rows=1 width=70) (actual time=0.151..0.152 rows=1 loops=1)

  • Join Filter: (e_1.id = ea_1.state_machine_event_id)
15. 0.007 0.126 ↑ 1.0 1 1

Nested Loop (cost=3.15..13.40 rows=1 width=62) (actual time=0.126..0.126 rows=1 loops=1)

  • Join Filter: (e_1.id = ea.state_machine_event_id)
16. 0.009 0.101 ↑ 1.0 1 1

Nested Loop Left Join (cost=2.44..10.51 rows=1 width=54) (actual time=0.100..0.101 rows=1 loops=1)

17. 0.008 0.086 ↑ 1.0 1 1

Nested Loop Left Join (cost=1.87..8.62 rows=1 width=44) (actual time=0.085..0.086 rows=1 loops=1)

18. 0.011 0.069 ↑ 1.0 1 1

Nested Loop (cost=1.29..6.74 rows=1 width=34) (actual time=0.069..0.069 rows=1 loops=1)

19. 0.030 0.030 ↑ 1.0 1 1

Index Scan using idx_state_machine_event_ei_et_ei_ed on state_machine_event e_1 (cost=0.71..4.83 rows=1 width=16) (actual time=0.030..0.030 rows=1 loops=1)

  • Index Cond: ((entity_id = 396,701) AND ((entity_type)::text = 'WORKER'::text) AND ((event_id)::text = 'TURN_ON'::text) AND (event_date >= to_timestamp('2019-09-16 10:30:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (event_date <= to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Filter: ((event_status)::text = 'ACCEPTED'::text)
20. 0.028 0.028 ↑ 60.0 1 1

Index Scan using state_machine_event_attribute_state_machine_event_id_attrib_key on state_machine_event_attribute logistics_operators_id (cost=0.58..80.63 rows=60 width=18) (actual time=0.028..0.028 rows=1 loops=1)

  • Index Cond: ((state_machine_event_id = e_1.id) AND (attribute_key_id = $3))
  • Filter: ((value)::integer <> $4)
21. 0.009 0.009 ↓ 0.0 0 1

Index Scan using state_machine_event_attribute_state_machine_event_id_attrib_key on state_machine_event_attribute e_exclusive_origin_id (cost=0.58..80.17 rows=61 width=18) (actual time=0.009..0.009 rows=0 loops=1)

  • Index Cond: ((e_1.id = state_machine_event_id) AND (attribute_key_id = $1))
22. 0.006 0.006 ↓ 0.0 0 1

Index Scan using state_machine_event_attribute_state_machine_event_id_attrib_key on state_machine_event_attribute e_sub_regions_id (cost=0.58..80.17 rows=61 width=18) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((e_1.id = state_machine_event_id) AND (attribute_key_id = $2))
23. 0.018 0.018 ↑ 1.0 1 1

Index Only Scan using idx_state_machine_event_attribute_attribute_key_id_value on state_machine_event_attribute ea (cost=0.70..2.79 rows=1 width=8) (actual time=0.018..0.018 rows=1 loops=1)

  • Index Cond: ((attribute_key_id = $5) AND (value = 'REGULAR'::text) AND (state_machine_event_id = logistics_operators_id.state_machine_event_id))
  • Heap Fetches: 1
24. 0.017 0.017 ↑ 1.0 1 1

Index Only Scan using idx_state_machine_event_attribute_attribute_key_id_value on state_machine_event_attribute ea_1 (cost=0.70..2.79 rows=1 width=8) (actual time=0.017..0.017 rows=1 loops=1)

  • Index Cond: ((attribute_key_id = $6) AND (value = 'ALMOCO LOOP'::text) AND (state_machine_event_id = logistics_operators_id.state_machine_event_id))
  • Heap Fetches: 1
25. 0.007 0.007 ↑ 1.0 1 1

Index Scan using state_machine_event_attribute_state_machine_event_id_attrib_key on state_machine_event_attribute ea_2 (cost=0.58..79.55 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=1)

  • Index Cond: ((state_machine_event_id = logistics_operators_id.state_machine_event_id) AND (attribute_key_id = $7))
  • Filter: ((value)::integer = 31)
26.          

CTE shift_orders

27. 0.000 223.766 ↓ 29.0 609 1

Nested Loop (cost=1.14..85,487.56 rows=21 width=801) (actual time=0.775..223.766 rows=609 loops=1)

28. 199.870 199.870 ↓ 8.5 12,401 1

Index Scan using idx_order_delivery_created_date on order_delivery o_2 (cost=0.57..81,479.83 rows=1,452 width=801) (actual time=0.045..199.870 rows=12,401 loops=1)

  • Index Cond: ((created_date >= (to_date('2019-09-16'::text, 'YYYY-MM-DD'::text))::timestamp without time zone) AND (created_date <= to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Filter: ((shift)::text = 'ALMOCO LOOP'::text)
  • Rows Removed by Filter: 215,988
29. 24.802 24.802 ↓ 0.0 0 12,401

Index Only Scan using order_subset_pkey on order_subset os (cost=0.57..2.76 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=12,401)

  • Index Cond: ((order_id = o_2.id) AND (subset_id = 31))
  • Heap Fetches: 175
30.          

CTE turn_on_turn_off_times

31. 0.000 0.498 ↑ 1.0 1 1

Unique (cost=4.74..4.76 rows=1 width=40) (actual time=0.498..0.498 rows=1 loops=1)

32. 0.009 0.498 ↑ 1.0 1 1

Sort (cost=4.74..4.75 rows=1 width=40) (actual time=0.497..0.498 rows=1 loops=1)

  • Sort Key: workers_turn_on.worker_id, workers_turn_on.exclusive_origin_id, workers_turn_on.sub_region_id, (GREATEST((max(turn_on.created_date))::timestamp with time zone, to_timestamp('2019-09-16 10:30:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text))), (LEAST((min(turn_off.created_date))::timestamp with time zone, to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Sort Method: quicksort Memory: 25kB
33. 0.007 0.489 ↑ 1.0 1 1

GroupAggregate (cost=4.59..4.73 rows=1 width=40) (actual time=0.489..0.489 rows=1 loops=1)

  • Group Key: turn_on.id, workers_turn_on.worker_id, workers_turn_on.exclusive_origin_id, workers_turn_on.sub_region_id
  • Filter: (GREATEST((max(turn_on.created_date))::timestamp with time zone, to_timestamp('2019-09-16 10:30:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) < LEAST((min(turn_off.created_date))::timestamp with time zone, to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
34. 0.009 0.482 ↑ 1.0 1 1

Sort (cost=4.59..4.60 rows=1 width=40) (actual time=0.481..0.482 rows=1 loops=1)

  • Sort Key: turn_on.id, workers_turn_on.worker_id, workers_turn_on.exclusive_origin_id, workers_turn_on.sub_region_id
  • Sort Method: quicksort Memory: 25kB
35. 0.002 0.473 ↑ 1.0 1 1

Nested Loop Left Join (cost=0.00..4.58 rows=1 width=40) (actual time=0.460..0.473 rows=1 loops=1)

  • Join Filter: ((turn_on.created_date < turn_off.created_date) AND (turn_on.entity_id = turn_off.entity_id))
  • Rows Removed by Join Filter: 6
36. 0.003 0.323 ↑ 1.0 1 1

Nested Loop (cost=0.00..2.38 rows=1 width=40) (actual time=0.310..0.323 rows=1 loops=1)

  • Join Filter: (workers_turn_on.event_id = turn_on.event_id)
  • Rows Removed by Join Filter: 12
37. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on workers_turn_on (cost=0.00..0.20 rows=1 width=24) (actual time=0.000..0.000 rows=1 loops=1)

38. 0.320 0.320 ↓ 13.0 13 1

CTE Scan on worker_state_machine_trail turn_on (cost=0.00..2.08 rows=1 width=32) (actual time=0.028..0.320 rows=13 loops=1)

  • Filter: (((state_machine_id)::text = 'WORKER_SHIFT_STATE_MACHINE'::text) AND (created_date < to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Rows Removed by Filter: 231
39. 0.148 0.148 ↓ 6.0 6 1

CTE Scan on worker_state_machine_trail turn_off (cost=0.00..2.10 rows=1 width=16) (actual time=0.007..0.148 rows=6 loops=1)

  • Filter: (((current_state)::text = 'OFF_SHIFT'::text) AND ((state_machine_id)::text = 'WORKER_SHIFT_STATE_MACHINE'::text) AND (created_date < to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Rows Removed by Filter: 238
40.          

CTE workers_on_shift_on_off_times

41. 0.003 0.430 ↑ 1.0 1 1

Nested Loop Left Join (cost=4.72..5.15 rows=1 width=32) (actual time=0.429..0.430 rows=1 loops=1)

  • Join Filter: (((wto_1.exclusive_origin_id = tt_3.exclusive_origin_id) OR ((wto_1.exclusive_origin_id IS NULL) AND (tt_3.exclusive_origin_id IS NULL))) AND ((wto_1.sub_region_id = tt_3.sub_region_id) OR ((wto_1.sub_region_id IS NULL) AND (tt_3.sub_region_id IS NULL))) AND "overlaps"(COALESCE((GREATEST((max(on_trail.created_date))::timestamp with time zone, to_timestamp('2019-09-16 10:30:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text))), tt_3.turn_on_time), COALESCE((LEAST((min(off_trail.created_date))::timestamp with time zone, to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text))), tt_3.turn_off_time), tt_3.turn_on_time, tt_3.turn_off_time) AND (wto_1.worker_id = tt_3.worker_id))
42. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on turn_on_turn_off_times tt_3 (cost=0.00..0.20 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

43. 0.000 0.426 ↑ 1.0 1 1

Unique (cost=4.72..4.73 rows=1 width=40) (actual time=0.426..0.426 rows=1 loops=1)

44. 0.011 0.426 ↑ 1.0 1 1

Sort (cost=4.72..4.72 rows=1 width=40) (actual time=0.426..0.426 rows=1 loops=1)

  • Sort Key: wto_1.worker_id, wto_1.exclusive_origin_id, wto_1.sub_region_id, (GREATEST((max(on_trail.created_date))::timestamp with time zone, to_timestamp('2019-09-16 10:30:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text))), (LEAST((min(off_trail.created_date))::timestamp with time zone, to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Sort Method: quicksort Memory: 25kB
45. 0.034 0.415 ↑ 1.0 1 1

GroupAggregate (cost=4.57..4.71 rows=1 width=40) (actual time=0.415..0.415 rows=1 loops=1)

  • Group Key: on_trail.id, wto_1.worker_id, wto_1.exclusive_origin_id, wto_1.sub_region_id
  • Filter: (GREATEST((max(on_trail.created_date))::timestamp with time zone, to_timestamp('2019-09-16 10:30:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) < LEAST((min(off_trail.created_date))::timestamp with time zone, to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Rows Removed by Filter: 10
46. 0.039 0.381 ↓ 121.0 121 1

Sort (cost=4.57..4.57 rows=1 width=40) (actual time=0.376..0.381 rows=121 loops=1)

  • Sort Key: on_trail.id, wto_1.worker_id, wto_1.exclusive_origin_id, wto_1.sub_region_id
  • Sort Method: quicksort Memory: 34kB
47. 0.021 0.342 ↓ 121.0 121 1

Nested Loop Left Join (cost=0.00..4.56 rows=1 width=40) (actual time=0.026..0.342 rows=121 loops=1)

  • Join Filter: ((on_trail.created_date < off_trail.created_date) AND (on_trail.entity_id = off_trail.entity_id))
  • Rows Removed by Join Filter: 66
48. 0.003 0.046 ↓ 11.0 11 1

Nested Loop (cost=0.00..2.40 rows=1 width=40) (actual time=0.017..0.046 rows=11 loops=1)

  • Join Filter: (wto_1.worker_id = on_trail.entity_id)
49. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on workers_turn_on wto_1 (cost=0.00..0.20 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1)

50. 0.041 0.041 ↓ 11.0 11 1

CTE Scan on worker_state_machine_trail on_trail (cost=0.00..2.10 rows=1 width=24) (actual time=0.013..0.041 rows=11 loops=1)

  • Filter: (((current_state)::text = 'ONLINE'::text) AND ((state_machine_id)::text = 'WORKER_CONNECTIVITY_STATE_MACHINE'::text) AND (created_date < to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Rows Removed by Filter: 233
51. 0.275 0.275 ↓ 17.0 17 11

CTE Scan on worker_state_machine_trail off_trail (cost=0.00..2.05 rows=1 width=16) (actual time=0.003..0.025 rows=17 loops=11)

  • Filter: (((current_state)::text = 'OFFLINE'::text) AND ((state_machine_id)::text = 'WORKER_CONNECTIVITY_STATE_MACHINE'::text))
  • Rows Removed by Filter: 227
52.          

CTE workers_on_shift_block_unblock_times

53. 0.008 0.231 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.57..5.13 rows=1 width=32) (actual time=0.231..0.231 rows=0 loops=1)

  • Join Filter: ((((max(wto_2.exclusive_origin_id)) = oo_1.exclusive_origin_id) OR (((max(wto_2.exclusive_origin_id)) IS NULL) AND (oo_1.exclusive_origin_id IS NULL))) AND (((max(wto_2.sub_region_id)) = oo_1.sub_region_id) OR (((max(wto_2.sub_region_id)) IS NULL) AND (oo_1.sub_region_id IS NULL))) AND ((max(wto_2.worker_id)) = oo_1.worker_id))
  • Filter: ((CASE WHEN (((min(block.created_date)) < oo_1.on_time) OR (((min(block.created_date)) IS NULL) AND ((min(unblock.created_date)) IS NOT NULL))) THEN oo_1.on_time ELSE ((min(block.created_date)))::timestamp with time zone END IS NOT NULL) AND (CASE WHEN (((min(unblock.created_date)) > oo_1.off_time) OR (((min(unblock.created_date)) IS NULL) AND ((min(block.created_date)) IS NOT NULL))) THEN oo_1.off_time ELSE ((min(unblock.created_date)))::timestamp with time zone END IS NOT NULL) AND "overlaps"(CASE WHEN (((min(block.created_date)) < oo_1.on_time) OR (((min(block.created_date)) IS NULL) AND ((min(unblock.created_date)) IS NOT NULL))) THEN oo_1.on_time ELSE ((min(block.created_date)))::timestamp with time zone END, CASE WHEN (((min(unblock.created_date)) > oo_1.off_time) OR (((min(unblock.created_date)) IS NULL) AND ((min(block.created_date)) IS NOT NULL))) THEN oo_1.off_time ELSE ((min(unblock.created_date)))::timestamp with time zone END, oo_1.on_time, oo_1.off_time))
  • Rows Removed by Filter: 7
54. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on workers_on_shift_on_off_times oo_1 (cost=0.00..0.20 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

55. 0.008 0.223 ↓ 7.0 7 1

GroupAggregate (cost=4.57..4.69 rows=1 width=40) (actual time=0.218..0.223 rows=7 loops=1)

  • Group Key: block.id
56. 0.014 0.215 ↓ 28.0 28 1

Sort (cost=4.57..4.57 rows=1 width=40) (actual time=0.214..0.215 rows=28 loops=1)

  • Sort Key: block.id
  • Sort Method: quicksort Memory: 27kB
57. 0.006 0.201 ↓ 28.0 28 1

Nested Loop Left Join (cost=0.00..4.56 rows=1 width=40) (actual time=0.014..0.201 rows=28 loops=1)

  • Join Filter: ((unblock.created_date > block.created_date) AND (unblock.entity_id = block.entity_id))
  • Rows Removed by Join Filter: 21
58. 0.002 0.027 ↓ 7.0 7 1

Nested Loop Left Join (cost=0.00..2.38 rows=1 width=40) (actual time=0.007..0.027 rows=7 loops=1)

  • Join Filter: (wto_2.worker_id = block.entity_id)
59. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on workers_turn_on wto_2 (cost=0.00..0.20 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1)

60. 0.025 0.025 ↓ 7.0 7 1

CTE Scan on worker_state_machine_trail block (cost=0.00..2.08 rows=1 width=24) (actual time=0.006..0.025 rows=7 loops=1)

  • Filter: (((current_state)::text = 'BLOCKED'::text) AND ((previous_state)::text = 'UNBLOCKED'::text) AND ((state_machine_id)::text = 'WORKER_BLOCK_STATE_MACHINE'::text))
  • Rows Removed by Filter: 237
61. 0.168 0.168 ↓ 7.0 7 7

CTE Scan on worker_state_machine_trail unblock (cost=0.00..2.08 rows=1 width=16) (actual time=0.005..0.024 rows=7 loops=7)

  • Filter: (((current_state)::text = 'UNBLOCKED'::text) AND ((previous_state)::text = 'BLOCKED'::text) AND ((state_machine_id)::text = 'WORKER_BLOCK_STATE_MACHINE'::text))
  • Rows Removed by Filter: 237
62.          

CTE workers_on_shift_online_working_times

63. 0.004 1.962 ↓ 4.0 4 1

Nested Loop (cost=4.74..5.17 rows=1 width=32) (actual time=1.959..1.962 rows=4 loops=1)

  • Join Filter: (((wto_3.exclusive_origin_id = oo_2.exclusive_origin_id) OR ((wto_3.exclusive_origin_id IS NULL) AND (oo_2.exclusive_origin_id IS NULL))) AND ((wto_3.sub_region_id = oo_2.sub_region_id) OR ((wto_3.sub_region_id IS NULL) AND (oo_2.sub_region_id IS NULL))) AND "overlaps"(COALESCE((GREATEST((max(start_work_trail.created_date))::timestamp with time zone, to_timestamp('2019-09-16 10:30:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text))), oo_2.on_time), COALESCE((LEAST((min(end_work_trail.created_date))::timestamp with time zone, to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text))), oo_2.off_time), oo_2.on_time, oo_2.off_time) AND (oo_2.worker_id = wto_3.worker_id))
64. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on workers_on_shift_on_off_times oo_2 (cost=0.00..0.20 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

65. 0.001 1.958 ↓ 4.0 4 1

Unique (cost=4.74..4.76 rows=1 width=40) (actual time=1.957..1.958 rows=4 loops=1)

66. 0.006 1.957 ↓ 4.0 4 1

Sort (cost=4.74..4.75 rows=1 width=40) (actual time=1.957..1.957 rows=4 loops=1)

  • Sort Key: wto_3.worker_id, wto_3.exclusive_origin_id, wto_3.sub_region_id, (GREATEST((max(start_work_trail.created_date))::timestamp with time zone, to_timestamp('2019-09-16 10:30:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text))), (LEAST((min(end_work_trail.created_date))::timestamp with time zone, to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Sort Method: quicksort Memory: 25kB
67. 0.223 1.951 ↓ 4.0 4 1

GroupAggregate (cost=4.59..4.73 rows=1 width=40) (actual time=1.939..1.951 rows=4 loops=1)

  • Group Key: start_work_trail.id, wto_3.worker_id, wto_3.exclusive_origin_id, wto_3.sub_region_id
  • Filter: (GREATEST((max(start_work_trail.created_date))::timestamp with time zone, to_timestamp('2019-09-16 10:30:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) < LEAST((min(end_work_trail.created_date))::timestamp with time zone, to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Rows Removed by Filter: 38
68. 0.303 1.728 ↓ 1,281.0 1,281 1

Sort (cost=4.59..4.60 rows=1 width=40) (actual time=1.678..1.728 rows=1,281 loops=1)

  • Sort Key: start_work_trail.id, wto_3.worker_id, wto_3.exclusive_origin_id, wto_3.sub_region_id
  • Sort Method: quicksort Memory: 149kB
69. 0.191 1.425 ↓ 1,281.0 1,281 1

Nested Loop Left Join (cost=0.00..4.58 rows=1 width=40) (actual time=0.007..1.425 rows=1,281 loops=1)

  • Join Filter: ((start_work_trail.created_date < end_work_trail.created_date) AND (start_work_trail.entity_id = end_work_trail.entity_id))
  • Rows Removed by Join Filter: 861
70. 0.007 0.058 ↓ 42.0 42 1

Nested Loop (cost=0.00..2.40 rows=1 width=40) (actual time=0.005..0.058 rows=42 loops=1)

  • Join Filter: (wto_3.worker_id = start_work_trail.entity_id)
71. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on workers_turn_on wto_3 (cost=0.00..0.20 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=1)

72. 0.051 0.051 ↓ 42.0 42 1

CTE Scan on worker_state_machine_trail start_work_trail (cost=0.00..2.10 rows=1 width=24) (actual time=0.003..0.051 rows=42 loops=1)

  • Filter: (((current_state)::text = 'WORKING'::text) AND ((state_machine_id)::text = 'WORKER_STATE_MACHINE'::text) AND (created_date < to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)))
  • Rows Removed by Filter: 202
73. 1.176 1.176 ↓ 51.0 51 42

CTE Scan on worker_state_machine_trail end_work_trail (cost=0.00..2.08 rows=1 width=16) (actual time=0.000..0.028 rows=51 loops=42)

  • Filter: (((previous_state)::text = 'WORKING'::text) AND ((current_state)::text = 'FREE'::text) AND ((state_machine_id)::text = 'WORKER_STATE_MACHINE'::text))
  • Rows Removed by Filter: 193
74.          

CTE workers_on_shift_online_blocked_working_times

75. 0.001 0.001 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.52 rows=1 width=32) (actual time=0.000..0.001 rows=0 loops=1)

  • Join Filter: (((t_3.exclusive_origin_id = b.exclusive_origin_id) OR ((t_3.exclusive_origin_id IS NULL) AND (b.exclusive_origin_id IS NULL))) AND ((t_3.sub_region_id = b.sub_region_id) OR ((t_3.sub_region_id IS NULL) AND (b.sub_region_id IS NULL))) AND "overlaps"(t_3.start_work_time, t_3.end_work_time, b.block_time, b.unblock_time) AND (b.worker_id = t_3.worker_id))
76. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on workers_on_shift_block_unblock_times b (cost=0.00..0.20 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=1)

77. 0.000 0.000 ↓ 0.0 0

CTE Scan on workers_on_shift_online_working_times t_3 (cost=0.00..0.20 rows=1 width=32) (never executed)

78. 0.001 238.354 ↑ 1.0 1 1

Nested Loop Left Join (cost=7,605.32..7,613.05 rows=1 width=246) (actual time=238.348..238.354 rows=1 loops=1)

  • Join Filter: (((t_2.exclusive_origin_id = wto.exclusive_origin_id) OR ((t_2.exclusive_origin_id IS NULL) AND (wto.exclusive_origin_id IS NULL))) AND ((t_2.sub_region_id = wto.sub_region_id) OR ((t_2.sub_region_id IS NULL) AND (wto.sub_region_id IS NULL))) AND (t_2.worker_id = w.id))
79. 0.002 238.351 ↑ 1.0 1 1

Nested Loop Left Join (cost=7,605.11..7,612.52 rows=1 width=238) (actual time=238.345..238.351 rows=1 loops=1)

  • Join Filter: (((t_1.exclusive_origin_id = wto.exclusive_origin_id) OR ((t_1.exclusive_origin_id IS NULL) AND (wto.exclusive_origin_id IS NULL))) AND ((t_1.sub_region_id = wto.sub_region_id) OR ((t_1.sub_region_id IS NULL) AND (wto.sub_region_id IS NULL))) AND (t_1.worker_id = w.id))
80. 0.002 236.378 ↑ 1.0 1 1

Nested Loop Left Join (cost=7,604.89..7,612.00 rows=1 width=230) (actual time=236.372..236.378 rows=1 loops=1)

  • Join Filter: (((t.exclusive_origin_id = wto.exclusive_origin_id) OR ((t.exclusive_origin_id IS NULL) AND (wto.exclusive_origin_id IS NULL))) AND ((t.sub_region_id = wto.sub_region_id) OR ((t.sub_region_id IS NULL) AND (wto.sub_region_id IS NULL))) AND (t.worker_id = w.id))
81. 0.004 236.144 ↑ 1.0 1 1

Nested Loop Left Join (cost=7,604.68..7,611.48 rows=1 width=222) (actual time=236.138..236.144 rows=1 loops=1)

  • Join Filter: (((oo.exclusive_origin_id = wto.exclusive_origin_id) OR ((oo.exclusive_origin_id IS NULL) AND (wto.exclusive_origin_id IS NULL))) AND ((oo.sub_region_id = wto.sub_region_id) OR ((oo.sub_region_id IS NULL) AND (wto.sub_region_id IS NULL))) AND (oo.worker_id = w.id))
82. 0.002 235.693 ↑ 1.0 1 1

Nested Loop Left Join (cost=7,604.46..7,610.96 rows=1 width=214) (actual time=235.688..235.693 rows=1 loops=1)

  • Join Filter: (((tt_1.exclusive_origin_id = wto.exclusive_origin_id) OR ((tt_1.exclusive_origin_id IS NULL) AND (wto.exclusive_origin_id IS NULL))) AND ((tt_1.sub_region_id = wto.sub_region_id) OR ((tt_1.sub_region_id IS NULL) AND (wto.sub_region_id IS NULL))) AND (r_1.worker_id = w.id))
83. 0.002 3.057 ↑ 1.0 1 1

Nested Loop Left Join (cost=5,082.24..5,088.38 rows=1 width=166) (actual time=3.052..3.057 rows=1 loops=1)

  • Join Filter: (((tt.exclusive_origin_id = wto.exclusive_origin_id) OR ((tt.exclusive_origin_id IS NULL) AND (wto.exclusive_origin_id IS NULL))) AND ((tt.sub_region_id = wto.sub_region_id) OR ((tt.sub_region_id IS NULL) AND (wto.sub_region_id IS NULL))) AND (r.worker_id = w.id))
84. 0.002 0.236 ↑ 1.0 1 1

Nested Loop (cost=1.64..7.40 rows=1 width=94) (actual time=0.232..0.236 rows=1 loops=1)

85. 0.001 0.220 ↑ 1.0 1 1

Nested Loop (cost=1.21..6.83 rows=1 width=85) (actual time=0.217..0.220 rows=1 loops=1)

86. 0.001 0.210 ↓ 1.5 3 1

Nested Loop (cost=1.07..6.21 rows=2 width=93) (actual time=0.206..0.210 rows=3 loops=1)

87. 0.001 0.196 ↑ 1.0 1 1

Nested Loop (cost=0.64..4.86 rows=1 width=28) (actual time=0.195..0.196 rows=1 loops=1)

88. 0.001 0.184 ↑ 1.0 1 1

Group (cost=0.21..0.23 rows=1 width=20) (actual time=0.183..0.184 rows=1 loops=1)

  • Group Key: wto.worker_id, wto.logistics_operator_id, wto.exclusive_origin_id, wto.sub_region_id
89. 0.012 0.183 ↑ 1.0 1 1

Sort (cost=0.21..0.22 rows=1 width=20) (actual time=0.182..0.183 rows=1 loops=1)

  • Sort Key: wto.worker_id, wto.logistics_operator_id, wto.exclusive_origin_id, wto.sub_region_id
  • Sort Method: quicksort Memory: 25kB
90. 0.171 0.171 ↑ 1.0 1 1

CTE Scan on workers_turn_on wto (cost=0.00..0.20 rows=1 width=20) (actual time=0.170..0.171 rows=1 loops=1)

91. 0.011 0.011 ↑ 1.0 1 1

Index Scan using pk_worker on worker w (cost=0.43..4.54 rows=1 width=16) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (id = wto.worker_id)
92. 0.013 0.013 ↑ 1.3 3 1

Index Scan using user_attribute_user_id_attribute_key_id_key on user_attribute ua (cost=0.43..0.95 rows=4 width=65) (actual time=0.011..0.013 rows=3 loops=1)

  • Index Cond: (user_id = w.user_id)
93. 0.009 0.009 ↓ 0.0 0 3

Index Scan using pk_attribute_key on attribute_key uak (cost=0.14..0.26 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=3)

  • Index Cond: (id = ua.attribute_key_id)
  • Filter: ((name)::text = 'CPF'::text)
  • Rows Removed by Filter: 1
94. 0.014 0.014 ↑ 1.0 1 1

Index Scan using pk_user_fleet on user_fleet u (cost=0.42..0.57 rows=1 width=33) (actual time=0.014..0.014 rows=1 loops=1)

  • Index Cond: (id = w.user_id)
95. 0.110 2.819 ↑ 1.0 1 1

GroupAggregate (cost=5,080.60..5,080.77 rows=1 width=88) (actual time=2.819..2.819 rows=1 loops=1)

  • Group Key: r.worker_id, tt.exclusive_origin_id, tt.sub_region_id
96.          

Initplan (for GroupAggregate)

97. 0.010 0.010 ↑ 1.0 1 1

Index Scan using idx_attribute_key_name on attribute_key ak (cost=0.14..4.25 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)

  • Index Cond: ((name)::text = 'GEOFENCE_VIOLATED'::text)
98. 0.048 2.699 ↓ 128.0 128 1

Sort (cost=5,076.35..5,076.35 rows=1 width=73) (actual time=2.694..2.699 rows=128 loops=1)

  • Sort Key: r.worker_id, tt.exclusive_origin_id, tt.sub_region_id
  • Sort Method: quicksort Memory: 43kB
99. 0.022 2.651 ↓ 128.0 128 1

Nested Loop Left Join (cost=1.85..5,076.34 rows=1 width=73) (actual time=0.741..2.651 rows=128 loops=1)

100. 0.011 2.501 ↓ 32.0 32 1

Nested Loop (cost=1.28..3,195.40 rows=1 width=55) (actual time=0.730..2.501 rows=32 loops=1)

101. 0.002 2.402 ↑ 22.0 4 1

Nested Loop (cost=0.56..1,092.52 rows=88 width=24) (actual time=0.708..2.402 rows=4 loops=1)

102. 0.500 0.500 ↑ 1.0 1 1

CTE Scan on turn_on_turn_off_times tt (cost=0.00..0.20 rows=1 width=32) (actual time=0.499..0.500 rows=1 loops=1)

103. 1.900 1.900 ↑ 22.0 4 1

Index Scan using idx_route_worker_id on route r (cost=0.56..1,083.52 rows=88 width=24) (actual time=0.208..1.900 rows=4 loops=1)

  • Index Cond: (worker_id = tt.worker_id)
  • Filter: ((created_date >= tt.turn_on_time) AND (created_date <= tt.turn_off_time))
  • Rows Removed by Filter: 1,995
104. 0.088 0.088 ↓ 8.0 8 4

Index Scan using idx_state_machine_event_ei_et_ei_ed on state_machine_event e (cost=0.71..23.80 rows=1 width=39) (actual time=0.010..0.022 rows=8 loops=4)

  • Index Cond: ((entity_id = r.id) AND ((entity_type)::text = 'ROUTE'::text) AND (event_date >= to_timestamp('2019-09-16 10:30:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text)) AND (event_date <= (to_timestamp('2019-09-16 14:29:00'::text, 'YYYY-MM-DD HH24:MI:SS'::text) + '01:00:00'::interval)))
  • Filter: (((event_status)::text = 'ACCEPTED'::text) AND ((event_id)::text = ANY ('{CHECK_IN,CHECK_OUT,CANCEL_OFFER_TIMEOUT,REJECT,OFFER,CANCEL_OFFER,ACCEPT,AUTO_CANCEL,CANCEL}'::text[])))
  • Rows Removed by Filter: 7
105. 0.128 0.128 ↑ 349.0 4 32

Index Scan using state_machine_event_attribute_state_machine_event_id_attrib_key on state_machine_event_attribute a (cost=0.58..1,741.34 rows=1,396 width=26) (actual time=0.003..0.004 rows=4 loops=32)

  • Index Cond: (e.id = state_machine_event_id)
106. 0.013 232.634 ↑ 1.0 1 1

GroupAggregate (cost=2,522.22..2,522.37 rows=1 width=64) (actual time=232.634..232.634 rows=1 loops=1)

  • Group Key: r_1.worker_id, tt_1.exclusive_origin_id, tt_1.sub_region_id
107. 0.008 232.621 ↑ 1.0 1 1

Sort (cost=2,522.22..2,522.23 rows=1 width=61) (actual time=232.620..232.621 rows=1 loops=1)

  • Sort Key: r_1.worker_id, tt_1.exclusive_origin_id, tt_1.sub_region_id
  • Sort Method: quicksort Memory: 25kB
108. 0.002 232.613 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,324.21..2,522.21 rows=1 width=61) (actual time=230.789..232.613 rows=1 loops=1)

109. 0.002 232.607 ↑ 1.0 1 1

Nested Loop Left Join (cost=1,323.65..2,469.22 rows=1 width=53) (actual time=230.784..232.607 rows=1 loops=1)

110. 0.012 232.593 ↑ 1.0 1 1

Hash Semi Join (cost=1,323.08..2,415.82 rows=1 width=45) (actual time=230.769..232.593 rows=1 loops=1)

  • Hash Cond: (r_1.id = s.route_id)
111. 0.004 1.996 ↑ 37.0 2 1

Nested Loop (cost=0.56..1,093.10 rows=74 width=45) (actual time=0.174..1.996 rows=2 loops=1)

112. 0.002 0.002 ↑ 1.0 1 1

CTE Scan on turn_on_turn_off_times tt_1 (cost=0.00..0.20 rows=1 width=32) (actual time=0.001..0.002 rows=1 loops=1)

113. 1.990 1.990 ↑ 37.0 2 1

Index Scan using idx_route_worker_id on route r_1 (cost=0.56..1,085.50 rows=74 width=45) (actual time=0.170..1.990 rows=2 loops=1)

  • Index Cond: (worker_id = tt_1.worker_id)
  • Filter: (((route_execution_state)::text = 'COMPLETED'::text) AND (created_date >= tt_1.turn_on_time) AND (created_date <= tt_1.turn_off_time))
  • Rows Removed by Filter: 1,997
114. 0.173 230.585 ↓ 1.9 661 1

Hash (cost=1,287.26..1,287.26 rows=344 width=8) (actual time=230.585..230.585 rows=661 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 34kB
115. 0.876 230.412 ↓ 3.8 1,322 1

Nested Loop (cost=1.14..1,287.26 rows=344 width=8) (actual time=0.805..230.412 rows=1,322 loops=1)

116. 0.031 226.892 ↓ 3.8 1,322 1

Nested Loop (cost=0.57..196.57 rows=344 width=8) (actual time=0.790..226.892 rows=1,322 loops=1)

117. 224.425 224.425 ↓ 29.0 609 1

CTE Scan on shift_orders o (cost=0.00..4.20 rows=21 width=8) (actual time=0.777..224.425 rows=609 loops=1)

118. 2.436 2.436 ↑ 8.0 2 609

Index Scan using idx_loaded_orders_order_id on loaded_orders lo (cost=0.57..7.56 rows=16 width=16) (actual time=0.003..0.004 rows=2 loops=609)

  • Index Cond: (order_id = o.id)
119. 2.644 2.644 ↑ 1.0 1 1,322

Index Scan using pk_service on service s (cost=0.57..3.17 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=1,322)

  • Index Cond: (id = lo.service_id)
120. 0.012 0.012 ↓ 0.0 0 1

Index Scan using route_attribute_route_id_attribute_key_id_key on route_attribute ra (cost=0.56..52.60 rows=8 width=16) (actual time=0.012..0.012 rows=0 loops=1)

  • Index Cond: (route_id = r_1.id)
  • Filter: ((attributes_key_name)::text = 'MIN_FREIGHT_VALUE'::text)
  • Rows Removed by Filter: 3
121. 0.004 0.004 ↓ 0.0 0 1

Index Scan using route_attribute_route_id_attribute_key_id_key on route_attribute ramax (cost=0.56..52.60 rows=4 width=16) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (route_id = r_1.id)
  • Filter: ((attributes_key_name)::text = 'MAX_FREIGHT_VALUE'::text)
  • Rows Removed by Filter: 3
122. 0.014 0.447 ↑ 1.0 1 1

HashAggregate (cost=0.22..0.32 rows=1 width=24) (actual time=0.447..0.447 rows=1 loops=1)

  • Group Key: oo.worker_id, oo.exclusive_origin_id, oo.sub_region_id
123. 0.433 0.433 ↑ 1.0 1 1

CTE Scan on workers_on_shift_on_off_times oo (cost=0.00..0.20 rows=1 width=32) (actual time=0.432..0.433 rows=1 loops=1)

124. 0.000 0.232 ↓ 0.0 0 1

HashAggregate (cost=0.22..0.32 rows=1 width=24) (actual time=0.232..0.232 rows=0 loops=1)

  • Group Key: t.worker_id, t.exclusive_origin_id, t.sub_region_id
125. 0.232 0.232 ↓ 0.0 0 1

CTE Scan on workers_on_shift_block_unblock_times t (cost=0.00..0.20 rows=1 width=32) (actual time=0.232..0.232 rows=0 loops=1)

126. 0.007 1.971 ↑ 1.0 1 1

HashAggregate (cost=0.22..0.32 rows=1 width=24) (actual time=1.971..1.971 rows=1 loops=1)

  • Group Key: t_1.worker_id, t_1.exclusive_origin_id, t_1.sub_region_id
127. 1.964 1.964 ↓ 4.0 4 1

CTE Scan on workers_on_shift_online_working_times t_1 (cost=0.00..0.20 rows=1 width=32) (actual time=1.960..1.964 rows=4 loops=1)

128. 0.001 0.002 ↓ 0.0 0 1

HashAggregate (cost=0.22..0.32 rows=1 width=24) (actual time=0.002..0.002 rows=0 loops=1)

  • Group Key: t_2.worker_id, t_2.exclusive_origin_id, t_2.sub_region_id
129. 0.001 0.001 ↓ 0.0 0 1

CTE Scan on workers_on_shift_online_blocked_working_times t_2 (cost=0.00..0.20 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1)

130. 0.001 0.378 ↑ 1.0 1 1

GroupAggregate (cost=19.57..19.68 rows=1 width=24) (actual time=0.378..0.378 rows=1 loops=1)

  • Group Key: o_1.worker_id, tt_2.exclusive_origin_id, tt_2.sub_region_id
131. 0.008 0.377 ↑ 1.0 1 1

Sort (cost=19.57..19.57 rows=1 width=16) (actual time=0.377..0.377 rows=1 loops=1)

  • Sort Key: o_1.worker_id, tt_2.exclusive_origin_id, tt_2.sub_region_id
  • Sort Method: quicksort Memory: 25kB
132. 0.003 0.369 ↑ 1.0 1 1

Nested Loop (cost=1.70..19.56 rows=1 width=16) (actual time=0.130..0.369 rows=1 loops=1)

  • Join Filter: ((r_2.created_date >= tt_2.turn_on_time) AND (r_2.created_date <= tt_2.turn_off_time))
133. 0.003 0.350 ↓ 4.0 4 1

Nested Loop (cost=1.14..16.57 rows=1 width=40) (actual time=0.116..0.350 rows=4 loops=1)

134. 0.003 0.327 ↓ 4.0 4 1

Nested Loop (cost=0.57..13.72 rows=1 width=40) (actual time=0.102..0.327 rows=4 loops=1)

135. 0.177 0.308 ↑ 1.0 1 1

Nested Loop (cost=0.00..4.56 rows=1 width=40) (actual time=0.085..0.308 rows=1 loops=1)

  • Join Filter: (o_1.worker_id = tt_2.worker_id)
  • Rows Removed by Join Filter: 603
136. 0.131 0.131 ↓ 604.0 604 1

CTE Scan on shift_orders o_1 (cost=0.00..4.25 rows=1 width=16) (actual time=0.001..0.131 rows=604 loops=1)

  • Filter: ((order_routing_state)::text = 'DELIVERED'::text)
  • Rows Removed by Filter: 5
137. 0.000 0.000 ↑ 1.0 1 604

CTE Scan on turn_on_turn_off_times tt_2 (cost=0.00..0.20 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=604)

138. 0.016 0.016 ↑ 4.0 4 1

Index Scan using idx_loaded_orders_order_id on loaded_orders lo_1 (cost=0.57..7.56 rows=16 width=16) (actual time=0.014..0.016 rows=4 loops=1)

  • Index Cond: (order_id = o_1.id)
139. 0.020 0.020 ↑ 1.0 1 4

Index Scan using pk_service on service s_1 (cost=0.57..2.85 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=4)

  • Index Cond: (id = lo_1.service_id)
140. 0.016 0.016 ↓ 0.0 0 4

Index Scan using pk_route on route r_2 (cost=0.56..2.88 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=4)

  • Index Cond: (id = s_1.route_id)
  • Filter: ((route_execution_state)::text = 'COMPLETED'::text)
  • Rows Removed by Filter: 0
Planning time : 9.391 ms
Execution time : 239.917 ms