explain.depesz.com

PostgreSQL's explain analyze made readable

Result: celg

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

Group (cost=1,927,582.64..1,927,582.80 rows=3 width=936) (actual time=1.144..1.144 rows=0 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, ns.cost_adjusted
2.          

CTE prior_schedule

3. 0.000 0.122 ↓ 0.0 0 1

GroupAggregate (cost=1,927,199.39..1,927,199.99 rows=1 width=184) (actual time=0.122..0.122 rows=0 loops=1)

  • Group Key: ap.first_name, ap.last_name, ap.middle_name, em.id, lo.id, ti.id
4. 0.013 0.122 ↓ 0.0 0 1

Sort (cost=1,927,199.39..1,927,199.39 rows=1 width=84) (actual time=0.122..0.122 rows=0 loops=1)

  • Sort Key: ap.first_name, ap.last_name, ap.middle_name, em.id, lo.id, ti.id
  • Sort Method: quicksort Memory: 25kB
5. 0.020 0.109 ↓ 0.0 0 1

Hash Right Join (cost=603.58..1,927,199.38 rows=1 width=84) (actual time=0.109..0.109 rows=0 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
6. 0.000 0.000 ↓ 0.0 0

Seq Scan on break_time bt (cost=0.00..1,746,893.50 rows=47,920,562 width=28) (never executed)

  • Filter: (paid = 'N'::bpchar)
7. 0.001 0.089 ↓ 0.0 0 1

Hash (cost=603.56..603.56 rows=1 width=90) (actual time=0.089..0.089 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
8. 0.000 0.088 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.83..603.56 rows=1 width=90) (actual time=0.088..0.088 rows=0 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
9. 0.000 0.088 ↓ 0.0 0 1

Nested Loop (cost=4.40..600.92 rows=1 width=101) (actual time=0.088..0.088 rows=0 loops=1)

  • Join Filter: (ti.location_id = ul.location_id)
10. 0.000 0.088 ↓ 0.0 0 1

Nested Loop (cost=3.97..577.56 rows=1 width=141) (actual time=0.088..0.088 rows=0 loops=1)

11. 0.001 0.088 ↓ 0.0 0 1

Nested Loop (cost=3.55..575.12 rows=1 width=118) (actual time=0.088..0.088 rows=0 loops=1)

12. 0.000 0.087 ↓ 0.0 0 1

Nested Loop (cost=3.13..572.68 rows=1 width=126) (actual time=0.087..0.087 rows=0 loops=1)

13. 0.000 0.087 ↓ 0.0 0 1

Nested Loop (cost=2.71..570.23 rows=1 width=118) (actual time=0.087..0.087 rows=0 loops=1)

14. 0.000 0.087 ↓ 0.0 0 1

Nested Loop (cost=2.42..567.92 rows=1 width=126) (actual time=0.087..0.087 rows=0 loops=1)

  • Join Filter: (sc.department_id = dep.id)
15. 0.000 0.087 ↓ 0.0 0 1

Nested Loop (cost=2.13..554.27 rows=1 width=142) (actual time=0.087..0.087 rows=0 loops=1)

  • Join Filter: (ti.location_id = de_lo.location_id)
16. 0.001 0.087 ↓ 0.0 0 1

Nested Loop (cost=1.71..515.71 rows=1 width=126) (actual time=0.087..0.087 rows=0 loops=1)

  • Join Filter: (ti.location_id = lo.id)
17. 0.010 0.010 ↑ 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.009..0.010 rows=1 loops=1)

  • Index Cond: (id = ANY ('{114106}'::bigint[]))
18. 0.000 0.076 ↓ 0.0 0 1

Nested Loop (cost=1.42..513.39 rows=1 width=105) (actual time=0.076..0.076 rows=0 loops=1)

19. 0.003 0.076 ↓ 0.0 0 1

Nested Loop (cost=0.99..510.95 rows=1 width=105) (actual time=0.076..0.076 rows=0 loops=1)

20. 0.025 0.025 ↓ 3.0 3 1

Index Scan using idx_employee_id_del on employee em (cost=0.42..15.33 rows=1 width=19) (actual time=0.018..0.025 rows=3 loops=1)

  • Index Cond: (id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::bigint[]))
21. 0.048 0.048 ↓ 0.0 0 3

Index Scan using i_timeblock_emp on timeblock ti (cost=0.57..495.60 rows=1 width=94) (actual time=0.016..0.016 rows=0 loops=3)

  • Index Cond: (employee_id = em.id)
  • Filter: ((start_date >= '2020-02-01'::date) AND (start_date <= '2020-02-05'::date))
  • Rows Removed by Filter: 11
22. 0.000 0.000 ↓ 0.0 0

Index Scan using schedule_pkey on schedule sc (cost=0.43..2.44 rows=1 width=16) (never executed)

  • Index Cond: (id = ti.schedule_id)
23. 0.000 0.000 ↓ 0.0 0

Index Scan using department_location_dep_idx on department_location de_lo (cost=0.42..38.22 rows=27 width=16) (never executed)

  • Index Cond: (department_id = sc.department_id)
  • Filter: (deleted = 'N'::bpchar)
24. 0.000 0.000 ↓ 0.0 0

Index Only Scan using i_department_id on department dep (cost=0.29..13.63 rows=1 width=4) (never executed)

  • Index Cond: ((id = de_lo.department_id) AND (id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::integer[])))
  • Heap Fetches: 0
