explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bOC4

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

Sort (cost=285,869.67..285,881.76 rows=4,834 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..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=19,368.98..285,568.81 rows=4,834 width=6,990) (actual rows= loops=)

  • Hash Cond: (ui.id = userlocation22.userid)
9. 0.000 0.000 ↓ 0.0

Hash Join (cost=19,195.64..285,317.27 rows=4,834 width=2,132) (actual rows= loops=)

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

Nested Loop Left Join (cost=19,054.56..285,109.01 rows=4,992 width=2,106) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=19,053.84..280,971.81 rows=4,992 width=1,563) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=19,051.98..269,452.05 rows=4,992 width=1,533) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=19,051.70..267,908.86 rows=4,992 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=19,030.54..267,874.47 rows=4,992 width=922) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=19,030.11..255,799.94 rows=4,992 width=861) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=19,029.67..243,775.33 rows=4,992 width=784) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=19,028.42..236,339.99 rows=4,992 width=776) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=19,027.58..225,919.70 rows=4,992 width=715) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=19,027.29..223,962.32 rows=4,992 width=703) (actual rows= loops=)

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

Hash Left Join (cost=18,970.44..223,847.19 rows=4,992 width=699) (actual rows= loops=)

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

Hash Join (cost=18,968.30..223,831.90 rows=4,992 width=683) (actual rows= loops=)

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

Hash Join (cost=18,793.36..222,639.05 rows=258,095 width=420) (actual rows= loops=)

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

Hash Join (cost=8,295.38..211,463.52 rows=258,095 width=432) (actual rows= loops=)

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

Hash Join (cost=5,979.88..208,470.48 rows=258,095 width=408) (actual rows= loops=)

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

Hash Join (cost=5,978.54..207,600.22 rows=258,095 width=294) (actual rows= loops=)

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

Hash Join (cost=5,766.02..206,707.81 rows=258,770 width=223) (actual rows= loops=)

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

Hash Join (cost=5,570.82..205,804.94 rows=269,334 width=200) (actual rows= loops=)

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

Hash Left Join (cost=3,255.31..200,067.48 rows=271,043 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,220.07..50,228.60 rows=271,043 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..46,975.69 rows=271,043 width=133) (actual rows= loops=)

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

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

32. 0.000 0.000 ↓ 0.0

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

33. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_2 (cost=0.00..1,217.74 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..2.31 rows=1 width=16) (actual rows= loops=)

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

Hash (cost=1,826.76..1,826.76 rows=16,679 width=52) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,427.47..1,826.76 rows=16,679 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..355.49 rows=16,679 width=48) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash (cost=1,217.74..1,217.74 rows=16,779 width=8) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

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

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

Hash (cost=1,465.78..1,465.78 rows=67,978 width=20) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts (cost=0.00..1,465.78 rows=67,978 width=20) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash (cost=140.09..140.09 rows=4,409 width=23) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

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

44. 0.000 0.000 ↓ 0.0

Hash (cost=155.01..155.01 rows=4,601 width=71) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

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

46. 0.000 0.000 ↓ 0.0

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

47. 0.000 0.000 ↓ 0.0

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

48. 0.000 0.000 ↓ 0.0

Hash (cost=1,465.78..1,465.78 rows=67,978 width=24) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts_1 (cost=0.00..1,465.78 rows=67,978 width=24) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Hash (cost=9,642.99..9,642.99 rows=68,399 width=20) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on dm_timesheetlist_facts tslist (cost=0.00..9,642.99 rows=68,399 width=20) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

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

53. 0.000 0.000 ↓ 0.0

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

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

Nested Loop (cost=2.19..172.29 rows=89 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..170.31 rows=89 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..150.90 rows=4,356 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.51..1.51 rows=51 width=24) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

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

64. 0.000 0.000 ↓ 0.0

Hash (cost=40.02..40.02 rows=1,346 width=8) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

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

  • Filter: (('2020-09-01'::date >= startdate) AND ('2020-09-01'::date <= enddate))
66. 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)
67. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.84..2.08 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.42..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.26..1.48 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.42..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.42..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.40 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.40 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=16.07..16.07 rows=407 width=40) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Seq Scan on clients cl (cost=0.00..16.07 rows=407 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.26 rows=5 width=46) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.56..1.83 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.35 rows=1 width=70) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.70..0.86 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.57 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.41 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

Nested Loop Left Join (cost=0.72..0.82 rows=1 width=547) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.43..0.49 rows=1 width=543) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Index Scan using task_pkey on task tk (cost=0.29..0.31 rows=1 width=547) (actual rows= loops=)

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

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

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

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh (cost=0.29..0.33 rows=1 width=5) (actual rows= loops=)

  • Index Cond: (tk.id = taskid)
94. 0.000 0.000 ↓ 0.0

Hash (cost=85.39..85.39 rows=4,455 width=38) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.28..85.39 rows=4,455 width=38) (actual rows= loops=)

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

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

97. 0.000 0.000 ↓ 0.0

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

98. 0.000 0.000 ↓ 0.0

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

99. 0.000 0.000 ↓ 0.0

Hash (cost=123.42..123.42 rows=3,993 width=222) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1.11..123.42 rows=3,993 width=222) (actual rows= loops=)

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

Seq Scan on userlocation userlocation22 (cost=0.00..102.94 rows=3,993 width=20) (actual rows= loops=)

  • Filter: (('2020-09-01'::date >= startdate) AND ('2020-09-01'::date <= enddate))
102. 0.000 0.000 ↓ 0.0

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

103. 0.000 0.000 ↓ 0.0

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