explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k8WK

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

Sort (cost=900,803.69..900,922.35 rows=47,462 width=1,823) (actual rows= loops=)

  • Sort Key: ((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", ((ui.displayname)::text) COLLATE "en_US", ((pj.name)::character varying(255)) COLLATE "en_US", ((tk.code)::character varying(50)) COLLATE "en_US", ((timeentrymetadata4.text)::text) COLLATE "en_US", "*SELECT* 1".entrydate, ((objectextensiontag10.name)::character varying(50)) COLLATE "en_US", ((objectextensiontag11.name)::character varying(50)) 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

Nested Loop Left Join (cost=43,175.76..897,116.19 rows=47,462 width=1,823) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=43,175.32..749,683.59 rows=47,462 width=402) (actual rows= loops=)

  • Hash Cond: (objectextensionfieldvalue20.tagid = objectextensiontag11.id)
6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=43,161.08..749,543.37 rows=47,462 width=380) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=43,160.65..602,110.77 rows=47,462 width=364) (actual rows= loops=)

  • Hash Cond: (objectextensionfieldvalue19.tagid = objectextensiontag10.id)
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=43,146.40..601,970.55 rows=47,462 width=342) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=43,145.97..498,883.91 rows=33,186 width=326) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=43,145.41..375,689.38 rows=33,186 width=269) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=43,144.85..252,494.85 rows=33,186 width=196) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=43,144.29..168,164.73 rows=33,186 width=162) (actual rows= loops=)

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

Hash Left Join (cost=13,736.86..136,673.84 rows=33,186 width=142) (actual rows= loops=)

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

Hash Left Join (cost=13,735.82..136,548.34 rows=33,186 width=146) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".userid = ts.userid)
  • Join Filter: (("*SELECT* 1".entrydate >= ts.startdate) AND ("*SELECT* 1".entrydate <= ts.enddate))
  • Filter: ((hashed SubPlan 3) OR (hashed SubPlan 5))
15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.80..114,323.91 rows=12,846 width=134) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop (cost=5.38..108,315.51 rows=12,846 width=128) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Index Scan using login_pkey on login (cost=0.28..2.30 rows=1 width=11) (actual rows= loops=)

  • Index Cond: (userid = 2,374)
18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=5.09..108,184.75 rows=12,846 width=125) (actual rows= loops=)

  • Filter: (((timeentrymetadata18.uri IS NULL) AND ("*SELECT* 1".timeoffentryid IS NULL) AND (("*SELECT* 1".timeallocationtype = 1) OR ("*SELECT* 1".timeallocationtype IS NULL))) OR ("*SELECT* 1".timeoffentryid IS NOT NULL))
19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4.53..75,343.87 rows=12,911 width=133) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=4.11..68,991.86 rows=12,911 width=97) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.67..63,056.64 rows=12,911 width=152) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=3.11..34,974.55 rows=11,051 width=89) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.68..28,824.12 rows=11,051 width=148) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Nested Loop (cost=2.12..742.03 rows=11,051 width=85) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..3.62 rows=1 width=33) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..2.30 rows=1 width=33) (actual rows= loops=)

  • Index Cond: (id = 2,374)
27. 0.000 0.000 ↓ 0.0

Index Only Scan using ix4ucc_userdepartmentgroupstartend on userdepartmentgroup userdepartmentgroup17 (cost=0.28..1.31 rows=1 width=4) (actual rows= loops=)

  • Index Cond: ((userid = 2,374) AND (departmentgroupid = 'ae326f30-871b-4c35-9564-21031e6a8478'::uuid) AND (startdate <= '2020-06-22'::date) AND (enddate >= '2020-06-22'::date))
28. 0.000 0.000 ↓ 0.0

Append (cost=1.55..627.91 rows=11,051 width=52) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=1.55..567.65 rows=11,050 width=52) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.55..457.15 rows=11,050 width=140) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

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

32. 0.000 0.000 ↓ 0.0

Materialize (cost=1.55..109.20 rows=17 width=60) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1.55..109.11 rows=17 width=60) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

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

35. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.55..107.93 rows=17 width=60) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.99..63.90 rows=17 width=60) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Index Scan using ixte2userid on timeentry te (cost=0.43..19.87 rows=17 width=60) (actual rows= loops=)

  • Index Cond: ((userid = 2,374) AND (entrydate >= '2020-06-14'::date) AND (entrydate <= '2020-06-20'::date))
  • Filter: ((timeallocationtype <> 2) OR (timeallocationtype IS NULL))
38. 0.000 0.000 ↓ 0.0

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

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

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

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

Subquery Scan on *SELECT* 2 (cost=0.83..60.25 rows=1 width=52) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.83..60.24 rows=1 width=140) (actual rows= loops=)

42. 0.000 0.000 ↓ 0.0

Index Scan using ixto4userid on timeoffs toff (cost=0.42..18.59 rows=17 width=8) (actual rows= loops=)

  • Index Cond: (userid = 2,374)
43. 0.000 0.000 ↓ 0.0

Index Scan using uix2toe_timeoffidentrydate on timeoffentries toe (cost=0.42..2.44 rows=1 width=28) (actual rows= loops=)

  • Index Cond: ((timeoffid = toff.id) AND (entrydate >= '2020-06-14'::date) AND (entrydate <= '2020-06-20'::date))
44. 0.000 0.000 ↓ 0.0

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

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

Index Only Scan using project_pkey on project project14 (cost=0.43..0.56 rows=1 width=4) (actual rows= loops=)

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

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

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

Index Scan using task_pkey on task task16 (cost=0.44..0.46 rows=1 width=8) (actual rows= loops=)

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

Index Scan using project_pkey on project pj (cost=0.42..0.49 rows=1 width=44) (actual rows= loops=)

  • Index Cond: (id = COALESCE(project14.id, task16.projectid))
49. 0.000 0.000 ↓ 0.0

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

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

Index Scan using task_pkey on task tk (cost=0.42..0.47 rows=1 width=10) (actual rows= loops=)

  • Index Cond: (task16.id = id)
51. 0.000 0.000 ↓ 0.0

Hash (cost=82.82..82.82 rows=31 width=28) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.84..82.82 rows=31 width=28) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Index Scan using uix2tsuseridstartdate on timesheet ts (cost=0.42..33.82 rows=32 width=28) (actual rows= loops=)

  • Index Cond: (userid = 2,374)
54. 0.000 0.000 ↓ 0.0

Index Only Scan using ixdtslsftimesheetid on dm_timesheetlist_facts tslist (cost=0.42..1.53 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (timesheetid = ts.id)
55.          

SubPlan (for Hash Left Join)

56. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=249.19..9,096.76 rows=93,675 width=16) (actual rows= loops=)

  • Hash Cond: (timesheet21.userid = userdepartmentgroup22.userid)
  • Join Filter: ((userdepartmentgroup22.startdate <= timesheet21.enddate) AND (userdepartmentgroup22.enddate >= timesheet21.startdate))
  • Filter: ((hashed SubPlan 2) OR (timesheet21.userid = 2,373))
57. 0.000 0.000 ↓ 0.0

Seq Scan on timesheet timesheet21 (cost=0.00..3,926.17 rows=187,317 width=28) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Hash (cost=161.23..161.23 rows=6,930 width=28) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Index Only Scan using ix4ucc_userdepartmentgroupstartend on userdepartmentgroup userdepartmentgroup22 (cost=0.28..161.23 rows=6,930 width=28) (actual rows= loops=)

60.          

SubPlan (for Hash Left Join)

61. 0.000 0.000 ↓ 0.0

Index Only Scan using departmentgroupflathierarchy_pkey on departmentgroupflathierarchy departmentgroupflathierarchy23 (cost=0.28..1.33 rows=3 width=16) (actual rows= loops=)

  • Index Cond: (parentid = 'ae326f30-871b-4c35-9564-21031e6a8478'::uuid)
62. 0.000 0.000 ↓ 0.0

Seq Scan on timeoffs timeoffs24 (cost=141.99..4,186.32 rows=51,819 width=4) (actual rows= loops=)

  • Filter: ((userid = 2,373) OR (hashed SubPlan 4))
63.          

