explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ULN5

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

Sort (cost=3,881,256.48..3,881,265.01 rows=3,414 width=1,925) (actual rows= loops=)

  • Sort Key: ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", timeexpensecostbilling2_facts6.userduplicatename5, ((login.loginname)::character varying(255)) COLLATE "en_US", ((ui.displayname)::text) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", timeexpensecostbilling2_facts6.billingratename2 COLLATE "en_US", timeexpensecostbilling2_facts6.comments3 COLLATE "en_US
2.          

CTE eexpensecostbilling2_facts0cte

3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=3,872,723.05..3,872,868.15 rows=3,414 width=173) (actual rows= loops=)

  • Group Key: (upper((timeexpensecostbilling2_facts0.billingratename)::text)), (upper(timeexpensecostbilling2_facts0.comments)), timeexpensecostbilling2_facts0.userid, ui_1.duplicatename, timeexpensecostbilling2_facts0.projectid, timeexpensecostbilling2_facts0.taskid
4. 0.000 0.000 ↓ 0.0

Sort (cost=3,872,723.05..3,872,731.59 rows=3,414 width=173) (actual rows= loops=)

  • Sort Key: (upper((timeexpensecostbilling2_facts0.billingratename)::text)), (upper(timeexpensecostbilling2_facts0.comments)), timeexpensecostbilling2_facts0.userid, ui_1.duplicatename, timeexpensecostbilling2_facts0.projectid, timeexpensecostbilling2_facts0.taskid
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=220,121.26..3,872,522.70 rows=3,414 width=173) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Index Only Scan using program_pkey on program program4 (cost=0.14..1.16 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (id = 'f076c3ac-cf53-4742-bdcd-29080928be6c'::uuid)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=220,121.12..3,872,470.33 rows=3,414 width=125) (actual rows= loops=)

  • Hash Cond: (timeexpensecostbilling2_facts0.userid = ui_1.id)
8. 0.000 0.000 ↓ 0.0

Hash Join (cost=219,841.22..3,872,181.46 rows=3,414 width=124) (actual rows= loops=)

  • Hash Cond: (timeexpensecostbilling2_facts0.userid = userlocation3.userid)
9. 0.000 0.000 ↓ 0.0

Hash Join (cost=219,708.35..3,871,992.68 rows=6,829 width=124) (actual rows= loops=)

  • Hash Cond: (timeexpensecostbilling2_facts0.projectid = pj_1.id)
10. 0.000 0.000 ↓ 0.0

Subquery Scan on timeexpensecostbilling2_facts0 (cost=218,784.23..3,870,916.93 rows=57,743 width=108) (actual rows= loops=)

  • Filter: (((timeexpensecostbilling2_facts0.entrydatesheetdate >= '2020-06-22'::date) AND (timeexpensecostbilling2_facts0.entrydatesheetdate <= '2020-06-28'::date)) OR (timeexpensecostbilling2_facts0.entrydatesheetdate IS NULL))
11. 0.000 0.000 ↓ 0.0

Append (cost=218,784.23..3,784,085.10 rows=5,788,789 width=1,052) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Result (cost=218,784.23..1,205,703.89 rows=4,468,009 width=1,052) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Append (cost=218,784.23..1,138,683.75 rows=4,468,009 width=1,044) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=218,784.23..1,094,003.67 rows=4,468,008 width=1,415) (actual rows= loops=)

  • Hash Cond: ((at.projectid = ptm.projectid) AND (at.userid = ptm.userid) AND (at.entrydate = ptma.date))
15.          

Initplan (for Hash Left Join)

16. 0.000 0.000 ↓ 0.0

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

17. 0.000 0.000 ↓ 0.0

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

18. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=213,521.31..742,470.12 rows=4,468,008 width=755) (actual rows= loops=)

  • Hash Cond: (at.taskid = tk_1.id)
19. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=185,331.47..702,551.68 rows=4,468,008 width=751) (actual rows= loops=)

  • Hash Cond: (at.billingrateid = br.id)
20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=185,157.01..690,642.07 rows=4,468,008 width=708) (actual rows= loops=)

  • Hash Cond: (pbr.id = pbrh.projectbillingrateid)
  • Join Filter: ((at.entrydate >= pbrh.effectivedate) AND (at.entrydate <= pbrh.enddate))
21. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=147,669.52..494,826.25 rows=4,468,008 width=711) (actual rows= loops=)

  • Hash Cond: (at.projectid = pj_2.id)
