explain.depesz.com

PostgreSQL's explain analyze made readable

Result: s0Qi

Settings
# exclusive inclusive rows x rows loops node
1. 21.215 833.187 ↑ 11.8 55,893 1

Unique (cost=143,057.81..152,945.57 rows=659,184 width=76) (actual time=742.769..833.187 rows=55,893 loops=1)

2. 660.772 811.972 ↑ 7.2 92,021 1

Sort (cost=143,057.81..144,705.77 rows=659,184 width=76) (actual time=742.769..811.972 rows=92,021 loops=1)

  • Sort Key: (unnest(string_to_array(replace(group_users.recipients, ','::text, ';'::text), ';'::text))), ((group_users.user_type + 10)), group_users.id, group_users.group_id, groups.name_of_practice
  • Sort Method: external merge Disk: 5512kB
3. 5.600 151.200 ↑ 7.2 92,021 1

Append (cost=94.96..20,763.61 rows=659,184 width=76) (actual time=0.347..151.200 rows=92,021 loops=1)

4. 3.574 6.049 ↑ 59.1 5,791 1

Hash Left Join (cost=94.96..2,770.04 rows=342,300 width=73) (actual time=0.347..6.049 rows=5,791 loops=1)

  • Hash Cond: (group_users.group_id = groups.id)
5. 2.170 2.170 ↑ 1.5 2,251 1

Seq Scan on group_users (cost=0.00..899.40 rows=3,423 width=52) (actual time=0.022..2.170 rows=2,251 loops=1)

  • Filter: ((recipients <> ''::text) AND (user_type = 3))
  • Rows Removed by Filter: 15912
6. 0.107 0.305 ↑ 1.0 576 1

