explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OfCt

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

Sort (cost=12,413,016.88..12,413,022.53 rows=2,261 width=564) (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".action, "*SELECT* 1".modifiedonutc, "*SELECT* 1".validuntilutc, "*SELECT* 1".entrydate
2. 0.000 0.000 ↓ 0.0

Nested Loop (cost=38.46..12,412,890.91 rows=2,261 width=564) (actual rows= loops=)

  • Join Filter: (tslist.timesheetid = ts.id)
3. 0.000 0.000 ↓ 0.0

Nested Loop (cost=38.04..12,411,897.64 rows=2,206 width=424) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop (cost=37.76..12,411,235.81 rows=2,206 width=401) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Hash Join (cost=37.34..12,410,246.26 rows=2,261 width=385) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".timesheetid = timesheet3.id)
6. 0.000 0.000 ↓ 0.0

Append (cost=1.11..12,374,602.83 rows=13,564,366 width=370) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=1.11..24.90 rows=1 width=362) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.11..24.89 rows=1 width=402) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.54..18.07 rows=1 width=112) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.12..12.45 rows=1 width=96) (actual rows= loops=)

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

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

  • Filter: ((fromtimestamputc >= '2020-01-01 06:00:00'::timestamp without time zone) AND (fromtimestamputc < '2020-08-01 05:00:00'::timestamp without time zone))
12. 0.000 0.000 ↓ 0.0

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

13. 0.000 0.000 ↓ 0.0

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

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

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tsformat (cost=0.56..6.81 rows=1 width=102) (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))
15. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.56..18.09 rows=1 width=308) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..18.08 rows=1 width=348) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

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

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

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

  • Filter: ((totimestamputc IS NOT NULL) AND (totimestamputc >= '2020-01-01 06:00:00'::timestamp without time zone) AND (totimestamputc < '2020-08-01 05:00:00'::timestamp without time zone))
19. 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)
20. 0.000 0.000 ↓ 0.0

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

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

Subquery Scan on *SELECT* 3 (cost=3,471.75..1,564,985.67 rows=2,082,287 width=368) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Gather (cost=3,471.75..1,544,162.80 rows=2,082,287 width=408) (actual rows= loops=)

  • Workers Planned: 1
23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,471.75..1,334,934.10 rows=1,224,875 width=408) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,471.06..680,011.09 rows=638,676 width=166) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,470.37..337,711.49 rows=333,808 width=104) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,469.68..25,455.16 rows=333,808 width=104) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,469.25..6,436.45 rows=1,365 width=28) (actual rows= loops=)

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

Parallel Seq Scan on timesheet ts_3 (cost=0.00..3,637.01 rows=120,001 width=28) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=2,440.43..2,440.43 rows=2,305 width=16) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,417.38..2,440.43 rows=2,305 width=16) (actual rows= loops=)

  • Group Key: timesheetpolicysettingssnapshot.timesheetid
31. 0.000 0.000 ↓ 0.0

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot (cost=0.69..2,411.58 rows=2,321 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))
32. 0.000 0.000 ↓ 0.0

Index Scan using ixter2userid on timeentryrevision rev (cost=0.43..11.38 rows=255 width=88) (actual rows= loops=)

  • Index Cond: ((userid = ts_3.userid) AND (entrydate >= ts_3.startdate) AND (entrydate <= ts_3.enddate))
  • Filter: ((fromtimestamputc >= '2020-01-01 06:00:00'::timestamp without time zone) AND (fromtimestamputc < '2020-08-01 05:00:00'::timestamp without time zone))
33. 0.000 0.000 ↓ 0.0

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

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

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser (cost=0.69..0.99 rows=4 width=78) (actual rows= loops=)

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

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser (cost=0.69..0.99 rows=4 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

Subquery Scan on *SELECT* 4 (cost=3,472.06..1,903,776.19 rows=3,689,956 width=368) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Gather (cost=3,472.06..1,866,876.63 rows=3,689,956 width=408) (actual rows= loops=)

  • Workers Planned: 1
38. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,472.06..1,496,881.03 rows=2,170,562 width=408) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,471.37..1,003,337.21 rows=542,256 width=166) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=2,470.68..879,746.47 rows=135,789 width=104) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,470.25..548,990.38 rows=696,330 width=120) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,469.68..24,185.71 rows=424,217 width=56) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,469.25..6,436.45 rows=1,365 width=28) (actual rows= loops=)

  • Hash Cond: (ts_4.id = timesheetpolicysettingssnapshot_1.timesheetid)
44. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timesheet ts_4 (cost=0.00..3,637.01 rows=120,001 width=28) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Hash (cost=2,440.43..2,440.43 rows=2,305 width=16) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,417.38..2,440.43 rows=2,305 width=16) (actual rows= loops=)

  • Group Key: timesheetpolicysettingssnapshot_1.timesheetid
47. 0.000 0.000 ↓ 0.0

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_1 (cost=0.69..2,411.58 rows=2,321 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))
48. 0.000 0.000 ↓ 0.0

Index Scan using ixter2userid on timeentryrevision rev_1 (cost=0.43..9.76 rows=324 width=40) (actual rows= loops=)

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

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision mdrev (cost=0.56..1.22 rows=2 width=80) (actual rows= loops=)

  • Index Cond: (timeentryid = rev_1.timeentryid)
  • Filter: ((fromtimestamputc >= '2020-01-01 06:00:00'::timestamp without time zone) AND (fromtimestamputc < '2020-08-01 05:00:00'::timestamp without time zone) AND (rev_1.fromtimestamputc <= fromtimestamputc) AND ((rev_1.totimestamputc IS NULL) OR (rev_1.totimestamputc > fromtimestamputc)))
50. 0.000 0.000 ↓ 0.0

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

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

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_1 (cost=0.69..0.87 rows=4 width=78) (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 uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_1 (cost=0.69..0.87 rows=4 width=78) (actual rows= loops=)

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

Subquery Scan on *SELECT* 5 (cost=4,473.31..3,416,511.64 rows=4,696,776 width=368) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=4,473.31..3,369,543.88 rows=4,696,776 width=408) (actual rows= loops=)

  • Join Filter: ((rev_1_1.timeentryid = mdrev_1.timeentryid) AND (rev_1_1.totimestamputc = mdrev_1.totimestamputc))
55. 0.000 0.000 ↓ 0.0

Gather (cost=3,472.19..2,220,405.74 rows=4,696,776 width=204) (actual rows= loops=)

  • Workers Planned: 1
56. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,472.19..1,749,728.14 rows=2,762,809 width=204) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,471.50..1,008,334.35 rows=690,212 width=158) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=2,470.81..822,678.70 rows=172,839 width=96) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,470.25..544,748.21 rows=264,049 width=156) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,469.68..24,185.71 rows=424,217 width=56) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,469.25..6,436.45 rows=1,365 width=28) (actual rows= loops=)

  • Hash Cond: (ts_5.id = timesheetpolicysettingssnapshot_2.timesheetid)
62. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timesheet ts_5 (cost=0.00..3,637.01 rows=120,001 width=28) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Hash (cost=2,440.43..2,440.43 rows=2,305 width=16) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,417.38..2,440.43 rows=2,305 width=16) (actual rows= loops=)

  • Group Key: timesheetpolicysettingssnapshot_2.timesheetid
65. 0.000 0.000 ↓ 0.0

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_2 (cost=0.69..2,411.58 rows=2,321 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))
66. 0.000 0.000 ↓ 0.0

Index Scan using ixter2userid on timeentryrevision rev_2 (cost=0.43..9.76 rows=324 width=40) (actual rows= loops=)

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

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision mdrev_1 (cost=0.56..1.22 rows=1 width=116) (actual rows= loops=)

  • Index Cond: (timeentryid = rev_2.timeentryid)
  • Filter: ((totimestamputc IS NOT NULL) AND (totimestamputc >= '2020-01-01 06:00:00'::timestamp without time zone) AND (totimestamputc < '2020-08-01 05:00:00'::timestamp without time zone) AND (rev_2.fromtimestamputc <= totimestamputc) AND ((rev_2.totimestamputc IS NULL) OR (rev_2.totimestamputc > totimestamputc)))
68. 0.000 0.000 ↓ 0.0

Index Scan using ixtemr2fromtimestamputc on timeentrymetadatarevision (cost=0.56..1.04 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))
69. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_2 (cost=0.69..1.03 rows=4 width=78) (actual rows= loops=)

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

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

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

Materialize (cost=1,001.12..1,066,944.56 rows=1 width=24) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Gather (cost=1,001.12..1,066,944.56 rows=1 width=24) (actual rows= loops=)

  • Workers Planned: 2
73. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1.12..1,065,944.46 rows=1 width=24) (actual rows= loops=)

  • Merge Cond: (rev_1_1.id = child_rev.previousrevisionid)
  • Filter: (child_rev.id IS NULL)