22. 0.000 0.000 ↓ 0.0

Hash Join (cost=146,652.68..482,076.46 rows=4,468,008 width=707) (actual rows= loops=)

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

Merge Left Join (cost=131,681.64..455,324.16 rows=4,488,055 width=695) (actual rows= loops=)

  • Merge Cond: ((at.billingrateid = pbr.billingrateid) AND (at.projectid = pbr.projectid))
  • Join Filter: (((pbr.billingrateid = $3) AND (at.userid = pbr.userid)) OR ((pbr.billingrateid <> $4) AND (pbr.userid IS NULL)))
24. 0.000 0.000 ↓ 0.0

Index Scan using ixpta4billingrateid on dm_projecttimeallocation_facts at (cost=0.56..284,129.22 rows=4,488,055 width=679) (actual rows= loops=)

  • Filter: ((timeoffcodeid IS NULL) AND (duration IS NOT NULL))
25. 0.000 0.000 ↓ 0.0

Sort (cost=131,680.93..134,451.02 rows=1,108,037 width=40) (actual rows= loops=)

  • Sort Key: pbr.billingrateid, pbr.projectid
26. 0.000 0.000 ↓ 0.0

Seq Scan on projectbillingrate pbr (cost=0.00..20,436.37 rows=1,108,037 width=40) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Hash (cost=9,302.13..9,302.13 rows=453,513 width=28) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts (cost=0.00..9,302.13 rows=453,513 width=28) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=880.82..880.82 rows=10,882 width=8) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_2 (cost=0.00..880.82 rows=10,882 width=8) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Hash (cost=23,234.44..23,234.44 rows=1,140,244 width=33) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on projectbillingratehistory pbrh (cost=0.00..23,234.44 rows=1,140,244 width=33) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=110.87..110.87 rows=5,087 width=43) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Seq Scan on billingrate br (cost=0.00..110.87 rows=5,087 width=43) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Hash (cost=20,534.93..20,534.93 rows=612,393 width=8) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on task tk_1 (cost=0.00..20,534.93 rows=612,393 width=8) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash (cost=5,144.38..5,144.38 rows=4,888 width=44) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.42..5,144.38 rows=4,888 width=44) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on projectteammemberallocation ptma (cost=0.00..99.88 rows=4,888 width=52) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Index Scan using projectteammember_pkey on projectteammember ptm (cost=0.42..1.03 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (id = ptma.projectteammemberid)
41. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.00..0.00 rows=1 width=1,044) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=1,044) (actual rows= loops=)

  • One-Time Filter: false
43. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 3 (cost=3.13..11,281.07 rows=37,216 width=1,052) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Nested Loop (cost=3.13..10,908.91 rows=37,216 width=2,410) (actual rows= loops=)

  • Join Filter: (((ee.amount * ee.exchangevalue) * exchangerate_1.exchangevalue) IS NOT NULL)
45.          

Initplan (for Nested Loop)

46. 0.000 0.000 ↓ 0.0

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

47. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.70..4,368.81 rows=9,579 width=30) (actual rows= loops=)

  • Hash Cond: (ex.reimbursementcurrencyid = exchangerate_1.fixedcurrencyid)
  • Join Filter: ((ex.expensedate >= exchangerate_1.effectivedate) AND (ex.expensedate <= exchangerate_1.enddate))
48. 0.000 0.000 ↓ 0.0

Seq Scan on expense ex (cost=0.00..2,104.10 rows=86,210 width=27) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Hash (cost=1.61..1.61 rows=7 width=19) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Seq Scan on exchangerate exchangerate_1 (cost=0.00..1.61 rows=7 width=19) (actual rows= loops=)

  • Filter: (variablecurrencyid = $2)
51. 0.000 0.000 ↓ 0.0

Index Scan using ixeeexpenseid on expenseentry ee (cost=0.42..0.56 rows=4 width=1,615) (actual rows= loops=)

  • Index Cond: (expenseid = ex.id)
52. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 4 (cost=0.00..0.01 rows=1 width=1,052) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.00 rows=0 width=1,044) (actual rows= loops=)

  • One-Time Filter: false
54. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 5 (cost=1,262,681.15..2,567,100.13 rows=1,283,563 width=948) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,262,681.15..2,547,846.69 rows=1,283,563 width=940) (actual rows= loops=)

56.          

Initplan (for Nested Loop Left Join)

57. 0.000 0.000 ↓ 0.0

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

