explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bU8g

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

Sort (cost=319,520.97..319,535.54 rows=5,827 width=6,990) (actual rows= loops=)

  • Sort Key: ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", ui.duplicatename, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", (CASE WHEN ui.disabled THEN 0 ELSE 1 END), ((ui.email)::character varying(255)) COLLATE "en_US", ((ui.externalid)::character varying(255)) COLLATE "en_US", ((employeetype2.name)::character varying(50)) COLLATE "en_US", at.entrydate, ts_1.startdate, ts_1.enddate, ((currencyinfo6.symbol)::character varying(50)) COLLATE "en_US", ubrh.hourlyrate, ((currencyinfo8.symbol)::character varying(50)) COLLATE "en_US", ((ubrh.hourlyrate * exchangerate.exchangevalue)), ((at.comments)::text) COLLATE "en_US", ((timeoffcode9.name)::character varying(255)) COLLATE "en_US", ((userinfo10.displayname)::text) COLLATE "en_US", ta.timestamputc, tslist.timesheetstatus, tah.timestamputc, ((tah.approvalcomments)::text) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((cl.code)::character varying(50)) COLLATE "en_US", ((pj.description)::character varying(255)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((pj.code)::character varying(50)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US", (CASE WHEN tdh.effectivelyenabled THEN 0 ELSE 1 END), ((tk.description)::character varying(255)) COLLATE "en_US", pj.timeandexpenseentrytype, ((br.name)::character varying(50)) COLLATE "en_US", ((dep.name)::character varying(255)) COLLATE "en_US", ((dep.code)::character varying(50)) COLLATE "en_US", ((location20.name)::character varying(100)) COLLATE "en_US
2.          

Initplan (for Sort)

3. 0.000 0.000 ↓ 0.0

Seq Scan on systeminformation (cost=0.00..1.01 rows=1 width=4) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Seq Scan on systeminformation systeminformation_1 (cost=0.00..1.01 rows=1 width=4) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Seq Scan on systeminformation systeminformation_2 (cost=0.00..1.01 rows=1 width=4) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Seq Scan on projectsysteminformation (cost=0.00..16.50 rows=650 width=16) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Seq Scan on projectsysteminformation projectsysteminformation_1 (cost=0.00..16.50 rows=650 width=16) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=15,741.96..319,120.51 rows=5,827 width=6,990) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Hash Join (cost=15,741.55..316,333.10 rows=5,827 width=2,131) (actual rows= loops=)

  • Hash Cond: (ts.userid = du.userid)
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=15,606.17..316,116.34 rows=6,058 width=2,105) (actual rows= loops=)

  • Hash Cond: (at.taskid = tk.id)
11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=14,247.93..314,678.26 rows=6,058 width=1,563) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=14,246.07..300,857.57 rows=6,058 width=1,533) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,245.79..299,004.14 rows=6,058 width=962) (actual rows= loops=)

  • Hash Cond: ((CASE WHEN (pj_1.clientbillingallocationmethod = 0) THEN pc.clientid ELSE at.userspecifiedclientid END) = cl.id)
14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=14,226.52..298,968.76 rows=6,058 width=922) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=14,226.09..284,955.43 rows=6,058 width=861) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=14,225.66..271,002.68 rows=6,058 width=784) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=14,224.40..262,023.20 rows=6,058 width=776) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=14,223.57..249,431.51 rows=6,058 width=715) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,223.28..247,036.39 rows=6,058 width=703) (actual rows= loops=)

  • Hash Cond: (ui.id = userhierarchy21.userid)
20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,167.82..246,910.16 rows=6,058 width=699) (actual rows= loops=)

  • Hash Cond: (at.timeoffcodeid = timeoffcode9.id)
21. 0.000 0.000 ↓ 0.0

Hash Join (cost=14,165.72..246,892.10 rows=6,058 width=683) (actual rows= loops=)

  • Hash Cond: (ts.userid = ubrh.userid)
22. 0.000 0.000 ↓ 0.0

