explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lQSQ

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

Hash Left Join (cost=9,165,719.95..3,461,619,304.20 rows=6,415,705,301 width=1,355) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".clientid = clients7.id)
2. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,165,582.37..3,373,403,218.73 rows=6,415,705,301 width=1,314) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".timeofftypeid = timeoffcode6.id)
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,165,580.40..3,335,550,555.49 rows=6,415,705,301 width=798) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".breaktypeid = breaktype5.id)
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,165,579.36..3,310,850,089.03 rows=6,415,705,301 width=562) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".billingrateid = billingrate4.id)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,165,578.31..3,286,149,622.58 rows=6,415,705,301 width=444) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".taskid = task3.id)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,165,384.71..3,197,933,481.09 rows=6,415,705,301 width=411) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".projectid = project2.id)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,164,842.24..3,109,716,990.73 rows=6,415,705,301 width=385) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".activityid = activities1.id)
8. 0.000 0.000 ↓ 0.0

Append (cost=9,164,841.17..3,084,695,738.98 rows=6,415,705,301 width=149) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=9,164,841.17..3,084,356,523.98 rows=6,414,391,800 width=149) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,164,841.17..3,020,212,605.98 rows=6,414,391,800 width=277) (actual rows= loops=)

  • Hash Cond: (te.id = te_isbillable.timeentryid)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=8,468,205.51..1,199,432,290.17 rows=6,414,391,800 width=276) (actual rows= loops=)

  • Hash Cond: (("substring"(te_client.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':client:(.*)$')))::integer = client.id)
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=8,468,067.93..1,079,162,306.34 rows=6,414,391,800 width=332) (actual rows= loops=)

  • Hash Cond: (te.id = te_client.timeentryid)
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7,771,432.27..1,054,411,694.53 rows=6,414,391,800 width=272) (actual rows= loops=)

  • Hash Cond: (("substring"(te_timeofftype.uri, concat('urn:replicon-tenant:', si.tenantslug, ':time-off-type:(.*)$')))::integer = toc.id)
14. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7,771,430.30..984,494,821.94 rows=6,414,391,800 width=328) (actual rows= loops=)

  • Hash Cond: (("substring"(te_breaktype.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':break-type:(.*)$')))::uuid = bt.id)
15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7,771,429.25..927,727,453.47 rows=6,414,391,800 width=372) (actual rows= loops=)

  • Hash Cond: (("substring"(te_task.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':task:(.*)$')))::integer = task.id)
16. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7,771,235.65..807,457,413.62 rows=6,414,391,800 width=428) (actual rows= loops=)

  • Hash Cond: (("substring"(te_project.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':project:(.*)$')))::integer = proj.id)
17. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=7,770,693.18..687,187,024.89 rows=6,414,391,800 width=480) (actual rows= loops=)

  • Merge Cond: (te.id = te_timeofftype.timeentryid)
18. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=7,770,692.62..670,027,039.68 rows=6,414,391,800 width=420) (actual rows= loops=)

  • Merge Cond: (te.id = te_breaktype.timeentryid)
19. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=7,770,692.06..652,867,054.47 rows=6,414,391,800 width=360) (actual rows= loops=)

  • Merge Cond: (te.id = te_billingrate.timeentryid)
20. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=6,628,161.37..611,956,473.28 rows=6,414,391,800 width=300) (actual rows= loops=)

  • Merge Cond: (te.id = te_task.timeentryid)
21. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5,402,978.73..566,734,223.19 rows=6,414,391,800 width=240) (actual rows= loops=)

  • Join Filter: (("substring"(te_activity.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':activity:(.*)$')))::integer = act.id)
22. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5,402,978.73..85,654,837.15 rows=6,414,391,800 width=296) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=5,402,978.73..5,474,913.12 rows=9,296,220 width=232) (actual rows= loops=)

  • Merge Cond: (te.id = te_activity.timeentryid)
24. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=4,706,343.07..4,755,036.89 rows=9,296,220 width=172) (actual rows= loops=)

  • Merge Cond: (te.id = te_comment.timeentryid)
25. 0.000 0.000 ↓ 0.0

Sort (cost=3,994,670.25..4,017,910.80 rows=9,296,220 width=136) (actual rows= loops=)

  • Sort Key: te.id
26. 0.000 0.000 ↓ 0.0

Hash Join (cost=402,241.37..1,647,746.85 rows=9,296,220 width=136) (actual rows= loops=)

  • Hash Cond: (te.userid = ui.id)
27. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=401,844.50..1,519,526.96 rows=9,296,220 width=120) (actual rows= loops=)

  • Hash Cond: (te_project.timeentryid = te.id)
28. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_project (cost=0.00..696,635.65 rows=6,536,034 width=76) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:project'::text)
29. 0.000 0.000 ↓ 0.0

