explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LPbN

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

Hash Left Join (cost=18,465.38..25,413.57 rows=20,164 width=3,736) (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=17,031.48..22,868.13 rows=20,164 width=1,344) (actual rows= loops=)

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

Hash Left Join (cost=17,000.54..22,783.80 rows=20,164 width=1,319) (actual rows= loops=)

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

Hash Right Join (cost=16,999.11..22,729.02 rows=20,164 width=1,083) (actual rows= loops=)

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

CTE Scan on allstartenddates (cost=3,563.57..4,513.89 rows=22,242 width=36) (actual rows= loops=)

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

CTE alldates

9. 0.000 0.000 ↓ 0.0

HashAggregate (cost=815.48..1,040.14 rows=22,466 width=12) (actual rows= loops=)

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

Append (cost=0.00..703.15 rows=22,466 width=12) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Seq Scan on userhierarchy (cost=0.00..36.18 rows=2,018 width=12) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Seq Scan on userhierarchy userhierarchy_1 (cost=0.00..41.23 rows=2,018 width=12) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

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

14. 0.000 0.000 ↓ 0.0

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

15. 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=)

16. 0.000 0.000 ↓ 0.0

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

17. 0.000 0.000 ↓ 0.0

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

18. 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=)

19. 0.000 0.000 ↓ 0.0

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

20. 0.000 0.000 ↓ 0.0

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

21. 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=)

22. 0.000 0.000 ↓ 0.0

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

23. 0.000 0.000 ↓ 0.0

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

24. 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=)

25. 0.000 0.000 ↓ 0.0

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

26. 0.000 0.000 ↓ 0.0

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

27. 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=)

28. 0.000 0.000 ↓ 0.0

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

29. 0.000 0.000 ↓ 0.0

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

30. 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=)

31. 0.000 0.000 ↓ 0.0

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

32. 0.000 0.000 ↓ 0.0

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

33. 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=)

34. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 17 (cost=0.00..14.04 rows=402 width=12) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on userprojectroleratehistory (cost=0.00..9.02 rows=402 width=8) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on userprojectroleratehistory userprojectroleratehistory_1 (cost=0.00..10.03 rows=402 width=12) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 19 (cost=0.00..12.30 rows=369 width=12) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Seq Scan on userpayrollratehistory (cost=0.00..7.69 rows=369 width=8) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Seq Scan on userpayrollratehistory userpayrollratehistory_1 (cost=0.00..8.61 rows=369 width=12) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 21 (cost=0.28..19.51 rows=554 width=12) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Index Only Scan using userinfo_pkey on userinfo (cost=0.28..12.58 rows=554 width=8) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Index Only Scan using userinfo_pkey on userinfo userinfo_1 (cost=0.28..12.58 rows=554 width=12) (actual rows= loops=)

43.          

CTE allstartenddates

44. 0.000 0.000 ↓ 0.0

WindowAgg (cost=2,073.10..2,522.42 rows=22,466 width=20) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Sort (cost=2,073.10..2,129.27 rows=22,466 width=12) (actual rows= loops=)

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

CTE Scan on alldates main (cost=0.00..449.32 rows=22,466 width=12) (actual rows= loops=)

47.          

Initplan (for CTE Scan)

48. 0.000 0.000 ↓ 0.0

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

49. 0.000 0.000 ↓ 0.0

Hash (cost=13,379.96..13,379.96 rows=4,447 width=1,067) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=121.47..13,379.96 rows=4,447 width=1,067) (actual rows= loops=)

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

Hash Left Join (cost=120.36..13,367.17 rows=4,447 width=831) (actual rows= loops=)

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

Hash Left Join (cost=119.11..13,351.97 rows=4,447 width=713) (actual rows= loops=)

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

Hash Left Join (cost=93.64..13,314.75 rows=4,447 width=690) (actual rows= loops=)

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

Hash Left Join (cost=42.63..13,252.02 rows=4,447 width=664) (actual rows= loops=)

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

Hash Join (cost=10.87..13,208.44 rows=4,447 width=628) (actual rows= loops=)

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

Nested Loop (cost=9.64..13,190.59 rows=4,447 width=392) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

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

58. 0.000 0.000 ↓ 0.0

Nested Loop (cost=9.64..13,145.11 rows=4,447 width=388) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=9.22..11,080.11 rows=4,440 width=287) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Hash Join (cost=8.80..7,489.30 rows=4,440 width=271) (actual rows= loops=)

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

Hash Left Join (cost=5.16..6,286.92 rows=39,958 width=28) (actual rows= loops=)

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

Hash Left Join (cost=2.58..5,891.07 rows=104,870 width=28) (actual rows= loops=)

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

Seq Scan on billingitem2 bi (cost=0.00..4,856.35 rows=275,235 width=28) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Hash (cost=2.57..2.57 rows=1 width=96) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

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

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

Hash (cost=2.57..2.57 rows=1 width=96) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

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

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

Hash (cost=3.51..3.51 rows=10 width=259) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.23..3.51 rows=10 width=259) (actual rows= loops=)

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

Seq Scan on exchangerate (cost=0.00..2.25 rows=10 width=19) (actual rows= loops=)

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

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

72. 0.000 0.000 ↓ 0.0

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

73. 0.000 0.000 ↓ 0.0

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

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

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

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

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

76. 0.000 0.000 ↓ 0.0

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

77. 0.000 0.000 ↓ 0.0

Hash (cost=27.45..27.45 rows=345 width=40) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Seq Scan on project project3 (cost=0.00..27.45 rows=345 width=40) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Hash (cost=34.34..34.34 rows=1,334 width=30) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Seq Scan on task task4 (cost=0.00..34.34 rows=1,334 width=30) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Hash (cost=18.54..18.54 rows=554 width=27) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo userinfo5 (cost=0.00..18.54 rows=554 width=27) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Hash (cost=1.11..1.11 rows=11 width=134) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

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

85. 0.000 0.000 ↓ 0.0

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

86. 0.000 0.000 ↓ 0.0

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

87. 0.000 0.000 ↓ 0.0

Hash (cost=1.19..1.19 rows=19 width=240) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

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

89. 0.000 0.000 ↓ 0.0

Hash (cost=24.86..24.86 rows=486 width=29) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Seq Scan on clients clients10 (cost=0.00..24.86 rows=486 width=29) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Hash (cost=898.51..898.51 rows=42,751 width=24) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet timesheet11 (cost=0.00..898.51 rows=42,751 width=24) (actual rows= loops=)