explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cPV

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

Sort (cost=11,368,861.70..11,382,906.09 rows=5,617,756 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

Hash Join (cost=2,921.62..9,927,125.37 rows=5,617,756 width=564) (actual rows= loops=)

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

Hash Join (cost=2,799.12..9,912,184.16 rows=5,617,756 width=358) (actual rows= loops=)

  • Hash Cond: (tslist.timesheetid = ts.id)
4. 0.000 0.000 ↓ 0.0

Hash Join (cost=2,079.48..9,896,715.68 rows=5,617,756 width=382) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".timesheetid = tslist.timesheetid)
5. 0.000 0.000 ↓ 0.0

Append (cost=0.84..9,879,888.19 rows=5,617,756 width=366) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=0.84..20.59 rows=1 width=355) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..20.58 rows=1 width=395) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.41..16.88 rows=1 width=112) (actual rows= loops=)

9. 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)
10. 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-09-04 05:00:00'::timestamp without time zone))
11. 0.000 0.000 ↓ 0.0

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

12. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_1 (cost=0.29..4.40 rows=3 width=28) (actual rows= loops=)

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

Index Scan using ixtpsstimesheetid on timesheetpolicysettingssnapshot tsformat (cost=0.43..3.69 rows=1 width=95) (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))
14. 0.000 0.000 ↓ 0.0

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

15. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..16.90 rows=1 width=348) (actual rows= loops=)

16. 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)
17. 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-09-04 05:00:00'::timestamp without time zone))
18. 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)
19. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_2 (cost=0.29..4.40 rows=3 width=28) (actual rows= loops=)

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

Subquery Scan on *SELECT* 3 (cost=419,644.38..1,663,058.73 rows=765,938 width=366) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Gather (cost=419,644.38..1,655,399.35 rows=765,938 width=406) (actual rows= loops=)

  • Workers Planned: 2
22. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=418,644.38..1,577,805.55 rows=319,141 width=406) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=418,643.69..1,327,668.78 rows=296,822 width=165) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=418,643.00..1,093,477.90 rows=277,900 width=104) (actual rows= loops=)

  • Merge Cond: (rev.openingauditid = changereason.revisionauditid)
25. 0.000 0.000 ↓ 0.0

Sort (cost=418,642.32..419,337.07 rows=277,900 width=104) (actual rows= loops=)

  • Sort Key: rev.openingauditid
26. 0.000 0.000 ↓ 0.0

Hash Join (cost=819.68..393,514.31 rows=277,900 width=104) (actual rows= loops=)

  • Hash Cond: (rev.userid = ts_3.userid)
  • Join Filter: ((rev.entrydate >= ts_3.startdate) AND (rev.entrydate <= ts_3.enddate))
27. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timeentryrevision rev (cost=0.00..335,773.46 rows=5,174,564 width=88) (actual rows= loops=)

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

Hash (cost=815.02..815.02 rows=373 width=28) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=388.64..815.02 rows=373 width=28) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

HashAggregate (cost=388.35..392.05 rows=370 width=16) (actual rows= loops=)

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

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot (cost=0.55..387.42 rows=373 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 timesheet_pkey on timesheet ts_3 (cost=0.29..1.16 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (id = timesheetpolicysettingssnapshot.timesheetid)
33. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (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.82 rows=2 width=77) (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.82 rows=2 width=77) (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=1,822.05..1,543,584.87 rows=210,601 width=366) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Gather (cost=1,822.05..1,541,478.86 rows=210,601 width=406) (actual rows= loops=)

  • Workers Planned: 2
38. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=822.05..1,519,418.76 rows=87,750 width=406) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=821.36..1,485,265.78 rows=44,181 width=165) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=820.67..1,467,955.45 rows=22,393 width=104) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Nested Loop (cost=820.24..910,032.72 rows=1,197,243 width=120) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash Join (cost=819.68..367,641.49 rows=277,900 width=56) (actual rows= loops=)

  • Hash Cond: (rev_1.userid = ts_4.userid)
  • Join Filter: ((rev_1.entrydate >= ts_4.startdate) AND (rev_1.entrydate <= ts_4.enddate))
43. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timeentryrevision rev_1 (cost=0.00..309,900.64 rows=5,174,564 width=40) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash (cost=815.02..815.02 rows=373 width=28) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Nested Loop (cost=388.64..815.02 rows=373 width=28) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

