explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WObP

Settings
# exclusive inclusive rows x rows loops node
1. 247,794.991 247,794.991 ↓ 2.0 2 1

CTE Scan on final (cost=1,600,658.43..1,600,658.45 rows=1 width=4,956) (actual time=247,794.953..247,794.991 rows=2 loops=1)

2.          

CTE prior_schedule

3. 0.001 247,773.937 ↓ 0.0 0 1

GroupAggregate (cost=1,600,370.22..1,600,370.83 rows=1 width=222) (actual time=247,773.937..247,773.937 rows=0 loops=1)

  • Group Key: ti.start_date, ap.first_name, ap.last_name, ap.middle_name, l_flag.value, de_lo.payroll_id, dep.name, po.payroll_id, po.alias, po_code.description, ti.end_date, ti.end_time, ti.start_time, em.id, lo.id
4. 0.026 247,773.936 ↓ 0.0 0 1

Sort (cost=1,600,370.22..1,600,370.22 rows=1 width=134) (actual time=247,773.936..247,773.936 rows=0 loops=1)

  • Sort Key: ti.start_date, ap.first_name, ap.last_name, ap.middle_name, l_flag.value, de_lo.payroll_id, dep.name, po.payroll_id, po.alias, po_code.description, ti.end_date, ti.end_time, ti.start_time, em.id, lo.id
  • Sort Method: quicksort Memory: 25kB
