explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uE8I

Settings
# exclusive inclusive rows x rows loops node
1. 8.929 953.611 ↑ 12.1 2,428 1

Sort (cost=243,705.76..243,779.18 rows=29,368 width=225) (actual time=953.323..953.611 rows=2,428 loops=1)

  • Sort Key: r.target_name NULLS FIRST
  • Sort Method: quicksort Memory: 836kB
2.          

Initplan (for Sort)

3. 0.005 0.296 ↓ 0.0 0 1

Nested Loop (cost=0.85..8.07 rows=1 width=0) (actual time=0.296..0.296 rows=0 loops=1)

4. 0.010 0.175 ↓ 11.6 58 1

Nested Loop (cost=0.71..7.13 rows=5 width=8) (actual time=0.069..0.175 rows=58 loops=1)

5. 0.005 0.093 ↓ 6.0 6 1

Nested Loop (cost=0.43..6.52 rows=1 width=16) (actual time=0.051..0.093 rows=6 loops=1)

6. 0.046 0.046 ↓ 6.0 6 1

Index Only Scan using persons_func_pk on persons_func (cost=0.29..3.31 rows=1 width=8) (actual time=0.035..0.046 rows=6 loops=1)

  • Index Cond: (person_id = 200,153)
  • Heap Fetches: 6
7. 0.042 0.042 ↑ 1.0 1 6

Index Only Scan using role_pk on role (cost=0.14..3.16 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=6)

  • Index Cond: (role_id = persons_func.func_id)
  • Heap Fetches: 6
8. 0.072 0.072 ↓ 1.7 10 6

Index Scan using idx_role_func1 on role_func (cost=0.28..0.55 rows=6 width=16) (actual time=0.006..0.012 rows=10 loops=6)

  • Index Cond: (role_id = role.role_id)
9. 0.116 0.116 ↓ 0.0 0 58

Index Scan using org_func_pk on org_func (cost=0.14..0.18 rows=1 width=8) (actual time=0.002..0.002 rows=0 loops=58)

  • Index Cond: (org_func_id = role_func.org_func_id)
  • Filter: ((name)::text = 'COMPANY'::text)
  • Rows Removed by Filter: 1
10. 0.001 0.759 ↑ 1.0 1 1

Nested Loop (cost=13.87..27.16 rows=1 width=0) (actual time=0.759..0.759 rows=1 loops=1)

  • Join Filter: (role_func_1.org_func_id = org_func_1.org_func_id)
11. 0.062 0.747 ↑ 1.0 1 1

Hash Join (cost=13.72..26.98 rows=1 width=16) (actual time=0.746..0.747 rows=1 loops=1)

  • Hash Cond: (org_func_perms.perm_id = perms.perm_id)
12. 0.060 0.552 ↓ 3.6 433 1

Nested Loop (cost=0.99..13.93 rows=120 width=24) (actual time=0.058..0.552 rows=433 loops=1)

13. 0.006 0.063 ↓ 7.8 39 1

Nested Loop (cost=0.71..7.13 rows=5 width=8) (actual time=0.033..0.063 rows=39 loops=1)

14. 0.004 0.032 ↓ 5.0 5 1

Nested Loop (cost=0.43..6.52 rows=1 width=16) (actual time=0.024..0.032 rows=5 loops=1)

15. 0.013 0.013 ↓ 5.0 5 1

Index Only Scan using persons_func_pk on persons_func persons_func_1 (cost=0.29..3.31 rows=1 width=8) (actual time=0.010..0.013 rows=5 loops=1)

  • Index Cond: (person_id = 200,153)
  • Heap Fetches: 5
16. 0.015 0.015 ↑ 1.0 1 5

Index Only Scan using role_pk on role role_1 (cost=0.14..3.16 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=5)

  • Index Cond: (role_id = persons_func_1.func_id)
  • Heap Fetches: 5
17. 0.025 0.025 ↓ 1.3 8 5

Index Scan using idx_role_func1 on role_func role_func_1 (cost=0.28..0.55 rows=6 width=16) (actual time=0.003..0.005 rows=8 loops=5)

  • Index Cond: (role_id = role_1.role_id)
18. 0.429 0.429 ↑ 3.0 11 39

Index Scan using idx_org_func_perms2 on org_func_perms (cost=0.28..1.03 rows=33 width=16) (actual time=0.005..0.011 rows=11 loops=39)

  • Index Cond: (org_func_id = role_func_1.org_func_id)
19. 0.008 0.133 ↑ 1.0 1 1

