explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JWWI

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

Hash Left Join (cost=7,439,742.27..4,077,019,235,257.40 rows=11,973,258,976 width=1,358) (actual rows= loops=)

  • Hash Cond: ((COALESCE(client.id, proj.clientid)) = clients7.id)
2. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7,439,604.69..4,076,854,602,808.90 rows=11,973,258,976 width=1,317) (actual rows= loops=)

  • Hash Cond: (toc.id = timeoffcode6.id)
3. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7,439,602.72..4,076,783,960,578.97 rows=11,973,258,976 width=801) (actual rows= loops=)

  • Hash Cond: (bt.id = breaktype5.id)
4. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7,439,601.67..4,076,737,863,530.86 rows=11,973,258,976 width=565) (actual rows= loops=)

  • Hash Cond: ((CASE WHEN (te_billingrate.uri IS NULL) THEN NULL::uuid WHEN (te_billingrate.uri = 'urn:replicon:project-specific-billing-rate'::text) THEN psi.projectcustombillingrateid WHEN (te_billingrate.uri = 'urn:replicon:user-specific-billing-rate'::text) THEN psi.usercustombillingrateid ELSE ("substring"(te_billingrate.uri, concat('^urn:replicon-tenant:', si.tenantslug, ':company-billing-rate:(.*)$')))::uuid END) = billingrate4.id)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7,439,600.63..4,076,691,766,482.76 rows=11,973,258,976 width=447) (actual rows= loops=)

  • Hash Cond: (task.id = task3.id)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7,439,407.03..4,076,527,133,978.24 rows=11,973,258,976 width=414) (actual rows= loops=)

  • Hash Cond: (proj.id = project2.id)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=7,438,864.56..4,076,362,501,124.85 rows=11,973,258,976 width=388) (actual rows= loops=)

  • Hash Cond: (act.id = activities1.id)
8. 0.000 0.000 ↓ 0.0

Append (cost=7,438,863.49..4,076,315,805,413.77 rows=11,973,258,976 width=152) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=7,438,863.49..4,076,196,072,812.13 rows=11,973,258,975 width=280) (actual rows= loops=)

  • Merge Cond: (te.id = te_isbillable.timeentryid)
10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=7,438,862.93..4,075,723,636,467.01 rows=11,973,258,975 width=279) (actual rows= loops=)

  • Join Filter: (te_client.uri = ((('urn:replicon-tenant:'::text || si.tenantslug) || ':client:'::text) || (client.id)::text))
11. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=7,438,862.93..2,940,419,220,354.41 rows=11,973,258,975 width=335) (actual rows= loops=)

  • Merge Cond: (te.id = te_client.timeentryid)
12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=7,438,862.36..2,940,305,981,778.53 rows=11,973,258,975 width=275) (actual rows= loops=)

  • Join Filter: (te_timeofftype.uri = ((('urn:replicon-tenant:'::text || si.tenantslug) || ':time-off-type:'::text) || (toc.id)::text))
13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=7,438,862.36..2,926,147,603,039.06 rows=11,973,258,975 width=331) (actual rows= loops=)

  • Join Filter: (te_project.uri = ((('urn:replicon-tenant:'::text || si.tenantslug) || ':project:'::text) || (proj.id)::text))
14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=7,438,862.36..946,810,616,944.30 rows=6,414,391,800 width=383) (actual rows= loops=)

  • Join Filter: (te.id = te_timeofftype.timeentryid)
15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=7,438,862.36..946,713,496,446.52 rows=6,414,391,800 width=323) (actual rows= loops=)

  • Join Filter: (te_breaktype.uri = ((('urn:replicon-tenant:'::text || si.tenantslug) || ':break-type:'::text) || (bt.id)::text))
16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=7,438,862.36..946,360,704,896.49 rows=6,414,391,800 width=367) (actual rows= loops=)

  • Join Filter: (te.id = te_breaktype.timeentryid)
17. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=7,438,862.36..946,263,584,398.72 rows=6,414,391,800 width=307) (actual rows= loops=)

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

Nested Loop Left Join (cost=7,438,861.80..946,140,754,244.00 rows=6,414,391,800 width=247) (actual rows= loops=)

  • Join Filter: (te_task.uri = ((('urn:replicon-tenant:'::text || si.tenantslug) || ':task:'::text) || (task.id)::text))
19. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=7,438,861.80..659,402,784.00 rows=6,414,391,800 width=303) (actual rows= loops=)

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

