explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8dFd

Settings
# exclusive inclusive rows x rows loops node
1. 0.075 68,010.719 ↑ 1.0 11 1

Limit (cost=244,048.01..244,048.03 rows=11 width=2,428) (actual time=68,010.642..68,010.719 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. 242.966 68,010.644 ↑ 719.5 11 1

Sort (cost=244,048.01..244,067.79 rows=7,914 width=2,428) (actual time=68,010.639..68,010.644 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. 686.242 67,767.678 ↓ 30.7 243,303 1

Nested Loop (cost=99.22..243,871.55 rows=7,914 width=2,428) (actual time=2.644..67,767.678 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.017 0.017 ↑ 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.014..0.017 rows=1 loops=1)

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

Hash Left Join (cost=98.81..243,526.77 rows=7,914 width=136) (actual time=2.621..67,081.419 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. 571.829 65,359.505 ↓ 32.7 517,840 1

Nested Loop (cost=87.12..242,804.75 rows=15,828 width=84) (actual time=2.295..65,359.505 rows=517,840 loops=1)

  • Output: p.id, p.name, p.slug, p.code, task.id, task.name, task.code, projectclient.clientid
7. 1,127.731 62,717.196 ↓ 281.0 258,810 1

Hash Join (cost=86.85..242,242.16 rows=921 width=84) (actual time=2.284..62,717.196 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. 664.855 60,414.038 ↓ 1.8 328,898 1

Hash Join (cost=85.75..161,933.76 rows=184,149 width=84) (actual time=2.208..60,414.038 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. 271.843 496.251 ↓ 1.0 369,683 1

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

10. 223.460 223.460 ↓ 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.460 rows=367,960 loops=1)

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

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

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

Hash (cost=64.22..64.22 rows=1,722 width=66) (actual time=1.871..1.872 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. 0.958 0.958 ↓ 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..0.958 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.884 6.054 ↓ 0.0 0 1,009

Index Scan using uix3ptm_projectrole on e3ec273096164aa4a2a67621ca1e0643.projectteammember projectteammember5 (cost=126.21..1,180.02 rows=624 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.101 ↑ 1.0 2 1

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

  • Output: departmentflathierarchy6.parentid
  • Join Filter: (departmentusers7.departmentid = departmentflathierarchy6.childid)
  • Rows Removed by Join Filter: 107
19. 0.008 0.008 ↑ 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.008 rows=1 loops=1)

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

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

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

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

  • Output: locationflathierarchy8.parentid
22. 0.011 0.011 ↑ 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.010..0.011 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.009..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.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: divisionflathierarchy10.parentid
26. 0.009 0.009 ↓ 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.008..0.009 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.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: costcenterflathierarchy12.parentid
30. 0.010 0.010 ↓ 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.010 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.008 ↓ 0.0 0 1

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

  • Output: servicecenterflathierarchy14.parentid
34. 0.008 0.008 ↓ 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.007..0.008 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.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: departmentgroupflathierarchy16.parentid
  • Join Filter: (userdepartmentgroup17.departmentgroupid = departmentgroupflathierarchy16.childid)
38. 0.008 0.008 ↓ 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.007..0.008 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.000 0.004 ↓ 0.0 0 1

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

  • Output: ployeetypegroupflathierarchy18.parentid
41. 0.004 0.004 ↓ 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.003..0.004 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. 420.085 59,241.560 ↑ 1.0 1 367,960

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

45.          

Initplan (for Nested Loop)

46. 0.002 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.053..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.001 0.052 ↓ 0.0 0 1

BitmapAnd (cost=14.93..14.93 rows=1 width=0) (actual time=0.051..0.052 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.015..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,389.529 57,769.720 ↑ 1.0 1 367,960

Hash Join (cost=204.37..1,259.81 rows=1 width=4) (actual time=0.154..0.157 rows=1 loops=367,960)

  • Output: taskteammemberassignment21.taskid
  • Inner Unique: true
  • Hash Cond: (projectteammember20.id = taskteammemberassignment21.projectteammemberid)
51. 53,804.301 53,804.471 ↑ 624.0 1 356,321

Index Scan using uix3ptm_projectrole on e3ec273096164aa4a2a67621ca1e0643.projectteammember projectteammember20 (cost=126.21..1,180.02 rows=624 width=16) (actual time=0.116..0.151 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.102 ↑ 1.0 2 1

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

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

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

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

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

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

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

  • Output: locationflathierarchy25.parentid
57. 0.018 0.018 ↑ 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.017..0.018 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.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: divisionflathierarchy27.parentid
61. 0.009 0.009 ↓ 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.009..0.009 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.000 0.007 ↓ 0.0 0 1

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

  • Output: costcenterflathierarchy29.parentid
65. 0.007 0.007 ↓ 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.006..0.007 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.008 ↓ 0.0 0 1

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

  • Output: servicecenterflathierarchy31.parentid
69. 0.007 0.007 ↓ 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.007..0.007 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.008 ↓ 0.0 0 1

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

  • Output: departmentgroupflathierarchy33.parentid
  • Join Filter: (userdepartmentgroup34.departmentgroupid = departmentgroupflathierarchy33.childid)
73. 0.007 0.007 ↓ 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.007..0.007 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.006 ↓ 0.0 0 1

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

  • Output: ployeetypegroupflathierarchy35.parentid
76. 0.005 0.005 ↓ 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.005..0.005 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.003 0.011 ↑ 1.0 1 1

Hash (cost=1.09..1.09 rows=1 width=16) (actual time=0.010..0.011 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. 1,035.240 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. 517.620 517.620 ↑ 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.002 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.163 0.311 ↑ 1.0 297 1

Hash (cost=7.97..7.97 rows=297 width=56) (actual time=0.310..0.311 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.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 : 8.324 ms
Execution time : 68,011.511 ms