explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Vz61 : wcg_before

Settings
# exclusive inclusive rows x rows loops node
1. 0.300 23,521.419 ↓ 4.6 1,000 1

Limit (cost=27,997.42..386,178.78 rows=217 width=2,212) (actual time=16,693.463..23,521.419 rows=1,000 loops=1)

  • Output: ((SubPlan 3)), project7.id, ((project7.name)::character varying(255)), ((project7.code)::character varying(50)), ((project7.slug)::character varying(255)), project7.istimeentryallowed, project7.startdate, project7.enddate, project7.programid, ((program8.name)::character varying(255)), ((program8.slug)::character varying(255))
  • Buffers: shared hit=662268 read=25162
2. 1.850 23,521.119 ↓ 4.6 1,000 1

Result (cost=27,997.42..386,178.78 rows=217 width=2,212) (actual time=16,693.462..23,521.119 rows=1,000 loops=1)

  • Output: (SubPlan 3), project7.id, ((project7.name)::character varying(255)), ((project7.code)::character varying(50)), ((project7.slug)::character varying(255)), project7.istimeentryallowed, project7.startdate, project7.enddate, project7.programid, ((program8.name)::character varying(255)), ((program8.slug)::character varying(255))
  • Buffers: shared hit=662268 read=25162
3. 3.543 16,682.269 ↓ 4.6 1,000 1

Sort (cost=27,997.42..27,997.96 rows=217 width=2,211) (actual time=16,681.890..16,682.269 rows=1,000 loops=1)

  • Output: project7.id, ((project7.name)::character varying(255)), ((project7.code)::character varying(50)), ((project7.slug)::character varying(255)), project7.istimeentryallowed, project7.startdate, project7.enddate, project7.programid, ((program8.name)::character varying(255)), ((program8.slug)::character varying(255))
  • Sort Key: ((project7.name)::character varying(255)) COLLATE "en_US
  • Sort Method: quicksort Memory: 199kB
  • Buffers: shared hit=350770 read=18045
4. 3.834 16,678.726 ↓ 5.5 1,202 1

Hash Semi Join (cost=23,455.54..27,989.00 rows=217 width=2,211) (actual time=99.979..16,678.726 rows=1,202 loops=1)

  • Output: project7.id, project7.name, project7.code, project7.slug, project7.istimeentryallowed, project7.startdate, project7.enddate, project7.programid, program8.name, program8.slug
  • Hash Cond: (project7.id = projectteammember10.projectid)
  • Join Filter: (project7.istimeentryallowed OR (SubPlan 5))
  • Rows Removed by Join Filter: 17
  • Buffers: shared hit=350763 read=18045
5. 0.542 2.979 ↓ 5.5 2,030 1

Hash Left Join (cost=12.67..163.53 rows=371 width=1,104) (actual time=0.044..2.979 rows=2,030 loops=1)

  • Output: project7.id, project7.name, project7.code, project7.slug, project7.istimeentryallowed, project7.startdate, project7.enddate, project7.programid, program8.name, program8.slug
  • Inner Unique: true
  • Hash Cond: (project7.programid = program8.id)
  • Buffers: shared hit=117
6. 1.055 2.436 ↓ 5.5 2,030 1

Hash Join (cost=1.10..150.98 rows=371 width=72) (actual time=0.034..2.436 rows=2,030 loops=1)

  • Output: project7.id, project7.name, project7.code, project7.slug, project7.istimeentryallowed, project7.startdate, project7.enddate, project7.programid
  • Inner Unique: true
  • Hash Cond: (project7.projectstatuslabelid = projectstatuslabel9.id)
  • Buffers: shared hit=117
7. 1.374 1.374 ↓ 1.0 2,598 1

Seq Scan on "17f7a637b83349e9b16ef4905eb64944".project project7 (cost=0.00..138.94 rows=2,594 width=88) (actual time=0.004..1.374 rows=2,598 loops=1)

  • Output: project7.id, project7.name, project7.code, project7.slug, project7.istimeentryallowed, project7.startdate, project7.enddate, project7.programid, project7.projectstatuslabelid
  • Buffers: shared hit=113