Hash (cost=185,779.75..185,779.75 rows=9,296,220 width=60) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Seq Scan on timeentry te (cost=0.00..185,779.75 rows=9,296,220 width=60) (actual rows= loops=)

  • Filter: ((timeallocationtype <> 2) OR (timeallocationtype IS NULL))
31. 0.000 0.000 ↓ 0.0

Hash (cost=299.72..299.72 rows=7,772 width=20) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..299.72 rows=7,772 width=20) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Materialize (cost=711,672.83..712,305.03 rows=126,441 width=52) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Sort (cost=711,672.83..711,988.93 rows=126,441 width=52) (actual rows= loops=)

  • Sort Key: te_comment.timeentryid
35. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_comment (cost=0.00..696,635.65 rows=126,441 width=52) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:comments'::text)
36. 0.000 0.000 ↓ 0.0

Sort (cost=696,635.66..696,635.67 rows=1 width=76) (actual rows= loops=)

  • Sort Key: te_activity.timeentryid
37. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_activity (cost=0.00..696,635.65 rows=1 width=76) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:activity'::text)
38. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..28.26 rows=690 width=64) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..24.81 rows=690 width=64) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

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

41. 0.000 0.000 ↓ 0.0

Seq Scan on projectsysteminformation psi (cost=0.00..16.90 rows=690 width=32) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.04 rows=3 width=4) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Seq Scan on activities act (cost=0.00..1.03 rows=3 width=4) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Materialize (cost=1,225,182.64..1,238,687.15 rows=2,700,902 width=76) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Sort (cost=1,225,182.64..1,231,934.89 rows=2,700,902 width=76) (actual rows= loops=)

  • Sort Key: te_task.timeentryid
46. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_task (cost=0.00..696,635.65 rows=2,700,902 width=76) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:task'::text)
47. 0.000 0.000 ↓ 0.0

Materialize (cost=1,142,530.68..1,153,992.68 rows=2,292,400 width=76) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Sort (cost=1,142,530.68..1,148,261.68 rows=2,292,400 width=76) (actual rows= loops=)

  • Sort Key: te_billingrate.timeentryid
49. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_billingrate (cost=0.00..696,635.65 rows=2,292,400 width=76) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:billing-rate'::text)
50. 0.000 0.000 ↓ 0.0

Materialize (cost=0.56..1,123,995.36 rows=1 width=76) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid2 on timeentrymetadata te_breaktype (cost=0.56..1,123,995.36 rows=1 width=76) (actual rows= loops=)

  • Index Cond: (key = 'urn:replicon:time-entry-metadata-key:break-type'::text)
52. 0.000 0.000 ↓ 0.0

Materialize (cost=0.56..1,123,995.36 rows=1 width=76) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid2 on timeentrymetadata te_timeofftype (cost=0.56..1,123,995.36 rows=1 width=76) (actual rows= loops=)

  • Index Cond: (key = 'urn:replicon:time-entry-metadata-key:time-off-type'::text)
54. 0.000 0.000 ↓ 0.0

Hash (cost=402.21..402.21 rows=11,221 width=8) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Seq Scan on project proj (cost=0.00..402.21 rows=11,221 width=8) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Hash (cost=126.60..126.60 rows=5,360 width=4) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Seq Scan on task (cost=0.00..126.60 rows=5,360 width=4) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

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

59. 0.000 0.000 ↓ 0.0

Seq Scan on breaktype bt (cost=0.00..1.02 rows=2 width=16) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Hash (cost=1.43..1.43 rows=43 width=4) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffcode toc (cost=0.00..1.43 rows=43 width=4) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Hash (cost=696,635.65..696,635.65 rows=1 width=76) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_client (cost=0.00..696,635.65 rows=1 width=76) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:client'::text)
64. 0.000 0.000 ↓ 0.0

Hash (cost=94.48..94.48 rows=3,448 width=4) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Seq Scan on clients client (cost=0.00..94.48 rows=3,448 width=4) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Hash (cost=696,635.65..696,635.65 rows=1 width=17) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_isbillable (cost=0.00..696,635.65 rows=1 width=17) (actual rows= loops=)

  • Filter: (key = 'urn:replicon:time-entry-metadata-key:is-billable'::text)
68. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=3.69..11.88 rows=1 width=118) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.69..11.87 rows=1 width=109) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Merge Join (cost=3.41..3.56 rows=1 width=105) (actual rows= loops=)

  • Merge Cond: (ui_1.id = at.userid)
71. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo ui_1 (cost=0.28..1,038.24 rows=7,772 width=20) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Sort (cost=2.99..2.99 rows=1 width=89) (actual rows= loops=)

  • Sort Key: at.userid
