explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BuKL : test

Settings
# exclusive inclusive rows x rows loops node
1. 34.091 10,229.105 ↓ 2.2 25,874 1

Group (cost=662,534.79..696,729.18 rows=11,611 width=1,653) (actual time=10,187.688..10,229.105 rows=25,874 loops=1)

  • Group Key: e.id, (concat((e.first_name)::text, (e.last_name)::text)), (CASE WHEN (e.pay_region_id = 1) THEN 'West India'::text WHEN (e.pay_region_id = 2) THEN 'Rest India'::text ELSE ''::text END), att.imei, (concat((man.first_name)::text, (man.last_name)::text)), man.user_id, rl.role_name, b.branch_name, b.branch_code, cty.city_name, sm.state, gsv1.name, gsv2.name, sh.shift_name, ((timezone('utc'::text, ((to_timestamp((date_part('epoch'::text, al.check_in_time) + (((((tz.operator)::text || ''::text) || (tz.difference)::text))::integer)::double precision)))::time without time zone)::time with time zone))::time without time zone), al.check_in_lat, al.check_in_long, al.check_in_selfie, al.check_out_selfie, ((SubPlan 1)), att.check_in_late, al.check_in_distance_variation, al.check_in_distance_remarks, al.check_out_distance_remarks, (CASE WHEN (((@ att.regularize_flag) = 1) OR ((@ att.approval_flag) = 1)) THEN 'Approved'::text WHEN ((@ aa1.approval_flag) = 1) THEN 'Approved'::text WHEN ((@ aa1.approval_flag) = 2) THEN 'Rejected'::text WHEN ((@ aa1.approval_flag) = 0) THEN 'Pending'::text ELSE NULL::text END), (CASE WHEN ((@ aa1.approval_flag) = 2) THEN ch_in.attendance_reason ELSE NULL::citext END), qc_ch_in.attendance_reason, (CASE WHEN ((@ att.checkout_flag) = 1) THEN (timezone('utc'::text, ((to_timestamp((date_part('epoch'::text, al.check_out_time) + (((((tz.operator)::text || ''::text) || (tz.difference)::text))::integer)::double precision)))::time without time zone)::time with time zone))::time without time zone ELSE NULL::time without time zone END), (CASE WHEN ((@ att.checkout_flag) = 1) THEN al.check_out_lat ELSE NULL::character varying END), (CASE WHEN ((@ att.checkout_flag) = 1) THEN al.check_out_long ELSE NULL::character varying END), (CASE WHEN ((@ att.checkout_flag) = 1) THEN (SubPlan 2) ELSE NULL::text END), (CASE WHEN ((@ att.checkout_flag) = 1) THEN att.check_out_early ELSE NULL::character varying END), (CASE WHEN ((@ att.checkout_flag) = 1) THEN al.check_out_distance_variation ELSE NULL::numeric END), (CASE WHEN (((@ att.regularize_flag) = 1) OR ((@ att.approval_flag) = 1)) THEN 'Approved'::text WHEN ((@ aa2.approval_flag) = 1) THEN 'Approved'::text WHEN ((@ aa2.approval_flag) = 2) THEN 'Rejected'::text WHEN ((@ aa2.approval_flag) = 0) THEN 'Pending'::text ELSE NULL::text END), ch_out.attendance_reason, qc_ch_out.attendance_reason, (CASE WHEN ((((att.attendance_type)::text = 'P'::text) AND ((@ att.approval_flag) = 1)) OR (((att.attendance_type)::text = 'L'::text) AND (el.approval_flag = 1)) OR (((att.attendance_type)::text = 'H'::text) AND (eh.approval_flag = 1)) OR (((att.attendance_type)::text = 'M'::text) AND (em.approval_flag = 1)) OR (((att.attendance_type)::text = 'W'::text) AND (ew.approval_flag = 1))) THEN 'Approved'::text WHEN ((((att.attendance_type)::text = 'P'::text) AND ((@ att.approval_flag) = 0)) OR (el.approval_flag = 0) OR (eh.approval_flag = 0) OR (em.approval_flag = 0) OR (ew.approval_flag = 0)) THEN 'Waiting for Approval'::text WHEN ((((att.attendance_type)::text = 'P'::text) AND (el.approval_flag = 2)) OR (((att.attendance_type)::text = 'H'::text) AND (eh.approval_flag = 2)) OR (((att.attendance_type)::text = 'M'::text) AND (em.approval_flag = 2)) OR (((att.attendance_type)::text = 'W'::text) AND (ew.approval_flag = 2))) THEN 'Rejected'::text WHEN ((@ att.approval_flag) = 1) THEN 'Approved'::text ELSE 'Waiting for Approval'::text END), (CASE WHEN ((att.attendance_type)::text = 'P'::text) THEN 'Marked'::text WHEN ((att.attendance_type)::text = 'L'::text) THEN 'Marked'::text WHEN (((att.attendance_type)::text = 'HL'::text) OR ((att.attendance_type)::text = 'HP'::text)) THEN 'Marked'::text WHEN ((att.attendance_type)::text = 'W'::text) THEN 'Marked'::text WHEN ((att.attendance_type)::text = 'A'::text) THEN 'Marked Absent'::text WHEN (((e.customer_id IS NULL) AND (cehv.id IS NOT NULL)) OR ((e.customer_id IS NOT NULL) AND (ehv.id IS NOT NULL))) THEN 'Holiday'::text WHEN ((el.employee_id IS NOT NULL) OR (eh.employee_id IS NOT NULL) OR (ew.employee_id IS NOT NULL) OR (em.employee_id IS NOT NULL)) THEN 'Marked'::text ELSE 'Not Marked'::text END), (CASE WHEN ((att.attendance_type)::text = 'P'::text) THEN check_in.attendance_reason WHEN (((att.attendance_type)::text = 'L'::text) OR (el.employee_id IS NOT NULL)) THEN lt.leave_type_name WHEN (((att.attendance_type)::text = 'HL'::text) OR ((att.attendance_type)::text = 'HP'::text)) THEN 'Half Day'::citext WHEN ((att.attendance_type)::text = 'W'::text) THEN 'Week off'::citext WHEN ((att.attendance_type)::text = 'A'::text) THEN 'Marked Absent'::citext WHEN (((e.customer_id IS NULL) AND (cehv.id IS NOT NULL)) OR ((e.customer_id IS NOT NULL) AND (ehv.id IS NOT NULL))) THEN 'Holiday'::citext WHEN (eh.employee_id IS NOT NULL) THEN 'Holiday'::citext WHEN (ew.employee_id IS NOT NULL) THEN 'Week off'::citext WHEN (em.employee_id IS NOT NULL) THEN 'Market Closed'::citext ELSE 'Not Marked'::citext END), (CASE WHEN ((att.on_behalf_attendance IS NOT NULL) AND (concat((man.first_name)::text, (man.last_name)::text) IS NOT NULL)) THEN concat((man.first_name)::text, (man.last_name)::text) ELSE NULL::text END), (CASE WHEN ((((al.uniform)::text = '0'::text) OR ((al.uniform)::text = 'N'::text)) AND (att.on_behalf_attendance IS NULL)) THEN 'No'::text WHEN (((al.uniform)::text = '1'::text) AND (att.on_behalf_attendance IS NULL)) THEN 'Yes'::text WHEN (((al.uniform)::text = '2'::text) AND (att.on_behalf_attendance IS NULL)) THEN 'Not Recieved'::text ELSE NULL::text END), (CASE WHEN ((al.samsung_logo)::text = 'N'::text) THEN 'No'::text WHEN ((al.samsung_logo)::text = 'Y'::text) THEN 'Yes'::text ELSE 'N/A'::text END), (CASE WHEN ((al.blue_color_check)::text = 'N'::text) THEN 'No'::text WHEN ((al.blue_color_check)::text = 'Y'::text) THEN 'Yes'::text ELSE NULL::text END), al.blue_color_percentage, (CASE WHEN ((al.face_detection_flag)::text = 'Y'::text) THEN 'Yes'::text WHEN ((al.face_detection_flag)::text = 'N'::text) THEN 'No'::text ELSE 'N/A'::text END), (CASE WHEN (e.active_flag = 1) THEN 'Active'::text ELSE 'Inactive'::text END)
