explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wBLf

Settings
# exclusive inclusive rows x rows loops node
1. 4.578 3,791.056 ↓ 1,848.0 1,848 1

Sort (cost=219,039.78..219,039.78 rows=1 width=2,456) (actual time=3,790.758..3,791.056 rows=1,848 loops=1)

  • Sort Key: runtimeeve0_.creation_date DESC
  • Sort Method: quicksort Memory: 1925kB
2. 84.014 3,786.478 ↓ 1,848.0 1,848 1

Hash Join (cost=141,902.91..219,039.77 rows=1 width=2,456) (actual time=2,145.871..3,786.478 rows=1,848 loops=1)

  • Hash Cond: (lower(""left""((cv.value)::text, 200)) = (lower((retrieve_person_linked_recipients.target_name)::text)))
3. 77.831 3,682.373 ↓ 5.2 108,691 1

Nested Loop Left Join (cost=141,902.41..218,979.43 rows=20,811 width=2,576) (actual time=2,121.615..3,682.373 rows=108,691 loops=1)

4. 22.837 3,387.160 ↓ 7.0 108,691 1

Nested Loop (cost=141,901.99..197,104.55 rows=15,564 width=2,474) (actual time=2,121.589..3,387.160 rows=108,691 loops=1)

5. 95.412 2,785.535 ↓ 6.0 82,684 1

Nested Loop Left Join (cost=141,901.55..170,264.18 rows=13,788 width=2,362) (actual time=2,121.556..2,785.535 rows=82,684 loops=1)

6. 98.709 2,442.071 ↓ 6.0 82,684 1

Merge Right Join (cost=141,901.13..147,467.05 rows=13,788 width=384) (actual time=2,121.511..2,442.071 rows=82,684 loops=1)

  • Merge Cond: (runtimeeve3_.comm_ev_id = runtimeeve0_.ev_id)
7. 208.111 208.111 ↓ 1.1 345,144 1

Index Scan using comm_comm_ev_id_pidx on comm runtimeeve3_ (cost=0.42..32,331.77 rows=327,944 width=26) (actual time=0.441..208.111 rows=345,144 loops=1)

8. 97.235 2,135.251 ↓ 6.0 82,684 1

Sort (cost=141,900.70..141,935.17 rows=13,788 width=358) (actual time=2,119.589..2,135.251 rows=82,684 loops=1)

  • Sort Key: runtimeeve0_.ev_id
  • Sort Method: quicksort Memory: 45602kB
9. 45.500 2,038.016 ↓ 6.0 82,684 1

Hash Left Join (cost=35,082.21..140,952.70 rows=13,788 width=358) (actual time=304.028..2,038.016 rows=82,684 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))
  • Rows Removed by Filter: 398
10. 1.564 1,992.117 ↓ 5.4 83,082 1

Nested Loop (cost=35,031.07..140,860.49 rows=15,455 width=358) (actual time=303.558..1,992.117 rows=83,082 loops=1)

11. 211.299 1,033.625 ↓ 6.4 318,976 1

Hash Join (cost=35,030.65..93,545.96 rows=49,839 width=16) (actual time=297.483..1,033.625 rows=318,976 loops=1)

  • Hash Cond: (cf.comm_id = commx.comm_id)
12. 527.565 527.565 ↓ 1.1 318,976 1

Index Scan using comm_flds_fld_id_persons_pidx on comm_flds cf (cost=0.42..57,724.28 rows=301,504 width=16) (actual time=0.029..527.565 rows=318,976 loops=1)

13. 94.585 294.761 ↓ 1.1 345,151 1

Hash (cost=30,930.93..30,930.93 rows=327,944 width=16) (actual time=294.761..294.761 rows=345,151 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 20275kB
14. 173.925 200.176 ↓ 1.1 345,151 1

Bitmap Heap Scan on comm commx (cost=4,648.63..30,930.93 rows=327,944 width=16) (actual time=29.101..200.176 rows=345,151 loops=1)

  • Recheck Cond: ((comm_type)::text = 'EVENT'::text)
  • Heap Blocks: exact=18242
15. 26.251 26.251 ↓ 1.1 354,723 1

Bitmap Index Scan on comm_comm_ev_id_pidx (cost=0.00..4,566.64 rows=327,944 width=0) (actual time=26.251..26.251 rows=354,723 loops=1)

16. 956.928 956.928 ↓ 0.0 0 318,976

Index Scan using evs_pk on evs runtimeeve0_ (cost=0.42..0.95 rows=1 width=350) (actual time=0.003..0.003 rows=0 loops=318,976)

  • Index Cond: (ev_id = commx.comm_ev_id)
  • Filter: ((dmn_name IS NOT NULL) AND (system_message_type IS NULL) AND ((status)::text <> 'SUPPRESSED'::text) AND ((dmn_name)::text <> 'devicevalidation'::text) AND ((dmn_name)::text <> 'onboarding_notification'::text) AND ((dmn_name)::text <> 'voicerecordings'::text) AND ((dmn_name)::text <> 'generic_responses'::text) AND ((dmn_name)::text <> 'sms_confirmation'::text) AND ((dmn_name)::text <> 'sms_deactivation'::text) AND (company_id = 1) AND ((status)::text = ANY ('{TERMINATED_EXT_FAILFILL,SUPPRESSED,PURGED,SUSPENDED,ACTIVE,TERMINATED_EXTERNAL,TERMINATED_WITH_ERR,CREATE,TERMINATED_PASSFILL,TERMINATED_EXT_PASSFILL,TERMINATED_FAILFILL,TERMINATED_BY_USER,TERMINATED}'::text[])))
  • Rows Removed by Filter: 1
17. 0.095 0.399 ↑ 1.0 406 1

Hash (cost=46.06..46.06 rows=406 width=12) (actual time=0.399..0.399 rows=406 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
18. 0.304 0.304 ↑ 1.0 406 1

Seq Scan on application applicatio4_ (cost=0.00..46.06 rows=406 width=12) (actual time=0.016..0.304 rows=406 loops=1)

19. 248.052 248.052 ↑ 1.0 1 82,684

Index Scan using ev_metadata_uc_ev_id on ev_metadata runtimeeve1_ (cost=0.42..1.65 rows=1 width=1,978) (actual time=0.003..0.003 rows=1 loops=82,684)

  • Index Cond: (runtimeeve0_.ev_id = ev_id)
20. 578.788 578.788 ↑ 1.0 1 82,684

Index Scan using idx_comm_values1 on comm_values cv (cost=0.44..1.94 rows=1 width=128) (actual time=0.006..0.007 rows=1 loops=82,684)

  • Index Cond: (comm_fld_id = cf.comm_fld_id)
21. 217.382 217.382 ↑ 1.0 1 108,691

Index Scan using evs_email_message_ev_id_uc on evs_email_message runtimeeve2_ (cost=0.42..1.41 rows=1 width=102) (actual time=0.002..0.002 rows=1 loops=108,691)

  • Index Cond: (runtimeeve0_.ev_id = ev_id)
22. 0.005 20.091 ↓ 6.0 6 1

Hash (cost=0.49..0.49 rows=1 width=32) (actual time=20.091..20.091 rows=6 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.014 20.086 ↓ 6.0 6 1

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

  • Group Key: lower((retrieve_person_linked_recipients.target_name)::text)
24. 20.072 20.072 ↓ 25.0 25 1

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

  • Filter: ((org_id = 1) AND (((recipient_cat)::text <> 'GROUP'::text) OR ((grp_association)::text <> 'supervisor'::text)))
Planning time : 17.568 ms
Execution time : 3,800.014 ms