explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZxuO

Settings
# exclusive inclusive rows x rows loops node
1. 0.114 1,355.959 ↓ 24.0 24 1

Sort (cost=1,906,840.68..1,906,840.69 rows=1 width=896) (actual time=1,355.948..1,355.959 rows=24 loops=1)

  • Sort Key: ns.order_column, ns.employee_name
  • Sort Method: quicksort Memory: 28kB
2.          

CTE prior_schedule

3. 0.002 539.595 ↓ 0.0 0 1

HashAggregate (cost=1,906,507.44..1,906,507.76 rows=1 width=68) (actual time=539.595..539.595 rows=0 loops=1)

  • Group Key: em.payroll_id, ti.start_date, ap.first_name, ap.last_name, ap.middle_name, ti.end_date, ti.end_time, ti.start_time, em.id, lo.id, ti.shift_type, ti.extra
4. 0.015 539.593 ↓ 0.0 0 1

Hash Right Join (cost=1,329.68..1,906,507.41 rows=1 width=68) (actual time=539.593..539.593 rows=0 loops=1)

  • Hash Cond: (bt.timeblock_id = ti.id)
  • Join Filter: CASE WHEN ((date_part('timezone'::text, ('now'::cstring)::time with time zone) / '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, ('now'::cstring)::time with time zone) / '3600'::double precision) = '-5'::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
5. 0.000 0.000 ↓ 0.0 0

Seq Scan on break_time bt (cost=0.00..1,728,491.86 rows=47,116,177 width=28) (never executed)

  • Filter: (paid = 'N'::bpchar)
6. 0.002 539.578 ↓ 0.0 0 1

Hash (cost=1,329.67..1,329.67 rows=1 width=82) (actual time=539.578..539.578 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
7. 0.001 539.576 ↓ 0.0 0 1

Nested Loop (cost=1,192.27..1,329.67 rows=1 width=82) (actual time=539.576..539.576 rows=0 loops=1)

  • Join Filter: (ti.employee_id = em.id)
8. 0.002 539.575 ↓ 0.0 0 1

Nested Loop (cost=1,191.85..1,299.04 rows=2 width=95) (actual time=539.575..539.575 rows=0 loops=1)

9. 0.001 539.573 ↓ 0.0 0 1

Nested Loop (cost=1,191.42..1,269.58 rows=12 width=72) (actual time=539.573..539.573 rows=0 loops=1)

10. 4.698 539.572 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,191.00..1,233.29 rows=1 width=64) (actual time=539.572..539.572 rows=0 loops=1)

  • Filter: CASE WHEN ((date_part('timezone'::text, ('now'::cstring)::time with time zone) / '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, ('now'::cstring)::time with time zone) / '3600'::double precision) = '-5'::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: 876
11. 1.933 528.742 ↓ 876.0 876 1

Nested Loop (cost=1,190.57..1,230.65 rows=1 width=75) (actual time=67.842..528.742 rows=876 loops=1)

12. 2.068 523.305 ↓ 876.0 876 1

Nested Loop (cost=1,190.15..1,228.19 rows=1 width=83) (actual time=67.804..523.305 rows=876 loops=1)

13. 21.650 516.857 ↓ 876.0 876 1

Nested Loop (cost=1,189.73..1,225.74 rows=1 width=83) (actual time=67.752..516.857 rows=876 loops=1)

  • Join Filter: (dep.id = sc.department_id)
  • Rows Removed by Join Filter: 7884
14. 17.812 468.927 ↓ 8,760.0 8,760 1

Nested Loop (cost=1,189.30..1,223.28 rows=1 width=103) (actual time=67.383..468.927 rows=8,760 loops=1)

15. 0.115 4.495 ↓ 10.0 10 1

Nested Loop (cost=4.90..23.47 rows=1 width=33) (actual time=0.804..4.495 rows=10 loops=1)

16. 0.050 0.360 ↓ 10.0 10 1

Nested Loop (cost=0.86..18.40 rows=1 width=17) (actual time=0.154..0.360 rows=10 loops=1)

17. 0.180 0.180 ↓ 3.3 10 1

Index Scan using i_department_id on department dep (cost=0.29..13.74 rows=3 width=4) (actual time=0.049..0.180 rows=10 loops=1)

  • Index Cond: (id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::integer[]))
18. 0.030 0.130 ↑ 1.0 1 10

Materialize (cost=0.58..4.63 rows=1 width=13) (actual time=0.011..0.013 rows=1 loops=10)

19. 0.009 0.100 ↑ 1.0 1 1

Nested Loop (cost=0.58..4.63 rows=1 width=13) (actual time=0.094..0.100 rows=1 loops=1)

20. 0.056 0.056 ↑ 1.0 1 1

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

  • Index Cond: (id = 114106)
21. 0.035 0.035 ↑ 1.0 1 1

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

  • Index Cond: (id = lo.corporation_id)
  • Filter: (deleted = 'N'::bpchar)
22. 0.310 4.020 ↑ 1.0 1 10

Bitmap Heap Scan on department_location de_lo (cost=4.04..5.06 rows=1 width=16) (actual time=0.398..0.402 rows=1 loops=10)

  • Recheck Cond: ((department_id = dep.id) AND (location_id = 114106))
  • Filter: (deleted = 'N'::bpchar)
  • Rows Removed by Filter: 8
  • Heap Blocks: exact=59
23. 0.680 3.710 ↓ 0.0 0 10

BitmapAnd (cost=4.04..4.04 rows=1 width=0) (actual time=0.371..0.371 rows=0 loops=10)

24. 2.670 2.670 ↓ 32.2 1,673 10

Bitmap Index Scan on department_location_dep_idx (cost=0.00..1.81 rows=52 width=0) (actual time=0.267..0.267 rows=1,673 loops=10)

  • Index Cond: (department_id = dep.id)
25. 0.360 0.360 ↓ 2.6 188 10

Bitmap Index Scan on department_location_loc_idx (cost=0.00..1.97 rows=73 width=0) (actual time=0.036..0.036 rows=188 loops=10)

  • Index Cond: (location_id = 114106)
26. 9.950 446.620 ↓ 58.4 876 10

Bitmap Heap Scan on timeblock ti (cost=1,184.39..1,199.66 rows=15 width=86) (actual time=43.819..44.662 rows=876 loops=10)

  • Recheck Cond: ((location_id = 114106) AND (start_date >= '2020-01-25'::date) AND (start_date <= '2020-02-08'::date))
  • Heap Blocks: exact=1050
27. 86.290 436.670 ↓ 0.0 0 10

BitmapAnd (cost=1,184.39..1,184.39 rows=15 width=0) (actual time=43.667..43.667 rows=0 loops=10)

28. 205.510 205.510 ↓ 7.5 147,293 10

Bitmap Index Scan on i_timeblock_loc (cost=0.00..203.34 rows=19,703 width=0) (actual time=20.551..20.551 rows=147,293 loops=10)

  • Index Cond: (location_id = 114106)
29. 144.870 144.870 ↓ 1.4 107,585 10

Bitmap Index Scan on i_timeblock_start_nodel (cost=0.00..980.80 rows=76,923 width=0) (actual time=14.487..14.487 rows=107,585 loops=10)

  • Index Cond: ((start_date >= '2020-01-25'::date) AND (start_date <= '2020-02-08'::date))
30. 26.280 26.280 ↑ 1.0 1 8,760

Index Scan using schedule_pkey on schedule sc (cost=0.43..2.45 rows=1 width=16) (actual time=0.003..0.003 rows=1 loops=8,760)

  • Index Cond: (id = ti.schedule_id)
31. 4.380 4.380 ↑ 1.0 1 876

Index Scan using i_position_id on "position" po (cost=0.42..2.45 rows=1 width=24) (actual time=0.004..0.005 rows=1 loops=876)

  • Index Cond: (id = ti.position_id)
  • Filter: (location_id = 114106)
32. 3.504 3.504 ↑ 1.0 1 876

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

  • Index Cond: (id = po.position_code_id)
  • Filter: (deleted = 'N'::bpchar)
33. 6.132 6.132 ↑ 1.0 1 876

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

  • Index Cond: ((postal_code)::text = split_part((lo.zip)::text, '-'::text, 1))
34. 0.000 0.000 ↓ 0.0 0

Index Scan using user_loc_loc_id on user_location ul (cost=0.42..36.04 rows=25 width=16) (never executed)

  • Index Cond: (location_id = 114106)
  • Filter: ((termination_date IS NULL) OR (termination_date >= '2020-02-08 00:00:00'::timestamp without time zone))
35. 0.000 0.000 ↓ 0.0 0

Index Scan using i_au_id_del on app_user ap (cost=0.42..2.45 rows=1 width=23) (never executed)

  • Index Cond: (id = ul.user_id)
36. 0.000 0.000 ↓ 0.0 0

Index Scan using i_emp_user_id on employee em (cost=0.42..15.30 rows=1 width=19) (never executed)

  • Index Cond: (user_id = ap.id)
  • Filter: (id = ANY ('{91672447,24137827,94214053,14294648,95083521,19048609,92146306,88003096,94514269,87697352,17971146,92500768,92801207,87906652,92170354,94185669,19043187,20739895,19043387,94224220,93795134,19043185,17971287,92097293,92254685,93417831,94987951,44372285,19041151,85628241,47686780,86173098,95084812,91849701,92848760,92426540,91401606,92146308,94289481,88468242,19041160,50540711,20739810,47686729,89828632,94224219,19041161,94460520,19041136,19041153,19042027,72165658,88123553,19041138,19041146,93185582,49231582,19041148,19041169,95084821,93994593,63331863,95074069,85832697,86248146,87105440,90921896,19041150,19041145,91556927,91024699,94991158,92588221,19045651,21175130,19041195,19041894,93698765,19041816,88123552,95019242,92146307,87553971,95083436,90683166,19045627,95074677,19177944,19041193,94877063,93240899,47686730,88106509,88498584,93163779,19460265,19829476,19045632,95034204,94948427,19041173,93071175,93473045,95083987,94264083,95083520,92027616}'::bigint[]))
37.          

CTE final

38. 13.660 1,355.739 ↓ 24.0 24 1

HashAggregate (cost=332.45..332.89 rows=1 width=666) (actual time=1,355.095..1,355.739 rows=24 loops=1)

  • Group Key: em_1.payroll_id, ti_1.start_date, ap_1.first_name, ap_1.last_name, ap_1.middle_name, ps.schedule_duration, ps.start_date, ps.start_time, ps.end_time, ti_1.end_date, ti_1.end_time, ti_1.start_time, ti_1.shift_type, ti_3.shift_type, ss.pickedup, ps.payroll_id, ps.ename, ti_1.extra, ps.extra
39. 0.127 1,342.079 ↓ 26.0 26 1

Hash Full Join (cost=138.24..332.40 rows=1 width=666) (actual time=558.929..1,342.079 rows=26 loops=1)

  • Hash Cond: ((ti_1.start_date = ps.start_date) AND (lo_1.id = ps.location_id) AND (ti_1.employee_id = ps.employee_id))
  • Filter: (COALESCE(sc_1.deleted, 'N'::bpchar) = 'N'::bpchar)
40. 0.092 802.354 ↓ 26.0 26 1

Nested Loop Left Join (cost=138.20..332.34 rows=1 width=76) (actual time=19.305..802.354 rows=26 loops=1)

41. 0.103 801.352 ↓ 26.0 26 1

Nested Loop Left Join (cost=136.92..324.95 rows=1 width=80) (actual time=19.295..801.352 rows=26 loops=1)

42. 0.154 799.425 ↓ 24.0 24 1

Nested Loop (cost=136.36..319.84 rows=1 width=84) (actual time=19.247..799.425 rows=24 loops=1)

  • Join Filter: (em_1.user_id = ul_1.user_id)
43. 0.113 790.151 ↓ 24.0 24 1

Nested Loop (cost=122.33..304.78 rows=1 width=100) (actual time=18.364..790.151 rows=24 loops=1)

44. 0.055 789.750 ↓ 24.0 24 1

Nested Loop Left Join (cost=121.90..302.32 rows=1 width=77) (actual time=18.319..789.750 rows=24 loops=1)

45. 0.088 789.503 ↓ 24.0 24 1

Nested Loop (cost=120.62..294.90 rows=1 width=75) (actual time=18.300..789.503 rows=24 loops=1)

46. 0.097 789.247 ↓ 24.0 24 1

Nested Loop (cost=120.20..292.45 rows=1 width=83) (actual time=18.278..789.247 rows=24 loops=1)

47. 0.194 788.982 ↓ 24.0 24 1

Nested Loop (cost=119.78..289.99 rows=1 width=83) (actual time=18.260..788.982 rows=24 loops=1)

48. 0.136 788.500 ↓ 24.0 24 1

Nested Loop (cost=119.49..278.48 rows=1 width=99) (actual time=18.236..788.500 rows=24 loops=1)

49. 0.058 781.764 ↓ 24.0 24 1

Nested Loop (cost=115.65..273.60 rows=1 width=99) (actual time=17.939..781.764 rows=24 loops=1)

50. 0.097 781.514 ↓ 24.0 24 1

Nested Loop (cost=115.36..271.29 rows=1 width=91) (actual time=17.924..781.514 rows=24 loops=1)

51. 0.523 781.225 ↓ 24.0 24 1

Nested Loop (cost=114.93..268.83 rows=1 width=89) (actual time=17.908..781.225 rows=24 loops=1)

52. 1.528 1.528 ↓ 107.0 107 1

Index Scan using idx_employee_id_del on employee em_1 (cost=0.42..153.29 rows=1 width=19) (actual time=0.044..1.528 rows=107 loops=1)

  • Index Cond: (id = ANY ('{91672447,24137827,94214053,14294648,95083521,19048609,92146306,88003096,94514269,87697352,17971146,92500768,92801207,87906652,92170354,94185669,19043187,20739895,19043387,94224220,93795134,19043185,17971287,92097293,92254685,93417831,94987951,44372285,19041151,85628241,47686780,86173098,95084812,91849701,92848760,92426540,91401606,92146308,94289481,88468242,19041160,50540711,20739810,47686729,89828632,94224219,19041161,94460520,19041136,19041153,19042027,72165658,88123553,19041138,19041146,93185582,49231582,19041148,19041169,95084821,93994593,63331863,95074069,85832697,86248146,87105440,90921896,19041150,19041145,91556927,91024699,94991158,92588221,19045651,21175130,19041195,19041894,93698765,19041816,88123552,95019242,92146307,87553971,95083436,90683166,19045627,95074677,19177944,19041193,94877063,93240899,47686730,88106509,88498584,93163779,19460265,19829476,19045632,95034204,94948427,19041173,93071175,93473045,95083987,94264083,95083520,92027616}'::bigint[]))
53. 64.735 779.174 ↓ 0.0 0 107

Bitmap Heap Scan on timeblock ti_1 (cost=114.51..115.53 rows=1 width=78) (actual time=7.282..7.282 rows=0 loops=107)

  • Recheck Cond: ((employee_id = em_1.id) AND (location_id = 114106) AND (deleted = 'N'::bpchar))
  • Filter: ((start_date >= '2020-01-25'::date) AND (start_date <= '2020-02-08'::date))
  • Rows Removed by Filter: 277
  • Heap Blocks: exact=19018
54. 4.681 714.439 ↓ 0.0 0 107

BitmapAnd (cost=114.51..114.51 rows=1 width=0) (actual time=6.677..6.677 rows=0 loops=107)

55. 9.630 9.630 ↓ 1.1 643 107

Bitmap Index Scan on i_timeblock_emp (cost=0.00..7.09 rows=603 width=0) (actual time=0.090..0.090 rows=643 loops=107)

  • Index Cond: (employee_id = em_1.id)
56. 700.128 700.128 ↓ 6.2 63,833 104

Bitmap Index Scan on i_timeblock_location (cost=0.00..107.17 rows=10,347 width=0) (actual time=6.732..6.732 rows=63,833 loops=104)

  • Index Cond: (location_id = 114106)
57. 0.192 0.192 ↑ 1.0 1 24

Index Scan using schedule_pkey on schedule sc_1 (cost=0.43..2.45 rows=1 width=18) (actual time=0.007..0.008 rows=1 loops=24)

  • Index Cond: (id = ti_1.schedule_id)
58. 0.192 0.192 ↑ 1.0 1 24

Index Scan using idx_location_id_del on location lo_1 (cost=0.29..2.31 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=24)

  • Index Cond: (id = 114106)
59. 0.384 6.600 ↑ 1.0 1 24

Bitmap Heap Scan on department_location de_lo_1 (cost=3.85..4.86 rows=1 width=16) (actual time=0.271..0.275 rows=1 loops=24)

  • Recheck Cond: ((department_id = sc_1.department_id) AND (location_id = 114106))
  • Filter: (deleted = 'N'::bpchar)
  • Rows Removed by Filter: 11
  • Heap Blocks: exact=142
60. 1.416 6.216 ↓ 0.0 0 24

BitmapAnd (cost=3.85..3.85 rows=1 width=0) (actual time=0.259..0.259 rows=0 loops=24)

61. 4.248 4.248 ↓ 32.2 1,674 24

Bitmap Index Scan on department_location_dep_idx (cost=0.00..1.62 rows=52 width=0) (actual time=0.177..0.177 rows=1,674 loops=24)

  • Index Cond: (department_id = sc_1.department_id)
62. 0.552 0.552 ↓ 2.6 188 24

Bitmap Index Scan on department_location_loc_idx (cost=0.00..1.97 rows=73 width=0) (actual time=0.023..0.023 rows=188 loops=24)

  • Index Cond: (location_id = 114106)
63. 0.288 0.288 ↑ 1.0 1 24

Index Scan using i_department_id on department dep_1 (cost=0.29..11.51 rows=1 width=4) (actual time=0.009..0.012 rows=1 loops=24)

  • Index Cond: ((id = sc_1.department_id) AND (id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::integer[])))
64. 0.168 0.168 ↑ 1.0 1 24

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

  • Index Cond: (id = ti_1.position_id)
  • Filter: (location_id = 114106)
65. 0.168 0.168 ↑ 1.0 1 24

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

  • Index Cond: (id = po_1.position_code_id)
  • Filter: (deleted = 'N'::bpchar)
66. 0.048 0.192 ↓ 0.0 0 24

Nested Loop (cost=1.28..7.41 rows=1 width=10) (actual time=0.008..0.008 rows=0 loops=24)

67. 0.048 0.144 ↓ 0.0 0 24

Nested Loop (cost=0.99..5.09 rows=1 width=18) (actual time=0.006..0.006 rows=0 loops=24)

68. 0.096 0.096 ↓ 0.0 0 24

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

  • Index Cond: (timeblock_id = ti_1.id)
  • Filter: ((location_id = 114106) AND (pickedup = 'Y'::bpchar))
69. 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-01-25'::date) AND (start_date <= '2020-02-08'::date))
70. 0.000 0.000 ↓ 0.0 0

Index 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)
71. 0.288 0.288 ↑ 1.0 1 24

Index Scan using i_au_id_del on app_user ap_1 (cost=0.42..2.45 rows=1 width=23) (actual time=0.012..0.012 rows=1 loops=24)

  • Index Cond: (id = em_1.user_id)
72. 0.192 9.120 ↑ 1.0 1 24

Bitmap Heap Scan on user_location ul_1 (cost=14.03..15.05 rows=1 width=16) (actual time=0.380..0.380 rows=1 loops=24)

  • Recheck Cond: ((location_id = 114106) AND (deleted = 'N'::bpchar) AND (user_id = ap_1.id))
  • Filter: ((termination_date IS NULL) OR (termination_date >= '2020-02-08 00:00:00'::timestamp without time zone))
  • Heap Blocks: exact=24
73. 0.864 8.928 ↓ 0.0 0 24

BitmapAnd (cost=14.03..14.03 rows=1 width=0) (actual time=0.372..0.372 rows=0 loops=24)

74. 1.728 1.728 ↓ 9.0 561 24

Bitmap Index Scan on user_loc_loc_id (cost=0.00..1.89 rows=62 width=0) (actual time=0.072..0.072 rows=561 loops=24)

  • Index Cond: (location_id = 114106)
75. 6.336 6.336 ↑ 294.0 3 24

Bitmap Index Scan on user_loc_user_id_all_index (cost=0.00..11.88 rows=882 width=0) (actual time=0.264..0.264 rows=3 loops=24)

  • Index Cond: (user_id = ap_1.id)
76. 1.824 1.824 ↑ 1.0 1 24

Index Scan using i_breaktime_time_id on break_time bt_1 (cost=0.56..5.10 rows=1 width=12) (actual time=0.076..0.076 rows=1 loops=24)

  • Index Cond: (timeblock_id = ti_1.id)
  • Filter: (COALESCE(paid, 'N'::bpchar) = 'N'::bpchar)
77. 0.048 0.910 ↓ 0.0 0 26

Nested Loop (cost=1.28..7.38 rows=1 width=12) (actual time=0.034..0.035 rows=0 loops=26)

78. 0.066 0.832 ↓ 0.0 0 26

Nested Loop (cost=0.99..5.06 rows=1 width=20) (actual time=0.031..0.032 rows=0 loops=26)

79. 0.416 0.416 ↓ 0.0 0 26

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

  • Index Cond: (id = ti_1.parent_id)
  • Filter: ((start_date >= '2020-01-25'::date) AND (start_date <= '2020-02-08'::date) AND (location_id = 114106))
80. 0.350 0.350 ↑ 1.0 1 5

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

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

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

  • Index Cond: (id = 114106)
82. 0.001 539.598 ↓ 0.0 0 1

Hash (cost=0.02..0.02 rows=1 width=624) (actual time=539.598..539.598 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
83. 539.597 539.597 ↓ 0.0 0 1

CTE Scan on prior_schedule ps (cost=0.00..0.02 rows=1 width=624) (actual time=539.597..539.597 rows=0 loops=1)

84. 1,355.845 1,355.845 ↓ 24.0 24 1

CTE Scan on final ns (cost=0.00..0.03 rows=1 width=896) (actual time=1,355.109..1,355.845 rows=24 loops=1)

  • Filter: ((COALESCE(schedule_duration_14_days_prior, '0'::numeric) <> COALESCE(schedule_duration, '0'::numeric)) OR (COALESCE(schedule_start_time_14_days_prior, ' '::text) <> COALESCE(schedule_start_time, ' '::text)) OR (COALESCE(schedule_end_time_14_days_prior, ' '::text) <> COALESCE(schedule_end_time, ' '::text)) OR (COALESCE(schdeule_reg_or_on_call_14_days_prior, ' '::text) <> COALESCE(schdeule_reg_or_on_call, ' '::text)))
Planning time : 146.764 ms
Execution time : 1,358.621 ms