explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 3CeM

Settings
# exclusive inclusive rows x rows loops node
1. 0.073 69,202.536 ↑ 1.0 11 1

Limit (cost=245,895.58..245,895.61 rows=11 width=2,428) (actual time=69,202.460..69,202.536 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. 256.508 69,202.463 ↑ 719.5 11 1

Sort (cost=245,895.58..245,915.37 rows=7,914 width=2,428) (actual time=69,202.458..69,202.463 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. 738.713 68,945.955 ↓ 30.7 243,303 1

Nested Loop (cost=99.22..245,719.13 rows=7,914 width=2,428) (actual time=3.060..68,945.955 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.021 0.021 ↑ 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.018..0.021 rows=1 loops=1)

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

Hash Left Join (cost=98.81..245,374.35 rows=7,914 width=136) (actual time=3.032..68,207.221 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. 674.585 66,405.653 ↓ 32.7 517,840 1

Nested Loop (cost=87.12..244,652.33 rows=15,828 width=84) (actual time=2.685..66,405.653 rows=517,840 loops=1)

  • Output: p.id, p.name, p.slug, p.code, task.id, task.name, task.code, projectclient.clientid
7. 1,242.823 63,660.588 ↓ 281.0 258,810 1

Hash Join (cost=86.85..244,089.74 rows=921 width=84) (actual time=2.666..63,660.588 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. 741.115 61,242.336 ↓ 1.8 328,898 1

Hash Join (cost=85.75..163,781.34 rows=184,149 width=84) (actual time=2.586..61,242.336 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. 287.192 512.060 ↓ 1.0 369,683 1

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

10. 223.909 223.909 ↓ 1.0 367,960 1

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

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

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

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

Hash (cost=64.22..64.22 rows=1,722 width=66) (actual time=2.181..2.181 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.201 1.201 ↓ 1.0 1,723 1

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.project p (cost=0.00..64.22 rows=1,722 width=66) (actual time=0.004..1.201 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.857 6.054 ↓ 0.0 0 1,009

Index Scan using uix3ptm_projectrole on e3ec273096164aa4a2a67621ca1e0643.projectteammember projectteammember5 (cost=126.21..1,197.26 rows=635 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.045 0.103 ↑ 1.0 2 1

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

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

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

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

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

  • Output: departmentflathierarchy6.parentid, departmentflathierarchy6.childid, departmentflathierarchy6.depth
21. 0.006 0.029 ↑ 2.0 1 1

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

  • Output: locationflathierarchy8.parentid
22. 0.012 0.012 ↑ 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.011..0.012 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.005 0.011 ↑ 2.0 1 1

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.locationflathierarchy locationflathierarchy8 (cost=4.29..8.52 rows=2 width=32) (actual time=0.010..0.011 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.012 ↓ 0.0 0 1

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

  • Output: divisionflathierarchy10.parentid
26. 0.011 0.011 ↓ 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.011 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.000 0.012 ↓ 0.0 0 1

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

  • Output: costcenterflathierarchy12.parentid
30. 0.012 0.012 ↓ 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.011..0.012 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.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: servicecenterflathierarchy14.parentid
34. 0.010 0.010 ↓ 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.009..0.010 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.019 ↓ 0.0 0 1

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

  • Output: ployeetypegroupflathierarchy18.parentid
41. 0.018 0.018 ↓ 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.017..0.018 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. 788.045 59,977.480 ↑ 1.0 1 367,960

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

45.          

Initplan (for Nested Loop)

46. 0.001 0.054 ↓ 0.0 0 1

Bitmap Heap Scan on e3ec273096164aa4a2a67621ca1e0643.timesheetpolicysettingssnapshot esheetpolicysettingssnapshot37 (cost=14.93..18.96 rows=1 width=0) (actual time=0.054..0.054 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.002 0.053 ↓ 0.0 0 1

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

48. 0.016 0.016 ↑ 1.5 60 1

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

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

Bitmap Index Scan on ixtspss2key (cost=0.00..9.57 rows=89 width=0) (actual time=0.034..0.035 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,401.168 58,137.680 ↑ 1.0 1 367,960

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

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

Index Scan using uix3ptm_projectrole on e3ec273096164aa4a2a67621ca1e0643.projectteammember projectteammember20 (cost=126.21..1,197.26 rows=635 width=16) (actual time=0.117..0.152 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.044 0.104 ↑ 1.0 2 1

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

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

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

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

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

  • Output: departmentflathierarchy23.parentid, departmentflathierarchy23.childid, departmentflathierarchy23.depth
56. 0.003 0.030 ↑ 2.0 1 1

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

  • Output: locationflathierarchy25.parentid
57. 0.019 0.019 ↑ 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.018..0.019 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.006..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.003..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.010 ↓ 0.0 0 1

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

  • Output: costcenterflathierarchy29.parentid
65. 0.009 0.009 ↓ 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.008..0.009 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.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: servicecenterflathierarchy31.parentid
69. 0.010 0.010 ↓ 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.009..0.010 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.000 0.009 ↓ 0.0 0 1

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

  • Output: departmentgroupflathierarchy33.parentid
  • Join Filter: (userdepartmentgroup34.departmentgroupid = departmentgroupflathierarchy33.childid)
73. 0.009 0.009 ↓ 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.009 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.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: ployeetypegroupflathierarchy35.parentid
76. 0.011 0.011 ↓ 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.010..0.011 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.013 ↑ 1.0 1 1

Hash (cost=1.09..1.09 rows=1 width=16) (actual time=0.012..0.013 rows=1 loops=1)

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

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.projectstatuslabel projectstatuslabel3 (cost=0.00..1.09 rows=1 width=16) (actual time=0.006..0.008 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.167 0.315 ↑ 1.0 297 1

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

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

Seq Scan on e3ec273096164aa4a2a67621ca1e0643.clients c (cost=0.00..7.97 rows=297 width=56) (actual time=0.005..0.148 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.835 0.835 ↑ 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.835 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 : 9.786 ms
Execution time : 69,203.759 ms