explain.depesz.com

PostgreSQL's explain analyze made readable

Result: pcA6

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

Sort (cost=919,977.22..919,977.83 rows=245 width=356) (actual rows= loops=)

  • Sort Key: ts.startdate, ts.enddate, ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", "*SELECT* 1".entrydate, "*SELECT* 1".modifiedonutc, "*SELECT* 1".action, "*SELECT* 1".validuntilutc, ui.id, ts.id
2. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.82..919,967.49 rows=245 width=356) (actual rows= loops=)

  • Join Filter: ("*SELECT* 1".userid = ui.id)
3. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..97.21 rows=2,421 width=33) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Materialize (cost=1.82..910,973.72 rows=245 width=117) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.82..910,972.50 rows=245 width=117) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.40..910,860.32 rows=247 width=125) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Append (cost=0.98..910,494.25 rows=247 width=109) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=0.98..24.62 rows=1 width=80) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.98..24.61 rows=1 width=348) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.54..21.30 rows=1 width=60) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.12..12.65 rows=1 width=44) (actual rows= loops=)

  • Join Filter: (at.rootid = allocatedtime.rootid)
12. 0.000 0.000 ↓ 0.0

Seq Scan on allocatedtime at (cost=0.00..10.50 rows=1 width=60) (actual rows= loops=)

  • Filter: ((fromtimestamputc >= '2020-09-14 04:00:00'::timestamp without time zone) AND (fromtimestamputc < '2020-09-15 04:00:00'::timestamp without time zone) AND (CASE WHEN (parentid IS NULL) THEN 100 ELSE 101 END = ANY ('{1,2,3,4,6,100,101,102}'::integer[])))
13. 0.000 0.000 ↓ 0.0

Index Only Scan using ixallocatedtimerootid on allocatedtime (cost=0.12..2.14 rows=1 width=16) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_1 (cost=0.42..8.58 rows=7 width=28) (actual rows= loops=)

  • Index Cond: ((userid = at.userid) AND (at.entrydate >= startdate) AND (at.entrydate <= enddate))
15. 0.000 0.000 ↓ 0.0

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tsformat (cost=0.43..3.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = ts_1.id)
  • Filter: ((upper(uri) <> 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text) AND (upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text))
16. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.57..21.13 rows=1 width=80) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Result (cost=0.57..21.12 rows=1 width=348) (actual rows= loops=)

  • One-Time Filter: (102 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.57..21.12 rows=1 width=348) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.14..12.46 rows=1 width=20) (actual rows= loops=)

  • Filter: (child_at.id IS NULL)
20. 0.000 0.000 ↓ 0.0

Seq Scan on allocatedtime at_1 (cost=0.00..10.30 rows=1 width=36) (actual rows= loops=)

  • Filter: ((totimestamputc IS NOT NULL) AND (totimestamputc >= '2020-09-14 04:00:00'::timestamp without time zone) AND (totimestamputc < '2020-09-15 04:00:00'::timestamp without time zone))
21. 0.000 0.000 ↓ 0.0

Index Scan using uixallocatedtime_parent on allocatedtime child_at (cost=0.14..2.16 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (parentid = at_1.id)
22. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_2 (cost=0.42..8.58 rows=7 width=28) (actual rows= loops=)

  • Index Cond: ((userid = at_1.userid) AND (at_1.entrydate >= startdate) AND (at_1.entrydate <= enddate))
23. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 3 (cost=4,030.07..19,150.33 rows=232 width=110) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Gather (cost=4,030.07..19,148.01 rows=232 width=378) (actual rows= loops=)

  • Workers Planned: 2
25. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3,030.07..18,124.81 rows=97 width=378) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3,029.38..17,894.04 rows=85 width=134) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3,028.70..17,693.13 rows=74 width=72) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Hash Semi Join (cost=3,028.01..17,492.23 rows=74 width=72) (actual rows= loops=)

  • Hash Cond: (ts_3.id = timesheetpolicysettingssnapshot.timesheetid)