8. 0.004 0.007 ↑ 1.0 1 1

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

  • Output: projectstatuslabel9.id
  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
  • Buffers: shared hit=1
9. 0.003 0.003 ↑ 1.0 1 1

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

  • Output: projectstatuslabel9.id
  • Filter: (projectstatuslabel9.projectstatustype = 1)
  • Rows Removed by Filter: 6
  • Buffers: shared hit=1
10. 0.000 0.001 ↓ 0.0 0 1

Hash (cost=10.70..10.70 rows=70 width=1,048) (actual time=0.001..0.001 rows=0 loops=1)

  • Output: program8.name, program8.slug, program8.id
  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
11. 0.001 0.001 ↓ 0.0 0 1

Seq Scan on "17f7a637b83349e9b16ef4905eb64944".program program8 (cost=0.00..10.70 rows=70 width=1,048) (actual time=0.000..0.001 rows=0 loops=1)

  • Output: program8.name, program8.slug, program8.id
12. 0.480 98.203 ↑ 230.2 1,780 1

Hash (cost=18,321.65..18,321.65 rows=409,698 width=20) (actual time=98.203..98.203 rows=1,780 loops=1)

  • Output: projectteammember10.projectid, projectteammember10.id
  • Buckets: 524288 Batches: 1 Memory Usage: 4187kB
  • Buffers: shared hit=2515 read=2500
13. 97.705 97.723 ↑ 230.2 1,780 1

Seq Scan on "17f7a637b83349e9b16ef4905eb64944".projectteammember projectteammember10 (cost=4.50..18,321.65 rows=409,698 width=20) (actual time=0.075..97.723 rows=1,780 loops=1)

  • Output: projectteammember10.projectid, projectteammember10.id
  • Filter: ((projectteammember10.userid = 11190) OR (hashed SubPlan 4))
  • Rows Removed by Filter: 790042
  • Buffers: shared hit=2515 read=2500
14.          

SubPlan (for Seq Scan)

15. 0.003 0.018 ↑ 1.0 2 1

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

  • Output: departmentflathierarchy11.parentid
  • Buffers: shared hit=5
16. 0.009 0.009 ↑ 1.0 1 1

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

  • Output: departmentusers12.departmentid, departmentusers12.userid, departmentusers12.isprimarydepartment
  • Index Cond: (departmentusers12.userid = 11190)
  • Buffers: shared hit=3
17. 0.006 0.006 ↑ 1.0 2 1

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

  • Output: departmentflathierarchy11.parentid, departmentflathierarchy11.childid, departmentflathierarchy11.depth
  • Index Cond: (departmentflathierarchy11.childid = departmentusers12.departmentid)
  • Buffers: shared hit=2
18.          

SubPlan (for Hash Semi Join)

19. 1.842 16,573.710 ↑ 1.0 1 678

Nested Loop (cost=1.27..20.16 rows=1 width=0) (actual time=24.445..24.445 rows=1 loops=678)

  • Inner Unique: true
  • Buffers: shared hit=348131 read=15545
20. 74.356 16,027.242 ↑ 1.5 2 678

Nested Loop (cost=0.85..18.71 rows=3 width=8) (actual time=21.362..23.639 rows=2 loops=678)

  • Output: taskdenormalizedhierarchy14.taskid, taskteammemberassignment15.taskid
  • Inner Unique: true
  • Buffers: shared hit=342931 read=15053
21. 3,948.672 3,948.672 ↓ 25.8 129 678

Index Scan using ixttmaprojectteammemberid on "17f7a637b83349e9b16ef4905eb64944".taskteammemberassignment taskteammemberassignment15 (cost=0.43..6.51 rows=5 width=4) (actual time=2.176..5.824 rows=129 loops=678)

  • Output: taskteammemberassignment15.id, taskteammemberassignment15.taskid, taskteammemberassignment15.projectteammemberid, taskteammemberassignment15.taskallocationstartdate, taskteammemberassignment15.taskallocationenddate, taskteammemberassignment15.taskallocationduration
  • Index Cond: (taskteammemberassignment15.projectteammemberid = projectteammember10.id)
  • Buffers: shared hit=2256 read=4437
