explain.depesz.com

PostgreSQL's explain analyze made readable

Result: IjVz

Settings
# exclusive inclusive rows x rows loops node
1. 0.018 996.977 ↑ 1.0 100 1

Limit (cost=117,029.69..117,514.24 rows=100 width=2,408) (actual time=990.731..996.977 rows=100 loops=1)

2.          

CTE get_targeted_event_ids

3. 14.170 455.872 ↓ 32,974.0 32,974 1

Nested Loop (cost=2.01..88.42 rows=1 width=8) (actual time=19.513..455.872 rows=32,974 loops=1)

4. 1.114 309.806 ↓ 32,974.0 32,974 1

Nested Loop (cost=1.58..87.83 rows=1 width=8) (actual time=19.487..309.806 rows=32,974 loops=1)

5. 4.468 143.822 ↓ 766.8 32,974 1

Nested Loop (cost=1.16..68.28 rows=43 width=8) (actual time=19.444..143.822 rows=32,974 loops=1)

6. 0.007 19.369 ↑ 1.0 1 1

HashAggregate (cost=0.47..0.48 rows=1 width=32) (actual time=19.368..19.369 rows=1 loops=1)

  • Group Key: lower((retrieve_person_linked_recipients.target_name)::text)
7. 19.362 19.362 ↑ 1.0 1 1

Function Scan on retrieve_person_linked_recipients (cost=0.03..0.47 rows=1 width=32) (actual time=19.360..19.362 rows=1 loops=1)

  • Filter: ((org_id = 1) AND ((recipient_cat)::text = 'GROUP'::text) AND ((grp_association)::text = 'supervisor'::text))
  • Rows Removed by Filter: 19
8. 119.985 119.985 ↓ 766.8 32,974 1

Index Scan using idx_comm_values0 on comm_values cv (cost=0.69..67.36 rows=43 width=128) (actual time=0.071..119.985 rows=32,974 loops=1)

  • Index Cond: (lower(""left""((value)::text, 200)) = (lower((retrieve_person_linked_recipients.target_name)::text)))
9. 164.870 164.870 ↑ 1.0 1 32,974

Index Scan using comm_flds_fld_id_persons_pidx on comm_flds cf (cost=0.42..0.45 rows=1 width=16) (actual time=0.005..0.005 rows=1 loops=32,974)

  • Index Cond: (comm_fld_id = cv.comm_fld_id)
10. 131.896 131.896 ↑ 1.0 1 32,974

Index Scan using comm_pk on comm c (cost=0.43..0.59 rows=1 width=16) (actual time=0.004..0.004 rows=1 loops=32,974)

  • Index Cond: (comm_id = cf.comm_id)
  • Filter: ((comm_ev_id IS NOT NULL) AND ((comm_type)::text = 'EVENT'::text))
11. 0.018 996.959 ↑ 1,533.3 100 1

Nested Loop Left Join (cost=116,941.27..859,887.25 rows=153,329 width=2,408) (actual time=990.729..996.959 rows=100 loops=1)

12. 0.045 996.441 ↑ 1,533.3 100 1

Nested Loop Left Join (cost=116,940.85..757,005.52 rows=153,329 width=2,382) (actual time=990.694..996.441 rows=100 loops=1)

13. 0.083 995.596 ↑ 1,533.3 100 1

Nested Loop Left Join (cost=116,940.42..570,001.05 rows=153,329 width=2,279) (actual time=990.670..995.596 rows=100 loops=1)

14. 0.076 994.613 ↑ 1,533.3 100 1

Nested Loop Left Join (cost=116,940.00..338,999.83 rows=153,329 width=349) (actual time=990.625..994.613 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. 21.145 994.337 ↑ 1,585.9 100 1

Index Scan Backward using idx_evs_cdate on evs runtimeeve0_ (cost=116,939.73..290,568.13 rows=158,587 width=349) (actual time=990.597..994.337 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: 2607
16.          

SubPlan (for Index Scan Backward)

17. 509.481 509.481 ↓ 0.0 0 1

Seq Scan on evs runtimeeve5_ (cost=0.00..116,936.01 rows=1,307 width=8) (actual time=509.481..509.481 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: 365254
18. 463.711 463.711 ↓ 32,974.0 32,974 1

CTE Scan on get_targeted_event_ids (cost=0.00..0.02 rows=1 width=8) (actual time=19.516..463.711 rows=32,974 loops=1)

19. 0.200 0.200 ↑ 1.0 1 100

Index Scan using application_pk on application applicatio4_ (cost=0.27..0.29 rows=1 width=26) (actual time=0.002..0.002 rows=1 loops=100)

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

Index Scan using ev_metadata_uc_ev_id on ev_metadata runtimeeve1_ (cost=0.42..1.51 rows=1 width=1,930) (actual time=0.009..0.009 rows=1 loops=100)

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

Index Scan using evs_email_message_ev_id_uc on evs_email_message runtimeeve2_ (cost=0.42..1.22 rows=1 width=103) (actual time=0.008..0.008 rows=1 loops=100)

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

Index Scan using comm_comm_ev_id_pidx on comm runtimeeve3_ (cost=0.42..0.67 rows=1 width=26) (actual time=0.005..0.005 rows=1 loops=100)

  • Index Cond: (runtimeeve0_.ev_id = comm_ev_id)
Planning time : 12.987 ms
Execution time : 998.843 ms