74. 0.000 0.000 ↓ 0.0

Parallel Index Scan using timeentryrevision_pkey on timeentryrevision rev_1_1 (cost=0.56..556,484.07 rows=6,918,841 width=40) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev (cost=0.56..675,269.84 rows=16,605,219 width=32) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 6 (cost=3,472.06..698,756.59 rows=374,888 width=368) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Gather (cost=3,472.06..695,007.71 rows=374,888 width=408) (actual rows= loops=)

  • Workers Planned: 1
78. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,472.06..656,518.91 rows=220,522 width=408) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,471.37..592,250.08 rows=64,962 width=166) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=2,470.68..573,272.75 rows=19,182 width=104) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,470.24..385,201.56 rows=387,874 width=120) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,469.68..24,185.71 rows=424,217 width=56) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,469.25..6,436.45 rows=1,365 width=28) (actual rows= loops=)

  • Hash Cond: (ts_6.id = timesheetpolicysettingssnapshot_3.timesheetid)
84. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timesheet ts_6 (cost=0.00..3,637.01 rows=120,001 width=28) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Hash (cost=2,440.43..2,440.43 rows=2,305 width=16) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,417.38..2,440.43 rows=2,305 width=16) (actual rows= loops=)

  • Group Key: timesheetpolicysettingssnapshot_3.timesheetid
87. 0.000 0.000 ↓ 0.0

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_3 (cost=0.69..2,411.58 rows=2,321 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))
88. 0.000 0.000 ↓ 0.0

Index Scan using ixter2userid on timeentryrevision rev_3 (cost=0.43..9.76 rows=324 width=40) (actual rows= loops=)

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

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision oefvr (cost=0.56..0.84 rows=1 width=80) (actual rows= loops=)

  • Index Cond: (objectid = rev_3.timeentryid)
  • Filter: ((fromtimestamputc >= '2020-01-01 06:00:00'::timestamp without time zone) AND (fromtimestamputc < '2020-08-01 05:00:00'::timestamp without time zone) AND (rev_3.fromtimestamputc <= fromtimestamputc) AND ((rev_3.totimestamputc IS NULL) OR (rev_3.totimestamputc > fromtimestamputc)))
90. 0.000 0.000 ↓ 0.0

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

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

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_3 (cost=0.69..0.95 rows=4 width=78) (actual rows= loops=)

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

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

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

Subquery Scan on *SELECT* 7 (cost=4,473.31..2,593,946.65 rows=2,433,499 width=368) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=4,473.31..2,569,611.66 rows=2,433,499 width=408) (actual rows= loops=)

  • Join Filter: ((rev_1_2.timeentryid = oefvr_1.objectid) AND (rev_1_2.totimestamputc = oefvr_1.totimestamputc))
95. 0.000 0.000 ↓ 0.0

Gather (cost=3,472.19..1,460,080.87 rows=2,433,499 width=204) (actual rows= loops=)

  • Workers Planned: 1
96. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,472.19..1,215,730.97 rows=1,431,470 width=204) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,471.50..675,064.20 rows=421,687 width=158) (actual rows= loops=)

98. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=2,470.81..515,415.78 rows=124,516 width=96) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,470.24..385,201.56 rows=147,320 width=128) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,469.68..24,185.71 rows=424,217 width=56) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,469.25..6,436.45 rows=1,365 width=28) (actual rows= loops=)

  • Hash Cond: (ts_7.id = timesheetpolicysettingssnapshot_4.timesheetid)
102. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timesheet ts_7 (cost=0.00..3,637.01 rows=120,001 width=28) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Hash (cost=2,440.43..2,440.43 rows=2,305 width=16) (actual rows= loops=)

104. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,417.38..2,440.43 rows=2,305 width=16) (actual rows= loops=)

  • Group Key: timesheetpolicysettingssnapshot_4.timesheetid
105. 0.000 0.000 ↓ 0.0

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot timesheetpolicysettingssnapshot_4 (cost=0.69..2,411.58 rows=2,321 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))
106. 0.000 0.000 ↓ 0.0

Index Scan using ixter2userid on timeentryrevision rev_4 (cost=0.43..9.76 rows=324 width=40) (actual rows= loops=)

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

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision oefvr_1 (cost=0.56..0.84 rows=1 width=88) (actual rows= loops=)

  • Index Cond: (objectid = rev_4.timeentryid)
  • Filter: ((totimestamputc IS NOT NULL) AND (totimestamputc >= '2020-01-01 06:00:00'::timestamp without time zone) AND (totimestamputc < '2020-08-01 05:00:00'::timestamp without time zone) AND (rev_4.fromtimestamputc <= totimestamputc) AND ((rev_4.totimestamputc IS NULL) OR (rev_4.totimestamputc > totimestamputc)))
