explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VzP2

Settings
# exclusive inclusive rows x rows loops node
1. 0.432 1,508.366 ↓ 7.2 13,313 1

Append (cost=248.45..8,679.06 rows=1,838 width=423) (actual time=235.197..1,508.366 rows=13,313 loops=1)

2. 0.136 235.332 ↓ 3.0 21 1

Unique (cost=248.45..249.80 rows=7 width=423) (actual time=235.194..235.332 rows=21 loops=1)

3. 2.581 235.196 ↓ 13.3 93 1

Sort (cost=248.45..248.47 rows=7 width=423) (actual time=235.194..235.196 rows=93 loops=1)

  • Sort Key: u.id, u.aspmembershipid, o.name, orole.name, otype.name, u.firstname, u.lastname, u.email, u.telephoneoffice, u.telephonemobile, u."position", u.department, am.last_login, (''::text), (''::text), (''::text), (''::text), u.isaccountdonotexpire, u.selfrepavingnotification, newtable_2.read_only, newtable_2.createrw, newtable_2.submitrw, newtable_2.withdrawrw, newtable_2.deleterw, newtable_2.approver, newtable_2.updaterw, newtable_2.entermaterial, newtable_2.submitmaterial, newtable_2.rollbackmaterial, newtable_2.exporter, newtable_2.admin, newtable_2.areamanager, newtable_2.gwsuser, newtable_2.moorwowapp, newtable_2.piadmin, newtable_2.mobileapp, newtable_2.permitapprover, newtable_2.permitreviewer, newtable_2.permitadvisor, newtable_2.permitfeeadmin, newtable_2.permitfeeexport, newtable_2.permitallowsignoff, (CASE WHEN (newtable_2.mu_pj_readonly OR newtable_2.uc_pj_readonly) THEN 1 ELSE 0 END), (CASE WHEN (newtable_2.mu_projectcreator OR newtable_2.uc_projectcreator) THEN 1 ELSE 0 END), newtable_2.projectgroupmanager, (COALESCE(newtable_2.projectprepreadonly, false)), (COALESCE(newtable_2.projectprepactive, false)), (COALESCE(newtable_2.editchecklistcontent, false)), (COALESCE(newtable_2.landregistrycheck, false)), (COALESCE(newtable_2.crowscan, false)), (COALESCE(newtable.approvalrequested, false)), (COALESCE(newtable.approvalrejected, false)), (COALESCE(newtable.approved, false)), (COALESCE(newtable.withdrawn, false)), (COALESCE(newtable.inprogress, false)), (COALESCE(newtable.finalizingwork, false)), (COALESCE(newtable.cancelled, false)), (COALESCE(newtable.completed, false)), (COALESCE(newtable.awaitingmaterials, false)), (COALESCE(newtable.discussionpoint, false)), (COALESCE(newtable.readyforinvoicing, false)), (COALESCE(newtable.psmmaterialscaptured, false)), (COALESCE(newtable.ownermaterialscaptured, false)), (COALESCE(newtable.awaitingownerapproval, false)), (COALESCE(newtable_1.accepted, false)), (COALESCE(newtable_1.rejected, false)), (COALESCE(newtable_1.assigned, false)), (COALESCE(newtable_1.assessmentinprogress, false)), (COALESCE(newtable_1.discontinued, false)), (COALESCE(newtable_1.expired, false)), (COALESCE(newtable_1.permitwithdrawn, false)), (COALESCE(newtable_1.downloadlinkarchiving, false)), (COALESCE(newtable_1.projectcreated, false)), (COALESCE(newtable_1.relatedprojectchanged, false)), (COALESCE(newtable_1.rwpermitsubmittedwithinarea, false)), ((SubPlan 1))
  • Sort Method: quicksort Memory: 49kB
4. 0.032 232.615 ↓ 13.3 93 1

Nested Loop (cost=186.08..248.35 rows=7 width=423) (actual time=225.704..232.615 rows=93 loops=1)

