explain.depesz.com

PostgreSQL's explain analyze made readable

Result: WZlx : team booking summarys3 (ts approval history not cte)

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 28,774.272 ↑ 1.0 1 1

Limit (cost=18,044,024.74..18,044,024.79 rows=1 width=40) (actual time=28,774.268..28,774.272 rows=1 loops=1)

  • Output: ($37), ($43), ($44), ($51), ($57)
  • Buffers: shared hit=13378560 read=14283
2.          

CTE timesheet0cte

3. 0.005 0.260 ↓ 4.0 4 1

Nested Loop (cost=1.53..48.02 rows=1 width=76) (actual time=0.091..0.260 rows=4 loops=1)

  • Output: timesheet0.userid, timesheet0.startdate, timesheet0.enddate, tsapprovalrequestkeyvalue1.key, tsapprovalrequestkeyvalue1.uri
  • Inner Unique: true
  • Buffers: shared hit=58
4. 0.002 0.235 ↓ 4.0 4 1

Nested Loop (cost=1.25..45.84 rows=1 width=176) (actual time=0.078..0.235 rows=4 loops=1)

  • Output: timesheet0.userid, timesheet0.startdate, timesheet0.enddate, tsapprovalrequestkeyvalue1.key, tsapprovalrequestkeyvalue1.uri, tsapprovalrequestkeyvalue1.parentid
  • Inner Unique: true
  • Buffers: shared hit=46
5. 0.002 0.201 ↓ 4.0 4 1

Nested Loop (cost=0.83..44.07 rows=1 width=180) (actual time=0.059..0.201 rows=4 loops=1)

  • Output: timesheetapprovalnodes2.timesheetid, tsapprovalrequestkeyvalue1.key, tsapprovalrequestkeyvalue1.uri, tsapprovalrequestkeyvalue1.parentid
  • Inner Unique: true
  • Buffers: shared hit=30
6. 0.004 0.175 ↓ 4.0 4 1

Nested Loop (cost=0.55..41.77 rows=1 width=180) (actual time=0.045..0.175 rows=4 loops=1)

  • Output: tsapprovalrequest3.nodeid, tsapprovalrequestkeyvalue1.key, tsapprovalrequestkeyvalue1.uri, tsapprovalrequestkeyvalue1.parentid
  • Buffers: shared hit=18
7. 0.019 0.019 ↑ 1.0 4 1

Index Scan using uix2tsar_nodeid_userid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.tsapprovalrequest tsapprovalrequest3 (cost=0.27..5.04 rows=4 width=20) (actual time=0.014..0.019 rows=4 loops=1)

  • Output: tsapprovalrequest3.id, tsapprovalrequest3.effectivedateutc, tsapprovalrequest3.userid, tsapprovalrequest3.nodeid, tsapprovalrequest3.remindersentutc, tsapprovalrequest3.overduesentutc, tsapprovalrequest3.waitingbatchsentutc, tsapprovalrequest3.approvalagenttype, tsapprovalrequest3.systemprocessidentifier
  • Index Cond: (tsapprovalrequest3.userid = 2892)
  • Buffers: shared hit=6
8. 0.152 0.152 ↑ 1.0 1 4

Index Scan using ixtsarkvtsapprovalrequestid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue1 (cost=0.28..9.17 rows=1 width=168) (actual time=0.020..0.038 rows=1 loops=4)

  • Output: tsapprovalrequestkeyvalue1.id, tsapprovalrequestkeyvalue1.tsapprovalrequestid, tsapprovalrequestkeyvalue1.parentid, tsapprovalrequestkeyvalue1.index, tsapprovalrequestkeyvalue1.key, tsapprovalrequestkeyvalue1.uri, tsapprovalrequestkeyvalue1.slug, tsapprovalrequestkeyvalue1."boolean", tsapprovalrequestkeyvalue1.date, tsapprovalrequestkeyvalue1.number, tsapprovalrequestkeyvalue1.text, tsapprovalrequestkeyvalue1."time", tsapprovalrequestkeyvalue1.timespan, tsapprovalrequestkeyvalue1.daterange_startdate, tsapprovalrequestkeyvalue1.daterange_enddate, tsapprovalrequestkeyvalue1.daterange_relativedaterangeuri, tsapprovalrequestkeyvalue1.daterange_relativedaterangeasofdate, tsapprovalrequestkeyvalue1.workdayduration_decimalworkdays, tsapprovalrequestkeyvalue1.workdayduration_workdays, tsapprovalrequestkeyvalue1.workdayduration_hours, tsapprovalrequestkeyvalue1.workdayduration_minutes
  • Index Cond: (tsapprovalrequestkeyvalue1.tsapprovalrequestid = tsapprovalrequest3.id)
  • Filter: ((upper(tsapprovalrequestkeyvalue1.key) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text) OR ((upper(tsapprovalrequestkeyvalue1.key) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text) AND (upper(tsapprovalrequestkeyvalue1.uri) = 'URN:REPLICON:TIME-OFF-DATA-ACCESS-LEVEL:TIMESHEET-APPROVER'::text)))
  • Rows Removed by Filter: 10
  • Buffers: shared hit=12
9. 0.024 0.024 ↑ 1.0 1 4

Index Scan using timesheetapprovalnodes_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timesheetapprovalnodes timesheetapprovalnodes2 (cost=0.28..2.29 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=4)

  • Output: timesheetapprovalnodes2.id, timesheetapprovalnodes2.isapproved, timesheetapprovalnodes2.approverrole, timesheetapprovalnodes2.userid, timesheetapprovalnodes2.timesheetid, timesheetapprovalnodes2.systemprocessidentifier, timesheetapprovalnodes2.scriptid, timesheetapprovalnodes2.agentresolutionstate
  • Index Cond: (timesheetapprovalnodes2.id = tsapprovalrequest3.nodeid)
  • Buffers: shared hit=12
10. 0.032 0.032 ↑ 1.0 1 4

Index Scan using timesheet_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timesheet timesheet0 (cost=0.42..1.77 rows=1 width=28) (actual time=0.008..0.008 rows=1 loops=4)

  • Output: timesheet0.id, timesheet0.userid, timesheet0.startdate, timesheet0.enddate, timesheet0.approvalstatus, timesheet0.duedate, timesheet0.autosubmitdatetimeutc, timesheet0.lastautosubmitattemptdatetimeutc, timesheet0.createdonutc
  • Index Cond: (timesheet0.id = timesheetapprovalnodes2.timesheetid)
  • Buffers: shared hit=16
11. 0.020 0.020 ↑ 1.0 1 4

Index Scan using tsapprovalrequestkeyvalue_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.tsapprovalrequestkeyvalue tsapprovalrequestkeyvalue4 (cost=0.28..2.16 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=4)

  • Output: tsapprovalrequestkeyvalue4.id, tsapprovalrequestkeyvalue4.tsapprovalrequestid, tsapprovalrequestkeyvalue4.parentid, tsapprovalrequestkeyvalue4.index, tsapprovalrequestkeyvalue4.key, tsapprovalrequestkeyvalue4.uri, tsapprovalrequestkeyvalue4.slug, tsapprovalrequestkeyvalue4."boolean", tsapprovalrequestkeyvalue4.date, tsapprovalrequestkeyvalue4.number, tsapprovalrequestkeyvalue4.text, tsapprovalrequestkeyvalue4."time", tsapprovalrequestkeyvalue4.timespan, tsapprovalrequestkeyvalue4.daterange_startdate, tsapprovalrequestkeyvalue4.daterange_enddate, tsapprovalrequestkeyvalue4.daterange_relativedaterangeuri, tsapprovalrequestkeyvalue4.daterange_relativedaterangeasofdate, tsapprovalrequestkeyvalue4.workdayduration_decimalworkdays, tsapprovalrequestkeyvalue4.workdayduration_workdays, tsapprovalrequestkeyvalue4.workdayduration_hours, tsapprovalrequestkeyvalue4.workdayduration_minutes
  • Index Cond: (tsapprovalrequestkeyvalue4.id = tsapprovalrequestkeyvalue1.parentid)
  • Filter: (tsapprovalrequestkeyvalue4.parentid IS NULL)
  • Buffers: shared hit=12
12.          

CTE eofflist_rtcombined_facts14cte

13. 0.000 28,388.595 ↑ 25.4 4,512 1

Nested Loop Semi Join (cost=12.77..18,005,380.88 rows=114,538 width=1,538) (actual time=0.991..28,388.595 rows=4,512 loops=1)

  • Output: dm_timeofflist_facts_1.timeoffid, dm_timeofflist_facts_1.timeoffslug, dm_timeofflist_facts_1.timeoffowneruserid, dm_timeofflist_facts_1.timeoffowneruserdisplayname, dm_timeofflist_facts_1.timeoffowneruserslug, dm_timeofflist_facts_1.timeoffowneruserenabled, dm_timeofflist_facts_1.timeoffownerdepartmentid, dm_timeofflist_facts_1.timeoffownerdepartmentdisplayname, dm_timeofflist_facts_1.timeoffownerdepartmentslug, dm_timeofflist_facts_1.timeoffownersupervisoruserid, dm_timeofflist_facts_1.timeoffownersupervisoruserdisplayname, dm_timeofflist_facts_1.timeoffownersupervisoruserslug, dm_timeofflist_facts_1.timeoffcodeid, dm_timeofflist_facts_1.timeoffcodedisplayname, dm_timeofflist_facts_1.timeoffcodeslug, dm_timeofflist_facts_1.approvalstatus, dm_timeofflist_facts_1.timeoffstatus, dm_timeofflist_facts_1.startdate, dm_timeofflist_facts_1.enddate, dm_timeofflist_facts_1.startdaydurationtype, dm_timeofflist_facts_1.enddaydurationtype, dm_timeofflist_facts_1.startdayduration, dm_timeofflist_facts_1.enddayduration, dm_timeofflist_facts_1.startdaystarttime, dm_timeofflist_facts_1.enddayendtime, dm_timeofflist_facts_1.totalduration, dm_timeofflist_facts_1.totalworkdayduration, dm_timeofflist_facts_1.effectivehoursperday, dm_timeofflist_facts_1.lastactiondatetimeutc, dm_timeofflist_facts_1.waitingonapproveruserid, dm_timeofflist_facts_1.waitingonapproveruserslug, dm_timeofflist_facts_1.waitingonapproveruserdisplayname, dm_timeofflist_facts_1.generatedonutc, dm_timeofflist_facts_1.timeoffowneruseremployeeid, dm_timeofflist_facts_1.timeoffowneruserloginname, dm_timeofflist_facts_1.displayformat
  • Buffers: shared hit=13317771 read=14283
14.          

Initplan (for Nested Loop Semi Join)

15. 0.002 0.065 ↓ 0.0 0 1

Nested Loop (cost=0.43..11.37 rows=1 width=0) (actual time=0.065..0.065 rows=0 loops=1)

  • Buffers: shared hit=9
16. 0.015 0.015 ↑ 1.0 2 1

Index Only Scan using userpolicyset_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.userpolicyset userpolicyset20 (cost=0.29..3.23 rows=2 width=16) (actual time=0.015..0.015 rows=2 loops=1)

  • Output: userpolicyset20.userid, userpolicyset20.policysetid
  • Index Cond: (userpolicyset20.userid = 2892)
  • Heap Fetches: 2
  • Buffers: shared hit=4
17. 0.048 0.048 ↓ 0.0 0 2

Index Scan using ixpskvpolicysetid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.policysetkeyvalue policysetkeyvalue21 (cost=0.14..4.06 rows=1 width=16) (actual time=0.024..0.024 rows=0 loops=2)

  • Output: policysetkeyvalue21.id, policysetkeyvalue21.policysetid, policysetkeyvalue21.parentid, policysetkeyvalue21.index, policysetkeyvalue21.key, policysetkeyvalue21.uri, policysetkeyvalue21.slug, policysetkeyvalue21."boolean", policysetkeyvalue21.date, policysetkeyvalue21.number, policysetkeyvalue21.text, policysetkeyvalue21."time", policysetkeyvalue21.timespan, policysetkeyvalue21.daterange_startdate, policysetkeyvalue21.daterange_enddate, policysetkeyvalue21.daterange_relativedaterangeuri, policysetkeyvalue21.daterange_relativedaterangeasofdate, policysetkeyvalue21.workdayduration_decimalworkdays, policysetkeyvalue21.workdayduration_workdays, policysetkeyvalue21.workdayduration_hours, policysetkeyvalue21.workdayduration_minutes
  • Index Cond: (policysetkeyvalue21.policysetid = userpolicyset20.policysetid)
  • Filter: ((policysetkeyvalue21.parentid IS NULL) AND (upper(policysetkeyvalue21.key) = 'URN:REPLICON:POLICY:TIME-OFF:CAN-USER-VIEW-ALL-TIME-OFF'::text) AND (upper(policysetkeyvalue21.uri) = 'URN:REPLICON:POLICY:TIME-OFF:CAN-USER-VIEW-ALL-TIME-OFF:ALLOWED'::text))
  • Rows Removed by Filter: 12
  • Buffers: shared hit=5
