explain.depesz.com

PostgreSQL's explain analyze made readable

Result: uvQo

Settings
# exclusive inclusive rows x rows loops node
1. 0.021 68,964.917 ↑ 1.0 1 1

Aggregate (cost=1,067.78..1,067.79 rows=1 width=8) (actual time=68,964.917..68,964.917 rows=1 loops=1)

2.          

CTE get_targeted_event_ids

3. 17.747 68,917.314 ↓ 1,972.0 1,972 1

Nested Loop (cost=2.14..151.50 rows=1 width=8) (actual time=340.441..68,917.314 rows=1,972 loops=1)

4. 51.917 51,841.767 ↓ 1,972.0 1,972 1

Nested Loop (cost=1.71..150.73 rows=1 width=8) (actual time=340.265..51,841.767 rows=1,972 loops=1)

5. 15.885 40,410.238 ↓ 106.9 7,908 1

Nested Loop (cost=1.28..116.92 rows=74 width=8) (actual time=331.338..40,410.238 rows=7,908 loops=1)

6. 0.049 279.628 ↓ 5.0 5 1

HashAggregate (cost=0.47..0.48 rows=1 width=32) (actual time=279.590..279.628 rows=5 loops=1)

  • Group Key: lower((retrieve_person_linked_recipients.target_name)::text)
7. 279.579 279.579 ↓ 5.0 5 1

Function Scan on retrieve_person_linked_recipients (cost=0.03..0.47 rows=1 width=32) (actual time=279.569..279.579 rows=5 loops=1)

  • Filter: ((org_id = 1) AND (((recipient_cat)::text <> 'GROUP'::text) OR ((grp_association)::text <> 'supervisor'::text)))
8. 40,114.725 40,114.725 ↓ 21.4 1,582 5

Index Scan using idx_comm_values0 on comm_values cv (cost=0.81..115.69 rows=74 width=128) (actual time=26.834..8,022.945 rows=1,582 loops=5)

  • Index Cond: (lower(""left""((value)::text, 200)) = (lower((retrieve_person_linked_recipients.target_name)::text)))
9. 11,379.612 11,379.612 ↓ 0.0 0 7,908

Index Scan using comm_flds_fld_id_persons_pidx on comm_flds cf (cost=0.42..0.46 rows=1 width=16) (actual time=1.439..1.439 rows=0 loops=7,908)

  • Index Cond: (comm_fld_id = cv.comm_fld_id)
10. 17,057.800 17,057.800 ↑ 1.0 1 1,972

Index Scan using comm_pk on comm c (cost=0.43..0.76 rows=1 width=16) (actual time=8.650..8.650 rows=1 loops=1,972)

  • Index Cond: (comm_id = cf.comm_id)
  • Filter: ((comm_ev_id IS NOT NULL) AND ((comm_type)::text = 'EVENT'::text))
11. 8.151 68,964.896 ↑ 8.0 9 1

Hash Left Join (cost=611.52..916.10 rows=72 width=8) (actual time=68,959.862..68,964.896 rows=9 loops=1)

  • Hash Cond: (runtimeeve0_.application_id = applicatio1_.application_id)
  • Filter: (((runtimeeve0_.application_id IS NULL) OR (applicatio1_.application_id IS NULL) OR ((applicatio1_.type)::text <> 'SYSTEM'::text)) AND (((hashed SubPlan 2) AND (runtimeeve0_.system_message_type IS NULL) AND ((runtimeeve0_.application_id IS NULL) OR (applicatio1_.application_id IS NULL) OR ((applicatio1_.type)::text <> 'SYSTEM'::text))) OR (hashed SubPlan 3)))
12. 8.347 68,956.413 ↑ 61.6 9 1

Index Scan using idx_evs_status on evs runtimeeve0_ (cost=303.82..606.76 rows=554 width=34) (actual time=68,951.398..68,956.413 rows=9 loops=1)

  • Index Cond: ((status)::text = ANY ('{SUSPENDED,PURGED,ACTIVE,SUPPRESSED,CREATE}'::text[]))
  • 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)))
  • Rows Removed by Filter: 592
13.          

SubPlan (for Index Scan)

14. 21.736 21.736 ↓ 0.0 0 2

Index Scan using idx_evs_status on evs runtimeeve2_ (cost=0.42..303.36 rows=4 width=8) (actual time=10.868..10.868 rows=0 loops=2)

  • Index Cond: ((status)::text = ANY ('{SUSPENDED,PURGED,ACTIVE,SUPPRESSED,CREATE}'::text[]))
  • 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 (company_id = 1) AND (upper((message_panel_name)::text) ~~ '%%'::text) AND (lower((sender)::text) = '43952976'::text))
  • Rows Removed by Filter: 601
15. 68,926.330 68,926.330 ↓ 1,972.0 1,972 2

CTE Scan on get_targeted_event_ids (cost=0.00..0.02 rows=1 width=8) (actual time=170.223..34,463.165 rows=1,972 loops=2)

16. 0.035 0.332 ↑ 1.0 58 1

Hash (cost=3.58..3.58 rows=58 width=13) (actual time=0.332..0.332 rows=58 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
17. 0.297 0.297 ↑ 1.0 58 1

Seq Scan on application applicatio1_ (cost=0.00..3.58 rows=58 width=13) (actual time=0.117..0.297 rows=58 loops=1)

Planning time : 16.405 ms
Execution time : 68,967.172 ms