explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aIjm

Settings
# exclusive inclusive rows x rows loops node
1. 17.326 83,214.663 ↑ 43.7 1,672 1

Sort (cost=639,880.70..640,063.44 rows=73,096 width=2,515) (actual time=83,214.580..83,214.663 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. 5.032 25,293.725 ↓ 1,672.0 1,672 1

Nested Loop (cost=2.00..84.49 rows=1 width=8) (actual time=7,717.091..25,293.725 rows=1,672 loops=1)

4. 11.498 19,254.445 ↓ 1,672.0 1,672 1

Nested Loop (cost=1.58..83.91 rows=1 width=8) (actual time=7,717.045..19,254.445 rows=1,672 loops=1)

5. 2.481 12,603.059 ↓ 78.5 3,217 1

Nested Loop (cost=1.16..65.22 rows=41 width=8) (actual time=1,173.521..12,603.059 rows=3,217 loops=1)

6. 0.017 1,173.122 ↓ 6.0 6 1

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

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

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

  • Filter: ((org_id = 1) AND (((recipient_cat)::text <> 'GROUP'::text) OR ((grp_association)::text <> 'supervisor'::text)))
8. 11,427.456 11,427.456 ↓ 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=0.159..1,904.576 rows=536 loops=6)

  • Index Cond: (lower(""left""((value)::text, 200)) = (lower((retrieve_person_linked_recipients.target_name)::text)))
9. 6,639.888 6,639.888 ↑ 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=2.064..2.064 rows=1 loops=3,217)

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

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

Nested Loop Left Join (cost=381,874.19..582,815.97 rows=73,096 width=2,515) (actual time=59,216.571..83,197.337 rows=1,672 loops=1)

12. 93.352 68,928.970 ↑ 43.7 1,672 1

Hash Right Join (cost=381,873.76..465,311.41 rows=73,096 width=478) (actual time=59,129.395..68,928.970 rows=1,672 loops=1)

  • Hash Cond: (runtimeeve2_.ev_id = runtimeeve0_.ev_id)
13. 9,764.741 9,764.741 ↑ 1.3 346,091 1

Seq Scan on evs_email_message runtimeeve2_ (cost=0.00..81,123.74 rows=461,574 width=102) (actual time=37.510..9,764.741 rows=346,091 loops=1)

14. 4.072 59,070.877 ↑ 43.7 1,672 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 1632kB
15. 34.334 59,066.805 ↑ 43.7 1,672 1

Merge Left Join (cost=375,278.68..380,960.06 rows=73,096 width=376) (actual time=53,713.477..59,066.805 rows=1,672 loops=1)

  • Merge Cond: (runtimeeve0_.ev_id = runtimeeve3_.comm_ev_id)
16. 6.543 53,439.998 ↑ 43.7 1,672 1

Sort (cost=375,278.26..375,461.00 rows=73,096 width=350) (actual time=53,439.444..53,439.998 rows=1,672 loops=1)

  • Sort Key: runtimeeve0_.ev_id
  • Sort Method: quicksort Memory: 911kB
17. 278.868 53,433.455 ↑ 43.7 1,672 1

Hash Left Join (cost=249,199.98..369,373.01 rows=73,096 width=350) (actual time=38,217.152..53,433.455 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. 14,946.309 53,153.485 ↑ 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=37,944.544..53,153.485 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: 341702
19.          

SubPlan (for Index Scan)

20. 12,910.278 12,910.278 ↓ 0.0 0 2

Seq Scan on evs runtimeeve5_ (cost=0.00..124,571.14 rows=1,209 width=8) (actual time=6,455.139..6,455.139 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: 346281
21. 25,296.898 25,296.898 ↓ 1,672.0 1,672 2

CTE Scan on get_targeted_event_ids (cost=0.00..0.02 rows=1 width=8) (actual time=3,858.549..12,648.449 rows=1,672 loops=2)

22. 0.076 1.102 ↑ 1.0 408 1

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

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

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

24. 5,592.473 5,592.473 ↑ 1.0 345,364 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=60.258..5,592.473 rows=345,364 loops=1)

25. 14,248.784 14,248.784 ↑ 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=8.522..8.522 rows=1 loops=1,672)

  • Index Cond: (runtimeeve0_.ev_id = ev_id)
Planning time : 1,315.729 ms
Execution time : 83,217.370 ms