explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZKWb

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 284.618 2,299.072 ↓ 46.4 66,951 1

GroupAggregate (cost=33,454.03..33,836.79 rows=1,444 width=634) (actual time=2,005.472..2,299.072 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. 69.213 1,787.191 ↓ 9.6 138,321 1

Group (cost=30,492.09..32,167.20 rows=14,444 width=110) (actual time=1,501.343..1,787.191 rows=138,321 loops=1)

  • Group Key: o.customer_id, r.target_name, org_dvc_names.name, persons.person_id, dvc.descrion, role.name
4. 236.256 1,717.978 ↓ 11.5 138,321 1

Gather Merge (cost=30,492.09..31,986.66 rows=12,036 width=110) (actual time=1,501.341..1,717.978 rows=138,321 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
5. 27.451 1,481.722 ↓ 7.7 46,107 3 / 3

Group (cost=29,492.07..29,597.38 rows=6,018 width=110) (actual time=1,442.225..1,481.722 rows=46,107 loops=3)

  • Group Key: o.customer_id, r.target_name, org_dvc_names.name, persons.person_id, dvc.descrion, role.name
6. 238.779 1,454.271 ↓ 12.7 76,598 3 / 3

Sort (cost=29,492.07..29,507.11 rows=6,018 width=110) (actual time=1,442.220..1,454.271 rows=76,598 loops=3)

  • Sort Key: o.customer_id, r.target_name, org_dvc_names.name, persons.person_id, dvc.descrion, role.name
  • Sort Method: quicksort Memory: 20091kB
7. 23.150 1,215.492 ↓ 12.7 76,598 3 / 3

Nested Loop Left Join (cost=24,322.45..29,114.28 rows=6,018 width=110) (actual time=204.025..1,215.492 rows=76,598 loops=3)

8. 15.039 384.706 ↓ 188.5 23,754 3 / 3

Nested Loop Left Join (cost=24,321.31..28,677.12 rows=126 width=91) (actual time=203.922..384.706 rows=23,754 loops=3)

9. 6.906 298.405 ↓ 188.5 23,754 3 / 3

Nested Loop (cost=24,320.89..28,519.01 rows=126 width=62) (actual time=203.878..298.405 rows=23,754 loops=3)

10. 26.427 243.920 ↓ 174.9 23,790 3 / 3

Hash Join (cost=24,320.61..28,477.96 rows=136 width=53) (actual time=203.829..243.920 rows=23,790 loops=3)

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

  • Heap Fetches: 5186
12. 21.406 203.698 ↓ 4.3 67,054 3 / 3

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

  • Buckets: 131072 (originally 16384) Batches: 1 (originally 1) Memory Usage: 6177kB
13. 8.729 182.292 ↓ 4.3 67,054 3 / 3

Nested Loop (cost=0.42..24,127.20 rows=15,439 width=45) (actual time=0.217..182.292 rows=67,054 loops=3)

14. 1.339 1.339 ↑ 1.0 72 3 / 3

Seq Scan on orgs o (cost=0.00..12.70 rows=72 width=24) (actual time=0.152..1.339 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
15. 172.224 172.224 ↓ 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.392 rows=931 loops=216)

  • Index Cond: ((org_id = o.org_id) AND ((recipient_cat)::text = 'PERSON'::text))
16. 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
17. 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)
18. 14.345 807.636 ↓ 3.0 3 71,262 / 3

Nested Loop Left Join (cost=1.14..3.46 rows=1 width=48) (actual time=0.017..0.034 rows=3 loops=71,262)

19. 4.937 641.358 ↓ 3.0 3 71,262 / 3

Nested Loop Left Join (cost=0.86..3.16 rows=1 width=47) (actual time=0.014..0.027 rows=3 loops=71,262)

20. 332.556 332.556 ↓ 3.0 3 71,262 / 3

Index Scan using recipients_oi_tn_odni_stn_di_puidx on recipients r2 (cost=0.42..2.17 rows=1 width=37) (actual time=0.010..0.014 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
21. 303.865 303.865 ↓ 0.0 0 227,899 / 3

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

  • Index Cond: (dvc_id = r2.recipient_id)
  • Filter: ((dvc_type)::text = 'EMAIL'::text)
  • Rows Removed by Filter: 1
22. 151.933 151.933 ↓ 0.0 0 227,899 / 3

Index Scan using org_dvc_names_pk on org_dvc_names (cost=0.28..0.30 rows=1 width=17) (actual time=0.002..0.002 rows=0 loops=227,899)

  • Index Cond: (org_dvc_name_id = r2.org_dvc_name_id)
  • Filter: ((name)::text = 'Work Email'::text)
  • Rows Removed by Filter: 1
23. 123.813 2,014.454 ↓ 9.6 138,321 1

Sort (cost=1,286.83..1,322.94 rows=14,444 width=1,424) (actual time=2,005.426..2,014.454 rows=138,321 loops=1)

  • Sort Key: res.customer_id, res.target_name, res.first_name, res.last_name, res.status, res.when_created
  • Sort Method: quicksort Memory: 28427kB
24. 1,890.641 1,890.641 ↓ 9.6 138,321 1

CTE Scan on res (cost=0.00..288.88 rows=14,444 width=1,424) (actual time=1,501.347..1,890.641 rows=138,321 loops=1)

Planning time : 5.831 ms
Execution time : 2,321.892 ms