explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wFY5 : Optimization for: plan #6BgJ

Settings

Optimization path:

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

Append (cost=362,227,156.78..362,232,142.37 rows=25,768 width=8) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

HashAggregate (cost=362,227,156.78..362,227,927.66 rows=25,696 width=8) (actual rows= loops=)

  • Output: jaids.employeeid
  • Group Key: jaids.employeeid
3. 0.000 0.000 ↓ 0.0

Append (cost=0.83..362,227,092.54 rows=25,696 width=8) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Unique (cost=0.83..361,824,192.61 rows=13,057 width=8) (actual rows= loops=)

  • Output: jaids.employeeid
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.83..361,824,159.97 rows=13,057 width=8) (actual rows= loops=)

  • Output: jaids.employeeid
6. 0.000 0.000 ↓ 0.0

Index Only Scan using xu2_wtkemployee on cardinalhealthinc_prd_01.wtkemployee (cost=0.41..1,921.08 rows=34,888 width=8) (actual rows= loops=)

  • Output: wtkemployee.employeeid, wtkemployee.payruleid
  • Index Cond: (wtkemployee.payruleid IS NOT NULL)
7. 0.000 0.000 ↓ 0.0

Index Scan using pk_jaids on cardinalhealthinc_prd_01.jaids (cost=0.41..10,370.94 rows=1 width=8) (actual rows= loops=)

  • Output: jaids.employeeid, jaids.personid, jaids.deletedsw, jaids.versioncnt, jaids.assignpersonotsw, jaids.ppstartdatedtm, jaids.ppenddatedtm, jaids.cpstartdatedtm, jaids.cpenddatedtm, jaids.npstartdatedtm, jaids.npenddatedtm, jaids.npstartdatetimedtm, jaids.usedmasw, jaids.gold_data_type_id
  • Index Cond: (jaids.employeeid = wtkemployee.employeeid)
  • Filter: ((jaids.personid > 0) AND (jaids.deletedsw = '0'::numeric) AND ((alternatives: SubPlan 1 or hashed SubPlan 2) OR (alternatives: SubPlan 3 or hashed SubPlan 4)) AND (SubPlan 6))
8.          

SubPlan (for Index Scan)

9. 0.000 0.000 ↓ 0.0

Index Only Scan using x4_personstatusmm on cardinalhealthinc_prd_01.personstatusmm (cost=0.42..1.98 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((personstatusmm.personid = jaids.personid) AND (personstatusmm.employmentstatid = 1) AND (personstatusmm.effectivedtm <= '2020-10-12 23:59:59'::timestamp without time zone) AND (personstatusmm.expirationdtm > '2020-10-12 00:00:00'::timestamp without time zone))
10. 0.000 0.000 ↓ 0.0

Index Only Scan using x4_personstatusmm on cardinalhealthinc_prd_01.personstatusmm personstatusmm_1 (cost=0.42..3,456.15 rows=19,369 width=8) (actual rows= loops=)

  • Output: personstatusmm_1.personid
  • Index Cond: ((personstatusmm_1.employmentstatid = 1) AND (personstatusmm_1.effectivedtm <= '2020-10-12 23:59:59'::timestamp without time zone) AND (personstatusmm_1.expirationdtm > '2020-10-12 00:00:00'::timestamp without time zone))
11. 0.000 0.000 ↓ 0.0

Index Scan using x4_personstatusmm on cardinalhealthinc_prd_01.personstatusmm personstatusmm_2 (cost=0.42..3.49 rows=1 width=0) (actual rows= loops=)

  • Index Cond: ((personstatusmm_2.personid = jaids.personid) AND (personstatusmm_2.effectivedtm <= '2020-10-12 23:59:59'::timestamp without time zone) AND (personstatusmm_2.expirationdtm > '2020-10-12 00:00:00'::timestamp without time zone))
  • Filter: (personstatusmm_2.useracctstatid = 1)
12. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on cardinalhealthinc_prd_01.personstatusmm personstatusmm_3 (cost=2,100.46..4,977.03 rows=9,201 width=8) (actual rows= loops=)

  • Output: personstatusmm_3.personid
  • Recheck Cond: ((personstatusmm_3.effectivedtm <= '2020-10-12 23:59:59'::timestamp without time zone) AND (personstatusmm_3.expirationdtm > '2020-10-12 00:00:00'::timestamp without time zone))
  • Filter: (personstatusmm_3.useracctstatid = 1)
13. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on x2_personstatusmm (cost=0.00..2,098.16 rows=34,682 width=0) (actual rows= loops=)

  • Index Cond: ((personstatusmm_3.effectivedtm <= '2020-10-12 23:59:59'::timestamp without time zone) AND (personstatusmm_3.expirationdtm > '2020-10-12 00:00:00'::timestamp without time zone))
14. 0.000 0.000 ↓ 0.0

Result (cost=3.75..20,666.15 rows=23,913 width=8) (actual rows= loops=)

  • Output: c.employeeid
  • One-Time Filter: $5
15.          

Initplan (for Result)

16. 0.000 0.000 ↓ 0.0

Index Scan using xu1_wtkemployee on cardinalhealthinc_prd_01.wtkemployee a (cost=0.29..3.33 rows=1 width=0) (actual rows= loops=)

  • Index Cond: (a.personid = jaids.personid)
  • Filter: (a.supervisorid = 60,930)
17. 0.000 0.000 ↓ 0.0

Index Scan using x4_combhomeacct on cardinalhealthinc_prd_01.combhomeacct c (cost=3.75..20,666.15 rows=23,913 width=8) (actual rows= loops=)

  • Output: c.combhomeacctid, c.employeeid, c.laboracctid, c.homeaccthistid, c.wfcjoborgid, c.effectivedtm, c.expirationdtm, c.updatedtm, c.orgnodeid, c.gold_data_type_id
  • Index Cond: (c.wfcjoborgid = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205}'::bigint[]))
  • Filter: ((c.effectivedtm <= '2020-09-14 23:59:59'::timestamp without time zone) AND (c.expirationdtm > '2020-08-31 00:00:00'::timestamp without time zone))
18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.56..402,129.05 rows=12,639 width=8) (actual rows= loops=)

  • Output: sa.employee_id