Hash (cost=87.76..87.76 rows=576 width=33) (actual time=0.305..0.305 rows=576 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
7. 0.198 0.198 ↑ 1.0 576 1

Seq Scan on groups (cost=0.00..87.76 rows=576 width=33) (actual time=0.009..0.198 rows=576 loops=1)

8. 4.712 62.593 ↓ 1.1 36,589 1

Subquery Scan on *SELECT* 2 (cost=4,282.78..5,508.79 rows=33,996 width=76) (actual time=28.179..62.593 rows=36,589 loops=1)

9. 18.371 57.881 ↓ 1.1 36,589 1

Hash Left Join (cost=4,282.78..5,168.83 rows=33,996 width=69) (actual time=28.178..57.881 rows=36,589 loops=1)

  • Hash Cond: (tbl_users.public_user_id = group_users_1.id)
  • Join Filter: (tbl_users.user_type = 4)
  • Rows Removed by Join Filter: 125
10. 10.653 28.304 ↓ 1.1 36,589 1

Hash Right Join (cost=2,902.52..3,261.93 rows=33,996 width=39) (actual time=16.869..28.304 rows=36,589 loops=1)

  • Hash Cond: (party_user_report_xref.user_id = tbl_users.id)
  • Join Filter: (tbl_users.user_type = 4)
11. 1.003 1.003 ↑ 1.0 11,990 1

Seq Scan on party_user_report_xref (cost=0.00..196.90 rows=11,990 width=12) (actual time=0.020..1.003 rows=11,990 loops=1)

12. 6.195 16.648 ↑ 1.0 33,955 1

Hash (cost=2,477.57..2,477.57 rows=33,996 width=35) (actual time=16.648..16.648 rows=33,955 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2766kB
13. 10.453 10.453 ↑ 1.0 33,955 1

Seq Scan on tbl_users (cost=0.00..2,477.57 rows=33,996 width=35) (actual time=0.023..10.453 rows=33,955 loops=1)

  • Filter: (user_type = ANY ('{1,2,4}'::integer[]))
  • Rows Removed by Filter: 8620
14. 4.157 11.206 ↓ 1.0 18,163 1

Hash (cost=1,153.26..1,153.26 rows=18,160 width=37) (actual time=11.206..11.206 rows=18,163 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1484kB
15. 4.753 7.049 ↓ 1.0 18,163 1

Hash Left Join (cost=94.96..1,153.26 rows=18,160 width=37) (actual time=0.218..7.049 rows=18,163 loops=1)

  • Hash Cond: (group_users_1.group_id = groups_1.id)
16. 2.092 2.092 ↓ 1.0 18,163 1

Seq Scan on group_users group_users_1 (cost=0.00..808.60 rows=18,160 width=8) (actual time=0.006..2.092 rows=18,163 loops=1)

17. 0.093 0.204 ↑ 1.0 576 1

Hash (cost=87.76..87.76 rows=576 width=33) (actual time=0.204..0.204 rows=576 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 45kB
18. 0.111 0.111 ↑ 1.0 576 1

Seq Scan on groups groups_1 (cost=0.00..87.76 rows=576 width=33) (actual time=0.005..0.111 rows=576 loops=1)

19. 10.588 12.967 ↑ 34.0 7,077 1

Hash Join (cost=73.31..1,595.84 rows=240,300 width=72) (actual time=0.463..12.967 rows=7,077 loops=1)

  • Hash Cond: (party_details.party_information_id = pi.id)
20. 1.980 1.980 ↑ 1.0 2,398 1

Seq Scan on party_details (cost=0.00..281.99 rows=2,403 width=80) (actual time=0.024..1.980 rows=2,398 loops=1)

  • Filter: ((reconciliation_emails)::text <> ''::text)
  • Rows Removed by Filter: 241
21. 0.198 0.399 ↑ 1.0 1,747 1

Hash (cost=51.47..51.47 rows=1,747 width=32) (actual time=0.399..0.399 rows=1,747 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 129kB
22. 0.201 0.201 ↑ 1.0 1,747 1

Seq Scan on party_information pi (cost=0.00..51.47 rows=1,747 width=32) (actual time=0.009..0.201 rows=1,747 loops=1)

23. 5.963 63.871 ↑ 1.0 42,430 1

Subquery Scan on *SELECT* 4 (cost=1,380.26..5,046.51 rows=42,454 width=73) (actual time=25.861..63.871 rows=42,430 loops=1)

24. 17.405 57.908 ↑ 1.0 42,430 1

Hash Left Join (cost=1,380.26..4,515.84 rows=42,454 width=64) (actual time=25.856..57.908 rows=42,430 loops=1)

  • Hash Cond: (tbl_users_1.public_user_id = group_users_2.id)
  • Join Filter: (tbl_users_1.user_type = 4)
  • Rows Removed by Join Filter: 3653
25. 14.745 14.745 ↑ 1.0 42,430 1

Seq Scan on tbl_users tbl_users_1 (cost=0.00..2,584.04 rows=42,454 width=35) (actual time=0.014..14.745 rows=42,430 loops=1)

  • Filter: (user_type = ANY ('{1,2,3,4,7}'::integer[]))
  • Rows Removed by Filter: 145
26. 4.451 25.758 ↓ 1.0 18,163 1

Hash (cost=1,153.26..1,153.26 rows=18,160 width=37) (actual time=25.758..25.758 rows=18,163 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 1484kB
27. 18.823 21.307 ↓ 1.0 18,163 1

Hash Left Join (cost=94.96..1,153.26 rows=18,160 width=37) (actual time=0.212..21.307 rows=18,163 loops=1)

  • Hash Cond: (group_users_2.group_id = groups_2.id)
28. 2.286 2.286 ↓ 1.0 18,163 1

Seq Scan on group_users group_users_2 (cost=0.00..808.60 rows=18,160 width=8) (actual time=0.007..2.286 rows=18,163 loops=1)

29. 0.079 0.198 ↑ 1.0 576 1

Hash (cost=87.76..87.76 rows=576 width=33) (actual time=0.198..0.198 rows=576 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 46kB
30. 0.119 0.119 ↑ 1.0 576 1

Seq Scan on groups groups_2 (cost=0.00..87.76 rows=576 width=33) (actual time=0.005..0.119 rows=576 loops=1)

31. 0.010 0.120 ↑ 1.0 134 1

Subquery Scan on *SELECT* 5 (cost=6.90..16.43 rows=134 width=63) (actual time=0.074..0.120 rows=134 loops=1)

32. 0.042 0.110 ↑ 1.0 134 1

Hash Left Join (cost=6.90..15.09 rows=134 width=53) (actual time=0.073..0.110 rows=134 loops=1)

  • Hash Cond: (portal_users.portal_id = portal.id)
33. 0.029 0.029 ↑ 1.0 134 1

Seq Scan on portal_users (cost=0.00..6.34 rows=134 width=30) (actual time=0.018..0.029 rows=134 loops=1)

34. 0.016 0.039 ↑ 1.0 129 1

Hash (cost=5.29..5.29 rows=129 width=23) (actual time=0.039..0.039 rows=129 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 16kB
35. 0.023 0.023 ↑ 1.0 129 1

Seq Scan on portal (cost=0.00..5.29 rows=129 width=23) (actual time=0.007..0.023 rows=129 loops=1)

Planning time : 1.088 ms
Execution time : 849.856 ms