Hash Join (cost=13,997.08..245,503.73 rows=309,004 width=420) (actual rows= loops=)

  • Hash Cond: (ts.id = ts_1.id)
23. 0.000 0.000 ↓ 0.0

Hash Join (cost=11,859.55..242,555.01 rows=309,004 width=428) (actual rows= loops=)

  • Hash Cond: (ui.employeetypeid = employeetype2.id)
24. 0.000 0.000 ↓ 0.0

Hash Join (cost=11,858.22..241,513.37 rows=309,004 width=314) (actual rows= loops=)

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

Hash Join (cost=5,761.72..234,603.41 rows=309,868 width=294) (actual rows= loops=)

  • Hash Cond: (ts.userid = ui.id)
26. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,552.87..233,575.03 rows=311,906 width=223) (actual rows= loops=)

  • Hash Cond: (ts.userid = login.userid)
27. 0.000 0.000 ↓ 0.0

Hash Join (cost=5,363.70..232,540.43 rows=321,758 width=200) (actual rows= loops=)

  • Hash Cond: (at.timesheetid = ts.id)
28. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,226.18..226,309.35 rows=324,238 width=628) (actual rows= loops=)

  • Hash Cond: (at.projectid = pj_1.id)
  • Join Filter: ((at.entrydate >= pc.effectivedate) AND (at.entrydate <= pc.enddate))
29. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,213.74..47,491.67 rows=324,238 width=133) (actual rows= loops=)

  • Hash Cond: ((at.projectid = pj_2.id) AND (at.userspecifiedclientid = pc_1.clientid))
  • Join Filter: ((at.entrydate >= pc_1.effectivedate) AND (at.entrydate <= pc_1.enddate))
30. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..43,846.13 rows=324,238 width=133) (actual rows= loops=)

  • Filter: ((entrydate >= '2019-12-01'::date) AND (entrydate <= '2020-07-31'::date))
31. 0.000 0.000 ↓ 0.0

Hash (cost=1,213.72..1,213.72 rows=1 width=16) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.29..1,213.72 rows=1 width=16) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_2 (cost=0.00..1,212.41 rows=1 width=4) (actual rows= loops=)

  • Filter: (clientbillingallocationmethod = 1)
34. 0.000 0.000 ↓ 0.0

Index Only Scan using uix4pc_projectclienteffectiveend on projectclient pc_1 (cost=0.29..1.30 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (projectid = pj_2.id)
35. 0.000 0.000 ↓ 0.0

Hash (cost=1,809.45..1,809.45 rows=16,239 width=52) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,416.82..1,809.45 rows=16,239 width=52) (actual rows= loops=)

  • Hash Cond: (pc.projectid = pj_1.id)
37. 0.000 0.000 ↓ 0.0

Seq Scan on projectclient pc (cost=0.00..349.99 rows=16,239 width=48) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash (cost=1,212.41..1,212.41 rows=16,353 width=8) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_1 (cost=0.00..1,212.41 rows=16,353 width=8) (actual rows= loops=)

  • Filter: (clientbillingallocationmethod = 0)
40. 0.000 0.000 ↓ 0.0

Hash (cost=1,335.01..1,335.01 rows=64,201 width=20) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts (cost=0.00..1,335.01 rows=64,201 width=20) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash (cost=135.74..135.74 rows=4,274 width=23) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on login (cost=0.00..135.74 rows=4,274 width=23) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash (cost=153.38..153.38 rows=4,438 width=71) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..153.38 rows=4,438 width=71) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash (cost=5,296.22..5,296.22 rows=64,022 width=20) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on dm_timesheetlist_facts tslist (cost=0.00..5,296.22 rows=64,022 width=20) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Hash (cost=1.15..1.15 rows=15 width=122) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Seq Scan on employeetype employeetype2 (cost=0.00..1.15 rows=15 width=122) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Hash (cost=1,335.01..1,335.01 rows=64,201 width=24) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts_1 (cost=0.00..1,335.01 rows=64,201 width=24) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Hash (cost=167.55..167.55 rows=87 width=263) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Hash Join (cost=3.34..167.55 rows=87 width=263) (actual rows= loops=)

  • Hash Cond: (ubrh.currencyid = currencyinfo6.id)
