explain.depesz.com

PostgreSQL's explain analyze made readable

Result: zTDB

Settings
# exclusive inclusive rows x rows loops node
1. 0.014 10,920.365 ↑ 1.0 1 1

Aggregate (cost=341,916.06..341,916.07 rows=1 width=8) (actual time=10,920.365..10,920.365 rows=1 loops=1)

2.          

CTE get_targeted_event_ids

3. 0.501 66.666 ↓ 1,672.0 1,672 1

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

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

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

5. 0.411 35.281 ↓ 78.5 3,217 1

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

6. 0.017 21.250 ↓ 6.0 6 1

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

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

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

  • Filter: ((org_id = 1) AND (((recipient_cat)::text <> 'GROUP'::text) OR ((grp_association)::text <> 'supervisor'::text)))
8. 13.620 13.620 ↓ 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.039..2.270 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. 106.700 10,920.351 ↑ 5,523.6 8 1

Hash Left Join (cost=234,837.28..341,721.10 rows=44,189 width=8) (actual time=10,847.707..10,920.351 rows=8 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. 0.000 10,813.127 ↑ 9,296.9 8 1

Bitmap Heap Scan on evs runtimeeve0_ (cost=120,967.97..227,652.82 rows=74,375 width=30) (actual time=10,740.526..10,813.127 rows=8 loops=1)

  • Recheck 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: 146835
  • Heap Blocks: exact=19510
13. 10,449.832 10,449.832 ↑ 1.7 148,190 1

Bitmap Index Scan on idx_evs_status (cost=0.00..7,131.25 rows=254,548 width=0) (actual time=10,449.832..10,449.832 rows=148,190 loops=1)

  • Index Cond: ((status)::text = ANY ('{SUSPENDED,PURGED,ACTIVE,SUPPRESSED,CREATE}'::text[]))
14.          

SubPlan (for Bitmap Heap Scan)

15. 280.290 308.868 ↓ 0.0 0 2

Bitmap Heap Scan on evs runtimeeve2_ (cost=7,131.43..113,816.28 rows=731 width=8) (actual time=154.434..154.434 rows=0 loops=2)

  • Recheck 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) = 'hle'::text))
  • Rows Removed by Filter: 146843
  • Heap Blocks: exact=39020
16. 28.578 28.578 ↑ 1.7 148,190 2

Bitmap Index Scan on idx_evs_status (cost=0.00..7,131.25 rows=254,548 width=0) (actual time=14.289..14.289 rows=148,190 loops=2)

  • Index Cond: ((status)::text = ANY ('{SUSPENDED,PURGED,ACTIVE,SUPPRESSED,CREATE}'::text[]))
17. 67.290 67.290 ↓ 1,672.0 1,672 2

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

18. 0.074 0.524 ↑ 1.0 408 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
19. 0.450 0.450 ↑ 1.0 408 1

Seq Scan on application applicatio1_ (cost=0.00..46.08 rows=408 width=12) (actual time=0.018..0.450 rows=408 loops=1)

Planning time : 14.443 ms
Execution time : 10,921.470 ms