explain.depesz.com

PostgreSQL's explain analyze made readable

Result: wmME

Settings
# exclusive inclusive rows x rows loops node
1. 10.206 2,716.912 ↓ 1,846.0 1,846 1

Sort (cost=388,241.19..388,241.19 rows=1 width=2,455) (actual time=2,716.546..2,716.912 rows=1,846 loops=1)

  • Sort Key: runtimeeve0_.creation_date DESC
  • Sort Method: quicksort Memory: 1923kB
2. 18.105 2,706.706 ↓ 1,846.0 1,846 1

Hash Semi Join (cost=218,760.12..388,241.18 rows=1 width=2,455) (actual time=1,846.790..2,706.706 rows=1,846 loops=1)

  • Hash Cond: (runtimeeve0_.ev_id = c.comm_ev_id)
3. 323.246 2,609.281 ↑ 1.2 107,377 1

Hash Right Join (cost=218,675.59..387,815.27 rows=130,047 width=2,455) (actual time=1,767.394..2,609.281 rows=107,377 loops=1)

  • Hash Cond: (runtimeeve1_.ev_id = runtimeeve0_.ev_id)
4. 519.150 519.150 ↓ 1.0 342,658 1

Seq Scan on ev_metadata runtimeeve1_ (cost=0.00..167,010.54 rows=328,754 width=1,978) (actual time=0.028..519.150 rows=342,658 loops=1)

5. 107.281 1,766.885 ↑ 1.2 107,377 1

Hash (cost=217,050.00..217,050.00 rows=130,047 width=477) (actual time=1,766.885..1,766.885 rows=107,377 loops=1)

  • Buckets: 131072 Batches: 1 Memory Usage: 49639kB
6. 226.716 1,659.604 ↑ 1.2 107,377 1

Hash Right Join (cost=132,936.87..217,050.00 rows=130,047 width=477) (actual time=1,178.465..1,659.604 rows=107,377 loops=1)

  • Hash Cond: (runtimeeve2_.ev_id = runtimeeve0_.ev_id)
7. 255.170 255.170 ↑ 1.3 342,461 1

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

8. 91.791 1,177.718 ↑ 1.2 107,377 1

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

  • Buckets: 131072 Batches: 1 Memory Usage: 38716kB
9. 107.455 1,085.927 ↑ 1.2 107,377 1

Merge Right Join (cost=125,077.03..131,311.28 rows=130,047 width=375) (actual time=696.400..1,085.927 rows=107,377 loops=1)

  • Merge Cond: (runtimeeve3_.comm_ev_id = runtimeeve0_.ev_id)
10. 259.516 259.516 ↓ 1.0 342,651 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.414..259.516 rows=342,651 loops=1)

11. 124.298 718.956 ↑ 1.2 107,377 1

Sort (cost=125,076.61..125,401.73 rows=130,047 width=349) (actual time=693.439..718.956 rows=107,377 loops=1)

  • Sort Key: runtimeeve0_.ev_id
  • Sort Method: quicksort Memory: 57269kB
12. 64.166 594.658 ↑ 1.2 107,377 1

Hash Left Join (cost=51.51..114,029.98 rows=130,047 width=349) (actual time=0.577..594.658 rows=107,377 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: 401
13. 530.030 530.030 ↑ 1.4 107,778 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.060..530.030 rows=107,778 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: 232053
14. 0.081 0.462 ↑ 1.0 404 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 26kB
15. 0.381 0.381 ↑ 1.0 404 1

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

16. 0.379 79.320 ↓ 1,846.0 1,846 1

Hash (cost=84.52..84.52 rows=1 width=8) (actual time=79.320..79.320 rows=1,846 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 89kB
17. 1.214 78.941 ↓ 1,846.0 1,846 1

Nested Loop (cost=2.00..84.52 rows=1 width=8) (actual time=36.182..78.941 rows=1,846 loops=1)

18. 0.000 61.113 ↓ 1,846.0 1,846 1

Nested Loop (cost=1.58..83.92 rows=1 width=8) (actual time=36.147..61.113 rows=1,846 loops=1)

19. 0.496 42.026 ↓ 96.1 3,942 1

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

20. 0.018 20.662 ↓ 6.0 6 1

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

  • Group Key: lower((retrieve_person_linked_recipients.target_name)::text)
21. 20.644 20.644 ↓ 25.0 25 1

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

  • Filter: ((org_id = 1) AND (((recipient_cat)::text <> 'GROUP'::text) OR ((grp_association)::text <> 'supervisor'::text)))
22. 20.868 20.868 ↓ 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=0.041..3.478 rows=657 loops=6)

  • Index Cond: (lower(""left""((value)::text, 200)) = (lower((retrieve_person_linked_recipients.target_name)::text)))
23. 19.710 19.710 ↓ 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.005..0.005 rows=0 loops=3,942)

  • Index Cond: (comm_fld_id = cv.comm_fld_id)
24. 16.614 16.614 ↑ 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.009..0.009 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 : 16.417 ms
Execution time : 2,728.135 ms