explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jJkw : Optimization for: Optimization for: plan #ZKWb; plan #kt7C

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 367.786 3,464.959 ↓ 46.4 66,951 1

GroupAggregate (cost=32,024.70..32,407.49 rows=1,444 width=634) (actual time=3,057.158..3,464.959 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. 53.513 2,055.002 ↓ 15.9 229,795 1

Hash Left Join (cost=25,373.76..30,737.78 rows=14,445 width=102) (actual time=105.564..2,055.002 rows=229,795 loops=1)

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

Nested Loop Left Join (cost=25,322.18..30,648.17 rows=14,445 width=101) (actual time=105.287..2,001.224 rows=229,795 loops=1)

5. 58.506 422.967 ↓ 235.2 71,262 1

Gather (cost=25,321.32..29,707.42 rows=303 width=83) (actual time=105.232..422.967 rows=71,262 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
6. 8.232 364.461 ↓ 188.5 23,754 3 / 3

Nested Loop Left Join (cost=24,321.32..28,677.12 rows=126 width=83) (actual time=186.924..364.461 rows=23,754 loops=3)

7. 9.618 284.967 ↓ 188.5 23,754 3 / 3

Nested Loop (cost=24,320.90..28,519.03 rows=126 width=62) (actual time=186.881..284.967 rows=23,754 loops=3)

8. 24.766 227.770 ↓ 174.9 23,790 3 / 3

Hash Join (cost=24,320.62..28,477.97 rows=136 width=53) (actual time=186.830..227.770 rows=23,790 loops=3)

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

  • Heap Fetches: 241
10. 19.152 186.635 ↓ 4.3 67,054 3 / 3

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

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

Nested Loop (cost=0.42..24,127.20 rows=15,440 width=45) (actual time=0.216..167.483 rows=67,054 loops=3)

12. 1.170 1.170 ↑ 1.0 72 3 / 3

Seq Scan on orgs o (cost=0.00..12.70 rows=72 width=24) (actual time=0.154..1.170 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. 158.040 158.040 ↓ 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.016..2.195 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. 28.923 1,496.502 ↓ 3.0 3 71,262

Nested Loop Left Join (cost=0.86..3.09 rows=1 width=47) (actual time=0.012..0.021 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. 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.013 0.265 ↑ 1.0 93 1

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

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

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

  • Filter: ((name)::text = 'Work Email'::text)
  • Rows Removed by Filter: 1300
21. 860.406 3,097.173 ↓ 15.9 229,795 1

Sort (cost=1,286.93..1,323.04 rows=14,445 width=1,424) (actual time=3,057.118..3,097.173 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,236.767 2,236.767 ↓ 15.9 229,795 1

CTE Scan on res (cost=0.00..288.90 rows=14,445 width=1,424) (actual time=105.567..2,236.767 rows=229,795 loops=1)

Planning time : 5.697 ms
Execution time : 3,487.091 ms