explain.depesz.com

PostgreSQL's explain analyze made readable

Result: AQz

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 92,096.017 ↑ 1.0 100 1

Limit (cost=57,201.60..57,482.33 rows=100 width=2,275) (actual time=92,090.064..92,096.017 rows=100 loops=1)

2.          

CTE get_targeted_event_ids

3. 46.291 91,370.578 ↓ 57,131.0 57,131 1

Nested Loop (cost=2.14..153.43 rows=1 width=8) (actual time=43.821..91,370.578 rows=57,131 loops=1)

4. 36.370 89,381.833 ↓ 57,131.0 57,131 1

Nested Loop (cost=1.71..152.66 rows=1 width=8) (actual time=43.784..89,381.833 rows=57,131 loops=1)

5. 28.851 61,873.623 ↓ 1,526.2 114,466 1

Nested Loop (cost=1.28..118.40 rows=75 width=8) (actual time=43.732..61,873.623 rows=114,466 loops=1)

6. 0.171 43.704 ↓ 79.0 79 1

HashAggregate (cost=0.47..0.48 rows=1 width=32) (actual time=43.579..43.704 rows=79 loops=1)

  • Group Key: lower((retrieve_person_linked_recipients.target_name)::text)
7. 43.533 43.533 ↓ 138.0 138 1

Function Scan on retrieve_person_linked_recipients (cost=0.03..0.47 rows=1 width=32) (actual time=43.436..43.533 rows=138 loops=1)

  • Filter: ((org_id = 1) AND ((recipient_cat)::text = 'GROUP'::text) AND ((grp_association)::text = 'supervisor'::text))
  • Rows Removed by Filter: 6
8. 61,801.068 61,801.068 ↓ 19.3 1,449 79

Index Scan using idx_comm_values0 on comm_values cv (cost=0.81..117.16 rows=75 width=128) (actual time=2.904..782.292 rows=1,449 loops=79)

  • Index Cond: (lower("left"((value)::text, 200)) = (lower((retrieve_person_linked_recipients.target_name)::text)))
9. 27,471.840 27,471.840 ↓ 0.0 0 114,466

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.240..0.240 rows=0 loops=114,466)

  • Index Cond: (comm_fld_id = cv.comm_fld_id)
10. 1,942.454 1,942.454 ↑ 1.0 1 57,131

Index Scan using comm_pk on comm c (cost=0.43..0.78 rows=1 width=16) (actual time=0.034..0.034 rows=1 loops=57,131)

  • Index Cond: (comm_id = cf.comm_id)
  • Filter: ((comm_ev_id IS NOT NULL) AND ((comm_type)::text = 'EVENT'::text))
11. 0.029 92,095.999 ↑ 3,480.8 100 1

Nested Loop Left Join (cost=57,048.16..1,034,249.88 rows=348,084 width=2,275) (actual time=92,090.062..92,095.999 rows=100 loops=1)

12. 0.043 92,095.370 ↑ 3,480.8 100 1

Nested Loop Left Join (cost=57,047.74..767,257.30 rows=348,084 width=2,249) (actual time=92,089.931..92,095.370 rows=100 loops=1)

13. 0.086 92,094.627 ↑ 3,480.8 100 1

Nested Loop Left Join (cost=57,047.31..588,571.61 rows=348,084 width=2,168) (actual time=92,089.897..92,094.627 rows=100 loops=1)

14. 0.045 92,093.241 ↑ 3,480.8 100 1

Nested Loop Left Join (cost=57,046.89..222,811.85 rows=348,084 width=330) (actual time=92,089.859..92,093.241 rows=100 loops=1)

  • Filter: ((runtimeeve0_.application_id IS NULL) OR (applicatio4_.application_id IS NULL) OR ((applicatio4_.name)::text <> ALL ('{"Voice Recording Notifications","Error Response Notifications","User Upload Onboarding Notifications","Data Upload Notifications"}'::text[])))
15. 91.921 92,092.896 ↑ 4,573.2 100 1

Index Scan Backward using idx_evs_cdate on evs runtimeeve0_ (cost=57,046.75..144,161.78 rows=457,320 width=330) (actual time=92,089.716..92,092.896 rows=100 loops=1)

  • Filter: ((dmn_name IS NOT NULL) AND ((status)::text <> 'SUPPRESSED'::text) AND ((system_message_type IS NULL) OR ((system_message_type)::text = 'SYSTEM_MESSAGE'::text)) AND (company_id = 1) AND ((hashed SubPlan 2) OR (hashed SubPlan 3)) AND ((status)::text = ANY ('{SUPPRESSED,TERMINATED_WITH_ERR,TERMINATED_EXTERNAL,SUSPENDED,TERMINATED,TERMINATED_BY_USER,TERMINATED_EXT_PASSFILL,TERMINATED_PASSFILL,CREATE,TERMINATED_EXT_FAILFILL,PURGED,ACTIVE,TERMINATED_FAILFILL}'::text[])))
  • Rows Removed by Filter: 1842
16.          

SubPlan (for Index Scan Backward)

17. 596.857 596.857 ↓ 0.0 0 1

Seq Scan on evs runtimeeve5_ (cost=0.00..57,038.60 rows=3,081 width=8) (actual time=596.857..596.857 rows=0 loops=1)

  • Filter: ((dmn_name IS NOT NULL) AND ((status)::text <> 'SUPPRESSED'::text) AND (company_id = 1) AND (lower((sender)::text) = 'amunster'::text) AND ((status)::text = ANY ('{SUPPRESSED,TERMINATED_WITH_ERR,TERMINATED_EXTERNAL,SUSPENDED,TERMINATED,TERMINATED_BY_USER,TERMINATED_EXT_PASSFILL,TERMINATED_PASSFILL,CREATE,TERMINATED_EXT_FAILFILL,PURGED,ACTIVE,TERMINATED_FAILFILL}'::text[])))
  • Rows Removed by Filter: 658496
18. 91,404.118 91,404.118 ↓ 57,131.0 57,131 1

CTE Scan on get_targeted_event_ids (cost=0.00..0.02 rows=1 width=8) (actual time=43.824..91,404.118 rows=57,131 loops=1)

19. 0.300 0.300 ↑ 1.0 1 100

Index Scan using application_pk on application applicatio4_ (cost=0.14..0.16 rows=1 width=27) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (runtimeeve0_.application_id = application_id)
20. 1.300 1.300 ↑ 1.0 1 100

Index Scan using ev_metadata_uc_ev_id on ev_metadata runtimeeve1_ (cost=0.42..1.05 rows=1 width=1,838) (actual time=0.013..0.013 rows=1 loops=100)

  • Index Cond: (runtimeeve0_.ev_id = ev_id)
21. 0.700 0.700 ↑ 1.0 1 100

Index Scan using evs_email_message_ev_id_uc on evs_email_message runtimeeve2_ (cost=0.42..0.51 rows=1 width=81) (actual time=0.007..0.007 rows=1 loops=100)

  • Index Cond: (runtimeeve0_.ev_id = ev_id)
22. 0.600 0.600 ↑ 1.0 1 100

Index Scan using comm_comm_ev_id_uidx on comm runtimeeve3_ (cost=0.42..0.77 rows=1 width=26) (actual time=0.006..0.006 rows=1 loops=100)

  • Index Cond: (runtimeeve0_.ev_id = comm_ev_id)
  • Filter: ((comm_type)::text = 'EVENT'::text)
Planning time : 15.230 ms
Execution time : 92,099.746 ms