explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4BiJ

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

Hash Left Join (cost=116,224.21..782,462.03 rows=2,569,702 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=114,396.56..638,979.04 rows=2,569,702 width=1,939) (actual rows= loops=)

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

Hash Left Join (cost=114,394.38..630,747.07 rows=2,569,702 width=1,805) (actual rows= loops=)

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

Hash Left Join (cost=114,392.82..623,996.43 rows=2,569,702 width=1,673) (actual rows= loops=)

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

Hash Left Join (cost=114,391.71..611,552.06 rows=2,569,702 width=1,437) (actual rows= loops=)

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

Hash Left Join (cost=114,390.12..604,744.34 rows=2,569,702 width=803) (actual rows= loops=)

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

Hash Join (cost=114,379.78..597,713.67 rows=2,569,702 width=775) (actual rows= loops=)

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

Merge Left Join (cost=114,378.65..586,202.40 rows=2,569,702 width=539) (actual rows= loops=)

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

Merge Left Join (cost=103,027.72..110,980.46 rows=529,880 width=523) (actual rows= loops=)

  • Merge Cond: (bidm.userid = userinfo5.id)
12. 0.000 0.000 ↓ 0.0

Sort (cost=102,918.55..104,243.25 rows=529,880 width=506) (actual rows= loops=)

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

Hash Left Join (cost=1,008.46..52,539.40 rows=529,880 width=506) (actual rows= loops=)

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

Nested Loop (cost=1,007.42..50,622.72 rows=529,880 width=388) (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 (cost=1,007.42..45,322.91 rows=529,880 width=384) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Gather (cost=1,007.14..27,386.54 rows=29,043 width=383) (actual rows= loops=)

  • Workers Planned: 1
18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=7.14..23,482.24 rows=17,084 width=383) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6.72..11,954.20 rows=16,864 width=367) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Hash Join (cost=6.30..4,205.51 rows=16,770 width=268) (actual rows= loops=)

  • Hash Cond: (bi.currencyid = currencyinfo1.id)
21. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=5.17..4,129.26 rows=16,770 width=28) (actual rows= loops=)

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

Hash Left Join (cost=2.58..3,940.24 rows=49,716 width=28) (actual rows= loops=)

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

Parallel Seq Scan on billingitem2 bi (cost=0.00..3,384.92 rows=147,392 width=28) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

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

25. 0.000 0.000 ↓ 0.0

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

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

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

27. 0.000 0.000 ↓ 0.0

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

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

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

29. 0.000 0.000 ↓ 0.0

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

30. 0.000 0.000 ↓ 0.0

Index Scan using uixbi2dm on billingitem2denormalizedmetadata bidm (cost=0.42..0.46 rows=1 width=115) (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.67 rows=1 width=32) (actual rows= loops=)

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

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.28..0.58 rows=4 width=17) (actual rows= loops=)

  • Index Cond: ((variablecurrencyid = $0) AND (fixedcurrencyid = bi.currencyid) AND (bi.entrydate >= effectivedate))
  • Filter: (bi.entrydate <= enddate)
33. 0.000 0.000 ↓ 0.0

Hash (cost=1.02..1.02 rows=2 width=134) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

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

35. 0.000 0.000 ↓ 0.0

Sort (cost=109.16..111.44 rows=915 width=21) (actual rows= loops=)

  • Sort Key: userinfo5.id
36. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo userinfo5 (cost=0.00..64.15 rows=915 width=21) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Sort (cost=11,350.93..11,447.49 rows=38,627 width=36) (actual rows= loops=)

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

CTE Scan on allstartenddates (cost=6,371.40..8,021.80 rows=38,627 width=36) (actual rows= loops=)

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

CTE alldates

40. 0.000 0.000 ↓ 0.0

HashAggregate (cost=1,444.27..1,834.43 rows=39,016 width=12) (actual rows= loops=)

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

Append (cost=0.00..1,249.19 rows=39,016 width=12) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Seq Scan on userhierarchy (cost=0.00..17.03 rows=903 width=12) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on userhierarchy userhierarchy_1 (cost=0.00..19.29 rows=903 width=12) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 3 (cost=0.28..45.23 rows=1,162 width=12) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Index Only Scan using ix4ul_userlocationstartend on userlocation (cost=0.28..30.71 rows=1,162 width=8) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Index Only Scan using ix4ul_userlocationstartend on userlocation userlocation_1 (cost=0.28..33.61 rows=1,162 width=12) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

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

48. 0.000 0.000 ↓ 0.0

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

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

50. 0.000 0.000 ↓ 0.0

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

51. 0.000 0.000 ↓ 0.0

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

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

53. 0.000 0.000 ↓ 0.0

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

54. 0.000 0.000 ↓ 0.0

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

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

56. 0.000 0.000 ↓ 0.0

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

57. 0.000 0.000 ↓ 0.0

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

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

59. 0.000 0.000 ↓ 0.0

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

60. 0.000 0.000 ↓ 0.0

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

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

62. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 15 (cost=0.00..175.56 rows=5,447 width=12) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on userprojectroleratehistory (cost=0.00..107.47 rows=5,447 width=8) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Seq Scan on userprojectroleratehistory userprojectroleratehistory_1 (cost=0.00..121.09 rows=5,447 width=12) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 17 (cost=0.00..168.20 rows=5,431 width=12) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Seq Scan on userpayrollratehistory (cost=0.00..100.31 rows=5,431 width=8) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Seq Scan on userpayrollratehistory userpayrollratehistory_1 (cost=0.00..113.89 rows=5,431 width=12) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 19 (cost=0.28..32.44 rows=915 width=12) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

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

70. 0.000 0.000 ↓ 0.0

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

71.          

CTE allstartenddates

72. 0.000 0.000 ↓ 0.0

WindowAgg (cost=3,755.64..4,535.96 rows=39,016 width=20) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Sort (cost=3,755.64..3,853.18 rows=39,016 width=12) (actual rows= loops=)

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

CTE Scan on alldates main (cost=0.00..780.32 rows=39,016 width=12) (actual rows= loops=)

75.          

Initplan (for CTE Scan)

76. 0.000 0.000 ↓ 0.0

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

77. 0.000 0.000 ↓ 0.0

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

78. 0.000 0.000 ↓ 0.0

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

79. 0.000 0.000 ↓ 0.0

Hash (cost=9.04..9.04 rows=104 width=32) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Seq Scan on project project3 (cost=0.00..9.04 rows=104 width=32) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Hash (cost=1.26..1.26 rows=26 width=638) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Seq Scan on task task4 (cost=0.00..1.26 rows=26 width=638) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

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

84. 0.000 0.000 ↓ 0.0

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

85. 0.000 0.000 ↓ 0.0

Hash (cost=1.25..1.25 rows=25 width=136) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Seq Scan on expensetype expensetype9 (cost=0.00..1.25 rows=25 width=136) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Hash (cost=2.08..2.08 rows=8 width=138) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Seq Scan on clients clients10 (cost=0.00..2.08 rows=8 width=138) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Hash (cost=1,145.17..1,145.17 rows=54,517 width=24) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet timesheet11 (cost=0.00..1,145.17 rows=54,517 width=24) (actual rows= loops=)