22. 12,004.214 12,004.214 ↓ 0.0 0 87,622

Index Scan using taskdenormalizedhierarchy_pkey on "17f7a637b83349e9b16ef4905eb64944".taskdenormalizedhierarchy taskdenormalizedhierarchy14 (cost=0.42..2.44 rows=1 width=4) (actual time=0.137..0.137 rows=0 loops=87,622)

  • Output: taskdenormalizedhierarchy14.taskid, taskdenormalizedhierarchy14.levelcount, taskdenormalizedhierarchy14.closedcount, taskdenormalizedhierarchy14.hierarchysorting, taskdenormalizedhierarchy14.hierarchytaskname, taskdenormalizedhierarchy14.level1, taskdenormalizedhierarchy14.level2, taskdenormalizedhierarchy14.level3, taskdenormalizedhierarchy14.level4, taskdenormalizedhierarchy14.level5, taskdenormalizedhierarchy14.level6, taskdenormalizedhierarchy14.level7, taskdenormalizedhierarchy14.level8, taskdenormalizedhierarchy14.level9, taskdenormalizedhierarchy14.level10, taskdenormalizedhierarchy14.fullpath, taskdenormalizedhierarchy14.effectivelyenabled, taskdenormalizedhierarchy14.effectivecloseddate
  • Index Cond: (taskdenormalizedhierarchy14.taskid = taskteammemberassignment15.taskid)
  • Filter: taskdenormalizedhierarchy14.effectivelyenabled
  • Rows Removed by Filter: 1
  • Buffers: shared hit=340675 read=10616
23. 544.626 544.626 ↓ 0.0 0 1,422

Index Scan using task_pkey on "17f7a637b83349e9b16ef4905eb64944".task task13 (cost=0.42..0.48 rows=1 width=4) (actual time=0.383..0.383 rows=0 loops=1,422)

  • Output: task13.id, task13.projectid, task13.parentid, task13.name, task13.code, task13.description, task13.isclosed, task13.orderindex, task13.percentcomplete, task13.istimeentryallowed, task13.estimatedhours, task13.timeentrystartdate, task13.timeentryenddate, task13.estimatedexpenses, task13.estimatedexpensescurrencyid, task13.expenseentrystartdate, task13.expenseentryenddate, task13.costtype, task13.estimatedcost, task13.estimatedcostcurrencyid, task13.timeandexpenseentrytype, task13.info1, task13.info2, task13.info3, task13.info4, task13.info5, task13.info6, task13.info7, task13.info8, task13.info9, task13.info10, task13.info11, task13.info12, task13.info13, task13.info14, task13.info15, task13.info16, task13.info17, task13.info18, task13.info19, task13.info20, task13.closeddate
  • Index Cond: (task13.id = taskdenormalizedhierarchy14.taskid)
  • Filter: (task13.istimeentryallowed AND (task13.timeentrystartdate <= '2016-06-11'::date) AND (task13.timeentryenddate >= '2016-06-05'::date) AND (task13.projectid = project7.id))
  • Rows Removed by Filter: 1
  • Buffers: shared hit=5200 read=492
24.          

SubPlan (for Result)

25. 1.005 6,837.000 ↑ 1.0 1 1,000

Result (cost=1,649.58..1,650.59 rows=1 width=1) (actual time=6.837..6.837 rows=1 loops=1,000)

  • Output: true
  • One-Time Filter: $5
  • Buffers: shared hit=311498 read=7117
26.          

Initplan (for Result)

27. 7.816 6,835.000 ↑ 1.0 1 1,000

Nested Loop (cost=1,610.77..1,649.58 rows=1 width=0) (actual time=6.835..6.835 rows=1 loops=1,000)

  • Inner Unique: true
  • Join Filter: (task1.id = taskdenormalizedhierarchy2.taskid)
  • Buffers: shared hit=310503 read=7117
