explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FuqJ

Settings
# exclusive inclusive rows x rows loops node
1. 0.054 519.605 ↓ 11.0 33 1

Sort (cost=17,749.49..17,749.49 rows=3 width=577) (actual time=519.603..519.605 rows=33 loops=1)

  • Output: ftaskselector_nextgen20.taskid, ftaskselector_nextgen20.parenttaskid, ftaskselector_nextgen20.taskname, ftaskselector_nextgen20.taskcode, ftaskselector_nextgen20.taskstartdate, ftaskselector_nextgen20.taskenddate, ftaskselector_nextgen20.hierarchylevel, ftaskselector_nextgen20.timeentryallowed, ((ftaskselector_nextgen20.taskname)::character varying(255))
  • Sort Key: ftaskselector_nextgen20.taskname COLLATE "en_US
  • Sort Method: quicksort Memory: 30kB
  • Buffers: shared hit=41579
2. 0.008 519.551 ↓ 11.0 33 1

Subquery Scan on ftaskselector_nextgen20 (cost=17,749.37..17,749.46 rows=3 width=577) (actual time=519.535..519.551 rows=33 loops=1)

  • Output: ftaskselector_nextgen20.taskid, ftaskselector_nextgen20.parenttaskid, ftaskselector_nextgen20.taskname, ftaskselector_nextgen20.taskcode, ftaskselector_nextgen20.taskstartdate, ftaskselector_nextgen20.taskenddate, ftaskselector_nextgen20.hierarchylevel, ftaskselector_nextgen20.timeentryallowed, ftaskselector_nextgen20.taskname
  • Buffers: shared hit=41579
3. 0.000 519.543 ↓ 11.0 33 1

Unique (cost=17,749.37..17,749.43 rows=3 width=61) (actual time=519.534..519.543 rows=33 loops=1)

  • Output: alltk.id, alltk.parentid, alltk.name, alltk.code, alltk.timeentrystartdate, alltk.timeentryenddate, alltdh.hierarchysorting, (CASE WHEN (max(CASE WHEN (alltk.id = ttma.taskid) THEN 1 ELSE 0 END) = 1) THEN true ELSE false END)
  • Buffers: shared hit=41579
4.          

Initplan (for Unique)

5. 0.006 0.006 ↑ 1.0 1 1

Index Scan using parameters_pkey on "17f7a637b83349e9b16ef4905eb64944".parameters (cost=0.42..2.44 rows=1 width=1) (actual time=0.006..0.006 rows=1 loops=1)

  • Output: parameters.istaskentrydateoverriden
  • Index Cond: (parameters.backend_pid = pg_backend_pid())
  • Buffers: shared hit=4
6. 0.004 0.004 ↑ 1.0 1 1

Index Scan using parameters_pkey on "17f7a637b83349e9b16ef4905eb64944".parameters parameters_1 (cost=0.42..2.44 rows=1 width=4) (actual time=0.004..0.004 rows=1 loops=1)

  • Output: parameters_1.startdate
  • Index Cond: (parameters_1.backend_pid = pg_backend_pid())
  • Buffers: shared hit=4
7. 0.008 0.008 ↑ 1.0 1 1

Index Scan using parameters_pkey on "17f7a637b83349e9b16ef4905eb64944".parameters parameters_2 (cost=0.42..2.44 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)

  • Output: parameters_2.enddate
  • Index Cond: (parameters_2.backend_pid = pg_backend_pid())
  • Buffers: shared hit=4
8. 0.016 0.016 ↑ 1.0 1 1

Index Scan using parameters_pkey on "17f7a637b83349e9b16ef4905eb64944".parameters parameters_3 (cost=0.42..2.44 rows=1 width=4) (actual time=0.016..0.016 rows=1 loops=1)

  • Output: parameters_3.projectid
  • Index Cond: (parameters_3.backend_pid = pg_backend_pid())
  • Buffers: shared hit=4
9. 0.011 0.011 ↑ 1.0 1 1

Index Scan using parameters_pkey on "17f7a637b83349e9b16ef4905eb64944".parameters parameters_4 (cost=0.42..2.44 rows=1 width=4) (actual time=0.010..0.011 rows=1 loops=1)

  • Output: parameters_4.projectleaderid
  • Index Cond: (parameters_4.backend_pid = pg_backend_pid())
  • Buffers: shared hit=4