5. 0.025 232.499 ↓ 21.0 21 1

Nested Loop Semi Join (cost=185.66..243.74 rows=1 width=423) (actual time=225.681..232.499 rows=21 loops=1)

6. 0.074 219.202 ↓ 21.0 21 1

Nested Loop (cost=185.41..230.98 rows=1 width=423) (actual time=216.118..219.202 rows=21 loops=1)

7. 0.143 218.918 ↓ 21.0 21 1

Nested Loop (cost=185.12..226.66 rows=1 width=423) (actual time=215.923..218.918 rows=21 loops=1)

  • Join Filter: (o.organisationroleid = orole.id)
  • Rows Removed by Join Filter: 84
8. 0.166 213.903 ↓ 21.0 21 1

Nested Loop (cost=185.12..215.27 rows=1 width=250) (actual time=213.375..213.903 rows=21 loops=1)

  • Join Filter: (o.organisationtypeid = otype.id)
  • Rows Removed by Join Filter: 21
9. 0.011 213.716 ↓ 21.0 21 1

Nested Loop (cost=185.12..214.18 rows=1 width=239) (actual time=213.353..213.716 rows=21 loops=1)

10. 0.076 213.642 ↓ 21.0 21 1

Nested Loop (cost=184.83..205.87 rows=1 width=202) (actual time=213.339..213.642 rows=21 loops=1)

11. 0.283 213.314 ↓ 21.0 21 1

Hash Right Join (cost=184.54..198.30 rows=1 width=194) (actual time=213.290..213.314 rows=21 loops=1)

  • Hash Cond: (newtable_1.userid = u.id)
12. 16.704 16.704 ↓ 4.0 4,020 1

Function Scan on crosstab newtable_1 (cost=0.00..10.00 rows=1,000 width=15) (actual time=16.565..16.704 rows=4,020 loops=1)

13. 0.019 196.327 ↓ 21.0 21 1