28. 19.355 5,991.000 ↓ 70.0 70 1,000

Hash Semi Join (cost=1,610.34..1,648.94 rows=1 width=8) (actual time=5.838..5.991 rows=70 loops=1,000)

  • Output: task1.id, taskteammemberassignment4.taskid
  • Hash Cond: (task1.id = taskteammemberassignment4.taskid)
  • Buffers: shared hit=31728 read=6519
29. 354.645 354.645 ↑ 2.4 73 999

Index Scan using ixtkprojectid on "17f7a637b83349e9b16ef4905eb64944".task task1 (cost=0.42..38.55 rows=178 width=4) (actual time=0.216..0.355 rows=73 loops=999)

  • Output: task1.id, task1.projectid, task1.parentid, task1.name, task1.code, task1.description, task1.isclosed, task1.orderindex, task1.percentcomplete, task1.istimeentryallowed, task1.estimatedhours, task1.timeentrystartdate, task1.timeentryenddate, task1.estimatedexpenses, task1.estimatedexpensescurrencyid, task1.expenseentrystartdate, task1.expenseentryenddate, task1.costtype, task1.estimatedcost, task1.estimatedcostcurrencyid, task1.timeandexpenseentrytype, task1.info1, task1.info2, task1.info3, task1.info4, task1.info5, task1.info6, task1.info7, task1.info8, task1.info9, task1.info10, task1.info11, task1.info12, task1.info13, task1.info14, task1.info15, task1.info16, task1.info17, task1.info18, task1.info19, task1.info20, task1.closeddate
  • Index Cond: (task1.projectid = project7.id)
  • Filter: (task1.istimeentryallowed AND (task1.timeentrystartdate <= '2016-06-11'::date) AND (task1.timeentryenddate >= '2016-06-05'::date))
  • Rows Removed by Filter: 4
  • Buffers: shared hit=5819 read=304
30. 31.000 5,617.000 ↑ 1.8 251 1,000

Hash (cost=1,604.28..1,604.28 rows=451 width=4) (actual time=5.617..5.617 rows=251 loops=1,000)

  • Output: taskteammemberassignment4.taskid
  • Buckets: 1024 Batches: 1 Memory Usage: 21kB
  • Buffers: shared hit=25909 read=6215
31. 35.000 5,586.000 ↑ 1.8 251 1,000

Nested Loop (cost=5.35..1,604.28 rows=451 width=4) (actual time=1.603..5.586 rows=251 loops=1,000)

  • Output: taskteammemberassignment4.taskid
  • Buffers: shared hit=25909 read=6215
32. 1,457.975 1,458.000 ↑ 219.0 1 1,000

Index Scan using uix3ptm_projectrole on "17f7a637b83349e9b16ef4905eb64944".projectteammember projectteammember3 (cost=4.92..206.67 rows=219 width=16) (actual time=1.050..1.458 rows=1 loops=1,000)

  • Output: projectteammember3.id, projectteammember3.projectid, projectteammember3.userid, projectteammember3.departmentid, projectteammember3.placeholderprojectroleid, projectteammember3.index, projectteammember3.placeholderdisplayname, projectteammember3.locationid, projectteammember3.divisionid, projectteammember3.costcenterid, projectteammember3.servicecenterid, projectteammember3.departmentgroupid, projectteammember3.employeetypegroupid
  • Index Cond: (projectteammember3.projectid = project7.id)
  • Filter: ((projectteammember3.userid = 11190) OR (hashed SubPlan 1))
  • Rows Removed by Filter: 481
  • Buffers: shared hit=7730 read=1213
33.          

SubPlan (for Index Scan)

34. 0.002 0.025 ↑ 1.0 2 1

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

  • Output: departmentflathierarchy5.parentid
  • Buffers: shared hit=5
35. 0.012 0.012 ↑ 1.0 1 1

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

  • Output: departmentusers6.departmentid, departmentusers6.userid, departmentusers6.isprimarydepartment
  • Index Cond: (departmentusers6.userid = 11190)
  • Buffers: shared hit=3
