explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BXJe

Settings
# exclusive inclusive rows x rows loops node
1. 0.078 69,699.289 ↑ 1.0 11 1

Limit (cost=242,619.32..242,619.34 rows=11 width=2,428) (actual time=69,699.208..69,699.289 rows=11 loops=1)

  • Output: ts.id, c.id, ((c.name)::character varying(255)), ((c.slug)::character varying(255)), ((c.code)::character varying(50)), p.id, ((p.name)::character varying(255)), ((p.slug)::character varying(255)), ((p.code)::character varying(50)), task.id, ((task.name)::character varying), ((task.name)::character varying), ((task.code)::character varying), ((0 + (((0 + (CASE WHEN (upper((c.name)::text) ~~ '%A%'::text) THEN true ELSE false END)::integer) + (CASE WHEN (upper((p.name)::text) ~~ '%A%'::text) THEN true ELSE false END)::integer) + (CASE WHEN (upper((task.name)::text) ~~ '%A%'::text) THEN true ELSE false END)::integer)))
2. 248.246 69,699.211 ↑ 719.5 11 1

Sort (cost=242,619.32..242,639.10 rows=7,914 width=2,428) (actual time=69,699.206..69,699.211 rows=11 loops=1)

  • Output: ts.id, c.id, ((c.name)::character varying(255)), ((c.slug)::character varying(255)), ((c.code)::character varying(50)), p.id, ((p.name)::character varying(255)), ((p.slug)::character varying(255)), ((p.code)::character varying(50)), task.id, ((task.name)::character varying), ((task.name)::character varying), ((task.code)::character varying), ((0 + (((0 + (CASE WHEN (upper((c.name)::text) ~~ '%A%'::text) THEN true ELSE false END)::integer) + (CASE WHEN (upper((p.name)::text) ~~ '%A%'::text) THEN true ELSE false END)::integer) + (CASE WHEN (upper((task.name)::text) ~~ '%A%'::text) THEN true ELSE false END)::integer)))
  • Sort Key: ((0 + (((0 + (CASE WHEN (upper((c.name)::text) ~~ '%A%'::text) THEN true ELSE false END)::integer) + (CASE WHEN (upper((p.name)::text) ~~ '%A%'::text) THEN true ELSE false END)::integer) + (CASE WHEN (upper((task.name)::text) ~~ '%A%'::text) THEN true ELSE false END)::integer))), ((c.name)::character varying(255)) COLLATE "en_US" NULLS FIRST, ((p.name)::character varying(255)) COLLATE "en_US" NULLS FIRST, ((task.name)::character varying) COLLATE "en_US" NULLS FIRST
  • Sort Method: top-N heapsort Memory: 29kB
3. 708.867 69,450.965 ↓ 30.7 243,303 1

Nested Loop (cost=99.22..242,442.86 rows=7,914 width=2,428) (actual time=3.328..69,450.965 rows=243,303 loops=1)

  • Output: ts.id, c.id, c.name, c.slug, c.code, p.id, p.name, p.slug, p.code, task.id, task.name, task.name, task.code, (0 + (((0 + (CASE WHEN (upper((c.name)::text) ~~ '%A%'::text) THEN true ELSE false END)::integer) + (CASE WHEN (upper((p.name)::text) ~~ '%A%'::text) THEN true ELSE false END)::integer) + (CASE WHEN (upper((task.name)::text) ~~ '%A%'::text) THEN true ELSE false END)::integer))
4. 0.059 0.059 ↑ 1.0 1 1

Index Only Scan using timesheet_pkey on e3ec273096164aa4a2a67621ca1e0643.timesheet ts (cost=0.42..8.44 rows=1 width=16) (actual time=0.057..0.059 rows=1 loops=1)

  • Output: ts.id
  • Index Cond: (ts.id = '484e44e6-7209-4c91-9351-961796dc98d2'::uuid)
  • Heap Fetches: 1
5. 1,298.128 68,742.039 ↓ 30.7 243,303 1

Hash Left Join (cost=98.81..242,098.08 rows=7,914 width=136) (actual time=3.261..68,742.039 rows=243,303 loops=1)

  • Output: p.id, p.name, p.slug, p.code, task.id, task.name, task.code, c.id, c.name, c.slug, c.code
  • Inner Unique: true
  • Hash Cond: (projectclient.clientid = c.id)
  • Filter: (((upper((c.name)::text) ~~ '%A%'::text) OR (upper((p.name)::text) ~~ '%A%'::text) OR (upper((task.name)::text) ~~ '%A%'::text)) AND (((c.id IS NULL) AND (NOT (alternatives: SubPlan 1 or hashed SubPlan 2))) OR ((c.id IS NOT NULL) AND (SubPlan 3))))
  • Rows Removed by Filter: 274,537
6. 661.159 66,956.320 ↓ 32.7 517,840 1

