explain.depesz.com

PostgreSQL's explain analyze made readable

Result: UGqT

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

Aggregate (cost=4,193.22..4,193.23 rows=1 width=8) (actual time=50.283..50.283 rows=1 loops=1)

2.          

CTE get_targeted_event_ids

3. 0.027 9.073 ↓ 1.3 108 1

Nested Loop (cost=0.75..36.41 rows=82 width=8) (actual time=9.024..9.073 rows=108 loops=1)

4. 0.006 9.001 ↓ 3.0 3 1

HashAggregate (cost=0.47..0.48 rows=1 width=8) (actual time=9.001..9.001 rows=3 loops=1)

  • Group Key: retrieve_person_linked_recipients.recipient_id
5. 8.995 8.995 ↓ 3.0 3 1

Function Scan on retrieve_person_linked_recipients (cost=0.03..0.46 rows=1 width=8) (actual time=8.994..8.995 rows=3 loops=1)

  • Filter: ((org_id = 200189) AND (((recipient_cat)::text <> 'GROUP'::text) OR ((grp_association)::text <> 'supervisor'::text)))
6. 0.045 0.045 ↑ 2.3 36 3

Index Only Scan using ev_targeted_recipients_pkey on ev_targeted_recipients etr (cost=0.29..35.11 rows=82 width=16) (actual time=0.007..0.015 rows=36 loops=3)

  • Index Cond: ((org_id = 200189) AND (recipient_id = retrieve_person_linked_recipients.recipient_id))
  • Heap Fetches: 108
7. 15.058 50.265 ↑ 16.5 108 1

Hash Left Join (cost=2,781.22..4,152.37 rows=1,778 width=8) (actual time=43.611..50.265 rows=108 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)))
8. 0.000 34.785 ↑ 36.0 108 1

Index Scan using idx_evs_status on evs runtimeeve0_ (cost=1,363.45..2,724.35 rows=3,888 width=32) (actual time=28.234..34.785 rows=108 loops=1)

  • Index Cond: ((status)::text = ANY ('{TERMINATED_PASSFILL,TERMINATED_EXT_PASSFILL,TERMINATED_BY_USER,TERMINATED_EXTERNAL,SUSPENDED,TERMINATED_EXT_FAILFILL,TERMINATED,TERMINATED_WITH_ERR,CREATE,ACTIVE,TERMINATED_FAILFILL,PURGED,SUPPRESSED}'::text[]))
  • Filter: ((dmn_name IS NOT NULL) AND (system_message_type IS NULL) AND ((dmn_name)::text <> 'sms_deactivation'::text) AND ((dmn_name)::text <> 'generic_responses'::text) AND ((dmn_name)::text <> 'devicevalidation'::text) AND ((dmn_name)::text <> 'sms_confirmation'::text) AND ((dmn_name)::text <> 'voicerecordings'::text) AND ((status)::text <> 'SUPPRESSED'::text) AND ((dmn_name)::text <> 'onboarding_notification'::text) AND (company_id = 200189) AND (((hashed SubPlan 2) AND (system_message_type IS NULL)) OR (hashed SubPlan 3)))
  • Rows Removed by Filter: 15535
9.          

SubPlan (for Index Scan)

10. 29.974 29.974 ↓ 0.0 0 2

Index Scan using idx_evs_status on evs runtimeeve2_ (cost=0.29..1,361.19 rows=53 width=8) (actual time=14.987..14.987 rows=0 loops=2)

  • Index Cond: ((status)::text = ANY ('{TERMINATED_PASSFILL,TERMINATED_EXT_PASSFILL,TERMINATED_BY_USER,TERMINATED_EXTERNAL,SUSPENDED,TERMINATED_EXT_FAILFILL,TERMINATED,TERMINATED_WITH_ERR,CREATE,ACTIVE,TERMINATED_FAILFILL,PURGED,SUPPRESSED}'::text[]))
  • Filter: ((dmn_name IS NOT NULL) AND ((dmn_name)::text <> 'sms_deactivation'::text) AND ((dmn_name)::text <> 'generic_responses'::text) AND ((dmn_name)::text <> 'devicevalidation'::text) AND ((dmn_name)::text <> 'sms_confirmation'::text) AND ((dmn_name)::text <> 'voicerecordings'::text) AND ((status)::text <> 'SUPPRESSED'::text) AND ((dmn_name)::text <> 'onboarding_notification'::text) AND (company_id = 200189) AND (lower((sender)::text) = 'jblow'::text))
  • Rows Removed by Filter: 15643
11. 9.112 9.112 ↓ 1.3 108 2

CTE Scan on get_targeted_event_ids (cost=0.00..1.64 rows=82 width=8) (actual time=4.514..4.556 rows=108 loops=2)

12. 0.163 0.422 ↑ 1.0 916 1

Hash (cost=43.16..43.16 rows=916 width=12) (actual time=0.422..0.422 rows=916 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
13. 0.259 0.259 ↑ 1.0 916 1

Seq Scan on application applicatio1_ (cost=0.00..43.16 rows=916 width=12) (actual time=0.013..0.259 rows=916 loops=1)

Planning time : 5.799 ms
Execution time : 50.496 ms