36. 0.011 0.011 ↑ 1.0 2 1

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

  • Output: departmentflathierarchy5.parentid, departmentflathierarchy5.childid, departmentflathierarchy5.depth
  • Index Cond: (departmentflathierarchy5.childid = departmentusers6.departmentid)
  • Buffers: shared hit=2
37. 4,093.000 4,093.000 ↓ 50.2 251 1,000

Index Scan using ixttmaprojectteammemberid on "17f7a637b83349e9b16ef4905eb64944".taskteammemberassignment taskteammemberassignment4 (cost=0.43..6.33 rows=5 width=20) (actual time=0.551..4.093 rows=251 loops=1,000)

  • Output: taskteammemberassignment4.id, taskteammemberassignment4.taskid, taskteammemberassignment4.projectteammemberid, taskteammemberassignment4.taskallocationstartdate, taskteammemberassignment4.taskallocationenddate, taskteammemberassignment4.taskallocationduration
  • Index Cond: (taskteammemberassignment4.projectteammemberid = projectteammember3.id)
  • Buffers: shared hit=18179 read=5002
38. 836.184 836.184 ↓ 0.0 0 69,682

Index Scan using taskdenormalizedhierarchy_pkey on "17f7a637b83349e9b16ef4905eb64944".taskdenormalizedhierarchy taskdenormalizedhierarchy2 (cost=0.42..0.63 rows=1 width=4) (actual time=0.012..0.012 rows=0 loops=69,682)

  • Output: taskdenormalizedhierarchy2.taskid, taskdenormalizedhierarchy2.levelcount, taskdenormalizedhierarchy2.closedcount, taskdenormalizedhierarchy2.hierarchysorting, taskdenormalizedhierarchy2.hierarchytaskname, taskdenormalizedhierarchy2.level1, taskdenormalizedhierarchy2.level2, taskdenormalizedhierarchy2.level3, taskdenormalizedhierarchy2.level4, taskdenormalizedhierarchy2.level5, taskdenormalizedhierarchy2.level6, taskdenormalizedhierarchy2.level7, taskdenormalizedhierarchy2.level8, taskdenormalizedhierarchy2.level9, taskdenormalizedhierarchy2.level10, taskdenormalizedhierarchy2.fullpath, taskdenormalizedhierarchy2.effectivelyenabled, taskdenormalizedhierarchy2.effectivecloseddate
  • Index Cond: (taskdenormalizedhierarchy2.taskid = taskteammemberassignment4.taskid)
  • Filter: taskdenormalizedhierarchy2.effectivelyenabled
  • Rows Removed by Filter: 1
  • Buffers: shared hit=278775 read=598
39. 0.995 0.995 ↑ 1.0 1 995

Seq Scan on "17f7a637b83349e9b16ef4905eb64944".systeminformation systeminformation0 (cost=1,649.58..1,650.59 rows=1 width=1) (actual time=0.001..0.001 rows=1 loops=995)

  • Output: systeminformation0.rootuserid, systeminformation0.basecurrencyid, systeminformation0.basecurrencysymbol, systeminformation0.defaulttimesheetapprovalpathid, systeminformation0.defaultexpenseapprovalpathid, systeminformation0.defaulttimeoffapprovalpathid, systeminformation0.defaultlanguageid, systeminformation0.defaultholidaycalendarid, systeminformation0.buildnumber, systeminformation0.isreleaseversion, systeminformation0.systemguid, systeminformation0.systemtimezoneid, systeminformation0.newuserdefaulttimezoneid, systeminformation0.databaseguid, systeminformation0.uncategorizedskillcategoryid, systeminformation0.defaultofficescheduleid, systeminformation0.customlogouri, systeminformation0.databaseupdatescriptstage, systeminformation0.tenantslug, systeminformation0.defaulttimesheetperiodid
  • Buffers: shared hit=995
Planning time : 48.244 ms
Execution time : 23,522.196 ms