29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.98..14,430.03 rows=3,673 width=72) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Parallel Index Scan using ixter2fromtimestamputc on timeentryrevision rev (cost=0.56..13,080.12 rows=493 width=56) (actual rows= loops=)

  • Index Cond: ((fromtimestamputc >= '2020-09-14 04:00:00'::timestamp without time zone) AND (fromtimestamputc < '2020-09-15 04:00:00'::timestamp without time zone))
  • Filter: (CASE WHEN (previousrevisionid IS NULL) THEN 100 ELSE 101 END = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
31. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_3 (cost=0.42..2.67 rows=7 width=28) (actual rows= loops=)

  • Index Cond: ((userid = rev.userid) AND (rev.entrydate >= startdate) AND (rev.entrydate <= enddate))
32. 0.000 0.000 ↓ 0.0

Hash (cost=2,990.81..2,990.81 rows=2,898 width=16) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot (cost=0.68..2,990.81 rows=2,898 width=16) (actual rows= loops=)

  • Index Cond: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text))
34. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue changereason (cost=0.69..2.70 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:COMMENT'::text))
35. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser (cost=0.69..2.70 rows=1 width=78) (actual rows= loops=)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
36. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser (cost=0.69..2.70 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((revisionauditid = rev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
37. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 4 (cost=1,003.66..19,615.42 rows=1 width=110) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Result (cost=1,003.66..19,615.41 rows=1 width=378) (actual rows= loops=)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
39. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=1,003.66..19,615.41 rows=1 width=378) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.22..19,612.09 rows=1 width=102) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.53..19,609.40 rows=1 width=118) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,001.84..19,606.72 rows=1 width=56) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,001.42..19,605.89 rows=1 width=40) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.99..19,601.80 rows=1 width=48) (actual rows= loops=)

  • Workers Planned: 1
45. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=0.99..18,601.70 rows=1 width=48) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Parallel Index Scan using ixtemr2fromtimestamputc on timeentrymetadatarevision mdrev (cost=0.56..4,933.16 rows=6,145 width=48) (actual rows= loops=)

  • Index Cond: ((fromtimestamputc >= '2020-09-14 04:00:00'::timestamp without time zone) AND (fromtimestamputc < '2020-09-15 04:00:00'::timestamp without time zone))
47. 0.000 0.000 ↓ 0.0

Index Scan using timeentryrevision_singleinitialrevision on timeentryrevision (cost=0.43..2.21 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (timeentryid = mdrev.timeentryid)
  • Filter: (fromtimestamputc = mdrev.fromtimestamputc)
48. 0.000 0.000 ↓ 0.0

Index Scan using ixtertimeentryid on timeentryrevision rev_1 (cost=0.43..4.09 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (timeentryid = mdrev.timeentryid)
  • Filter: ((fromtimestamputc <= mdrev.fromtimestamputc) AND ((totimestamputc IS NULL) OR (totimestamputc > mdrev.fromtimestamputc)))
49. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_4 (cost=0.42..0.75 rows=7 width=28) (actual rows= loops=)

  • Index Cond: ((userid = rev_1.userid) AND (rev_1.entrydate >= startdate) AND (rev_1.entrydate <= enddate))
50. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_1 (cost=0.69..2.68 rows=1 width=78) (actual rows= loops=)

  • Index Cond: ((revisionauditid = mdrev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
51. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_1 (cost=0.69..2.68 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((revisionauditid = mdrev.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
52. 0.000 0.000 ↓ 0.0

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_1 (cost=0.43..3.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = ts_4.id)
  • Filter: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text))
53. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 5 (cost=4.24..606,664.01 rows=1 width=110) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Result (cost=4.24..606,664.00 rows=1 width=378) (actual rows= loops=)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
55. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=4.24..606,664.00 rows=1 width=378) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.37..606,659.00 rows=1 width=110) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.68..606,656.29 rows=1 width=126) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=1.99..606,653.57 rows=1 width=64) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.56..606,630.88 rows=6 width=64) (actual rows= loops=)

  • Join Filter: ((rev_2.entrydate >= ts_5.startdate) AND (rev_2.entrydate <= ts_5.enddate) AND (rev_2.userid = ts_5.userid))
60. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=1.56..601,071.13 rows=1 width=48) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.99..601,045.28 rows=1 width=108) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Index Scan using ixtemr2fromtimestamputc on timeentrymetadatarevision mdrev_1 (cost=0.56..601,040.77 rows=1 width=100) (actual rows= loops=)

  • Index Cond: ((totimestamputc IS NOT NULL) AND (totimestamputc >= '2020-09-14 04:00:00'::timestamp without time zone) AND (totimestamputc < '2020-09-15 04:00:00'::timestamp without time zone))
63. 0.000 0.000 ↓ 0.0

Index Scan using ixtertimeentryid on timeentryrevision rev_2 (cost=0.43..4.50 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (timeentryid = mdrev_1.timeentryid)
  • Filter: ((fromtimestamputc <= mdrev_1.totimestamputc) AND ((totimestamputc IS NULL) OR (totimestamputc > mdrev_1.totimestamputc)))
64. 0.000 0.000 ↓ 0.0

Index Scan using ixtemr2fromtimestamputc on timeentrymetadatarevision (cost=0.56..25.83 rows=1 width=84) (actual rows= loops=)

  • Index Cond: (fromtimestamputc = mdrev_1.totimestamputc)
  • Filter: ((timeentryid = mdrev_1.timeentryid) AND (key = mdrev_1.key) AND (openingauditid = mdrev_1.closingauditid))
65. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts_5 (cost=0.00..3,048.82 rows=143,482 width=28) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_2 (cost=0.43..3.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = ts_5.id)
  • Filter: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text))
67. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_2 (cost=0.69..2.71 rows=1 width=78) (actual rows= loops=)

  • Index Cond: ((revisionauditid = mdrev_1.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
68. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_2 (cost=0.69..2.71 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((revisionauditid = mdrev_1.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
69. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.86..4.98 rows=1 width=24) (actual rows= loops=)

  • Filter: (child_rev.id IS NULL)
70. 0.000 0.000 ↓ 0.0

Index Scan using ixtertimeentryid on timeentryrevision rev_3 (cost=0.43..4.49 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (timeentryid = mdrev_1.timeentryid)
  • Filter: (totimestamputc = mdrev_1.totimestamputc)
71. 0.000 0.000 ↓ 0.0

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev (cost=0.43..0.48 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (previousrevisionid = rev_3.id)
72. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 6 (cost=3.52..98.02 rows=1 width=110) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Result (cost=3.52..98.01 rows=1 width=378) (actual rows= loops=)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
74. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=3.52..98.01 rows=1 width=378) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.08..94.69 rows=1 width=102) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.39..91.97 rows=1 width=118) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.70..89.25 rows=1 width=56) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.28..88.43 rows=1 width=40) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=0.85..83.92 rows=1 width=48) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Index Scan using ixoefvr2fromtimestamputc on objectextensionfieldvaluerevision oefvr (cost=0.42..15.04 rows=28 width=48) (actual rows= loops=)

  • Index Cond: ((fromtimestamputc >= '2020-09-14 04:00:00'::timestamp without time zone) AND (fromtimestamputc < '2020-09-15 04:00:00'::timestamp without time zone))
81. 0.000 0.000 ↓ 0.0

