explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 5SUd

Settings
# exclusive inclusive rows x rows loops node
1. 13.436 81,403.367 ↑ 43.7 1,672 1

Sort (cost=639,880.70..640,063.44 rows=73,096 width=2,515) (actual time=81,403.283..81,403.367 rows=1,672 loops=1)

  • Sort Key: (upper((COALESCE(runtimeeve0_.message_panel_name, runtimeeve0_.system_message_type, runtimeeve0_.dmn_name))::text)), runtimeeve0_.ev_id
  • Sort Method: quicksort Memory: 1747kB
2.          

CTE get_targeted_event_ids

3. 18.012 62,751.909 ↓ 1,672.0 1,672 1

Nested Loop (cost=2.00..84.49 rows=1 width=8) (actual time=12,828.556..62,751.909 rows=1,672 loops=1)

4. 27.089 55,795.097 ↓ 1,672.0 1,672 1

Nested Loop (cost=1.58..83.91 rows=1 width=8) (actual time=12,828.494..55,795.097 rows=1,672 loops=1)

5. 6.983 35,118.085 ↓ 78.5 3,217 1

Nested Loop (cost=1.16..65.22 rows=41 width=8) (actual time=1,299.572..35,118.085 rows=3,217 loops=1)

6. 0.031 1,281.458 ↓ 6.0 6 1

HashAggregate (cost=0.47..0.48 rows=1 width=32) (actual time=1,281.445..1,281.458 rows=6 loops=1)

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

Function Scan on retrieve_person_linked_recipients (cost=0.03..0.47 rows=1 width=32) (actual time=1,281.407..1,281.427 rows=25 loops=1)

  • Filter: ((org_id = 1) AND (((recipient_cat)::text <> 'GROUP'::text) OR ((grp_association)::text <> 'supervisor'::text)))
8. 33,829.644 33,829.644 ↓ 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=8.840..5,638.274 rows=536 loops=6)

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

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

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

Index Scan using comm_pk on comm c (cost=0.43..0.58 rows=1 width=16) (actual time=4.150..4.150 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. 9.984 81,389.931 ↑ 43.7 1,672 1

Nested Loop Left Join (cost=381,874.19..582,815.97 rows=73,096 width=2,515) (actual time=74,419.991..81,389.931 rows=1,672 loops=1)

12. 90.559 79,144.483 ↑ 43.7 1,672 1

Hash Right Join (cost=381,873.76..465,311.41 rows=73,096 width=478) (actual time=74,361.985..79,144.483 rows=1,672 loops=1)

  • Hash Cond: (runtimeeve2_.ev_id = runtimeeve0_.ev_id)
13. 4,728.287 4,728.287 ↑ 1.3 346,061 1

Seq Scan on evs_email_message runtimeeve2_ (cost=0.00..81,123.74 rows=461,574 width=102) (actual time=0.070..4,728.287 rows=346,061 loops=1)

14. 4.169 74,325.637 ↑ 43.7 1,672 1

Hash (cost=380,960.06..380,960.06 rows=73,096 width=376) (actual time=74,325.637..74,325.637 rows=1,672 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 1632kB
15. 34.393 74,321.468 ↑ 43.7 1,672 1

Merge Left Join (cost=375,278.68..380,960.06 rows=73,096 width=376) (actual time=69,213.970..74,321.468 rows=1,672 loops=1)

  • Merge Cond: (runtimeeve0_.ev_id = runtimeeve3_.comm_ev_id)
16. 3.814 68,995.123 ↑ 43.7 1,672 1

Sort (cost=375,278.26..375,461.00 rows=73,096 width=350) (actual time=68,994.564..68,995.123 rows=1,672 loops=1)

  • Sort Key: runtimeeve0_.ev_id
  • Sort Method: quicksort Memory: 911kB
17. 272.642 68,991.309 ↑ 43.7 1,672 1

Hash Left Join (cost=249,199.98..369,373.01 rows=73,096 width=350) (actual time=68,500.221..68,991.309 rows=1,672 loops=1)

  • Hash Cond: (runtimeeve0_.application_id = applicatio4_.application_id)
  • 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)))
18. 231.693 68,717.661 ↑ 73.6 1,672 1

Index Scan using evs_ev_dmn_id_pidx on evs runtimeeve0_ (cost=124,574.61..244,418.49 rows=123,027 width=350) (actual time=68,229.278..68,717.661 rows=1,672 loops=1)

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

SubPlan (forIndex Scan)

20. 5,727.538 5,727.538 ↓ 0.0 0 2

Seq Scan on evs runtimeeve5_ (cost=0.00..124,571.14 rows=1,209 width=8) (actual time=2,863.769..2,863.769 rows=0 loops=2)

  • Filter: ((dmn_name IS NOT NULL) AND ((dmn_name)::text <> 'devicevalidation'::text) AND ((dmn_name)::text <> 'voicerecordings'::text) AND ((dmn_name)::text <> 'generic_responses'::text) AND ((dmn_name)::text <> 'onboarding_notification'::text) AND ((dmn_name)::text <> 'sms_deactivation'::text) AND ((dmn_name)::text <> 'sms_confirmation'::text) AND ((status)::text <> 'SUPPRESSED'::text) AND (company_id = 1) AND (lower((sender)::text) = 'jblow'::text) AND ((status)::text = ANY ('{TERMINATED_FAILFILL,TERMINATED_EXTERNAL,TERMINATED_WITH_ERR,SUPPRESSED,CREATE,TERMINATED_PASSFILL,ACTIVE,PURGED,SUSPENDED,TERMINATED_EXT_FAILFILL,TERMINATED_BY_USER,TERMINATED,TERMINATED_EXT_PASSFILL}'::text[])))
  • Rows Removed by Filter: 346251
21. 62,758.430 62,758.430 ↓ 1,672.0 1,672 2

CTE Scan on get_targeted_event_ids (cost=0.00..0.02 rows=1 width=8) (actual time=6,414.281..31,379.215 rows=1,672 loops=2)

22. 0.077 1.006 ↑ 1.0 408 1

Hash (cost=46.08..46.08 rows=408 width=12) (actual time=1.006..1.006 rows=408 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
23. 0.929 0.929 ↑ 1.0 408 1

Seq Scan on application applicatio4_ (cost=0.00..46.08 rows=408 width=12) (actual time=0.015..0.929 rows=408 loops=1)

24. 5,291.952 5,291.952 ↑ 1.0 345,357 1

Index Scan using comm_comm_ev_id_pidx on comm runtimeeve3_ (cost=0.42..30,966.52 rows=348,570 width=26) (actual time=56.627..5,291.952 rows=345,357 loops=1)

25. 2,235.464 2,235.464 ↑ 1.0 1 1,672

Index Scan using ev_metadata_uc_ev_id on ev_metadata runtimeeve1_ (cost=0.42..1.61 rows=1 width=1,965) (actual time=1.337..1.337 rows=1 loops=1,672)

  • Index Cond: (runtimeeve0_.ev_id = ev_id)
Planning time : 865.725 ms
Execution time : 81,405.818 ms