19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.15..395,983.15 rows=12,664 width=8) (actual rows= loops=)

  • Output: sa.employee_id
20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.71..299,060.42 rows=115,263 width=8) (actual rows= loops=)

  • Output: schedule_shift_segment.schedule_item_id
21. 0.000 0.000 ↓ 0.0

Index Scan using pk_wfcjoborg on cardinalhealthinc_prd_01.wfcjoborg w (cost=0.14..40.66 rows=130 width=24) (actual rows= loops=)

  • Output: w.wfcjoborgid, w.wfcjobid, w.orgidsid, w.laboracctid, w.orgjob_assignment_id, w.effectivedtm, w.expirationdtm, w.lastacctupdtm, w.updatedtm
  • Index Cond: (w.wfcjoborgid = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205}'::bigint[]))
22. 0.000 0.000 ↓ 0.0

Index Scan using schedule_shift_segment_ix_orgjob_id_start_end_date_time_deleted on cardinalhealthinc_prd_01.schedule_shift_segment (cost=0.56..2,252.72 rows=1,581 width=24) (actual rows= loops=)

  • Output: schedule_shift_segment.id, schedule_shift_segment.time_entity_segment_type_id, schedule_shift_segment.schedule_item_id, schedule_shift_segment.start_date_time, schedule_shift_segment.end_date_time, schedule_shift_segment.deleted, schedule_shift_segment.labor_account_id, schedule_shift_segment.user_entered_labor_account_id, schedule_shift_segment.user_entered_labor_account, schedule_shift_segment.transfer_labor_account, schedule_shift_segment.primary_labor_account_id, schedule_shift_segment.orgjob_id, schedule_shift_segment.user_entered_orgjob, schedule_shift_segment.transfer_orgjob, schedule_shift_segment.primary_orgjob_id, schedule_shift_segment.workrule_id, schedule_shift_segment.user_entered_workrule, schedule_shift_segment.transfer_workrule, schedule_shift_segment.primary_workrule_id, schedule_shift_segment.order_number, schedule_shift_segment.skill_available, schedule_shift_segment.shift_segment_detail_type_id
  • Index Cond: ((schedule_shift_segment.orgjob_id = w.orgidsid) AND (w.effectivedtm <= schedule_shift_segment.start_date_time) AND (w.expirationdtm > schedule_shift_segment.start_date_time) AND (schedule_shift_segment.start_date_time <= '2020-09-14 23:59:59'::timestamp without time zone) AND (schedule_shift_segment.start_date_time >= '2020-08-31 00:00:00'::timestamp without time zone))
23. 0.000 0.000 ↓ 0.0

Index Scan using schedule_item_employee_assignment_pkey on cardinalhealthinc_prd_01.schedule_item_employee_assignment sa (cost=0.44..0.81 rows=1 width=16) (actual rows= loops=)

  • Output: sa.schedule_item_id, sa.employee_id, sa.start_date_time, sa.end_date_time, sa.deleted, sa.time_entity_item_type_id, sa.version
  • Index Cond: (sa.schedule_item_id = schedule_shift_segment.schedule_item_id)
  • Filter: ((NOT sa.deleted) AND (sa.start_date_time <= '2020-09-14 23:59:59'::timestamp without time zone) AND (sa.end_date_time >= '2020-08-31 00:00:00'::timestamp without time zone))
24. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_jaids on cardinalhealthinc_prd_01.jaids jaids_1 (cost=0.41..0.46 rows=1 width=8) (actual rows= loops=)

  • Output: jaids_1.employeeid
  • Index Cond: ((jaids_1.employeeid = sa.employee_id) AND (jaids_1.employeeid > 0))
