explain.depesz.com

PostgreSQL's explain analyze made readable

Result: g3rf

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

Sort (cost=84,782,447.90..84,782,486.34 rows=15,376 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=2,158.16..84,748,881.62 rows=15,376 width=4,302) (actual rows= loops=)

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

Nested Loop Left Join (cost=2,150.42..84,748,832.66 rows=15,376 width=1,878) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,149.99..84,714,960.59 rows=15,376 width=1,822) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,148.72..84,654,913.82 rows=14,117 width=1,684) (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

Nested Loop Left Join (cost=2,147.70..84,654,865.73 rows=14,117 width=1,111) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,146.99..84,642,806.08 rows=3,844 width=1,083) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,146.44..84,633,135.34 rows=3,844 width=1,017) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,146.28..84,632,462.91 rows=3,844 width=415) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,145.73..84,622,792.17 rows=3,844 width=349) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,145.44..84,621,508.50 rows=3,844 width=309) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,145.16..84,620,109.33 rows=3,844 width=305) (actual rows= loops=)

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

Nested Loop Left Join (cost=2,145.16..84,619,703.34 rows=3,844 width=309) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,144.59..84,602,649.25 rows=3,844 width=180) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,144.29..84,601,435.45 rows=3,844 width=238) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,143.74..84,591,764.71 rows=3,844 width=172) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,143.45..84,590,572.94 rows=3,844 width=234) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2,142.89..84,580,902.20 rows=3,844 width=168) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2,142.34..84,571,231.46 rows=3,844 width=145) (actual rows= loops=)

  • Join Filter: ("*SELECT* 1".userid = ui.id)
22. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..83.41 rows=1,942 width=11) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Materialize (cost=2,142.06..84,459,181.94 rows=3,844 width=134) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,142.06..84,459,162.72 rows=3,844 width=134) (actual rows= loops=)

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

Append (cost=2,141.01..84,459,151.37 rows=3,844 width=128) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=2,141.01..81,211,070.10 rows=3,696 width=128) (actual rows= loops=)

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

Nested Loop (cost=1,072.06..16,427.14 rows=4,928 width=140) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

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

29. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,072.06..16,044.21 rows=4,928 width=221) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

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

31. 0.000 0.000 ↓ 0.0

Gather (cost=1,072.06..15,993.92 rows=4,928 width=188) (actual rows= loops=)

  • Workers Planned: 2
32. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=72.06..14,501.12 rows=2,053 width=188) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=71.50..9,374.51 rows=2,053 width=122) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on timeentry te (cost=70.95..4,247.89 rows=2,053 width=56) (actual rows= loops=)

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

Bitmap Index Scan on ixte2entrydate (cost=0.00..69.71 rows=4,929 width=0) (actual rows= loops=)

  • Index Cond: ((entrydate >= '2020-07-17'::date) AND (entrydate <= '2020-07-17'::date))
36. 0.000 0.000 ↓ 0.0

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

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

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

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

SubPlan (for Subquery Scan)

39. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..30,716.64 rows=894,109 width=16) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Seq Scan on timeentry timeentry20 (cost=0.00..21,880.09 rows=894,109 width=16) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Index Only Scan using timeoffs_pkey on timeoffs timeoffs21 (cost=0.29..976.00 rows=37,181 width=4) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=1,069.54..3,248,081.27 rows=148 width=128) (actual rows= loops=)

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

Nested Loop (cost=0.58..1,246.47 rows=197 width=140) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

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

  • Index Cond: ((entrydate >= '2020-07-17'::date) AND (entrydate <= '2020-07-17'::date))
45. 0.000 0.000 ↓ 0.0

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

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

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

47. 0.000 0.000 ↓ 0.0

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

48. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata timeentrymetadata2 (cost=0.56..2.51 rows=1 width=39) (actual rows= loops=)

  • Index Cond: (("*SELECT* 1".timeentryid = timeentryid) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:COMMENTS'::text))
49. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (("*SELECT* 1".timeentryid = timeentryid) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:PROJECT'::text))
50. 0.000 0.000 ↓ 0.0

Index Only Scan using project_pkey on project project15 (cost=0.29..0.31 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = 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)
51. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (("*SELECT* 1".timeentryid = timeentryid) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:TASK'::text))
52. 0.000 0.000 ↓ 0.0

Index Scan using task_pkey on task task17 (cost=0.30..0.32 rows=1 width=8) (actual rows= loops=)

  • Index 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 = id)
53. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.57..4.43 rows=1 width=133) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Index Scan using task_pkey on task tk (cost=0.29..4.09 rows=1 width=129) (actual rows= loops=)

  • Index Cond: (task17.id = id)
55.          

SubPlan (for Index Scan)

56. 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)
57. 0.000 0.000 ↓ 0.0

Limit (cost=0.42..0.74 rows=1 width=4) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Index Only Scan using ixpta2taskid on dm_projecttimeallocation_facts (cost=0.42..18.55 rows=58 width=4) (actual rows= loops=)

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

Limit (cost=0.42..0.74 rows=1 width=4) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Index Only Scan Backward using ixpta2taskid on dm_projecttimeallocation_facts dm_projecttimeallocation_facts_1 (cost=0.42..18.55 rows=58 width=4) (actual rows= loops=)

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

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh (cost=0.29..0.33 rows=1 width=5) (actual rows= loops=)

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

Materialize (cost=0.00..2.38 rows=7 width=4) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on exchangerate (cost=0.00..2.35 rows=7 width=4) (actual rows= loops=)

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

Index Scan using project_pkey on project pj (cost=0.28..0.36 rows=1 width=12) (actual rows= loops=)

  • Index Cond: (id = COALESCE(project15.id, task17.projectid))
65. 0.000 0.000 ↓ 0.0

Index Scan using taskdenormalizedhierarchy_pkey on taskdenormalizedhierarchy tdh_1 (cost=0.29..0.33 rows=1 width=44) (actual rows= loops=)

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

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

  • Index Cond: (("*SELECT* 1".timeentryid = timeentryid) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:ACTIVITY'::text))
67. 0.000 0.000 ↓ 0.0

Index Scan using activities_pkey on activities activities7 (cost=0.16..0.18 rows=1 width=672) (actual rows= loops=)

  • Index 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 = id)
68. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (("*SELECT* 1".timeentryid = timeentryid) AND (upper(key) = 'URN:REPLICON:TIME-ENTRY-METADATA-KEY:BREAK-TYPE'::text))
69. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.70..3.10 rows=4 width=32) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.29..0.78 rows=4 width=28) (actual rows= loops=)

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

Index Scan using dm_timesheetlist_facts_pkey on dm_timesheetlist_facts tslist (cost=0.41..0.58 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (timesheetid = ts.id)
72. 0.000 0.000 ↓ 0.0

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

73. 0.000 0.000 ↓ 0.0

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

74. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.27..4.24 rows=1 width=154) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.84..2.04 rows=1 width=82) (actual rows= loops=)

  • Filter: ((tahls.timesheetid IS NULL) OR (tah.serialnumber > tahls.lastsubmitserialnumber))
76. 0.000 0.000 ↓ 0.0

Index Scan using ixtah2timesheetid on timesheetapprovalhistory tah (cost=0.42..0.70 rows=3 width=86) (actual rows= loops=)

  • Index Cond: (ts.id = timesheetid)
  • 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[])))
77. 0.000 0.000 ↓ 0.0

Index Scan using timesheetapprovalhistorylastsubmit_pkey on timesheetapprovalhistorylastsubmit tahls (cost=0.41..0.44 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (tah.timesheetid = timesheetid)
78. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue sheetapprovalhistorykeyvalue11 (cost=0.43..2.19 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)
79. 0.000 0.000 ↓ 0.0

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

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

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

81. 0.000 0.000 ↓ 0.0

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