explain.depesz.com

PostgreSQL's explain analyze made readable

Result: m7R0

Settings
# exclusive inclusive rows x rows loops node
1. 0.024 51,882.991 ↓ 100.0 100 1

Limit (cost=389,850.51..389,850.51 rows=1 width=2,455) (actual time=51,882.948..51,882.991 rows=100 loops=1)

2. 8.467 51,882.967 ↓ 100.0 100 1

Sort (cost=389,850.51..389,850.51 rows=1 width=2,455) (actual time=51,882.946..51,882.967 rows=100 loops=1)

  • Sort Key: runtimeeve0_.creation_date DESC
  • Sort Method: top-N heapsort Memory: 225kB
3. 13,612.976 51,874.500 ↓ 1,846.0 1,846 1

Nested Loop Semi Join (cost=218,677.59..389,850.50 rows=1 width=2,455) (actual time=19,485.925..51,874.500 rows=1,846 loops=1)

  • Join Filter: (runtimeeve0_.ev_id = c.comm_ev_id)
  • Rows Removed by Join Filter: 196607307
4. 435.753 14,519.936 ↑ 1.2 107,428 1

Hash Right Join (cost=218,675.59..387,815.27 rows=130,047 width=2,455) (actual time=5,907.080..14,519.936 rows=107,428 loops=1)

  • Hash Cond: (runtimeeve1_.ev_id = runtimeeve0_.ev_id)
5. 8,195.768 8,195.768 ↓ 1.0 342,737 1

Seq Scan on ev_metadata runtimeeve1_ (cost=0.00..167,010.54 rows=328,754 width=1,978) (actual time=17.902..8,195.768 rows=342,737 loops=1)

6. 243.032 5,888.415 ↑ 1.2 107,428 1

Hash (cost=217,050.00..217,050.00 rows=130,047 width=477) (actual time=5,888.415..5,888.415 rows=107,428 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 49661kB
7. 265.263 5,645.383 ↑ 1.2 107,428 1

Hash Right Join (cost=132,936.87..217,050.00 rows=130,047 width=477) (actual time=1,592.604..5,645.383 rows=107,428 loops=1)

  • Hash Cond: (runtimeeve2_.ev_id = runtimeeve0_.ev_id)
8. 3,795.762 3,795.762 ↑ 1.3 342,540 1

Seq Scan on evs_email_message runtimeeve2_ (cost=0.00..81,123.74 rows=461,574 width=102) (actual time=7.534..3,795.762 rows=342,540 loops=1)

9. 275.446 1,584.358 ↑ 1.2 107,428 1

Hash (cost=131,311.28..131,311.28 rows=130,047 width=375) (actual time=1,584.358..1,584.358 rows=107,428 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 38734kB
10. 109.529 1,308.912 ↑ 1.2 107,428 1

Merge Right Join (cost=125,077.03..131,311.28 rows=130,047 width=375) (actual time=879.325..1,308.912 rows=107,428 loops=1)

  • Merge Cond: (runtimeeve3_.comm_ev_id = runtimeeve0_.ev_id)
11. 295.105 295.105 ↓ 1.0 342,729 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.321..295.105 rows=342,729 loops=1)

12. 282.771 904.278 ↑ 1.2 107,428 1

Sort (cost=125,076.61..125,401.73 rows=130,047 width=349) (actual time=876.179..904.278 rows=107,428 loops=1)

  • Sort Key: runtimeeve0_.ev_id
  • Sort Method: quicksort Memory: 57295kB
13. 65.922 621.507 ↑ 1.2 107,428 1

Hash Left Join (cost=51.51..114,029.98 rows=130,047 width=349) (actual time=0.676..621.507 rows=107,428 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: 402
14. 555.038 555.038 ↑ 1.4 107,830 1

Index Scan using evs_ev_dmn_id_pidx on evs runtimeeve0_ (cost=0.42..113,591.22 rows=145,862 width=349) (actual time=0.090..555.038 rows=107,830 loops=1)

  • 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: 232076
15. 0.089 0.547 ↑ 1.0 404 1

Hash (cost=46.04..46.04 rows=404 width=12) (actual time=0.547..0.547 rows=404 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
16. 0.458 0.458 ↑ 1.0 404 1

Seq Scan on application applicatio4_ (cost=0.00..46.04 rows=404 width=12) (actual time=0.027..0.458 rows=404 loops=1)

17. 10,165.241 23,741.588 ↓ 1,830.0 1,830 107,428

Materialize (cost=2.00..84.52 rows=1 width=8) (actual time=0.126..0.221 rows=1,830 loops=107,428)

18. 1.524 13,576.347 ↓ 1,846.0 1,846 1

Nested Loop (cost=2.00..84.52 rows=1 width=8) (actual time=13,539.423..13,576.347 rows=1,846 loops=1)

19. 10.459 13,563.747 ↓ 1,846.0 1,846 1

Nested Loop (cost=1.58..83.92 rows=1 width=8) (actual time=13,539.384..13,563.747 rows=1,846 loops=1)

20. 3.001 13,517.810 ↓ 96.1 3,942 1

Nested Loop (cost=1.16..65.22 rows=41 width=8) (actual time=157.985..13,517.810 rows=3,942 loops=1)

21. 0.031 157.555 ↓ 6.0 6 1

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

  • Group Key: lower((retrieve_person_linked_recipients.target_name)::text)
22. 157.524 157.524 ↓ 25.0 25 1

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

  • Filter: ((org_id = 1) AND (((recipient_cat)::text <> 'GROUP'::text) OR ((grp_association)::text <> 'supervisor'::text)))
23. 13,357.254 13,357.254 ↓ 16.0 657 6

Index Scan using idx_comm_values0 on comm_values cv (cost=0.69..64.32 rows=41 width=128) (actual time=15.256..2,226.209 rows=657 loops=6)

  • Index Cond: (lower(""left""((value)::text, 200)) = (lower((retrieve_person_linked_recipients.target_name)::text)))
24. 35.478 35.478 ↓ 0.0 0 3,942

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.009..0.009 rows=0 loops=3,942)

  • Index Cond: (comm_fld_id = cv.comm_fld_id)
25. 11.076 11.076 ↑ 1.0 1 1,846

Index Scan using comm_pk on comm c (cost=0.43..0.59 rows=1 width=16) (actual time=0.006..0.006 rows=1 loops=1,846)

  • Index Cond: (comm_id = cf.comm_id)
  • Filter: ((comm_ev_id IS NOT NULL) AND ((comm_type)::text = 'EVENT'::text))
Planning time : 18.058 ms
Execution time : 51,894.673 ms