2. 78.235 10,195.014 ↓ 2.2 25,892 1

Sort (cost=662,534.79..662,563.82 rows=11,611 width=1,621) (actual time=10,187.685..10,195.014 rows=25,892 loops=1)

  • Sort Key: e.id, (concat((e.first_name)::text, (e.last_name)::text)), (CASE WHEN (e.pay_region_id = 1) THEN 'West India'::text WHEN (e.pay_region_id = 2) THEN 'Rest India'::text ELSE ''::text END), att.imei, (concat((man.first_name)::text, (man.last_name)::text)), man.user_id, rl.role_name, b.branch_name, b.branch_code, cty.city_name, sm.state, gsv1.name, gsv2.name, sh.shift_name, ((timezone('utc'::text, ((to_timestamp((date_part('epoch'::text, al.check_in_time) + (((((tz.operator)::text || ''::text) || (tz.difference)::text))::integer)::double precision)))::time without time zone)::time with time zone))::time without time zone), al.check_in_lat, al.check_in_long, al.check_in_selfie, al.check_out_selfie, ((SubPlan 1)), att.check_in_late, al.check_in_distance_variation, al.check_in_distance_remarks, al.check_out_distance_remarks, (CASE WHEN (((@ att.regularize_flag) = 1) OR ((@ att.approval_flag) = 1)) THEN 'Approved'::text WHEN ((@ aa1.approval_flag) = 1) THEN 'Approved'::text WHEN ((@ aa1.approval_flag) = 2) THEN 'Rejected'::text WHEN ((@ aa1.approval_flag) = 0) THEN 'Pending'::text ELSE NULL::text END), (CASE WHEN ((@ aa1.approval_flag) = 2) THEN ch_in.attendance_reason ELSE NULL::citext END), qc_ch_in.attendance_reason, (CASE WHEN ((@ att.checkout_flag) = 1) THEN (timezone('utc'::text, ((to_timestamp((date_part('epoch'::text, al.check_out_time) + (((((tz.operator)::text || ''::text) || (tz.difference)::text))::integer)::double precision)))::time without time zone)::time with time zone))::time without time zone ELSE NULL::time without time zone END), (CASE WHEN ((@ att.checkout_flag) = 1) THEN al.check_out_lat ELSE NULL::character varying END), (CASE WHEN ((@ att.checkout_flag) = 1) THEN al.check_out_long ELSE NULL::character varying END), (CASE WHEN ((@ att.checkout_flag) = 1) THEN (SubPlan 2) ELSE NULL::text END), (CASE WHEN ((@ att.checkout_flag) = 1) THEN att.check_out_early ELSE NULL::character varying END), (CASE WHEN ((@ att.checkout_flag) = 1) THEN al.check_out_distance_variation ELSE NULL::numeric END), (CASE WHEN (((@ att.regularize_flag) = 1) OR ((@ att.approval_flag) = 1)) THEN 'Approved'::text WHEN ((@ aa2.approval_flag) = 1) THEN 'Approved'::text WHEN ((@ aa2.approval_flag) = 2) THEN 'Rejected'::text WHEN ((@ aa2.approval_flag) = 0) THEN 'Pending'::text ELSE NULL::text END), ch_out.attendance_reason, qc_ch_out.attendance_reason, (CASE WHEN ((((att.attendance_type)::text = 'P'::text) AND ((@ att.approval_flag) = 1)) OR (((att.attendance_type)::text = 'L'::text) AND (el.approval_flag = 1)) OR (((att.attendance_type)::text = 'H'::text) AND (eh.approval_flag = 1)) OR (((att.attendance_type)::text = 'M'::text) AND (em.approval_flag = 1)) OR (((att.attendance_type)::text = 'W'::text) AND (ew.approval_flag = 1))) THEN 'Approved'::text WHEN ((((att.attendance_type)::text = 'P'::text) AND ((@ att.approval_flag) = 0)) OR (el.approval_flag = 0) OR (eh.approval_flag = 0) OR (em.approval_flag = 0) OR (ew.approval_flag = 0)) THEN 'Waiting for Approval'::text WHEN ((((att.attendance_type)::text = 'P'::text) AND (el.approval_flag = 2)) OR (((att.attendance_type)::text = 'H'::text) AND (eh.approval_flag = 2)) OR (((att.attendance_type)::text = 'M'::text) AND (em.approval_flag = 2)) OR (((att.attendance_type)::text = 'W'::text) AND (ew.approval_flag = 2))) THEN 'Rejected'::text WHEN ((@ att.approval_flag) = 1) THEN 'Approved'::text ELSE 'Waiting for Approval'::text END), (CASE WHEN ((att.attendance_type)::text = 'P'::text) THEN 'Marked'::text WHEN ((att.attendance_type)::text = 'L'::text) THEN 'Marked'::text WHEN (((att.attendance_type)::text = 'HL'::text) OR ((att.attendance_type)::text = 'HP'::text)) THEN 'Marked'::text WHEN ((att.attendance_type)::text = 'W'::text) THEN 'Marked'::text WHEN ((att.attendance_type)::text = 'A'::text) THEN 'Marked Absent'::text WHEN (((e.customer_id IS NULL) AND (cehv.id IS NOT NULL)) OR ((e.customer_id IS NOT NULL) AND (ehv.id IS NOT NULL))) THEN 'Holiday'::text WHEN ((el.employee_id IS NOT NULL) OR (eh.employee_id IS NOT NULL) OR (ew.employee_id IS NOT NULL) OR (em.employee_id IS NOT NULL)) THEN 'Marked'::text ELSE 'Not Marked'::text END), (CASE WHEN ((att.attendance_type)::text = 'P'::text) THEN check_in.attendance_reason WHEN (((att.attendance_type)::text = 'L'::text) OR (el.employee_id IS NOT NULL)) THEN lt.leave_type_name WHEN (((att.attendance_type)::text = 'HL'::text) OR ((att.attendance_type)::text = 'HP'::text)) THEN 'Half Day'::citext WHEN ((att.attendance_type)::text = 'W'::text) THEN 'Week off'::citext WHEN ((att.attendance_type)::text = 'A'::text) THEN 'Marked Absent'::citext WHEN (((e.customer_id IS NULL) AND (cehv.id IS NOT NULL)) OR ((e.customer_id IS NOT NULL) AND (ehv.id IS NOT NULL))) THEN 'Holiday'::citext WHEN (eh.employee_id IS NOT NULL) THEN 'Holiday'::citext WHEN (ew.employee_id IS NOT NULL) THEN 'Week off'::citext WHEN (em.employee_id IS NOT NULL) THEN 'Market Closed'::citext ELSE 'Not Marked'::citext END), (CASE WHEN ((att.on_behalf_attendance IS NOT NULL) AND (concat((man.first_name)::text, (man.last_name)::text) IS NOT NULL)) THEN concat((man.first_name)::text, (man.last_name)::text) ELSE NULL::text END), (CASE WHEN ((((al.uniform)::text = '0'::text) OR ((al.uniform)::text = 'N'::text)) AND (att.on_behalf_attendance IS NULL)) THEN 'No'::text WHEN (((al.uniform)::text = '1'::text) AND (att.on_behalf_attendance IS NULL)) THEN 'Yes'::text WHEN (((al.uniform)::text = '2'::text) AND (att.on_behalf_attendance IS NULL)) THEN 'Not Recieved'::text ELSE NULL::text END), (CASE WHEN ((al.samsung_logo)::text = 'N'::text) THEN 'No'::text WHEN ((al.samsung_logo)::text = 'Y'::text) THEN 'Yes'::text ELSE 'N/A'::text END), (CASE WHEN ((al.blue_color_check)::text = 'N'::text) THEN 'No'::text WHEN ((al.blue_color_check)::text = 'Y'::text) THEN 'Yes'::text ELSE NULL::text END), al.blue_color_percentage, (CASE WHEN ((al.face_detection_flag)::text = 'Y'::text) THEN 'Yes'::text WHEN ((al.face_detection_flag)::text = 'N'::text) THEN 'No'::text ELSE 'N/A'::text END), (CASE WHEN (e.active_flag = 1) THEN 'Active'::text ELSE 'Inactive'::text END)
  • Sort Method: external sort Disk: 10296kB