Nested Loop (cost=87.12..241,376.06 rows=15,828 width=84) (actual time=2.888..66,956.320 rows=517,840 loops=1)

  • Output: p.id, p.name, p.slug, p.code, task.id, task.name, task.code, projectclient.clientid
7. 1,200.379 64,224.681 ↓ 281.0 258,810 1

Hash Join (cost=86.85..240,813.47 rows=921 width=84) (actual time=2.858..64,224.681 rows=258,810 loops=1)

  • Output: p.id, p.name, p.slug, p.code, task.id, task.name, task.code, task.projectid
  • Inner Unique: true
  • Hash Cond: ((SubPlan 37) = projectstatuslabel3.id)
8. 589.900 61,848.869 ↓ 1.8 328,898 1

Hash Join (cost=85.75..160,505.07 rows=184,149 width=84) (actual time=2.684..61,848.869 rows=328,898 loops=1)

  • Output: p.id, p.name, p.slug, p.code, task.id, task.name, task.code, task.projectid
  • Inner Unique: true
  • Hash Cond: (task.projectid = p.id)
  • Join Filter: (((task.id IS NULL) AND (SubPlan 4) AND (SubPlan 12)) OR ((task.id IS NOT NULL) AND (SubPlan 28)))
  • Rows Removed by Join Filter: 40,785
9. 285.129 534.007 ↓ 1.0 369,683 1

Append (cost=0.00..8,570.81 rows=369,681 width=34) (actual time=0.011..534.007 rows=369,683 loops=1)

10. 247.922 247.922 ↓ 1.0 367,960 1

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.task (cost=0.00..8,506.59 rows=367,959 width=34) (actual time=0.010..247.922 rows=367,960 loops=1)

  • Output: task.id, task.name, task.code, task.projectid
11. 0.956 0.956 ↓ 1.0 1,723 1

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.project (cost=0.00..64.22 rows=1,722 width=72) (actual time=0.009..0.956 rows=1,723 loops=1)

  • Output: NULL::integer, NULL::character varying, NULL::character varying, project.id
12. 0.997 2.062 ↓ 1.0 1,723 1

Hash (cost=64.22..64.22 rows=1,722 width=66) (actual time=2.061..2.062 rows=1,723 loops=1)

  • Output: p.id, p.name, p.slug, p.code, p.programid
  • Buckets: 2,048 Batches: 1 Memory Usage: 153kB
13. 1.065 1.065 ↓ 1.0 1,723 1

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.project p (cost=0.00..64.22 rows=1,722 width=66) (actual time=0.010..1.065 rows=1,723 loops=1)

  • Output: p.id, p.name, p.slug, p.code, p.programid
14.          

SubPlan (for Hash Join)

15. 3.446 3.446 ↑ 1.0 1 1,723

Index Scan using project_pkey on e3ec273096164aa4a2a67621ca1e0643.project project4 (cost=0.28..8.29 rows=1 width=1) (actual time=0.002..0.002 rows=1 loops=1,723)

  • Output: project4.istimeentryallowed
  • Index Cond: (project4.id = p.id)
16. 5.840 6.054 ↓ 0.0 0 1,009

Index Scan using uix3ptm_projectrole on e3ec273096164aa4a2a67621ca1e0643.projectteammember projectteammember5 (cost=126.21..1,166.69 rows=615 width=0) (actual time=0.006..0.006 rows=0 loops=1,009)

  • Index Cond: (projectteammember5.projectid = p.id)
  • Filter: ((projectteammember5.userid = 376) OR (hashed SubPlan 5) OR (hashed SubPlan 6) OR (hashed SubPlan 7) OR (hashed SubPlan 8) OR (hashed SubPlan 9) OR (hashed SubPlan 10) OR (hashed SubPlan 11))
  • Rows Removed by Filter: 6
17.          

SubPlan (for Index Scan)

18. 0.046 0.120 ↑ 1.0 2 1

Nested Loop (cost=0.28..11.75 rows=2 width=4) (actual time=0.035..0.120 rows=2 loops=1)

  • Output: departmentflathierarchy6.parentid
  • Join Filter: (departmentusers7.departmentid = departmentflathierarchy6.childid)
  • Rows Removed by Join Filter: 107
19. 0.027 0.027 ↑ 1.0 1 1

Index Scan using ixduuserid on e3ec273096164aa4a2a67621ca1e0643.departmentusers departmentusers7 (cost=0.28..8.30 rows=1 width=4) (actual time=0.026..0.027 rows=1 loops=1)

  • Output: departmentusers7.departmentid, departmentusers7.userid, departmentusers7.isprimarydepartment
  • Index Cond: (departmentusers7.userid = 376)
20. 0.047 0.047 ↑ 1.0 109 1

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.departmentflathierarchy departmentflathierarchy6 (cost=0.00..2.09 rows=109 width=8) (actual time=0.006..0.047 rows=109 loops=1)

  • Output: departmentflathierarchy6.parentid, departmentflathierarchy6.childid, departmentflathierarchy6.depth