25. 0.000 0.000 ↓ 0.0 0

Index Scan using corporation_pkey on corporation co (cost=0.29..2.31 rows=1 width=8) (never executed)

  • Index Cond: (id = lo.corporation_id)
  • Filter: (deleted = 'N'::bpchar)
26. 0.000 0.000 ↓ 0.0 0

Index Scan using i_position_id on "position" po (cost=0.42..2.44 rows=1 width=24) (never executed)

  • Index Cond: (id = ti.position_id)
  • Filter: (ti.location_id = location_id)
27. 0.000 0.000 ↓ 0.0 0

Index Scan using position_code_pkey on position_code po_code (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: (id = po.position_code_id)
  • Filter: (deleted = 'N'::bpchar)
28. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = em.user_id)
29. 0.000 0.000 ↓ 0.0 0

Index Scan using user_loc_user_id on user_location ul (cost=0.42..23.09 rows=22 width=16) (never executed)

  • Index Cond: (user_id = em.user_id)
  • Filter: ((termination_date IS NULL) OR (termination_date >= '2020-02-05 00:00:00'::timestamp without time zone))
30. 0.000 0.000 ↓ 0.0 0

Index Scan using timezone_postal_idx on timezone tz (cost=0.43..2.45 rows=1 width=17) (never executed)

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

SubPlan (for GroupAggregate)

32. 0.000 0.000 ↓ 0.0 0

Function Scan on getactivetimeblockid (cost=0.25..0.26 rows=1 width=8) (never executed)

33.          

CTE schedule_now

34. 0.004 0.929 ↓ 0.0 0 1

