explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 7P9E

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 844.022 ↑ 1.0 1 1

Limit (cost=1.83..122.56 rows=1 width=90) (actual time=844.022..844.022 rows=1 loops=1)

2. 0.005 844.019 ↑ 319.0 1 1

Nested Loop Left Join (cost=1.83..38,515.72 rows=319 width=90) (actual time=844.019..844.019 rows=1 loops=1)

  • Join Filter: (org_dvc_names.org_dvc_name_id = r2.org_dvc_name_id)
  • Rows Removed by Join Filter: 5
3. 0.006 843.966 ↑ 319.0 1 1

Nested Loop Left Join (cost=1.83..38,489.39 rows=319 width=89) (actual time=843.966..843.966 rows=1 loops=1)

4. 5.161 371.938 ↑ 4.0 1 1

Nested Loop (cost=0.98..529.07 rows=4 width=77) (actual time=371.937..371.938 rows=1 loops=1)

  • Join Filter: (pf.func_id = role.role_id)
  • Rows Removed by Join Filter: 38147
5. 0.033 0.033 ↑ 29.0 4 1

Seq Scan on role (cost=0.00..9.57 rows=116 width=25) (actual time=0.023..0.033 rows=4 loops=1)

  • Filter: ((name)::text <> ALL ('{"Company Admin","Super Admin"}'::text[]))
6. 7.422 366.744 ↓ 2,384.2 9,537 4

Materialize (cost=0.98..512.55 rows=4 width=68) (actual time=0.055..91.686 rows=9,537 loops=4)

7. 3.544 359.322 ↓ 3,051.5 12,206 1

Nested Loop Left Join (cost=0.98..512.53 rows=4 width=68) (actual time=0.212..359.322 rows=12,206 loops=1)

8. 4.160 331.366 ↓ 3,051.5 12,206 1

Nested Loop (cost=0.70..507.77 rows=4 width=48) (actual time=0.172..331.366 rows=12,206 loops=1)

9. 1.212 17.192 ↓ 47.8 5,741 1

Nested Loop (cost=0.41..431.33 rows=120 width=40) (actual time=0.126..17.192 rows=5,741 loops=1)

10. 0.103 0.103 ↑ 1.0 1 1

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

  • 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: 4
11. 15.877 15.877 ↓ 9.6 5,741 1

Index Scan using idx_recipients_del_org_id_cat on recipients r (cost=0.41..424.19 rows=601 width=24) (actual time=0.061..15.877 rows=5,741 loops=1)

  • Index Cond: ((org_id = o.org_id) AND ((recipient_cat)::text = 'PERSON'::text))
12. 310.014 310.014 ↑ 1.0 2 5,741

Index Only Scan using persons_func_pk on persons_func pf (cost=0.29..0.62 rows=2 width=16) (actual time=0.050..0.054 rows=2 loops=5,741)

  • Index Cond: (person_id = r.recipient_id)
  • Heap Fetches: 2098
13. 24.412 24.412 ↑ 1.0 1 12,206

Index Scan using persons_pk on persons (cost=0.28..1.19 rows=1 width=36) (actual time=0.002..0.002 rows=1 loops=12,206)

  • Index Cond: (person_id = r.recipient_id)
14. 0.004 472.022 ↓ 0.0 0 1

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

15. 472.018 472.018 ↓ 0.0 0 1

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

  • Index Cond: (org_id = r.org_id)
  • Filter: (((recipient_cat)::text = 'DEVICE'::text) AND ((target_name)::text = (r.target_name)::text))
  • Rows Removed by Filter: 425112
16. 0.000 0.000 ↓ 0.0 0

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

  • Index Cond: (dvc_id = r2.recipient_id)
  • Filter: ((dvc_type)::text = 'EMAIL'::text)
17. 0.010 0.048 ↑ 1.0 5 1

Materialize (cost=0.00..2.73 rows=5 width=17) (actual time=0.034..0.048 rows=5 loops=1)

18. 0.038 0.038 ↑ 1.0 5 1

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

  • Filter: ((name)::text = 'Work Email'::text)
  • Rows Removed by Filter: 51
Planning time : 13.031 ms
Execution time : 845.012 ms