explain.depesz.com

PostgreSQL's explain analyze made readable

Result: DLu1y

Settings
# exclusive inclusive rows x rows loops node
1. 0.015 760,579.480 ↓ 3.0 9 1

Group (cost=1,927,061.55..1,927,061.69 rows=3 width=896) (actual time=760,579.469..760,579.480 rows=9 loops=1)

  • Group Key: ns.order_column, ns.employee_name, ns.employee_payroll_id, ns.schedule_duration_14_days_prior, ns.schedule_start_date_14_days_prior, ns.schedule_start_time_14_days_prior, ns.schedule_end_time_14_days_prior, ns.schdeule_reg_or_on_call_14_days_prior, ns.schedule_duration, ns.schedule_start_date, ns.schedule_start_time, ns.schedule_end_time, ns.schdeule_reg_or_on_call, ns.employee_or_manager, ns.start_time_adjustment, ns.end_time_adjustment, ns.hours_adjusted
2.          

CTE prior_schedule

3. 0.785 760,387.075 ↓ 9.0 9 1

GroupAggregate (cost=1,926,870.09..1,926,870.70 rows=1 width=176) (actual time=64,137.975..760,387.075 rows=9 loops=1)

  • Group Key: ap.first_name, ap.last_name, ap.middle_name, em.id, lo.id, ti.id
4. 0.102 64,124.879 ↓ 9.0 9 1

Sort (cost=1,926,870.09..1,926,870.10 rows=1 width=76) (actual time=64,124.870..64,124.879 rows=9 loops=1)

  • Sort Key: ap.first_name, ap.last_name, ap.middle_name, em.id, ti.id
  • Sort Method: quicksort Memory: 26kB
5. 7,321.803 64,124.777 ↓ 9.0 9 1

