explain.depesz.com

PostgreSQL's explain analyze made readable

Result: CoHo

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 4,441.366 ↑ 12.1 2,428 1

Sort (cost=243,705.03..243,778.45 rows=29,368 width=225) (actual time=4,440.981..4,441.366 rows=2,428 loops=1)

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

Initplan (for Sort)

3. 0.000 22.582 ↓ 0.0 0 1

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

4. 0.017 22.416 ↓ 11.6 58 1

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

5. 0.013 22.237 ↓ 6.0 6 1

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

6. 22.122 22.122 ↓ 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=21.898..22.122 rows=6 loops=1)

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

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

  • Index Cond: (role_id = persons_func.func_id)
  • Heap Fetches: 6
8. 0.162 0.162 ↓ 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.007..0.027 rows=10 loops=6)

  • Index Cond: (role_id = role.role_id)
9. 0.174 0.174 ↓ 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.003..0.003 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.002 3.367 ↑ 1.0 1 1

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

  • Join Filter: (role_func_1.org_func_id = org_func_1.org_func_id)
11. 0.090 3.346 ↑ 1.0 1 1

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

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

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

13. 0.009 0.122 ↓ 7.8 39 1

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

14. 0.009 0.073 ↓ 5.0 5 1

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

15. 0.024 0.024 ↓ 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.019..0.024 rows=5 loops=1)

  • Index Cond: (person_id = 200,153)
  • Heap Fetches: 5
16. 0.040 0.040 ↑ 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.008..0.008 rows=1 loops=5)

  • Index Cond: (role_id = persons_func_1.func_id)
  • Heap Fetches: 5
17. 0.040 0.040 ↓ 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.005..0.008 rows=8 loops=5)

  • Index Cond: (role_id = role_1.role_id)
18. 2.730 2.730 ↑ 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.062..0.070 rows=11 loops=39)

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

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

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

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

  • Filter: ((name)::text = 'ability.act.SupportUser'::text)
  • Rows Removed by Filter: 377
21. 0.019 0.019 ↑ 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.019..0.019 rows=1 loops=1)

  • Index Cond: (org_func_id = org_func_perms.org_func_id)
  • Heap Fetches: 1
22. 231.852 4,425.302 ↑ 12.1 2,428 1

GroupAggregate (cost=237,525.73..241,490.41 rows=29,368 width=225) (actual time=4,133.385..4,425.302 rows=2,428 loops=1)

  • Group Key: r.recipient_id, g.descrion
23. 432.163 4,193.450 ↓ 8.2 239,359 1

Sort (cost=237,525.73..237,599.15 rows=29,368 width=150) (actual time=4,133.154..4,193.450 rows=239,359 loops=1)

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

Nested Loop (cost=1,609.76..235,346.34 rows=29,368 width=150) (actual time=67.780..3,761.287 rows=239,359 loops=1)

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

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

  • Merge Cond: (r.recipient_id = g.grp_id)
26. 50.683 50.683 ↑ 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.194..50.683 rows=11,470 loops=1)

  • Index Cond: ((org_id = 1) AND ((recipient_cat)::text = 'GROUP'::text))
27. 74.437 74.437 ↓ 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.014..74.437 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. 3,532.760 3,532.760 ↑ 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.019..0.308 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 : 424.498 ms
Execution time : 4,460.116 ms