explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GZMW

Settings
# exclusive inclusive rows x rows loops node
1. 4.033 4,864.475 ↑ 5.9 302 1

Unique (cost=9,499.83..9,870.01 rows=1,784 width=430) (actual time=4,860.284..4,864.475 rows=302 loops=1)

2. 84.965 4,860.442 ↓ 1.7 3,008 1

Sort (cost=9,499.83..9,504.29 rows=1,784 width=430) (actual time=4,860.282..4,860.442 rows=3,008 loops=1)

  • Sort Key: u.id, u.aspmembershipid, o.id, 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.modifyforeman, 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.superadmin, newtable_2.areamanager, newtable_2.gwsuser, newtable_2.moorwowapp, newtable_2.piadmin, newtable_2.financialoutput, newtable_2.allowuilogin, 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), (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.projectinvited, false)), (COALESCE(newtable_1.participantstatuschange, false)), (COALESCE(newtable_1.rwpermitsubmittedwithinarea, false)), ((SubPlan 1))
  • Sort Method: quicksort Memory: 1,640kB
3. 1.161 4,775.477 ↓ 1.7 3,008 1

Nested Loop (cost=1,218.62..9,403.49 rows=1,784 width=430) (actual time=413.357..4,775.477 rows=3,008 loops=1)

4. 1.301 4,772.202 ↓ 1.5 302 1

Nested Loop (cost=1,218.20..6,513.57 rows=207 width=430) (actual time=413.348..4,772.202 rows=302 loops=1)

5. 3.794 4,770.297 ↓ 1.5 302 1

Hash Join (cost=1,217.91..5,825.91 rows=207 width=430) (actual time=413.336..4,770.297 rows=302 loops=1)

  • Hash Cond: (o.id = getorgtree.id)
6. 23.812 4,766.091 ↓ 32.4 13,402 1

Hash Join (cost=1,200.66..5,801.13 rows=414 width=430) (actual time=412.661..4,766.091 rows=13,402 loops=1)

  • Hash Cond: (o.organisationroleid = orole.id)
7. 8.552 547.437 ↓ 32.4 13,402 1

Hash Join (cost=1,199.52..1,554.64 rows=414 width=257) (actual time=411.538..547.437 rows=13,402 loops=1)

  • Hash Cond: (o.organisationtypeid = otype.id)
8. 9.449 538.873 ↓ 32.4 13,402 1

Nested Loop (cost=1,198.43..1,551.31 rows=414 width=246) (actual time=411.523..538.873 rows=13,402 loops=1)

9. 36.784 502.620 ↓ 32.4 13,402 1

Nested Loop (cost=1,198.15..1,387.79 rows=414 width=209) (actual time=411.515..502.620 rows=13,402 loops=1)

10. 13.745 425.630 ↓ 32.4 13,402 1

Hash Right Join (cost=1,197.86..1,211.74 rows=414 width=201) (actual time=411.496..425.630 rows=13,402 loops=1)

  • Hash Cond: (newtable_1.userid = u.id)
11. 25.919 25.919 ↓ 5.2 5,182 1

Function Scan on crosstab newtable_1 (cost=0.00..10.00 rows=1,000 width=17) (actual time=25.513..25.919 rows=5,182 loops=1)

12. 6.949 385.966 ↓ 32.4 13,402 1

Hash (cost=1,192.68..1,192.68 rows=414 width=188) (actual time=385.966..385.966 rows=13,402 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,299kB
13. 6.633 379.017 ↓ 32.4 13,402 1

Hash Right Join (cost=1,178.80..1,192.68 rows=414 width=188) (actual time=372.228..379.017 rows=13,402 loops=1)

  • Hash Cond: (newtable.userid = u.id)
14. 30.492 30.492 ↓ 5.2 5,217 1

Function Scan on crosstab newtable (cost=0.00..10.00 rows=1,000 width=18) (actual time=30.321..30.492 rows=5,217 loops=1)

15. 6.179 341.892 ↓ 32.4 13,402 1

Hash (cost=1,173.63..1,173.63 rows=414 width=174) (actual time=341.892..341.892 rows=13,402 loops=1)

  • Buckets: 16,384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 2,286kB
16. 9.041 335.713 ↓ 32.4 13,402 1

Hash Join (cost=1,161.00..1,173.63 rows=414 width=174) (actual time=325.642..335.713 rows=13,402 loops=1)

  • Hash Cond: (newtable_2.userid = u.id)
17. 317.502 317.502 ↓ 32.1 32,061 1

Function Scan on crosstab newtable_2 (cost=0.00..10.00 rows=1,000 width=40) (actual time=316.456..317.502 rows=32,061 loops=1)

18. 3.622 9.170 ↓ 1.0 13,402 1

Hash (cost=994.11..994.11 rows=13,351 width=138) (actual time=9.170..9.170 rows=13,402 loops=1)

  • Buckets: 16,384 Batches: 1 Memory Usage: 2,162kB
19. 5.548 5.548 ↓ 1.0 13,402 1

Seq Scan on "user" u (cost=0.00..994.11 rows=13,351 width=138) (actual time=0.024..5.548 rows=13,402 loops=1)

  • Filter: (NOT isdeleted)
  • Rows Removed by Filter: 18,809
20. 40.206 40.206 ↑ 1.0 1 13,402

Index Scan using pk_users on users am (cost=0.29..0.43 rows=1 width=24) (actual time=0.003..0.003 rows=1 loops=13,402)

  • Index Cond: (user_id = (u.aspmembershipid)::uuid)
21. 26.804 26.804 ↑ 1.0 1 13,402

Index Scan using ix_organisation_id_emailenabled on organisation o (cost=0.28..0.39 rows=1 width=41) (actual time=0.002..0.002 rows=1 loops=13,402)

  • Index Cond: (id = u.organisationid)
22. 0.003 0.012 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 0.009 0.009 ↑ 1.0 4 1

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

24. 0.004 0.016 ↑ 1.0 6 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
25. 0.012 0.012 ↑ 1.0 6 1

Seq Scan on organisationroletype orole (cost=0.00..1.06 rows=6 width=17) (actual time=0.011..0.012 rows=6 loops=1)

26.          

SubPlan (for Hash Join)

27. 4,194.826 4,194.826 ↑ 1,000.0 1 13,402

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

28. 0.009 0.412 ↑ 2.9 68 1

Hash (cost=14.75..14.75 rows=200 width=4) (actual time=0.412..0.412 rows=68 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
29. 0.019 0.403 ↑ 2.9 68 1

HashAggregate (cost=12.75..14.75 rows=200 width=4) (actual time=0.396..0.403 rows=68 loops=1)

  • Group Key: getorgtree.id
30. 0.384 0.384 ↑ 14.7 68 1

Function Scan on getorgtree (cost=0.25..10.25 rows=1,000 width=4) (actual time=0.379..0.384 rows=68 loops=1)

31. 0.604 0.604 ↑ 1.0 1 302

Index Only Scan using pk_users on users us (cost=0.29..3.32 rows=1 width=16) (actual time=0.002..0.002 rows=1 loops=302)

  • Index Cond: (user_id = (u.aspmembershipid)::uuid)
  • Heap Fetches: 302
32. 2.114 2.114 ↓ 1.1 10 302

Index Only Scan using user_id_userrole_id_unique on userrolesresolving (cost=0.42..13.87 rows=9 width=4) (actual time=0.004..0.007 rows=10 loops=302)

  • Index Cond: (userid = u.id)
  • Heap Fetches: 3,008