73. 0.000 0.000 ↓ 0.0

Seq Scan on allocatedtime at (cost=0.00..2.98 rows=1 width=89) (actual rows= loops=)

  • Filter: (totimestamputc IS NULL)
74. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj (cost=0.29..8.30 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (at.projectid = id)
75. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 3 (cost=398.37..39,650.89 rows=348,360 width=151) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Hash Join (cost=398.37..36,167.29 rows=348,360 width=98) (actual rows= loops=)

  • Hash Cond: (toff.userid = ui_2.id)
77. 0.000 0.000 ↓ 0.0

Merge Join (cost=1.50..30,980.47 rows=348,360 width=82) (actual rows= loops=)

  • Merge Cond: (toff.id = toe.timeoffid)
78. 0.000 0.000 ↓ 0.0

Index Scan using timeoffs_pkey on timeoffs toff (cost=0.42..12,512.43 rows=264,415 width=50) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Index Scan using ixtoetimeoffid on timeoffentries toe (cost=0.42..13,453.20 rows=348,360 width=40) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Hash (cost=299.72..299.72 rows=7,772 width=20) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui_2 (cost=0.00..299.72 rows=7,772 width=20) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 4 (cost=162,498.93..299,552.23 rows=965,140 width=145) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=162,498.93..289,900.83 rows=965,140 width=152) (actual rows= loops=)

  • Hash Cond: (COALESCE(tp_start.projectid, tp_end.projectid) = proj_1.id)
84. 0.000 0.000 ↓ 0.0

Hash Join (cost=161,956.46..271,261.98 rows=965,140 width=148) (actual rows= loops=)

  • Hash Cond: (punch_pair.userid = ui_3.id)
85. 0.000 0.000 ↓ 0.0

Hash Join (cost=161,559.59..257,594.43 rows=965,140 width=132) (actual rows= loops=)

  • Hash Cond: (tp_start.id = punch_pair.starttimepunchid)
86. 0.000 0.000 ↓ 0.0

Seq Scan on timepunch tp_start (cost=0.00..34,396.05 rows=1,392,105 width=72) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Hash (cost=135,355.94..135,355.94 rows=965,172 width=92) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Hash Join (cost=44,029.78..135,355.94 rows=965,172 width=92) (actual rows= loops=)

  • Hash Cond: (tp_end.id = punch_pair.endtimepunchid)
89. 0.000 0.000 ↓ 0.0

Seq Scan on timepunch tp_end (cost=0.00..34,396.05 rows=1,392,105 width=72) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Hash (cost=22,529.90..22,529.90 rows=965,590 width=52) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Seq Scan on denormalizedtimepunchpair punch_pair (cost=0.00..22,529.90 rows=965,590 width=52) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Hash (cost=299.72..299.72 rows=7,772 width=20) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui_3 (cost=0.00..299.72 rows=7,772 width=20) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Hash (cost=402.21..402.21 rows=11,221 width=8) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Seq Scan on project proj_1 (cost=0.00..402.21 rows=11,221 width=8) (actual rows= loops=)

96. 0.000 0.000 ↓ 0.0

Hash (cost=1.03..1.03 rows=3 width=240) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Seq Scan on activities activities1 (cost=0.00..1.03 rows=3 width=240) (actual rows= loops=)

98. 0.000 0.000 ↓ 0.0

Hash (cost=402.21..402.21 rows=11,221 width=30) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

Seq Scan on project project2 (cost=0.00..402.21 rows=11,221 width=30) (actual rows= loops=)

100. 0.000 0.000 ↓ 0.0

Hash (cost=126.60..126.60 rows=5,360 width=37) (actual rows= loops=)

101. 0.000 0.000 ↓ 0.0

Seq Scan on task task3 (cost=0.00..126.60 rows=5,360 width=37) (actual rows= loops=)

102. 0.000 0.000 ↓ 0.0

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

103. 0.000 0.000 ↓ 0.0

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

104. 0.000 0.000 ↓ 0.0

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

105. 0.000 0.000 ↓ 0.0

Seq Scan on breaktype breaktype5 (cost=0.00..1.02 rows=2 width=252) (actual rows= loops=)

106. 0.000 0.000 ↓ 0.0

Hash (cost=1.43..1.43 rows=43 width=520) (actual rows= loops=)

107. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffcode timeoffcode6 (cost=0.00..1.43 rows=43 width=520) (actual rows= loops=)

108. 0.000 0.000 ↓ 0.0

Hash (cost=94.48..94.48 rows=3,448 width=45) (actual rows= loops=)

109. 0.000 0.000 ↓ 0.0

Seq Scan on clients clients7 (cost=0.00..94.48 rows=3,448 width=45) (actual rows= loops=)