explain.depesz.com

PostgreSQL's explain analyze made readable

Result: kt7C : Optimization for: plan #ZKWb

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 278.462 2,252.826 ↓ 46.4 66,951 1

GroupAggregate (cost=33,444.49..33,827.25 rows=1,444 width=634) (actual time=1,965.844..2,252.826 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.392 1,747.735 ↓ 9.6 138,321 1

Group (cost=30,482.55..32,157.66 rows=14,444 width=110) (actual time=1,468.490..1,747.735 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. 225.584 1,678.343 ↓ 11.5 138,321 1

Gather Merge (cost=30,482.55..31,977.12 rows=12,036 width=110) (actual time=1,468.487..1,678.343 rows=138,321 loops=1)

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

Group (cost=29,482.53..29,587.85 rows=6,018 width=110) (actual time=1,415.232..1,452.759 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. 246.847 1,426.714 ↓ 12.7 76,598 3 / 3

Sort (cost=29,482.53..29,497.58 rows=6,018 width=110) (actual time=1,415.227..1,426.714 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: 20,083kB
7. 16.310 1,179.867 ↓ 12.7 76,598 3 / 3

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

8. 14.192 379.675 ↓ 188.5 23,754 3 / 3

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

9. 7.124 294.221 ↓ 188.5 23,754 3 / 3

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

10. 25.999 239.518 ↓ 174.9 23,790 3 / 3

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

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

  • Heap Fetches: 4,672
12. 21.431 199.824 ↓ 4.3 67,054 3 / 3

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

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

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

14. 1.319 1.319 ↑ 1.0 72 3 / 3

Seq Scan on orgs o (cost=0.00..12.70 rows=72 width=24) (actual time=0.157..1.319 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. 168.984 168.984 ↓ 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.347 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 783.882 ↓ 3.0 3 71,262 / 3

Nested Loop Left Join (cost=1.14..3.38 rows=1 width=48) (actual time=0.016..0.033 rows=3 loops=71,262)

19. 57.149 617.604 ↓ 3.0 3 71,262 / 3

Nested Loop Left Join (cost=0.86..3.09 rows=1 width=47) (actual time=0.014..0.026 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. 227.899 227.899 ↓ 0.0 0 227,899 / 3

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
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.609 1,974.364 ↓ 9.6 138,321 1

Sort (cost=1,286.83..1,322.94 rows=14,444 width=1,424) (actual time=1,965.794..1,974.364 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: 28,427kB
24. 1,850.755 1,850.755 ↓ 9.6 138,321 1

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

Planning time : 5.950 ms
Execution time : 2,275.837 ms