explain.depesz.com

PostgreSQL's explain analyze made readable

Result: J3Ey

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

Sort (cost=26,641,049.03..26,641,051.58 rows=1,022 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", ((objectextensiontag4.name)::character varying(50)) COLLATE "en_US", ((timeentrymetadata5.text)::text) COLLATE "en_US", "*SELECT* 1".entrydate, ((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 (cost=16,168,001.26..26,640,996.93 rows=1,022 width=1,823) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Index Only Scan using project_pkey on project project20 (cost=0.42..2.44 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = 209,165)
6. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16,168,000.83..26,640,984.27 rows=1,022 width=405) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=16,168,000.40..26,640,489.22 rows=1,022 width=401) (actual rows= loops=)

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

Nested Loop Left Join (cost=16,167,986.09..26,640,472.19 rows=1,022 width=379) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16,167,985.66..26,640,121.00 rows=725 width=363) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16,167,985.09..26,637,271.45 rows=721 width=306) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16,167,984.53..26,634,429.11 rows=721 width=233) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16,167,983.69..26,633,320.18 rows=721 width=213) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16,167,982.85..26,632,868.42 rows=185 width=197) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16,167,982.29..26,632,757.87 rows=185 width=163) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16,167,982.01..26,632,704.22 rows=185 width=141) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=16,167,981.58..26,632,614.61 rows=185 width=125) (actual rows= loops=)

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

Nested Loop Left Join (cost=16,167,981.58..26,632,610.80 rows=185 width=129) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop (cost=16,167,981.16..26,632,524.39 rows=185 width=123) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop (cost=16,167,980.87..26,632,468.89 rows=185 width=120) (actual rows= loops=)

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

Index Scan using userinfo_pkey on userinfo ui (cost=0.28..301.65 rows=5,958 width=32) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Materialize (cost=16,167,980.59..26,615,634.25 rows=185 width=88) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash Join (cost=16,167,980.59..26,615,633.33 rows=185 width=88) (actual rows= loops=)

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

Hash Left Join (cost=16,167,978.14..26,383,304.94 rows=88,504,333 width=52) (actual rows= loops=)

  • Hash 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 = task16.id)
24. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=16,122,601.22..23,107,910.40 rows=88,504,333 width=107) (actual rows= loops=)

  • Hash Cond: (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 = project14.id)
25. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=16,087,972.90..18,635,132.90 rows=88,504,333 width=166) (actual rows= loops=)

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

Merge Left Join (cost=16,087,972.34..16,474,869.79 rows=68,337,623 width=103) (actual rows= loops=)

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

Sort (cost=15,667,172.34..15,838,016.39 rows=68,337,623 width=40) (actual rows= loops=)

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

Hash Left Join (cost=349,936.32..3,571,009.07 rows=68,337,623 width=40) (actual rows= loops=)

  • Hash Cond: ("*SELECT* 1".timeentryid = timeentrymetadata17.timeentryid)
  • Filter: (((timeentrymetadata17.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))
29. 0.000 0.000 ↓ 0.0

Append (cost=1,001.55..2,964,520.29 rows=68,681,018 width=48) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=1,001.55..2,962,589.23 rows=68,680,950 width=48) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,001.55..2,275,779.73 rows=68,680,950 width=140) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Nested Loop (cost=1,001.55..215,333.10 rows=105,663 width=60) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

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

34. 0.000 0.000 ↓ 0.0

Gather (cost=1,001.55..214,275.46 rows=105,663 width=60) (actual rows= loops=)

  • Workers Planned: 2
35. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.55..202,709.16 rows=44,026 width=60) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.99..103,647.99 rows=44,026 width=60) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Parallel Index Scan using ixte2entrydate on timeentry te (cost=0.43..4,586.81 rows=44,026 width=60) (actual rows= loops=)

  • Index Cond: ((entrydate >= '2020-07-26'::date) AND (entrydate <= '2020-08-01'::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.24 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.24 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

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

41. 0.000 0.000 ↓ 0.0

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

42. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=0.83..1,931.06 rows=68 width=48) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.83..1,930.38 rows=68 width=140) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

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

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

Index Scan using timeoffs_pkey on timeoffs toff (cost=0.42..2.39 rows=1 width=8) (actual rows= loops=)

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

Hash (cost=348,934.76..348,934.76 rows=1 width=79) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Index Scan using ixtem2timeentryid on timeentrymetadata timeentrymetadata17 (cost=0.56..348,934.76 rows=1 width=79) (actual rows= loops=)

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

Materialize (cost=420,800.01..421,787.14 rows=197,427 width=79) (actual rows= loops=)

49. 0.000 0.000 ↓ 0.0

Sort (cost=420,800.01..421,293.58 rows=197,427 width=79) (actual rows= loops=)

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

Gather (cost=1,000.00..398,421.36 rows=197,427 width=79) (actual rows= loops=)

  • Workers Planned: 2
51. 0.000 0.000 ↓ 0.0

Parallel Seq Scan on timeentrymetadata timeentrymetadata13 (cost=0.00..377,678.66 rows=82,261 width=79) (actual rows= loops=)

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

Materialize (cost=0.56..676,507.57 rows=5,861,404 width=79) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Index Scan using ixtemtimeentryid on timeentrymetadata timeentrymetadata15 (cost=0.56..661,854.06 rows=5,861,404 width=79) (actual rows= loops=)

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

Hash (cost=26,798.14..26,798.14 rows=477,214 width=4) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Seq Scan on project project14 (cost=0.00..26,798.14 rows=477,214 width=4) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Hash (cost=29,607.52..29,607.52 rows=961,152 width=8) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Seq Scan on task task16 (cost=0.00..29,607.52 rows=961,152 width=8) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Hash (cost=2.44..2.44 rows=1 width=44) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (id = 209,165)
60. 0.000 0.000 ↓ 0.0

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

  • Index Cond: (userid = "*SELECT* 1".userid)
61. 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)
62. 0.000 0.000 ↓ 0.0

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

63. 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))
64. 0.000 0.000 ↓ 0.0

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

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

Index Scan using objectextensiontag_pkey on objectextensiontag objectextensiontag4 (cost=0.27..0.29 rows=1 width=54) (actual rows= loops=)

  • Index Cond: (objectextensionfieldvalue18.tagid = id)
66. 0.000 0.000 ↓ 0.0

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

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

Nested Loop (cost=0.84..2.40 rows=4 width=28) (actual rows= loops=)

68. 0.000 0.000 ↓ 0.0

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

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

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

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

Nested Loop Left Join (cost=0.84..1.53 rows=1 width=36) (actual rows= loops=)

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

Index Scan using ixtah2timesheetid on timesheetapprovalhistory tah (cost=0.42..0.61 rows=2 width=40) (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[])))
72. 0.000 0.000 ↓ 0.0

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

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

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue7 (cost=0.56..3.92 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)
74. 0.000 0.000 ↓ 0.0

Index Scan using ixtahkvtimesheetapprovalhistoryid on timesheetapprovalhistorykeyvalue esheetapprovalhistorykeyvalue8 (cost=0.56..3.92 rows=3 width=89) (actual rows= loops=)

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

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

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

Hash (cost=10.25..10.25 rows=325 width=54) (actual rows= loops=)

77. 0.000 0.000 ↓ 0.0

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

78. 0.000 0.000 ↓ 0.0

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

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