58. 0.000 0.000 ↓ 0.0

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

59. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,262,647.72..1,345,006.55 rows=1,283,563 width=180) (actual rows= loops=)

  • Hash Cond: (bi.id = iibi.billingitemid)
60. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,262,613.64..1,341,603.12 rows=1,283,563 width=164) (actual rows= loops=)

  • Hash Cond: (aggm.billingitemid = bi.id)
  • Filter: (aggm.uri IS NULL)
61. 0.000 0.000 ↓ 0.0

Index Scan using ixbiminvoicinghint on billingitem2metadata aggm (cost=0.41..552.10 rows=10,646 width=66) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Hash (cost=1,142,936.86..1,142,936.86 rows=3,330,110 width=164) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=959,295.84..1,142,936.86 rows=3,330,110 width=164) (actual rows= loops=)

  • Hash Cond: (bidm.timesheetid = t.id)
64. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=944,324.79..1,119,224.20 rows=3,330,110 width=152) (actual rows= loops=)

  • Hash Cond: (pbr_1.id = pbrh_1.projectbillingrateid)
  • Join Filter: ((bi.entrydate >= pbrh_1.effectivedate) AND (bi.entrydate <= pbrh_1.enddate))
65. 0.000 0.000 ↓ 0.0

Hash Join (cost=906,837.30..963,730.94 rows=3,330,110 width=164) (actual rows= loops=)

  • Hash Cond: (bidm.projectid = pj_3.id)
66. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=906,165.76..954,314.54 rows=3,330,110 width=164) (actual rows= loops=)

  • Hash Cond: (bidm.billingrateid = br_1.id)
67. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=905,991.31..945,393.94 rows=3,330,110 width=137) (actual rows= loops=)

  • Merge Cond: ((bidm.projectid = pbr_1.projectid) AND (bidm.billingrateid = pbr_1.billingrateid))
  • Join Filter: (((pbr_1.billingrateid = $0) AND (bidm.userid = pbr_1.userid)) OR ((pbr_1.billingrateid <> $1) AND (pbr_1.userid IS NULL)))
68. 0.000 0.000 ↓ 0.0

Sort (cost=774,310.38..782,635.65 rows=3,330,110 width=121) (actual rows= loops=)

  • Sort Key: bidm.projectid, bidm.billingrateid
69. 0.000 0.000 ↓ 0.0

Hash Join (cost=128,566.05..289,960.61 rows=3,330,110 width=121) (actual rows= loops=)

  • Hash Cond: (bidm.billingitemid = bi.id)
  • Join Filter: (CASE WHEN (bidm.billingtypeuri = 'urn:replicon:billing-item-type:timesheet'::text) THEN bi.amount ELSE 0.0000::numeric(19,4) END IS NOT NULL)
70. 0.000 0.000 ↓ 0.0

Seq Scan on billingitem2denormalizedmetadata bidm (cost=0.00..148,346.91 rows=4,970,529 width=109) (actual rows= loops=)

  • Filter: (userid IS NOT NULL)
71. 0.000 0.000 ↓ 0.0

Hash (cost=86,689.27..86,689.27 rows=3,350,142 width=28) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Seq Scan on billingitem2 bi (cost=0.00..86,689.27 rows=3,350,142 width=28) (actual rows= loops=)

  • Filter: isbillable
73. 0.000 0.000 ↓ 0.0

Sort (cost=131,680.93..134,451.02 rows=1,108,037 width=40) (actual rows= loops=)

  • Sort Key: pbr_1.projectid, pbr_1.billingrateid
74. 0.000 0.000 ↓ 0.0

Seq Scan on projectbillingrate pbr_1 (cost=0.00..20,436.37 rows=1,108,037 width=40) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Hash (cost=110.87..110.87 rows=5,087 width=43) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Seq Scan on billingrate br_1 (cost=0.00..110.87 rows=5,087 width=43) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Hash (cost=535.51..535.51 rows=10,882 width=4) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Index Only Scan using project_pkey on project pj_3 (cost=0.29..535.51 rows=10,882 width=4) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Hash (cost=23,234.44..23,234.44 rows=1,140,244 width=24) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Seq Scan on projectbillingratehistory pbrh_1 (cost=0.00..23,234.44 rows=1,140,244 width=24) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Hash (cost=9,302.13..9,302.13 rows=453,513 width=28) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet t (cost=0.00..9,302.13 rows=453,513 width=28) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Hash (cost=20.70..20.70 rows=1,070 width=32) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Seq Scan on invoiceitembillingitems iibi (cost=0.00..20.70 rows=1,070 width=32) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Index Scan using ixbimbillingitemidkey on billingitem2metadata bi2m (cost=0.43..0.88 rows=4 width=32) (actual rows= loops=)

  • Index Cond: (billingitemid = bi.id)