54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.19..166.03 rows=87 width=153) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Seq Scan on currencyinfo currencyinfo8 (cost=0.00..1.09 rows=1 width=118) (actual rows= loops=)

  • Filter: ($1 = id)
56. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.19..164.08 rows=87 width=35) (actual rows= loops=)

  • Hash Cond: (ubrh.currencyid = exchangerate.fixedcurrencyid)
57. 0.000 0.000 ↓ 0.0

Seq Scan on userbillingratehistory ubrh (cost=0.00..144.98 rows=4,279 width=11) (actual rows= loops=)

  • Filter: ((('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))
58. 0.000 0.000 ↓ 0.0

Hash (cost=2.17..2.17 rows=1 width=24) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.14..2.17 rows=1 width=24) (actual rows= loops=)

  • Index Cond: ((variablecurrencyid = $0) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
60. 0.000 0.000 ↓ 0.0

Hash (cost=1.07..1.07 rows=7 width=122) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on currencyinfo currencyinfo6 (cost=0.00..1.07 rows=7 width=122) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Hash (cost=1.49..1.49 rows=49 width=24) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffcode timeoffcode9 (cost=0.00..1.49 rows=49 width=24) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Hash (cost=39.22..39.22 rows=1,300 width=8) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Seq Scan on userhierarchy userhierarchy21 (cost=0.00..39.22 rows=1,300 width=8) (actual rows= loops=)

  • Filter: (('2020-06-23'::date >= startdate) AND ('2020-06-23'::date <= enddate))
66. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo userinfo10 (cost=0.28..0.40 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (userhierarchy21.supervisorid = id)
67. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.84..2.07 rows=1 width=77) (actual rows= loops=)

  • Filter: ((tahls_1.timesheetid IS NULL) OR (tah.serialnumber > tahls_1.lastsubmitserialnumber))
68. 0.000 0.000 ↓ 0.0

Index Scan using ixtah2timesheetid on timesheetapprovalhistory tah (cost=0.42..0.72 rows=3 width=81) (actual rows= loops=)

  • Index Cond: (ts_1.id = timesheetid)
  • Filter: (((systemprocessidentifier IS NULL) OR (systemprocessidentifier <> 'urn:replicon:approval-system-process:timesheet-submit-script-data-and-validation'::text)) AND (action = ANY ('{2,3,6}'::integer[])))
69. 0.000 0.000 ↓ 0.0

Index Scan using timesheetapprovalhistorylastsubmit_pkey on timesheetapprovalhistorylastsubmit tahls_1 (cost=0.41..0.44 rows=1 width=20) (actual rows= loops=)

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

Nested Loop (cost=1.25..1.47 rows=1 width=24) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..1.02 rows=1 width=24) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Index Scan using timesheetapprovalhistorylastsubmit_pkey on timesheetapprovalhistorylastsubmit tahls (cost=0.41..0.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (ts_1.id = timesheetid)
73. 0.000 0.000 ↓ 0.0

Index Scan using uixtahsn on timesheetapprovalhistory ta (cost=0.42..0.58 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (serialnumber = tahls.lastsubmitserialnumber)
74. 0.000 0.000 ↓ 0.0

Index Scan using timesheet_pkey on timesheet ts_2 (cost=0.41..0.45 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = tahls.timesheetid)
  • Filter: (approvalstatus = ANY ('{1,2}'::integer[]))
75. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue13 (cost=0.43..2.29 rows=1 width=93) (actual rows= loops=)

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

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue14 (cost=0.43..2.29 rows=2 width=93) (actual rows= loops=)

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

Hash (cost=15.23..15.23 rows=323 width=40) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Seq Scan on clients cl (cost=0.00..15.23 rows=323 width=40) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj (cost=0.29..0.31 rows=1 width=575) (actual rows= loops=)

  • Index Cond: (at.projectid = id)
80. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.85..2.23 rows=5 width=46) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.56..1.81 rows=1 width=54) (actual rows= loops=)

  • Join Filter: ((dm_attendancetimeallocation_facts.entrydate >= pbrh.effectivedate) AND (dm_attendancetimeallocation_facts.entrydate <= pbrh.enddate))
82. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.13..1.33 rows=1 width=70) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.70..0.83 rows=1 width=90) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Index Scan using dm_attendancetimeallocation_facts_pkey on dm_attendancetimeallocation_facts (cost=0.43..0.54 rows=1 width=44) (actual rows= loops=)

  • Index Cond: (at.id = id)