3. 344.596 10,116.779 ↓ 2.2 25,892 1

Hash Left Join (cost=597,465.18..653,574.86 rows=11,611 width=1,621) (actual time=9,045.873..10,116.779 rows=25,892 loops=1)

  • Hash Cond: (sh.id = se.shift_id)
4. 17.056 9,601.543 ↓ 2.2 25,892 1

Hash Left Join (cost=597,461.69..620,599.48 rows=11,611 width=1,451) (actual time=9,045.725..9,601.543 rows=25,892 loops=1)

  • Hash Cond: (gom.level2 = gsv2.id)
5. 18.362 9,584.388 ↓ 2.2 25,892 1

Hash Left Join (cost=597,451.01..620,429.15 rows=11,611 width=1,448) (actual time=9,045.614..9,584.388 rows=25,892 loops=1)

  • Hash Cond: (gom.level1 = gsv1.id)
6. 20.829 9,565.915 ↓ 2.2 25,892 1

Hash Join (cost=597,440.33..620,258.81 rows=11,611 width=1,445) (actual time=9,045.491..9,565.915 rows=25,892 loops=1)

  • Hash Cond: ((b.timezone)::text = (tz.time_zone)::text)
7. 16.529 9,545.076 ↓ 2.2 25,892 1

Hash Left Join (cost=597,439.28..620,098.12 rows=11,611 width=1,439) (actual time=9,045.462..9,545.076 rows=25,892 loops=1)

  • Hash Cond: (al.reason_id = check_in.id)
8. 13.126 9,528.099 ↓ 2.2 25,892 1

Hash Left Join (cost=597,388.14..619,887.56 rows=11,611 width=1,426) (actual time=9,045.000..9,528.099 rows=25,892 loops=1)

  • Hash Cond: (att.check_out_qc_review = qc_ch_out.id)
9. 25.944 9,514.527 ↓ 2.2 25,892 1

Hash Join (cost=597,337.00..619,792.87 rows=11,611 width=1,413) (actual time=9,044.548..9,514.527 rows=25,892 loops=1)

  • Hash Cond: (b.id = gom.outlet_id)
10. 13.743 9,464.166 ↓ 2.2 25,892 1

Hash Left Join (cost=595,256.50..617,552.21 rows=11,748 width=1,413) (actual time=9,019.862..9,464.166 rows=25,892 loops=1)

  • Hash Cond: (att.check_in_qc_review = qc_ch_in.id)
11. 13.991 9,450.004 ↓ 2.2 25,892 1

Hash Left Join (cost=595,205.35..617,457.00 rows=11,748 width=1,400) (actual time=9,019.436..9,450.004 rows=25,892 loops=1)

  • Hash Cond: (aa2.reason_id = ch_out.id)
12. 16.857 9,435.617 ↓ 2.2 25,892 1

Hash Left Join (cost=595,154.21..617,281.01 rows=11,748 width=1,387) (actual time=9,019.029..9,435.617 rows=25,892 loops=1)

  • Hash Cond: (aa1.reason_id = ch_in.id)
13. 16.564 9,418.305 ↓ 2.2 25,892 1

Hash Left Join (cost=595,103.07..617,105.02 rows=11,748 width=1,374) (actual time=9,018.568..9,418.305 rows=25,892 loops=1)

  • Hash Cond: (att.shift_id = sh.id)
14. 15.922 9,401.710 ↓ 2.2 25,892 1

Hash Join (cost=595,099.31..616,942.53 rows=11,748 width=858) (actual time=9,018.533..9,401.710 rows=25,892 loops=1)

  • Hash Cond: (states.state_id = sm.id)
15. 16.122 9,385.755 ↓ 2.2 25,892 1

Hash Join (cost=595,096.24..616,777.93 rows=11,748 width=853) (actual time=9,018.482..9,385.755 rows=25,892 loops=1)

  • Hash Cond: (b.country_id = countries.id)
16. 17.466 9,369.601 ↓ 2.2 25,892 1

Hash Join (cost=595,093.84..616,613.99 rows=11,748 width=857) (actual time=9,018.439..9,369.601 rows=25,892 loops=1)

  • Hash Cond: (b.state_id = states.id)
17. 20.942 9,352.114 ↓ 2.2 25,892 1

Hash Join (cost=595,091.58..616,450.19 rows=11,748 width=857) (actual time=9,018.407..9,352.114 rows=25,892 loops=1)

  • Hash Cond: (b.city_id = cty.id)
18. 18.195 9,329.296 ↓ 2.2 25,892 1

Hash Join (cost=594,885.70..616,082.78 rows=11,748 width=852) (actual time=9,016.486..9,329.296 rows=25,892 loops=1)

  • Hash Cond: (e.role_id = rl.id)
19. 14.290 9,310.670 ↓ 2.2 25,892 1