10. 0.000 0.000 ↓ 0.0 0

Index Scan using parameters_pkey on "17f7a637b83349e9b16ef4905eb64944".parameters parameters_5 (cost=0.42..2.44 rows=1 width=4) (never executed)

  • Output: parameters_5.projectleaderid
  • Index Cond: (parameters_5.backend_pid = pg_backend_pid())
11. 0.021 519.535 ↓ 11.0 33 1

Sort (cost=17,734.74..17,734.75 rows=3 width=61) (actual time=519.533..519.535 rows=33 loops=1)

  • Output: alltk.id, alltk.parentid, alltk.name, alltk.code, alltk.timeentrystartdate, alltk.timeentryenddate, alltdh.hierarchysorting, (CASE WHEN (max(CASE WHEN (alltk.id = ttma.taskid) THEN 1 ELSE 0 END) = 1) THEN true ELSE false END)
  • Sort Key: alltk.id, alltk.parentid, alltk.name, alltk.code, alltk.timeentrystartdate, alltk.timeentryenddate, alltdh.hierarchysorting, (CASE WHEN (max(CASE WHEN (alltk.id = ttma.taskid) THEN 1 ELSE 0 END) = 1) THEN true ELSE false END)
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=41579
12. 0.022 519.514 ↓ 11.0 33 1

GroupAggregate (cost=17,734.64..17,734.72 rows=3 width=61) (actual time=519.500..519.514 rows=33 loops=1)

  • Output: alltk.id, alltk.parentid, alltk.name, alltk.code, alltk.timeentrystartdate, alltk.timeentryenddate, alltdh.hierarchysorting, CASE WHEN (max(CASE WHEN (alltk.id = ttma.taskid) THEN 1 ELSE 0 END) = 1) THEN true ELSE false END
  • Group Key: alltk.id, alltdh.hierarchysorting
  • Buffers: shared hit=41579
13. 0.028 519.492 ↓ 11.0 33 1

Sort (cost=17,734.64..17,734.65 rows=3 width=64) (actual time=519.490..519.492 rows=33 loops=1)

  • Output: alltk.id, alltdh.hierarchysorting, alltk.parentid, alltk.name, alltk.code, alltk.timeentrystartdate, alltk.timeentryenddate, ttma.taskid
  • Sort Key: alltk.id, alltdh.hierarchysorting
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=41579
14. 0.023 519.464 ↓ 11.0 33 1

Nested Loop (cost=17,609.71..17,734.62 rows=3 width=64) (actual time=399.818..519.464 rows=33 loops=1)

  • Output: alltk.id, alltdh.hierarchysorting, alltk.parentid, alltk.name, alltk.code, alltk.timeentrystartdate, alltk.timeentryenddate, ttma.taskid
  • Inner Unique: true
  • Buffers: shared hit=41579
15. 0.037 519.375 ↓ 11.0 33 1

Nested Loop (cost=17,609.29..17,733.26 rows=3 width=51) (actual time=399.810..519.375 rows=33 loops=1)

  • Output: ttma.taskid, tfh.parentid, alltk.id, alltk.parentid, alltk.name, alltk.code, alltk.timeentrystartdate, alltk.timeentryenddate
  • Inner Unique: true
  • Buffers: shared hit=41447
16. 0.047 519.239 ↓ 11.0 33 1

Nested Loop (cost=17,608.87..17,731.92 rows=3 width=8) (actual time=399.798..519.239 rows=33 loops=1)

  • Output: ttma.taskid, tfh.parentid
  • Join Filter: (ttma.taskid = tfh.childid)
  • Buffers: shared hit=41315
17. 0.054 519.093 ↓ 33.0 33 1

Nested Loop (cost=17,608.44..17,731.31 rows=1 width=12) (actual time=399.784..519.093 rows=33 loops=1)

  • Output: tk.id, ttma.taskid, tdh.taskid
  • Inner Unique: true
  • Buffers: shared hit=41183
18. 0.040 518.940 ↓ 33.0 33 1