85. 0.000 0.000 ↓ 0.0

Index Scan using billingrate_pkey on billingrate br (cost=0.28..0.29 rows=1 width=46) (actual rows= loops=)

  • Index Cond: (id = dm_attendancetimeallocation_facts.billingrateid)
86. 0.000 0.000 ↓ 0.0

Index Scan using ixpbr2projectid on projectbillingrate pbr (cost=0.43..0.49 rows=1 width=40) (actual rows= loops=)

  • Index Cond: ((projectid = dm_attendancetimeallocation_facts.projectid) AND (billingrateid = dm_attendancetimeallocation_facts.billingrateid))
  • Filter: (((billingrateid = $3) AND (dm_attendancetimeallocation_facts.userid = userid)) OR ((billingrateid <> $4) AND (userid IS NULL)))
87. 0.000 0.000 ↓ 0.0

Index Scan using ixpbrhprojectbillingrateid on projectbillingratehistory pbrh (cost=0.43..0.46 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (projectbillingrateid = pbr.id)
88. 0.000 0.000 ↓ 0.0

Index Scan using uix2tsuseridstartdate on timesheet dmvts (cost=0.29..0.40 rows=2 width=12) (actual rows= loops=)

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

Hash (cost=1,154.57..1,154.57 rows=16,294 width=546) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=573.80..1,154.57 rows=16,294 width=546) (actual rows= loops=)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate_1.fixedcurrencyid)
91. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=571.62..1,091.27 rows=16,294 width=550) (actual rows= loops=)

  • Hash Cond: (tdh.taskid = tk.id)
92. 0.000 0.000 ↓ 0.0

Seq Scan on taskdenormalizedhierarchy tdh (cost=0.00..476.89 rows=16,289 width=5) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Hash (cost=367.94..367.94 rows=16,294 width=546) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Seq Scan on task tk (cost=0.00..367.94 rows=16,294 width=546) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Hash (cost=2.17..2.17 rows=1 width=4) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Index Scan using uix3er_currencyideffectivedate on exchangerate exchangerate_1 (cost=0.14..2.17 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((variablecurrencyid = $2) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
97. 0.000 0.000 ↓ 0.0

Hash (cost=82.01..82.01 rows=4,269 width=38) (actual rows= loops=)

98. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.28..82.01 rows=4,269 width=38) (actual rows= loops=)

  • Hash Cond: (du.departmentid = dep.id)
99. 0.000 0.000 ↓ 0.0

Seq Scan on departmentusers du (cost=0.00..67.69 rows=4,269 width=8) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Hash (cost=1.57..1.57 rows=57 width=38) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

Seq Scan on departments dep (cost=0.00..1.57 rows=57 width=38) (actual rows= loops=)

102. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.41..0.47 rows=1 width=222) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Index Only Scan using ix4ul_userlocationstartend on userlocation userlocation22 (cost=0.28..0.31 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ((userid = ui.id) AND (startdate <= '2020-06-23'::date) AND (enddate >= '2020-06-23'::date))
104. 0.000 0.000 ↓ 0.0

Index Scan using location_pkey on location location20 (cost=0.13..0.15 rows=1 width=234) (actual rows= loops=)

  • Index Cond: (userlocation22.locationid = id)