explain.depesz.com

PostgreSQL's explain analyze made readable

Result: HuwN

Settings
# exclusive inclusive rows x rows loops node
1. 0.009 9,058.723 ↑ 12.5 8 1

Limit (cost=90.46..976.80 rows=100 width=2,443) (actual time=90.333..9,058.723 rows=8 loops=1)

2.          

CTE get_targeted_event_ids

3. 0.191 71.598 ↓ 1,672.0 1,672 1

Nested Loop (cost=2.00..84.49 rows=1 width=8) (actual time=31.159..71.598 rows=1,672 loops=1)

4. 1.549 54.687 ↓ 1,672.0 1,672 1

Nested Loop (cost=1.58..83.91 rows=1 width=8) (actual time=31.125..54.687 rows=1,672 loops=1)

5. 0.424 37.043 ↓ 78.5 3,219 1

Nested Loop (cost=1.16..65.22 rows=41 width=8) (actual time=20.527..37.043 rows=3,219 loops=1)

6. 0.017 20.449 ↓ 6.0 6 1

HashAggregate (cost=0.47..0.48 rows=1 width=32) (actual time=20.444..20.449 rows=6 loops=1)

  • Group Key: lower((retrieve_person_linked_recipients.target_name)::text)
7. 20.432 20.432 ↓ 25.0 25 1

Function Scan on retrieve_person_linked_recipients (cost=0.03..0.47 rows=1 width=32) (actual time=20.414..20.432 rows=25 loops=1)

  • Filter: ((org_id = 1) AND (((recipient_cat)::text <> 'GROUP'::text) OR ((grp_association)::text <> 'supervisor'::text)))
8. 16.170 16.170 ↓ 13.1 536 6

Index Scan using idx_comm_values0 on comm_values cv (cost=0.69..64.32 rows=41 width=128) (actual time=0.045..2.695 rows=536 loops=6)

  • Index Cond: (lower(""left""((value)::text, 200)) = (lower((retrieve_person_linked_recipients.target_name)::text)))
9. 16.095 16.095 ↑ 1.0 1 3,219

Index Scan using comm_flds_fld_id_persons_pidx on comm_flds cf (cost=0.42..0.46 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=3,219)

  • Index Cond: (comm_fld_id = cv.comm_fld_id)
10. 16.720 16.720 ↑ 1.0 1 1,672

Index Scan using comm_pk on comm c (cost=0.43..0.58 rows=1 width=16) (actual time=0.010..0.010 rows=1 loops=1,672)

  • Index Cond: (comm_id = cf.comm_id)
  • Filter: ((comm_ev_id IS NOT NULL) AND ((comm_type)::text = 'EVENT'::text))
11. 0.032 9,058.714 ↑ 5,523.6 8 1

Nested Loop Left Join (cost=5.97..391,670.99 rows=44,189 width=2,443) (actual time=90.331..9,058.714 rows=8 loops=1)

12. 0.029 9,058.490 ↑ 5,523.6 8 1

Nested Loop Left Join (cost=5.55..350,676.66 rows=44,189 width=2,417) (actual time=90.289..9,058.490 rows=8 loops=1)

13. 0.044 9,035.093 ↑ 5,523.6 8 1

Nested Loop Left Join (cost=5.12..281,355.34 rows=44,189 width=2,315) (actual time=90.236..9,035.093 rows=8 loops=1)

14. 0.617 9,034.849 ↑ 5,523.6 8 1

Nested Loop Left Join (cost=4.70..202,509.72 rows=44,189 width=350) (actual time=90.200..9,034.849 rows=8 loops=1)

  • Filter: (((runtimeeve0_.application_id IS NULL) OR (applicatio4_.application_id IS NULL) OR ((applicatio4_.type)::text <> 'SYSTEM'::text)) AND (((hashed SubPlan 2) AND (runtimeeve0_.system_message_type IS NULL) AND ((runtimeeve0_.application_id IS NULL) OR (applicatio4_.application_id IS NULL) OR ((applicatio4_.type)::text <> 'SYSTEM'::text))) OR (hashed SubPlan 3)))