Hash (cost=184.53..184.53 rows=1 width=183) (actual time=196.327..196.327 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
14. 0.357 196.308 ↓ 21.0 21 1

Hash Right Join (cost=170.77..184.53 rows=1 width=183) (actual time=196.021..196.308 rows=21 loops=1)

  • Hash Cond: (newtable.userid = u.id)
15. 28.555 28.555 ↓ 4.4 4,398 1

Function Scan on crosstab newtable (cost=0.00..10.00 rows=1,000 width=18) (actual time=28.407..28.555 rows=4,398 loops=1)

16. 0.022 167.396 ↓ 21.0 21 1

Hash (cost=170.75..170.75 rows=1 width=169) (actual time=167.396..167.396 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
17. 1.807 167.374 ↓ 21.0 21 1

Hash Join (cost=158.13..170.75 rows=1 width=169) (actual time=164.694..167.374 rows=21 loops=1)

  • Hash Cond: (newtable_2.userid = u.id)
18. 165.231 165.231 ↓ 26.4 26,358 1

Function Scan on crosstab newtable_2 (cost=0.00..10.00 rows=1,000 width=37) (actual time=164.342..165.231 rows=26,358 loops=1)

19. 0.018 0.336 ↑ 1.3 21 1

Hash (cost=157.79..157.79 rows=27 width=136) (actual time=0.336..0.336 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.318 0.318 ↑ 1.3 21 1

Index Scan using ix_user_organisationid on "user" u (cost=0.29..157.79 rows=27 width=136) (actual time=0.033..0.318 rows=21 loops=1)

  • Index Cond: (organisationid = 1)
  • Filter: (NOT isdeleted)
  • Rows Removed by Filter: 32
21. 0.252 0.252 ↑ 1.0 1 21

Index Scan using pk_users on users am (cost=0.29..7.57 rows=1 width=24) (actual time=0.012..0.012 rows=1 loops=21)

  • Index Cond: (user_id = (u.aspmembershipid)::uuid)
22. 0.063 0.063 ↑ 1.0 1 21

Index Scan using pk_organisation on organisation o (cost=0.28..8.30 rows=1 width=41) (actual time=0.002..0.003 rows=1 loops=21)

  • Index Cond: (id = 1)
23. 0.021 0.021 ↑ 2.0 2 21

Seq Scan on organisationtype otype (cost=0.00..1.04 rows=4 width=19) (actual time=0.001..0.001 rows=2 loops=21)

24. 0.021 0.021 ↑ 1.2 5 21

Seq Scan on organisationroletype orole (cost=0.00..1.06 rows=6 width=17) (actual time=0.001..0.001 rows=5 loops=21)

25.          

SubPlan (forNested Loop)

26. 4.851 4.851 ↑ 1,000.0 1 21

Function Scan on getlinkedusersbyuserid (cost=0.25..10.25 rows=1,000 width=32) (actual time=0.231..0.231 rows=1 loops=21)

27. 0.210 0.210 ↑ 1.0 1 21

Index Only Scan using pk_users on users us (cost=0.29..4.31 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=21)

  • Index Cond: (user_id = (u.aspmembershipid)::uuid)
  • Heap Fetches: 0
28. 13.272 13.272 ↑ 5.0 1 21

Function Scan on getorgtree (cost=0.25..12.75 rows=5 width=4) (actual time=0.632..0.632 rows=1 loops=21)

  • Filter: (id = 1)
  • Rows Removed by Filter: 4885
29. 0.084 0.084 ↑ 1.8 4 21

Index Only Scan using user_id_userrole_id_unique on userrolesresolving (cost=0.42..4.54 rows=7 width=4) (actual time=0.004..0.004 rows=4 loops=21)

  • Index Cond: (userid = u.id)
  • Heap Fetches: 0
30. 271.759 1,272.602 ↓ 7.3 13,292 1

HashAggregate (cost=8,392.57..8,410.88 rows=1,831 width=423) (actual time=1,257.791..1,272.602 rows=13,292 loops=1)

  • Group Key: u_1.id, u_1.aspmembershipid, o_1.id, o_1.name, orole_1.name, otype_1.name, u_1.firstname, u_1.lastname, u_1.email, u_1.telephoneoffice, u_1.telephonemobile, u_1."position", u_1.department, am_1.last_login, (''::text), (''::text), (''::text), (''::text), u_1.isaccountdonotexpire, u_1.selfrepavingnotification, newtable_5.read_only, newtable_5.createrw, newtable_5.submitrw, newtable_5.withdrawrw, newtable_5.deleterw, newtable_5.approver, newtable_5.updaterw, newtable_5.entermaterial, newtable_5.submitmaterial, newtable_5.rollbackmaterial, newtable_5.exporter, newtable_5.admin, newtable_5.areamanager, newtable_5.gwsuser, newtable_5.moorwowapp, newtable_5.piadmin, newtable_5.mobileapp, newtable_5.permitapprover, newtable_5.permitreviewer, newtable_5.permitadvisor, newtable_5.permitfeeadmin, newtable_5.permitfeeexport, newtable_5.permitallowsignoff, (CASE WHEN (newtable_5.mu_pj_readonly OR newtable_5.uc_pj_readonly) THEN 1 ELSE 0 END), (CASE WHEN (newtable_5.mu_projectcreator OR newtable_5.uc_projectcreator) THEN 1 ELSE 0 END), newtable_5.projectgroupmanager, (COALESCE(newtable_5.projectprepreadonly, false)), (COALESCE(newtable_5.projectprepactive, false)), (COALESCE(newtable_5.editchecklistcontent, false)), (COALESCE(newtable_5.landregistrycheck, false)), (COALESCE(newtable_5.crowscan, false)), (COALESCE(newtable_3.approvalrequested, false)), (COALESCE(newtable_3.approvalrejected, false)), (COALESCE(newtable_3.approved, false)), (COALESCE(newtable_3.withdrawn, false)), (COALESCE(newtable_3.inprogress, false)), (COALESCE(newtable_3.finalizingwork, false)), (COALESCE(newtable_3.cancelled, false)), (COALESCE(newtable_3.completed, false)), (COALESCE(newtable_3.awaitingmaterials, false)), (COALESCE(newtable_3.discussionpoint, false)), (COALESCE(newtable_3.readyforinvoicing, false)), (COALESCE(newtable_3.psmmaterialscaptured, false)), (COALESCE(newtable_3.ownermaterialscaptured, false)), (COALESCE(newtable_3.awaitingownerapproval, false)), (COALESCE(newtable_4.accepted, false)), (COALESCE(newtable_4.rejected, false)), (COALESCE(newtable_4.assigned, false)), (COALESCE(newtable_4.assessmentinprogress, false)), (COALESCE(newtable_4.discontinued, false)), (COALESCE(newtable_4.expired, false)), (COALESCE(newtable_4.permitwithdrawn, false)), (COALESCE(newtable_4.downloadlinkarchiving, false)), (COALESCE(newtable_4.projectcreated, false)), (COALESCE(newtable_4.relatedprojectchanged, false)), (COALESCE(newtable_4.rwpermitsubmittedwithinarea, false)), ((SubPlan 2))
31. 26.544 1,000.843 ↓ 52.4 95,934 1

Nested Loop (cost=1,576.08..8,040.10 rows=1,831 width=423) (actual time=250.505..1,000.843 rows=95,934 loops=1)

32. 11.100 934.423 ↓ 53.0 13,292 1

Hash Join (cost=1,575.66..7,256.36 rows=251 width=423) (actual time=250.496..934.423 rows=13,292 loops=1)

  • Hash Cond: (o_1.id = getorgtree_1.id)
33. 25.416 915.501 ↓ 26.5 13,292 1

Hash Join (cost=1,558.41..7,229.98 rows=502 width=423) (actual time=242.667..915.501 rows=13,292 loops=1)

  • Hash Cond: (o_1.organisationroleid = orole_1.id)
34. 7.325 358.370 ↓ 26.5 13,292 1

Hash Join (cost=1,557.28..2,081.09 rows=502 width=250) (actual time=242.504..358.370 rows=13,292 loops=1)

  • Hash Cond: (o_1.organisationtypeid = otype_1.id)
35. 6.883 351.029 ↓ 26.5 13,292 1

Nested Loop (cost=1,556.19..2,077.28 rows=502 width=239) (actual time=242.484..351.029 rows=13,292 loops=1)

36. 37.287 317.520 ↓ 26.5 13,313 1

Nested Loop (cost=1,555.91..1,840.36 rows=502 width=202) (actual time=242.470..317.520 rows=13,313 loops=1)

37. 10.819 253.607 ↓ 26.5 13,313 1

Hash Right Join (cost=1,555.62..1,569.56 rows=502 width=194) (actual time=242.434..253.607 rows=13,313 loops=1)

  • Hash Cond: (newtable_4.userid = u_1.id)
38. 15.967 15.967 ↓ 4.0 4,020 1

Function Scan on crosstab newtable_4 (cost=0.00..10.00 rows=1,000 width=15) (actual time=15.603..15.967 rows=4,020 loops=1)

39. 8.474 226.821 ↓ 26.5 13,313 1

Hash (cost=1,549.34..1,549.34 rows=502 width=183) (actual time=226.821..226.821 rows=13,313 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2223kB
40. 7.552 218.347 ↓ 26.5 13,313 1

Hash Right Join (cost=1,535.40..1,549.34 rows=502 width=183) (actual time=210.645..218.347 rows=13,313 loops=1)

  • Hash Cond: (newtable_3.userid = u_1.id)
41. 20.406 20.406 ↓ 4.4 4,398 1

Function Scan on crosstab newtable_3 (cost=0.00..10.00 rows=1,000 width=18) (actual time=20.243..20.406 rows=4,398 loops=1)

42. 7.281 190.389 ↓ 26.5 13,313 1

Hash (cost=1,529.12..1,529.12 rows=502 width=169) (actual time=190.389..190.389 rows=13,313 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2212kB
43. 8.599 183.108 ↓ 26.5 13,313 1

Hash Join (cost=1,516.49..1,529.12 rows=502 width=169) (actual time=173.452..183.108 rows=13,313 loops=1)

  • Hash Cond: (newtable_5.userid = u_1.id)
44. 158.401 158.401 ↓ 26.4 26,358 1

Function Scan on crosstab newtable_5 (cost=0.00..10.00 rows=1,000 width=37) (actual time=157.246..158.401 rows=26,358 loops=1)

45. 5.480 16.108 ↑ 1.0 13,313 1

Hash (cost=1,350.08..1,350.08 rows=13,313 width=136) (actual time=16.108..16.108 rows=13,313 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 2107kB
46. 10.628 10.628 ↑ 1.0 13,313 1

Seq Scan on "user" u_1 (cost=0.00..1,350.08 rows=13,313 width=136) (actual time=0.499..10.628 rows=13,313 loops=1)

  • Filter: (NOT isdeleted)
  • Rows Removed by Filter: 13195
47. 26.626 26.626 ↑ 1.0 1 13,313

Index Scan using pk_users on users am_1 (cost=0.29..0.54 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=13,313)

  • Index Cond: (user_id = (u_1.aspmembershipid)::uuid)
48. 26.626 26.626 ↑ 1.0 1 13,313

Index Scan using pk_organisation on organisation o_1 (cost=0.28..0.47 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=13,313)

  • Index Cond: (id = u_1.organisationid)
  • Filter: (id <> 1)
  • Rows Removed by Filter: 0
49. 0.008 0.016 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=19) (actual time=0.016..0.016 rows=4 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.008 0.008 ↑ 1.0 4 1

Seq Scan on organisationtype otype_1 (cost=0.00..1.04 rows=4 width=19) (actual time=0.007..0.008 rows=4 loops=1)

51. 0.009 0.035 ↑ 1.0 6 1

Hash (cost=1.06..1.06 rows=6 width=17) (actual time=0.035..0.035 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 0.026 0.026 ↑ 1.0 6 1

Seq Scan on organisationroletype orole_1 (cost=0.00..1.06 rows=6 width=17) (actual time=0.024..0.026 rows=6 loops=1)

53.          

SubPlan (forHash Join)

54. 531.680 531.680 ↑ 1,000.0 1 13,292

Function Scan on getlinkedusersbyuserid getlinkedusersbyuserid_1 (cost=0.25..10.25 rows=1,000 width=32) (actual time=0.040..0.040 rows=1 loops=13,292)

55. 0.714 7.822 ↓ 31.9 6,372 1

Hash (cost=14.75..14.75 rows=200 width=4) (actual time=7.822..7.822 rows=6,372 loops=1)

  • Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 289kB
56. 2.070 7.108 ↓ 31.9 6,372 1

HashAggregate (cost=12.75..14.75 rows=200 width=4) (actual time=6.417..7.108 rows=6,372 loops=1)

  • Group Key: getorgtree_1.id
57. 5.038 5.038 ↓ 6.4 6,372 1

Function Scan on getorgtree getorgtree_1 (cost=0.25..10.25 rows=1,000 width=4) (actual time=4.449..5.038 rows=6,372 loops=1)

58. 39.876 39.876 ↑ 1.0 7 13,292

Index Only Scan using user_id_userrole_id_unique on userrolesresolving userrolesresolving_1 (cost=0.42..3.05 rows=7 width=4) (actual time=0.002..0.003 rows=7 loops=13,292)

  • Index Cond: (userid = u_1.id)
  • Heap Fetches: 0
Planning time : 17.176 ms