explain.depesz.com

PostgreSQL's explain analyze made readable

Result: c7CU

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

Sort (cost=10,431,980.86..10,444,687.70 rows=5,082,736 width=4,302) (actual rows= loops=)

  • Sort Key: "*SELECT* 1".timeentryid, ((ui.externalid)::character varying(255)) COLLATE "en_US", "*SELECT* 1".entrydate, "*SELECT* 1".intime, "*SELECT* 1".outtime, ((timeentrymetadata2.text)::text) COLLATE "en_US", ((billingrate3.name)::character varying(50)) COLLATE "en_US", ((pj.code)::character varying(50)) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US", ((tk.name)::character varying(255)) COLLATE "en_US", ((tdh_1.hierarchysorting)::text) COLLATE "en_US", ((tdh_1.hierarchytaskname)::text) COLLATE "en_US", (CASE WHEN tdh.effectivelyenabled THEN 0 ELSE 1 END), tk.timeentrystartdate, tk.timeentryenddate, tk.timeandexpenseentrytype, (COALESCE(tk.costtype, (SubPlan 1))), ((tk.description)::character varying(255)) COLLATE "en_US", ((SubPlan 2)), ((SubPlan 3)), ((activities7.name)::character varying(50)) COLLATE "en_US", ((activities7.code)::character varying(50)) COLLATE "en_US", ((activities7.description)::character varying(255)) COLLATE "en_US", ((breaktype8.name)::character varying(50)) COLLATE "en_US", ((breaktype8.code)::character varying(50)) COLLATE "en_US", ((breaktype8.description)::character varying(255)) COLLATE "en_US", ts.startdate, ts.enddate, tslist.timesheetstatus, tah.timestamputc, ((tah.approvalcomments)::text) COLLATE "en_US", ((timeoffcode13.name)::character varying(255)) COLLATE "en_US
2.          

Initplan (for Sort)

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=1,048,031.81..4,495,208.88 rows=5,082,736 width=4,302) (actual rows= loops=)

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

Hash Left Join (cost=1,048,025.08..4,481,577.23 rows=5,082,736 width=2,088) (actual rows= loops=)

  • Hash Cond: (tah.id = sheetapprovalhistorykeyvalue12.timesheetapprovalhistoryid)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=867,937.64..2,187,493.95 rows=5,082,736 width=2,032) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".userid = ts.userid)
  • Join Filter: (("*SELECT* 1".entrydate >= ts.startdate) AND ("*SELECT* 1".entrydate <= ts.enddate))
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=765,317.70..1,318,394.89 rows=1,259,761 width=1,866) (actual rows= loops=)

  • Hash Cond: (CASE WHEN ("substring"(timeentrymetadata19.uri, '^urn:replicon-tenant:fc6869a7e8484f8a85a64b398d092338:break-type:(.*)$'::text) ~* '^[0-9A-F]{8}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{4}-[0-9A-F]{12}$'::text) THEN ("substring"(timeentrymetadata19.uri, '^urn:replicon-tenant:fc6869a7e8484f8a85a64b398d092338:break-type:(.*)$'::text))::uuid ELSE NULL::uuid END = breaktype8.id)
8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=765,316.67..1,314,194.13 rows=1,259,761 width=1,180) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".timeentryid = timeentrymetadata19.timeentryid)
9. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=668,480.09..1,126,882.43 rows=1,259,761 width=1,114) (actual rows= loops=)

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

Hash Left Join (cost=668,473.36..1,122,681.00 rows=1,259,761 width=512) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".timeentryid = timeentrymetadata18.timeentryid)
11. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=537,922.17..890,222.57 rows=1,259,761 width=446) (actual rows= loops=)

  • Hash Cond: (COALESCE(project15.id, task17.projectid) = pj.id)
12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=537,781.21..886,769.29 rows=1,259,761 width=442) (actual rows= loops=)

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