Nested Loop Semi Join (cost=17,608.02..17,729.55 rows=1 width=8) (actual time=399.773..518.940 rows=33 loops=1)

  • Output: tk.id, ttma.taskid
  • Buffers: shared hit=41051
19. 32.416 518.702 ↓ 33.0 33 1

Hash Join (cost=17,607.74..17,726.14 rows=1 width=12) (actual time=399.735..518.702 rows=33 loops=1)

  • Output: ttma.taskid, tk.id, tk.projectid
  • Inner Unique: true
  • Hash Cond: (ttma.taskid = tk.id)
  • Buffers: shared hit=40915
20. 227.408 486.177 ↓ 88.4 462,689 1

HashAggregate (cost=17,567.95..17,620.28 rows=5,233 width=4) (actual time=396.559..486.177 rows=462,689 loops=1)

  • Output: ttma.taskid
  • Group Key: ttma.taskid
  • Buffers: shared hit=40873
21. 40.479 258.769 ↓ 88.4 462,689 1

Append (cost=3.71..17,554.87 rows=5,233 width=4) (actual time=9.735..258.769 rows=462,689 loops=1)

  • Buffers: shared hit=40873
22. 56.128 218.242 ↓ 112.6 462,689 1

Nested Loop (cost=3.71..13,623.91 rows=4,109 width=4) (actual time=9.734..218.242 rows=462,689 loops=1)

  • Output: ttma.taskid
  • Buffers: shared hit=40858
23.          

Initplan (for Nested Loop)

24. 0.006 0.006 ↑ 1.0 1 1

Index Scan using parameters_pkey on "17f7a637b83349e9b16ef4905eb64944".parameters parameters_7 (cost=0.42..2.44 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)

  • Output: parameters_7.userid
  • Index Cond: (parameters_7.backend_pid = pg_backend_pid())
  • Buffers: shared hit=4
25. 0.288 23.268 ↑ 1.1 1,780 1

Nested Loop (cost=0.85..12,436.29 rows=1,997 width=16) (actual time=9.709..23.268 rows=1,780 loops=1)

  • Output: ptm.id
  • Buffers: shared hit=7982
26. 0.004 0.026 ↑ 1.0 2 1

Nested Loop (cost=0.42..4.50 rows=2 width=4) (actual time=0.021..0.026 rows=2 loops=1)

  • Output: dfh.parentid
  • Buffers: shared hit=9
27. 0.015 0.015 ↑ 1.0 1 1

Index Scan using ixduuserid on "17f7a637b83349e9b16ef4905eb64944".departmentusers (cost=0.28..2.30 rows=1 width=4) (actual time=0.014..0.015 rows=1 loops=1)

  • Output: departmentusers.departmentid, departmentusers.userid, departmentusers.isprimarydepartment
  • Index Cond: (departmentusers.userid = $7)
  • Buffers: shared hit=7
28. 0.007 0.007 ↑ 1.0 2 1

Index Scan using ixdfhchildid on "17f7a637b83349e9b16ef4905eb64944".departmentflathierarchy dfh (cost=0.14..2.18 rows=2 width=8) (actual time=0.005..0.007 rows=2 loops=1)

  • Output: dfh.parentid, dfh.childid, dfh.depth
  • Index Cond: (dfh.childid = departmentusers.departmentid)
  • Buffers: shared hit=2
29. 22.954 22.954 ↓ 1.0 890 2

Index Scan using uix2ptm_projectdept on "17f7a637b83349e9b16ef4905eb64944".projectteammember ptm (cost=0.42..6,207.35 rows=855 width=20) (actual time=4.842..11.477 rows=890 loops=2)

  • Output: ptm.id, ptm.projectid, ptm.userid, ptm.departmentid, ptm.placeholderprojectroleid, ptm.index, ptm.placeholderdisplayname, ptm.locationid, ptm.divisionid, ptm.costcenterid, ptm.servicecenterid, ptm.departmentgroupid, ptm.employeetypegroupid
  • Index Cond: (ptm.departmentid = dfh.parentid)
  • Buffers: shared hit=7973
30. 138.840 138.840 ↓ 52.0 260 1,780