18. 21.452 409.794 ↓ 1.0 229,568 1

Append (cost=0.70..21,525.36 rows=229,075 width=327) (actual time=0.035..409.794 rows=229,568 loops=1)

  • Buffers: shared hit=60502
19. 89.584 387.475 ↓ 1.0 228,599 1

Merge Anti Join (cost=0.70..19,092.19 rows=228,106 width=327) (actual time=0.035..387.475 rows=228,599 loops=1)

  • Output: dm_timeofflist_facts_1.timeoffid, dm_timeofflist_facts_1.timeoffslug, dm_timeofflist_facts_1.timeoffowneruserid, dm_timeofflist_facts_1.timeoffowneruserdisplayname, dm_timeofflist_facts_1.timeoffowneruserslug, dm_timeofflist_facts_1.timeoffowneruserenabled, dm_timeofflist_facts_1.timeoffownerdepartmentid, dm_timeofflist_facts_1.timeoffownerdepartmentdisplayname, dm_timeofflist_facts_1.timeoffownerdepartmentslug, dm_timeofflist_facts_1.timeoffownersupervisoruserid, dm_timeofflist_facts_1.timeoffownersupervisoruserdisplayname, dm_timeofflist_facts_1.timeoffownersupervisoruserslug, dm_timeofflist_facts_1.timeoffcodeid, dm_timeofflist_facts_1.timeoffcodedisplayname, dm_timeofflist_facts_1.timeoffcodeslug, dm_timeofflist_facts_1.approvalstatus, dm_timeofflist_facts_1.timeoffstatus, dm_timeofflist_facts_1.startdate, dm_timeofflist_facts_1.enddate, dm_timeofflist_facts_1.startdaydurationtype, dm_timeofflist_facts_1.enddaydurationtype, dm_timeofflist_facts_1.startdayduration, dm_timeofflist_facts_1.enddayduration, dm_timeofflist_facts_1.startdaystarttime, dm_timeofflist_facts_1.enddayendtime, dm_timeofflist_facts_1.totalduration, dm_timeofflist_facts_1.totalworkdayduration, dm_timeofflist_facts_1.effectivehoursperday, dm_timeofflist_facts_1.lastactiondatetimeutc, dm_timeofflist_facts_1.waitingonapproveruserid, dm_timeofflist_facts_1.waitingonapproveruserslug, dm_timeofflist_facts_1.waitingonapproveruserdisplayname, dm_timeofflist_facts_1.generatedonutc, dm_timeofflist_facts_1.timeoffowneruseremployeeid, dm_timeofflist_facts_1.timeoffowneruserloginname, dm_timeofflist_facts_1.displayformat
  • Merge Cond: (dm_timeofflist_facts_1.timeoffid = dm_timeofflist_realtime_facts_2.timeoffid)
  • Buffers: shared hit=60377
20. 296.961 296.961 ↓ 1.0 229,568 1

Index Scan using dm_timeofflist_facts_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_facts dm_timeofflist_facts_1 (cost=0.42..18,447.82 rows=229,500 width=327) (actual time=0.019..296.961 rows=229,568 loops=1)

  • Output: dm_timeofflist_facts_1.timeoffid, dm_timeofflist_facts_1.timeoffslug, dm_timeofflist_facts_1.timeoffowneruserid, dm_timeofflist_facts_1.timeoffowneruserdisplayname, dm_timeofflist_facts_1.timeoffowneruserslug, dm_timeofflist_facts_1.timeoffowneruserenabled, dm_timeofflist_facts_1.timeoffownerdepartmentid, dm_timeofflist_facts_1.timeoffownerdepartmentdisplayname, dm_timeofflist_facts_1.timeoffownerdepartmentslug, dm_timeofflist_facts_1.timeoffownersupervisoruserid, dm_timeofflist_facts_1.timeoffownersupervisoruserdisplayname, dm_timeofflist_facts_1.timeoffownersupervisoruserslug, dm_timeofflist_facts_1.timeoffcodeid, dm_timeofflist_facts_1.timeoffcodedisplayname, dm_timeofflist_facts_1.timeoffcodeslug, dm_timeofflist_facts_1.approvalstatus, dm_timeofflist_facts_1.timeoffstatus, dm_timeofflist_facts_1.startdate, dm_timeofflist_facts_1.enddate, dm_timeofflist_facts_1.startdaydurationtype, dm_timeofflist_facts_1.enddaydurationtype, dm_timeofflist_facts_1.startdayduration, dm_timeofflist_facts_1.enddayduration, dm_timeofflist_facts_1.startdaystarttime, dm_timeofflist_facts_1.enddayendtime, dm_timeofflist_facts_1.totalduration, dm_timeofflist_facts_1.effectivehoursperday, dm_timeofflist_facts_1.lastactiondatetimeutc, dm_timeofflist_facts_1.waitingonapproveruserid, dm_timeofflist_facts_1.waitingonapproveruserslug, dm_timeofflist_facts_1.waitingonapproveruserdisplayname, dm_timeofflist_facts_1.generatedonutc, dm_timeofflist_facts_1.timeoffowneruseremployeeid, dm_timeofflist_facts_1.timeoffowneruserloginname, dm_timeofflist_facts_1.totalworkdayduration, dm_timeofflist_facts_1.displayformat
  • Filter: (dm_timeofflist_facts_1.timeoffcodeid <> 5)
  • Buffers: shared hit=60150
21. 0.930 0.930 ↑ 1.0 1,393 1

Index Only Scan using dm_timeofflist_realtime_facts_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_2 (cost=0.28..53.19 rows=1,394 width=4) (actual time=0.015..0.930 rows=1,393 loops=1)

  • Output: dm_timeofflist_realtime_facts_2.timeoffid
  • Heap Fetches: 410
  • Buffers: shared hit=227
22. 0.867 0.867 ↑ 1.0 969 1

Seq Scan on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_3 (cost=0.00..142.43 rows=969 width=332) (actual time=0.007..0.867 rows=969 loops=1)

  • Output: dm_timeofflist_realtime_facts_3.timeoffid, dm_timeofflist_realtime_facts_3.timeoffslug, dm_timeofflist_realtime_facts_3.timeoffowneruserid, dm_timeofflist_realtime_facts_3.timeoffowneruserdisplayname, dm_timeofflist_realtime_facts_3.timeoffowneruserslug, dm_timeofflist_realtime_facts_3.timeoffowneruserenabled, dm_timeofflist_realtime_facts_3.timeoffownerdepartmentid, dm_timeofflist_realtime_facts_3.timeoffownerdepartmentdisplayname, dm_timeofflist_realtime_facts_3.timeoffownerdepartmentslug, dm_timeofflist_realtime_facts_3.timeoffownersupervisoruserid, dm_timeofflist_realtime_facts_3.timeoffownersupervisoruserdisplayname, dm_timeofflist_realtime_facts_3.timeoffownersupervisoruserslug, dm_timeofflist_realtime_facts_3.timeoffcodeid, dm_timeofflist_realtime_facts_3.timeoffcodedisplayname, dm_timeofflist_realtime_facts_3.timeoffcodeslug, dm_timeofflist_realtime_facts_3.approvalstatus, dm_timeofflist_realtime_facts_3.timeoffstatus, dm_timeofflist_realtime_facts_3.startdate, dm_timeofflist_realtime_facts_3.enddate, dm_timeofflist_realtime_facts_3.startdaydurationtype, dm_timeofflist_realtime_facts_3.enddaydurationtype, dm_timeofflist_realtime_facts_3.startdayduration, dm_timeofflist_realtime_facts_3.enddayduration, dm_timeofflist_realtime_facts_3.startdaystarttime, dm_timeofflist_realtime_facts_3.enddayendtime, dm_timeofflist_realtime_facts_3.totalduration, dm_timeofflist_realtime_facts_3.totalworkdayduration, dm_timeofflist_realtime_facts_3.effectivehoursperday, dm_timeofflist_realtime_facts_3.lastactiondatetimeutc, dm_timeofflist_realtime_facts_3.waitingonapproveruserid, dm_timeofflist_realtime_facts_3.waitingonapproveruserslug, dm_timeofflist_realtime_facts_3.waitingonapproveruserdisplayname, dm_timeofflist_realtime_facts_3.generatedonutc, dm_timeofflist_realtime_facts_3.timeoffowneruseremployeeid, dm_timeofflist_realtime_facts_3.timeoffowneruserloginname, dm_timeofflist_realtime_facts_3.displayformat
  • Filter: ((NOT dm_timeofflist_realtime_facts_3.deleted) AND (dm_timeofflist_realtime_facts_3.timeoffcodeid <> 5))
  • Rows Removed by Filter: 424
  • Buffers: shared hit=125
23. 1,679.796 28,007.296 ↓ 0.0 0 229,568

Nested Loop Left Join (cost=0.71..119.42 rows=3 width=4) (actual time=0.122..0.122 rows=0 loops=229,568)

  • Output: timeoffs15.id
  • Filter: (((userhierarchy16.supervisorid = 2892) AND ((userhierarchy16.startdate IS NULL) OR (userhierarchy16.startdate <= '2020-02-09'::date)) AND ((userhierarchy16.enddate IS NULL) OR (userhierarchy16.enddate >= '2020-02-09'::date))) OR (timeoffs15.userid = 2892) OR (alternatives: SubPlan 2 or hashed SubPlan 3) OR (alternatives: SubPlan 4 or hashed SubPlan 5) OR $11 OR (SubPlan 7) OR (SubPlan 8))
  • Rows Removed by Filter: 3
  • Buffers: shared hit=13257269 read=14283
24. 918.272 918.272 ↑ 1.0 1 229,568

Index Scan using timeoffs_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffs timeoffs15 (cost=0.42..0.46 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=229,568)

  • Output: timeoffs15.id, timeoffs15.guidid, timeoffs15.userid, timeoffs15.createdbyuserid, timeoffs15.timeoffcodeid, timeoffs15.startdate, timeoffs15.enddate, timeoffs15.approvalstatus, timeoffs15.comments, timeoffs15.submittedonutc, timeoffs15.modifiedonutc, timeoffs15.info1, timeoffs15.info2, timeoffs15.info3, timeoffs15.info4, timeoffs15.info5, timeoffs15.startdurationtype, timeoffs15.enddurationtype, timeoffs15.startduration, timeoffs15.endduration, timeoffs15.startdatestarttime, timeoffs15.enddateendtime, timeoffs15.entryconfigurationmethod
  • Index Cond: (timeoffs15.id = dm_timeofflist_facts_1.timeoffid)
  • Buffers: shared hit=919299 read=363
25. 688.704 688.704 ↓ 1.5 3 229,568

Index Scan using ix3uh_usersuperstart on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.userhierarchy userhierarchy16 (cost=0.29..0.32 rows=2 width=24) (actual time=0.002..0.003 rows=3 loops=229,568)

  • Output: userhierarchy16.id, userhierarchy16.userid, userhierarchy16.supervisorid, userhierarchy16.startdate, userhierarchy16.enddate
  • Index Cond: (userhierarchy16.userid = timeoffs15.userid)
  • Buffers: shared hit=819223 read=3
26.          

SubPlan (for Nested Loop Left Join)

27. 0.000 728.014 ↓ 0.0 0 728,014

Nested Loop (cost=0.55..4.87 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=728,014)

  • Inner Unique: true
  • Buffers: shared hit=1517974
28. 728.014 728.014 ↓ 0.0 0 728,014

Index Scan using ixtoantimeoffid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalnodes timeoffapprovalnodes17 (cost=0.27..2.29 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=728,014)

  • Output: timeoffapprovalnodes17.id, timeoffapprovalnodes17.timeoffid, timeoffapprovalnodes17.isapproved, timeoffapprovalnodes17.approverrole, timeoffapprovalnodes17.userid, timeoffapprovalnodes17.agentresolutionstate, timeoffapprovalnodes17.scriptid
  • Index Cond: (timeoffapprovalnodes17.timeoffid = timeoffs15.id)
  • Buffers: shared hit=1516035
29. 0.963 0.963 ↓ 0.0 0 963