Index Scan using timeentryrevision_singleinitialrevision on timeentryrevision timeentryrevision_1 (cost=0.43..2.45 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (timeentryid = oefvr.objectid)
  • Filter: (fromtimestamputc = oefvr.fromtimestamputc)
82. 0.000 0.000 ↓ 0.0

Index Scan using ixtertimeentryid on timeentryrevision rev_4 (cost=0.43..4.50 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (timeentryid = oefvr.objectid)
  • Filter: ((fromtimestamputc <= oefvr.fromtimestamputc) AND ((totimestamputc IS NULL) OR (totimestamputc > oefvr.fromtimestamputc)))
83. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_6 (cost=0.42..0.75 rows=7 width=28) (actual rows= loops=)

  • Index Cond: ((userid = rev_4.userid) AND (rev_4.entrydate >= startdate) AND (rev_4.entrydate <= enddate))
84. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_3 (cost=0.69..2.71 rows=1 width=78) (actual rows= loops=)

  • Index Cond: ((revisionauditid = oefvr.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
85. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_3 (cost=0.69..2.71 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((revisionauditid = oefvr.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
86. 0.000 0.000 ↓ 0.0

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_3 (cost=0.43..3.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = ts_6.id)
  • Filter: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text))
87. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 7 (cost=4.38..3,469.79 rows=1 width=110) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Result (cost=4.38..3,469.78 rows=1 width=378) (actual rows= loops=)

  • One-Time Filter: (101 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
89. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=4.38..3,469.78 rows=1 width=378) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=3.50..3,464.78 rows=1 width=110) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.08..3,462.33 rows=1 width=142) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.39..3,459.61 rows=1 width=158) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Nested Loop Semi Join (cost=1.71..3,456.89 rows=1 width=96) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.27..3,430.90 rows=7 width=96) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..3,430.08 rows=1 width=80) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Index Scan using ixoefvr2fromtimestamputc on objectextensionfieldvaluerevision oefvr_1 (cost=0.42..3,425.57 rows=1 width=72) (actual rows= loops=)

  • Index Cond: ((totimestamputc IS NOT NULL) AND (totimestamputc >= '2020-09-14 04:00:00'::timestamp without time zone) AND (totimestamputc < '2020-09-15 04:00:00'::timestamp without time zone))
97. 0.000 0.000 ↓ 0.0