Hash Right Join (cost=274.28..1,926,870.08 rows=1 width=76) (actual time=63,514.405..64,124.777 rows=9 loops=1)

  • Hash Cond: (bt.timeblock_id = ti.id)
  • Join Filter: CASE WHEN ((date_part('timezone'::text, CURRENT_TIME) / '3600'::double precision) = '-4'::double precision) THEN CASE WHEN ((tz.timezone_offset = '-5'::double precision) AND (tz.dst = 'Y'::bpchar)) THEN ((((ti.start_date - '13 days'::interval) - '08:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '08:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-6'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-5'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '07:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '07:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-7'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-6'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '06:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '06:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-8'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-7'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '05:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '05:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) ELSE NULL::boolean END WHEN ((date_part('timezone'::text, CURRENT_TIME) / '3600'::double precision) = '-6'::double precision) THEN CASE WHEN (tz.timezone_offset = '-5'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '08:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '08:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-6'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '07:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '07:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-7'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '06:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '06:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-8'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '05:00:00'::interval) >= bt.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '05:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) ELSE NULL::boolean END ELSE NULL::boolean END
  • Rows Removed by Join Filter: 3
6. 56,314.414 56,314.414 ↑ 1.0 47,752,314 1

Seq Scan on break_time bt (cost=0.00..1,746,893.50 rows=47,920,562 width=28) (actual time=11.042..56,314.414 rows=47,752,314 loops=1)

  • Filter: (paid = 'N'::bpchar)
  • Rows Removed by Filter: 13984690
7. 0.045 488.560 ↓ 9.0 9 1

Hash (cost=274.27..274.27 rows=1 width=82) (actual time=488.560..488.560 rows=9 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
8. 0.262 488.515 ↓ 9.0 9 1

Nested Loop Left Join (cost=235.54..274.27 rows=1 width=82) (actual time=195.421..488.515 rows=9 loops=1)

  • Filter: CASE WHEN ((date_part('timezone'::text, CURRENT_TIME) / '3600'::double precision) = '-4'::double precision) THEN CASE WHEN ((tz.timezone_offset = '-5'::double precision) AND (tz.dst = 'Y'::bpchar)) THEN ((((ti.start_date - '13 days'::interval) - '08:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '08:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-6'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-5'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '07:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '07:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-7'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-6'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '06:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '06:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz.timezone_offset = '-8'::double precision) AND (tz.dst = 'Y'::bpchar)) OR ((tz.timezone_offset = '-7'::double precision) AND (tz.dst = 'N'::bpchar))) THEN ((((ti.start_date - '13 days'::interval) - '05:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '05:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) ELSE NULL::boolean END WHEN ((date_part('timezone'::text, CURRENT_TIME) / '3600'::double precision) = '-6'::double precision) THEN CASE WHEN (tz.timezone_offset = '-5'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '08:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '08:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-6'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '07:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '07:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-7'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '06:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '06:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz.timezone_offset = '-8'::double precision) THEN ((((ti.start_date - '13 days'::interval) - '05:00:00'::interval) >= ti.starttimestamp) AND (((ti.start_date - '13 days'::interval) - '05:00:00'::interval) <= COALESCE(ti.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) ELSE NULL::boolean END ELSE NULL::boolean END
  • Rows Removed by Filter: 18
9. 0.092 487.929 ↓ 27.0 27 1

Nested Loop (cost=235.12..271.63 rows=1 width=93) (actual time=195.303..487.929 rows=27 loops=1)

10. 0.070 485.110 ↓ 27.0 27 1

Nested Loop (cost=219.93..255.41 rows=1 width=109) (actual time=195.107..485.110 rows=27 loops=1)

11. 0.034 484.905 ↓ 27.0 27 1

Nested Loop (cost=219.50..252.97 rows=1 width=86) (actual time=195.081..484.905 rows=27 loops=1)

12. 0.049 484.709 ↓ 27.0 27 1

Nested Loop (cost=219.08..250.53 rows=1 width=94) (actual time=195.058..484.709 rows=27 loops=1)

13. 0.041 484.498 ↓ 27.0 27 1

Nested Loop (cost=218.66..247.90 rows=1 width=94) (actual time=195.035..484.498 rows=27 loops=1)

14. 0.146 484.295 ↓ 27.0 27 1

Nested Loop (cost=218.37..245.59 rows=1 width=102) (actual time=195.001..484.295 rows=27 loops=1)

  • Join Filter: (sc.department_id = dep.id)
15. 0.144 483.879 ↓ 27.0 27 1

Nested Loop (cost=218.08..231.93 rows=1 width=118) (actual time=194.947..483.879 rows=27 loops=1)

16. 0.049 463.323 ↓ 27.0 27 1

Nested Loop (cost=214.05..226.87 rows=1 width=118) (actual time=180.554..463.323 rows=27 loops=1)

17. 0.108 463.085 ↓ 27.0 27 1

Nested Loop (cost=213.76..224.56 rows=1 width=97) (actual time=180.525..463.085 rows=27 loops=1)

18. 0.115 462.734 ↓ 27.0 27 1

Nested Loop (cost=213.33..222.11 rows=1 width=97) (actual time=180.478..462.734 rows=27 loops=1)

19. 0.129 0.129 ↓ 5.0 5 1

Index Scan using idx_employee_id_del on employee em (cost=0.42..8.17 rows=1 width=19) (actual time=0.046..0.129 rows=5 loops=1)

  • Index Cond: (id = ANY ('{94948427,19041173,93071175,95083987,94264083}'::bigint[]))
20. 25.755 462.490 ↓ 5.0 5 5

Bitmap Heap Scan on timeblock ti (cost=212.91..213.93 rows=1 width=86) (actual time=92.469..92.498 rows=5 loops=5)

  • Recheck Cond: ((employee_id = em.id) AND (location_id = 114106))
  • Filter: ((start_date >= '2020-02-01'::date) AND (start_date <= '2020-02-05'::date))
  • Rows Removed by Filter: 262
  • Heap Blocks: exact=1026
21. 24.755 436.735 ↓ 0.0 0 5

BitmapAnd (cost=212.91..212.91 rows=1 width=0) (actual time=87.347..87.347 rows=0 loops=5)

22. 0.300 0.300 ↑ 2.1 276 5

Bitmap Index Scan on i_timeblock_emp (cost=0.00..6.93 rows=582 width=0) (actual time=0.060..0.060 rows=276 loops=5)

  • Index Cond: (employee_id = em.id)
23. 411.680 411.680 ↓ 7.4 148,223 5

Bitmap Index Scan on i_timeblock_loc (cost=0.00..205.72 rows=20,020 width=0) (actual time=82.336..82.336 rows=148,223 loops=5)

  • Index Cond: (location_id = 114106)
24. 0.243 0.243 ↑ 1.0 1 27

Index Scan using schedule_pkey on schedule sc (cost=0.43..2.45 rows=1 width=16) (actual time=0.009..0.009 rows=1 loops=27)

  • Index Cond: (id = ti.schedule_id)
25. 0.189 0.189 ↑ 1.0 1 27

Index Scan using idx_location_id_del on location lo (cost=0.29..2.31 rows=1 width=21) (actual time=0.007..0.007 rows=1 loops=27)

  • Index Cond: (id = 114106)
26. 0.324 20.412 ↑ 1.0 1 27

Bitmap Heap Scan on department_location de_lo (cost=4.03..5.05 rows=1 width=16) (actual time=0.755..0.756 rows=1 loops=27)

  • Recheck Cond: ((department_id = sc.department_id) AND (location_id = 114106))
  • Filter: (deleted = 'N'::bpchar)
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=108
27. 0.918 20.088 ↓ 0.0 0 27

BitmapAnd (cost=4.03..4.03 rows=1 width=0) (actual time=0.744..0.744 rows=0 loops=27)

28. 18.765 18.765 ↓ 33.4 1,701 27

Bitmap Index Scan on department_location_dep_idx (cost=0.00..1.80 rows=51 width=0) (actual time=0.695..0.695 rows=1,701 loops=27)

  • Index Cond: (department_id = sc.department_id)
29. 0.405 0.405 ↓ 1.9 141 27

Bitmap Index Scan on department_location_loc_idx (cost=0.00..1.97 rows=73 width=0) (actual time=0.015..0.015 rows=141 loops=27)

  • Index Cond: (location_id = 114106)
30. 0.270 0.270 ↑ 1.0 1 27

Index Only Scan using i_department_id on department dep (cost=0.29..13.64 rows=1 width=4) (actual time=0.010..0.010 rows=1 loops=27)

  • Index Cond: ((id = de_lo.department_id) AND (id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::integer[])))
  • Heap Fetches: 27
31. 0.162 0.162 ↑ 1.0 1 27

Index Scan using corporation_pkey on corporation co (cost=0.29..2.31 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=27)

  • Index Cond: (id = lo.corporation_id)
  • Filter: (deleted = 'N'::bpchar)
32. 0.162 0.162 ↑ 1.0 1 27

Index Scan using i_position_id on "position" po (cost=0.42..2.44 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=27)

  • Index Cond: (id = ti.position_id)
  • Filter: (location_id = 114106)
33. 0.162 0.162 ↑ 1.0 1 27

Index Scan using position_code_pkey on position_code po_code (cost=0.42..2.44 rows=1 width=8) (actual time=0.006..0.006 rows=1 loops=27)

  • Index Cond: (id = po.position_code_id)
  • Filter: (deleted = 'N'::bpchar)
34. 0.135 0.135 ↑ 1.0 1 27

Index Scan using i_au_id_del on app_user ap (cost=0.42..2.44 rows=1 width=23) (actual time=0.005..0.005 rows=1 loops=27)

  • Index Cond: (id = em.user_id)
35. 0.135 2.727 ↑ 1.0 1 27

Bitmap Heap Scan on user_location ul (cost=15.19..16.21 rows=1 width=16) (actual time=0.100..0.101 rows=1 loops=27)

  • Recheck Cond: ((location_id = 114106) AND (deleted = 'N'::bpchar) AND (user_id = em.user_id))
  • Filter: ((termination_date IS NULL) OR (termination_date >= '2020-02-05 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=27
36. 0.486 2.592 ↓ 0.0 0 27

BitmapAnd (cost=15.19..15.19 rows=1 width=0) (actual time=0.096..0.096 rows=0 loops=27)

37. 1.971 1.971 ↓ 9.6 555 27

Bitmap Index Scan on user_loc_loc_id (cost=0.00..1.86 rows=58 width=0) (actual time=0.073..0.073 rows=555 loops=27)

  • Index Cond: (location_id = 114106)
38. 0.135 0.135 ↑ 885.0 1 27

Bitmap Index Scan on user_loc_user_id_all_index (cost=0.00..13.07 rows=885 width=0) (actual time=0.005..0.005 rows=1 loops=27)

  • Index Cond: (user_id = em.user_id)
39. 0.324 0.324 ↑ 1.0 1 27

Index Scan using timezone_postal_idx on timezone tz (cost=0.43..2.45 rows=1 width=17) (actual time=0.012..0.012 rows=1 loops=27)

  • Index Cond: ((postal_code)::text = split_part((lo.zip)::text, '-'::text, 1))
40.          

SubPlan (for GroupAggregate)

41. 696,261.411 696,261.411 ↑ 1.0 1 9

Function Scan on getactivetimeblockid (cost=0.25..0.26 rows=1 width=8) (actual time=77,362.379..77,362.379 rows=1 loops=9)

42.          

CTE schedule_now

43. 0.936 185.040 ↓ 13.0 13 1

GroupAggregate (cost=190.15..190.51 rows=1 width=206) (actual time=184.744..185.040 rows=13 loops=1)

  • Group Key: ap_1.first_name, ap_1.last_name, ap_1.middle_name, em_1.id, lo_1.id, ti_3.shift_type, ss.pickedup, ti_1.id
44. 0.160 184.104 ↓ 13.0 13 1

Sort (cost=190.15..190.16 rows=1 width=82) (actual time=184.099..184.104 rows=13 loops=1)

  • Sort Key: ap_1.first_name, ap_1.last_name, ap_1.middle_name, em_1.id, ti_3.shift_type, ss.pickedup, ti_1.id
  • Sort Method: quicksort Memory: 26kB
45. 0.036 183.944 ↓ 13.0 13 1

Nested Loop Left Join (cost=140.77..190.14 rows=1 width=82) (actual time=80.372..183.944 rows=13 loops=1)

46. 0.044 183.609 ↓ 13.0 13 1

Nested Loop Left Join (cost=139.49..182.77 rows=1 width=86) (actual time=80.352..183.609 rows=13 loops=1)

47. 0.070 183.279 ↓ 13.0 13 1

Nested Loop (cost=138.92..179.55 rows=1 width=82) (actual time=80.298..183.279 rows=13 loops=1)

48. 0.057 181.649 ↓ 13.0 13 1

Nested Loop (cost=123.74..163.33 rows=1 width=98) (actual time=80.067..181.649 rows=13 loops=1)

49. 0.027 181.423 ↓ 13.0 13 1

Nested Loop Left Join (cost=123.31..160.89 rows=1 width=75) (actual time=80.015..181.423 rows=13 loops=1)

50. 0.036 181.227 ↓ 13.0 13 1

Nested Loop (cost=122.03..153.48 rows=1 width=73) (actual time=79.975..181.227 rows=13 loops=1)

51. 0.045 181.022 ↓ 13.0 13 1

Nested Loop (cost=121.61..151.04 rows=1 width=81) (actual time=79.929..181.022 rows=13 loops=1)

52. 0.121 180.795 ↓ 13.0 13 1

Nested Loop (cost=121.19..148.41 rows=1 width=81) (actual time=79.867..180.795 rows=13 loops=1)

  • Join Filter: (sc_1.department_id = dep_1.id)
53. 0.085 180.440 ↓ 13.0 13 1

Nested Loop (cost=120.90..134.76 rows=1 width=97) (actual time=79.761..180.440 rows=13 loops=1)

54. 0.027 166.614 ↓ 13.0 13 1

Nested Loop (cost=116.87..129.69 rows=1 width=97) (actual time=79.380..166.614 rows=13 loops=1)

55. 0.071 166.366 ↓ 13.0 13 1

Nested Loop (cost=116.58..127.38 rows=1 width=89) (actual time=79.278..166.366 rows=13 loops=1)

56. 0.093 165.970 ↓ 13.0 13 1

Nested Loop (cost=116.15..124.93 rows=1 width=89) (actual time=79.198..165.970 rows=13 loops=1)

57. 0.232 0.232 ↓ 5.0 5 1

Index Scan using idx_employee_id_del on employee em_1 (cost=0.42..8.17 rows=1 width=19) (actual time=0.075..0.232 rows=5 loops=1)

  • Index Cond: (id = ANY ('{94948427,19041173,93071175,95083987,94264083}'::bigint[]))
58. 5.555 165.645 ↓ 3.0 3 5

Bitmap Heap Scan on timeblock ti_1 (cost=115.73..116.75 rows=1 width=78) (actual time=33.114..33.129 rows=3 loops=5)

  • Recheck Cond: ((employee_id = em_1.id) AND (location_id = 114106) AND (deleted = 'N'::bpchar))
  • Filter: ((start_date >= '2020-02-01'::date) AND (start_date <= '2020-02-05'::date))
  • Rows Removed by Filter: 153
  • Heap Blocks: exact=606
59. 27.295 160.090 ↓ 0.0 0 5

BitmapAnd (cost=115.73..115.73 rows=1 width=0) (actual time=32.018..32.018 rows=0 loops=5)

60. 0.440 0.440 ↑ 2.1 276 5

Bitmap Index Scan on i_timeblock_emp (cost=0.00..6.93 rows=582 width=0) (actual time=0.088..0.088 rows=276 loops=5)

  • Index Cond: (employee_id = em_1.id)
61. 132.355 132.355 ↓ 6.1 64,114 5

Bitmap Index Scan on i_timeblock_location (cost=0.00..108.54 rows=10,530 width=0) (actual time=26.471..26.471 rows=64,114 loops=5)

  • Index Cond: (location_id = 114106)
62. 0.325 0.325 ↑ 1.0 1 13

Index Scan using schedule_pkey on schedule sc_1 (cost=0.43..2.45 rows=1 width=16) (actual time=0.025..0.025 rows=1 loops=13)

  • Index Cond: (id = ti_1.schedule_id)
  • Filter: (COALESCE(deleted, 'N'::bpchar) = 'N'::bpchar)
63. 0.221 0.221 ↑ 1.0 1 13

Index Only Scan using idx_location_id_del on location lo_1 (cost=0.29..2.31 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=13)

  • Index Cond: (id = 114106)
  • Heap Fetches: 13
64. 0.234 13.741 ↑ 1.0 1 13

Bitmap Heap Scan on department_location de_lo_1 (cost=4.03..5.05 rows=1 width=16) (actual time=1.055..1.057 rows=1 loops=13)

  • Recheck Cond: ((department_id = sc_1.department_id) AND (location_id = 114106))
  • Filter: (deleted = 'N'::bpchar)
  • Rows Removed by Filter: 4
  • Heap Blocks: exact=52
65. 0.455 13.507 ↓ 0.0 0 13

BitmapAnd (cost=4.03..4.03 rows=1 width=0) (actual time=1.039..1.039 rows=0 loops=13)

66. 12.805 12.805 ↓ 33.5 1,709 13

Bitmap Index Scan on department_location_dep_idx (cost=0.00..1.80 rows=51 width=0) (actual time=0.985..0.985 rows=1,709 loops=13)

  • Index Cond: (department_id = sc_1.department_id)
67. 0.247 0.247 ↓ 1.9 141 13

Bitmap Index Scan on department_location_loc_idx (cost=0.00..1.97 rows=73 width=0) (actual time=0.019..0.019 rows=141 loops=13)

  • Index Cond: (location_id = 114106)
68. 0.234 0.234 ↑ 1.0 1 13

Index Only Scan using i_department_id on department dep_1 (cost=0.29..13.64 rows=1 width=4) (actual time=0.018..0.018 rows=1 loops=13)

  • Index Cond: ((id = de_lo_1.department_id) AND (id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::integer[])))
  • Heap Fetches: 13
69. 0.182 0.182 ↑ 1.0 1 13

Index Scan using i_position_id on "position" po_1 (cost=0.42..2.44 rows=1 width=24) (actual time=0.014..0.014 rows=1 loops=13)

  • Index Cond: (id = ti_1.position_id)
  • Filter: (location_id = 114106)
70. 0.169 0.169 ↑ 1.0 1 13

Index Scan using position_code_pkey on position_code po_code_1 (cost=0.42..2.44 rows=1 width=8) (actual time=0.013..0.013 rows=1 loops=13)

  • Index Cond: (id = po_1.position_code_id)
  • Filter: (deleted = 'N'::bpchar)
71. 0.013 0.169 ↓ 0.0 0 13

Nested Loop (cost=1.28..7.40 rows=1 width=10) (actual time=0.013..0.013 rows=0 loops=13)

72. 0.013 0.156 ↓ 0.0 0 13

Nested Loop (cost=0.99..5.08 rows=1 width=18) (actual time=0.012..0.012 rows=0 loops=13)

73. 0.143 0.143 ↓ 0.0 0 13

Index Scan using idx_shift_swap on shift_swap ss (cost=0.42..2.49 rows=1 width=18) (actual time=0.011..0.011 rows=0 loops=13)

  • Index Cond: (timeblock_id = ti_1.id)
  • Filter: ((location_id = 114106) AND (pickedup = 'Y'::bpchar))
74. 0.000 0.000 ↓ 0.0 0

Index Scan using timeblock_pkey on timeblock ti_2 (cost=0.57..2.59 rows=1 width=8) (never executed)

  • Index Cond: (id = ss.timeblock_id)
  • Filter: ((start_date >= '2020-02-01'::date) AND (start_date <= '2020-02-05'::date))
75. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_location_id_del on location lo_2 (cost=0.29..2.31 rows=1 width=8) (never executed)

  • Index Cond: (id = 114106)
  • Heap Fetches: 0
76. 0.169 0.169 ↑ 1.0 1 13

Index Scan using i_au_id_del on app_user ap_1 (cost=0.42..2.44 rows=1 width=23) (actual time=0.013..0.013 rows=1 loops=13)

  • Index Cond: (id = em_1.user_id)
77. 0.091 1.560 ↑ 1.0 1 13

Bitmap Heap Scan on user_location ul_1 (cost=15.19..16.21 rows=1 width=16) (actual time=0.120..0.120 rows=1 loops=13)

  • Recheck Cond: ((location_id = 114106) AND (deleted = 'N'::bpchar) AND (user_id = em_1.user_id))
  • Filter: ((termination_date IS NULL) OR (termination_date >= '2020-02-05 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=13
78. 0.247 1.469 ↓ 0.0 0 13

BitmapAnd (cost=15.19..15.19 rows=1 width=0) (actual time=0.113..0.113 rows=0 loops=13)

79. 1.092 1.092 ↓ 9.6 555 13

Bitmap Index Scan on user_loc_loc_id (cost=0.00..1.86 rows=58 width=0) (actual time=0.084..0.084 rows=555 loops=13)

  • Index Cond: (location_id = 114106)
80. 0.130 0.130 ↑ 442.5 2 13

Bitmap Index Scan on user_loc_user_id_all_index (cost=0.00..13.07 rows=885 width=0) (actual time=0.010..0.010 rows=2 loops=13)

  • Index Cond: (user_id = em_1.user_id)
81. 0.286 0.286 ↑ 1.0 1 13

Index Scan using i_breaktime_time_id on break_time bt_1 (cost=0.56..3.21 rows=1 width=12) (actual time=0.022..0.022 rows=1 loops=13)

  • Index Cond: (timeblock_id = ti_1.id)
  • Filter: (COALESCE(paid, 'N'::bpchar) = 'N'::bpchar)
82. 0.022 0.299 ↓ 0.0 0 13

Nested Loop (cost=1.28..7.37 rows=1 width=12) (actual time=0.023..0.023 rows=0 loops=13)

83. 0.021 0.247 ↓ 0.0 0 13

Nested Loop (cost=0.99..5.05 rows=1 width=20) (actual time=0.019..0.019 rows=0 loops=13)

84. 0.130 0.130 ↓ 0.0 0 13

Index Scan using timeblock_pkey on timeblock ti_3 (cost=0.57..2.59 rows=1 width=28) (actual time=0.010..0.010 rows=0 loops=13)

  • Index Cond: (id = ti_1.parent_id)
  • Filter: ((start_date >= '2020-02-01'::date) AND (start_date <= '2020-02-05'::date) AND (location_id = 114106))
85. 0.096 0.096 ↑ 1.0 1 6

Index Scan using i_schedule_id on schedule sc_2 (cost=0.43..2.46 rows=1 width=8) (actual time=0.016..0.016 rows=1 loops=6)

  • Index Cond: (id = ti_3.schedule_id)
  • Filter: (department_id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::bigint[]))
86. 0.030 0.030 ↑ 1.0 1 6

Index Only Scan using idx_location_id_del on location lo_3 (cost=0.29..2.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=6)

  • Index Cond: (id = 114106)
  • Heap Fetches: 6
87.          

CTE final

88. 0.005 760,572.403 ↓ 3.0 9 1

Append (cost=0.00..0.26 rows=3 width=896) (actual time=760,571.957..760,572.403 rows=9 loops=1)

89. 0.082 760,572.321 ↓ 9.0 9 1

Nested Loop (cost=0.00..0.10 rows=1 width=896) (actual time=760,571.956..760,572.321 rows=9 loops=1)

  • Join Filter: ((sn.start_date = ps.start_date) AND (sn.location_id = ps.location_id) AND (sn.employee_id = ps.employee_id) AND (sn.time_block_id = ps.time_block_id))
  • Rows Removed by Join Filter: 108
90. 185.079 185.079 ↓ 13.0 13 1

CTE Scan on schedule_now sn (cost=0.00..0.02 rows=1 width=688) (actual time=184.751..185.079 rows=13 loops=1)

91. 760,387.160 760,387.160 ↓ 9.0 9 13

CTE Scan on prior_schedule ps (cost=0.00..0.02 rows=1 width=656) (actual time=4,933.692..58,491.320 rows=9 loops=13)

92. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on prior_schedule ps_1 (cost=0.00..0.03 rows=1 width=896) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (time_block_id IS NULL)
  • Rows Removed by Filter: 9
93. 0.030 0.075 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.10 rows=1 width=896) (actual time=0.075..0.075 rows=0 loops=1)

  • Join Filter: ((ps_2.time_block_id <> sn_1.time_block_id) AND (sn_1.start_date = ps_2.start_date) AND (sn_1.location_id = ps_2.location_id) AND (sn_1.employee_id = ps_2.employee_id))
  • Rows Removed by Join Filter: 117
94. 0.006 0.006 ↓ 13.0 13 1

CTE Scan on schedule_now sn_1 (cost=0.00..0.02 rows=1 width=688) (actual time=0.002..0.006 rows=13 loops=1)

95. 0.039 0.039 ↓ 9.0 9 13

CTE Scan on prior_schedule ps_2 (cost=0.00..0.02 rows=1 width=656) (actual time=0.000..0.003 rows=9 loops=13)

96. 7.042 760,579.465 ↓ 3.0 9 1

Sort (cost=0.08..0.09 rows=3 width=896) (actual time=760,579.464..760,579.465 rows=9 loops=1)

  • Sort Key: ns.order_column, ns.employee_name, ns.employee_payroll_id, ns.schedule_duration_14_days_prior, ns.schedule_start_date_14_days_prior, ns.schedule_start_time_14_days_prior, ns.schedule_end_time_14_days_prior, ns.schdeule_reg_or_on_call_14_days_prior, ns.schedule_duration, ns.schedule_start_date, ns.schedule_start_time, ns.schedule_end_time, ns.schdeule_reg_or_on_call, ns.employee_or_manager, ns.start_time_adjustment, ns.end_time_adjustment, ns.hours_adjusted
  • Sort Method: quicksort Memory: 27kB
97. 760,572.423 760,572.423 ↓ 3.0 9 1

CTE Scan on final ns (cost=0.00..0.06 rows=3 width=896) (actual time=760,571.962..760,572.423 rows=9 loops=1)

Planning time : 262.162 ms
Execution time : 760,590.366 ms