explain.depesz.com

PostgreSQL's explain analyze made readable

Result: GOnw : Optimization for: plan #kfXR

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 378.186 3,348.397 ↓ 46.3 66,951 1

GroupAggregate (cost=32,010.53..32,393.40 rows=1,445 width=634) (actual time=2,929.261..3,348.397 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. 54.037 1,924.209 ↓ 15.9 229,795 1

Hash Left Join (cost=25,373.78..30,723.31 rows=14,448 width=102) (actual time=114.276..1,924.209 rows=229,795 loops=1)

  • Hash Cond: (r2.org_dvc_name_id = org_dvc_names.org_dvc_name_id)
4. 86.817 1,869.902 ↓ 15.9 229,795 1

Nested Loop Left Join (cost=25,322.21..30,633.70 rows=14,448 width=101) (actual time=113.992..1,869.902 rows=229,795 loops=1)

5. 61.149 429.107 ↓ 235.2 71,262 1

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

  • Workers Planned: 2
  • Workers Launched: 2
6. 3.748 367.958 ↓ 188.5 23,754 3 / 3

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

7. 6.246 292.948 ↓ 188.5 23,754 3 / 3

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

8. 24.919 239.123 ↓ 174.9 23,790 3 / 3

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

  • Hash Cond: (pf.person_id = r.recipient_id)
9. 15.664 15.664 ↑ 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.042..15.664 rows=47,508 loops=3)

  • Heap Fetches: 251
10. 21.216 198.540 ↓ 4.3 67,054 3 / 3

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

  • Buckets: 131072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 6177kB
11. 9.011 177.324 ↓ 4.3 67,054 3 / 3

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

12. 1.345 1.345 ↑ 1.0 72 3 / 3

Seq Scan on orgs o (cost=0.00..12.70 rows=72 width=24) (actual time=0.176..1.345 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. 166.968 166.968 ↓ 2.8 931 216 / 3

Index Scan using idx_recipients_del_org_id_cat on recipients r (cost=0.42..331.58 rows=334 width=29) (actual time=0.018..2.319 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. 114.298 1,353.978 ↓ 3.0 3 71,262

Nested Loop Left Join (cost=0.85..3.05 rows=1 width=47) (actual time=0.011..0.019 rows=3 loops=71,262)

17. 783.882 783.882 ↓ 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.011 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. 455.798 455.798 ↓ 0.0 0 227,899

Index Scan using idx_dvc_type_not_deleted1 on dvc (cost=0.42..0.87 rows=1 width=26) (actual time=0.002..0.002 rows=0 loops=227,899)

  • Index Cond: ((dvc_id = r2.recipient_id) AND ((dvc_type)::text = 'EMAIL'::text))
19. 0.014 0.270 ↑ 1.0 93 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
20. 0.256 0.256 ↑ 1.0 93 1

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

  • Filter: ((name)::text = 'Work Email'::text)
  • Rows Removed by Filter: 1300
21. 867.842 2,970.211 ↓ 15.9 229,795 1

Sort (cost=1,287.21..1,323.33 rows=14,448 width=1,424) (actual time=2,929.211..2,970.211 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: 23672kB
22. 2,102.369 2,102.369 ↓ 15.9 229,795 1

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

Planning time : 16.675 ms
Execution time : 3,370.656 ms