explain.depesz.com

PostgreSQL's explain analyze made readable

Result: r1tS

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

Sort (cost=39,792,021,383.30..39,797,927,775.40 rows=2,362,556,840 width=4,045) (actual rows= loops=)

  • Sort Key: ((pj.code)::character varying(50)) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US", ((ui.externalid)::character varying(255)) COLLATE "en_US", ((ui.lastname)::character varying(50)) COLLATE "en_US", ((ui.firstname)::character varying(50)) COLLATE "en_US", ui.duplicatename, ((login.loginname)::character varying(255)) COLLATE "en_US", ((dep.name)::character varying(255)) COLLATE "en_US", "*SELECT* 1".timeentryid, "*SELECT* 1".entrydate, ts.enddate, ((timeentrymetadata7.text)::text) COLLATE "en_US", ((cl.name)::character varying(255)) COLLATE "en_US", ((cl.code)::character varying(50)) COLLATE "en_US", ((userinfo9.displayname)::text) COLLATE "en_US", ((userinfo10.displayname)::text) COLLATE "en_US", ((billingrate11.name)::character varying(50)) COLLATE "en_US", ((tdh.hierarchysorting)::text) COLLATE "en_US", ((tdh.hierarchytaskname)::text) COLLATE "en_US
2.          

Initplan (forSort)

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=12,420,925,438.95..14,762,704,222.34 rows=2,362,556,840 width=4,045) (actual rows= loops=)

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

Hash Left Join (cost=12,420,925,426.47..14,756,432,780.80 rows=2,362,556,840 width=870) (actual rows= loops=)

  • Hash Cond: (pj.projectleaderapproverid = userinfo10.id)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=12,420,925,386.11..14,750,204,653.40 rows=2,362,556,840 width=860) (actual rows= loops=)

  • Hash Cond: (cl.clientmanageruserid = userinfo9.id)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=12,420,925,345.74..14,744,002,901.33 rows=2,362,556,840 width=850) (actual rows= loops=)

  • Hash Cond: (COALESCE(clients18.id, pj.clientid) = cl.id)
8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=12,420,925,343.26..14,737,669,769.92 rows=2,362,556,840 width=835) (actual rows= loops=)

  • Hash Cond: (tk.id = tdh.taskid)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=12,420,919,935.75..14,270,024,531.52 rows=2,362,556,840 width=775) (actual rows= loops=)

  • Hash Cond: (CASE WHEN ("substring"(timeentrymetadata17.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:client:(.*)$'::text) ~ '^\s*[-]?[0-9]+\s*$'::text) THEN ("substring"(timeentrymetadata17.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:client:(.*)$'::text))::integer ELSE NULL::integer END = clients18.id)
10. 0.000 0.000 ↓ 0.0

Hash Join (cost=12,420,919,933.27..14,262,148,355.17 rows=2,362,556,840 width=828) (actual rows= loops=)

  • Hash Cond: (pj.id = project19.id)
11. 0.000 0.000 ↓ 0.0

Hash Join (cost=12,420,919,791.78..14,217,658,734.90 rows=16,922,500,154 width=828) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".userid = ui.id)
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=12,420,919,672.49..13,984,974,238.49 rows=16,922,500,154 width=282) (actual rows= loops=)

  • Hash Cond: (task16.id = tk.id)
13. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=12,420,915,193.12..12,562,421,495.00 rows=16,922,500,154 width=257) (actual rows= loops=)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata17.timeentryid)
14. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=12,420,740,862.08..12,512,647,138.22 rows=16,922,500,154 width=200) (actual rows= loops=)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata7.timeentryid)
15. 0.000 0.000 ↓ 0.0

Sort (cost=12,420,566,531.05..12,462,872,781.43 rows=16,922,500,154 width=156) (actual rows= loops=)

  • Sort Key: "*SELECT* 1".timeentryid
16. 0.000 0.000 ↓ 0.0

Hash Join (cost=391,192,637.99..2,893,917,721.36 rows=16,922,500,154 width=156) (actual rows= loops=)

  • Hash Cond: (COALESCE(task16.projectid, project14.id) = pj.id)
17. 0.000 0.000 ↓ 0.0

Hash Join (cost=391,192,473.62..2,849,428,078.20 rows=16,922,500,154 width=96) (actual rows= loops=)

  • Hash Cond: (ts.id = tslist.timesheetid)
18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=391,179,979.67..2,241,716,420.37 rows=16,961,450,690 width=108) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=391,179,979.25..399,633,605.21 rows=1,010,954,881 width=84) (actual rows= loops=)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata13.timeentryid)
20. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=391,005,443.86..396,495,950.45 rows=1,010,954,881 width=80) (actual rows= loops=)

  • Merge Cond: ("*SELECT* 1".timeentryid = timeentrymetadata15.timeentryid)
21. 0.000 0.000 ↓ 0.0