Index Scan using ixtertimeentryid on timeentryrevision rev_5 (cost=0.43..4.50 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (timeentryid = oefvr_1.objectid)
  • Filter: ((fromtimestamputc <= oefvr_1.totimestamputc) AND ((totimestamputc IS NULL) OR (totimestamputc > oefvr_1.totimestamputc)))
98. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_7 (cost=0.42..0.75 rows=7 width=28) (actual rows= loops=)

  • Index Cond: ((userid = rev_5.userid) AND (rev_5.entrydate >= startdate) AND (rev_5.entrydate <= enddate))
99. 0.000 0.000 ↓ 0.0

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_4 (cost=0.43..3.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = ts_7.id)
  • Filter: ((upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text) AND (upper(uri) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT:GEN4-TIMESHEET'::text))
100. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_4 (cost=0.69..2.71 rows=1 width=78) (actual rows= loops=)

  • Index Cond: ((revisionauditid = oefvr_1.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
101. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_4 (cost=0.69..2.71 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((revisionauditid = oefvr_1.openingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
102. 0.000 0.000 ↓ 0.0

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision (cost=0.42..2.45 rows=1 width=56) (actual rows= loops=)

  • Index Cond: (objectid = oefvr_1.objectid)
  • Filter: ((definitionid = oefvr_1.definitionid) AND (fromtimestamputc = oefvr_1.totimestamputc) AND (openingauditid = oefvr_1.closingauditid))
103. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.86..4.98 rows=1 width=24) (actual rows= loops=)

  • Filter: (child_rev_1.id IS NULL)
104. 0.000 0.000 ↓ 0.0

Index Scan using ixtertimeentryid on timeentryrevision rev_6 (cost=0.43..4.49 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (timeentryid = oefvr_1.objectid)
  • Filter: (totimestamputc = oefvr_1.totimestamputc)
105. 0.000 0.000 ↓ 0.0

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev_1 (cost=0.43..0.48 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (previousrevisionid = rev_6.id)
106. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 8 (cost=1,002.92..198,227.13 rows=1 width=110) (actual rows= loops=)

107. 0.000 0.000 ↓ 0.0

Result (cost=1,002.92..198,227.12 rows=1 width=378) (actual rows= loops=)

  • One-Time Filter: (102 = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
108. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.92..198,227.12 rows=1 width=378) (actual rows= loops=)

109. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.23..198,224.41 rows=1 width=110) (actual rows= loops=)

110. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,001.54..198,221.71 rows=1 width=110) (actual rows= loops=)

111. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,000.85..198,219.02 rows=1 width=48) (actual rows= loops=)

112. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.43..198,217.83 rows=1 width=32) (actual rows= loops=)

  • Workers Planned: 2
113. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.43..197,217.73 rows=1 width=32) (actual rows= loops=)

  • Filter: (child_rev_2.id IS NULL)
114. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timeentryrevision rev_7 (cost=0.00..190,743.39 rows=2,725 width=48) (actual rows= loops=)

  • Filter: ((totimestamputc IS NOT NULL) AND (totimestamputc >= '2020-09-14 04:00:00'::timestamp without time zone) AND (totimestamputc < '2020-09-15 04:00:00'::timestamp without time zone))
115. 0.000 0.000 ↓ 0.0

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev_2 (cost=0.43..2.37 rows=1 width=32) (actual rows= loops=)

  • Index Cond: (previousrevisionid = rev_7.id)
116. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_8 (cost=0.42..1.12 rows=7 width=28) (actual rows= loops=)

  • Index Cond: ((userid = rev_7.userid) AND (rev_7.entrydate >= startdate) AND (rev_7.entrydate <= enddate))
117. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_5 (cost=0.69..2.69 rows=1 width=78) (actual rows= loops=)

  • Index Cond: ((revisionauditid = rev_7.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:EFFECTIVE-USER'::text))
118. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_5 (cost=0.69..2.69 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((revisionauditid = rev_7.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:ACTUAL-USER'::text))
119. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue changereason_1 (cost=0.69..2.69 rows=1 width=16) (actual rows= loops=)

  • Index Cond: ((revisionauditid = rev_7.closingauditid) AND (upper(keyuri) = 'URN:REPLICON:REVISION-AUDIT-KEY:COMMENT'::text))
120. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 9 (cost=44,482.51..63,149.98 rows=1 width=119) (actual rows= loops=)

121. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=44,482.51..63,149.97 rows=1 width=387) (actual rows= loops=)

122. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=44,482.08..63,114.78 rows=1 width=119) (actual rows= loops=)

123. 0.000 0.000 ↓ 0.0

Nested Loop (cost=44,481.64..63,079.59 rows=1 width=48) (actual rows= loops=)

124. 0.000 0.000 ↓ 0.0

Nested Loop (cost=44,481.21..63,076.52 rows=1 width=64) (actual rows= loops=)

125. 0.000 0.000 ↓ 0.0

Index Scan using ixtahaction on timesheetapprovalhistory tah (cost=44,480.79..63,074.08 rows=1 width=44) (actual rows= loops=)

  • Index Cond: (action = ANY ('{1,2,3,4,6,100,101,102}'::integer[]))
  • Filter: ((NOT (hashed SubPlan 3)) AND (timestamputc >= '2020-09-14 04:00:00'::timestamp without time zone) AND (timestamputc < '2020-09-15 04:00:00'::timestamp without time zone) AND (COALESCE(upper(systemprocessidentifier), ''::text) <> 'URN:REPLICON:APPROVAL-SYSTEM-PROCESS:TIMESHEET-SUBMIT-SCRIPT-DATA-AND-VALIDATION'::text))
126.          

SubPlan (for Index Scan)

127. 0.000 0.000 ↓ 0.0

Nested Loop (cost=44,089.83..44,480.37 rows=1 width=16) (actual rows= loops=)

128. 0.000 0.000 ↓ 0.0

Hash Join (cost=44,089.40..44,477.90 rows=1 width=24) (actual rows= loops=)

  • Hash Cond: ((faas.timesheetid = tah_3.timesheetid) AND (faas.nextaction_serialnumber = tah_3.serialnumber))
129.          

CTE firstactionaftersubmit

130. 0.000 0.000 ↓ 0.0

HashAggregate (cost=41,255.07..41,408.93 rows=15,386 width=24) (actual rows= loops=)

  • Group Key: submissions.timesheetid, submissions.serialnumber
131.          

CTE submissions

132. 0.000 0.000 ↓ 0.0

Index Scan using ixtahaction on timesheetapprovalhistory (cost=0.42..9,502.64 rows=153,855 width=20) (actual rows= loops=)

  • Index Cond: (action = 1)
133. 0.000 0.000 ↓ 0.0

Hash Join (cost=16,182.62..30,471.64 rows=170,772 width=24) (actual rows= loops=)

  • Hash Cond: (submissions.timesheetid = tah_1.timesheetid)
  • Join Filter: (tah_1.serialnumber > submissions.serialnumber)
134. 0.000 0.000 ↓ 0.0

CTE Scan on submissions (cost=0.00..3,077.10 rows=153,855 width=20) (actual rows= loops=)

135. 0.000 0.000 ↓ 0.0

Hash (cost=10,421.62..10,421.62 rows=460,880 width=20) (actual rows= loops=)

136. 0.000 0.000 ↓ 0.0

Index Only Scan using ixtah2timesheetid on timesheetapprovalhistory tah_1 (cost=0.42..10,421.62 rows=460,880 width=20) (actual rows= loops=)

137. 0.000 0.000 ↓ 0.0

CTE Scan on firstactionaftersubmit faas (cost=0.00..307.72 rows=15,386 width=24) (actual rows= loops=)

138. 0.000 0.000 ↓ 0.0

Hash (cost=2,680.15..2,680.15 rows=22 width=20) (actual rows= loops=)

139. 0.000 0.000 ↓ 0.0

Index Scan using ixtahaction on timesheetapprovalhistory tah_3 (cost=0.42..2,680.15 rows=22 width=20) (actual rows= loops=)

  • Index Cond: (action = 3)
  • Filter: (upper(systemprocessidentifier) = 'URN:REPLICON:APPROVAL-SYSTEM-PROCESS:TIMESHEET-SUBMIT-SCRIPT-DATA-AND-VALIDATION'::text)
140. 0.000 0.000 ↓ 0.0

Index Scan using ixtah2timesheetid on timesheetapprovalhistory tah_2 (cost=0.42..2.44 rows=1 width=36) (actual rows= loops=)

  • Index Cond: ((timesheetid = faas.timesheetid) AND (serialnumber >= faas.submission_serialnumber) AND (serialnumber <= faas.nextaction_serialnumber))
141. 0.000 0.000 ↓ 0.0

Index Scan using timesheet_pkey on timesheet ts_9 (cost=0.42..2.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (id = tah.timesheetid)
142. 0.000 0.000 ↓ 0.0

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tsformat_1 (cost=0.43..3.05 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = ts_9.id)
  • Filter: (upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text)
143. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue kv_ea (cost=0.43..35.18 rows=2 width=87) (actual rows= loops=)

  • Index Cond: (timesheetapprovalhistoryid = tah.id)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
144. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue kv_aa (cost=0.43..35.18 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetapprovalhistoryid = tah.id)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
145. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 10 (cost=16.98..73.80 rows=7 width=80) (actual rows= loops=)

146. 0.000 0.000 ↓ 0.0

Nested Loop (cost=16.98..73.73 rows=7 width=348) (actual rows= loops=)

147. 0.000 0.000 ↓ 0.0

Nested Loop (cost=16.54..52.27 rows=7 width=68) (actual rows= loops=)

148. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16.12..51.45 rows=1 width=52) (actual rows= loops=)

  • Join Filter: (kv_aa_1.timeentryapprovalhistoryid = teah.id)
149. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=15.98..49.28 rows=1 width=68) (actual rows= loops=)

  • Join Filter: (kv_ea_1.timeentryapprovalhistoryid = teah.id)
150. 0.000 0.000 ↓ 0.0

Nested Loop (cost=15.83..47.10 rows=1 width=36) (actual rows= loops=)

151. 0.000 0.000 ↓ 0.0

Seq Scan on timeentryapprovalhistory teah (cost=15.40..44.65 rows=1 width=44) (actual rows= loops=)

  • Filter: ((NOT (hashed SubPlan 6)) AND (timestamputc >= '2020-09-14 04:00:00'::timestamp without time zone) AND (timestamputc < '2020-09-15 04:00:00'::timestamp without time zone) AND (COALESCE(upper(systemprocessidentifier), ''::text) <> 'URN:REPLICON:APPROVAL-SYSTEM-PROCESS:TIME-ENTRY-SUBMIT-SCRIPT-DATA-AND-VALIDATION'::text) AND ((200 + action) = ANY ('{1,2,3,4,6,100,101,102}'::integer[])))
152.          

SubPlan (for Seq Scan)

153. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9.19..15.40 rows=1 width=16) (actual rows= loops=)

154. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9.04..13.21 rows=1 width=24) (actual rows= loops=)

  • Join Filter: ((faas_1.timeentryid = teah_3.timeentryid) AND (faas_1.nextaction_serialnumber = teah_3.serialnumber))
155.          

CTE firstactionaftersubmit

156. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=8.83..8.89 rows=3 width=24) (actual rows= loops=)

  • Group Key: submissions_1.timeentryid, submissions_1.serialnumber
157.          

CTE submissions

158. 0.000 0.000 ↓ 0.0

Index Scan using ixteahaction on timeentryapprovalhistory (cost=0.15..4.20 rows=3 width=20) (actual rows= loops=)

  • Index Cond: (action = 1)
159. 0.000 0.000 ↓ 0.0

Sort (cost=4.62..4.63 rows=3 width=24) (actual rows= loops=)

  • Sort Key: submissions_1.timeentryid, submissions_1.serialnumber
160. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.15..4.60 rows=3 width=24) (actual rows= loops=)

161. 0.000 0.000 ↓ 0.0

CTE Scan on submissions submissions_1 (cost=0.00..0.06 rows=3 width=20) (actual rows= loops=)

162. 0.000 0.000 ↓ 0.0

Index Only Scan using ixteah2timeentryid on timeentryapprovalhistory teah_1 (cost=0.15..1.50 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((timeentryid = submissions_1.timeentryid) AND (serialnumber > submissions_1.serialnumber))
163. 0.000 0.000 ↓ 0.0

Index Scan using ixteahaction on timeentryapprovalhistory teah_3 (cost=0.15..4.22 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (action = 3)
  • Filter: (upper(systemprocessidentifier) = 'URN:REPLICON:APPROVAL-SYSTEM-PROCESS:TIME-ENTRY-SUBMIT-SCRIPT-DATA-AND-VALIDATION'::text)
164. 0.000 0.000 ↓ 0.0

CTE Scan on firstactionaftersubmit faas_1 (cost=0.00..0.06 rows=3 width=24) (actual rows= loops=)

165. 0.000 0.000 ↓ 0.0

Index Scan using ixteah2timeentryid on timeentryapprovalhistory teah_2 (cost=0.15..2.17 rows=1 width=36) (actual rows= loops=)

  • Index Cond: ((timeentryid = faas_1.timeentryid) AND (serialnumber >= faas_1.submission_serialnumber) AND (serialnumber <= faas_1.nextaction_serialnumber))
166. 0.000 0.000 ↓ 0.0

Index Scan using timeentry_pkey on timeentry te (cost=0.43..2.45 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = teah.timeentryid)
167. 0.000 0.000 ↓ 0.0

Index Scan using ixteahkvkey on timeentryapprovalhistorykeyvalue kv_ea_1 (cost=0.14..2.16 rows=1 width=48) (actual rows= loops=)

  • Index Cond: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
168. 0.000 0.000 ↓ 0.0

Index Scan using ixteahkvkey on timeentryapprovalhistorykeyvalue kv_aa_1 (cost=0.14..2.16 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
169. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_10 (cost=0.42..0.75 rows=7 width=28) (actual rows= loops=)

  • Index Cond: ((userid = te.userid) AND (te.entrydate >= startdate) AND (te.entrydate <= enddate))
170. 0.000 0.000 ↓ 0.0

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tsformat_2 (cost=0.43..3.05 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = ts_10.id)
  • Filter: (upper(key) = 'URN:REPLICON:POLICY:TIMESHEET:TIMESHEET-FORMAT'::text)
171. 0.000 0.000 ↓ 0.0

Index Only Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..1.48 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = "*SELECT* 1".timesheetid)
172. 0.000 0.000 ↓ 0.0

Index Scan using timesheet_pkey on timesheet ts (cost=0.42..0.45 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = tslist.timesheetid)