21. 0.005 0.028 ↑ 2.0 1 1

Nested Loop (cost=4.57..16.84 rows=2 width=16) (actual time=0.025..0.028 rows=1 loops=1)

  • Output: locationflathierarchy8.parentid
22. 0.009 0.009 ↑ 1.0 1 1

Index Only Scan using ix4ul_userlocationstartend on e3ec273096164aa4a2a67621ca1e0643.userlocation userlocation9 (cost=0.28..8.30 rows=1 width=16) (actual time=0.007..0.009 rows=1 loops=1)

  • Output: userlocation9.userid, userlocation9.locationid, userlocation9.startdate, userlocation9.enddate
  • Index Cond: ((userlocation9.userid = 376) AND (userlocation9.startdate <= '2019-09-30'::date) AND (userlocation9.enddate >= '2019-09-22'::date))
  • Heap Fetches: 1
23. 0.008 0.014 ↑ 2.0 1 1

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.locationflathierarchy locationflathierarchy8 (cost=4.29..8.52 rows=2 width=32) (actual time=0.012..0.014 rows=1 loops=1)

  • Output: locationflathierarchy8.parentid, locationflathierarchy8.childid, locationflathierarchy8.parentdepth
  • Recheck Cond: (locationflathierarchy8.childid = userlocation9.locationid)
  • Heap Blocks: exact=1
24. 0.006 0.006 ↑ 2.0 1 1

Bitmap Index Scan on ixlfhchildid (cost=0.00..4.29 rows=2 width=0) (actual time=0.005..0.006 rows=1 loops=1)

  • Index Cond: (locationflathierarchy8.childid = userlocation9.locationid)
25. 0.001 0.013 ↓ 0.0 0 1

Nested Loop (cost=4.35..21.96 rows=6 width=16) (actual time=0.012..0.013 rows=0 loops=1)

  • Output: divisionflathierarchy10.parentid
26. 0.012 0.012 ↓ 0.0 0 1

Index Only Scan using ix4ud_userdivisionstartend on e3ec273096164aa4a2a67621ca1e0643.userdivision userdivision11 (cost=0.15..8.24 rows=1 width=16) (actual time=0.011..0.012 rows=0 loops=1)

  • Output: userdivision11.userid, userdivision11.divisionid, userdivision11.startdate, userdivision11.enddate
  • Index Cond: ((userdivision11.userid = 376) AND (userdivision11.startdate <= '2019-09-30'::date) AND (userdivision11.enddate >= '2019-09-22'::date))
  • Heap Fetches: 0
27. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.divisionflathierarchy divisionflathierarchy10 (cost=4.20..13.67 rows=6 width=32) (never executed)

  • Output: divisionflathierarchy10.parentid, divisionflathierarchy10.childid, divisionflathierarchy10.parentdepth
  • Recheck Cond: (divisionflathierarchy10.childid = userdivision11.divisionid)
28. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ixdivfhchildid (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (divisionflathierarchy10.childid = userdivision11.divisionid)
29. 0.002 0.011 ↓ 0.0 0 1

Nested Loop (cost=4.35..21.96 rows=6 width=16) (actual time=0.010..0.011 rows=0 loops=1)

  • Output: costcenterflathierarchy12.parentid
30. 0.009 0.009 ↓ 0.0 0 1

Index Only Scan using ix4ucc_usercostcenterstartend on e3ec273096164aa4a2a67621ca1e0643.usercostcenter usercostcenter13 (cost=0.15..8.24 rows=1 width=16) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: usercostcenter13.userid, usercostcenter13.costcenterid, usercostcenter13.startdate, usercostcenter13.enddate
  • Index Cond: ((usercostcenter13.userid = 376) AND (usercostcenter13.startdate <= '2019-09-30'::date) AND (usercostcenter13.enddate >= '2019-09-22'::date))
  • Heap Fetches: 0
31. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.costcenterflathierarchy costcenterflathierarchy12 (cost=4.20..13.67 rows=6 width=32) (never executed)

  • Output: costcenterflathierarchy12.parentid, costcenterflathierarchy12.childid, costcenterflathierarchy12.parentdepth
  • Recheck Cond: (costcenterflathierarchy12.childid = usercostcenter13.costcenterid)
32. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ixccfhchildid (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (costcenterflathierarchy12.childid = usercostcenter13.costcenterid)
33. 0.000 0.020 ↓ 0.0 0 1

Nested Loop (cost=4.35..21.96 rows=6 width=16) (actual time=0.020..0.020 rows=0 loops=1)

  • Output: servicecenterflathierarchy14.parentid
34. 0.020 0.020 ↓ 0.0 0 1

Index Only Scan using ix4usc_userservicecenterstartend on e3ec273096164aa4a2a67621ca1e0643.userservicecenter userservicecenter15 (cost=0.15..8.24 rows=1 width=16) (actual time=0.019..0.020 rows=0 loops=1)

  • Output: userservicecenter15.userid, userservicecenter15.servicecenterid, userservicecenter15.startdate, userservicecenter15.enddate
  • Index Cond: ((userservicecenter15.userid = 376) AND (userservicecenter15.startdate <= '2019-09-30'::date) AND (userservicecenter15.enddate >= '2019-09-22'::date))
  • Heap Fetches: 0
35. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.servicecenterflathierarchy servicecenterflathierarchy14 (cost=4.20..13.67 rows=6 width=32) (never executed)

  • Output: servicecenterflathierarchy14.parentid, servicecenterflathierarchy14.childid, servicecenterflathierarchy14.parentdepth
  • Recheck Cond: (servicecenterflathierarchy14.childid = userservicecenter15.servicecenterid)
36. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ixscfhchildid (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (servicecenterflathierarchy14.childid = userservicecenter15.servicecenterid)
37. 0.001 0.011 ↓ 0.0 0 1

Nested Loop (cost=0.15..9.26 rows=1 width=16) (actual time=0.010..0.011 rows=0 loops=1)

  • Output: departmentgroupflathierarchy16.parentid
  • Join Filter: (userdepartmentgroup17.departmentgroupid = departmentgroupflathierarchy16.childid)
38. 0.010 0.010 ↓ 0.0 0 1

Index Only Scan using ix4ucc_userdepartmentgroupstartend on e3ec273096164aa4a2a67621ca1e0643.userdepartmentgroup userdepartmentgroup17 (cost=0.15..8.24 rows=1 width=16) (actual time=0.009..0.010 rows=0 loops=1)

  • Output: userdepartmentgroup17.userid, userdepartmentgroup17.departmentgroupid, userdepartmentgroup17.startdate, userdepartmentgroup17.enddate
  • Index Cond: ((userdepartmentgroup17.userid = 376) AND (userdepartmentgroup17.startdate <= '2019-09-30'::date) AND (userdepartmentgroup17.enddate >= '2019-09-22'::date))
  • Heap Fetches: 0
39. 0.000 0.000 ↓ 0.0 0

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.departmentgroupflathierarchy departmentgroupflathierarchy16 (cost=0.00..1.01 rows=1 width=32) (never executed)

  • Output: departmentgroupflathierarchy16.parentid, departmentgroupflathierarchy16.childid, departmentgroupflathierarchy16.parentdepth
40. 0.001 0.011 ↓ 0.0 0 1

Nested Loop (cost=4.35..21.96 rows=6 width=16) (actual time=0.010..0.011 rows=0 loops=1)

  • Output: ployeetypegroupflathierarchy18.parentid
41. 0.010 0.010 ↓ 0.0 0 1

Index Only Scan using ix4ucc_useremployeetypegroupstartend on e3ec273096164aa4a2a67621ca1e0643.useremployeetypegroup useremployeetypegroup19 (cost=0.15..8.24 rows=1 width=16) (actual time=0.009..0.010 rows=0 loops=1)

  • Output: useremployeetypegroup19.userid, useremployeetypegroup19.employeetypegroupid, useremployeetypegroup19.startdate, useremployeetypegroup19.enddate
  • Index Cond: ((useremployeetypegroup19.userid = 376) AND (useremployeetypegroup19.startdate <= '2019-09-30'::date) AND (useremployeetypegroup19.enddate >= '2019-09-22'::date))
  • Heap Fetches: 0
42. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.employeetypegroupflathierarchy ployeetypegroupflathierarchy18 (cost=4.20..13.67 rows=6 width=32) (never executed)

  • Output: ployeetypegroupflathierarchy18.parentid, ployeetypegroupflathierarchy18.childid, ployeetypegroupflathierarchy18.parentdepth
  • Recheck Cond: (ployeetypegroupflathierarchy18.childid = useremployeetypegroup19.employeetypegroupid)
43. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ixetgfhchildid (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (ployeetypegroupflathierarchy18.childid = useremployeetypegroup19.employeetypegroupid)
44. 787.975 60,713.400 ↑ 1.0 1 367,960

Nested Loop (cost=223.74..1,273.87 rows=1 width=0) (actual time=0.165..0.165 rows=1 loops=367,960)

45.          

Initplan (for Nested Loop)

46. 0.002 0.124 ↓ 0.0 0 1

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.timesheetpolicysettingssnapshot esheetpolicysettingssnapshot37 (cost=14.93..18.96 rows=1 width=0) (actual time=0.124..0.124 rows=0 loops=1)

  • Recheck Cond: ((esheetpolicysettingssnapshot37.timesheetid = '484e44e6-7209-4c91-9351-961796dc98d2'::uuid) AND (upper(esheetpolicysettingssnapshot37.key) = 'URN:REPLICON:POLICY:TIMESHEET:TASK-DATERANGE-ENTRY-OVERRIDE'::text) AND (upper(esheetpolicysettingssnapshot37.uri) = 'URN:REPLICON:POLICY:TIMESHEET:TASK-DATERANGE-ENTRY-OVERRIDE:OVERRIDE-TASK-DATERANGE-ENTRY'::text))
47. 0.001 0.122 ↓ 0.0 0 1

BitmapAnd (cost=14.93..14.93 rows=1 width=0) (actual time=0.122..0.122 rows=0 loops=1)

48. 0.037 0.037 ↑ 1.5 60 1

Bitmap Index Scan on ixtpsstimesheetid (cost=0.00..5.11 rows=91 width=0) (actual time=0.036..0.037 rows=60 loops=1)

  • Index Cond: (esheetpolicysettingssnapshot37.timesheetid = '484e44e6-7209-4c91-9351-961796dc98d2'::uuid)
49. 0.084 0.084 ↓ 0.0 0 1

Bitmap Index Scan on ixtspss2key (cost=0.00..9.57 rows=89 width=0) (actual time=0.083..0.084 rows=0 loops=1)

  • Index Cond: ((upper(esheetpolicysettingssnapshot37.key) = 'URN:REPLICON:POLICY:TIMESHEET:TASK-DATERANGE-ENTRY-OVERRIDE'::text) AND (upper(esheetpolicysettingssnapshot37.uri) = 'URN:REPLICON:POLICY:TIMESHEET:TASK-DATERANGE-ENTRY-OVERRIDE:OVERRIDE-TASK-DATERANGE-ENTRY'::text))
50. 1,424.446 58,873.600 ↑ 1.0 1 367,960

Hash Join (cost=204.37..1,246.46 rows=1 width=4) (actual time=0.158..0.160 rows=1 loops=367,960)

  • Output: taskteammemberassignment21.taskid
  • Inner Unique: true
  • Hash Cond: (projectteammember20.id = taskteammemberassignment21.projectteammemberid)
51. 54,873.189 54,873.434 ↑ 615.0 1 356,321

Index Scan using uix3ptm_projectrole on e3ec273096164aa4a2a67621ca1e0643.projectteammember projectteammember20 (cost=126.21..1,166.69 rows=615 width=16) (actual time=0.118..0.154 rows=1 loops=356,321)

  • Output: projectteammember20.id, projectteammember20.projectid, projectteammember20.userid, projectteammember20.departmentid, projectteammember20.placeholderprojectroleid, projectteammember20.index, projectteammember20.placeholderdisplayname, projectteammember20.locationid, projectteammember20.divisionid, projectteammember20.costcenterid, projectteammember20.servicecenterid, projectteammember20.departmentgroupid, projectteammember20.employeetypegroupid
  • Index Cond: (projectteammember20.projectid = p.id)
  • Filter: ((projectteammember20.userid = 376) OR (hashed SubPlan 20) OR (hashed SubPlan 21) OR (hashed SubPlan 22) OR (hashed SubPlan 23) OR (hashed SubPlan 24) OR (hashed SubPlan 25) OR (hashed SubPlan 26))
  • Rows Removed by Filter: 384
52.          

SubPlan (for Index Scan)

53. 0.045 0.127 ↑ 1.0 2 1

Nested Loop (cost=0.28..11.75 rows=2 width=4) (actual time=0.043..0.127 rows=2 loops=1)

  • Output: departmentflathierarchy23.parentid
  • Join Filter: (departmentusers24.departmentid = departmentflathierarchy23.childid)
  • Rows Removed by Join Filter: 107
54. 0.029 0.029 ↑ 1.0 1 1

Index Scan using ixduuserid on e3ec273096164aa4a2a67621ca1e0643.departmentusers departmentusers24 (cost=0.28..8.30 rows=1 width=4) (actual time=0.028..0.029 rows=1 loops=1)

  • Output: departmentusers24.departmentid, departmentusers24.userid, departmentusers24.isprimarydepartment
  • Index Cond: (departmentusers24.userid = 376)
55. 0.053 0.053 ↑ 1.0 109 1

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.departmentflathierarchy departmentflathierarchy23 (cost=0.00..2.09 rows=109 width=8) (actual time=0.012..0.053 rows=109 loops=1)

  • Output: departmentflathierarchy23.parentid, departmentflathierarchy23.childid, departmentflathierarchy23.depth
56. 0.004 0.063 ↑ 2.0 1 1

Nested Loop (cost=4.57..16.84 rows=2 width=16) (actual time=0.060..0.063 rows=1 loops=1)

  • Output: locationflathierarchy25.parentid
57. 0.051 0.051 ↑ 1.0 1 1

Index Only Scan using ix4ul_userlocationstartend on e3ec273096164aa4a2a67621ca1e0643.userlocation userlocation26 (cost=0.28..8.30 rows=1 width=16) (actual time=0.050..0.051 rows=1 loops=1)

  • Output: userlocation26.userid, userlocation26.locationid, userlocation26.startdate, userlocation26.enddate
  • Index Cond: ((userlocation26.userid = 376) AND (userlocation26.startdate <= '2019-09-30'::date) AND (userlocation26.enddate >= '2019-09-22'::date))
  • Heap Fetches: 1
58. 0.004 0.008 ↑ 2.0 1 1

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.locationflathierarchy locationflathierarchy25 (cost=4.29..8.52 rows=2 width=32) (actual time=0.007..0.008 rows=1 loops=1)

  • Output: locationflathierarchy25.parentid, locationflathierarchy25.childid, locationflathierarchy25.parentdepth
  • Recheck Cond: (locationflathierarchy25.childid = userlocation26.locationid)
  • Heap Blocks: exact=1
59. 0.004 0.004 ↑ 2.0 1 1

Bitmap Index Scan on ixlfhchildid (cost=0.00..4.29 rows=2 width=0) (actual time=0.004..0.004 rows=1 loops=1)

  • Index Cond: (locationflathierarchy25.childid = userlocation26.locationid)
60. 0.001 0.012 ↓ 0.0 0 1

Nested Loop (cost=4.35..21.96 rows=6 width=16) (actual time=0.011..0.012 rows=0 loops=1)

  • Output: divisionflathierarchy27.parentid
61. 0.011 0.011 ↓ 0.0 0 1

Index Only Scan using ix4ud_userdivisionstartend on e3ec273096164aa4a2a67621ca1e0643.userdivision userdivision28 (cost=0.15..8.24 rows=1 width=16) (actual time=0.010..0.011 rows=0 loops=1)

  • Output: userdivision28.userid, userdivision28.divisionid, userdivision28.startdate, userdivision28.enddate
  • Index Cond: ((userdivision28.userid = 376) AND (userdivision28.startdate <= '2019-09-30'::date) AND (userdivision28.enddate >= '2019-09-22'::date))
  • Heap Fetches: 0
62. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.divisionflathierarchy divisionflathierarchy27 (cost=4.20..13.67 rows=6 width=32) (never executed)

  • Output: divisionflathierarchy27.parentid, divisionflathierarchy27.childid, divisionflathierarchy27.parentdepth
  • Recheck Cond: (divisionflathierarchy27.childid = userdivision28.divisionid)
63. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ixdivfhchildid (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (divisionflathierarchy27.childid = userdivision28.divisionid)
64. 0.001 0.012 ↓ 0.0 0 1

Nested Loop (cost=4.35..21.96 rows=6 width=16) (actual time=0.011..0.012 rows=0 loops=1)

  • Output: costcenterflathierarchy29.parentid
65. 0.011 0.011 ↓ 0.0 0 1

Index Only Scan using ix4ucc_usercostcenterstartend on e3ec273096164aa4a2a67621ca1e0643.usercostcenter usercostcenter30 (cost=0.15..8.24 rows=1 width=16) (actual time=0.010..0.011 rows=0 loops=1)

  • Output: usercostcenter30.userid, usercostcenter30.costcenterid, usercostcenter30.startdate, usercostcenter30.enddate
  • Index Cond: ((usercostcenter30.userid = 376) AND (usercostcenter30.startdate <= '2019-09-30'::date) AND (usercostcenter30.enddate >= '2019-09-22'::date))
  • Heap Fetches: 0
66. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.costcenterflathierarchy costcenterflathierarchy29 (cost=4.20..13.67 rows=6 width=32) (never executed)

  • Output: costcenterflathierarchy29.parentid, costcenterflathierarchy29.childid, costcenterflathierarchy29.parentdepth
  • Recheck Cond: (costcenterflathierarchy29.childid = usercostcenter30.costcenterid)
67. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ixccfhchildid (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (costcenterflathierarchy29.childid = usercostcenter30.costcenterid)
68. 0.001 0.009 ↓ 0.0 0 1

Nested Loop (cost=4.35..21.96 rows=6 width=16) (actual time=0.009..0.009 rows=0 loops=1)

  • Output: servicecenterflathierarchy31.parentid
69. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using ix4usc_userservicecenterstartend on e3ec273096164aa4a2a67621ca1e0643.userservicecenter userservicecenter32 (cost=0.15..8.24 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1)

  • Output: userservicecenter32.userid, userservicecenter32.servicecenterid, userservicecenter32.startdate, userservicecenter32.enddate
  • Index Cond: ((userservicecenter32.userid = 376) AND (userservicecenter32.startdate <= '2019-09-30'::date) AND (userservicecenter32.enddate >= '2019-09-22'::date))
  • Heap Fetches: 0
70. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.servicecenterflathierarchy servicecenterflathierarchy31 (cost=4.20..13.67 rows=6 width=32) (never executed)

  • Output: servicecenterflathierarchy31.parentid, servicecenterflathierarchy31.childid, servicecenterflathierarchy31.parentdepth
  • Recheck Cond: (servicecenterflathierarchy31.childid = userservicecenter32.servicecenterid)
71. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ixscfhchildid (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (servicecenterflathierarchy31.childid = userservicecenter32.servicecenterid)
72. 0.001 0.009 ↓ 0.0 0 1

Nested Loop (cost=0.15..9.26 rows=1 width=16) (actual time=0.008..0.009 rows=0 loops=1)

  • Output: departmentgroupflathierarchy33.parentid
  • Join Filter: (userdepartmentgroup34.departmentgroupid = departmentgroupflathierarchy33.childid)
73. 0.008 0.008 ↓ 0.0 0 1

Index Only Scan using ix4ucc_userdepartmentgroupstartend on e3ec273096164aa4a2a67621ca1e0643.userdepartmentgroup userdepartmentgroup34 (cost=0.15..8.24 rows=1 width=16) (actual time=0.008..0.008 rows=0 loops=1)

  • Output: userdepartmentgroup34.userid, userdepartmentgroup34.departmentgroupid, userdepartmentgroup34.startdate, userdepartmentgroup34.enddate
  • Index Cond: ((userdepartmentgroup34.userid = 376) AND (userdepartmentgroup34.startdate <= '2019-09-30'::date) AND (userdepartmentgroup34.enddate >= '2019-09-22'::date))
  • Heap Fetches: 0
74. 0.000 0.000 ↓ 0.0 0

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.departmentgroupflathierarchy departmentgroupflathierarchy33 (cost=0.00..1.01 rows=1 width=32) (never executed)

  • Output: departmentgroupflathierarchy33.parentid, departmentgroupflathierarchy33.childid, departmentgroupflathierarchy33.parentdepth
75. 0.001 0.013 ↓ 0.0 0 1

Nested Loop (cost=4.35..21.96 rows=6 width=16) (actual time=0.012..0.013 rows=0 loops=1)

  • Output: ployeetypegroupflathierarchy35.parentid
76. 0.012 0.012 ↓ 0.0 0 1

Index Only Scan using ix4ucc_useremployeetypegroupstartend on e3ec273096164aa4a2a67621ca1e0643.useremployeetypegroup useremployeetypegroup36 (cost=0.15..8.24 rows=1 width=16) (actual time=0.011..0.012 rows=0 loops=1)

  • Output: useremployeetypegroup36.userid, useremployeetypegroup36.employeetypegroupid, useremployeetypegroup36.startdate, useremployeetypegroup36.enddate
  • Index Cond: ((useremployeetypegroup36.userid = 376) AND (useremployeetypegroup36.startdate <= '2019-09-30'::date) AND (useremployeetypegroup36.enddate >= '2019-09-22'::date))
  • Heap Fetches: 0
77. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.employeetypegroupflathierarchy ployeetypegroupflathierarchy35 (cost=4.20..13.67 rows=6 width=32) (never executed)

  • Output: ployeetypegroupflathierarchy35.parentid, ployeetypegroupflathierarchy35.childid, ployeetypegroupflathierarchy35.parentdepth
  • Recheck Cond: (ployeetypegroupflathierarchy35.childid = useremployeetypegroup36.employeetypegroupid)
78. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on ixetgfhchildid (cost=0.00..4.20 rows=6 width=0) (never executed)

  • Index Cond: (ployeetypegroupflathierarchy35.childid = useremployeetypegroup36.employeetypegroupid)
79. 735.920 2,575.720 ↑ 17.0 2 367,960

Hash (cost=77.73..77.73 rows=34 width=20) (actual time=0.007..0.007 rows=2 loops=367,960)

  • Output: taskteammemberassignment21.projectteammemberid, taskteammemberassignment21.taskid
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
80. 1,839.800 1,839.800 ↑ 17.0 2 367,960

Index Only Scan using uix2projectteammembertaskassignment on e3ec273096164aa4a2a67621ca1e0643.taskteammemberassignment taskteammemberassignment21 (cost=0.42..77.73 rows=34 width=20) (actual time=0.003..0.005 rows=2 loops=367,960)

  • Output: taskteammemberassignment21.projectteammemberid, taskteammemberassignment21.taskid
  • Index Cond: (taskteammemberassignment21.taskid = task.id)
  • Heap Fetches: 861,380
81. 1,051.701 1,051.701 ↑ 1.0 1 350,567

Index Scan using task_pkey on e3ec273096164aa4a2a67621ca1e0643.task task22 (cost=0.42..8.45 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=350,567)

  • Output: task22.id, task22.projectid, task22.parentid, task22.name, task22.code, task22.description, task22.isclosed, task22.orderindex, task22.percentcomplete, task22.istimeentryallowed, task22.estimatedhours, task22.timeentrystartdate, task22.timeentryenddate, task22.estimatedexpenses, task22.estimatedexpensescurrencyid, task22.expenseentrystartdate, task22.expenseentryenddate, task22.costtype, task22.estimatedcost, task22.estimatedcostcurrencyid, task22.timeandexpenseentrytype, task22.info1, task22.info2, task22.info3, task22.info4, task22.info5, task22.info6, task22.info7, task22.info8, task22.info9, task22.info10, task22.info11, task22.info12, task22.info13, task22.info14, task22.info15, task22.info16, task22.info17, task22.info18, task22.info19, task22.info20
  • Index Cond: (task22.id = task.id)
  • Filter: (task22.istimeentryallowed AND (NOT task22.isclosed) AND ($43 OR ((task22.timeentrystartdate <= '2019-09-30'::date) AND (task22.timeentryenddate >= '2019-09-22'::date))))
  • Rows Removed by Filter: 0
82. 0.005 0.017 ↑ 1.0 1 1

Hash (cost=1.09..1.09 rows=1 width=16) (actual time=0.016..0.017 rows=1 loops=1)

  • Output: projectstatuslabel3.id
  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
83. 0.012 0.012 ↑ 1.0 1 1

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.projectstatuslabel projectstatuslabel3 (cost=0.00..1.09 rows=1 width=16) (actual time=0.010..0.012 rows=1 loops=1)

  • Output: projectstatuslabel3.id
  • Filter: (projectstatuslabel3.projectstatustype = 1)
  • Rows Removed by Filter: 6
84.          

SubPlan (for Hash Join)

85. 1,175.416 1,175.416 ↑ 1.0 1 587,708

Index Scan using project_pkey on e3ec273096164aa4a2a67621ca1e0643.project project2 (cost=0.28..8.29 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=587,708)

  • Output: project2.projectstatuslabelid
  • Index Cond: (project2.id = p.id)
86. 776.430 2,070.480 ↑ 1.0 2 258,810

Append (cost=0.28..0.59 rows=2 width=8) (actual time=0.003..0.008 rows=2 loops=258,810)

87. 776.430 776.430 ↑ 1.0 1 258,810

Index Only Scan using uix4pc_projectclienteffectiveend on e3ec273096164aa4a2a67621ca1e0643.projectclient (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.003 rows=1 loops=258,810)

  • Output: projectclient.projectid, projectclient.clientid
  • Index Cond: (projectclient.projectid = task.projectid)
  • Heap Fetches: 259,030
88. 517.620 517.620 ↑ 1.0 1 258,810

Index Only Scan using project_pkey on e3ec273096164aa4a2a67621ca1e0643.project project_1 (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=258,810)

  • Output: project_1.id, NULL::integer
  • Index Cond: (project_1.id = task.projectid)
  • Heap Fetches: 258,810
89. 0.172 0.339 ↑ 1.0 297 1

Hash (cost=7.97..7.97 rows=297 width=56) (actual time=0.339..0.339 rows=297 loops=1)

  • Output: c.id, c.name, c.slug, c.code
  • Buckets: 1,024 Batches: 1 Memory Usage: 34kB
90. 0.167 0.167 ↑ 1.0 297 1

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.clients c (cost=0.00..7.97 rows=297 width=56) (actual time=0.009..0.167 rows=297 loops=1)

  • Output: c.id, c.name, c.slug, c.code
91.          

SubPlan (for Hash Left Join)

92. 0.000 0.000 ↓ 0.0 0

Index Only Scan using uix4pc_projectclienteffectiveend on e3ec273096164aa4a2a67621ca1e0643.projectclient projectclient1 (cost=0.28..8.30 rows=1 width=0) (never executed)

  • Index Cond: ((projectclient1.projectid = p.id) AND (projectclient1.effectivedate <= '2019-09-30'::date) AND (projectclient1.enddate >= '2019-09-22'::date))
  • Heap Fetches: 0
93. 0.850 0.850 ↑ 1.0 1,716 1

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.projectclient projectclient1_1 (cost=0.00..40.74 rows=1,716 width=4) (actual time=0.006..0.850 rows=1,716 loops=1)

  • Output: projectclient1_1.projectid
  • Filter: ((projectclient1_1.effectivedate <= '2019-09-30'::date) AND (projectclient1_1.enddate >= '2019-09-22'::date))
94. 486.402 486.402 ↑ 1.0 1 243,201

Index Only Scan using uix4pc_projectclienteffectiveend on e3ec273096164aa4a2a67621ca1e0643.projectclient projectclient1_2 (cost=0.28..8.30 rows=1 width=4) (actual time=0.002..0.002 rows=1 loops=243,201)

  • Output: projectclient1_2.clientid
  • Index Cond: ((projectclient1_2.projectid = p.id) AND (projectclient1_2.effectivedate <= '2019-09-30'::date) AND (projectclient1_2.enddate >= '2019-09-22'::date))
  • Heap Fetches: 243,525
Planning time : 14.603 ms
Execution time : 69,700.628 ms