explain.depesz.com

PostgreSQL's explain analyze made readable

Result: YWGW

Settings
# exclusive inclusive rows x rows loops node
1. 0.020 1,868.117 ↑ 1.0 100 1

Limit (cost=585,694.14..585,694.39 rows=100 width=2,515) (actual time=1,868.094..1,868.117 rows=100 loops=1)

2.          

CTE get_targeted_event_ids

3. 0.000 63.580 ↓ 1,672.0 1,672 1

Nested Loop (cost=2.00..84.49 rows=1 width=8) (actual time=28.705..63.580 rows=1,672 loops=1)

4. 0.000 48.735 ↓ 1,672.0 1,672 1

Nested Loop (cost=1.58..83.91 rows=1 width=8) (actual time=28.678..48.735 rows=1,672 loops=1)

5. 0.415 33.250 ↓ 78.5 3,217 1

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

6. 0.016 20.007 ↓ 6.0 6 1

HashAggregate (cost=0.47..0.48 rows=1 width=32) (actual time=20.002..20.007 rows=6 loops=1)

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

Function Scan on retrieve_person_linked_recipients (cost=0.03..0.47 rows=1 width=32) (actual time=19.974..19.991 rows=25 loops=1)

  • Filter: ((org_id = 1) AND (((recipient_cat)::text <> 'GROUP'::text) OR ((grp_association)::text <> 'supervisor'::text)))
8. 12.828 12.828 ↓ 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.037..2.138 rows=536 loops=6)

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

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

Index Scan using comm_pk on comm c (cost=0.43..0.58 rows=1 width=16) (actual time=0.009..0.009 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. 2.244 1,868.097 ↑ 731.0 100 1

Sort (cost=585,609.64..585,792.38 rows=73,096 width=2,515) (actual time=1,868.092..1,868.097 rows=100 loops=1)

  • Sort Key: (upper((COALESCE(runtimeeve0_.message_panel_name, runtimeeve0_.system_message_type, runtimeeve0_.dmn_name))::text)), runtimeeve0_.ev_id
  • Sort Method: top-N heapsort Memory: 203kB
12. 2.442 1,865.853 ↑ 43.7 1,672 1

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

13. 54.890 1,850.035 ↑ 43.7 1,672 1

Hash Right Join (cost=381,873.76..465,311.41 rows=73,096 width=478) (actual time=1,524.823..1,850.035 rows=1,672 loops=1)

  • Hash Cond: (runtimeeve2_.ev_id = runtimeeve0_.ev_id)
14. 271.121 271.121 ↑ 1.3 346,154 1

Seq Scan on evs_email_message runtimeeve2_ (cost=0.00..81,123.74 rows=461,574 width=102) (actual time=0.023..271.121 rows=346,154 loops=1)

15. 2.188 1,524.024 ↑ 43.7 1,672 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 1632kB
16. 31.428 1,521.836 ↑ 43.7 1,672 1

Merge Left Join (cost=375,278.68..380,960.06 rows=73,096 width=376) (actual time=1,254.832..1,521.836 rows=1,672 loops=1)

  • Merge Cond: (runtimeeve0_.ev_id = runtimeeve3_.comm_ev_id)
17. 2.189 1,243.511 ↑ 43.7 1,672 1

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

  • Sort Key: runtimeeve0_.ev_id
  • Sort Method: quicksort Memory: 911kB
18. 271.095 1,241.322 ↑ 43.7 1,672 1

Hash Left Join (cost=249,199.98..369,373.01 rows=73,096 width=350) (actual time=843.266..1,241.322 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)))
19. 131.488 969.804 ↑ 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=573.865..969.804 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: 341764
20.          

SubPlan (for Index Scan)

21. 774.118 774.118 ↓ 0.0 0 2

Seq Scan on evs runtimeeve5_ (cost=0.00..124,571.14 rows=1,209 width=8) (actual time=387.059..387.059 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: 346344
22. 64.198 64.198 ↓ 1,672.0 1,672 2

CTE Scan on get_targeted_event_ids (cost=0.00..0.02 rows=1 width=8) (actual time=14.358..32.099 rows=1,672 loops=2)

23. 0.074 0.423 ↑ 1.0 408 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
24. 0.349 0.349 ↑ 1.0 408 1

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

25. 246.897 246.897 ↑ 1.0 345,388 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=2.657..246.897 rows=345,388 loops=1)

26. 13.376 13.376 ↑ 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=0.008..0.008 rows=1 loops=1,672)

  • Index Cond: (runtimeeve0_.ev_id = ev_id)
Planning time : 24.416 ms
Execution time : 1,869.304 ms