GroupAggregate (cost=381.92..382.29 rows=1 width=214) (actual time=0.929..0.929 rows=0 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
35. 0.043 0.925 ↓ 0.0 0 1

Sort (cost=381.92..381.93 rows=1 width=90) (actual time=0.925..0.925 rows=0 loops=1)

  • Sort 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
  • Sort Method: quicksort Memory: 25kB
36. 0.001 0.882 ↓ 0.0 0 1

Nested Loop Left Join (cost=7.23..381.91 rows=1 width=90) (actual time=0.882..0.882 rows=0 loops=1)

37. 0.000 0.881 ↓ 0.0 0 1

Nested Loop Left Join (cost=5.95..374.54 rows=1 width=94) (actual time=0.881..0.881 rows=0 loops=1)

38. 0.000 0.881 ↓ 0.0 0 1

Nested Loop (cost=5.39..371.32 rows=1 width=90) (actual time=0.881..0.881 rows=0 loops=1)

  • Join Filter: (ti_1.location_id = ul_1.location_id)
39. 0.000 0.881 ↓ 0.0 0 1

Nested Loop (cost=4.96..347.96 rows=1 width=130) (actual time=0.881..0.881 rows=0 loops=1)

40. 0.000 0.881 ↓ 0.0 0 1

Nested Loop Left Join (cost=4.54..345.52 rows=1 width=107) (actual time=0.881..0.881 rows=0 loops=1)

41. 0.000 0.881 ↓ 0.0 0 1

Nested Loop (cost=3.26..338.11 rows=1 width=105) (actual time=0.881..0.881 rows=0 loops=1)

42. 0.001 0.881 ↓ 0.0 0 1

Nested Loop (cost=2.83..335.67 rows=1 width=113) (actual time=0.881..0.881 rows=0 loops=1)

43. 0.000 0.880 ↓ 0.0 0 1

Nested Loop (cost=2.41..333.22 rows=1 width=105) (actual time=0.880..0.880 rows=0 loops=1)

  • Join Filter: (sc_1.department_id = dep_1.id)
44. 0.000 0.880 ↓ 0.0 0 1

Nested Loop (cost=2.13..319.57 rows=1 width=121) (actual time=0.880..0.880 rows=0 loops=1)

  • Join Filter: (ti_1.location_id = de_lo_1.location_id)
45. 0.003 0.880 ↓ 0.0 0 1

Nested Loop (cost=1.71..281.01 rows=1 width=105) (actual time=0.880..0.880 rows=0 loops=1)

  • Join Filter: (ti_1.location_id = lo_1.id)
46. 0.043 0.043 ↑ 1.0 1 1

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

  • Index Cond: (id = ANY ('{114106}'::bigint[]))
  • Heap Fetches: 1
47. 0.000 0.834 ↓ 0.0 0 1

Nested Loop (cost=1.42..278.70 rows=1 width=97) (actual time=0.834..0.834 rows=0 loops=1)

48. 0.004 0.834 ↓ 0.0 0 1

Nested Loop (cost=0.99..276.25 rows=1 width=97) (actual time=0.834..0.834 rows=0 loops=1)

49. 0.089 0.089 ↓ 3.0 3 1

Index Scan using idx_employee_id_del on employee em_1 (cost=0.42..15.33 rows=1 width=19) (actual time=0.076..0.089 rows=3 loops=1)

  • Index Cond: (id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::bigint[]))
50. 0.741 0.741 ↓ 0.0 0 3

Index Scan using i_timeblock_employee on timeblock ti_1 (cost=0.56..260.90 rows=1 width=86) (actual time=0.247..0.247 rows=0 loops=3)

  • Index Cond: (employee_id = em_1.id)
  • Filter: ((start_date >= '2020-02-01'::date) AND (start_date <= '2020-02-05'::date))
  • Rows Removed by Filter: 11
51. 0.000 0.000 ↓ 0.0 0

Index Scan using schedule_pkey on schedule sc_1 (cost=0.43..2.45 rows=1 width=16) (never executed)

  • Index Cond: (id = ti_1.schedule_id)
  • Filter: (COALESCE(deleted, 'N'::bpchar) = 'N'::bpchar)
52. 0.000 0.000 ↓ 0.0 0

Index Scan using department_location_dep_idx on department_location de_lo_1 (cost=0.42..38.22 rows=27 width=16) (never executed)

  • Index Cond: (department_id = sc_1.department_id)
  • Filter: (deleted = 'N'::bpchar)
53. 0.000 0.000 ↓ 0.0 0

Index Only Scan using i_department_id on department dep_1 (cost=0.29..13.63 rows=1 width=4) (never executed)

  • Index Cond: ((id = de_lo_1.department_id) AND (id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::integer[])))
  • Heap Fetches: 0
54. 0.000 0.000 ↓ 0.0 0

Index Scan using i_position_id on "position" po_1 (cost=0.42..2.44 rows=1 width=24) (never executed)

  • Index Cond: (id = ti_1.position_id)
  • Filter: (ti_1.location_id = location_id)
55. 0.000 0.000 ↓ 0.0 0

