explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Y5gq

Settings
# exclusive inclusive rows x rows loops node
1. 1.175 4,765.326 ↓ 1.7 3,008 1

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

2. 1.415 4,762.037 ↓ 1.5 302 1

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

3. 3.770 4,760.018 ↓ 1.5 302 1

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

  • Hash Cond: (o.id = getorgtree.id)
4. 21.261 4,755.727 ↓ 32.4 13,402 1

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

  • Hash Cond: (o.organisationroleid = orole.id)
5. 8.199 553.027 ↓ 32.4 13,402 1

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

  • Hash Cond: (o.organisationtypeid = otype.id)
6. 9.128 544.816 ↓ 32.4 13,402 1

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

7. 36.677 508.884 ↓ 32.4 13,402 1

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

8. 13.418 432.001 ↓ 32.4 13,402 1

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

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

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

10. 6.990 392.334 ↓ 32.4 13,402 1

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

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

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

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

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

13. 6.204 347.759 ↓ 32.4 13,402 1

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

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

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

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

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

16. 3.483 9.390 ↓ 1.0 13,402 1

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

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

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

  • Filter: (NOT isdeleted)
  • Rows Removed by Filter: 18,809
18. 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)
19. 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)
20. 0.004 0.012 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
21. 0.008 0.008 ↑ 1.0 4 1

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

22. 0.003 0.015 ↑ 1.0 6 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
23. 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.012..0.012 rows=6 loops=1)

24.          

SubPlan (for Hash Join)

25. 4,181.424 4,181.424 ↑ 1,000.0 1 13,402

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

26. 0.010 0.521 ↑ 2.9 68 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
27. 0.021 0.511 ↑ 2.9 68 1

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

  • Group Key: getorgtree.id
28. 0.490 0.490 ↑ 14.7 68 1

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

29. 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
30. 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