Index Only Scan using uix2toaraidnid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalrequest timeoffapprovalrequest18 (cost=0.27..2.29 rows=1 width=16) (actual time=0.001..0.001 rows=0 loops=963)

  • Output: timeoffapprovalrequest18.approverid, timeoffapprovalrequest18.nodeid
  • Index Cond: ((timeoffapprovalrequest18.approverid = 2892) AND (timeoffapprovalrequest18.nodeid = timeoffapprovalnodes17.id))
  • Heap Fetches: 12
  • Buffers: shared hit=1939
30. 0.000 0.000 ↓ 0.0 0

Hash Join (cost=7.35..16.07 rows=13 width=4) (never executed)

  • Output: timeoffapprovalnodes17_1.timeoffid
  • Inner Unique: true
  • Hash Cond: (timeoffapprovalnodes17_1.id = timeoffapprovalrequest18_1.nodeid)
31. 0.000 0.000 ↓ 0.0 0

Seq Scan on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalnodes timeoffapprovalnodes17_1 (cost=0.00..7.94 rows=294 width=20) (never executed)

  • Output: timeoffapprovalnodes17_1.id, timeoffapprovalnodes17_1.timeoffid, timeoffapprovalnodes17_1.isapproved, timeoffapprovalnodes17_1.approverrole, timeoffapprovalnodes17_1.userid, timeoffapprovalnodes17_1.agentresolutionstate, timeoffapprovalnodes17_1.scriptid
32. 0.000 0.000 ↓ 0.0 0

Hash (cost=7.19..7.19 rows=13 width=16) (never executed)

  • Output: timeoffapprovalrequest18_1.nodeid
33. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtoarapproverid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalrequest timeoffapprovalrequest18_1 (cost=0.27..7.19 rows=13 width=16) (never executed)

  • Output: timeoffapprovalrequest18_1.nodeid
  • Index Cond: (timeoffapprovalrequest18_1.approverid = 2892)
34. 3,640.010 3,640.010 ↓ 0.0 0 728,002

Index Scan using ixtoahtimeoffid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistory timeoffapprovalhistory19 (cost=0.42..3.47 rows=1 width=0) (actual time=0.005..0.005 rows=0 loops=728,002)

  • Index Cond: (timeoffapprovalhistory19.timeoffid = timeoffs15.id)
  • Filter: ((timeoffapprovalhistory19.userid = 2892) AND (timeoffapprovalhistory19.action = ANY ('{2,3,4}'::integer[])))
  • Rows Removed by Filter: 2
  • Buffers: shared hit=3442848 read=1745
35. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtoahuserid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistory timeoffapprovalhistory19_1 (cost=0.42..1,239.28 rows=657 width=4) (never executed)

  • Output: timeoffapprovalhistory19_1.timeoffid
  • Index Cond: (timeoffapprovalhistory19_1.userid = 2892)
  • Filter: (timeoffapprovalhistory19_1.action = ANY ('{2,3,4}'::integer[]))
36. 0.000 0.000 ↓ 0.0 0 726,876

CTE Scan on timesheet0cte timesheet22 (cost=0.00..0.06 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=726,876)

  • Filter: ((timeoffs15.startdate <= timesheet22.enddate2) AND (timeoffs15.enddate >= timesheet22.startdate1) AND (timesheet22.userid0 = timeoffs15.userid) AND ((('URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text = upper(timesheet22.key3)) AND ('URN:REPLICON:TIME-OFF-DATA-ACCESS-LEVEL:TIMESHEET-APPROVER'::text = upper(timesheet22.uri4))) OR (('URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text = upper(timesheet22.key3)) AND (upper(('urn:replicon-tenant:jsiprodg3trial:time-off:'::text || COALESCE((timeoffs15.id)::text, ''::text))) = upper(timesheet22.uri4)))))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=58
37. 0.000 20,352.500 ↓ 0.0 0 726,875

Nested Loop (cost=3.87..50.90 rows=1 width=0) (actual time=0.028..0.028 rows=0 loops=726,875)

  • Join Filter: (((upper(esheetapprovalhistorykeyvalue6.uri) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text) OR ((upper(esheetapprovalhistorykeyvalue6.uri) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text) AND (upper(esheetapprovalhistorykeyvalue7.uri) = 'URN:REPLICON:TIME-OFF-DATA-ACCESS-LEVEL:TIMESHEET-APPROVER'::text))) AND ((('URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text = upper((esheetapprovalhistorykeyvalue6.uri)::text)) AND ('URN:REPLICON:TIME-OFF-DATA-ACCESS-LEVEL:TIMESHEET-APPROVER'::text = upper((esheetapprovalhistorykeyvalue7.uri)::text))) OR (('URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text = upper((esheetapprovalhistorykeyvalue6.uri)::text)) AND (upper(('urn:replicon-tenant:jsiprodg3trial:time-off:'::text || COALESCE((timeoffs15.id)::text, ''::text))) = upper((esheetapprovalhistorykeyvalue7.uri)::text)))))
  • Buffers: shared hit=6557858 read=12172
38. 406.280 20,352.500 ↓ 0.0 0 726,875

Nested Loop (cost=3.44..50.27 rows=1 width=85) (actual time=0.028..0.028 rows=0 loops=726,875)

  • Output: esheetapprovalhistorykeyvalue6.uri, sheetapprovalhistorykeyvalue13.id
  • Buffers: shared hit=6550036 read=11848
39. 268.666 19,625.625 ↓ 0.0 0 726,875

Nested Loop (cost=3.01..45.82 rows=1 width=85) (actual time=0.027..0.027 rows=0 loops=726,875)

  • Output: esheetapprovalhistorykeyvalue6.id, esheetapprovalhistorykeyvalue6.uri
  • Buffers: shared hit=6542537 read=11556
40. 287.858 18,898.750 ↓ 0.0 0 726,875

Nested Loop (cost=2.57..45.23 rows=1 width=16) (actual time=0.026..0.026 rows=0 loops=726,875)

  • Output: sheetapprovalhistorykeyvalue12.id
  • Buffers: shared hit=6533736 read=11211
41. 194.297 18,171.875 ↓ 0.0 0 726,875

Nested Loop (cost=2.14..43.87 rows=1 width=16) (actual time=0.025..0.025 rows=0 loops=726,875)

  • Output: sheetapprovalhistorykeyvalue11.id
  • Buffers: shared hit=6524519 read=10869
42. 0.000 17,445.000 ↓ 0.0 0 726,875

Nested Loop (cost=1.71..41.76 rows=1 width=16) (actual time=0.024..0.024 rows=0 loops=726,875)

  • Output: sheetapprovalhistorykeyvalue10.parentid
  • Inner Unique: true
  • Buffers: shared hit=6514198 read=10532
43. 163.066 17,445.000 ↓ 0.0 0 726,875

Nested Loop (cost=1.27..39.38 rows=1 width=16) (actual time=0.024..0.024 rows=0 loops=726,875)

  • Output: esheetapprovalhistorykeyvalue9.parentid
  • Buffers: shared hit=6505039 read=10140
44. 1,172.300 15,264.375 ↓ 0.0 0 726,875

Nested Loop (cost=0.84..14.26 rows=1 width=16) (actual time=0.021..0.021 rows=0 loops=726,875)

  • Output: timesheetapprovalhistory8.id
  • Buffers: shared hit=6490404 read=8491
45. 1,453.750 1,453.750 ↑ 2.0 1 726,875

Index Scan using ixtsuseridstartdateenddate on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timesheet timesheet5 (cost=0.42..3.24 rows=2 width=16) (actual time=0.002..0.002 rows=1 loops=726,875)

  • Output: timesheet5.id, timesheet5.userid, timesheet5.startdate, timesheet5.enddate, timesheet5.approvalstatus, timesheet5.duedate, timesheet5.autosubmitdatetimeutc, timesheet5.lastautosubmitattemptdatetimeutc, timesheet5.createdonutc
  • Index Cond: ((timesheet5.userid = timeoffs15.userid) AND (timeoffs15.enddate >= timesheet5.startdate) AND (timeoffs15.startdate <= timesheet5.enddate))
  • Buffers: shared hit=2918096 read=8
46. 12,638.325 12,638.325 ↓ 0.0 0 665,175

Index Scan using ixtah2timesheetid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timesheetapprovalhistory timesheetapprovalhistory8 (cost=0.42..5.50 rows=1 width=32) (actual time=0.019..0.019 rows=0 loops=665,175)

  • Output: timesheetapprovalhistory8.id, timesheetapprovalhistory8.action, timesheetapprovalhistory8.approvalcomments, timesheetapprovalhistory8.timestamputc, timesheetapprovalhistory8.serialnumber, timesheetapprovalhistory8.timesheetid, timesheetapprovalhistory8.approvalagenttype, timesheetapprovalhistory8.userid, timesheetapprovalhistory8.systemprocessidentifier
  • Index Cond: (timesheetapprovalhistory8.timesheetid = timesheet5.id)
  • Filter: (timesheetapprovalhistory8.userid = 2892)
  • Rows Removed by Filter: 4
  • Buffers: shared hit=3572308 read=8483
47. 2,017.559 2,017.559 ↑ 1.0 1 2,399