108. 0.000 0.000 ↓ 0.0

Index Scan using ixoefvrobjectid on objectextensionfieldvaluerevision (cost=0.56..0.87 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))
109. 0.000 0.000 ↓ 0.0

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_4 (cost=0.69..1.24 rows=4 width=78) (actual rows= loops=)

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

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

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

Materialize (cost=1,001.12..1,066,944.56 rows=1 width=24) (actual rows= loops=)

112. 0.000 0.000 ↓ 0.0

Gather (cost=1,001.12..1,066,944.56 rows=1 width=24) (actual rows= loops=)

  • Workers Planned: 2
113. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1.12..1,065,944.46 rows=1 width=24) (actual rows= loops=)

  • Merge Cond: (rev_1_2.id = child_rev_1.previousrevisionid)
  • Filter: (child_rev_1.id IS NULL)
114. 0.000 0.000 ↓ 0.0

Parallel Index Scan using timeentryrevision_pkey on timeentryrevision rev_1_2 (cost=0.56..556,484.07 rows=6,918,841 width=40) (actual rows= loops=)

115. 0.000 0.000 ↓ 0.0

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev_1 (cost=0.56..675,269.84 rows=16,605,219 width=32) (actual rows= loops=)

116. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 8 (cost=1,003.61..1,060,536.20 rows=1 width=368) (actual rows= loops=)

117. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.61..1,060,536.19 rows=1 width=408) (actual rows= loops=)

118. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.92..1,060,534.94 rows=1 width=204) (actual rows= loops=)

119. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.23..1,060,533.61 rows=1 width=142) (actual rows= loops=)

120. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,001.54..1,060,532.28 rows=1 width=80) (actual rows= loops=)

121. 0.000 0.000 ↓ 0.0

Gather (cost=1,001.12..1,060,531.64 rows=1 width=64) (actual rows= loops=)

  • Workers Planned: 2
122. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1.12..1,059,531.54 rows=1 width=64) (actual rows= loops=)

  • Merge Cond: (rev_5.id = child_rev_2.previousrevisionid)
  • Filter: (child_rev_2.id IS NULL)
123. 0.000 0.000 ↓ 0.0

Parallel Index Scan using timeentryrevision_pkey on timeentryrevision rev_5 (cost=0.56..591,078.27 rows=2,240,555 width=64) (actual rows= loops=)

  • Filter: ((totimestamputc IS NOT NULL) AND (totimestamputc >= '2020-01-01 06:00:00'::timestamp without time zone) AND (totimestamputc < '2020-08-01 05:00:00'::timestamp without time zone))
124. 0.000 0.000 ↓ 0.0

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev_2 (cost=0.56..675,269.84 rows=16,605,219 width=32) (actual rows= loops=)

125. 0.000 0.000 ↓ 0.0

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

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

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

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

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_5 (cost=0.69..1.29 rows=4 width=78) (actual rows= loops=)

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

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

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

Subquery Scan on *SELECT* 9 (cost=670,131.62..1,136,046.90 rows=286,957 width=450) (actual rows= loops=)

130. 0.000 0.000 ↓ 0.0

Hash Join (cost=670,131.62..1,133,177.33 rows=286,957 width=490) (actual rows= loops=)

  • Hash Cond: (tah.timesheetid = ts_9.id)
131. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=463,495.93..902,478.23 rows=277,027 width=212) (actual rows= loops=)

  • Hash Cond: (kv_ea.timesheetapprovalhistoryid = tah.id)
132. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvkey on timesheetapprovalhistorykeyvalue kv_ea (cost=0.69..404,336.69 rows=792,272 width=89) (actual rows= loops=)

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

Hash (cost=454,634.38..454,634.38 rows=253,429 width=155) (actual rows= loops=)

134. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=134,049.25..454,634.38 rows=253,429 width=155) (actual rows= loops=)

  • Hash Cond: (kv_aa.timesheetapprovalhistoryid = tah.id)
135. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvkey on timesheetapprovalhistorykeyvalue kv_aa (cost=0.69..301,044.97 rows=503,554 width=89) (actual rows= loops=)

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

Hash (cost=127,415.70..127,415.70 rows=253,429 width=82) (actual rows= loops=)

137. 0.000 0.000 ↓ 0.0