Hash (cost=12.73..12.73 rows=1 width=8) (actual time=0.132..0.133 rows=1 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
20. 0.125 0.125 ↑ 1.0 1 1

Seq Scan on perms (cost=0.00..12.73 rows=1 width=8) (actual time=0.037..0.125 rows=1 loops=1)

  • Filter: ((name)::text = 'ability.act.SupportUser'::text)
  • Rows Removed by Filter: 377
21. 0.011 0.011 ↑ 1.0 1 1

Index Only Scan using org_func_pk on org_func org_func_1 (cost=0.14..0.17 rows=1 width=8) (actual time=0.011..0.011 rows=1 loops=1)

  • Index Cond: (org_func_id = org_func_perms.org_func_id)
  • Heap Fetches: 1
22. 149.906 943.627 ↑ 12.1 2,428 1

GroupAggregate (cost=237,526.46..241,491.14 rows=29,368 width=225) (actual time=757.584..943.627 rows=2,428 loops=1)

  • Group Key: r.recipient_id, g.descrion
23. 236.565 793.721 ↓ 8.2 239,359 1

Sort (cost=237,526.46..237,599.88 rows=29,368 width=150) (actual time=757.472..793.721 rows=239,359 loops=1)

  • Sort Key: r.recipient_id, g.descrion
  • Sort Method: external merge Disk: 37,448kB
24. 46.622 557.156 ↓ 8.2 239,359 1

Nested Loop (cost=1,609.76..235,347.06 rows=29,368 width=150) (actual time=1.250..557.156 rows=239,359 loops=1)

  • Join Filter: (g.grp_id = rrs.recip_id)
25. 6.393 40.264 ↓ 7.7 11,470 1

Merge Join (cost=1,609.19..19,784.50 rows=1,487 width=113) (actual time=0.096..40.264 rows=11,470 loops=1)

  • Merge Cond: (r.recipient_id = g.grp_id)
26. 22.952 22.952 ↑ 1.3 11,470 1

Index Scan using recipients_org_id_recipient_cat_recipient_id_pidx on recipients r (cost=0.42..3,144.13 rows=15,239 width=59) (actual time=0.080..22.952 rows=11,470 loops=1)

  • Index Cond: ((org_id = 1) AND ((recipient_cat)::text = 'GROUP'::text))
27. 10.919 10.919 ↓ 1.0 11,470 1

Index Scan using grp_pk on grp g (cost=28.62..18,352.70 rows=11,458 width=54) (actual time=0.011..10.919 rows=11,470 loops=1)

  • Filter: ((deleted_id = '-1'::integer) AND ((all_roles_observe = 'Y'::bpchar) OR $3 OR $8 OR (hashed SubPlan 3) OR (alternatives: SubPlan 4 or hashed SubPlan 5)))
  • Rows Removed by Filter: 99
28.          

SubPlan (for Index Scan)

29. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on grp_sups (cost=1.93..28.29 rows=19 width=8) (never executed)

  • Recheck Cond: (person_id = 200,153)
30. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_grp_sups2 (cost=0.00..1.93 rows=19 width=0) (never executed)

  • Index Cond: (person_id = 200,153)
31. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.71..5.58 rows=6 width=0) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Index Only Scan using persons_func_pk on persons_func persons_func_2 (cost=0.29..3.31 rows=1 width=8) (never executed)

  • Index Cond: (person_id = 200,153)
  • Heap Fetches: 0
33. 0.000 0.000 ↓ 0.0 0

Index Only Scan using grp_observers_pk on grp_observers (cost=0.42..10.84 rows=6 width=8) (never executed)

  • Index Cond: ((grp_id = g.grp_id) AND (role_id = persons_func_2.func_id))
  • Heap Fetches: 0
34. 0.000 0.000 ↓ 0.0 0

Nested Loop Left Join (cost=68.36..1,937.09 rows=5,632 width=8) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Index Only Scan using persons_func_pk on persons_func persons_func_3 (cost=0.29..3.31 rows=1 width=8) (never executed)

  • Index Cond: (person_id = 200,153)
  • Heap Fetches: 0
36. 0.000 0.000 ↓ 0.0 0

Bitmap Heap Scan on grp_observers grp_observers_1 (cost=68.07..1,877.47 rows=5,632 width=16) (never executed)

  • Recheck Cond: (role_id = persons_func_3.func_id)
37. 0.000 0.000 ↓ 0.0 0

Bitmap Index Scan on idx_grp_observers2 (cost=0.00..66.66 rows=5,632 width=0) (never executed)

  • Index Cond: (role_id = persons_func_3.func_id)
38. 470.270 470.270 ↑ 17.6 21 11,470

Index Scan using idx_rrs_recip_id_date1 on recip_resp_stats rrs (cost=0.56..140.34 rows=370 width=53) (actual time=0.005..0.041 rows=21 loops=11,470)

  • Index Cond: ((recip_id = r.recipient_id) AND (evs_creation >= '2020-07-09 04:10:40+00'::timestamp with time zone) AND (evs_creation <= '2020-07-16 04:10:40+00'::timestamp with time zone))
  • Filter: (((recipient_cat)::text = 'GROUP'::text) AND (is_latest = 'Y'::bpchar))
Planning time : 12.158 ms
Execution time : 964.642 ms