explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yybl

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

Hash Left Join (cost=90,638.19..99,794.89 rows=37,818 width=3,735) (actual rows= loops=)

  • Hash Cond: (bidm.timesheetid = timesheet11.id)
2.          

Initplan (for Hash Left Join)

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

Hash Left Join (cost=87,491.20..94,563.19 rows=37,818 width=1,342) (actual rows= loops=)

  • Hash Cond: (bidm.clientid = clients10.id)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=87,449.89..94,421.97 rows=37,818 width=1,312) (actual rows= loops=)

  • Hash Cond: (bidm.expensetypeid = expensetype9.id)
6. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=87,448.67..94,321.47 rows=37,818 width=1,076) (actual rows= loops=)

  • Merge Cond: (bidm.userid = allstartenddates.userid)
  • Join Filter: ((bi.entrydate >= allstartenddates.startdate) AND (bi.entrydate <= ((allstartenddates.nextstartdate - '1 day'::interval))))
7. 0.000 0.000 ↓ 0.0

Sort (cost=78,316.59..78,349.33 rows=13,097 width=1,060) (actual rows= loops=)

  • Sort Key: bidm.userid
8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,663.62..77,420.95 rows=13,097 width=1,060) (actual rows= loops=)

  • Hash Cond: (bidm.activityid = activities8.id)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,662.49..77,385.44 rows=13,097 width=824) (actual rows= loops=)

  • Hash Cond: (bidm.billingrateid = billingrate7.id)
10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,661.33..77,335.78 rows=13,097 width=706) (actual rows= loops=)

  • Hash Cond: (bidm.userid = userinfo5.id)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,586.92..77,226.91 rows=13,097 width=690) (actual rows= loops=)

  • Hash Cond: (bidm.taskid = task4.id)
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=806.10..75,411.70 rows=13,097 width=665) (actual rows= loops=)

  • Hash Cond: (bidm.projectid = project3.id)
13. 0.000 0.000 ↓ 0.0

Hash Join (cost=8.04..74,579.26 rows=13,097 width=627) (actual rows= loops=)

  • Hash Cond: (si.basecurrencyid = currencyinfo2.id)
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6.89..74,522.91 rows=13,097 width=391) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

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

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6.89..74,390.93 rows=13,097 width=387) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6.46..65,414.81 rows=13,059 width=371) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=6.04..59,293.06 rows=13,078 width=271) (actual rows= loops=)

  • Filter: (timeoffcode.uri IS NULL)
19. 0.000 0.000 ↓ 0.0

Hash Join (cost=5.48..26,174.66 rows=35,020 width=271) (actual rows= loops=)

  • Hash Cond: (bi.currencyid = exchangerate.fixedcurrencyid)
  • Join Filter: ((bi.entrydate >= exchangerate.effectivedate) AND (bi.entrydate <= exchangerate.enddate))
20. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2.59..17,898.35 rows=315,178 width=28) (actual rows= loops=)

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

Seq Scan on billingitem2 bi (cost=0.00..14,730.82 rows=843,982 width=28) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=2.58..2.58 rows=1 width=69) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Index Scan using ix2bimkeybillingitemid on billingitem2metadata breaktype (cost=0.56..2.58 rows=1 width=69) (actual rows= loops=)

  • Index Cond: (upper(key) = 'URN:REPLICON:BILLING-ITEM-METADATA-KEY:BREAK-TYPE'::text)
24. 0.000 0.000 ↓ 0.0

Hash (cost=2.80..2.80 rows=7 width=259) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.16..2.80 rows=7 width=259) (actual rows= loops=)

  • Hash Cond: (exchangerate.fixedcurrencyid = currencyinfo1.id)
26. 0.000 0.000 ↓ 0.0

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

  • Filter: (variablecurrencyid = $0)
27. 0.000 0.000 ↓ 0.0

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

28. 0.000 0.000 ↓ 0.0

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

29. 0.000 0.000 ↓ 0.0

Index Scan using ix2bimkeybillingitemid on billingitem2metadata timeoffcode (cost=0.56..0.94 rows=1 width=69) (actual rows= loops=)

  • Index Cond: ((upper(key) = 'URN:REPLICON:BILLING-ITEM-METADATA-KEY:TIME-OFF-TYPE'::text) AND (billingitemid = bi.id))
30. 0.000 0.000 ↓ 0.0

Index Scan using uixbi2dm on billingitem2denormalizedmetadata bidm (cost=0.42..0.47 rows=1 width=116) (actual rows= loops=)

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

Index Scan using ixbimbillingitemidkey on billingitem2metadata timeworked (cost=0.42..0.68 rows=1 width=32) (actual rows= loops=)

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

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

33. 0.000 0.000 ↓ 0.0

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

34. 0.000 0.000 ↓ 0.0

Hash (cost=669.69..669.69 rows=10,269 width=42) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on project project3 (cost=0.00..669.69 rows=10,269 width=42) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Hash (cost=1,197.59..1,197.59 rows=46,659 width=29) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on task task4 (cost=0.00..1,197.59 rows=46,659 width=29) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash (cost=54.18..54.18 rows=1,618 width=20) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo userinfo5 (cost=0.00..54.18 rows=1,618 width=20) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

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