25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.42..4,212.55 rows=72 width=8) (actual rows= loops=)

  • Output: sa_1.employee_id
26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.00..4,177.61 rows=72 width=8) (actual rows= loops=)

  • Output: sa_1.employee_id
27. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..1,929.79 rows=656 width=8) (actual rows= loops=)

  • Output: schedule_paycode_edit_segment.schedule_item_id
28. 0.000 0.000 ↓ 0.0

Index Scan using pk_wfcjoborg on cardinalhealthinc_prd_01.wfcjoborg w_1 (cost=0.14..40.66 rows=130 width=24) (actual rows= loops=)

  • Output: w_1.wfcjoborgid, w_1.wfcjobid, w_1.orgidsid, w_1.laboracctid, w_1.orgjob_assignment_id, w_1.effectivedtm, w_1.expirationdtm, w_1.lastacctupdtm, w_1.updatedtm
  • Index Cond: (w_1.wfcjoborgid = ANY ('{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60,61,62,63,64,65,66,67,68,69,70,71,72,73,74,75,76,77,78,79,80,81,82,83,84,85,86,87,88,89,90,91,92,93,94,95,96,97,98,99,100,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119,120,121,122,123,124,125,126,127,128,129,130,131,132,133,134,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157,158,159,160,161,162,163,164,165,166,167,168,169,170,171,172,173,174,175,176,177,178,179,180,181,182,183,184,185,186,187,188,189,190,191,192,193,194,195,196,197,198,199,200,201,202,203,204,205}'::bigint[]))
29. 0.000 0.000 ↓ 0.0

Index Scan using schedule_paycode_edit_segment_ix_orgjob_id_start_date_time on cardinalhealthinc_prd_01.schedule_paycode_edit_segment (cost=0.42..14.26 rows=9 width=24) (actual rows= loops=)

  • Output: schedule_paycode_edit_segment.id, schedule_paycode_edit_segment.schedule_item_id, schedule_paycode_edit_segment.time_entity_segment_type_id, schedule_paycode_edit_segment.start_date_time, schedule_paycode_edit_segment.end_date_time, schedule_paycode_edit_segment.deleted, schedule_paycode_edit_segment.labor_account_id, schedule_paycode_edit_segment.user_entered_labor_account_id, schedule_paycode_edit_segment.user_entered_labor_account, schedule_paycode_edit_segment.transfer_labor_account, schedule_paycode_edit_segment.primary_labor_account_id, schedule_paycode_edit_segment.orgjob_id, schedule_paycode_edit_segment.user_entered_orgjob, schedule_paycode_edit_segment.transfer_orgjob, schedule_paycode_edit_segment.primary_orgjob_id, schedule_paycode_edit_segment.workrule_id, schedule_paycode_edit_segment.user_entered_workrule, schedule_paycode_edit_segment.transfer_workrule, schedule_paycode_edit_segment.primary_workrule_id, schedule_paycode_edit_segment.paycode_id, schedule_paycode_edit_segment.money_amount, schedule_paycode_edit_segment.duration_in_seconds, schedule_paycode_edit_segment.duration_in_days, schedule_paycode_edit_segment.schedule_amount_type_id, schedule_paycode_edit_segment.override_accrual_amount
  • Index Cond: ((schedule_paycode_edit_segment.orgjob_id = w_1.orgidsid) AND (w_1.effectivedtm <= schedule_paycode_edit_segment.start_date_time) AND (w_1.expirationdtm > schedule_paycode_edit_segment.start_date_time) AND (schedule_paycode_edit_segment.start_date_time <= '2020-09-14 23:59:59'::timestamp without time zone) AND (schedule_paycode_edit_segment.start_date_time >= '2020-08-31 00:00:00'::timestamp without time zone))
30. 0.000 0.000 ↓ 0.0

Index Scan using schedule_item_employee_assignment_pkey on cardinalhealthinc_prd_01.schedule_item_employee_assignment sa_1 (cost=0.44..3.40 rows=1 width=16) (actual rows= loops=)

  • Output: sa_1.schedule_item_id, sa_1.employee_id, sa_1.start_date_time, sa_1.end_date_time, sa_1.deleted, sa_1.time_entity_item_type_id, sa_1.version
  • Index Cond: (sa_1.schedule_item_id = schedule_paycode_edit_segment.schedule_item_id)
  • Filter: ((NOT sa_1.deleted) AND (sa_1.start_date_time <= '2020-09-14 23:59:59'::timestamp without time zone) AND (sa_1.end_date_time >= '2020-08-31 00:00:00'::timestamp without time zone))
31. 0.000 0.000 ↓ 0.0

Index Only Scan using pk_jaids on cardinalhealthinc_prd_01.jaids jaids_2 (cost=0.41..0.46 rows=1 width=8) (actual rows= loops=)

  • Output: jaids_2.employeeid
  • Index Cond: ((jaids_2.employeeid = sa_1.employee_id) AND (jaids_2.employeeid > 0))