Hash Left Join (cost=494,999.38..826,665.76 rows=1,259,761 width=277) (actual rows= loops=)

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

Hash Left Join (cost=494,583.39..822,153.73 rows=1,259,761 width=335) (actual rows= loops=)

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

Hash Join (cost=494,442.43..817,910.36 rows=1,259,761 width=397) (actual rows= loops=)

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

Hash Left Join (cost=494,351.73..814,505.73 rows=1,259,761 width=386) (actual rows= loops=)

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

Merge Left Join (cost=494,350.68..811,127.73 rows=1,259,761 width=284) (actual rows= loops=)

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

Merge Left Join (cost=494,350.13..653,555.95 rows=1,259,761 width=218) (actual rows= loops=)

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

Merge Left Join (cost=494,349.57..506,302.99 rows=1,259,761 width=194) (actual rows= loops=)

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

Sort (cost=348,573.48..351,722.88 rows=1,259,761 width=128) (actual rows= loops=)

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

Append (cost=25,542.06..220,929.97 rows=1,259,761 width=128) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=25,542.06..195,441.27 rows=1,259,700 width=128) (actual rows= loops=)

  • Filter: ((hashed SubPlan 5) OR (hashed SubPlan 6))
23. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,036.03..145,741.25 rows=1,679,600 width=140) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,036.03..11,355.12 rows=2,584 width=220) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

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

26. 0.000 0.000 ↓ 0.0

Gather (cost=1,036.03..11,328.27 rows=2,584 width=188) (actual rows= loops=)

  • Workers Planned: 1
27. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=36.03..10,069.87 rows=1,520 width=188) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=35.48..6,214.20 rows=1,520 width=122) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on timeentry te (cost=34.92..2,358.53 rows=1,520 width=56) (actual rows= loops=)

  • Recheck Cond: ((entrydate >= '2020-09-09'::date) AND (entrydate <= '2020-09-09'::date))
  • Filter: ((timeallocationtype <> 2) OR (timeallocationtype IS NULL))
30. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on ixte2entrydate (cost=0.00..34.27 rows=2,585 width=0) (actual rows= loops=)

  • Index Cond: ((entrydate >= '2020-09-09'::date) AND (entrydate <= '2020-09-09'::date))
31. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata tembillingrate (cost=0.56..2.53 rows=1 width=82) (actual rows= loops=)

  • Index Cond: ((timeentryid = te.id) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BILLING-RATE'::text))
32. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata temdbreaktype (cost=0.56..2.53 rows=1 width=82) (actual rows= loops=)

  • Index Cond: ((timeentryid = te.id) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text))
33. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..19.75 rows=650 width=32) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

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

35.          

SubPlan (for Subquery Scan)

36. 0.000 0.000 ↓ 0.0

Seq Scan on timeentry timeentry20 (cost=0.00..20,594.64 rows=1,028,664 width=16) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffs timeoffs21 (cost=0.00..1,241.58 rows=39,258 width=4) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=24,506.60..25,488.70 rows=61 width=128) (actual rows= loops=)

  • Filter: ((hashed SubPlan 5) OR (hashed SubPlan 6))
39. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.58..981.46 rows=81 width=140) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Index Scan using uix2toe_timeoffidentrydate on timeoffentries toe (cost=0.29..818.55 rows=81 width=28) (actual rows= loops=)

  • Index Cond: ((entrydate >= '2020-09-09'::date) AND (entrydate <= '2020-09-09'::date))
41. 0.000 0.000 ↓ 0.0

Index Scan using timeoffs_pkey on timeoffs toff (cost=0.29..2.01 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = toe.timeoffid)
42. 0.000 0.000 ↓ 0.0

Sort (cost=145,776.09..146,526.90 rows=300,322 width=82) (actual rows= loops=)

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

Seq Scan on timeentrymetadata timeentrymetadata16 (cost=0.00..118,452.57 rows=300,322 width=82) (actual rows= loops=)

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