Index Scan using ixttmaprojectteammemberid on "17f7a637b83349e9b16ef4905eb64944".taskteammemberassignment ttma (cost=0.43..0.54 rows=5 width=20) (actual time=0.008..0.078 rows=260 loops=1,780)

  • Output: ttma.id, ttma.taskid, ttma.projectteammemberid, ttma.taskallocationstartdate, ttma.taskallocationenddate, ttma.taskallocationduration
  • Index Cond: (ttma.projectteammemberid = ptm.id)
  • Buffers: shared hit=32876
31. 0.000 0.048 ↓ 0.0 0 1

Nested Loop (cost=3.29..3,878.63 rows=1,124 width=4) (actual time=0.048..0.048 rows=0 loops=1)

  • Output: ttma_1.taskid
  • Buffers: shared hit=15
32.          

Initplan (for Nested Loop)

33. 0.013 0.013 ↑ 1.0 1 1

Index Scan using parameters_pkey on "17f7a637b83349e9b16ef4905eb64944".parameters parameters_6 (cost=0.42..2.44 rows=1 width=4) (actual time=0.012..0.013 rows=1 loops=1)

  • Output: parameters_6.userid
  • Index Cond: (parameters_6.backend_pid = pg_backend_pid())
  • Buffers: shared hit=4
34. 0.047 0.047 ↓ 0.0 0 1

Index Scan using uix2ptm_projectuser on "17f7a637b83349e9b16ef4905eb64944".projectteammember ptm_1 (cost=0.42..534.97 rows=546 width=16) (actual time=0.047..0.047 rows=0 loops=1)

  • Output: ptm_1.id, ptm_1.projectid, ptm_1.userid, ptm_1.departmentid, ptm_1.placeholderprojectroleid, ptm_1.index, ptm_1.placeholderdisplayname, ptm_1.locationid, ptm_1.divisionid, ptm_1.costcenterid, ptm_1.servicecenterid, ptm_1.departmentgroupid, ptm_1.employeetypegroupid
  • Index Cond: (ptm_1.userid = $6)
  • Buffers: shared hit=15
35. 0.000 0.000 ↓ 0.0 0

Index Scan using ixttmaprojectteammemberid on "17f7a637b83349e9b16ef4905eb64944".taskteammemberassignment ttma_1 (cost=0.43..6.07 rows=5 width=20) (never executed)

  • Output: ttma_1.id, ttma_1.taskid, ttma_1.projectteammemberid, ttma_1.taskallocationstartdate, ttma_1.taskallocationenddate, ttma_1.taskallocationduration
  • Index Cond: (ttma_1.projectteammemberid = ptm_1.id)
36. 0.007 0.109 ↑ 3.0 33 1

Hash (cost=38.55..38.55 rows=99 width=8) (actual time=0.109..0.109 rows=33 loops=1)

  • Output: tk.id, tk.projectid
  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
  • Buffers: shared hit=42
37. 0.102 0.102 ↑ 3.0 33 1

Index Scan using ixtkprojectid on "17f7a637b83349e9b16ef4905eb64944".task tk (cost=0.42..38.55 rows=99 width=8) (actual time=0.062..0.102 rows=33 loops=1)

  • Output: tk.id, tk.projectid
  • Index Cond: (tk.projectid = $3)
  • Filter: (tk.istimeentryallowed AND ($0 OR (($1 <= tk.timeentryenddate) AND ($2 >= tk.timeentrystartdate))))
  • Buffers: shared hit=42
38. 0.033 0.198 ↑ 1.0 1 33

Nested Loop (cost=0.28..3.40 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=33)

  • Output: project.id
  • Inner Unique: true
  • Join Filter: (project.projectstatuslabelid = projectstatuslabel.id)
  • Buffers: shared hit=136
39. 0.132 0.132 ↑ 1.0 1 33

Index Scan using project_pkey on "17f7a637b83349e9b16ef4905eb64944".project (cost=0.28..2.30 rows=1 width=20) (actual time=0.004..0.004 rows=1 loops=33)

  • Output: project.projectstatuslabelid, project.id
  • Index Cond: (project.id = $3)
  • Filter: (($4 IS NULL) OR ($5 = project.projectleaderapproverid))
  • Buffers: shared hit=103