15. 8,951.184 9,034.128 ↑ 9,296.9 8 1

Index Scan Backward using idx_evs_cdate on evs runtimeeve0_ (cost=2.43..179,383.25 rows=74,375 width=350) (actual time=89.632..9,034.128 rows=8 loops=1)

  • Filter: ((dmn_name IS NOT NULL) AND (system_message_type IS NULL) AND ((status)::text <> 'SUPPRESSED'::text) AND ((dmn_name)::text <> 'generic_responses'::text) AND ((dmn_name)::text <> 'sms_deactivation'::text) AND ((dmn_name)::text <> 'sms_confirmation'::text) AND ((dmn_name)::text <> 'devicevalidation'::text) AND ((dmn_name)::text <> 'voicerecordings'::text) AND ((dmn_name)::text <> 'onboarding_notification'::text) AND (company_id = 1) AND (upper((message_panel_name)::text) ~~ '%%'::text) AND (((hashed SubPlan 2) AND (system_message_type IS NULL)) OR (hashed SubPlan 3)) AND ((status)::text = ANY ('{SUSPENDED,PURGED,ACTIVE,SUPPRESSED,CREATE}'::text[])))
  • Rows Removed by Filter: 346711
16.          

SubPlan (for Index Scan Backward)

17. 10.742 10.742 ↓ 0.0 0 2

Index Scan using evs_company_id_idx on evs runtimeeve5_ (cost=0.42..1.98 rows=1 width=8) (actual time=5.371..5.371 rows=0 loops=2)

  • Index Cond: (company_id = 200000)
  • Filter: ((dmn_name IS NOT NULL) AND ((status)::text <> 'SUPPRESSED'::text) AND ((dmn_name)::text <> 'generic_responses'::text) AND ((dmn_name)::text <> 'sms_deactivation'::text) AND ((dmn_name)::text <> 'sms_confirmation'::text) AND ((dmn_name)::text <> 'devicevalidation'::text) AND ((dmn_name)::text <> 'voicerecordings'::text) AND ((dmn_name)::text <> 'onboarding_notification'::text) AND (upper((message_panel_name)::text) ~~ '%%'::text) AND (lower((sender)::text) = 'hle'::text) AND ((status)::text = ANY ('{SUSPENDED,PURGED,ACTIVE,SUPPRESSED,CREATE}'::text[])))
18. 72.202 72.202 ↓ 1,672.0 1,672 2

CTE Scan on get_targeted_event_ids (cost=0.00..0.02 rows=1 width=8) (actual time=15.582..36.101 rows=1,672 loops=2)

19. 0.104 0.104 ↑ 1.0 1 8

Index Scan using application_pk on application applicatio4_ (cost=0.27..0.29 rows=1 width=12) (actual time=0.013..0.013 rows=1 loops=8)

  • Index Cond: (runtimeeve0_.application_id = application_id)
20. 0.200 0.200 ↑ 1.0 1 8

Index Scan using ev_metadata_uc_ev_id on ev_metadata runtimeeve1_ (cost=0.42..1.78 rows=1 width=1,965) (actual time=0.025..0.025 rows=1 loops=8)

  • Index Cond: (runtimeeve0_.ev_id = ev_id)
21. 23.368 23.368 ↑ 1.0 1 8

Index Scan using evs_email_message_ev_id_uc on evs_email_message runtimeeve2_ (cost=0.42..1.57 rows=1 width=102) (actual time=2.921..2.921 rows=1 loops=8)

  • Index Cond: (runtimeeve0_.ev_id = ev_id)
22. 0.192 0.192 ↑ 1.0 1 8

Index Scan using comm_comm_ev_id_pidx on comm runtimeeve3_ (cost=0.42..0.93 rows=1 width=26) (actual time=0.024..0.024 rows=1 loops=8)

  • Index Cond: (runtimeeve0_.ev_id = comm_ev_id)
Planning time : 23.987 ms
Execution time : 9,059.662 ms