Merge Left Join (cost=594,837.66..615,873.20 rows=11,748 width=838) (actual time=9,016.041..9,310.670 rows=25,892 loops=1)

  • Merge Cond: (e.id = cehv.id)
  • Join Filter: (ehv.holiday_date = '2019-05-12'::date)
20. 13.415 9,296.235 ↓ 2.2 25,892 1

Merge Left Join (cost=594,468.08..615,474.23 rows=11,748 width=838) (actual time=9,015.893..9,296.235 rows=25,892 loops=1)

  • Merge Cond: (e.id = ehv.id)
21. 15.451 9,282.282 ↓ 2.2 25,892 1

Merge Left Join (cost=567,882.48..588,782.67 rows=11,748 width=830) (actual time=9,015.353..9,282.282 rows=25,892 loops=1)

  • Merge Cond: (e.id = ewo.emp_id)
22. 17.945 9,252.209 ↓ 2.2 25,892 1

Nested Loop Left Join (cost=566,189.27..587,059.60 rows=11,748 width=830) (actual time=9,002.492..9,252.209 rows=25,892 loops=1)

23. 11.147 9,156.588 ↓ 2.2 25,892 1

Nested Loop (cost=566,188.85..572,895.26 rows=11,748 width=802) (actual time=9,002.482..9,156.588 rows=25,892 loops=1)

24. 24.097 9,041.873 ↓ 2.2 25,892 1

Merge Left Join (cost=566,188.56..566,250.49 rows=11,748 width=755) (actual time=9,002.470..9,041.873 rows=25,892 loops=1)

  • Merge Cond: (e.id = el.employee_id)
25. 60.561 8,992.278 ↓ 2.2 25,878 1

Sort (cost=559,942.72..559,972.09 rows=11,748 width=737) (actual time=8,977.236..8,992.278 rows=25,878 loops=1)

  • Sort Key: e.id
  • Sort Method: external merge Disk: 8288kB
26. 1,319.246 8,931.717 ↓ 2.2 25,878 1

Hash Right Join (cost=359,909.76..555,291.55 rows=11,748 width=737) (actual time=6,024.980..8,931.717 rows=25,878 loops=1)

  • Hash Cond: (aa1.attendance_log_id = al.id)
27. 1,621.887 1,621.887 ↑ 1.1 3,930,424 1

Seq Scan on attendance_approvals aa1 (cost=0.00..138,196.33 rows=4,145,894 width=12) (actual time=0.133..1,621.887 rows=3,930,424 loops=1)

  • Filter: ((action = 1) AND (active_flag = 1))
  • Rows Removed by Filter: 399035
28. 36.708 5,990.584 ↓ 2.2 25,878 1

Hash (cost=358,672.91..358,672.91 rows=11,748 width=733) (actual time=5,990.584..5,990.584 rows=25,878 loops=1)

  • Buckets: 8192 Batches: 4 Memory Usage: 3594kB
29. 159.154 5,953.876 ↓ 2.2 25,878 1

Hash Right Join (cost=214,537.32..358,672.91 rows=11,748 width=733) (actual time=5,894.189..5,953.876 rows=25,878 loops=1)

  • Hash Cond: (aa2.attendance_log_id = al.id)
30. 852.054 852.054 ↑ 1.0 343,946 1

Seq Scan on attendance_approvals aa2 (cost=0.00..138,196.33 rows=359,207 width=12) (actual time=0.133..852.054 rows=343,946 loops=1)

  • Filter: ((action = 2) AND (active_flag = 1))
  • Rows Removed by Filter: 3985513
31. 41.722 4,942.668 ↓ 2.2 25,878 1

Hash (cost=213,311.47..213,311.47 rows=11,748 width=725) (actual time=4,942.668..4,942.668 rows=25,878 loops=1)

  • Buckets: 8192 Batches: 4 Memory Usage: 3593kB
32. 6.088 4,900.946 ↓ 2.2 25,878 1

Nested Loop Left Join (cost=17,567.13..213,311.47 rows=11,748 width=725) (actual time=447.349..4,900.946 rows=25,878 loops=1)

33. 2,049.982 4,791.346 ↓ 2.2 25,878 1

Nested Loop Left Join (cost=17,566.70..144,007.17 rows=11,748 width=187) (actual time=447.338..4,791.346 rows=25,878 loops=1)

  • Join Filter: (em.employee_id = e.id)
  • Rows Removed by Join Filter: 22485480
34. 31.020 1,576.944 ↓ 2.2 25,876 1

Nested Loop Left Join (cost=17,566.70..139,660.92 rows=11,748 width=179) (actual time=416.998..1,576.944 rows=25,876 loops=1)

35. 619.348 1,416.544 ↓ 2.2 25,876 1

Nested Loop Left Join (cost=17,566.14..56,381.36 rows=11,748 width=125) (actual time=416.979..1,416.544 rows=25,876 loops=1)

  • Join Filter: (eh.employee_id = e.id)
  • Rows Removed by Join Filter: 6882855
36. 20.431 434.932 ↓ 2.2 25,876 1

Hash Right Join (cost=17,566.14..55,197.13 rows=11,748 width=117) (actual time=408.221..434.932 rows=25,876 loops=1)

  • Hash Cond: (ew.employee_id = e.id)
37. 222.406 222.406 ↓ 150.6 9,638 1

Seq Scan on employee_applied_weekoffs ew (cost=0.00..37,630.68 rows=64 width=8) (actual time=216.031..222.406 rows=9,638 loops=1)

  • Filter: (('2019-05-12'::date >= from_date) AND ('2019-05-12'::date <= to_date))
  • Rows Removed by Filter: 1061566
38. 12.642 192.095 ↓ 2.2 25,869 1

Hash (cost=17,419.29..17,419.29 rows=11,748 width=109) (actual time=192.095..192.095 rows=25,869 loops=1)

  • Buckets: 32768 (originally 16384) Batches: 1 (originally 1) Memory Usage: 4059kB
39. 6.735 179.453 ↓ 2.2 25,869 1

Hash Join (cost=13,453.89..17,419.29 rows=11,748 width=109) (actual time=108.059..179.453 rows=25,869 loops=1)

  • Hash Cond: (urg.role_group_id = ass.role_group_id)
40. 39.933 172.674 ↓ 1.5 26,803 1

Hash Join (cost=13,449.79..17,231.40 rows=17,685 width=113) (actual time=108.003..172.674 rows=26,803 loops=1)

  • Hash Cond: (urg.user_id = u.id)
41. 25.061 25.061 ↓ 1.0 108,838 1

Seq Scan on user_role_groups urg (cost=0.00..3,206.45 rows=106,214 width=8) (actual time=0.099..25.061 rows=108,838 loops=1)

  • Filter: (active_flag = 1)
  • Rows Removed by Filter: 29004
42. 14.875 107.680 ↓ 1.5 25,872 1

Hash (cost=13,226.85..13,226.85 rows=17,835 width=113) (actual time=107.680..107.680 rows=25,872 loops=1)

  • Buckets: 32768 (originally 32768) Batches: 2 (originally 1) Memory Usage: 3841kB
43. 14.519 92.805 ↓ 1.5 25,872 1

