explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EFhr

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=21,026.76..21,037.73 rows=200 width=634) (actual rows= loops=)

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

CTE res

3. 0.000 0.000 ↓ 0.0

Group (cost=20,979.85..21,007.11 rows=319 width=98) (actual rows= loops=)

  • Group Key: o.customer_id, r.target_name, org_dvc_names.name, persons.person_id, dvc.descrion, role.name
4. 0.000 0.000 ↓ 0.0

Gather Merge (cost=20,979.85..21,004.29 rows=188 width=98) (actual rows= loops=)

  • Workers Planned: 1
5. 0.000 0.000 ↓ 0.0

Group (cost=19,979.84..19,983.13 rows=188 width=98) (actual rows= loops=)

  • Group Key: o.customer_id, r.target_name, org_dvc_names.name, persons.person_id, dvc.descrion, role.name
6. 0.000 0.000 ↓ 0.0

Sort (cost=19,979.84..19,980.31 rows=188 width=98) (actual rows= loops=)

  • Sort Key: o.customer_id, r.target_name, org_dvc_names.name, persons.person_id, dvc.descrion, role.name
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=442.96..19,972.74 rows=188 width=98) (actual rows= loops=)

  • Hash Cond: (r2.org_dvc_name_id = org_dvc_names.org_dvc_name_id)
8. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=440.20..19,969.45 rows=188 width=97) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=439.91..19,746.01 rows=188 width=69) (actual rows= loops=)

10. 0.000 0.000 ↓ 0.0

Nested Loop (cost=439.07..765.85 rows=2 width=57) (actual rows= loops=)

11. 0.000 0.000 ↓ 0.0

Merge Join (cost=438.93..765.52 rows=2 width=48) (actual rows= loops=)

  • Merge Cond: (pf.person_id = r.recipient_id)
12. 0.000 0.000 ↓ 0.0

Parallel Index Only Scan using persons_func_pk on persons_func pf (cost=0.29..311.50 rows=7,174 width=16) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Sort (cost=435.48..435.78 rows=120 width=40) (actual rows= loops=)

  • Sort Key: r.recipient_id
14. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.41..431.33 rows=120 width=40) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Seq Scan on orgs o (cost=0.00..1.14 rows=1 width=24) (actual rows= loops=)

  • 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[])))
16. 0.000 0.000 ↓ 0.0

Index Scan using idx_recipients_del_org_id_cat on recipients r (cost=0.41..424.19 rows=601 width=24) (actual rows= loops=)

  • Index Cond: ((org_id = o.org_id) AND ((recipient_cat)::text = 'PERSON'::text))
17. 0.000 0.000 ↓ 0.0

Index Scan using role_pk on role (cost=0.14..0.16 rows=1 width=25) (actual rows= loops=)

  • Index Cond: (role_id = pf.func_id)
  • Filter: ((name)::text <> ALL ('{"Company Admin","Super Admin"}'::text[]))
18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.84..9,489.33 rows=75 width=36) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Index Scan using idx_recipients1 on recipients r2 (cost=0.42..9,449.41 rows=75 width=32) (actual rows= loops=)

  • Index Cond: (org_id = r.org_id)
  • Filter: (((recipient_cat)::text = 'DEVICE'::text) AND ((target_name)::text = (r.target_name)::text))
20. 0.000 0.000 ↓ 0.0

Index Scan using dvc_dvc_id_status_idx on dvc (cost=0.42..0.53 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (dvc_id = r2.recipient_id)
  • Filter: ((dvc_type)::text = 'EMAIL'::text)
21. 0.000 0.000 ↓ 0.0

Index Scan using persons_pk on persons (cost=0.28..1.19 rows=1 width=36) (actual rows= loops=)

  • Index Cond: (person_id = r.recipient_id)
22. 0.000 0.000 ↓ 0.0

Hash (cost=2.70..2.70 rows=5 width=17) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Seq Scan on org_dvc_names (cost=0.00..2.70 rows=5 width=17) (actual rows= loops=)

  • Filter: ((name)::text = 'Work Email'::text)
24. 0.000 0.000 ↓ 0.0

Sort (cost=19.65..20.44 rows=319 width=1,424) (actual rows= loops=)

  • Sort Key: res.customer_id, res.target_name, res.first_name, res.last_name, res.status, res.when_created
25. 0.000 0.000 ↓ 0.0

CTE Scan on res (cost=0.00..6.38 rows=319 width=1,424) (actual rows= loops=)