explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aZGZ

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

Sort (cost=289,182.26..289,195.65 rows=5,355 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 Join (cost=20,594.54..288,845.56 rows=5,355 width=6,990) (actual rows= loops=)

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

Hash Left Join (cost=20,440.02..288,604.89 rows=5,292 width=2,325) (actual rows= loops=)

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

Nested Loop Left Join (cost=20,239.78..288,326.61 rows=5,292 width=2,107) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=20,239.06..283,931.23 rows=5,292 width=1,564) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=20,237.20..271,642.97 rows=5,292 width=1,534) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=20,236.77..258,626.00 rows=5,228 width=1,473) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=20,236.48..257,004.05 rows=5,228 width=902) (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=20,215.48..256,969.18 rows=5,228 width=862) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=20,215.05..244,004.49 rows=5,228 width=785) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=20,213.79..236,248.68 rows=5,228 width=777) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=20,212.95..225,358.79 rows=5,228 width=716) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=20,212.67..223,243.67 rows=5,228 width=704) (actual rows= loops=)

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

Hash Left Join (cost=20,155.75..223,126.59 rows=5,228 width=700) (actual rows= loops=)

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

Hash Join (cost=20,153.60..223,110.67 rows=5,228 width=684) (actual rows= loops=)

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

Hash Join (cost=19,966.30..221,882.90 rows=263,426 width=421) (actual rows= loops=)

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

Hash Join (cost=19,964.96..220,994.69 rows=263,426 width=307) (actual rows= loops=)

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

Hash Join (cost=19,751.98..220,089.62 rows=263,426 width=283) (actual rows= loops=)

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

Hash Join (cost=19,518.00..219,163.54 rows=263,426 width=212) (actual rows= loops=)

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

Hash Join (cost=17,123.45..216,081.85 rows=261,751 width=224) (actual rows= loops=)

  • Hash Cond: (at.timesheetid = tslist.timesheetid)
27. 0.000 0.000 ↓ 0.0

Hash Join (cost=6,199.77..204,466.63 rows=263,426 width=204) (actual rows= loops=)

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

Hash Left Join (cost=3,805.22..198,725.00 rows=265,112 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,494.70..51,858.28 rows=265,112 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..48,375.22 rows=265,112 width=133) (actual rows= loops=)

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

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

32. 0.000 0.000 ↓ 0.0

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

33. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_2 (cost=0.00..1,492.38 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=2,102.03..2,102.03 rows=16,679 width=52) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash Join (cost=1,702.75..2,102.03 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,492.38..1,492.38 rows=16,830 width=8) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

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

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

Hash (cost=1,500.91..1,500.91 rows=71,491 width=24) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts_1 (cost=0.00..1,500.91 rows=71,491 width=24) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash (cost=10,029.97..10,029.97 rows=71,497 width=20) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on dm_timesheetlist_facts tslist (cost=0.00..10,029.97 rows=71,497 width=20) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash (cost=1,500.91..1,500.91 rows=71,491 width=20) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts (cost=0.00..1,500.91 rows=71,491 width=20) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash (cost=172.88..172.88 rows=4,888 width=71) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

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

48. 0.000 0.000 ↓ 0.0

Hash (cost=151.88..151.88 rows=4,888 width=24) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Seq Scan on login (cost=0.00..151.88 rows=4,888 width=24) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

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

51. 0.000 0.000 ↓ 0.0

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

52. 0.000 0.000 ↓ 0.0

Hash (cost=186.09..186.09 rows=97 width=263) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Hash Join (cost=3.34..186.09 rows=97 width=263) (actual rows= loops=)

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

Nested Loop (cost=2.19..184.52 rows=97 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..182.47 rows=97 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..161.40 rows=4,776 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.07..40.07 rows=1,348 width=8) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

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

  • Filter: (('2020-10-09'::date >= startdate) AND ('2020-10-09'::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.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.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.42..0.45 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.57 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.47 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

Hash (cost=16.00..16.00 rows=400 width=40) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

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

78. 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)
79. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue14 (cost=0.43..2.47 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)
80. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.85..2.26 rows=6 width=46) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.56..1.84 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.36 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.48 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=140.71..140.71 rows=4,763 width=222) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1.11..140.71 rows=4,763 width=222) (actual rows= loops=)

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

Seq Scan on userlocation userlocation22 (cost=0.00..116.49 rows=4,763 width=20) (actual rows= loops=)

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

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

98. 0.000 0.000 ↓ 0.0

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

99. 0.000 0.000 ↓ 0.0

Hash (cost=92.69..92.69 rows=4,946 width=38) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Hash Join (cost=2.28..92.69 rows=4,946 width=38) (actual rows= loops=)

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

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

102. 0.000 0.000 ↓ 0.0

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

103. 0.000 0.000 ↓ 0.0

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