40. 0.033 0.033 ↑ 1.0 1 33

Seq Scan on "17f7a637b83349e9b16ef4905eb64944".projectstatuslabel (cost=0.00..1.09 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=33)

  • Output: projectstatuslabel.id, projectstatuslabel.name, projectstatuslabel.description, projectstatuslabel.projectstatustype, projectstatuslabel.enabled
  • Filter: (projectstatuslabel.projectstatustype = 1)
  • Rows Removed by Filter: 1
  • Buffers: shared hit=33
41. 0.099 0.099 ↑ 1.0 1 33

Index Scan using taskdenormalizedhierarchy_pkey on "17f7a637b83349e9b16ef4905eb64944".taskdenormalizedhierarchy tdh (cost=0.42..1.77 rows=1 width=4) (actual time=0.003..0.003 rows=1 loops=33)

  • Output: tdh.taskid, tdh.levelcount, tdh.closedcount, tdh.hierarchysorting, tdh.hierarchytaskname, tdh.level1, tdh.level2, tdh.level3, tdh.level4, tdh.level5, tdh.level6, tdh.level7, tdh.level8, tdh.level9, tdh.level10, tdh.fullpath, tdh.effectivelyenabled, tdh.effectivecloseddate
  • Index Cond: (tdh.taskid = ttma.taskid)
  • Filter: (tdh.closedcount < 1)
  • Buffers: shared hit=132
42. 0.099 0.099 ↑ 3.0 1 33

Index Scan using ixtfhchildid on "17f7a637b83349e9b16ef4905eb64944".taskflathierarchy tfh (cost=0.43..0.56 rows=3 width=8) (actual time=0.003..0.003 rows=1 loops=33)

  • Output: tfh.parentid, tfh.childid, tfh.depth
  • Index Cond: (tfh.childid = tdh.taskid)
  • Buffers: shared hit=132
43. 0.099 0.099 ↑ 1.0 1 33

Index Scan using task_pkey on "17f7a637b83349e9b16ef4905eb64944".task alltk (cost=0.42..0.45 rows=1 width=43) (actual time=0.003..0.003 rows=1 loops=33)

  • Output: alltk.id, alltk.projectid, alltk.parentid, alltk.name, alltk.code, alltk.description, alltk.isclosed, alltk.orderindex, alltk.percentcomplete, alltk.istimeentryallowed, alltk.estimatedhours, alltk.timeentrystartdate, alltk.timeentryenddate, alltk.estimatedexpenses, alltk.estimatedexpensescurrencyid, alltk.expenseentrystartdate, alltk.expenseentryenddate, alltk.costtype, alltk.estimatedcost, alltk.estimatedcostcurrencyid, alltk.timeandexpenseentrytype, alltk.info1, alltk.info2, alltk.info3, alltk.info4, alltk.info5, alltk.info6, alltk.info7, alltk.info8, alltk.info9, alltk.info10, alltk.info11, alltk.info12, alltk.info13, alltk.info14, alltk.info15, alltk.info16, alltk.info17, alltk.info18, alltk.info19, alltk.info20, alltk.closeddate
  • Index Cond: (alltk.id = tfh.parentid)
  • Buffers: shared hit=132
44. 0.066 0.066 ↑ 1.0 1 33

Index Scan using taskdenormalizedhierarchy_pkey on "17f7a637b83349e9b16ef4905eb64944".taskdenormalizedhierarchy alltdh (cost=0.42..0.45 rows=1 width=21) (actual time=0.002..0.002 rows=1 loops=33)

  • Output: alltdh.taskid, alltdh.levelcount, alltdh.closedcount, alltdh.hierarchysorting, alltdh.hierarchytaskname, alltdh.level1, alltdh.level2, alltdh.level3, alltdh.level4, alltdh.level5, alltdh.level6, alltdh.level7, alltdh.level8, alltdh.level9, alltdh.level10, alltdh.fullpath, alltdh.effectivelyenabled, alltdh.effectivecloseddate
  • Index Cond: (alltdh.taskid = tfh.parentid)
  • Buffers: shared hit=132
Planning time : 5.697 ms
Execution time : 523.251 ms