SubPlan (for Seq Scan)

64. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.98..141.74 rows=102 width=4) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.56..70.40 rows=62 width=12) (actual rows= loops=)

66. 0.000 0.000 ↓ 0.0

Index Only Scan using departmentgroupflathierarchy_pkey on departmentgroupflathierarchy departmentgroupflathierarchy27 (cost=0.28..1.33 rows=3 width=16) (actual rows= loops=)

  • Index Cond: (parentid = 'ae326f30-871b-4c35-9564-21031e6a8478'::uuid)
67. 0.000 0.000 ↓ 0.0

Index Scan using ixudgdepartmentgroupid on userdepartmentgroup userdepartmentgroup26 (cost=0.28..22.75 rows=27 width=28) (actual rows= loops=)

  • Index Cond: (departmentgroupid = departmentgroupflathierarchy27.childid)
68. 0.000 0.000 ↓ 0.0

Index Scan using ixto4userid on timeoffs timeoffs25 (cost=0.42..1.13 rows=2 width=16) (actual rows= loops=)

  • Index Cond: ((userid = userdepartmentgroup26.userid) AND (userdepartmentgroup26.enddate >= startdate) AND (userdepartmentgroup26.startdate <= enddate))
69. 0.000 0.000 ↓ 0.0

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

70. 0.000 0.000 ↓ 0.0

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

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

Hash (cost=28,170.89..28,170.89 rows=98,923 width=36) (actual rows= loops=)

72. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=5,156.53..28,170.89 rows=98,923 width=36) (actual rows= loops=)

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

Index Scan using ixtahaction on timesheetapprovalhistory tah (cost=0.42..22,235.74 rows=296,770 width=40) (actual rows= loops=)

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

Hash (cost=2,942.16..2,942.16 rows=177,116 width=20) (actual rows= loops=)

75. 0.000 0.000 ↓ 0.0

Seq Scan on timesheetapprovalhistorylastsubmit tahls (cost=0.00..2,942.16 rows=177,116 width=20) (actual rows= loops=)

76. 0.000 0.000 ↓ 0.0

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

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

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue6 (cost=0.56..3.69 rows=2 width=89) (actual rows= loops=)

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

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue7 (cost=0.56..3.69 rows=2 width=89) (actual rows= loops=)

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

Index Scan using ixoefv_objectid on objectextensionfieldvalue objectextensionfieldvalue19 (cost=0.43..3.10 rows=1 width=32) (actual rows= loops=)

  • Index Cond: ("*SELECT* 1".timeentryid = objectid)
  • Filter: ('b814810f-03d6-4d06-894f-7d8b64bffed5'::uuid = definitionid)
80. 0.000 0.000 ↓ 0.0

Hash (cost=10.22..10.22 rows=322 width=54) (actual rows= loops=)

81. 0.000 0.000 ↓ 0.0

Seq Scan on objectextensiontag objectextensiontag10 (cost=0.00..10.22 rows=322 width=54) (actual rows= loops=)

82. 0.000 0.000 ↓ 0.0

Index Scan using ixoefv_objectid on objectextensionfieldvalue objectextensionfieldvalue20 (cost=0.43..3.10 rows=1 width=32) (actual rows= loops=)

  • Index Cond: ("*SELECT* 1".timeentryid = objectid)
  • Filter: ('4fd4e56a-a51a-4bbf-b826-fb7575748787'::uuid = definitionid)
83. 0.000 0.000 ↓ 0.0

Hash (cost=10.22..10.22 rows=322 width=54) (actual rows= loops=)

84. 0.000 0.000 ↓ 0.0

Seq Scan on objectextensiontag objectextensiontag11 (cost=0.00..10.22 rows=322 width=54) (actual rows= loops=)

85. 0.000 0.000 ↓ 0.0

Index Scan using ixoefv_objectid on objectextensionfieldvalue objectextensionfieldvalue12 (cost=0.43..3.10 rows=1 width=20) (actual rows= loops=)

  • Index Cond: ("*SELECT* 1".timeentryid = objectid)
  • Filter: ('d11e18bc-d5d2-4205-8af0-cc8993994362'::uuid = definitionid)