41. 0.000 0.000 ↓ 0.0

Seq Scan on billingrate billingrate7 (cost=0.00..1.07 rows=7 width=134) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Hash (cost=1.06..1.06 rows=6 width=240) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on activities activities8 (cost=0.00..1.06 rows=6 width=240) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Sort (cost=9,132.08..9,210.91 rows=31,530 width=36) (actual rows= loops=)

  • Sort Key: allstartenddates.userid
45. 0.000 0.000 ↓ 0.0

CTE Scan on allstartenddates (cost=5,113.61..6,460.79 rows=31,530 width=36) (actual rows= loops=)

  • Filter: ((nextstartdate IS NOT NULL) AND (userid <> $3))
46.          

CTE alldates

47. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,138.14..1,456.62 rows=31,848 width=12) (actual rows= loops=)

  • Group Key: userhierarchy.userid, userhierarchy.startdate
48. 0.000 0.000 ↓ 0.0

Append (cost=0.00..978.90 rows=31,848 width=12) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Seq Scan on userhierarchy (cost=0.00..60.47 rows=3,347 width=12) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Seq Scan on userhierarchy userhierarchy_1 (cost=0.00..68.84 rows=3,347 width=12) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 3 (cost=0.00..35.43 rows=1,130 width=12) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Seq Scan on userlocation (cost=0.00..21.30 rows=1,130 width=8) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Seq Scan on userlocation userlocation_1 (cost=0.00..24.12 rows=1,130 width=12) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 5 (cost=0.00..35.43 rows=1,130 width=12) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Seq Scan on userdivision (cost=0.00..21.30 rows=1,130 width=8) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Seq Scan on userdivision userdivision_1 (cost=0.00..24.12 rows=1,130 width=12) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 7 (cost=0.00..35.43 rows=1,130 width=12) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Seq Scan on usercostcenter (cost=0.00..21.30 rows=1,130 width=8) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Seq Scan on usercostcenter usercostcenter_1 (cost=0.00..24.12 rows=1,130 width=12) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 9 (cost=0.00..35.43 rows=1,130 width=12) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on userservicecenter (cost=0.00..21.30 rows=1,130 width=8) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Seq Scan on userservicecenter userservicecenter_1 (cost=0.00..24.12 rows=1,130 width=12) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 11 (cost=0.00..35.43 rows=1,130 width=12) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Seq Scan on userdepartmentgroup (cost=0.00..21.30 rows=1,130 width=8) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Seq Scan on userdepartmentgroup userdepartmentgroup_1 (cost=0.00..24.12 rows=1,130 width=12) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 13 (cost=0.00..35.43 rows=1,130 width=12) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Seq Scan on useremployeetypegroup (cost=0.00..21.30 rows=1,130 width=8) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Seq Scan on useremployeetypegroup useremployeetypegroup_1 (cost=0.00..24.12 rows=1,130 width=12) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 15 (cost=0.00..34.98 rows=1,110 width=12) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Seq Scan on userprojectroleassignment (cost=0.00..21.10 rows=1,110 width=8) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Seq Scan on userprojectroleassignment userprojectroleassignment_1 (cost=0.00..23.88 rows=1,110 width=12) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 17 (cost=0.00..48.67 rows=1,541 width=12) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Seq Scan on userprojectroleratehistory (cost=0.00..29.41 rows=1,541 width=8) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Seq Scan on userprojectroleratehistory userprojectroleratehistory_1 (cost=0.00..33.26 rows=1,541 width=12) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 19 (cost=0.00..45.38 rows=1,528 width=12) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Seq Scan on userpayrollratehistory (cost=0.00..26.28 rows=1,528 width=8) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Seq Scan on userpayrollratehistory userpayrollratehistory_1 (cost=0.00..30.10 rows=1,528 width=12) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 21 (cost=0.28..51.77 rows=1,618 width=12) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Index Only Scan using userinfo_pkey on userinfo (cost=0.28..31.55 rows=1,618 width=8) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Index Only Scan using userinfo_pkey on userinfo userinfo_1 (cost=0.28..31.55 rows=1,618 width=12) (actual rows= loops=)

81.          

CTE allstartenddates

82. 0.000 0.000 ↓ 0.0

WindowAgg (cost=3,019.02..3,655.98 rows=31,848 width=20) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Sort (cost=3,019.02..3,098.64 rows=31,848 width=12) (actual rows= loops=)

  • Sort Key: main.userid, main.startdate
84. 0.000 0.000 ↓ 0.0

CTE Scan on alldates main (cost=0.00..636.96 rows=31,848 width=12) (actual rows= loops=)

85.          

Initplan (for CTE Scan)

86. 0.000 0.000 ↓ 0.0

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

87. 0.000 0.000 ↓ 0.0

Hash (cost=1.10..1.10 rows=10 width=240) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Seq Scan on expensetype expensetype9 (cost=0.00..1.10 rows=10 width=240) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Hash (cost=33.36..33.36 rows=636 width=34) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Seq Scan on clients clients10 (cost=0.00..33.36 rows=636 width=34) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Hash (cost=1,962.10..1,962.10 rows=94,710 width=24) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet timesheet11 (cost=0.00..1,962.10 rows=94,710 width=24) (actual rows= loops=)