Sort (cost=390,826,515.42..393,353,902.62 rows=1,010,954,881 width=72) (actual rows= loops=)

  • Sort Key: "*SELECT* 1".timeentryid
22. 0.000 0.000 ↓ 0.0

Append (cost=348,662.50..73,762,895.40 rows=1,010,954,881 width=72) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=348,662.50..73,762,893.36 rows=1,010,954,880 width=72) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=348,662.50..63,653,344.56 rows=1,010,954,880 width=132) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=348,662.50..468,645.94 rows=1,465,152 width=202) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

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

27. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=348,662.50..453,993.41 rows=1,465,152 width=170) (actual rows= loops=)

  • Merge Cond: (te.id = temdbreaktype.timeentryid)
28. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=174,331.46..275,520.63 rows=1,465,152 width=113) (actual rows= loops=)

  • Merge Cond: (te.id = tembillingrate.timeentryid)
29. 0.000 0.000 ↓ 0.0

Index Scan using timeentry_pkey on timeentry te (cost=0.43..97,047.86 rows=1,465,152 width=56) (actual rows= loops=)

  • Filter: ((entrydate IS NOT NULL) AND ((timeallocationtype <> 2) OR (timeallocationtype IS NULL)))
30. 0.000 0.000 ↓ 0.0

Sort (cost=174,331.03..174,410.84 rows=31,924 width=73) (actual rows= loops=)

  • Sort Key: tembillingrate.timeentryid
31. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..171,942.74 rows=31,924 width=73) (actual rows= loops=)

  • Workers Planned: 2
32. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timeentrymetadata tembillingrate (cost=0.00..167,750.34 rows=13,302 width=73) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BILLING-RATE'::text)
33. 0.000 0.000 ↓ 0.0

Sort (cost=174,331.03..174,410.84 rows=31,924 width=73) (actual rows= loops=)

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

Gather (cost=1,000.00..171,942.74 rows=31,924 width=73) (actual rows= loops=)

  • Workers Planned: 2
35. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timeentrymetadata temdbreaktype (cost=0.00..167,750.34 rows=13,302 width=73) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text)
36. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..20.35 rows=690 width=32) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

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

38. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.00..2.04 rows=1 width=72) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.00..2.03 rows=1 width=132) (actual rows= loops=)

  • Join Filter: (toe.timeoffid = toff.id)
40. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffentries toe (cost=0.00..1.01 rows=1 width=24) (actual rows= loops=)

  • Filter: (entrydate IS NOT NULL)
41. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffs toff (cost=0.00..1.01 rows=1 width=8) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Sort (cost=178,928.44..179,008.25 rows=31,924 width=24) (actual rows= loops=)

  • Sort Key: timeentrymetadata15.timeentryid
43. 0.000 0.000 ↓ 0.0

Gather (cost=4,880.50..176,540.15 rows=31,924 width=24) (actual rows= loops=)

  • Workers Planned: 2
44. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=3,880.50..172,347.75 rows=13,302 width=24) (actual rows= loops=)

  • Hash Cond: (("substring"(timeentrymetadata15.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:task:(.*)$'::text))::integer = task16.id)
45. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timeentrymetadata timeentrymetadata15 (cost=0.00..167,750.34 rows=13,302 width=73) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text)
46. 0.000 0.000 ↓ 0.0

Hash (cost=2,457.89..2,457.89 rows=86,689 width=8) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Seq Scan on task task16 (cost=0.00..2,457.89 rows=86,689 width=8) (actual rows= loops=)

48. 0.000 0.000 ↓ 0.0

Sort (cost=174,535.38..174,615.19 rows=31,924 width=20) (actual rows= loops=)

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

Gather (cost=1,164.37..172,147.09 rows=31,924 width=20) (actual rows= loops=)

  • Workers Planned: 2
50. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=164.37..167,954.69 rows=13,302 width=20) (actual rows= loops=)

  • Hash Cond: (("substring"(timeentrymetadata13.uri, '^urn:replicon-tenant:d39c603ba5ee46da9a42069eec872cf2:project:(.*)$'::text))::integer = project14.id)
51. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timeentrymetadata timeentrymetadata13 (cost=0.00..167,750.34 rows=13,302 width=73) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:PROJECT'::text)
52. 0.000 0.000 ↓ 0.0

Hash (cost=129.72..129.72 rows=2,772 width=4) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Seq Scan on project project14 (cost=0.00..129.72 rows=2,772 width=4) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Index Scan using ixtsuseridstartdateenddate on timesheet ts (cost=0.42..1.65 rows=17 width=28) (actual rows= loops=)

  • Index Cond: ((userid = "*SELECT* 1".userid) AND ("*SELECT* 1".entrydate >= startdate) AND ("*SELECT* 1".entrydate <= enddate))
55. 0.000 0.000 ↓ 0.0

Hash (cost=10,020.41..10,020.41 rows=134,683 width=20) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Seq Scan on dm_timesheetlist_facts tslist (cost=0.00..10,020.41 rows=134,683 width=20) (actual rows= loops=)

  • Filter: (timesheetstatus = 2)
57. 0.000 0.000 ↓ 0.0

Hash (cost=129.72..129.72 rows=2,772 width=68) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Seq Scan on project pj (cost=0.00..129.72 rows=2,772 width=68) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Sort (cost=174,331.03..174,410.84 rows=31,924 width=60) (actual rows= loops=)

  • Sort Key: timeentrymetadata7.timeentryid
60. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..171,942.74 rows=31,924 width=60) (actual rows= loops=)

  • Workers Planned: 2
61. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timeentrymetadata timeentrymetadata7 (cost=0.00..167,750.34 rows=13,302 width=60) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:COMMENTS'::text)
62. 0.000 0.000 ↓ 0.0

Sort (cost=174,331.03..174,410.84 rows=31,924 width=73) (actual rows= loops=)

  • Sort Key: timeentrymetadata17.timeentryid
63. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..171,942.74 rows=31,924 width=73) (actual rows= loops=)

  • Workers Planned: 2
64. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timeentrymetadata timeentrymetadata17 (cost=0.00..167,750.34 rows=13,302 width=73) (actual rows= loops=)

  • Filter: (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:CLIENT'::text)
65. 0.000 0.000 ↓ 0.0

Hash (cost=2,802.76..2,802.76 rows=86,689 width=29) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2.12..2,802.76 rows=86,689 width=29) (actual rows= loops=)

  • Hash Cond: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
67. 0.000 0.000 ↓ 0.0

Seq Scan on task tk (cost=0.00..2,457.89 rows=86,689 width=33) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Hash (cost=2.10..2.10 rows=1 width=4) (actual rows= loops=)

69. 0.000 0.000 ↓ 0.0

Seq Scan on exchangerate (cost=0.00..2.10 rows=1 width=4) (actual rows= loops=)

  • Filter: ((variablecurrencyid = $0) AND (CURRENT_DATE >= effectivedate) AND (CURRENT_DATE <= enddate))
70. 0.000 0.000 ↓ 0.0

Hash (cost=106.87..106.87 rows=994 width=558) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash Join (cost=82.85..106.87 rows=994 width=558) (actual rows= loops=)

  • Hash Cond: (du.departmentid = dep.id)
72. 0.000 0.000 ↓ 0.0

Hash Join (cost=80.73..101.91 rows=994 width=46) (actual rows= loops=)

  • Hash Cond: (ui.id = login.userid)
73. 0.000 0.000 ↓ 0.0

Hash Join (cost=40.36..58.93 rows=994 width=34) (actual rows= loops=)

  • Hash Cond: (du.userid = ui.id)
74. 0.000 0.000 ↓ 0.0

Seq Scan on departmentusers du (cost=0.00..15.94 rows=994 width=8) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Hash (cost=27.94..27.94 rows=994 width=26) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..27.94 rows=994 width=26) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Hash (cost=27.94..27.94 rows=994 width=12) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Seq Scan on login (cost=0.00..27.94 rows=994 width=12) (actual rows= loops=)

79. 0.000 0.000 ↓ 0.0

Hash (cost=1.50..1.50 rows=50 width=520) (actual rows= loops=)

80. 0.000 0.000 ↓ 0.0

Seq Scan on departments dep (cost=0.00..1.50 rows=50 width=520) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Hash (cost=136.65..136.65 rows=387 width=4) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Seq Scan on project project19 (cost=0.00..136.65 rows=387 width=4) (actual rows= loops=)

  • Filter: (projectstatuslabelid = 'f6918709-d5cf-436b-b0ff-2191cd2dd6a9'::uuid)
83. 0.000 0.000 ↓ 0.0

Hash (cost=1.66..1.66 rows=66 width=4) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Seq Scan on clients clients18 (cost=0.00..1.66 rows=66 width=4) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Hash (cost=3,391.89..3,391.89 rows=86,689 width=64) (actual rows= loops=)

86. 0.000 0.000 ↓ 0.0

Seq Scan on taskdenormalizedhierarchy tdh (cost=0.00..3,391.89 rows=86,689 width=64) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Hash (cost=1.66..1.66 rows=66 width=23) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Seq Scan on clients cl (cost=0.00..1.66 rows=66 width=23) (actual rows= loops=)

89. 0.000 0.000 ↓ 0.0

Hash (cost=27.94..27.94 rows=994 width=18) (actual rows= loops=)

90. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo userinfo9 (cost=0.00..27.94 rows=994 width=18) (actual rows= loops=)

91. 0.000 0.000 ↓ 0.0

Hash (cost=27.94..27.94 rows=994 width=18) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo userinfo10 (cost=0.00..27.94 rows=994 width=18) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Hash (cost=7.77..7.77 rows=377 width=41) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Seq Scan on billingrate billingrate11 (cost=0.00..7.77 rows=377 width=41) (actual rows= loops=)