HashAggregate (cost=388.35..392.05 rows=370 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.55..387.42 rows=373 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 timesheet_pkey on timesheet ts_4 (cost=0.29..1.16 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (id = timesheetpolicysettingssnapshot_1.timesheetid)
49. 0.000 0.000 ↓ 0.0

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision mdrev (cost=0.56..1.90 rows=5 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-09-04 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.75 rows=2 width=77) (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.75 rows=2 width=77) (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=2,823.30..4,650,168.30 rows=4,508,429 width=366) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=2,823.30..4,605,084.01 rows=4,508,429 width=406) (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=1,822.18..3,617,979.23 rows=4,508,429 width=202) (actual rows= loops=)

  • Workers Planned: 2
56. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=822.18..3,166,136.33 rows=1,878,512 width=202) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=821.50..2,414,676.09 rows=945,807 width=157) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=820.81..2,033,808.23 rows=479,370 width=96) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Nested Loop (cost=820.24..904,474.72 rows=649,943 width=158) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Hash Join (cost=819.68..367,641.49 rows=277,900 width=56) (actual rows= loops=)

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

Parallel Seq Scan on timeentryrevision rev_2 (cost=0.00..309,900.64 rows=5,174,564 width=40) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Hash (cost=815.02..815.02 rows=373 width=28) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Nested Loop (cost=388.64..815.02 rows=373 width=28) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

HashAggregate (cost=388.35..392.05 rows=370 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.55..387.42 rows=373 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 timesheet_pkey on timesheet ts_5 (cost=0.29..1.16 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (id = timesheetpolicysettingssnapshot_2.timesheetid)
67. 0.000 0.000 ↓ 0.0

Index Scan using ixtemrtimeentryid on timeentrymetadatarevision mdrev_1 (cost=0.56..1.90 rows=3 width=118) (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-09-04 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 ixtemrtimeentryid on timeentrymetadatarevision (cost=0.56..1.73 rows=1 width=86) (actual rows= loops=)

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

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_2 (cost=0.69..0.77 rows=2 width=77) (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..0.77 rows=2 width=77) (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..908,207.27 rows=1 width=24) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Gather (cost=1,001.12..908,207.27 rows=1 width=24) (actual rows= loops=)

  • Workers Planned: 2
73. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1.12..907,207.17 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..441,261.96 rows=5,174,564 width=40) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev (cost=0.56..550,440.86 rows=12,418,953 width=32) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 6 (cost=38,461.17..400,518.03 rows=1 width=366) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=38,461.17..400,518.02 rows=1 width=406) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=38,460.48..400,517.11 rows=1 width=165) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Gather (cost=38,459.79..400,516.20 rows=1 width=104) (actual rows= loops=)

  • Workers Planned: 2
80. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=37,459.79..399,516.10 rows=1 width=104) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Hash Join (cost=37,459.36..308,276.06 rows=182,817 width=120) (actual rows= loops=)

  • Hash Cond: (oefvr.objectid = rev_3.timeentryid)
  • Join Filter: ((rev_3.fromtimestamputc <= oefvr.fromtimestamputc) AND ((rev_3.totimestamputc IS NULL) OR (rev_3.totimestamputc > oefvr.fromtimestamputc)))
82. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on objectextensionfieldvaluerevision oefvr (cost=0.00..195,108.02 rows=2,966,868 width=80) (actual rows= loops=)

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

Hash (cost=29,122.35..29,122.35 rows=666,961 width=56) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Nested Loop (cost=389.20..29,122.35 rows=666,961 width=56) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Nested Loop (cost=388.64..815.02 rows=373 width=28) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

HashAggregate (cost=388.35..392.05 rows=370 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.55..387.42 rows=373 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 timesheet_pkey on timesheet ts_6 (cost=0.29..1.16 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (id = timesheetpolicysettingssnapshot_3.timesheetid)
89. 0.000 0.000 ↓ 0.0

Index Scan using ixter2userid on timeentryrevision rev_3 (cost=0.56..58.66 rows=1,723 width=40) (actual rows= loops=)

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

Index Scan using timeentryrevision_singleinitialrevision on timeentryrevision timeentryrevision_1 (cost=0.43..0.49 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 effectiveuser_3 (cost=0.69..0.89 rows=2 width=77) (actual rows= loops=)

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

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_3 (cost=0.69..0.89 rows=2 width=77) (actual rows= loops=)

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

Subquery Scan on *SELECT* 7 (cost=38,462.42..625,869.21 rows=100,762 width=366) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Gather (cost=38,462.42..624,861.59 rows=100,762 width=406) (actual rows= loops=)

  • Workers Planned: 2
95. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=37,462.42..613,785.39 rows=41,984 width=406) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=37,461.30..555,956.83 rows=41,984 width=202) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=37,460.61..536,409.08 rows=21,140 width=157) (actual rows= loops=)

98. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=37,459.92..526,501.13 rows=10,715 width=96) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

Hash Join (cost=37,459.36..303,527.85 rows=171,351 width=128) (actual rows= loops=)

  • Hash Cond: (oefvr_1.objectid = rev_4.timeentryid)
  • Join Filter: ((rev_4.fromtimestamputc <= oefvr_1.totimestamputc) AND ((rev_4.totimestamputc IS NULL) OR (rev_4.totimestamputc > oefvr_1.totimestamputc)))
100. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on objectextensionfieldvaluerevision oefvr_1 (cost=0.00..195,108.02 rows=2,780,796 width=88) (actual rows= loops=)

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

Hash (cost=29,122.35..29,122.35 rows=666,961 width=56) (actual rows= loops=)

102. 0.000 0.000 ↓ 0.0

Nested Loop (cost=389.20..29,122.35 rows=666,961 width=56) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Nested Loop (cost=388.64..815.02 rows=373 width=28) (actual rows= loops=)

104. 0.000 0.000 ↓ 0.0

HashAggregate (cost=388.35..392.05 rows=370 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.55..387.42 rows=373 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 timesheet_pkey on timesheet ts_7 (cost=0.29..1.16 rows=1 width=28) (actual rows= loops=)

  • Index Cond: (id = timesheetpolicysettingssnapshot_4.timesheetid)
107. 0.000 0.000 ↓ 0.0

Index Scan using ixter2userid on timeentryrevision rev_4 (cost=0.56..58.66 rows=1,723 width=40) (actual rows= loops=)

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

Index Scan using ixoefvr2fromtimestamputc on objectextensionfieldvaluerevision (cost=0.56..1.29 rows=1 width=56) (actual rows= loops=)

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

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_4 (cost=0.69..0.90 rows=2 width=77) (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..0.90 rows=2 width=77) (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

Nested Loop Left Join (cost=1.12..1.37 rows=1 width=24) (actual rows= loops=)

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

Index Scan using ixtertimeentryid on timeentryrevision rev_1_2 (cost=0.56..0.75 rows=1 width=40) (actual rows= loops=)

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

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

  • Index Cond: (previousrevisionid = rev_1_2.id)
114. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 8 (cost=1,003.47..911,931.72 rows=1 width=366) (actual rows= loops=)

115. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,003.47..911,931.71 rows=1 width=406) (actual rows= loops=)

116. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.78..911,930.82 rows=1 width=202) (actual rows= loops=)

117. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,002.10..911,929.90 rows=1 width=141) (actual rows= loops=)

118. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,001.41..911,928.99 rows=1 width=80) (actual rows= loops=)

119. 0.000 0.000 ↓ 0.0

Gather (cost=1,001.12..911,928.53 rows=1 width=64) (actual rows= loops=)

  • Workers Planned: 2
120. 0.000 0.000 ↓ 0.0

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

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

Parallel Index Scan using timeentryrevision_pkey on timeentryrevision rev_5 (cost=0.56..467,134.78 rows=2,880,967 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-09-04 05:00:00'::timestamp without time zone))
122. 0.000 0.000 ↓ 0.0

Index Scan using timeentryrevision_uniquepreviousrevisionid on timeentryrevision child_rev_2 (cost=0.56..550,440.86 rows=12,418,953 width=32) (actual rows= loops=)

123. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts_8 (cost=0.29..0.43 rows=3 width=28) (actual rows= loops=)

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

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue effectiveuser_5 (cost=0.69..0.90 rows=2 width=77) (actual rows= loops=)

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

Index Scan using uix4rakv_uniquekeyuri on revisionauditkeyvalue actualuser_5 (cost=0.69..0.90 rows=2 width=77) (actual rows= loops=)

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

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

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

Subquery Scan on *SELECT* 9 (cost=52,371.49..84,719.83 rows=32,022 width=440) (actual rows= loops=)

128. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=52,371.49..84,399.61 rows=32,022 width=480) (actual rows= loops=)

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

