explain.depesz.com

PostgreSQL's explain analyze made readable

Result: d9X5

Settings
# exclusive inclusive rows x rows loops node
1. 0.399 5,870.253 ↓ 7.2 13,313 1

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

2. 0.122 206.854 ↓ 3.0 21 1

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

3. 2.544 206.732 ↓ 13.3 93 1

Sort (cost=248.45..248.47 rows=7 width=423) (actual time=206.730..206.732 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.046 204.188 ↓ 13.3 93 1

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

5. 0.030 203.995 ↓ 21.0 21 1

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

6. 0.071 193.213 ↓ 21.0 21 1

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

7. 0.234 193.100 ↓ 21.0 21 1

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

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

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

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

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

10. 0.080 190.606 ↓ 21.0 21 1

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

11. 0.282 190.421 ↓ 21.0 21 1

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

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

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

13. 0.014 175.009 ↓ 21.0 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
14. 0.271 174.995 ↓ 21.0 21 1

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

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

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

16. 0.023 154.391 ↓ 21.0 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
17. 1.597 154.368 ↓ 21.0 21 1

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

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

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

19. 0.014 0.081 ↑ 1.3 21 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.067 0.067 ↑ 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.020..0.067 rows=21 loops=1)

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

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

  • Index Cond: (user_id = (u.aspmembershipid)::uuid)
22. 0.042 0.042 ↑ 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.002 rows=1 loops=21)

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

Seq Scan on organisationtype otype (cost=0.00..1.04 rows=4 width=19) (actual time=0.003..0.003 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. 1.848 1.848 ↑ 1,000.0 1 21

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

27. 0.042 0.042 ↑ 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.002..0.002 rows=1 loops=21)

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

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

  • Filter: (id = 1)
  • Rows Removed by Filter: 4885
29. 0.147 0.147 ↑ 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.006..0.007 rows=4 loops=21)

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

HashAggregate (cost=8,392.57..8,410.88 rows=1,831 width=423) (actual time=5,647.977..5,663.000 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. 23.634 5,381.861 ↓ 52.4 95,934 1

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

32. 12.764 5,305.059 ↓ 53.0 13,292 1

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

  • Hash Cond: (o_1.id = getorgtree_1.id)
33. 30.907 5,284.674 ↓ 26.5 13,292 1

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

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

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

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

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

36. 33.456 312.868 ↓ 26.5 13,313 1

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

37. 12.521 239.473 ↓ 26.5 13,313 1

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

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

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

39. 8.550 211.513 ↓ 26.5 13,313 1

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

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

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

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

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

42. 6.952 174.720 ↓ 26.5 13,313 1

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

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

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

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

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

45. 4.675 10.713 ↑ 1.0 13,313 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 2107kB
46. 6.038 6.038 ↑ 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.100..6.038 rows=13,313 loops=1)

  • Filter: (NOT isdeleted)
  • Rows Removed by Filter: 13195
47. 39.939 39.939 ↑ 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.003..0.003 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.017 ↑ 1.0 4 1

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

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

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

51. 0.007 0.027 ↑ 1.0 6 1

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

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

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

53.          

SubPlan (forHash Join)

54. 4,891.456 4,891.456 ↑ 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.368..0.368 rows=1 loops=13,292)

55. 0.646 7.621 ↓ 31.9 6,372 1

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

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

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

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

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

58. 53.168 53.168 ↑ 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.003..0.004 rows=7 loops=13,292)

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