Hash Join (cost=4,845.25..13,226.85 rows=17,835 width=113) (actual time=37.236..92.805 rows=25,872 loops=1)

  • Hash Cond: (e.id = u.ref_id)
44. 41.409 41.409 ↑ 1.4 25,872 1

Seq Scan on employees e (cost=0.00..8,068.43 rows=35,952 width=109) (actual time=0.095..41.409 rows=25,872 loops=1)

  • Filter: ((customer_id = 7) AND (active_flag = 1))
  • Rows Removed by Filter: 81158
45. 10.115 36.877 ↑ 1.0 52,974 1

Hash (cost=4,180.95..4,180.95 rows=53,144 width=8) (actual time=36.877..36.877 rows=52,974 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2582kB
46. 26.762 26.762 ↑ 1.0 52,989 1

Seq Scan on users u (cost=0.00..4,180.95 rows=53,144 width=8) (actual time=0.079..26.762 rows=52,989 loops=1)

  • Filter: (customer_id = 7)
  • Rows Removed by Filter: 54127
47. 0.015 0.044 ↓ 1.0 97 1

Hash (cost=2.93..2.93 rows=93 width=4) (actual time=0.044..0.044 rows=97 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
48. 0.029 0.029 ↓ 1.0 97 1

Seq Scan on attendance_setups ass (cost=0.00..2.93 rows=93 width=4) (actual time=0.006..0.029 rows=97 loops=1)

49. 353.634 362.264 ↓ 266.0 266 25,876

Materialize (cost=0.00..1,008.01 rows=1 width=8) (actual time=0.000..0.014 rows=266 loops=25,876)

50. 8.630 8.630 ↓ 266.0 266 1

Seq Scan on employee_applied_holidays eh (cost=0.00..1,008.00 rows=1 width=8) (actual time=8.509..8.630 rows=266 loops=1)

  • Filter: (('2019-05-12'::date >= from_date) AND ('2019-05-12'::date <= to_date))
  • Rows Removed by Filter: 32556
51. 129.380 129.380 ↑ 1.0 1 25,876

Index Scan using "Unique_Constraint_attendance_status" on attendances att (cost=0.56..7.08 rows=1 width=58) (actual time=0.005..0.005 rows=1 loops=25,876)

  • Index Cond: ((employee_id = e.id) AND (start_date = '2019-05-12 00:00:00'::timestamp without time zone) AND (delete_flag = 0))
52. 1,134.427 1,164.420 ↓ 173.8 869 25,876

Materialize (cost=0.00..3,465.16 rows=5 width=8) (actual time=0.001..0.045 rows=869 loops=25,876)

53. 29.993 29.993 ↓ 173.8 869 1

Seq Scan on employee_applied_marketoffs em (cost=0.00..3,465.14 rows=5 width=8) (actual time=29.599..29.993 rows=869 loops=1)

  • Filter: (('2019-05-12'::date >= from_date) AND ('2019-05-12'::date <= to_date))
  • Rows Removed by Filter: 108523
54. 103.512 103.512 ↑ 1.0 1 25,878

Index Scan using "Idx_attendnace_logs" on attendance_logs al (cost=0.43..5.89 rows=1 width=546) (actual time=0.003..0.004 rows=1 loops=25,878)

  • Index Cond: (attendance_id = att.id)
  • Filter: (attendance_flag = 1)
  • Rows Removed by Filter: 0
55. 0.424 25.498 ↓ 1.4 743 1

Sort (cost=6,245.84..6,247.15 rows=523 width=18) (actual time=25.228..25.498 rows=743 loops=1)

  • Sort Key: el.employee_id
  • Sort Method: quicksort Memory: 76kB
56. 0.173 25.074 ↓ 1.4 743 1

Hash Left Join (cost=6.17..6,222.22 rows=523 width=18) (actual time=19.697..25.074 rows=743 loops=1)

  • Hash Cond: (el.leave_type = lt.id)
57. 24.845 24.845 ↓ 1.4 743 1

Seq Scan on employee_leaves el (cost=0.00..6,208.86 rows=523 width=12) (actual time=19.626..24.845 rows=743 loops=1)

  • Filter: (('2019-05-12'::date >= from_date) AND ('2019-05-12'::date <= to_date) AND (active_flag = 1))
  • Rows Removed by Filter: 179696
58. 0.020 0.056 ↓ 1.1 149 1

Hash (cost=4.41..4.41 rows=141 width=14) (actual time=0.056..0.056 rows=149 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
59. 0.036 0.036 ↓ 1.1 149 1

Seq Scan on leave_types lt (cost=0.00..4.41 rows=141 width=14) (actual time=0.010..0.036 rows=149 loops=1)

60. 103.568 103.568 ↑ 1.0 1 25,892

Index Scan using branches_pkey on branches b (cost=0.29..0.56 rows=1 width=47) (actual time=0.003..0.004 rows=1 loops=25,892)

  • Index Cond: (id = e.branch_id)
61. 77.676 77.676 ↑ 1.0 1 25,892

Index Scan using employees_pkey on employees man (cost=0.42..1.20 rows=1 width=36) (actual time=0.002..0.003 rows=1 loops=25,892)

  • Index Cond: (id = e.manager_id)
62. 4.662 14.622 ↓ 142.5 11,397 1

Sort (cost=1,693.21..1,693.41 rows=80 width=4) (actual time=12.852..14.622 rows=11,397 loops=1)

  • Sort Key: ewo.emp_id
  • Sort Method: quicksort Memory: 921kB
63. 9.960 9.960 ↓ 142.9 11,436 1

Seq Scan on employee_weekoff ewo (cost=0.00..1,690.69 rows=80 width=4) (actual time=0.095..9.960 rows=11,436 loops=1)

  • Filter: ((active_flag = 1) AND ('1'::double precision = (weekoff_id)::double precision))
  • Rows Removed by Filter: 50974
64. 0.007 0.538 ↓ 0.0 0 1

Sort (cost=26,585.60..26,617.00 rows=12,560 width=8) (actual time=0.538..0.538 rows=0 loops=1)

  • Sort Key: ehv.id
  • Sort Method: quicksort Memory: 25kB
65. 0.002 0.531 ↓ 0.0 0 1

Subquery Scan on ehv (cost=25,228.08..25,730.48 rows=12,560 width=8) (actual time=0.531..0.531 rows=0 loops=1)

66. 0.000 0.529 ↓ 0.0 0 1

Unique (cost=25,228.08..25,604.88 rows=12,560 width=892) (actual time=0.529..0.529 rows=0 loops=1)

67. 0.032 0.529 ↓ 0.0 0 1

Sort (cost=25,228.08..25,259.48 rows=12,560 width=892) (actual time=0.529..0.529 rows=0 loops=1)

  • Sort Key: h.holiday_name, h.holiday_date, (to_char((h.holiday_date)::timestamp with time zone, 'dd Mon, yyyy'::text)), e_1.id, e_1.branch_id, e_1.user_id, e_1.first_name, b_1.country_id, b_1.state_id, b_1.city_id, e_1.customer_id
  • Sort Method: quicksort Memory: 25kB
68. 0.002 0.497 ↓ 0.0 0 1

Append (cost=7,041.40..19,434.46 rows=12,560 width=892) (actual time=0.497..0.497 rows=0 loops=1)

69. 0.010 0.110 ↓ 0.0 0 1

HashAggregate (cost=7,041.40..7,163.74 rows=8,156 width=99) (actual time=0.110..0.110 rows=0 loops=1)

  • Group Key: h.holiday_name, h.holiday_date, e_1.id, b_1.country_id, b_1.state_id, b_1.city_id
70. 0.000 0.100 ↓ 0.0 0 1

Nested Loop (cost=61.75..6,919.06 rows=8,156 width=67) (actual time=0.100..0.100 rows=0 loops=1)

  • Join Filter: (h.country_id = b_1.country_id)
71. 0.000 0.100 ↓ 0.0 0 1

Nested Loop (cost=61.46..3,280.58 rows=8,213 width=59) (actual time=0.100..0.100 rows=0 loops=1)

72. 0.100 0.100 ↓ 0.0 0 1

Seq Scan on holidays h (cost=0.00..23.74 rows=1 width=28) (actual time=0.100..0.100 rows=0 loops=1)

  • Filter: ((country_flag = 1) AND (active_flag = 1) AND (holiday_date = '2019-05-12'::date))
  • Rows Removed by Filter: 731
73. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on employees e_1 (cost=61.46..3,244.12 rows=1,272 width=35) (never executed)

  • Recheck Cond: ((customer_id = h.customer_id) AND (active_flag = 1))
74. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on index_employee (cost=0.00..61.14 rows=1,272 width=0) (never executed)

  • Index Cond: ((customer_id = h.customer_id) AND (active_flag = 1))
75. 0.000 0.000 ↓ 0.0 0

Index Scan using branches_pkey on branches b_1 (cost=0.29..0.43 rows=1 width=16) (never executed)

  • Index Cond: (id = e_1.branch_id)
76. 0.010 0.230 ↓ 0.0 0 1

HashAggregate (cost=8,282.26..8,348.11 rows=4,390 width=99) (actual time=0.230..0.230 rows=0 loops=1)

  • Group Key: h_1.holiday_name, h_1.holiday_date, e_2.id, b_2.country_id, b_2.state_id, b_2.city_id
77. 0.001 0.220 ↓ 0.0 0 1

Merge Join (cost=8,047.93..8,216.41 rows=4,390 width=67) (actual time=0.220..0.220 rows=0 loops=1)

  • Merge Cond: ((b_2.state_id = sh_1.state_id) AND (h_1.id = sh_1.holiday_id))
78. 0.008 0.219 ↓ 0.0 0 1

Sort (cost=7,359.80..7,380.33 rows=8,213 width=71) (actual time=0.219..0.219 rows=0 loops=1)

  • Sort Key: b_2.state_id, h_1.id
  • Sort Method: quicksort Memory: 25kB
79. 0.001 0.211 ↓ 0.0 0 1

Hash Join (cost=3,470.07..6,825.80 rows=8,213 width=71) (actual time=0.211..0.211 rows=0 loops=1)

  • Hash Cond: (e_2.branch_id = b_2.id)
80. 0.000 0.210 ↓ 0.0 0 1

Nested Loop (cost=61.73..3,304.54 rows=8,213 width=59) (actual time=0.210..0.210 rows=0 loops=1)

81. 0.210 0.210 ↓ 0.0 0 1

Index Scan using holidays_pkey on holidays h_1 (cost=0.28..47.69 rows=1 width=28) (actual time=0.210..0.210 rows=0 loops=1)

  • Filter: ((state_flag = 1) AND (active_flag = 1) AND (holiday_date = '2019-05-12'::date))
  • Rows Removed by Filter: 731
82. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on employees e_2 (cost=61.46..3,244.12 rows=1,272 width=35) (never executed)

  • Recheck Cond: ((customer_id = h_1.customer_id) AND (active_flag = 1))
83. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on index_employee (cost=0.00..61.14 rows=1,272 width=0) (never executed)

  • Index Cond: ((customer_id = h_1.customer_id) AND (active_flag = 1))
84. 0.000 0.000 ↓ 0.0 0

Hash (cost=2,652.04..2,652.04 rows=60,504 width=16) (never executed)

85. 0.000 0.000 ↓ 0.0 0

Seq Scan on branches b_2 (cost=0.00..2,652.04 rows=60,504 width=16) (never executed)

86. 0.000 0.000 ↓ 0.0 0

Sort (cost=688.13..708.85 rows=8,286 width=8) (never executed)

  • Sort Key: sh_1.state_id, sh_1.holiday_id
87. 0.000 0.000 ↓ 0.0 0

Seq Scan on state_holidays sh_1 (cost=0.00..148.86 rows=8,286 width=8) (never executed)

88. 0.000 0.155 ↓ 0.0 0 1

Group (cost=3,796.69..3,797.01 rows=14 width=99) (actual time=0.155..0.155 rows=0 loops=1)

  • Group Key: h_2.holiday_name, h_2.holiday_date, e_3.id, b_3.country_id, b_3.state_id, b_3.city_id
89. 0.010 0.155 ↓ 0.0 0 1

Sort (cost=3,796.69..3,796.73 rows=14 width=67) (actual time=0.155..0.155 rows=0 loops=1)

  • Sort Key: h_2.holiday_name, e_3.id, b_3.country_id, b_3.state_id, b_3.city_id
  • Sort Method: quicksort Memory: 25kB
90. 0.001 0.145 ↓ 0.0 0 1

Merge Join (cost=609.96..3,796.43 rows=14 width=67) (actual time=0.145..0.145 rows=0 loops=1)

  • Merge Cond: (h_2.id = ch.holiday_id)
  • Join Filter: (b_3.city_id = ch.city_id)
91. 0.000 0.144 ↓ 0.0 0 1

Nested Loop (cost=62.02..6,922.48 rows=8,213 width=71) (actual time=0.144..0.144 rows=0 loops=1)

92. 0.000 0.144 ↓ 0.0 0 1

Nested Loop (cost=61.73..3,304.54 rows=8,213 width=59) (actual time=0.144..0.144 rows=0 loops=1)

93. 0.144 0.144 ↓ 0.0 0 1

Index Scan using holidays_pkey on holidays h_2 (cost=0.28..47.69 rows=1 width=28) (actual time=0.144..0.144 rows=0 loops=1)

  • Filter: ((city_flag = 1) AND (active_flag = 1) AND (holiday_date = '2019-05-12'::date))
  • Rows Removed by Filter: 731
94. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on employees e_3 (cost=61.46..3,244.12 rows=1,272 width=35) (never executed)

  • Recheck Cond: ((customer_id = h_2.customer_id) AND (active_flag = 1))
95. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on index_employee (cost=0.00..61.14 rows=1,272 width=0) (never executed)

  • Index Cond: ((customer_id = h_2.customer_id) AND (active_flag = 1))
96. 0.000 0.000 ↓ 0.0 0

Index Scan using branches_pkey on branches b_3 (cost=0.29..0.43 rows=1 width=16) (never executed)

  • Index Cond: (id = e_3.branch_id)
97. 0.000 0.000 ↓ 0.0 0

Sort (cost=472.53..487.28 rows=5,900 width=8) (never executed)

  • Sort Key: ch.holiday_id
98. 0.000 0.000 ↓ 0.0 0

Seq Scan on city_holidays ch (cost=0.00..103.00 rows=5,900 width=8) (never executed)

99. 0.006 0.145 ↓ 0.0 0 1

Sort (cost=369.58..369.58 rows=3 width=4) (actual time=0.145..0.145 rows=0 loops=1)

  • Sort Key: cehv.id
  • Sort Method: quicksort Memory: 25kB
100. 0.001 0.139 ↓ 0.0 0 1

Subquery Scan on cehv (cost=369.49..369.55 rows=3 width=4) (actual time=0.139..0.139 rows=0 loops=1)

101. 0.001 0.138 ↓ 0.0 0 1

HashAggregate (cost=369.49..369.52 rows=3 width=892) (actual time=0.138..0.138 rows=0 loops=1)

  • Group Key: h_3.holiday_name, h_3.holiday_date, (to_char((h_3.holiday_date)::timestamp with time zone, 'dd Mon, yyyy'::text)), e_4.id, e_4.branch_id, e_4.user_id, e_4.first_name, b_4.country_id, b_4.state_id, b_4.city_id, h_3.customer_id
102. 0.002 0.137 ↓ 0.0 0 1

Append (cost=0.71..369.41 rows=3 width=892) (actual time=0.137..0.137 rows=0 loops=1)

103. 0.000 0.111 ↓ 0.0 0 1

Nested Loop (cost=0.71..34.50 rows=1 width=99) (actual time=0.111..0.111 rows=0 loops=1)

  • Join Filter: (b_4.id = e_4.branch_id)
104. 0.000 0.111 ↓ 0.0 0 1

Nested Loop (cost=0.29..27.22 rows=1 width=40) (actual time=0.111..0.111 rows=0 loops=1)

  • Join Filter: (h_3.country_id = b_4.country_id)
105. 0.111 0.111 ↓ 0.0 0 1

Seq Scan on holidays h_3 (cost=0.00..21.92 rows=1 width=28) (actual time=0.111..0.111 rows=0 loops=1)

  • Filter: ((customer_id IS NULL) AND (country_flag = 1) AND (active_flag = 1))
  • Rows Removed by Filter: 731
106. 0.000 0.000 ↓ 0.0 0

Index Scan using indx_branches on branches b_4 (cost=0.29..5.29 rows=1 width=16) (never executed)

  • Index Cond: (customer_id IS NULL)
107. 0.000 0.000 ↓ 0.0 0

Index Scan using index_employee on employees e_4 (cost=0.42..7.25 rows=1 width=31) (never executed)

  • Index Cond: (customer_id IS NULL)
108. 0.001 0.016 ↓ 0.0 0 1

Nested Loop (cost=12.85..196.85 rows=1 width=99) (actual time=0.016..0.016 rows=0 loops=1)

109. 0.005 0.015 ↓ 0.0 0 1

Hash Join (cost=12.57..196.53 rows=1 width=47) (actual time=0.015..0.015 rows=0 loops=1)

  • Hash Cond: (sh_2.state_id = b_5.state_id)
110. 0.005 0.005 ↑ 8,286.0 1 1

Seq Scan on state_holidays sh_2 (cost=0.00..148.86 rows=8,286 width=8) (actual time=0.005..0.005 rows=1 loops=1)

111. 0.000 0.005 ↓ 0.0 0 1

Hash (cost=12.56..12.56 rows=1 width=43) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
112. 0.001 0.005 ↓ 0.0 0 1

Nested Loop (cost=0.71..12.56 rows=1 width=43) (actual time=0.005..0.005 rows=0 loops=1)

  • Join Filter: (b_5.id = e_5.branch_id)
113. 0.004 0.004 ↓ 0.0 0 1

Index Scan using indx_branches on branches b_5 (cost=0.29..5.29 rows=1 width=16) (actual time=0.004..0.004 rows=0 loops=1)

  • Index Cond: (customer_id IS NULL)
114. 0.000 0.000 ↓ 0.0 0

Index Scan using index_employee on employees e_5 (cost=0.42..7.25 rows=1 width=31) (never executed)

  • Index Cond: (customer_id IS NULL)
115. 0.000 0.000 ↓ 0.0 0

Index Scan using holidays_pkey on holidays h_4 (cost=0.28..0.30 rows=1 width=28) (never executed)

  • Index Cond: (id = sh_2.holiday_id)
  • Filter: ((customer_id IS NULL) AND (state_flag = 1) AND (active_flag = 1))
116. 0.000 0.008 ↓ 0.0 0 1

Nested Loop (cost=6.00..138.03 rows=1 width=99) (actual time=0.008..0.008 rows=0 loops=1)

  • Join Filter: (b_6.id = e_6.branch_id)
117. 0.000 0.008 ↓ 0.0 0 1

Nested Loop (cost=5.58..130.76 rows=1 width=40) (actual time=0.008..0.008 rows=0 loops=1)

118. 0.003 0.008 ↓ 0.0 0 1

Hash Join (cost=5.30..130.44 rows=1 width=20) (actual time=0.008..0.008 rows=0 loops=1)

  • Hash Cond: (ch_1.city_id = b_6.city_id)
119. 0.003 0.003 ↑ 5,900.0 1 1

Seq Scan on city_holidays ch_1 (cost=0.00..103.00 rows=5,900 width=8) (actual time=0.003..0.003 rows=1 loops=1)

120. 0.000 0.002 ↓ 0.0 0 1

Hash (cost=5.29..5.29 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
121. 0.002 0.002 ↓ 0.0 0 1

Index Scan using indx_branches on branches b_6 (cost=0.29..5.29 rows=1 width=16) (actual time=0.002..0.002 rows=0 loops=1)

  • Index Cond: (customer_id IS NULL)
122. 0.000 0.000 ↓ 0.0 0

Index Scan using holidays_pkey on holidays h_5 (cost=0.28..0.31 rows=1 width=28) (never executed)

  • Index Cond: (id = ch_1.holiday_id)
  • Filter: ((customer_id IS NULL) AND (city_flag = 1) AND (active_flag = 1))
123. 0.000 0.000 ↓ 0.0 0

Index Scan using index_employee on employees e_6 (cost=0.42..7.25 rows=1 width=31) (never executed)

  • Index Cond: (customer_id IS NULL)
124. 0.228 0.431 ↓ 1.0 1,381 1

Hash (cost=31.35..31.35 rows=1,335 width=22) (actual time=0.431..0.431 rows=1,381 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 91kB
125. 0.203 0.203 ↓ 1.0 1,381 1

Seq Scan on roles rl (cost=0.00..31.35 rows=1,335 width=22) (actual time=0.004..0.203 rows=1,381 loops=1)

126. 1.026 1.876 ↑ 1.0 6,198 1

Hash (cost=128.17..128.17 rows=6,217 width=13) (actual time=1.876..1.876 rows=6,198 loops=1)

  • Buckets: 8192 Batches: 1 Memory Usage: 348kB
127. 0.850 0.850 ↑ 1.0 6,198 1

Seq Scan on cities cty (cost=0.00..128.17 rows=6,217 width=13) (actual time=0.004..0.850 rows=6,198 loops=1)

128. 0.011 0.021 ↓ 1.0 58 1

Hash (cost=1.56..1.56 rows=56 width=8) (actual time=0.021..0.021 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
129. 0.010 0.010 ↓ 1.0 58 1

Seq Scan on states (cost=0.00..1.56 rows=56 width=8) (actual time=0.004..0.010 rows=58 loops=1)

130. 0.003 0.032 ↑ 1.0 5 1

Hash (cost=2.34..2.34 rows=5 width=4) (actual time=0.032..0.032 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
131. 0.018 0.029 ↑ 1.0 5 1

Hash Join (cost=1.11..2.34 rows=5 width=4) (actual time=0.026..0.029 rows=5 loops=1)

  • Hash Cond: (cm.country_id = countries.country_id)
132. 0.002 0.002 ↑ 1.0 13 1

Seq Scan on country_master cm (cost=0.00..1.13 rows=13 width=4) (actual time=0.002..0.002 rows=13 loops=1)

133. 0.005 0.009 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=8) (actual time=0.009..0.009 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
134. 0.004 0.004 ↑ 1.0 5 1

Seq Scan on countries (cost=0.00..1.05 rows=5 width=8) (actual time=0.003..0.004 rows=5 loops=1)

135. 0.017 0.033 ↓ 1.0 96 1

Hash (cost=1.92..1.92 rows=92 width=13) (actual time=0.033..0.033 rows=96 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
136. 0.016 0.016 ↓ 1.0 96 1

Seq Scan on state_master sm (cost=0.00..1.92 rows=92 width=13) (actual time=0.005..0.016 rows=96 loops=1)

137. 0.017 0.031 ↓ 1.1 82 1

Hash (cost=2.78..2.78 rows=78 width=520) (actual time=0.031..0.031 rows=82 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
138. 0.014 0.014 ↓ 1.1 82 1

Seq Scan on shifts sh (cost=0.00..2.78 rows=78 width=520) (actual time=0.004..0.014 rows=82 loops=1)

139. 0.228 0.455 ↑ 1.0 1,430 1

Hash (cost=32.73..32.73 rows=1,473 width=21) (actual time=0.455..0.455 rows=1,430 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 92kB
140. 0.227 0.227 ↑ 1.0 1,430 1

Seq Scan on attendance_reasons ch_in (cost=0.00..32.73 rows=1,473 width=21) (actual time=0.004..0.227 rows=1,430 loops=1)

141. 0.188 0.396 ↑ 1.0 1,430 1

Hash (cost=32.73..32.73 rows=1,473 width=21) (actual time=0.396..0.396 rows=1,430 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 92kB
142. 0.208 0.208 ↑ 1.0 1,430 1

Seq Scan on attendance_reasons ch_out (cost=0.00..32.73 rows=1,473 width=21) (actual time=0.002..0.208 rows=1,430 loops=1)

143. 0.232 0.419 ↑ 1.0 1,430 1

Hash (cost=32.73..32.73 rows=1,473 width=21) (actual time=0.419..0.419 rows=1,430 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 92kB
144. 0.187 0.187 ↑ 1.0 1,430 1

Seq Scan on attendance_reasons qc_ch_in (cost=0.00..32.73 rows=1,473 width=21) (actual time=0.002..0.187 rows=1,430 loops=1)

145. 13.032 24.417 ↑ 1.0 59,744 1

Hash (cost=1,333.00..1,333.00 rows=59,800 width=16) (actual time=24.417..24.417 rows=59,744 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 3313kB
146. 11.385 11.385 ↑ 1.0 59,744 1

Seq Scan on geo_outlet_mapping gom (cost=0.00..1,333.00 rows=59,800 width=16) (actual time=0.080..11.385 rows=59,744 loops=1)

147. 0.260 0.446 ↑ 1.0 1,430 1

Hash (cost=32.73..32.73 rows=1,473 width=21) (actual time=0.446..0.446 rows=1,430 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 92kB
148. 0.186 0.186 ↑ 1.0 1,430 1

Seq Scan on attendance_reasons qc_ch_out (cost=0.00..32.73 rows=1,473 width=21) (actual time=0.002..0.186 rows=1,430 loops=1)

149. 0.244 0.448 ↑ 1.0 1,430 1

Hash (cost=32.73..32.73 rows=1,473 width=21) (actual time=0.448..0.448 rows=1,430 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 92kB
150. 0.204 0.204 ↑ 1.0 1,430 1

Seq Scan on attendance_reasons check_in (cost=0.00..32.73 rows=1,473 width=21) (actual time=0.002..0.204 rows=1,430 loops=1)

151. 0.004 0.010 ↑ 1.0 2 1

Hash (cost=1.02..1.02 rows=2 width=14) (actual time=0.010..0.010 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
152. 0.006 0.006 ↑ 1.0 2 1

Seq Scan on time_zones tz (cost=0.00..1.02 rows=2 width=14) (actual time=0.004..0.006 rows=2 loops=1)

153. 0.060 0.111 ↓ 1.2 363 1

Hash (cost=6.97..6.97 rows=297 width=11) (actual time=0.111..0.111 rows=363 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
154. 0.051 0.051 ↓ 1.2 363 1

Seq Scan on geo_structure_values gsv1 (cost=0.00..6.97 rows=297 width=11) (actual time=0.006..0.051 rows=363 loops=1)

155. 0.058 0.099 ↓ 1.2 363 1

Hash (cost=6.97..6.97 rows=297 width=11) (actual time=0.099..0.099 rows=363 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 24kB
156. 0.041 0.041 ↓ 1.2 363 1

Seq Scan on geo_structure_values gsv2 (cost=0.00..6.97 rows=297 width=11) (actual time=0.002..0.041 rows=363 loops=1)

157. 0.017 0.034 ↓ 1.2 82 1

Hash (cost=2.66..2.66 rows=66 width=4) (actual time=0.034..0.034 rows=82 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
158. 0.017 0.017 ↓ 1.2 82 1

Seq Scan on sessions se (cost=0.00..2.66 rows=66 width=4) (actual time=0.007..0.017 rows=82 loops=1)

159.          

SubPlan (forHash Left Join)

160. 25.892 155.352 ↑ 1.0 1 25,892

Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=25,892)

161. 129.460 129.460 ↑ 20.0 5 25,892

Function Scan on json_each_text (cost=0.00..1.00 rows=100 width=32) (actual time=0.004..0.005 rows=5 loops=25,892)

162. 15.254 15.254 ↑ 1.0 1 15,254

Aggregate (cost=1.25..1.26 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=15,254)

163. 0.000 0.000 ↓ 0.0 0 15,254

Function Scan on json_each_text json_each_text_1 (cost=0.00..1.00 rows=100 width=32) (actual time=0.000..0.000 rows=0 loops=15,254)