Index Scan using ixtahkvkey on timesheetapprovalhistorykeyvalue kv_ea (cost=0.55..31,471.29 rows=64,195 width=88) (actual rows= loops=)

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

Hash (cost=51,970.67..51,970.67 rows=32,022 width=236) (actual rows= loops=)

131. 0.000 0.000 ↓ 0.0

Hash Join (cost=27,050.77..51,970.67 rows=32,022 width=236) (actual rows= loops=)

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

Hash Right Join (cost=9,674.57..34,153.15 rows=32,291 width=153) (actual rows= loops=)

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

Index Scan using ixtahkvkey on timesheetapprovalhistorykeyvalue kv_aa (cost=0.55..24,367.00 rows=42,714 width=88) (actual rows= loops=)

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

Hash (cost=9,270.38..9,270.38 rows=32,291 width=81) (actual rows= loops=)

135. 0.000 0.000 ↓ 0.0

Seq Scan on timesheetapprovalhistory tah (cost=6,702.70..9,270.38 rows=32,291 width=81) (actual rows= loops=)

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

SubPlan (for Seq Scan)

137. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6,644.37..6,702.70 rows=1 width=16) (actual rows= loops=)

138. 0.000 0.000 ↓ 0.0

Hash Join (cost=6,643.96..6,700.24 rows=1 width=24) (actual rows= loops=)

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

CTE firstactionaftersubmit

140. 0.000 0.000 ↓ 0.0

HashAggregate (cost=6,031.27..6,053.56 rows=2,229 width=24) (actual rows= loops=)

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

CTE submissions

142. 0.000 0.000 ↓ 0.0

Index Scan using ixtahaction on timesheetapprovalhistory (cost=0.29..1,396.01 rows=22,293 width=20) (actual rows= loops=)

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

Hash Join (cost=2,555.69..4,455.86 rows=23,920 width=24) (actual rows= loops=)

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

CTE Scan on submissions (cost=0.00..445.86 rows=22,293 width=20) (actual rows= loops=)

145. 0.000 0.000 ↓ 0.0

Hash (cost=1,744.20..1,744.20 rows=64,919 width=20) (actual rows= loops=)

146. 0.000 0.000 ↓ 0.0

Index Only Scan using ixtah2timesheetid on timesheetapprovalhistory tah_3 (cost=0.41..1,744.20 rows=64,919 width=20) (actual rows= loops=)

147. 0.000 0.000 ↓ 0.0

CTE Scan on firstactionaftersubmit faas (cost=0.00..44.58 rows=2,229 width=24) (actual rows= loops=)

148. 0.000 0.000 ↓ 0.0

Hash (cost=590.31..590.31 rows=6 width=20) (actual rows= loops=)

149. 0.000 0.000 ↓ 0.0

Index Scan using ixtahaction on timesheetapprovalhistory tah_2 (cost=0.29..590.31 rows=6 width=20) (actual rows= loops=)

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

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

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

Hash (cost=17,115.82..17,115.82 rows=20,831 width=115) (actual rows= loops=)

152. 0.000 0.000 ↓ 0.0

Hash Join (cost=720.19..17,115.82 rows=20,831 width=115) (actual rows= loops=)

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

Index Scan using ixtspss2key on timesheetpolicysettingssnapshot tsformat_1 (cost=0.55..16,341.49 rows=20,831 width=95) (actual rows= loops=)

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

Hash (cost=457.06..457.06 rows=21,006 width=20) (actual rows= loops=)

155. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts_9 (cost=0.00..457.06 rows=21,006 width=20) (actual rows= loops=)

156. 0.000 0.000 ↓ 0.0

Hash (cost=1,728.99..1,728.99 rows=27,972 width=16) (actual rows= loops=)

157. 0.000 0.000 ↓ 0.0

Index Only Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.41..1,728.99 rows=27,972 width=16) (actual rows= loops=)

158. 0.000 0.000 ↓ 0.0

Hash (cost=457.06..457.06 rows=21,006 width=24) (actual rows= loops=)

159. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts (cost=0.00..457.06 rows=21,006 width=24) (actual rows= loops=)

160. 0.000 0.000 ↓ 0.0

Hash (cost=111.67..111.67 rows=867 width=32) (actual rows= loops=)

161. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..111.67 rows=867 width=32) (actual rows= loops=)