Materialize (cost=0.56..138,976.65 rows=313,986 width=40) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata timeentrymetadata2 (cost=0.56..138,191.69 rows=313,986 width=40) (actual rows= loops=)

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

Materialize (cost=0.56..143,450.88 rows=671,924 width=82) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata timeentrymetadata14 (cost=0.56..141,771.07 rows=671,924 width=82) (actual rows= loops=)

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

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

49. 0.000 0.000 ↓ 0.0

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

50. 0.000 0.000 ↓ 0.0

Hash (cost=65.87..65.87 rows=1,987 width=11) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on userinfo ui (cost=0.00..65.87 rows=1,987 width=11) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Hash (cost=108.76..108.76 rows=2,576 width=4) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Seq Scan on project project15 (cost=0.00..108.76 rows=2,576 width=4) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Hash (cost=270.44..270.44 rows=11,644 width=8) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Seq Scan on task task17 (cost=0.00..270.44 rows=11,644 width=8) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Hash (cost=42,636.27..42,636.27 rows=11,644 width=169) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1.00..42,636.27 rows=11,644 width=169) (actual rows= loops=)

  • Merge Cond: (tk.id = tdh_1.taskid)
58. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=0.72..42,002.76 rows=11,644 width=129) (actual rows= loops=)

  • Merge Cond: (tk.id = tdh.taskid)
59. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.43..41,369.24 rows=11,644 width=125) (actual rows= loops=)

  • Join Filter: (tk.estimatedcostcurrencyid = exchangerate.fixedcurrencyid)
60. 0.000 0.000 ↓ 0.0

Index Scan using task_pkey on task tk (cost=0.29..41,192.41 rows=11,644 width=129) (actual rows= loops=)

61.          

SubPlan (for Index Scan)

62. 0.000 0.000 ↓ 0.0

Index Scan using project_pkey on project pj_1 (cost=0.28..2.30 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = tk.projectid)
63. 0.000 0.000 ↓ 0.0

Limit (cost=0.43..0.60 rows=1 width=4) (actual rows= loops=)

64. 0.000 0.000 ↓ 0.0

Index Only Scan using ixpta2taskid on dm_projecttimeallocation_facts (cost=0.43..11.75 rows=64 width=4) (actual rows= loops=)

  • Index Cond: (taskid = tk.id)
65. 0.000 0.000 ↓ 0.0

Limit (cost=0.43..0.60 rows=1 width=4) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using ixpta2taskid on dm_projecttimeallocation_facts dm_projecttimeallocation_facts_1 (cost=0.43..11.75 rows=64 width=4) (actual rows= loops=)

  • Index Cond: (taskid = tk.id)
67. 0.000 0.000 ↓ 0.0

Materialize (cost=0.14..2.18 rows=1 width=4) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

Index Scan using uix3er_currencyideffectivedate on exchangerate (cost=0.14..2.17 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((variablecurrencyid = $3) AND (('now'::cstring)::date >= effectivedate))
  • Filter: (('now'::cstring)::date <= enddate)
69. 0.000 0.000 ↓ 0.0

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh (cost=0.29..459.81 rows=11,568 width=5) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh_1 (cost=0.29..459.81 rows=11,568 width=44) (actual rows= loops=)

71. 0.000 0.000 ↓ 0.0

Hash (cost=108.76..108.76 rows=2,576 width=12) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Seq Scan on project pj (cost=0.00..108.76 rows=2,576 width=12) (actual rows= loops=)

73. 0.000 0.000 ↓ 0.0

Hash (cost=118,452.57..118,452.57 rows=967,890 width=82) (actual rows= loops=)

74. 0.000 0.000 ↓ 0.0

Seq Scan on timeentrymetadata timeentrymetadata18 (cost=0.00..118,452.57 rows=967,890 width=82) (actual rows= loops=)

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

Hash (cost=4.10..4.10 rows=210 width=672) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

Seq Scan on activities activities7 (cost=0.00..4.10 rows=210 width=672) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

Hash (cost=96,122.44..96,122.44 rows=57,132 width=82) (actual rows= loops=)

78. 0.000 0.000 ↓ 0.0

Gather (cost=1,000.00..96,122.44 rows=57,132 width=82) (actual rows= loops=)

  • Workers Planned: 2
79. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timeentrymetadata timeentrymetadata19 (cost=0.00..89,409.24 rows=23,805 width=82) (actual rows= loops=)

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

Hash (cost=1.01..1.01 rows=1 width=768) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Seq Scan on breaktype breaktype8 (cost=0.00..1.01 rows=1 width=768) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Hash (cost=101,741.64..101,741.64 rows=70,264 width=170) (actual rows= loops=)

83. 0.000 0.000 ↓ 0.0

Gather (cost=21,096.65..101,741.64 rows=70,264 width=170) (actual rows= loops=)

  • Workers Planned: 2
84. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=20,096.65..93,715.24 rows=29,277 width=170) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Hash Join (cost=20,096.21..30,238.34 rows=29,277 width=98) (actual rows= loops=)

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