Index Scan using ixtahkvtimesheetapprovalhistoryid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue9 (cost=0.43..25.11 rows=1 width=32) (actual time=0.836..0.841 rows=1 loops=2,399)

  • Output: esheetapprovalhistorykeyvalue9.id, esheetapprovalhistorykeyvalue9.timesheetapprovalhistoryid, esheetapprovalhistorykeyvalue9.parentid, esheetapprovalhistorykeyvalue9.index, esheetapprovalhistorykeyvalue9.key, esheetapprovalhistorykeyvalue9.uri, esheetapprovalhistorykeyvalue9.slug, esheetapprovalhistorykeyvalue9."boolean", esheetapprovalhistorykeyvalue9.date, esheetapprovalhistorykeyvalue9.number, esheetapprovalhistorykeyvalue9.text, esheetapprovalhistorykeyvalue9."time", esheetapprovalhistorykeyvalue9.timespan, esheetapprovalhistorykeyvalue9.daterange_startdate, esheetapprovalhistorykeyvalue9.daterange_enddate, esheetapprovalhistorykeyvalue9.daterange_relativedaterangeuri, esheetapprovalhistorykeyvalue9.daterange_relativedaterangeasofdate, esheetapprovalhistorykeyvalue9.workdayduration_decimalworkdays, esheetapprovalhistorykeyvalue9.workdayduration_workdays, esheetapprovalhistorykeyvalue9.workdayduration_hours, esheetapprovalhistorykeyvalue9.workdayduration_minutes
  • Index Cond: (esheetapprovalhistorykeyvalue9.timesheetapprovalhistoryid = timesheetapprovalhistory8.id)
  • Filter: ((upper(esheetapprovalhistorykeyvalue9.key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-NODES'::text) AND (upper(esheetapprovalhistorykeyvalue9.uri) = 'URN:REPLICON-TENANT:JSIPRODG3TRIAL:USER:2892'::text))
  • Rows Removed by Filter: 24
  • Buffers: shared hit=14635 read=1649
48. 448.613 448.613 ↑ 1.0 1 2,399

Index Scan using timesheetapprovalhistorykeyvalue_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue10 (cost=0.43..2.38 rows=1 width=32) (actual time=0.187..0.187 rows=1 loops=2,399)

  • Output: sheetapprovalhistorykeyvalue10.id, sheetapprovalhistorykeyvalue10.timesheetapprovalhistoryid, sheetapprovalhistorykeyvalue10.parentid, sheetapprovalhistorykeyvalue10.index, sheetapprovalhistorykeyvalue10.key, sheetapprovalhistorykeyvalue10.uri, sheetapprovalhistorykeyvalue10.slug, sheetapprovalhistorykeyvalue10."boolean", sheetapprovalhistorykeyvalue10.date, sheetapprovalhistorykeyvalue10.number, sheetapprovalhistorykeyvalue10.text, sheetapprovalhistorykeyvalue10."time", sheetapprovalhistorykeyvalue10.timespan, sheetapprovalhistorykeyvalue10.daterange_startdate, sheetapprovalhistorykeyvalue10.daterange_enddate, sheetapprovalhistorykeyvalue10.daterange_relativedaterangeuri, sheetapprovalhistorykeyvalue10.daterange_relativedaterangeasofdate, sheetapprovalhistorykeyvalue10.workdayduration_decimalworkdays, sheetapprovalhistorykeyvalue10.workdayduration_workdays, sheetapprovalhistorykeyvalue10.workdayduration_hours, sheetapprovalhistorykeyvalue10.workdayduration_minutes
  • Index Cond: (sheetapprovalhistorykeyvalue10.id = esheetapprovalhistorykeyvalue9.parentid)
  • Filter: (upper(sheetapprovalhistorykeyvalue10.uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-EXPECTED-APPROVAL-AGENT-USER'::text)
  • Buffers: shared hit=9159 read=392
49. 532.578 532.578 ↑ 1.0 1 2,399

Index Scan using ixtahkvparentid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue11 (cost=0.43..2.10 rows=1 width=32) (actual time=0.222..0.222 rows=1 loops=2,399)

  • Output: sheetapprovalhistorykeyvalue11.id, sheetapprovalhistorykeyvalue11.timesheetapprovalhistoryid, sheetapprovalhistorykeyvalue11.parentid, sheetapprovalhistorykeyvalue11.index, sheetapprovalhistorykeyvalue11.key, sheetapprovalhistorykeyvalue11.uri, sheetapprovalhistorykeyvalue11.slug, sheetapprovalhistorykeyvalue11."boolean", sheetapprovalhistorykeyvalue11.date, sheetapprovalhistorykeyvalue11.number, sheetapprovalhistorykeyvalue11.text, sheetapprovalhistorykeyvalue11."time", sheetapprovalhistorykeyvalue11.timespan, sheetapprovalhistorykeyvalue11.daterange_startdate, sheetapprovalhistorykeyvalue11.daterange_enddate, sheetapprovalhistorykeyvalue11.daterange_relativedaterangeuri, sheetapprovalhistorykeyvalue11.daterange_relativedaterangeasofdate, sheetapprovalhistorykeyvalue11.workdayduration_decimalworkdays, sheetapprovalhistorykeyvalue11.workdayduration_workdays, sheetapprovalhistorykeyvalue11.workdayduration_hours, sheetapprovalhistorykeyvalue11.workdayduration_minutes
  • Index Cond: (sheetapprovalhistorykeyvalue11.parentid = sheetapprovalhistorykeyvalue10.parentid)
  • Filter: (upper(sheetapprovalhistorykeyvalue11.uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-METADATA'::text)
  • Rows Removed by Filter: 2
  • Buffers: shared hit=10321 read=337
50. 439.017 439.017 ↑ 3.0 1 2,399

Index Scan using ixtahkvparentid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue12 (cost=0.43..1.33 rows=3 width=32) (actual time=0.183..0.183 rows=1 loops=2,399)

  • Output: sheetapprovalhistorykeyvalue12.id, sheetapprovalhistorykeyvalue12.timesheetapprovalhistoryid, sheetapprovalhistorykeyvalue12.parentid, sheetapprovalhistorykeyvalue12.index, sheetapprovalhistorykeyvalue12.key, sheetapprovalhistorykeyvalue12.uri, sheetapprovalhistorykeyvalue12.slug, sheetapprovalhistorykeyvalue12."boolean", sheetapprovalhistorykeyvalue12.date, sheetapprovalhistorykeyvalue12.number, sheetapprovalhistorykeyvalue12.text, sheetapprovalhistorykeyvalue12."time", sheetapprovalhistorykeyvalue12.timespan, sheetapprovalhistorykeyvalue12.daterange_startdate, sheetapprovalhistorykeyvalue12.daterange_enddate, sheetapprovalhistorykeyvalue12.daterange_relativedaterangeuri, sheetapprovalhistorykeyvalue12.daterange_relativedaterangeasofdate, sheetapprovalhistorykeyvalue12.workdayduration_decimalworkdays, sheetapprovalhistorykeyvalue12.workdayduration_workdays, sheetapprovalhistorykeyvalue12.workdayduration_hours, sheetapprovalhistorykeyvalue12.workdayduration_minutes
  • Index Cond: (sheetapprovalhistorykeyvalue12.parentid = sheetapprovalhistorykeyvalue11.id)
  • Buffers: shared hit=9217 read=342
51. 458.209 458.209 ↑ 1.0 1 2,399

Index Scan using ixtahkvparentid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue6 (cost=0.43..0.58 rows=1 width=101) (actual time=0.191..0.191 rows=1 loops=2,399)

  • Output: esheetapprovalhistorykeyvalue6.id, esheetapprovalhistorykeyvalue6.timesheetapprovalhistoryid, esheetapprovalhistorykeyvalue6.parentid, esheetapprovalhistorykeyvalue6.index, esheetapprovalhistorykeyvalue6.key, esheetapprovalhistorykeyvalue6.uri, esheetapprovalhistorykeyvalue6.slug, esheetapprovalhistorykeyvalue6."boolean", esheetapprovalhistorykeyvalue6.date, esheetapprovalhistorykeyvalue6.number, esheetapprovalhistorykeyvalue6.text, esheetapprovalhistorykeyvalue6."time", esheetapprovalhistorykeyvalue6.timespan, esheetapprovalhistorykeyvalue6.daterange_startdate, esheetapprovalhistorykeyvalue6.daterange_enddate, esheetapprovalhistorykeyvalue6.daterange_relativedaterangeuri, esheetapprovalhistorykeyvalue6.daterange_relativedaterangeasofdate, esheetapprovalhistorykeyvalue6.workdayduration_decimalworkdays, esheetapprovalhistorykeyvalue6.workdayduration_workdays, esheetapprovalhistorykeyvalue6.workdayduration_hours, esheetapprovalhistorykeyvalue6.workdayduration_minutes
  • Index Cond: (esheetapprovalhistorykeyvalue6.parentid = sheetapprovalhistorykeyvalue12.id)
  • Filter: (((upper(esheetapprovalhistorykeyvalue6.uri) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text) OR (upper(esheetapprovalhistorykeyvalue6.uri) = 'URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text)) AND (('URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-DATA-ACCESS-LEVELS'::text = upper((esheetapprovalhistorykeyvalue6.uri)::text)) OR ('URN:REPLICON:APPROVAL-REQUEST-METADATA-KEY:ADDITIONAL-APPROVER-ACCESSIBLE-OBJECTS'::text = upper((esheetapprovalhistorykeyvalue6.uri)::text))))
  • Buffers: shared hit=8801 read=345
52. 320.595 320.595 ↑ 3.0 1 1,943

Index Scan using ixtahkvparentid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue13 (cost=0.43..4.42 rows=3 width=32) (actual time=0.165..0.165 rows=1 loops=1,943)

  • Output: sheetapprovalhistorykeyvalue13.id, sheetapprovalhistorykeyvalue13.timesheetapprovalhistoryid, sheetapprovalhistorykeyvalue13.parentid, sheetapprovalhistorykeyvalue13.index, sheetapprovalhistorykeyvalue13.key, sheetapprovalhistorykeyvalue13.uri, sheetapprovalhistorykeyvalue13.slug, sheetapprovalhistorykeyvalue13."boolean", sheetapprovalhistorykeyvalue13.date, sheetapprovalhistorykeyvalue13.number, sheetapprovalhistorykeyvalue13.text, sheetapprovalhistorykeyvalue13."time", sheetapprovalhistorykeyvalue13.timespan, sheetapprovalhistorykeyvalue13.daterange_startdate, sheetapprovalhistorykeyvalue13.daterange_enddate, sheetapprovalhistorykeyvalue13.daterange_relativedaterangeuri, sheetapprovalhistorykeyvalue13.daterange_relativedaterangeasofdate, sheetapprovalhistorykeyvalue13.workdayduration_decimalworkdays, sheetapprovalhistorykeyvalue13.workdayduration_workdays, sheetapprovalhistorykeyvalue13.workdayduration_hours, sheetapprovalhistorykeyvalue13.workdayduration_minutes
  • Index Cond: (sheetapprovalhistorykeyvalue13.parentid = esheetapprovalhistorykeyvalue6.id)
  • Buffers: shared hit=7499 read=292
53. 382.771 382.771 ↑ 1.0 1 1,943

Index Scan using ixtahkvparentid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue7 (cost=0.43..0.58 rows=1 width=85) (actual time=0.197..0.197 rows=1 loops=1,943)

  • Output: esheetapprovalhistorykeyvalue7.id, esheetapprovalhistorykeyvalue7.timesheetapprovalhistoryid, esheetapprovalhistorykeyvalue7.parentid, esheetapprovalhistorykeyvalue7.index, esheetapprovalhistorykeyvalue7.key, esheetapprovalhistorykeyvalue7.uri, esheetapprovalhistorykeyvalue7.slug, esheetapprovalhistorykeyvalue7."boolean", esheetapprovalhistorykeyvalue7.date, esheetapprovalhistorykeyvalue7.number, esheetapprovalhistorykeyvalue7.text, esheetapprovalhistorykeyvalue7."time", esheetapprovalhistorykeyvalue7.timespan, esheetapprovalhistorykeyvalue7.daterange_startdate, esheetapprovalhistorykeyvalue7.daterange_enddate, esheetapprovalhistorykeyvalue7.daterange_relativedaterangeuri, esheetapprovalhistorykeyvalue7.daterange_relativedaterangeasofdate, esheetapprovalhistorykeyvalue7.workdayduration_decimalworkdays, esheetapprovalhistorykeyvalue7.workdayduration_workdays, esheetapprovalhistorykeyvalue7.workdayduration_hours, esheetapprovalhistorykeyvalue7.workdayduration_minutes
  • Index Cond: (esheetapprovalhistorykeyvalue7.parentid = sheetapprovalhistorykeyvalue13.id)
  • Filter: (('URN:REPLICON:TIME-OFF-DATA-ACCESS-LEVEL:TIMESHEET-APPROVER'::text = upper((esheetapprovalhistorykeyvalue7.uri)::text)) OR (upper(('urn:replicon-tenant:jsiprodg3trial:time-off:'::text || COALESCE((timeoffs15.id)::text, ''::text))) = upper((esheetapprovalhistorykeyvalue7.uri)::text)))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=7822 read=324
54.          

Initplan (for Limit)

55. 1.300 28,405.034 ↑ 1.0 1 1

Aggregate (cost=3,998.32..3,998.33 rows=1 width=8) (actual time=28,405.033..28,405.034 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=13317846 read=14283
56. 28,403.378 28,403.734 ↑ 29.3 3,661 1

CTE Scan on eofflist_rtcombined_facts14cte _timeofflist_rtcombined_facts0 (cost=293.73..3,729.87 rows=107,379 width=0) (actual time=1.259..28,403.734 rows=3,661 loops=1)

  • Output: _timeofflist_rtcombined_facts0.timeoffid10, _timeofflist_rtcombined_facts0.timeoffslug11, _timeofflist_rtcombined_facts0.timeoffowneruserid12, _timeofflist_rtcombined_facts0.timeoffowneruserdisplayname13, _timeofflist_rtcombined_facts0.timeoffowneruserslug14, _timeofflist_rtcombined_facts0.timeoffowneruserenabled15, _timeofflist_rtcombined_facts0.timeoffownerdepartmentid16, _timeofflist_rtcombined_facts0.timeoffownerdepartmentdisplayname17, _timeofflist_rtcombined_facts0.timeoffownerdepartmentslug18, _timeofflist_rtcombined_facts0.timeoffownersupervisoruserid19, _timeofflist_rtcombined_facts0.timeoffownersupervisoruserdisplayname20, _timeofflist_rtcombined_facts0.timeoffownersupervisoruserslug21, _timeofflist_rtcombined_facts0.timeoffcodeid22, _timeofflist_rtcombined_facts0.timeoffcodedisplayname23, _timeofflist_rtcombined_facts0.timeoffcodeslug24, _timeofflist_rtcombined_facts0.approvalstatus25, _timeofflist_rtcombined_facts0.timeoffstatus26, _timeofflist_rtcombined_facts0.startdate27, _timeofflist_rtcombined_facts0.enddate28, _timeofflist_rtcombined_facts0.startdaydurationtype29, _timeofflist_rtcombined_facts0.enddaydurationtype30, _timeofflist_rtcombined_facts0.startdayduration31, _timeofflist_rtcombined_facts0.enddayduration32, _timeofflist_rtcombined_facts0.startdaystarttime33, _timeofflist_rtcombined_facts0.enddayendtime34, _timeofflist_rtcombined_facts0.totalduration35, _timeofflist_rtcombined_facts0.totalworkdayduration36, _timeofflist_rtcombined_facts0.effectivehoursperday37, _timeofflist_rtcombined_facts0.lastactiondatetimeutc38, _timeofflist_rtcombined_facts0.waitingonapproveruserid39, _timeofflist_rtcombined_facts0.waitingonapproveruserslug40, _timeofflist_rtcombined_facts0.waitingonapproveruserdisplayname41, _timeofflist_rtcombined_facts0.generatedonutc42, _timeofflist_rtcombined_facts0.timeoffowneruseremployeeid43, _timeofflist_rtcombined_facts0.timeoffowneruserloginname44, _timeofflist_rtcombined_facts0.displayformat45
  • Filter: ((hashed SubPlan 10) OR (hashed SubPlan 11) OR (hashed SubPlan 12) OR (hashed SubPlan 13))
  • Rows Removed by Filter: 851
  • Buffers: shared hit=13317846 read=14283
57.          

SubPlan (for CTE Scan)

58. 0.046 0.046 ↑ 1.0 14 1

Seq Scan on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_remainingapprovers_facts list_remainingapprovers_facts1 (cost=0.00..12.43 rows=14 width=4) (actual time=0.026..0.046 rows=14 loops=1)

  • Output: list_remainingapprovers_facts1.timeoffid
  • Filter: (list_remainingapprovers_facts1.approveruserid = 2892)
  • Rows Removed by Filter: 500
  • Buffers: shared hit=6
59. 0.001 0.031 ↓ 0.0 0 1

Nested Loop (cost=1.41..162.73 rows=1 width=4) (actual time=0.030..0.031 rows=0 loops=1)

  • Output: timeoffapprovalhistory2.timeoffid
  • Inner Unique: true
  • Buffers: shared hit=4
60. 0.000 0.030 ↓ 0.0 0 1

Nested Loop (cost=0.98..160.28 rows=1 width=20) (actual time=0.030..0.030 rows=0 loops=1)

  • Output: timeoffapprovalhistory2.timeoffid, imeoffapprovalhistorykeyvalue3.parentid
  • Inner Unique: true
  • Buffers: shared hit=4
61. 0.030 0.030 ↓ 0.0 0 1

Index Scan using ixtoahkvuri on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistorykeyvalue imeoffapprovalhistorykeyvalue3 (cost=0.55..82.04 rows=32 width=20) (actual time=0.030..0.030 rows=0 loops=1)

  • Output: imeoffapprovalhistorykeyvalue3.id, imeoffapprovalhistorykeyvalue3.timeoffapprovalhistoryid, imeoffapprovalhistorykeyvalue3.parentid, imeoffapprovalhistorykeyvalue3.index, imeoffapprovalhistorykeyvalue3.key, imeoffapprovalhistorykeyvalue3.uri, imeoffapprovalhistorykeyvalue3.slug, imeoffapprovalhistorykeyvalue3."boolean", imeoffapprovalhistorykeyvalue3.date, imeoffapprovalhistorykeyvalue3.number, imeoffapprovalhistorykeyvalue3.text, imeoffapprovalhistorykeyvalue3."time", imeoffapprovalhistorykeyvalue3.timespan, imeoffapprovalhistorykeyvalue3.daterange_startdate, imeoffapprovalhistorykeyvalue3.daterange_enddate, imeoffapprovalhistorykeyvalue3.daterange_relativedaterangeuri, imeoffapprovalhistorykeyvalue3.daterange_relativedaterangeasofdate, imeoffapprovalhistorykeyvalue3.workdayduration_decimalworkdays, imeoffapprovalhistorykeyvalue3.workdayduration_workdays, imeoffapprovalhistorykeyvalue3.workdayduration_hours, imeoffapprovalhistorykeyvalue3.workdayduration_minutes
  • Index Cond: (upper(imeoffapprovalhistorykeyvalue3.uri) = 'URN:REPLICON-TENANT:2BC736BCEB814ED5B2CB107A69397ACD:USER:2892'::text)
  • Filter: (upper(imeoffapprovalhistorykeyvalue3.key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-NODES'::text)
  • Buffers: shared hit=4
62. 0.000 0.000 ↓ 0.0 0

Index Scan using timeoffapprovalhistory_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistory timeoffapprovalhistory2 (cost=0.42..2.45 rows=1 width=8) (never executed)

  • Output: timeoffapprovalhistory2.id, timeoffapprovalhistory2.timeoffid, timeoffapprovalhistory2.effectivedateutc, timeoffapprovalhistory2.action, timeoffapprovalhistory2.approvalcomments, timeoffapprovalhistory2.approvalagenttype, timeoffapprovalhistory2.userid, timeoffapprovalhistory2.systemprocessidentifier
  • Index Cond: (timeoffapprovalhistory2.id = imeoffapprovalhistorykeyvalue3.timeoffapprovalhistoryid)
  • Filter: (timeoffapprovalhistory2.userid = 2892)
63. 0.000 0.000 ↓ 0.0 0

Index Scan using timeoffapprovalhistorykeyvalue_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistorykeyvalue imeoffapprovalhistorykeyvalue4 (cost=0.43..2.45 rows=1 width=16) (never executed)

  • Output: imeoffapprovalhistorykeyvalue4.id, imeoffapprovalhistorykeyvalue4.timeoffapprovalhistoryid, imeoffapprovalhistorykeyvalue4.parentid, imeoffapprovalhistorykeyvalue4.index, imeoffapprovalhistorykeyvalue4.key, imeoffapprovalhistorykeyvalue4.uri, imeoffapprovalhistorykeyvalue4.slug, imeoffapprovalhistorykeyvalue4."boolean", imeoffapprovalhistorykeyvalue4.date, imeoffapprovalhistorykeyvalue4.number, imeoffapprovalhistorykeyvalue4.text, imeoffapprovalhistorykeyvalue4."time", imeoffapprovalhistorykeyvalue4.timespan, imeoffapprovalhistorykeyvalue4.daterange_startdate, imeoffapprovalhistorykeyvalue4.daterange_enddate, imeoffapprovalhistorykeyvalue4.daterange_relativedaterangeuri, imeoffapprovalhistorykeyvalue4.daterange_relativedaterangeasofdate, imeoffapprovalhistorykeyvalue4.workdayduration_decimalworkdays, imeoffapprovalhistorykeyvalue4.workdayduration_workdays, imeoffapprovalhistorykeyvalue4.workdayduration_hours, imeoffapprovalhistorykeyvalue4.workdayduration_minutes
  • Index Cond: (imeoffapprovalhistorykeyvalue4.id = imeoffapprovalhistorykeyvalue3.parentid)
  • Filter: (upper(imeoffapprovalhistorykeyvalue4.uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-EXPECTED-APPROVAL-AGENT-USER'::text)
64. 0.163 0.163 ↓ 1.1 70 1

Index Scan using ixuhsupervisorid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.userhierarchy userhierarchy5 (cost=0.29..102.10 rows=65 width=4) (actual time=0.029..0.163 rows=70 loops=1)

  • Output: userhierarchy5.userid
  • Index Cond: (userhierarchy5.supervisorid = 2892)
  • Filter: ((userhierarchy5.startdate <= '2020-02-09 00:00:00'::timestamp without time zone) AND (userhierarchy5.enddate >= '2020-02-09 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 92
  • Buffers: shared hit=45
65. 0.007 0.116 ↑ 1.0 13 1

HashAggregate (cost=16.11..16.24 rows=13 width=4) (actual time=0.114..0.116 rows=13 loops=1)

  • Output: timeoffapprovalnodes6.timeoffid
  • Group Key: timeoffapprovalnodes6.timeoffid
  • Buffers: shared hit=20
66. 0.040 0.109 ↑ 1.0 13 1

Hash Join (cost=7.35..16.07 rows=13 width=4) (actual time=0.057..0.109 rows=13 loops=1)

  • Output: timeoffapprovalnodes6.timeoffid
  • Inner Unique: true
  • Hash Cond: (timeoffapprovalnodes6.id = timeoffapprovalrequest7.nodeid)
  • Buffers: shared hit=20
67. 0.036 0.036 ↑ 1.0 294 1

Seq Scan on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalnodes timeoffapprovalnodes6 (cost=0.00..7.94 rows=294 width=20) (actual time=0.006..0.036 rows=294 loops=1)

  • Output: timeoffapprovalnodes6.id, timeoffapprovalnodes6.timeoffid, timeoffapprovalnodes6.isapproved, timeoffapprovalnodes6.approverrole, timeoffapprovalnodes6.userid, timeoffapprovalnodes6.agentresolutionstate, timeoffapprovalnodes6.scriptid
  • Buffers: shared hit=5
68. 0.006 0.033 ↑ 1.0 13 1

Hash (cost=7.19..7.19 rows=13 width=16) (actual time=0.033..0.033 rows=13 loops=1)

  • Output: timeoffapprovalrequest7.nodeid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=15
69. 0.027 0.027 ↑ 1.0 13 1

Index Scan using ixtoarapproverid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalrequest timeoffapprovalrequest7 (cost=0.27..7.19 rows=13 width=16) (actual time=0.018..0.027 rows=13 loops=1)

  • Output: timeoffapprovalrequest7.nodeid
  • Index Cond: (timeoffapprovalrequest7.approverid = 2892)
  • Buffers: shared hit=15
70. 0.005 1.286 ↑ 1.0 1 1

Aggregate (cost=3,714.85..3,714.86 rows=1 width=8) (actual time=1.286..1.286 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=55
71. 0.942 1.281 ↑ 25.1 20 1

CTE Scan on eofflist_rtcombined_facts14cte _timeofflist_rtcombined_facts8 (cost=277.46..3,713.60 rows=501 width=0) (actual time=0.472..1.281 rows=20 loops=1)

  • Output: _timeofflist_rtcombined_facts8.timeoffid10, _timeofflist_rtcombined_facts8.timeoffslug11, _timeofflist_rtcombined_facts8.timeoffowneruserid12, _timeofflist_rtcombined_facts8.timeoffowneruserdisplayname13, _timeofflist_rtcombined_facts8.timeoffowneruserslug14, _timeofflist_rtcombined_facts8.timeoffowneruserenabled15, _timeofflist_rtcombined_facts8.timeoffownerdepartmentid16, _timeofflist_rtcombined_facts8.timeoffownerdepartmentdisplayname17, _timeofflist_rtcombined_facts8.timeoffownerdepartmentslug18, _timeofflist_rtcombined_facts8.timeoffownersupervisoruserid19, _timeofflist_rtcombined_facts8.timeoffownersupervisoruserdisplayname20, _timeofflist_rtcombined_facts8.timeoffownersupervisoruserslug21, _timeofflist_rtcombined_facts8.timeoffcodeid22, _timeofflist_rtcombined_facts8.timeoffcodedisplayname23, _timeofflist_rtcombined_facts8.timeoffcodeslug24, _timeofflist_rtcombined_facts8.approvalstatus25, _timeofflist_rtcombined_facts8.timeoffstatus26, _timeofflist_rtcombined_facts8.startdate27, _timeofflist_rtcombined_facts8.enddate28, _timeofflist_rtcombined_facts8.startdaydurationtype29, _timeofflist_rtcombined_facts8.enddaydurationtype30, _timeofflist_rtcombined_facts8.startdayduration31, _timeofflist_rtcombined_facts8.enddayduration32, _timeofflist_rtcombined_facts8.startdaystarttime33, _timeofflist_rtcombined_facts8.enddayendtime34, _timeofflist_rtcombined_facts8.totalduration35, _timeofflist_rtcombined_facts8.totalworkdayduration36, _timeofflist_rtcombined_facts8.effectivehoursperday37, _timeofflist_rtcombined_facts8.lastactiondatetimeutc38, _timeofflist_rtcombined_facts8.waitingonapproveruserid39, _timeofflist_rtcombined_facts8.waitingonapproveruserslug40, _timeofflist_rtcombined_facts8.waitingonapproveruserdisplayname41, _timeofflist_rtcombined_facts8.generatedonutc42, _timeofflist_rtcombined_facts8.timeoffowneruseremployeeid43, _timeofflist_rtcombined_facts8.timeoffowneruserloginname44, _timeofflist_rtcombined_facts8.displayformat45
  • Filter: ((_timeofflist_rtcombined_facts8.timeoffstatus26 = 3) AND ((hashed SubPlan 15) OR (hashed SubPlan 16) OR (hashed SubPlan 17)))
  • Rows Removed by Filter: 4492
  • Buffers: shared hit=55
72.          

SubPlan (for CTE Scan)

73. 0.082 0.082 ↑ 1.0 14 1

Seq Scan on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_remainingapprovers_facts list_remainingapprovers_facts9 (cost=0.00..12.43 rows=14 width=4) (actual time=0.047..0.082 rows=14 loops=1)

  • Output: list_remainingapprovers_facts9.timeoffid
  • Filter: (list_remainingapprovers_facts9.approveruserid = 2892)
  • Rows Removed by Filter: 500
  • Buffers: shared hit=6
74. 0.000 0.043 ↓ 0.0 0 1

Nested Loop (cost=1.41..162.73 rows=1 width=4) (actual time=0.043..0.043 rows=0 loops=1)

  • Output: timeoffapprovalhistory10.timeoffid
  • Inner Unique: true
  • Buffers: shared hit=4
75. 0.000 0.043 ↓ 0.0 0 1

Nested Loop (cost=0.98..160.28 rows=1 width=20) (actual time=0.043..0.043 rows=0 loops=1)

  • Output: timeoffapprovalhistory10.timeoffid, meoffapprovalhistorykeyvalue11.parentid
  • Inner Unique: true
  • Buffers: shared hit=4
76. 0.043 0.043 ↓ 0.0 0 1

Index Scan using ixtoahkvuri on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistorykeyvalue meoffapprovalhistorykeyvalue11 (cost=0.55..82.04 rows=32 width=20) (actual time=0.042..0.043 rows=0 loops=1)

  • Output: meoffapprovalhistorykeyvalue11.id, meoffapprovalhistorykeyvalue11.timeoffapprovalhistoryid, meoffapprovalhistorykeyvalue11.parentid, meoffapprovalhistorykeyvalue11.index, meoffapprovalhistorykeyvalue11.key, meoffapprovalhistorykeyvalue11.uri, meoffapprovalhistorykeyvalue11.slug, meoffapprovalhistorykeyvalue11."boolean", meoffapprovalhistorykeyvalue11.date, meoffapprovalhistorykeyvalue11.number, meoffapprovalhistorykeyvalue11.text, meoffapprovalhistorykeyvalue11."time", meoffapprovalhistorykeyvalue11.timespan, meoffapprovalhistorykeyvalue11.daterange_startdate, meoffapprovalhistorykeyvalue11.daterange_enddate, meoffapprovalhistorykeyvalue11.daterange_relativedaterangeuri, meoffapprovalhistorykeyvalue11.daterange_relativedaterangeasofdate, meoffapprovalhistorykeyvalue11.workdayduration_decimalworkdays, meoffapprovalhistorykeyvalue11.workdayduration_workdays, meoffapprovalhistorykeyvalue11.workdayduration_hours, meoffapprovalhistorykeyvalue11.workdayduration_minutes
  • Index Cond: (upper(meoffapprovalhistorykeyvalue11.uri) = 'URN:REPLICON-TENANT:2BC736BCEB814ED5B2CB107A69397ACD:USER:2892'::text)
  • Filter: (upper(meoffapprovalhistorykeyvalue11.key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-NODES'::text)
  • Buffers: shared hit=4
77. 0.000 0.000 ↓ 0.0 0

Index Scan using timeoffapprovalhistory_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistory timeoffapprovalhistory10 (cost=0.42..2.45 rows=1 width=8) (never executed)

  • Output: timeoffapprovalhistory10.id, timeoffapprovalhistory10.timeoffid, timeoffapprovalhistory10.effectivedateutc, timeoffapprovalhistory10.action, timeoffapprovalhistory10.approvalcomments, timeoffapprovalhistory10.approvalagenttype, timeoffapprovalhistory10.userid, timeoffapprovalhistory10.systemprocessidentifier
  • Index Cond: (timeoffapprovalhistory10.id = meoffapprovalhistorykeyvalue11.timeoffapprovalhistoryid)
  • Filter: (timeoffapprovalhistory10.userid = 2892)
78. 0.000 0.000 ↓ 0.0 0

Index Scan using timeoffapprovalhistorykeyvalue_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistorykeyvalue meoffapprovalhistorykeyvalue12 (cost=0.43..2.45 rows=1 width=16) (never executed)

  • Output: meoffapprovalhistorykeyvalue12.id, meoffapprovalhistorykeyvalue12.timeoffapprovalhistoryid, meoffapprovalhistorykeyvalue12.parentid, meoffapprovalhistorykeyvalue12.index, meoffapprovalhistorykeyvalue12.key, meoffapprovalhistorykeyvalue12.uri, meoffapprovalhistorykeyvalue12.slug, meoffapprovalhistorykeyvalue12."boolean", meoffapprovalhistorykeyvalue12.date, meoffapprovalhistorykeyvalue12.number, meoffapprovalhistorykeyvalue12.text, meoffapprovalhistorykeyvalue12."time", meoffapprovalhistorykeyvalue12.timespan, meoffapprovalhistorykeyvalue12.daterange_startdate, meoffapprovalhistorykeyvalue12.daterange_enddate, meoffapprovalhistorykeyvalue12.daterange_relativedaterangeuri, meoffapprovalhistorykeyvalue12.daterange_relativedaterangeasofdate, meoffapprovalhistorykeyvalue12.workdayduration_decimalworkdays, meoffapprovalhistorykeyvalue12.workdayduration_workdays, meoffapprovalhistorykeyvalue12.workdayduration_hours, meoffapprovalhistorykeyvalue12.workdayduration_minutes
  • Index Cond: (meoffapprovalhistorykeyvalue12.id = meoffapprovalhistorykeyvalue11.parentid)
  • Filter: (upper(meoffapprovalhistorykeyvalue12.uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-EXPECTED-APPROVAL-AGENT-USER'::text)
79. 0.214 0.214 ↓ 1.1 70 1

Index Scan using ixuhsupervisorid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.userhierarchy userhierarchy13 (cost=0.29..102.10 rows=65 width=4) (actual time=0.046..0.214 rows=70 loops=1)

  • Output: userhierarchy13.userid
  • Index Cond: (userhierarchy13.supervisorid = 2892)
  • Filter: ((userhierarchy13.startdate <= '2020-02-09 00:00:00'::timestamp without time zone) AND (userhierarchy13.enddate >= '2020-02-09 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 92
  • Buffers: shared hit=45
80. 0.004 0.947 ↑ 1.0 1 1

Aggregate (cost=2,595.26..2,595.27 rows=1 width=8) (actual time=0.947..0.947 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=20
81. 0.023 0.943 ↑ 2.8 13 1

Hash Join (cost=16.53..2,595.17 rows=37 width=0) (actual time=0.938..0.943 rows=13 loops=1)

  • Inner Unique: true
  • Hash Cond: (timeofflist_rtcombined_facts14.timeoffid10 = timeoffapprovalnodes15.timeoffid)
  • Buffers: shared hit=20
82. 0.789 0.789 ↑ 44.1 13 1

CTE Scan on eofflist_rtcombined_facts14cte timeofflist_rtcombined_facts14 (cost=0.00..2,577.11 rows=573 width=4) (actual time=0.785..0.789 rows=13 loops=1)

  • Output: timeofflist_rtcombined_facts14.timeoffid10, timeofflist_rtcombined_facts14.timeoffslug11, timeofflist_rtcombined_facts14.timeoffowneruserid12, timeofflist_rtcombined_facts14.timeoffowneruserdisplayname13, timeofflist_rtcombined_facts14.timeoffowneruserslug14, timeofflist_rtcombined_facts14.timeoffowneruserenabled15, timeofflist_rtcombined_facts14.timeoffownerdepartmentid16, timeofflist_rtcombined_facts14.timeoffownerdepartmentdisplayname17, timeofflist_rtcombined_facts14.timeoffownerdepartmentslug18, timeofflist_rtcombined_facts14.timeoffownersupervisoruserid19, timeofflist_rtcombined_facts14.timeoffownersupervisoruserdisplayname20, timeofflist_rtcombined_facts14.timeoffownersupervisoruserslug21, timeofflist_rtcombined_facts14.timeoffcodeid22, timeofflist_rtcombined_facts14.timeoffcodedisplayname23, timeofflist_rtcombined_facts14.timeoffcodeslug24, timeofflist_rtcombined_facts14.approvalstatus25, timeofflist_rtcombined_facts14.timeoffstatus26, timeofflist_rtcombined_facts14.startdate27, timeofflist_rtcombined_facts14.enddate28, timeofflist_rtcombined_facts14.startdaydurationtype29, timeofflist_rtcombined_facts14.enddaydurationtype30, timeofflist_rtcombined_facts14.startdayduration31, timeofflist_rtcombined_facts14.enddayduration32, timeofflist_rtcombined_facts14.startdaystarttime33, timeofflist_rtcombined_facts14.enddayendtime34, timeofflist_rtcombined_facts14.totalduration35, timeofflist_rtcombined_facts14.totalworkdayduration36, timeofflist_rtcombined_facts14.effectivehoursperday37, timeofflist_rtcombined_facts14.lastactiondatetimeutc38, timeofflist_rtcombined_facts14.waitingonapproveruserid39, timeofflist_rtcombined_facts14.waitingonapproveruserslug40, timeofflist_rtcombined_facts14.waitingonapproveruserdisplayname41, timeofflist_rtcombined_facts14.generatedonutc42, timeofflist_rtcombined_facts14.timeoffowneruseremployeeid43, timeofflist_rtcombined_facts14.timeoffowneruserloginname44, timeofflist_rtcombined_facts14.displayformat45
  • Filter: (timeofflist_rtcombined_facts14.timeoffstatus26 = 1)
  • Rows Removed by Filter: 4499
83. 0.005 0.131 ↑ 1.0 13 1

Hash (cost=16.37..16.37 rows=13 width=4) (actual time=0.131..0.131 rows=13 loops=1)

  • Output: timeoffapprovalnodes15.timeoffid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=20
84. 0.007 0.126 ↑ 1.0 13 1

HashAggregate (cost=16.11..16.24 rows=13 width=4) (actual time=0.125..0.126 rows=13 loops=1)

  • Output: timeoffapprovalnodes15.timeoffid
  • Group Key: timeoffapprovalnodes15.timeoffid
  • Buffers: shared hit=20
85. 0.042 0.119 ↑ 1.0 13 1

Hash Join (cost=7.35..16.07 rows=13 width=4) (actual time=0.068..0.119 rows=13 loops=1)

  • Output: timeoffapprovalnodes15.timeoffid
  • Inner Unique: true
  • Hash Cond: (timeoffapprovalnodes15.id = timeoffapprovalrequest16.nodeid)
  • Buffers: shared hit=20
86. 0.036 0.036 ↑ 1.0 294 1

Seq Scan on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalnodes timeoffapprovalnodes15 (cost=0.00..7.94 rows=294 width=20) (actual time=0.008..0.036 rows=294 loops=1)

  • Output: timeoffapprovalnodes15.id, timeoffapprovalnodes15.timeoffid, timeoffapprovalnodes15.isapproved, timeoffapprovalnodes15.approverrole, timeoffapprovalnodes15.userid, timeoffapprovalnodes15.agentresolutionstate, timeoffapprovalnodes15.scriptid
  • Buffers: shared hit=5
87. 0.006 0.041 ↑ 1.0 13 1

Hash (cost=7.19..7.19 rows=13 width=16) (actual time=0.041..0.041 rows=13 loops=1)

  • Output: timeoffapprovalrequest16.nodeid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=15
88. 0.035 0.035 ↑ 1.0 13 1

Index Scan using ixtoarapproverid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalrequest timeoffapprovalrequest16 (cost=0.27..7.19 rows=13 width=16) (actual time=0.026..0.035 rows=13 loops=1)

  • Output: timeoffapprovalrequest16.nodeid
  • Index Cond: (timeoffapprovalrequest16.approverid = 2892)
  • Buffers: shared hit=15
89. 0.001 1.169 ↑ 1.0 1 1

Aggregate (cost=4,016.84..4,016.85 rows=1 width=8) (actual time=1.169..1.169 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=20
90. 1.076 1.168 ↓ 0.0 0 1

CTE Scan on eofflist_rtcombined_facts14cte timeofflist_rtcombined_facts17 (cost=293.73..4,016.21 rows=251 width=0) (actual time=1.168..1.168 rows=0 loops=1)

  • Output: timeofflist_rtcombined_facts17.timeoffid10, timeofflist_rtcombined_facts17.timeoffslug11, timeofflist_rtcombined_facts17.timeoffowneruserid12, timeofflist_rtcombined_facts17.timeoffowneruserdisplayname13, timeofflist_rtcombined_facts17.timeoffowneruserslug14, timeofflist_rtcombined_facts17.timeoffowneruserenabled15, timeofflist_rtcombined_facts17.timeoffownerdepartmentid16, timeofflist_rtcombined_facts17.timeoffownerdepartmentdisplayname17, timeofflist_rtcombined_facts17.timeoffownerdepartmentslug18, timeofflist_rtcombined_facts17.timeoffownersupervisoruserid19, timeofflist_rtcombined_facts17.timeoffownersupervisoruserdisplayname20, timeofflist_rtcombined_facts17.timeoffownersupervisoruserslug21, timeofflist_rtcombined_facts17.timeoffcodeid22, timeofflist_rtcombined_facts17.timeoffcodedisplayname23, timeofflist_rtcombined_facts17.timeoffcodeslug24, timeofflist_rtcombined_facts17.approvalstatus25, timeofflist_rtcombined_facts17.timeoffstatus26, timeofflist_rtcombined_facts17.startdate27, timeofflist_rtcombined_facts17.enddate28, timeofflist_rtcombined_facts17.startdaydurationtype29, timeofflist_rtcombined_facts17.enddaydurationtype30, timeofflist_rtcombined_facts17.startdayduration31, timeofflist_rtcombined_facts17.enddayduration32, timeofflist_rtcombined_facts17.startdaystarttime33, timeofflist_rtcombined_facts17.enddayendtime34, timeofflist_rtcombined_facts17.totalduration35, timeofflist_rtcombined_facts17.totalworkdayduration36, timeofflist_rtcombined_facts17.effectivehoursperday37, timeofflist_rtcombined_facts17.lastactiondatetimeutc38, timeofflist_rtcombined_facts17.waitingonapproveruserid39, timeofflist_rtcombined_facts17.waitingonapproveruserslug40, timeofflist_rtcombined_facts17.waitingonapproveruserdisplayname41, timeofflist_rtcombined_facts17.generatedonutc42, timeofflist_rtcombined_facts17.timeoffowneruseremployeeid43, timeofflist_rtcombined_facts17.timeoffowneruserloginname44, timeofflist_rtcombined_facts17.displayformat45
  • Filter: ((NOT (hashed SubPlan 23)) AND (timeofflist_rtcombined_facts17.timeoffstatus26 = 1) AND ((hashed SubPlan 20) OR (hashed SubPlan 21) OR (hashed SubPlan 22)))
  • Rows Removed by Filter: 4512
  • Buffers: shared hit=20
91.          

SubPlan (for CTE Scan)

92. 0.005 0.092 ↑ 1.0 13 1

HashAggregate (cost=16.11..16.24 rows=13 width=4) (actual time=0.091..0.092 rows=13 loops=1)

  • Output: timeoffapprovalnodes23.timeoffid
  • Group Key: timeoffapprovalnodes23.timeoffid
  • Buffers: shared hit=20
93. 0.035 0.087 ↑ 1.0 13 1

Hash Join (cost=7.35..16.07 rows=13 width=4) (actual time=0.043..0.087 rows=13 loops=1)

  • Output: timeoffapprovalnodes23.timeoffid
  • Inner Unique: true
  • Hash Cond: (timeoffapprovalnodes23.id = timeoffapprovalrequest24.nodeid)
  • Buffers: shared hit=20
94. 0.023 0.023 ↑ 1.0 294 1

Seq Scan on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalnodes timeoffapprovalnodes23 (cost=0.00..7.94 rows=294 width=20) (actual time=0.002..0.023 rows=294 loops=1)

  • Output: timeoffapprovalnodes23.id, timeoffapprovalnodes23.timeoffid, timeoffapprovalnodes23.isapproved, timeoffapprovalnodes23.approverrole, timeoffapprovalnodes23.userid, timeoffapprovalnodes23.agentresolutionstate, timeoffapprovalnodes23.scriptid
  • Buffers: shared hit=5
95. 0.005 0.029 ↑ 1.0 13 1

Hash (cost=7.19..7.19 rows=13 width=16) (actual time=0.029..0.029 rows=13 loops=1)

  • Output: timeoffapprovalrequest24.nodeid
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=15
96. 0.024 0.024 ↑ 1.0 13 1

Index Scan using ixtoarapproverid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalrequest timeoffapprovalrequest24 (cost=0.27..7.19 rows=13 width=16) (actual time=0.020..0.024 rows=13 loops=1)

  • Output: timeoffapprovalrequest24.nodeid
  • Index Cond: (timeoffapprovalrequest24.approverid = 2892)
  • Buffers: shared hit=15
97. 0.000 0.000 ↓ 0.0 0

Seq Scan on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_remainingapprovers_facts ist_remainingapprovers_facts18 (cost=0.00..12.43 rows=14 width=4) (never executed)

  • Output: ist_remainingapprovers_facts18.timeoffid
  • Filter: (ist_remainingapprovers_facts18.approveruserid = 2892)
98. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.41..162.73 rows=1 width=4) (never executed)

  • Output: timeoffapprovalhistory19_2.timeoffid
  • Inner Unique: true
99. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.98..160.28 rows=1 width=20) (never executed)

  • Output: timeoffapprovalhistory19_2.timeoffid, meoffapprovalhistorykeyvalue20.parentid
  • Inner Unique: true
100. 0.000 0.000 ↓ 0.0 0

Index Scan using ixtoahkvuri on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistorykeyvalue meoffapprovalhistorykeyvalue20 (cost=0.55..82.04 rows=32 width=20) (never executed)

  • Output: meoffapprovalhistorykeyvalue20.id, meoffapprovalhistorykeyvalue20.timeoffapprovalhistoryid, meoffapprovalhistorykeyvalue20.parentid, meoffapprovalhistorykeyvalue20.index, meoffapprovalhistorykeyvalue20.key, meoffapprovalhistorykeyvalue20.uri, meoffapprovalhistorykeyvalue20.slug, meoffapprovalhistorykeyvalue20."boolean", meoffapprovalhistorykeyvalue20.date, meoffapprovalhistorykeyvalue20.number, meoffapprovalhistorykeyvalue20.text, meoffapprovalhistorykeyvalue20."time", meoffapprovalhistorykeyvalue20.timespan, meoffapprovalhistorykeyvalue20.daterange_startdate, meoffapprovalhistorykeyvalue20.daterange_enddate, meoffapprovalhistorykeyvalue20.daterange_relativedaterangeuri, meoffapprovalhistorykeyvalue20.daterange_relativedaterangeasofdate, meoffapprovalhistorykeyvalue20.workdayduration_decimalworkdays, meoffapprovalhistorykeyvalue20.workdayduration_workdays, meoffapprovalhistorykeyvalue20.workdayduration_hours, meoffapprovalhistorykeyvalue20.workdayduration_minutes
  • Index Cond: (upper(meoffapprovalhistorykeyvalue20.uri) = 'URN:REPLICON-TENANT:2BC736BCEB814ED5B2CB107A69397ACD:USER:2892'::text)
  • Filter: (upper(meoffapprovalhistorykeyvalue20.key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-NODES'::text)
101. 0.000 0.000 ↓ 0.0 0

Index Scan using timeoffapprovalhistory_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistory timeoffapprovalhistory19_2 (cost=0.42..2.45 rows=1 width=8) (never executed)

  • Output: timeoffapprovalhistory19_2.id, timeoffapprovalhistory19_2.timeoffid, timeoffapprovalhistory19_2.effectivedateutc, timeoffapprovalhistory19_2.action, timeoffapprovalhistory19_2.approvalcomments, timeoffapprovalhistory19_2.approvalagenttype, timeoffapprovalhistory19_2.userid, timeoffapprovalhistory19_2.systemprocessidentifier
  • Index Cond: (timeoffapprovalhistory19_2.id = meoffapprovalhistorykeyvalue20.timeoffapprovalhistoryid)
  • Filter: (timeoffapprovalhistory19_2.userid = 2892)
102. 0.000 0.000 ↓ 0.0 0

Index Scan using timeoffapprovalhistorykeyvalue_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistorykeyvalue meoffapprovalhistorykeyvalue21 (cost=0.43..2.45 rows=1 width=16) (never executed)

  • Output: meoffapprovalhistorykeyvalue21.id, meoffapprovalhistorykeyvalue21.timeoffapprovalhistoryid, meoffapprovalhistorykeyvalue21.parentid, meoffapprovalhistorykeyvalue21.index, meoffapprovalhistorykeyvalue21.key, meoffapprovalhistorykeyvalue21.uri, meoffapprovalhistorykeyvalue21.slug, meoffapprovalhistorykeyvalue21."boolean", meoffapprovalhistorykeyvalue21.date, meoffapprovalhistorykeyvalue21.number, meoffapprovalhistorykeyvalue21.text, meoffapprovalhistorykeyvalue21."time", meoffapprovalhistorykeyvalue21.timespan, meoffapprovalhistorykeyvalue21.daterange_startdate, meoffapprovalhistorykeyvalue21.daterange_enddate, meoffapprovalhistorykeyvalue21.daterange_relativedaterangeuri, meoffapprovalhistorykeyvalue21.daterange_relativedaterangeasofdate, meoffapprovalhistorykeyvalue21.workdayduration_decimalworkdays, meoffapprovalhistorykeyvalue21.workdayduration_workdays, meoffapprovalhistorykeyvalue21.workdayduration_hours, meoffapprovalhistorykeyvalue21.workdayduration_minutes
  • Index Cond: (meoffapprovalhistorykeyvalue21.id = meoffapprovalhistorykeyvalue20.parentid)
  • Filter: (upper(meoffapprovalhistorykeyvalue21.uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-EXPECTED-APPROVAL-AGENT-USER'::text)
103. 0.000 0.000 ↓ 0.0 0

Index Scan using ixuhsupervisorid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.userhierarchy userhierarchy22 (cost=0.29..102.10 rows=65 width=4) (never executed)

  • Output: userhierarchy22.userid
  • Index Cond: (userhierarchy22.supervisorid = 2892)
  • Filter: ((userhierarchy22.startdate <= '2020-02-09 00:00:00'::timestamp without time zone) AND (userhierarchy22.enddate >= '2020-02-09 00:00:00'::timestamp without time zone))
104. 0.340 365.770 ↑ 1.0 1 1

Aggregate (cost=24,269.82..24,269.83 rows=1 width=8) (actual time=365.770..365.770 rows=1 loops=1)

  • Output: count(*)
  • Buffers: shared hit=60612
105. 0.360 365.430 ↑ 55.0 3,622 1

Append (cost=278.16..23,771.69 rows=199,252 width=0) (actual time=0.390..365.430 rows=3,622 loops=1)

  • Buffers: shared hit=60612
106. 52.646 363.980 ↑ 54.8 3,622 1

Subquery Scan on *SELECT* 1_1 (cost=278.16..23,339.00 rows=198,612 width=0) (actual time=0.390..363.980 rows=3,622 loops=1)

  • Filter: ((hashed SubPlan 25) OR (hashed SubPlan 26) OR (hashed SubPlan 27))
  • Rows Removed by Filter: 223791
  • Buffers: shared hit=60432
107. 50.734 310.832 ↓ 1.0 227,413 1

Merge Anti Join (cost=0.70..19,089.30 rows=226,985 width=1,553) (actual time=0.060..310.832 rows=227,413 loops=1)

  • Output: dm_timeofflist_facts_2.timeoffid, NULL::text, dm_timeofflist_facts_2.timeoffowneruserid, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::integer, NULL::date, NULL::date, NULL::integer, NULL::integer, NULL::interval, NULL::interval, NULL::time without time zone, NULL::time without time zone, NULL::interval, NULL::numeric(19,4), NULL::interval, NULL::timestamp without time zone, NULL::integer, NULL::text, NULL::text, NULL::timestamp without time zone, NULL::character varying(255), NULL::character varying(255), NULL::integer
  • Merge Cond: (dm_timeofflist_facts_2.timeoffid = dm_timeofflist_realtime_facts_4.timeoffid)
  • Buffers: shared hit=60377
108. 259.523 259.523 ↑ 1.0 228,372 1

Index Scan using dm_timeofflist_facts_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_facts dm_timeofflist_facts_2 (cost=0.42..18,447.82 rows=228,372 width=8) (actual time=0.033..259.523 rows=228,372 loops=1)

  • Output: dm_timeofflist_facts_2.timeoffid, dm_timeofflist_facts_2.timeoffslug, dm_timeofflist_facts_2.timeoffowneruserid, dm_timeofflist_facts_2.timeoffowneruserdisplayname, dm_timeofflist_facts_2.timeoffowneruserslug, dm_timeofflist_facts_2.timeoffowneruserenabled, dm_timeofflist_facts_2.timeoffownerdepartmentid, dm_timeofflist_facts_2.timeoffownerdepartmentdisplayname, dm_timeofflist_facts_2.timeoffownerdepartmentslug, dm_timeofflist_facts_2.timeoffownersupervisoruserid, dm_timeofflist_facts_2.timeoffownersupervisoruserdisplayname, dm_timeofflist_facts_2.timeoffownersupervisoruserslug, dm_timeofflist_facts_2.timeoffcodeid, dm_timeofflist_facts_2.timeoffcodedisplayname, dm_timeofflist_facts_2.timeoffcodeslug, dm_timeofflist_facts_2.approvalstatus, dm_timeofflist_facts_2.timeoffstatus, dm_timeofflist_facts_2.startdate, dm_timeofflist_facts_2.enddate, dm_timeofflist_facts_2.startdaydurationtype, dm_timeofflist_facts_2.enddaydurationtype, dm_timeofflist_facts_2.startdayduration, dm_timeofflist_facts_2.enddayduration, dm_timeofflist_facts_2.startdaystarttime, dm_timeofflist_facts_2.enddayendtime, dm_timeofflist_facts_2.totalduration, dm_timeofflist_facts_2.effectivehoursperday, dm_timeofflist_facts_2.lastactiondatetimeutc, dm_timeofflist_facts_2.waitingonapproveruserid, dm_timeofflist_facts_2.waitingonapproveruserslug, dm_timeofflist_facts_2.waitingonapproveruserdisplayname, dm_timeofflist_facts_2.generatedonutc, dm_timeofflist_facts_2.timeoffowneruseremployeeid, dm_timeofflist_facts_2.timeoffowneruserloginname, dm_timeofflist_facts_2.totalworkdayduration, dm_timeofflist_facts_2.displayformat
  • Filter: (dm_timeofflist_facts_2.timeoffstatus = 2)
  • Rows Removed by Filter: 1196
  • Buffers: shared hit=60150
109. 0.575 0.575 ↑ 1.0 1,393 1

Index Only Scan using dm_timeofflist_realtime_facts_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_4 (cost=0.28..53.19 rows=1,394 width=4) (actual time=0.024..0.575 rows=1,393 loops=1)

  • Output: dm_timeofflist_realtime_facts_4.timeoffid
  • Heap Fetches: 410
  • Buffers: shared hit=227
110.          

SubPlan (for Subquery Scan)

111. 0.094 0.094 ↑ 1.0 14 2

Seq Scan on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_remainingapprovers_facts ist_remainingapprovers_facts26 (cost=0.00..12.43 rows=14 width=4) (actual time=0.026..0.047 rows=14 loops=2)

  • Output: ist_remainingapprovers_facts26.timeoffid
  • Filter: (ist_remainingapprovers_facts26.approveruserid = 2892)
  • Rows Removed by Filter: 500
  • Buffers: shared hit=12
112. 0.002 0.062 ↓ 0.0 0 2

Nested Loop (cost=1.41..162.73 rows=1 width=4) (actual time=0.031..0.031 rows=0 loops=2)

  • Output: timeoffapprovalhistory27.timeoffid
  • Inner Unique: true
  • Buffers: shared hit=8
113. 0.000 0.060 ↓ 0.0 0 2

Nested Loop (cost=0.98..160.28 rows=1 width=20) (actual time=0.030..0.030 rows=0 loops=2)

  • Output: timeoffapprovalhistory27.timeoffid, meoffapprovalhistorykeyvalue28.parentid
  • Inner Unique: true
  • Buffers: shared hit=8
114. 0.060 0.060 ↓ 0.0 0 2

Index Scan using ixtoahkvuri on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistorykeyvalue meoffapprovalhistorykeyvalue28 (cost=0.55..82.04 rows=32 width=20) (actual time=0.030..0.030 rows=0 loops=2)

  • Output: meoffapprovalhistorykeyvalue28.id, meoffapprovalhistorykeyvalue28.timeoffapprovalhistoryid, meoffapprovalhistorykeyvalue28.parentid, meoffapprovalhistorykeyvalue28.index, meoffapprovalhistorykeyvalue28.key, meoffapprovalhistorykeyvalue28.uri, meoffapprovalhistorykeyvalue28.slug, meoffapprovalhistorykeyvalue28."boolean", meoffapprovalhistorykeyvalue28.date, meoffapprovalhistorykeyvalue28.number, meoffapprovalhistorykeyvalue28.text, meoffapprovalhistorykeyvalue28."time", meoffapprovalhistorykeyvalue28.timespan, meoffapprovalhistorykeyvalue28.daterange_startdate, meoffapprovalhistorykeyvalue28.daterange_enddate, meoffapprovalhistorykeyvalue28.daterange_relativedaterangeuri, meoffapprovalhistorykeyvalue28.daterange_relativedaterangeasofdate, meoffapprovalhistorykeyvalue28.workdayduration_decimalworkdays, meoffapprovalhistorykeyvalue28.workdayduration_workdays, meoffapprovalhistorykeyvalue28.workdayduration_hours, meoffapprovalhistorykeyvalue28.workdayduration_minutes
  • Index Cond: (upper(meoffapprovalhistorykeyvalue28.uri) = 'URN:REPLICON-TENANT:2BC736BCEB814ED5B2CB107A69397ACD:USER:2892'::text)
  • Filter: (upper(meoffapprovalhistorykeyvalue28.key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-NODES'::text)
  • Buffers: shared hit=8
115. 0.000 0.000 ↓ 0.0 0

Index Scan using timeoffapprovalhistory_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistory timeoffapprovalhistory27 (cost=0.42..2.45 rows=1 width=8) (never executed)

  • Output: timeoffapprovalhistory27.id, timeoffapprovalhistory27.timeoffid, timeoffapprovalhistory27.effectivedateutc, timeoffapprovalhistory27.action, timeoffapprovalhistory27.approvalcomments, timeoffapprovalhistory27.approvalagenttype, timeoffapprovalhistory27.userid, timeoffapprovalhistory27.systemprocessidentifier
  • Index Cond: (timeoffapprovalhistory27.id = meoffapprovalhistorykeyvalue28.timeoffapprovalhistoryid)
  • Filter: (timeoffapprovalhistory27.userid = 2892)
116. 0.000 0.000 ↓ 0.0 0

Index Scan using timeoffapprovalhistorykeyvalue_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.timeoffapprovalhistorykeyvalue meoffapprovalhistorykeyvalue29 (cost=0.43..2.45 rows=1 width=16) (never executed)

  • Output: meoffapprovalhistorykeyvalue29.id, meoffapprovalhistorykeyvalue29.timeoffapprovalhistoryid, meoffapprovalhistorykeyvalue29.parentid, meoffapprovalhistorykeyvalue29.index, meoffapprovalhistorykeyvalue29.key, meoffapprovalhistorykeyvalue29.uri, meoffapprovalhistorykeyvalue29.slug, meoffapprovalhistorykeyvalue29."boolean", meoffapprovalhistorykeyvalue29.date, meoffapprovalhistorykeyvalue29.number, meoffapprovalhistorykeyvalue29.text, meoffapprovalhistorykeyvalue29."time", meoffapprovalhistorykeyvalue29.timespan, meoffapprovalhistorykeyvalue29.daterange_startdate, meoffapprovalhistorykeyvalue29.daterange_enddate, meoffapprovalhistorykeyvalue29.daterange_relativedaterangeuri, meoffapprovalhistorykeyvalue29.daterange_relativedaterangeasofdate, meoffapprovalhistorykeyvalue29.workdayduration_decimalworkdays, meoffapprovalhistorykeyvalue29.workdayduration_workdays, meoffapprovalhistorykeyvalue29.workdayduration_hours, meoffapprovalhistorykeyvalue29.workdayduration_minutes
  • Index Cond: (meoffapprovalhistorykeyvalue29.id = meoffapprovalhistorykeyvalue28.parentid)
  • Filter: (upper(meoffapprovalhistorykeyvalue29.uri) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:REMOVED-APPROVAL-REQUEST-EXPECTED-APPROVAL-AGENT-USER'::text)
117. 0.346 0.346 ↓ 1.1 70 2

Index Scan using ixuhsupervisorid on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.userhierarchy userhierarchy30 (cost=0.29..102.10 rows=65 width=4) (actual time=0.026..0.173 rows=70 loops=2)

  • Output: userhierarchy30.userid
  • Index Cond: (userhierarchy30.supervisorid = 2892)
  • Filter: ((userhierarchy30.startdate <= '2020-02-09 00:00:00'::timestamp without time zone) AND (userhierarchy30.enddate >= '2020-02-09 00:00:00'::timestamp without time zone))
  • Rows Removed by Filter: 92
  • Buffers: shared hit=90
118. 0.474 1.090 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2_1 (cost=277.46..432.70 rows=640 width=0) (actual time=1.090..1.090 rows=0 loops=1)

  • Filter: ((hashed SubPlan 25) OR (hashed SubPlan 26) OR (hashed SubPlan 27))
  • Rows Removed by Filter: 959
  • Buffers: shared hit=180
119. 0.616 0.616 ↓ 1.3 959 1

Seq Scan on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_5 (cost=0.00..142.43 rows=732 width=1,553) (actual time=0.007..0.616 rows=959 loops=1)

  • Output: dm_timeofflist_realtime_facts_5.timeoffid, NULL::text, dm_timeofflist_realtime_facts_5.timeoffowneruserid, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::integer, NULL::date, NULL::date, NULL::integer, NULL::integer, NULL::interval, NULL::interval, NULL::time without time zone, NULL::time without time zone, NULL::interval, NULL::numeric(19,4), NULL::interval, NULL::timestamp without time zone, NULL::integer, NULL::text, NULL::text, NULL::timestamp without time zone, NULL::character varying(255), NULL::character varying(255), NULL::integer
  • Filter: ((NOT dm_timeofflist_realtime_facts_5.deleted) AND (dm_timeofflist_realtime_facts_5.timeoffstatus = 2))
  • Rows Removed by Filter: 434
  • Buffers: shared hit=125
120. 28,774.220 28,774.267 ↑ 229,142.0 1 1

Result (cost=0.70..12,151.22 rows=229,142 width=40) (actual time=28,774.267..28,774.267 rows=1 loops=1)

  • Output: $37, $43, $44, $51, $57
  • Buffers: shared hit=13378560 read=14283
121. 0.000 0.047 ↑ 229,142.0 1 1

Append (cost=0.70..9,859.80 rows=229,142 width=0) (actual time=0.047..0.047 rows=1 loops=1)

  • Buffers: shared hit=7
122. 0.000 0.047 ↑ 228,172.0 1 1

Subquery Scan on *SELECT* 1 (cost=0.70..9,711.16 rows=228,172 width=0) (actual time=0.047..0.047 rows=1 loops=1)

  • Buffers: shared hit=7
123. 0.003 0.047 ↑ 228,172.0 1 1

Merge Anti Join (cost=0.70..7,429.44 rows=228,172 width=1,553) (actual time=0.046..0.047 rows=1 loops=1)

  • Output: NULL::integer, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::integer, NULL::date, NULL::date, NULL::integer, NULL::integer, NULL::interval, NULL::interval, NULL::time without time zone, NULL::time without time zone, NULL::interval, NULL::numeric(19,4), NULL::interval, NULL::timestamp without time zone, NULL::integer, NULL::text, NULL::text, NULL::timestamp without time zone, NULL::character varying(255), NULL::character varying(255), NULL::integer
  • Merge Cond: (dm_timeofflist_facts.timeoffid = dm_timeofflist_realtime_facts.timeoffid)
  • Buffers: shared hit=7
124. 0.027 0.027 ↑ 229,566.0 1 1

Index Only Scan using dm_timeofflist_facts_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_facts (cost=0.42..6,784.91 rows=229,566 width=4) (actual time=0.027..0.027 rows=1 loops=1)

  • Output: dm_timeofflist_facts.timeoffid
  • Heap Fetches: 0
  • Buffers: shared hit=4
125. 0.017 0.017 ↑ 1,394.0 1 1

Index Only Scan using dm_timeofflist_realtime_facts_pkey on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_realtime_facts (cost=0.28..53.19 rows=1,394 width=4) (actual time=0.017..0.017 rows=1 loops=1)

  • Output: dm_timeofflist_realtime_facts.timeoffid
  • Heap Fetches: 0
  • Buffers: shared hit=3
126. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2 (cost=0.00..148.64 rows=970 width=0) (never executed)

127. 0.000 0.000 ↓ 0.0 0

Seq Scan on jsiprodg3trial_67c658be93d64a3fbc56f029e5ed9f6c.dm_timeofflist_realtime_facts dm_timeofflist_realtime_facts_1 (cost=0.00..138.94 rows=970 width=1,553) (never executed)

  • Output: NULL::integer, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::boolean, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::text, NULL::text, NULL::integer, NULL::integer, NULL::date, NULL::date, NULL::integer, NULL::integer, NULL::interval, NULL::interval, NULL::time without time zone, NULL::time without time zone, NULL::interval, NULL::numeric(19,4), NULL::interval, NULL::timestamp without time zone, NULL::integer, NULL::text, NULL::text, NULL::timestamp without time zone, NULL::character varying(255), NULL::character varying(255), NULL::integer
  • Filter: (NOT dm_timeofflist_realtime_facts_1.deleted)
Planning time : 20.177 ms
Execution time : 28,775.998 ms