86. 0.000 0.000 ↓ 0.0

Hash (cost=908.02..908.02 rows=1,287 width=20) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Seq Scan on project pj_1 (cost=0.00..908.02 rows=1,287 width=20) (actual rows= loops=)

  • Filter: (programid = 'f076c3ac-cf53-4742-bdcd-29080928be6c'::uuid)
88. 0.000 0.000 ↓ 0.0

Hash (cost=122.35..122.35 rows=842 width=4) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

HashAggregate (cost=113.93..122.35 rows=842 width=4) (actual rows= loops=)

  • Group Key: userlocation3.userid
90. 0.000 0.000 ↓ 0.0

Index Scan using ixullocationid on userlocation userlocation3 (cost=0.28..111.81 rows=847 width=4) (actual rows= loops=)

  • Index Cond: (locationid = ANY ('{d113e81d-c881-4c20-9188-54b6f4cf1671,7da2c924-ea0f-4c82-b9ba-e2eb7de029d2,3c0de1be-c61b-4893-b080-5b2879103e97,0a86b816-98f6-459e-b639-2987a0eb5211,52a64bec-4ca2-4230-92e9-b9667c8cca00,2796be80-40b1-4585-a9e3-9eeb6c5fb5e4,1ff50fbf-de8c-4aa6-a078-bf78b7288bdc}'::uuid[]))
  • Filter: ((startdate <= '2020-06-30'::date) AND (enddate >= '2020-06-30'::date))
91. 0.000 0.000 ↓ 0.0

Hash (cost=203.29..203.29 rows=6,129 width=5) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui_1 (cost=0.00..203.29 rows=6,129 width=5) (actual rows= loops=)

93.          

Initplan (for Sort)

94. 0.000 0.000 ↓ 0.0

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

95. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,428.50..8,186.96 rows=3,414 width=1,925) (actual rows= loops=)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
96. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1,426.06..8,125.02 rows=3,414 width=235) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1,425.64..1,682.33 rows=3,414 width=195) (actual rows= loops=)

  • Hash Cond: (login.userid = ui.id)
98. 0.000 0.000 ↓ 0.0

Seq Scan on login (cost=0.00..199.49 rows=6,149 width=18) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

Hash (cost=1,382.96..1,382.96 rows=3,414 width=185) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=1,296.75..1,382.96 rows=3,414 width=185) (actual rows= loops=)

  • Hash Cond: (timeexpensecostbilling2_facts6.projectid6 = pj.id)
101. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=279.90..357.16 rows=3,414 width=143) (actual rows= loops=)

  • Hash Cond: (timeexpensecostbilling2_facts6.userid4 = ui.id)
102. 0.000 0.000 ↓ 0.0

CTE Scan on eexpensecostbilling2_facts0cte timeexpensecostbilling2_facts6 (cost=0.00..68.28 rows=3,414 width=109) (actual rows= loops=)

103. 0.000 0.000 ↓ 0.0

Hash (cost=203.29..203.29 rows=6,129 width=34) (actual rows= loops=)

104. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..203.29 rows=6,129 width=34) (actual rows= loops=)

105. 0.000 0.000 ↓ 0.0

Hash (cost=880.82..880.82 rows=10,882 width=46) (actual rows= loops=)

106. 0.000 0.000 ↓ 0.0

Seq Scan on project pj (cost=0.00..880.82 rows=10,882 width=46) (actual rows= loops=)

107. 0.000 0.000 ↓ 0.0

Index Scan using task_pkey on task tk (cost=0.42..1.89 rows=1 width=44) (actual rows= loops=)

  • Index Cond: (timeexpensecostbilling2_facts6.taskid7 = id)
108. 0.000 0.000 ↓ 0.0

Hash (cost=2.35..2.35 rows=7 width=4) (actual rows= loops=)

109. 0.000 0.000 ↓ 0.0

Seq Scan on exchangerate (cost=0.00..2.35 rows=7 width=4) (actual rows= loops=)

  • Filter: ((variablecurrencyid = $9) AND (('now'::cstring)::date >= effectivedate) AND (('now'::cstring)::date <= enddate))