Parallel Seq Scan on dm_timesheetlist_facts tslist (cost=0.00..9,749.45 rows=26,645 width=20) (actual rows= loops=)

87. 0.000 0.000 ↓ 0.0

Hash (cost=19,077.15..19,077.15 rows=81,525 width=94) (actual rows= loops=)

88. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=4,443.89..19,077.15 rows=81,525 width=94) (actual rows= loops=)

  • Hash Cond: (tah.timesheetid = ts.id)
89. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,009.44..16,428.68 rows=81,525 width=82) (actual rows= loops=)

  • Hash Cond: (tah.timesheetid = tahls.timesheetid)
  • Filter: ((tahls.timesheetid IS NULL) OR (tah.serialnumber > tahls.lastsubmitserialnumber))
90. 0.000 0.000 ↓ 0.0

Seq Scan on timesheetapprovalhistory tah (cost=0.00..13,777.19 rows=244,576 width=86) (actual rows= loops=)

  • Filter: (((systemprocessidentifier IS NULL) OR (systemprocessidentifier <> 'urn:replicon:approval-system-process:timesheet-submit-script-data-and-validation'::text)) AND (action = ANY ('{2,3,6}'::integer[])))
91. 0.000 0.000 ↓ 0.0

Hash (cost=1,139.75..1,139.75 rows=69,575 width=20) (actual rows= loops=)

92. 0.000 0.000 ↓ 0.0

Seq Scan on timesheetapprovalhistorylastsubmit tahls (cost=0.00..1,139.75 rows=69,575 width=20) (actual rows= loops=)

93. 0.000 0.000 ↓ 0.0

Hash (cost=1,506.98..1,506.98 rows=74,198 width=28) (actual rows= loops=)

94. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet ts (cost=0.00..1,506.98 rows=74,198 width=28) (actual rows= loops=)

95. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue11 (cost=0.43..2.16 rows=1 width=88) (actual rows= loops=)

  • Index Cond: (tah.id = timesheetapprovalhistoryid)
  • Filter: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-ACTUAL-USER'::text)
96. 0.000 0.000 ↓ 0.0

Hash (cost=174,956.40..174,956.40 rows=410,483 width=88) (actual rows= loops=)

97. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvkey on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue12 (cost=0.56..174,956.40 rows=410,483 width=88) (actual rows= loops=)

  • Index Cond: (upper(key) = 'URN:REPLICON:APPROVAL-HISTORICAL-ACTION-DATA-KEY:ACTING-EFFECTIVE-USER'::text)
98. 0.000 0.000 ↓ 0.0

Hash (cost=4.66..4.66 rows=166 width=27) (actual rows= loops=)

99. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffcode timeoffcode13 (cost=0.00..4.66 rows=166 width=27) (actual rows= loops=)