Index Scan using position_code_pkey on position_code po_code_1 (cost=0.42..2.44 rows=1 width=8) (never executed)

  • Index Cond: (id = po_1.position_code_id)
  • Filter: (deleted = 'N'::bpchar)
56. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.28..7.39 rows=1 width=10) (never executed)

  • Join Filter: (ss.location_id = lo_2.id)
57. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.99..5.08 rows=1 width=18) (never executed)

58. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_shift_swap on shift_swap ss (cost=0.42..2.49 rows=1 width=18) (never executed)

  • Index Cond: (timeblock_id = ti_1.id)
  • Filter: (pickedup = 'Y'::bpchar)
59. 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))
60. 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 = ANY ('{114106}'::bigint[]))
  • Heap Fetches: 0
61. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = em_1.user_id)
62. 0.000 0.000 ↓ 0.0 0

Index Scan using user_loc_user_id on user_location ul_1 (cost=0.42..23.09 rows=22 width=16) (never executed)

  • Index Cond: (user_id = em_1.user_id)
  • Filter: ((termination_date IS NULL) OR (termination_date >= '2020-02-05 00:00:00'::timestamp without time zone))
63. 0.000 0.000 ↓ 0.0 0

Index Scan using i_breaktime_time_id on break_time bt_1 (cost=0.56..3.21 rows=1 width=12) (never executed)

  • Index Cond: (timeblock_id = ti_1.id)
  • Filter: (COALESCE(paid, 'N'::bpchar) = 'N'::bpchar)
64. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.28..7.36 rows=1 width=12) (never executed)

  • Join Filter: (ti_3.location_id = lo_3.id)
65. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.99..5.05 rows=1 width=20) (never executed)

66. 0.000 0.000 ↓ 0.0 0

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

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

Index Scan using i_schedule_id on schedule sc_2 (cost=0.43..2.46 rows=1 width=8) (never executed)

  • Index Cond: (id = ti_3.schedule_id)
  • Filter: (department_id = ANY ('{18002,17998,17996,17997,16498,17994,17999,16500,17993,17995}'::bigint[]))
68. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (id = ANY ('{114106}'::bigint[]))
  • Heap Fetches: 0
69.          

CTE final

70. 0.002 1.053 ↓ 0.0 0 1

Append (cost=0.00..0.24 rows=3 width=904) (actual time=1.053..1.053 rows=0 loops=1)

71. 0.000 0.929 ↓ 0.0 0 1

Nested Loop (cost=0.00..0.10 rows=1 width=904) (actual time=0.929..0.929 rows=0 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))
72. 0.929 0.929 ↓ 0.0 0 1

CTE Scan on schedule_now sn (cost=0.00..0.02 rows=1 width=696) (actual time=0.929..0.929 rows=0 loops=1)

73. 0.000 0.000 ↓ 0.0 0

CTE Scan on prior_schedule ps (cost=0.00..0.02 rows=1 width=664) (never executed)

74. 0.122 0.122 ↓ 0.0 0 1

CTE Scan on prior_schedule ps_1 (cost=0.00..0.04 rows=1 width=904) (actual time=0.122..0.122 rows=0 loops=1)

  • Filter: (time_block_id IS NULL)
75. 0.000 0.000 ↓ 0.0 0 1

CTE Scan on schedule_now sn_1 (cost=0.02..0.07 rows=1 width=904) (actual time=0.000..0.000 rows=0 loops=1)

  • Filter: (NOT (hashed SubPlan 4))
76.          

SubPlan (for CTE Scan)

77. 0.000 0.000 ↓ 0.0 0

CTE Scan on prior_schedule (cost=0.00..0.02 rows=1 width=8) (never executed)

78. 0.090 1.144 ↓ 0.0 0 1

Sort (cost=0.11..0.12 rows=3 width=904) (actual time=1.144..1.144 rows=0 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, ns.cost_adjusted
  • Sort Method: quicksort Memory: 25kB
79. 1.054 1.054 ↓ 0.0 0 1

CTE Scan on final ns (cost=0.00..0.09 rows=3 width=904) (actual time=1.054..1.054 rows=0 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 : 149.678 ms
Execution time : 3.859 ms