Seq Scan on timesheetapprovalhistory tah (cost=96,654.09..127,415.70 rows=253,429 width=82) (actual rows= loops=)

  • Filter: ((NOT (hashed SubPlan 3)) AND (timestamputc >= '2020-01-01 06:00:00'::timestamp without time zone) AND (timestamputc < '2020-08-01 05:00:00'::timestamp without time zone) AND (COALESCE(upper(systemprocessidentifier), ''::text) <> 'URN:REPLICON:APPROVAL-SYSTEM-PROCESS:TIMESHEET-SUBMIT-SCRIPT-DATA-AND-VALIDATION'::text))
138.          

SubPlan (for Seq Scan)

139. 0.000 0.000 ↓ 0.0

Nested Loop (cost=96,045.51..96,654.08 rows=1 width=16) (actual rows= loops=)

140. 0.000 0.000 ↓ 0.0

Hash Join (cost=96,045.09..96,651.62 rows=1 width=24) (actual rows= loops=)

  • Hash Cond: ((faas.timesheetid = tah_2.timesheetid) AND (faas.nextaction_serialnumber = tah_2.serialnumber))
141.          

CTE firstactionaftersubmit

142. 0.000 0.000 ↓ 0.0

HashAggregate (cost=83,405.81..83,646.02 rows=24,021 width=24) (actual rows= loops=)

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

CTE submissions

144. 0.000 0.000 ↓ 0.0

Index Scan using ixtahaction on timesheetapprovalhistory (cost=0.42..17,610.80 rows=240,211 width=20) (actual rows= loops=)

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

Merge Join (cost=29,088.23..63,351.89 rows=325,750 width=24) (actual rows= loops=)

  • Merge Cond: (submissions.timesheetid = tah_3.timesheetid)
  • Join Filter: (tah_3.serialnumber > submissions.serialnumber)
146. 0.000 0.000 ↓ 0.0

Sort (cost=29,087.81..29,688.34 rows=240,211 width=20) (actual rows= loops=)

  • Sort Key: submissions.timesheetid
147. 0.000 0.000 ↓ 0.0

CTE Scan on submissions (cost=0.00..4,804.22 rows=240,211 width=20) (actual rows= loops=)

148. 0.000 0.000 ↓ 0.0

Materialize (cost=0.42..17,773.13 rows=724,783 width=20) (actual rows= loops=)

149. 0.000 0.000 ↓ 0.0

Index Only Scan using ixtah2timesheetid on timesheetapprovalhistory tah_3 (cost=0.42..15,961.17 rows=724,783 width=20) (actual rows= loops=)

150. 0.000 0.000 ↓ 0.0

CTE Scan on firstactionaftersubmit faas (cost=0.00..480.42 rows=24,021 width=24) (actual rows= loops=)

151. 0.000 0.000 ↓ 0.0

Hash (cost=12,396.89..12,396.89 rows=145 width=20) (actual rows= loops=)

152. 0.000 0.000 ↓ 0.0

Index Scan using ixtahaction on timesheetapprovalhistory tah_2 (cost=0.42..12,396.89 rows=145 width=20) (actual rows= loops=)

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

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

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

Hash (cost=200,073.27..200,073.27 rows=211,314 width=122) (actual rows= loops=)

155. 0.000 0.000 ↓ 0.0

Hash Join (cost=8,223.73..200,073.27 rows=211,314 width=122) (actual rows= loops=)

  • Hash Cond: (tsformat_1.timesheetid = ts_9.id)
156. 0.000 0.000 ↓ 0.0

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot tsformat_1 (cost=0.69..183,495.51 rows=211,314 width=102) (actual rows= loops=)

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

Hash (cost=4,477.02..4,477.02 rows=204,002 width=20) (actual rows= loops=)

158. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts_9 (cost=0.00..4,477.02 rows=204,002 width=20) (actual rows= loops=)

159. 0.000 0.000 ↓ 0.0

Hash (cost=35.81..35.81 rows=34 width=16) (actual rows= loops=)

160. 0.000 0.000 ↓ 0.0

Index Scan using uix2tsuseridstartdate on timesheet timesheet3 (cost=0.42..35.81 rows=34 width=16) (actual rows= loops=)

  • Index Cond: (userid = 4,507)
161. 0.000 0.000 ↓ 0.0

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

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

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..0.30 rows=1 width=31) (actual rows= loops=)

  • Index Cond: (id = "*SELECT* 1".userid)
163. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = "*SELECT* 1".timesheetid)