explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kfXR

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 368.561 3,650.512 ↓ 46.3 66,951 1

GroupAggregate (cost=32,024.97..32,407.85 rows=1,445 width=634) (actual time=3,244.683..3,650.512 rows=66,951 loops=1)

  • Group Key: res.customer_id, res.target_name, res.first_name, res.last_name, res.status, res.when_created
2.          

CTE res

3. 52.978 2,220.862 ↓ 15.9 229,795 1

Hash Left Join (cost=25,373.79..30,737.76 rows=14,448 width=102) (actual time=118.148..2,220.862 rows=229,795 loops=1)

  • Hash Cond: (r2.org_dvc_name_id = org_dvc_names.org_dvc_name_id)
4. 56.810 2,167.592 ↓ 15.9 229,795 1

Nested Loop Left Join (cost=25,322.22..30,648.15 rows=14,448 width=101) (actual time=117.845..2,167.592 rows=229,795 loops=1)

5. 12.054 471.756 ↓ 235.2 71,262 1

Gather (cost=25,321.36..29,707.44 rows=303 width=83) (actual time=117.805..471.756 rows=71,262 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 15.615 459.702 ↓ 188.5 23,754 3 / 3

Nested Loop Left Join (cost=24,321.36..28,677.14 rows=126 width=83) (actual time=260.361..459.702 rows=23,754 loops=3)

7. 14.553 372.825 ↓ 188.5 23,754 3 / 3

Nested Loop (cost=24,320.94..28,519.06 rows=126 width=62) (actual time=260.315..372.825 rows=23,754 loops=3)

8. 31.654 310.693 ↓ 174.9 23,790 3 / 3

Hash Join (cost=24,320.66..28,478.01 rows=136 width=53) (actual time=260.268..310.693 rows=23,790 loops=3)

  • Hash Cond: (pf.person_id = r.recipient_id)
9. 18.961 18.961 ↑ 1.2 47,508 3 / 3

Parallel Index Only Scan using persons_func_pk on persons_func pf (cost=0.42..3,936.75 rows=58,575 width=16) (actual time=0.036..18.961 rows=47,508 loops=3)

  • Heap Fetches: 198
10. 26.038 260.078 ↓ 4.3 67,054 3 / 3

Hash (cost=24,127.20..24,127.20 rows=15,443 width=45) (actual time=260.078..260.078 rows=67,054 loops=3)

  • Buckets: 131,072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 6,177kB
11. 10.338 234.040 ↓ 4.3 67,054 3 / 3

Nested Loop (cost=0.42..24,127.20 rows=15,443 width=45) (actual time=0.239..234.040 rows=67,054 loops=3)

12. 1.582 1.582 ↑ 1.0 72 3 / 3

Seq Scan on orgs o (cost=0.00..12.70 rows=72 width=24) (actual time=0.168..1.582 rows=72 loops=3)

  • Filter: (((name)::text !~* '.*xmatters.*'::text) AND ((name)::text !~* '^smoke-test.*'::text) AND ((name)::text !~* '.*test$'::text) AND ((hostname_prefix)::text !~ '.*-np$'::text) AND ((hostname_prefix)::text !~ '.*test$'::text) AND ((name)::text <> ALL ('{"Generic Company","Default Company","Trial Promotion Template",Template}'::text[])))
  • Rows Removed by Filter: 26
13. 222.120 222.120 ↓ 2.8 931 216 / 3

Index Scan using idx_recipients_del_org_id_cat1 on recipients r (cost=0.42..331.58 rows=334 width=29) (actual time=0.024..3.085 rows=931 loops=216)

  • Index Cond: ((org_id = o.org_id) AND ((recipient_cat)::text = 'PERSON'::text))
14. 47.579 47.579 ↑ 1.0 1 71,369 / 3

Index Scan using role_pk on role (cost=0.28..0.30 rows=1 width=25) (actual time=0.002..0.002 rows=1 loops=71,369)

  • Index Cond: (role_id = pf.func_id)
  • Filter: ((name)::text <> ALL ('{"Company Admin","Super Admin"}'::text[]))
  • Rows Removed by Filter: 0
15. 71.262 71.262 ↑ 1.0 1 71,262 / 3

Index Scan using persons_pk on persons (cost=0.42..1.25 rows=1 width=37) (actual time=0.003..0.003 rows=1 loops=71,262)

  • Index Cond: (person_id = r.recipient_id)
16. 100.185 1,639.026 ↓ 3.0 3 71,262

Nested Loop Left Join (cost=0.86..3.09 rows=1 width=47) (actual time=0.013..0.023 rows=3 loops=71,262)

17. 855.144 855.144 ↓ 3.0 3 71,262

Index Scan using recipients_oi_tn_odni_stn_di_puidx on recipients r2 (cost=0.42..2.18 rows=1 width=37) (actual time=0.009..0.012 rows=3 loops=71,262)

  • Index Cond: ((org_id = r.org_id) AND ((target_name)::text = (r.target_name)::text))
  • Filter: ((recipient_cat)::text = 'DEVICE'::text)
  • Rows Removed by Filter: 1
18. 683.697 683.697 ↓ 0.0 0 227,899

Index Scan using dvc_pk on dvc (cost=0.43..0.92 rows=1 width=26) (actual time=0.003..0.003 rows=0 loops=227,899)

  • Index Cond: (dvc_id = r2.recipient_id)
  • Filter: ((dvc_type)::text = 'EMAIL'::text)
  • Rows Removed by Filter: 1
19. 0.016 0.292 ↑ 1.0 93 1

Hash (cost=50.41..50.41 rows=93 width=17) (actual time=0.292..0.292 rows=93 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
20. 0.276 0.276 ↑ 1.0 93 1

Seq Scan on org_dvc_names (cost=0.00..50.41 rows=93 width=17) (actual time=0.011..0.276 rows=93 loops=1)

  • Filter: ((name)::text = 'Work Email'::text)
  • Rows Removed by Filter: 1,300
21. 858.009 3,281.951 ↓ 15.9 229,795 1

Sort (cost=1,287.21..1,323.33 rows=14,448 width=1,424) (actual time=3,244.633..3,281.951 rows=229,795 loops=1)

  • Sort Key: res.customer_id, res.target_name, res.first_name, res.last_name, res.status, res.when_created
  • Sort Method: external merge Disk: 23,672kB
22. 2,423.942 2,423.942 ↓ 15.9 229,795 1

CTE Scan on res (cost=0.00..288.96 rows=14,448 width=1,424) (actual time=118.152..2,423.942 rows=229,795 loops=1)

Planning time : 6.233 ms
Execution time : 3,673.964 ms