explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Fq9m

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

Sort (cost=322,942.52..322,958.23 rows=6,284 width=6,958) (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", (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..1.01 rows=1 width=16) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

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

8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=14,317.24..322,541.03 rows=6,284 width=6,958) (actual rows= loops=)

  • Hash Cond: (userlocation22.locationid = location20.id)
9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=14,316.13..322,493.73 rows=6,284 width=2,129) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash Join (cost=14,315.85..320,473.21 rows=6,284 width=2,113) (actual rows= loops=)

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

Hash Left Join (cost=14,179.74..320,249.51 rows=6,489 width=2,087) (actual rows= loops=)

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

Nested Loop Left Join (cost=12,830.35..318,814.19 rows=6,489 width=1,545) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=12,828.49..304,023.34 rows=6,489 width=1,515) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=12,828.21..302,049.80 rows=6,489 width=944) (actual rows= loops=)

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

Nested Loop Left Join (cost=12,815.01..302,019.34 rows=6,489 width=904) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=12,814.58..286,933.56 rows=6,489 width=844) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=12,814.14..271,912.66 rows=6,489 width=768) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=12,812.89..262,316.00 rows=6,489 width=760) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=12,812.05..248,839.38 rows=6,489 width=699) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=12,811.77..246,318.45 rows=6,489 width=687) (actual rows= loops=)

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

Hash Left Join (cost=12,753.12..246,182.55 rows=6,489 width=683) (actual rows= loops=)

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

Hash Join (cost=12,751.02..246,163.36 rows=6,489 width=667) (actual rows= loops=)

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

Hash Join (cost=12,580.00..244,713.69 rows=323,555 width=404) (actual rows= loops=)

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

Hash Join (cost=10,478.88..241,763.18 rows=323,555 width=412) (actual rows= loops=)

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

Hash Join (cost=10,477.54..240,672.55 rows=323,555 width=298) (actual rows= loops=)

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

Hash Join (cost=10,284.96..239,627.52 rows=324,432 width=275) (actual rows= loops=)

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

Hash Join (cost=10,076.10..238,566.22 rows=324,432 width=220) (actual rows= loops=)

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

Hash Join (cost=4,272.15..231,905.61 rows=326,322 width=200) (actual rows= loops=)

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

Hash Left Join (cost=2,171.03..225,668.13 rows=327,628 width=628) (actual rows= loops=)

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

Hash Left Join (cost=687.68..45,512.51 rows=327,628 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))
31. 0.000 0.000 ↓ 0.0

Seq Scan on dm_attendancetimeallocation_facts at (cost=0.00..42,367.61 rows=327,628 width=133) (actual rows= loops=)

  • Filter: ((entrydate >= '2019-11-01'::date) AND (entrydate <= '2020-06-30'::date))
32. 0.000 0.000 ↓ 0.0

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

33. 0.000 0.000 ↓ 0.0

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

34. 0.000 0.000 ↓ 0.0

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

  • Filter: (clientbillingallocationmethod = 1)
35. 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)
36. 0.000 0.000 ↓ 0.0

Hash (cost=1,281.25..1,281.25 rows=16,168 width=52) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash Join (cost=889.70..1,281.25 rows=16,168 width=52) (actual rows= loops=)

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

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

39. 0.000 0.000 ↓ 0.0

Hash (cost=686.35..686.35 rows=16,268 width=8) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_1 (cost=0.00..686.35 rows=16,268 width=8) (actual rows= loops=)

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

Hash (cost=1,317.72..1,317.72 rows=62,672 width=20) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts (cost=0.00..1,317.72 rows=62,672 width=20) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Hash (cost=5,025.09..5,025.09 rows=62,309 width=20) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Seq Scan on dm_timesheetlist_facts tslist (cost=0.00..5,025.09 rows=62,309 width=20) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

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

46. 0.000 0.000 ↓ 0.0

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

47. 0.000 0.000 ↓ 0.0

Hash (cost=137.26..137.26 rows=4,426 width=23) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

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

49. 0.000 0.000 ↓ 0.0

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

50. 0.000 0.000 ↓ 0.0

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

51. 0.000 0.000 ↓ 0.0

Hash (cost=1,317.72..1,317.72 rows=62,672 width=24) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts_1 (cost=0.00..1,317.72 rows=62,672 width=24) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Hash (cost=169.91..169.91 rows=89 width=263) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Hash Join (cost=3.34..169.91 rows=89 width=263) (actual rows= loops=)

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

Nested Loop (cost=2.19..168.37 rows=89 width=153) (actual rows= loops=)

56. 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)
57. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.19..166.40 rows=89 width=35) (actual rows= loops=)

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

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

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

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

60. 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)
61. 0.000 0.000 ↓ 0.0

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

62. 0.000 0.000 ↓ 0.0

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

63. 0.000 0.000 ↓ 0.0

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

64. 0.000 0.000 ↓ 0.0

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

65. 0.000 0.000 ↓ 0.0

Hash (cost=41.13..41.13 rows=1,401 width=8) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

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

  • Filter: (('2020-05-18'::date >= startdate) AND ('2020-05-18'::date <= enddate))
67. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (userhierarchy21.supervisorid = id)
68. 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))
69. 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[])))
70. 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)
71. 0.000 0.000 ↓ 0.0

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

72. 0.000 0.000 ↓ 0.0

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

73. 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)
74. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (serialnumber = tahls.lastsubmitserialnumber)
75. 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[]))
76. 0.000 0.000 ↓ 0.0

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

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

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

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

Hash (cost=9.20..9.20 rows=320 width=40) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

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

80. 0.000 0.000 ↓ 0.0

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

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

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

82. 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))
83. 0.000 0.000 ↓ 0.0

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

84. 0.000 0.000 ↓ 0.0

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

85. 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)
86. 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)
87. 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)))
88. 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)
89. 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)
90. 0.000 0.000 ↓ 0.0

Hash (cost=1,147.58..1,147.58 rows=16,145 width=546) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=569.19..1,147.58 rows=16,145 width=546) (actual rows= loops=)

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

Hash Right Join (cost=567.00..1,084.84 rows=16,145 width=550) (actual rows= loops=)

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

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

94. 0.000 0.000 ↓ 0.0

Hash (cost=365.89..365.89 rows=16,089 width=546) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

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

96. 0.000 0.000 ↓ 0.0

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

97. 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)
98. 0.000 0.000 ↓ 0.0

Hash (cost=82.38..82.38 rows=4,298 width=38) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.28..82.38 rows=4,298 width=38) (actual rows= loops=)

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

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

101. 0.000 0.000 ↓ 0.0

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

102. 0.000 0.000 ↓ 0.0

Seq Scan on departments dep (cost=0.00..1.57 rows=57 width=38) (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-05-18'::date) AND (enddate >= '2020-05-18'::date))
104. 0.000 0.000 ↓ 0.0

Hash (cost=1.05..1.05 rows=5 width=234) (actual rows= loops=)

105. 0.000 0.000 ↓ 0.0

Seq Scan on location location20 (cost=0.00..1.05 rows=5 width=234) (actual rows= loops=)