5. 0.032 247,773.910 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,600,279.12..1,600,370.21 rows=1 width=134) (actual time=247,773.910..247,773.910 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 ((((sc.start_date - '9 days'::interval) - '08:00:00'::interval) >= ti.starttimestamp) AND (((sc.start_date - '9 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 ((((sc.start_date - '9 days'::interval) - '07:00:00'::interval) >= ti.starttimestamp) AND (((sc.start_date - '9 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 ((((sc.start_date - '9 days'::interval) - '06:00:00'::interval) >= ti.starttimestamp) AND (((sc.start_date - '9 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 ((((sc.start_date - '9 days'::interval) - '05:00:00'::interval) >= ti.starttimestamp) AND (((sc.start_date - '9 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) = '-5'::double precision) THEN CASE WHEN (tz.timezone_offset = '-5'::double precision) THEN ((((sc.start_date - '9 days'::interval) - '08:00:00'::interval) >= ti.starttimestamp) AND (((sc.start_date - '9 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 ((((sc.start_date - '9 days'::interval) - '07:00:00'::interval) >= ti.starttimestamp) AND (((sc.start_date - '9 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 ((((sc.start_date - '9 days'::interval) - '06:00:00'::interval) >= ti.starttimestamp) AND (((sc.start_date - '9 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 ((((sc.start_date - '9 days'::interval) - '05:00:00'::interval) >= ti.starttimestamp) AND (((sc.start_date - '9 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: 2
6. 0.008 247,773.058 ↓ 2.0 2 1

Nested Loop Left Join (cost=1,600,278.69..1,600,367.57 rows=1 width=163) (actual time=247,771.442..247,773.058 rows=2 loops=1)

  • Join Filter: ((bt.timeblock_id = ti.id) AND (tb.location_id = ti.location_id))
7. 0.014 5.000 ↓ 2.0 2 1

Nested Loop (cost=335.13..423.96 rows=1 width=171) (actual time=3.388..5.000 rows=2 loops=1)

8. 0.006 4.316 ↓ 2.0 2 1

Nested Loop (cost=326.16..413.96 rows=1 width=195) (actual time=2.775..4.316 rows=2 loops=1)

9. 0.012 4.294 ↓ 2.0 2 1

Nested Loop (cost=325.74..411.52 rows=1 width=192) (actual time=2.767..4.294 rows=2 loops=1)

  • Join Filter: ((ul.location_id = ti.location_id) AND (em.id = ti.employee_id))
  • Rows Removed by Join Filter: 14
10. 0.009 4.138 ↓ 2.0 2 1

Nested Loop (cost=324.47..381.87 rows=1 width=190) (actual time=2.738..4.138 rows=2 loops=1)

11. 0.004 3.813 ↓ 2.0 2 1

Nested Loop (cost=324.04..379.42 rows=1 width=161) (actual time=2.430..3.813 rows=2 loops=1)

12. 0.015 3.071 ↓ 2.0 2 1

Nested Loop (cost=323.75..377.10 rows=1 width=169) (actual time=1.708..3.071 rows=2 loops=1)

  • Join Filter: (sc.department_id = dep.id)
13. 0.007 3.032 ↓ 2.0 2 1

Nested Loop (cost=323.47..371.03 rows=1 width=174) (actual time=1.696..3.032 rows=2 loops=1)

  • Join Filter: (ti.location_id = de_lo.location_id)
  • Rows Removed by Join Filter: 6
14. 0.005 1.747 ↓ 2.0 2 1

Nested Loop (cost=323.05..331.44 rows=1 width=157) (actual time=1.684..1.747 rows=2 loops=1)

15. 0.008 1.702 ↓ 2.0 2 1

Nested Loop (cost=322.49..328.85 rows=1 width=123) (actual time=1.677..1.702 rows=2 loops=1)

16. 0.008 1.672 ↓ 2.0 2 1

Nested Loop (cost=322.06..326.40 rows=1 width=115) (actual time=1.665..1.672 rows=2 loops=1)

17. 0.010 0.010 ↑ 1.0 1 1

Index Scan using idx_location_id_del on location lo (cost=0.29..3.60 rows=1 width=35) (actual time=0.009..0.010 rows=1 loops=1)

  • Index Cond: (id = ANY ('{135906,12492}'::bigint[]))
  • Filter: (corporation_id = 79500)
  • Rows Removed by Filter: 1
18. 0.020 1.654 ↓ 2.0 2 1

Bitmap Heap Scan on timeblock ti (cost=321.77..322.79 rows=1 width=80) (actual time=1.652..1.654 rows=2 loops=1)

  • Recheck Cond: ((start_date >= '2019-11-03'::date) AND (start_date <= '2019-11-09'::date) AND (location_id = lo.id))
  • Heap Blocks: exact=1
19. 0.120 1.634 ↓ 0.0 0 1

BitmapAnd (cost=321.77..321.77 rows=1 width=0) (actual time=1.634..1.634 rows=0 loops=1)

20. 0.956 0.956 ↓ 1.4 8,085 1

Bitmap Index Scan on i_timeblock_start_nodel (cost=0.00..76.00 rows=5,843 width=0) (actual time=0.956..0.956 rows=8,085 loops=1)

  • Index Cond: ((start_date >= '2019-11-03'::date) AND (start_date <= '2019-11-09'::date))
21. 0.558 0.558 ↑ 11,833.0 2 1

Bitmap Index Scan on i_timeblock_loc (cost=0.00..244.06 rows=23,666 width=0) (actual time=0.558..0.558 rows=2 loops=1)

  • Index Cond: (location_id = lo.id)
22. 0.022 0.022 ↑ 1.0 1 2

Index Scan using schedule_pkey on schedule sc (cost=0.43..2.44 rows=1 width=24) (actual time=0.011..0.011 rows=1 loops=2)

  • Index Cond: (id = ti.schedule_id)
23. 0.040 0.040 ↑ 1.0 1 2

Index Scan using idx_loc_flag_del on location_flag l_flag (cost=0.56..2.58 rows=1 width=34) (actual time=0.019..0.020 rows=1 loops=2)

  • Index Cond: ((location_id = ti.location_id) AND (flag_id = 83))
24. 1.278 1.278 ↑ 6.8 4 2

Index Scan using department_location_dep_idx on department_location de_lo (cost=0.42..39.26 rows=27 width=17) (actual time=0.013..0.639 rows=4 loops=2)

  • Index Cond: (department_id = sc.department_id)
  • Filter: (deleted = 'N'::bpchar)
  • Rows Removed by Filter: 10
25. 0.024 0.024 ↑ 1.0 1 2

Index Scan using i_department_id on department dep (cost=0.29..6.05 rows=1 width=15) (actual time=0.012..0.012 rows=1 loops=2)

  • Index Cond: ((id = de_lo.department_id) AND (id = ANY ('{28656,26789,23735,24305}'::integer[])))
26. 0.738 0.738 ↑ 1.0 1 2

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

  • Index Cond: (id = 79500)
  • Filter: (deleted = 'N'::bpchar)
27. 0.316 0.316 ↑ 1.0 1 2

Index Scan using i_position_id on "position" po (cost=0.42..2.44 rows=1 width=29) (actual time=0.158..0.158 rows=1 loops=2)

  • Index Cond: (id = ti.position_id)
  • Filter: (ti.location_id = location_id)
28. 0.010 0.144 ↓ 8.0 8 2

Nested Loop (cost=1.27..29.63 rows=1 width=34) (actual time=0.019..0.072 rows=8 loops=2)

29. 0.014 0.082 ↓ 2.0 2 2

Nested Loop (cost=0.85..6.32 rows=1 width=42) (actual time=0.013..0.041 rows=2 loops=2)

30. 0.032 0.032 ↓ 2.0 2 2

Index Scan using idx_employee_id_del on employee em (cost=0.42..3.88 rows=1 width=19) (actual time=0.007..0.016 rows=2 loops=2)

  • Index Cond: (id = ANY ('{25644913,25642553}'::bigint[]))
31. 0.036 0.036 ↑ 1.0 1 4

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

  • Index Cond: (id = em.user_id)
32. 0.052 0.052 ↑ 5.2 4 4

Index Scan using user_loc_user_id on user_location ul (cost=0.42..23.11 rows=21 width=16) (actual time=0.005..0.013 rows=4 loops=4)

  • Index Cond: (user_id = em.user_id)
  • Filter: ((termination_date IS NULL) OR (termination_date >= '2019-11-09 00:00:00'::timestamp without time zone))
33. 0.016 0.016 ↑ 1.0 1 2

Index Scan using position_code_pkey on position_code po_code (cost=0.42..2.44 rows=1 width=19) (actual time=0.008..0.008 rows=1 loops=2)

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

Bitmap Heap Scan on employee_position em_po (cost=8.97..9.99 rows=1 width=16) (actual time=0.328..0.335 rows=1 loops=2)

  • Recheck Cond: ((employee_id = ti.employee_id) AND (position_id = ti.position_id))
  • Filter: (deleted = 'N'::bpchar)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=3
35. 0.010 0.626 ↓ 0.0 0 2

BitmapAnd (cost=8.97..8.97 rows=1 width=0) (actual time=0.313..0.313 rows=0 loops=2)

36. 0.324 0.324 ↑ 6.5 42 2

Bitmap Index Scan on emp_pos_employee_id (cost=0.00..3.46 rows=271 width=0) (actual time=0.162..0.162 rows=42 loops=2)

  • Index Cond: (employee_id = ti.employee_id)
37. 0.292 0.292 ↑ 188.0 2 2

Bitmap Index Scan on emp_pos_position_id (cost=0.00..5.25 rows=376 width=0) (actual time=0.146..0.146 rows=2 loops=2)

  • Index Cond: (position_id = ti.position_id)
38. 0.002 247,768.050 ↓ 0.0 0 2

GroupAggregate (cost=1,599,943.56..1,599,943.59 rows=1 width=24) (actual time=123,884.025..123,884.025 rows=0 loops=2)

  • Group Key: tb.location_id, bt.timeblock_id
39. 0.016 247,768.048 ↓ 0.0 0 2

Sort (cost=1,599,943.56..1,599,943.57 rows=1 width=20) (actual time=123,884.024..123,884.024 rows=0 loops=2)

  • Sort Key: tb.location_id, bt.timeblock_id
  • Sort Method: quicksort Memory: 25kB
40. 16.168 247,768.032 ↓ 0.0 0 1

Nested Loop Left Join (cost=86.14..1,599,943.55 rows=1 width=20) (actual time=247,768.032..247,768.032 rows=0 loops=1)

  • Filter: CASE WHEN ((date_part('timezone'::text, CURRENT_TIME) / '3600'::double precision) = '-4'::double precision) THEN CASE WHEN ((tz_1.timezone_offset = '-5'::double precision) AND (tz_1.dst = 'Y'::bpchar)) THEN ((((sc_1.start_date - '9 days'::interval) - '08:00:00'::interval) >= bt.starttimestamp) AND (((sc_1.start_date - '9 days'::interval) - '08:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz_1.timezone_offset = '-6'::double precision) AND (tz_1.dst = 'Y'::bpchar)) OR ((tz_1.timezone_offset = '-5'::double precision) AND (tz_1.dst = 'N'::bpchar))) THEN ((((sc_1.start_date - '9 days'::interval) - '07:00:00'::interval) >= bt.starttimestamp) AND (((sc_1.start_date - '9 days'::interval) - '07:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz_1.timezone_offset = '-7'::double precision) AND (tz_1.dst = 'Y'::bpchar)) OR ((tz_1.timezone_offset = '-6'::double precision) AND (tz_1.dst = 'N'::bpchar))) THEN ((((sc_1.start_date - '9 days'::interval) - '06:00:00'::interval) >= bt.starttimestamp) AND (((sc_1.start_date - '9 days'::interval) - '06:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (((tz_1.timezone_offset = '-8'::double precision) AND (tz_1.dst = 'Y'::bpchar)) OR ((tz_1.timezone_offset = '-7'::double precision) AND (tz_1.dst = 'N'::bpchar))) THEN ((((sc_1.start_date - '9 days'::interval) - '05:00:00'::interval) >= bt.starttimestamp) AND (((sc_1.start_date - '9 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) = '-5'::double precision) THEN CASE WHEN (tz_1.timezone_offset = '-5'::double precision) THEN ((((sc_1.start_date - '9 days'::interval) - '08:00:00'::interval) >= bt.starttimestamp) AND (((sc_1.start_date - '9 days'::interval) - '08:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz_1.timezone_offset = '-6'::double precision) THEN ((((sc_1.start_date - '9 days'::interval) - '07:00:00'::interval) >= bt.starttimestamp) AND (((sc_1.start_date - '9 days'::interval) - '07:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz_1.timezone_offset = '-7'::double precision) THEN ((((sc_1.start_date - '9 days'::interval) - '06:00:00'::interval) >= bt.starttimestamp) AND (((sc_1.start_date - '9 days'::interval) - '06:00:00'::interval) <= COALESCE(bt.endtimestamp, '3000-12-31 00:00:00'::timestamp without time zone))) WHEN (tz_1.timezone_offset = '-8'::double precision) THEN ((((sc_1.start_date - '9 days'::interval) - '05:00:00'::interval) >= bt.starttimestamp) AND (((sc_1.start_date - '9 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 Filter: 54
41. 0.072 247,750.244 ↓ 54.0 54 1

Nested Loop (cost=85.71..1,599,940.91 rows=1 width=49) (actual time=91,353.940..247,750.244 rows=54 loops=1)

  • Join Filter: (tb.location_id = lo_1.id)
  • Rows Removed by Join Filter: 54
42. 0.090 247,749.362 ↓ 54.0 54 1

Nested Loop (cost=85.43..1,599,938.59 rows=1 width=44) (actual time=91,353.418..247,749.362 rows=54 loops=1)

43. 50,737.181 247,748.948 ↓ 54.0 54 1

Nested Loop (cost=85.00..1,599,936.14 rows=1 width=44) (actual time=91,353.373..247,748.948 rows=54 loops=1)

  • Join Filter: (tb.id = bt.timeblock_id)
  • Rows Removed by Join Filter: 476708552
44. 0.030 2.966 ↓ 11.0 11 1

Nested Loop (cost=85.00..88.48 rows=1 width=24) (actual time=1.750..2.966 rows=11 loops=1)

45. 0.084 0.084 ↓ 2.0 2 1

Index Only Scan using idx_employee_id_del on employee em_1 (cost=0.42..2.88 rows=1 width=8) (actual time=0.058..0.084 rows=2 loops=1)

  • Index Cond: (id = ANY ('{25644913,25642553}'::bigint[]))
  • Heap Fetches: 1
46. 0.084 2.852 ↓ 6.0 6 2

Bitmap Heap Scan on timeblock tb (cost=84.57..85.59 rows=1 width=32) (actual time=1.392..1.426 rows=6 loops=2)

  • Recheck Cond: ((employee_id = em_1.id) AND (start_date >= '2019-11-03'::date) AND (start_date <= '2019-11-09'::date))
  • Heap Blocks: exact=5
47. 0.070 2.768 ↓ 0.0 0 2

BitmapAnd (cost=84.57..84.57 rows=1 width=0) (actual time=1.384..1.384 rows=0 loops=2)

48. 0.616 0.616 ↑ 11.9 48 2

Bitmap Index Scan on i_timeblock_emp (cost=0.00..6.86 rows=573 width=0) (actual time=0.308..0.308 rows=48 loops=2)

  • Index Cond: (employee_id = em_1.id)
49. 2.082 2.082 ↓ 1.4 8,085 2

Bitmap Index Scan on i_timeblock_start_nodel (cost=0.00..76.00 rows=5,843 width=0) (actual time=1.041..1.041 rows=8,085 loops=2)

  • Index Cond: ((start_date >= '2019-11-03'::date) AND (start_date <= '2019-11-09'::date))
50. 197,008.801 197,008.801 ↓ 153.6 43,337,146 11

Seq Scan on break_time bt (cost=0.00..1,596,321.20 rows=282,117 width=28) (actual time=0.060..17,909.891 rows=43,337,146 loops=11)

  • Filter: (COALESCE(paid, 'N'::bpchar) = 'N'::bpchar)
  • Rows Removed by Filter: 13085962
51. 0.324 0.324 ↑ 1.0 1 54

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

  • Index Cond: (id = tb.schedule_id)
  • Filter: (department_id = ANY ('{28656,26789,23735,24305}'::bigint[]))
52. 0.810 0.810 ↓ 2.0 2 54

Index Scan using i_loc_corp_id on location lo_1 (cost=0.29..2.31 rows=1 width=13) (actual time=0.014..0.015 rows=2 loops=54)

  • Index Cond: (corporation_id = 79500)
  • Filter: (id = ANY ('{135906,124925}'::bigint[]))
  • Rows Removed by Filter: 2
53. 1.620 1.620 ↑ 1.0 1 54

Index Scan using timezone_postal_idx on timezone tz_1 (cost=0.43..2.45 rows=1 width=17) (actual time=0.029..0.030 rows=1 loops=54)

  • Index Cond: ((postal_code)::text = split_part((lo_1.zip)::text, '-'::text, 1))
54. 0.820 0.820 ↑ 1.0 1 2

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

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

CTE final

56. 12.508 247,794.980 ↓ 2.0 2 1

GroupAggregate (cost=286.89..287.60 rows=1 width=2,434) (actual time=247,794.945..247,794.980 rows=2 loops=1)

  • Group Key: em_2.payroll_id, lo_2.name, ti_1.start_date, ap_1.first_name, ap_1.last_name, ap_1.middle_name, ps.loc_payroll_id, ps.location_name, ps.dep_payroll_id, ps.depname, ps.po_payroll_id, ps.description, ps.schedule_duration, ps.start_date, ps.start_time, ps.end_time, l_flag_1.value, de_lo_1.payroll_id, dep_1.name, po_1.payroll_id, po_1.alias, po_code_1.description, ti_1.end_date, ti_1.end_time, ti_1.start_time
57. 0.126 247,782.472 ↓ 2.0 2 1

Sort (cost=286.89..286.89 rows=1 width=2,298) (actual time=247,782.472..247,782.472 rows=2 loops=1)

  • Sort Key: em_2.payroll_id, lo_2.name, ti_1.start_date, ap_1.first_name, ap_1.last_name, ap_1.middle_name, ps.loc_payroll_id, ps.location_name, ps.dep_payroll_id, ps.depname, ps.po_payroll_id, ps.description, ps.schedule_duration, ps.start_date, ps.start_time, ps.end_time, l_flag_1.value, de_lo_1.payroll_id, dep_1.name, po_1.payroll_id, po_1.alias, po_code_1.description, ti_1.end_date, ti_1.end_time, ti_1.start_time
  • Sort Method: quicksort Memory: 25kB
58. 0.005 247,782.346 ↓ 2.0 2 1

Nested Loop Left Join (cost=163.12..286.88 rows=1 width=2,298) (actual time=247,781.098..247,782.346 rows=2 loops=1)

  • Join Filter: ((bt_1.timeblock_id = ti_1.id) AND (tb_1.location_id = ti_1.location_id))
  • Rows Removed by Join Filter: 2
59. 0.008 247,781.109 ↓ 2.0 2 1

Nested Loop (cost=105.76..229.46 rows=1 width=2,306) (actual time=247,779.865..247,781.109 rows=2 loops=1)

60. 0.004 247,781.061 ↓ 2.0 2 1

Nested Loop (cost=96.79..219.47 rows=1 width=2,338) (actual time=247,779.838..247,781.061 rows=2 loops=1)

61. 0.004 247,781.047 ↓ 2.0 2 1

Nested Loop Left Join (cost=96.50..217.15 rows=1 width=2,346) (actual time=247,779.830..247,781.047 rows=2 loops=1)

  • Join Filter: ((ps.start_date = ti_1.start_date) AND (ps.location_id = lo_2.id) AND (ps.employee_id = em_2.id))
62. 0.009 7.103 ↓ 2.0 2 1

Nested Loop (cost=96.50..217.11 rows=1 width=174) (actual time=5.889..7.103 rows=2 loops=1)

  • Join Filter: ((ul_1.location_id = ti_1.location_id) AND (em_2.id = ti_1.employee_id))
  • Rows Removed by Join Filter: 14
63. 0.014 6.862 ↓ 2.0 2 1

Nested Loop (cost=95.22..187.46 rows=1 width=172) (actual time=5.695..6.862 rows=2 loops=1)

  • Join Filter: (sc_2.department_id = dep_1.id)
64. 0.007 6.770 ↓ 2.0 2 1

Nested Loop (cost=94.94..181.39 rows=1 width=177) (actual time=5.614..6.770 rows=2 loops=1)

  • Join Filter: (ti_1.location_id = de_lo_1.location_id)
  • Rows Removed by Join Filter: 6
65. 0.004 4.545 ↓ 2.0 2 1

Nested Loop (cost=94.52..141.80 rows=1 width=160) (actual time=3.411..4.545 rows=2 loops=1)

66. 0.005 3.919 ↓ 2.0 2 1

Nested Loop (cost=93.96..139.21 rows=1 width=126) (actual time=2.794..3.919 rows=2 loops=1)

67. 0.014 3.872 ↓ 2.0 2 1

Nested Loop (cost=93.53..136.76 rows=1 width=126) (actual time=2.756..3.872 rows=2 loops=1)

  • Join Filter: (lo_2.id = ti_1.location_id)
68. 0.009 1.170 ↓ 7.0 7 1

Nested Loop (cost=1.13..43.33 rows=1 width=62) (actual time=0.812..1.170 rows=7 loops=1)

69. 0.010 0.496 ↓ 3.5 7 1

Nested Loop (cost=0.71..38.45 rows=2 width=59) (actual time=0.486..0.496 rows=7 loops=1)

70. 0.070 0.070 ↑ 1.0 1 1

Index Scan using idx_location_id_del on location lo_2 (cost=0.29..3.60 rows=1 width=30) (actual time=0.069..0.070 rows=1 loops=1)

  • Index Cond: (id = ANY ('{135906,12492}'::bigint[]))
  • Filter: (corporation_id = 79500)
  • Rows Removed by Filter: 1
71. 0.416 0.416 ↑ 8.1 7 1

Index Scan using i_position_loc_id on "position" po_1 (cost=0.42..34.28 rows=57 width=29) (actual time=0.412..0.416 rows=7 loops=1)

  • Index Cond: (location_id = lo_2.id)
72. 0.665 0.665 ↑ 1.0 1 7

Index Scan using position_code_pkey on position_code po_code_1 (cost=0.42..2.44 rows=1 width=19) (actual time=0.095..0.095 rows=1 loops=7)

  • Index Cond: (id = po_1.position_code_id)
  • Filter: (deleted = 'N'::bpchar)
73. 0.028 2.688 ↓ 0.0 0 7

Bitmap Heap Scan on timeblock ti_1 (cost=92.40..93.42 rows=1 width=64) (actual time=0.384..0.384 rows=0 loops=7)

  • Recheck Cond: ((position_id = po_1.id) AND (deleted = 'N'::bpchar) AND (start_date >= '2019-11-03'::date) AND (start_date <= '2019-11-09'::date))
  • Filter: (po_1.location_id = location_id)
  • Heap Blocks: exact=2
74. 0.062 2.660 ↓ 0.0 0 7

BitmapAnd (cost=92.40..92.40 rows=1 width=0) (actual time=0.380..0.380 rows=0 loops=7)

75. 0.364 0.364 ↓ 0.0 0 7

Bitmap Index Scan on i_timeblock_position (cost=0.00..15.44 rows=1,450 width=0) (actual time=0.052..0.052 rows=0 loops=7)

  • Index Cond: (position_id = po_1.id)
76. 2.234 2.234 ↓ 1.4 8,085 2

Bitmap Index Scan on i_timeblock_start_nodel (cost=0.00..76.00 rows=5,843 width=0) (actual time=1.117..1.117 rows=8,085 loops=2)

  • Index Cond: ((start_date >= '2019-11-03'::date) AND (start_date <= '2019-11-09'::date))
77. 0.042 0.042 ↑ 1.0 1 2

Index Scan using i_schedule_id on schedule sc_2 (cost=0.43..2.45 rows=1 width=16) (actual time=0.021..0.021 rows=1 loops=2)

  • Index Cond: (id = ti_1.schedule_id)
78. 0.622 0.622 ↑ 1.0 1 2

Index Scan using idx_loc_flag_del on location_flag l_flag_1 (cost=0.56..2.58 rows=1 width=34) (actual time=0.310..0.311 rows=1 loops=2)

  • Index Cond: ((location_id = ti_1.location_id) AND (flag_id = 83))
79. 2.218 2.218 ↑ 6.8 4 2

Index Scan using department_location_dep_idx on department_location de_lo_1 (cost=0.42..39.26 rows=27 width=17) (actual time=0.700..1.109 rows=4 loops=2)

  • Index Cond: (department_id = sc_2.department_id)
  • Filter: (deleted = 'N'::bpchar)
  • Rows Removed by Filter: 10
80. 0.078 0.078 ↑ 1.0 1 2

Index Scan using i_department_id on department dep_1 (cost=0.29..6.05 rows=1 width=15) (actual time=0.039..0.039 rows=1 loops=2)

  • Index Cond: ((id = de_lo_1.department_id) AND (id = ANY ('{28656,26789,23735,24305}'::integer[])))
81. 0.010 0.232 ↓ 8.0 8 2

Nested Loop (cost=1.27..29.63 rows=1 width=34) (actual time=0.077..0.116 rows=8 loops=2)

82. 0.010 0.142 ↓ 2.0 2 2

Nested Loop (cost=0.85..6.32 rows=1 width=42) (actual time=0.055..0.071 rows=2 loops=2)

83. 0.068 0.068 ↓ 2.0 2 2

Index Scan using idx_employee_id_del on employee em_2 (cost=0.42..3.88 rows=1 width=19) (actual time=0.026..0.034 rows=2 loops=2)

  • Index Cond: (id = ANY ('{25644913,25642553}'::bigint[]))
84. 0.064 0.064 ↑ 1.0 1 4

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

  • Index Cond: (id = em_2.user_id)
85. 0.080 0.080 ↑ 5.2 4 4

Index Scan using user_loc_user_id on user_location ul_1 (cost=0.42..23.11 rows=21 width=16) (actual time=0.012..0.020 rows=4 loops=4)

  • Index Cond: (user_id = em_2.user_id)
  • Filter: ((termination_date IS NULL) OR (termination_date >= '2019-11-09 00:00:00'::timestamp without time zone))
86. 247,773.940 247,773.940 ↓ 0.0 0 2

CTE Scan on prior_schedule ps (cost=0.00..0.02 rows=1 width=2,196) (actual time=123,886.970..123,886.970 rows=0 loops=2)

87. 0.010 0.010 ↑ 1.0 1 2

Index Scan using corporation_pkey on corporation co_1 (cost=0.29..2.31 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=2)

  • Index Cond: (id = 79500)
  • Filter: (deleted = 'N'::bpchar)
88. 0.010 0.040 ↑ 1.0 1 2

Bitmap Heap Scan on employee_position em_po_1 (cost=8.97..9.99 rows=1 width=16) (actual time=0.019..0.020 rows=1 loops=2)

  • Recheck Cond: ((employee_id = ti_1.employee_id) AND (position_id = ti_1.position_id))
  • Filter: (deleted = 'N'::bpchar)
  • Rows Removed by Filter: 0
  • Heap Blocks: exact=3
89. 0.006 0.030 ↓ 0.0 0 2

BitmapAnd (cost=8.97..8.97 rows=1 width=0) (actual time=0.015..0.015 rows=0 loops=2)

90. 0.014 0.014 ↑ 6.5 42 2

Bitmap Index Scan on emp_pos_employee_id (cost=0.00..3.46 rows=271 width=0) (actual time=0.007..0.007 rows=42 loops=2)

  • Index Cond: (employee_id = ti_1.employee_id)
91. 0.010 0.010 ↑ 188.0 2 2

Bitmap Index Scan on emp_pos_position_id (cost=0.00..5.25 rows=376 width=0) (actual time=0.005..0.005 rows=2 loops=2)

  • Index Cond: (position_id = ti_1.position_id)
92. 0.012 1.232 ↑ 1.0 1 2

GroupAggregate (cost=57.37..57.39 rows=1 width=24) (actual time=0.616..0.616 rows=1 loops=2)

  • Group Key: tb_1.location_id, bt_1.timeblock_id
93. 0.019 1.220 ↓ 2.0 2 2

Sort (cost=57.37..57.37 rows=1 width=20) (actual time=0.610..0.610 rows=2 loops=2)

  • Sort Key: tb_1.location_id, bt_1.timeblock_id
  • Sort Method: quicksort Memory: 25kB
94. 0.005 1.201 ↓ 2.0 2 1

Nested Loop Left Join (cost=45.06..57.36 rows=1 width=20) (actual time=1.179..1.201 rows=2 loops=1)

95. 0.005 1.128 ↓ 2.0 2 1

Nested Loop (cost=44.63..54.90 rows=1 width=25) (actual time=1.115..1.128 rows=2 loops=1)

  • Join Filter: (tb_1.location_id = lo_3.id)
  • Rows Removed by Join Filter: 2
96. 0.003 1.103 ↓ 2.0 2 1

Nested Loop (cost=44.34..52.58 rows=1 width=20) (actual time=1.096..1.103 rows=2 loops=1)

97. 0.005 1.084 ↓ 2.0 2 1

Nested Loop (cost=43.91..50.13 rows=1 width=28) (actual time=1.081..1.084 rows=2 loops=1)

98. 0.008 1.031 ↓ 3.0 3 1

Nested Loop (cost=43.35..46.83 rows=1 width=24) (actual time=0.606..1.031 rows=3 loops=1)

99. 0.015 0.015 ↓ 2.0 2 1

Index Only Scan using idx_employee_id_del on employee em_3 (cost=0.42..2.88 rows=1 width=8) (actual time=0.009..0.015 rows=2 loops=1)

  • Index Cond: (id = ANY ('{25644913,25642553}'::bigint[]))
  • Heap Fetches: 1
100. 0.010 1.008 ↓ 2.0 2 2

Bitmap Heap Scan on timeblock tb_1 (cost=42.92..43.95 rows=1 width=32) (actual time=0.503..0.504 rows=2 loops=2)

  • Recheck Cond: ((employee_id = em_3.id) AND (start_date >= '2019-11-03'::date) AND (start_date <= '2019-11-09'::date) AND (deleted = 'N'::bpchar) AND (deleted_flag = 'N'::bpchar))
  • Heap Blocks: exact=3
101. 0.008 0.998 ↓ 0.0 0 2

BitmapAnd (cost=42.92..42.92 rows=1 width=0) (actual time=0.499..0.499 rows=0 loops=2)

102. 0.032 0.032 ↑ 11.9 48 2

Bitmap Index Scan on i_timeblock_emp (cost=0.00..6.86 rows=573 width=0) (actual time=0.016..0.016 rows=48 loops=2)

  • Index Cond: (employee_id = em_3.id)
103. 0.958 0.958 ↓ 1.5 3,922 2

Bitmap Index Scan on i_timeblock_start_delflag (cost=0.00..35.14 rows=2,658 width=0) (actual time=0.479..0.479 rows=3,922 loops=2)

  • Index Cond: ((start_date >= '2019-11-03'::date) AND (start_date <= '2019-11-09'::date))
104. 0.048 0.048 ↑ 1.0 1 3

Index Scan using i_breaktime_time_id on break_time bt_1 (cost=0.56..3.28 rows=1 width=12) (actual time=0.016..0.016 rows=1 loops=3)

  • Index Cond: (timeblock_id = tb_1.id)
  • Filter: (COALESCE(paid, 'N'::bpchar) = 'N'::bpchar)
105. 0.016 0.016 ↑ 1.0 1 2

Index Scan using i_schedule_id on schedule sc_3 (cost=0.43..2.45 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=2)

  • Index Cond: (id = tb_1.schedule_id)
  • Filter: (department_id = ANY ('{28656,26789,23735,24305}'::bigint[]))
106. 0.020 0.020 ↓ 2.0 2 2

Index Scan using i_loc_corp_id on location lo_3 (cost=0.29..2.31 rows=1 width=13) (actual time=0.008..0.010 rows=2 loops=2)

  • Index Cond: (corporation_id = 79500)
  • Filter: (id = ANY ('{135906,124925}'::bigint[]))
  • Rows Removed by Filter: 2
107. 0.068 0.068 ↑ 1.0 1 2

Index Only Scan using timezone_postal_idx on timezone tz_2 (cost=0.43..2.45 rows=1 width=7) (actual time=0.034..0.034 rows=1 loops=2)

  • Index Cond: (postal_code = split_part((lo_3.zip)::text, '-'::text, 1))
  • Heap Fetches: 2