Nested Loop Left Join (cost=6,034,518.39..615,473,550.40 rows=6,414,391,800 width=243) (actual rows= loops=)

  • Join Filter: (te_activity.uri = ((('urn:replicon-tenant:'::text || si.tenantslug) || ':activity:'::text) || (act.id)::text))
21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=6,034,518.39..86,286,225.86 rows=6,414,391,800 width=299) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=6,034,518.39..6,106,301.82 rows=9,296,220 width=235) (actual rows= loops=)

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

Merge Left Join (cost=5,129,897.61..5,178,440.47 rows=9,296,220 width=175) (actual rows= loops=)

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

Sort (cost=4,211,324.68..4,234,565.23 rows=9,296,220 width=136) (actual rows= loops=)

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

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

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

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

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

Seq Scan on timeentrymetadata te_project (cost=0.00..904,620.78 rows=6,712,426 width=76) (actual rows= loops=)

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

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

29. 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))
30. 0.000 0.000 ↓ 0.0

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

31. 0.000 0.000 ↓ 0.0

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

32. 0.000 0.000 ↓ 0.0

Materialize (cost=918,572.93..919,162.00 rows=117,815 width=55) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Sort (cost=918,572.93..918,867.47 rows=117,815 width=55) (actual rows= loops=)

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

Seq Scan on timeentrymetadata te_comment (cost=0.00..904,620.78 rows=117,815 width=55) (actual rows= loops=)

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

Sort (cost=904,620.79..904,620.79 rows=1 width=76) (actual rows= loops=)

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

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

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

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

38. 0.000 0.000 ↓ 0.0

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

39. 0.000 0.000 ↓ 0.0

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

40. 0.000 0.000 ↓ 0.0

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

41. 0.000 0.000 ↓ 0.0

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

42. 0.000 0.000 ↓ 0.0

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

43. 0.000 0.000 ↓ 0.0

Materialize (cost=1,404,343.41..1,417,136.89 rows=2,558,697 width=76) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Sort (cost=1,404,343.41..1,410,740.15 rows=2,558,697 width=76) (actual rows= loops=)

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

Seq Scan on timeentrymetadata te_task (cost=0.00..904,620.78 rows=2,558,697 width=76) (actual rows= loops=)

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

Materialize (cost=0.00..153.40 rows=5,360 width=4) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

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

48. 0.000 0.000 ↓ 0.0

Materialize (cost=0.56..83,311,082.73 rows=2,269,446 width=76) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid on timeentrymetadata te_billingrate (cost=0.56..83,305,409.12 rows=2,269,446 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.00..904,620.78 rows=1 width=76) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_breaktype (cost=0.00..904,620.78 rows=1 width=76) (actual rows= loops=)

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

Materialize (cost=0.00..1.03 rows=2 width=16) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

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

54. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..904,620.78 rows=1 width=76) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata te_timeofftype (cost=0.00..904,620.78 rows=1 width=76) (actual rows= loops=)

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

Materialize (cost=0.00..458.32 rows=11,221 width=8) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

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

58. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.65 rows=43 width=4) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

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

60. 0.000 0.000 ↓ 0.0

Materialize (cost=0.56..83,305,409.12 rows=1 width=76) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid on timeentrymetadata te_client (cost=0.56..83,305,409.12 rows=1 width=76) (actual rows= loops=)

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

Materialize (cost=0.00..111.72 rows=3,448 width=4) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

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

64. 0.000 0.000 ↓ 0.0

Materialize (cost=0.56..83,305,409.12 rows=1 width=17) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid on timeentrymetadata te_isbillable (cost=0.56..83,305,409.12 rows=1 width=17) (actual rows= loops=)

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

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

67. 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)
68. 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=)

69. 0.000 0.000 ↓ 0.0

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

  • Sort Key: at.userid
70. 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)
71. 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)
72. 0.000 0.000 ↓ 0.0

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

73. 0.000 0.000 ↓ 0.0

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

74. 0.000 0.000 ↓ 0.0

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

75. 0.000 0.000 ↓ 0.0

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

76. 0.000 0.000 ↓ 0.0

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

77. 0.000 0.000 ↓ 0.0

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

78. 0.000 0.000 ↓ 0.0

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

79. 0.000 0.000 ↓ 0.0

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

80. 0.000 0.000 ↓ 0.0

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

81. 0.000 0.000 ↓ 0.0

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

82. 0.000 0.000 ↓ 0.0

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

83. 0.000 0.000 ↓ 0.0

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

84. 0.000 0.000 ↓ 0.0

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

85. 0.000 0.000 ↓ 0.0

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