explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ItO3

Settings
# exclusive inclusive rows x rows loops node
1. 0.348 16,381.146 ↓ 1.3 17 1

GroupAggregate (cost=15,893.97..15,895.49 rows=13 width=589) (actual time=16,380.922..16,381.146 rows=17 loops=1)

  • Group Key: evs.creation_date, evs.ev_id, evs.node_id, evs.incident_id, evs.enhanced_message_id, evs.sender, evs.scr_vpkg_id, evs.dmn_name, evs.status, evs.ev_dmn_id, evs.company_id, evs.company_name, evs.evs_type, evs.voice_msg_id, evs.last_action_user, evs.when_created, evs.conference_id, evs.priority, evs.message_panel_id, evs.termination_date, evs.application_name, evs.presentation_id, evs.system_message_type, evs.pass_response_count, evs.application_id, evs.uuid, evs.request_id, evs.integration_uuid, evs.revision_id, evs.suppressed_ntfns, evs.org_uuid, evs.updated_by_uuid, evs.flow_trace, mp.name, mp.uuid, (COALESCE(evs.system_message_type, msg.message_subject, CASE WHEN ((evs.dmn_name)::text <> 'applications'::text) THEN evs.dmn_name ELSE NULL::character varying END)), r.target_name, r.uuid
2. 0.376 16,380.798 ↓ 1.3 17 1

Sort (cost=15,893.97..15,894.00 rows=13 width=598) (actual time=16,380.796..16,380.798 rows=17 loops=1)

  • Sort Key: evs.creation_date DESC, evs.ev_id DESC, evs.node_id, evs.incident_id, evs.enhanced_message_id, evs.sender, evs.scr_vpkg_id, evs.dmn_name, evs.status, evs.ev_dmn_id, evs.company_id, evs.company_name, evs.evs_type, evs.voice_msg_id, evs.last_action_user, evs.when_created, evs.conference_id, evs.priority, evs.message_panel_id, evs.termination_date, evs.application_name, evs.presentation_id, evs.system_message_type, evs.pass_response_count, evs.application_id, evs.uuid, evs.request_id, evs.integration_uuid, evs.revision_id, evs.suppressed_ntfns, evs.org_uuid, evs.updated_by_uuid, evs.flow_trace, mp.name, mp.uuid, (COALESCE(evs.system_message_type, msg.message_subject, CASE WHEN ((evs.dmn_name)::text <> 'applications'::text) THEN evs.dmn_name ELSE NULL::character varying END)), r.target_name, r.uuid
  • Sort Method: quicksort Memory: 40kB
3. 0.102 16,380.422 ↓ 1.3 17 1

Nested Loop Left Join (cost=15,807.72..15,893.73 rows=13 width=598) (actual time=15,202.559..16,380.422 rows=17 loops=1)

  • Join Filter: ((r.recipient_cat)::text = 'PERSON'::text)
4. 0.090 16,365.785 ↓ 1.3 17 1

Nested Loop Left Join (cost=15,807.44..15,889.43 rows=13 width=588) (actual time=15,188.143..16,365.785 rows=17 loops=1)

  • Join Filter: ((r.recipient_cat)::text = 'DEVICE'::text)
5. 0.250 16,355.070 ↓ 1.3 17 1

Hash Left Join (cost=15,807.16..15,884.93 rows=13 width=581) (actual time=15,177.646..16,355.070 rows=17 loops=1)

  • Hash Cond: (r.org_dvc_name_id = dn.org_dvc_name_id)
  • Join Filter: ((((r.target_name_lower)::text || '|'::text) || lower((dn.name)::text)) = lower("left"((v.value)::text, 200)))
  • Rows Removed by Join Filter: 17
  • Filter: ((r.recipient_id IS NULL) OR ((r.recipient_cat)::text <> 'DEVICE'::text) OR (((r.recipient_cat)::text = 'DEVICE'::text) AND (dn.name IS NOT NULL)))
  • Rows Removed by Filter: 17
6. 0.260 16,354.793 ↓ 2.0 34 1

Nested Loop Left Join (cost=15,805.26..15,882.98 rows=17 width=668) (actual time=15,177.563..16,354.793 rows=34 loops=1)

7. 0.168 16,111.059 ↓ 17.0 17 1

Nested Loop Left Join (cost=15,626.45..15,638.99 rows=1 width=571) (actual time=15,011.173..16,111.059 rows=17 loops=1)

8. 0.146 15,654.611 ↓ 17.0 17 1

Nested Loop Left Join (cost=15,626.02..15,629.35 rows=1 width=509) (actual time=14,994.303..15,654.611 rows=17 loops=1)

9. 0.139 15,040.034 ↓ 17.0 17 1

Nested Loop Left Join (cost=15,625.46..15,628.49 rows=1 width=509) (actual time=14,909.521..15,040.034 rows=17 loops=1)

10. 0.032 14,882.356 ↓ 17.0 17 1

Limit (cost=15,625.17..15,625.18 rows=1 width=501) (actual time=14,882.292..14,882.356 rows=17 loops=1)

11. 0.174 14,882.324 ↓ 17.0 17 1

Sort (cost=15,625.17..15,625.18 rows=1 width=501) (actual time=14,882.289..14,882.324 rows=17 loops=1)

  • Sort Key: evs.creation_date DESC, evs.ev_id DESC
  • Sort Method: quicksort Memory: 33kB
12. 0.348 14,882.150 ↓ 17.0 17 1

Nested Loop Left Join (cost=15,617.31..15,625.16 rows=1 width=501) (actual time=14,208.617..14,882.150 rows=17 loops=1)

  • Join Filter: (mp.message_panel_id = evs.message_panel_id)
  • Rows Removed by Join Filter: 561
13. 0.156 14,862.541 ↓ 17.0 17 1

Nested Loop Left Join (cost=15,617.31..15,620.45 rows=1 width=503) (actual time=14,189.477..14,862.541 rows=17 loops=1)

14. 0.139 14,302.184 ↓ 17.0 17 1

Nested Loop (cost=15,617.02..15,620.08 rows=1 width=410) (actual time=14,135.284..14,302.184 rows=17 loops=1)

15. 0.769 14,120.451 ↓ 17.0 17 1

GroupAggregate (cost=15,616.74..15,616.76 rows=1 width=40) (actual time=14,119.735..14,120.451 rows=17 loops=1)

  • Group Key: c_1.comm_ev_id
  • Filter: ((array_agg(DISTINCT r_1.target_name_lower) || array_agg(DISTINCT r_1.uuid)) @> '{franco.e.canevali,cd2010fb-99f2-45e6-a15f-e6b833e5f4d3}'::character varying[])
16. 0.256 14,119.682 ↓ 34.0 34 1

Sort (cost=15,616.74..15,616.74 rows=1 width=64) (actual time=14,119.651..14,119.682 rows=34 loops=1)

  • Sort Key: c_1.comm_ev_id
  • Sort Method: quicksort Memory: 29kB
17. 0.136 14,119.426 ↓ 34.0 34 1

Nested Loop (cost=1,516.51..15,616.73 rows=1 width=64) (actual time=4,825.025..14,119.426 rows=34 loops=1)

18. 2.628 13,749.132 ↓ 1.4 34 1

Nested Loop (cost=1,516.08..15,587.50 rows=25 width=64) (actual time=4,796.771..13,749.132 rows=34 loops=1)

19. 1.016 4,693.152 ↑ 5.4 994 1

Nested Loop (cost=1,515.65..13,069.94 rows=5,394 width=64) (actual time=170.707..4,693.152 rows=994 loops=1)

20. 0.014 58.046 ↑ 1.5 2 1

Nested Loop Left Join (cost=0.00..361.19 rows=3 width=66) (actual time=0.175..58.046 rows=2 loops=1)

  • Join Filter: (dn_1.org_dvc_name_id = r_1.org_dvc_name_id)
  • Rows Removed by Join Filter: 45
21. 58.002 58.002 ↑ 1.5 2 1

Seq Scan on recipients r_1 (cost=0.00..357.89 rows=3 width=64) (actual time=0.139..58.002 rows=2 loops=1)

  • Filter: (((target_name_lower)::text = 'franco.e.canevali'::text) OR ((uuid)::text = 'cd2010fb-99f2-45e6-a15f-e6b833e5f4d3'::text))
  • Rows Removed by Filter: 7117
22. 0.018 0.030 ↑ 1.7 23 2

Materialize (cost=0.00..1.60 rows=40 width=18) (actual time=0.007..0.015 rows=23 loops=2)

23. 0.012 0.012 ↑ 1.0 40 1

Seq Scan on org_dvc_names dn_1 (cost=0.00..1.40 rows=40 width=18) (actual time=0.007..0.012 rows=40 loops=1)

24. 4,463.518 4,634.090 ↑ 3.7 497 2

Bitmap Heap Scan on comm_values v_1 (cost=1,515.65..4,217.68 rows=1,857 width=78) (actual time=85.360..2,317.045 rows=497 loops=2)

  • Recheck Cond: ((lower("left"((value)::text, 200)) = (r_1.target_name_lower)::text) OR (lower("left"((value)::text, 200)) = (((r_1.target_name_lower)::text || '|'::text) || lower((dn_1.name)::text))))
  • Heap Blocks: exact=836
25. 0.044 170.572 ↓ 0.0 0 2

BitmapOr (cost=1,515.65..1,515.65 rows=1,857 width=0) (actual time=85.286..85.286 rows=0 loops=2)

26. 170.512 170.512 ↑ 1.7 537 2

Bitmap Index Scan on idx_comm_values0 (cost=0.00..31.65 rows=929 width=0) (actual time=85.256..85.256 rows=537 loops=2)

  • Index Cond: (lower("left"((value)::text, 200)) = (r_1.target_name_lower)::text)
27. 0.016 0.016 ↓ 0.0 0 2

Bitmap Index Scan on idx_comm_values0 (cost=0.00..31.66 rows=929 width=0) (actual time=0.008..0.008 rows=0 loops=2)

  • Index Cond: (lower("left"((value)::text, 200)) = (((r_1.target_name_lower)::text || '|'::text) || lower((dn_1.name)::text)))
28. 9,053.352 9,053.352 ↓ 0.0 0 994

Index Scan using comm_flds_pk on comm_flds f_1 (cost=0.43..0.47 rows=1 width=16) (actual time=9.108..9.108 rows=0 loops=994)

  • Index Cond: (comm_fld_id = v_1.comm_fld_id)
  • Filter: ((name)::text = 'recipients'::text)
  • Rows Removed by Filter: 1
29. 370.158 370.158 ↑ 1.0 1 34

Index Scan using comm_pk on comm c_1 (cost=0.42..1.17 rows=1 width=16) (actual time=10.887..10.887 rows=1 loops=34)

  • Index Cond: (comm_id = f_1.comm_id)
  • Filter: ((comm_type)::text = 'EVENT'::text)
30. 181.594 181.594 ↑ 1.0 1 17

Index Scan using evs_pk on evs (cost=0.29..3.31 rows=1 width=410) (actual time=10.682..10.682 rows=1 loops=17)

  • Index Cond: (ev_id = c_1.comm_ev_id)
  • Filter: (company_id = 1)
31. 560.201 560.201 ↑ 1.0 1 17

Index Scan using evs_email_message_ev_id_uc on evs_email_message msg (cost=0.29..0.37 rows=1 width=101) (actual time=32.953..32.953 rows=1 loops=17)

  • Index Cond: (ev_id = evs.ev_id)
32. 19.261 19.261 ↑ 2.2 34 17

Seq Scan on message_panel mp (cost=0.00..3.76 rows=76 width=66) (actual time=1.131..1.133 rows=34 loops=17)

33. 157.539 157.539 ↑ 1.0 1 17

Index Scan using comm_comm_ev_id_uidx on comm c (cost=0.29..3.31 rows=1 width=16) (actual time=9.267..9.267 rows=1 loops=17)

  • Index Cond: (comm_ev_id = evs.ev_id)
34. 614.431 614.431 ↑ 1.0 1 17

Index Scan using idx_comm_fld_name on comm_flds f (cost=0.56..0.84 rows=1 width=16) (actual time=35.124..36.143 rows=1 loops=17)

  • Index Cond: ((c.comm_id = comm_id) AND ((name)::text = 'recipients'::text))
35. 456.280 456.280 ↑ 8.0 1 17

Index Scan using idx_comm_values1 on comm_values v (cost=0.43..9.56 rows=8 width=78) (actual time=26.838..26.840 rows=1 loops=17)

  • Index Cond: (comm_fld_id = f.comm_fld_id)
36. 66.130 243.474 ↑ 9.0 2 17

Bitmap Heap Scan on recipients r (cost=178.81..243.81 rows=18 width=97) (actual time=10.543..14.322 rows=2 loops=17)

  • Recheck Cond: (((v.value)::text = (target_name)::text) OR ((recipient_cat)::text = 'DEVICE'::text))
  • Filter: (((v.value)::text = (target_name)::text) OR (((v.value)::text ~~* ((target_name)::text || '|%'::text)) AND ((recipient_cat)::text = 'DEVICE'::text)))
  • Rows Removed by Filter: 3167
  • Heap Blocks: exact=4199
37. 0.068 177.344 ↓ 0.0 0 17

BitmapOr (cost=178.81..178.81 rows=3,175 width=0) (actual time=10.432..10.432 rows=0 loops=17)

38. 0.425 0.425 ↑ 1.0 2 17

Bitmap Index Scan on idx_recipients_stn (cost=0.00..0.30 rows=2 width=0) (actual time=0.025..0.025 rows=2 loops=17)

  • Index Cond: ((v.value)::text = (target_name)::text)
39. 176.851 176.851 ↑ 1.0 3,168 17

Bitmap Index Scan on idx_recipients_stnl (cost=0.00..176.73 rows=3,172 width=0) (actual time=10.403..10.403 rows=3,168 loops=17)

  • Index Cond: ((recipient_cat)::text = 'DEVICE'::text)
40. 0.011 0.027 ↑ 1.0 40 1

Hash (cost=1.40..1.40 rows=40 width=18) (actual time=0.027..0.027 rows=40 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
41. 0.016 0.016 ↑ 1.0 40 1

Seq Scan on org_dvc_names dn (cost=0.00..1.40 rows=40 width=18) (actual time=0.011..0.016 rows=40 loops=1)

42. 10.625 10.625 ↓ 0.0 0 17

Index Scan using dvc_pk on dvc d (cost=0.28..0.33 rows=1 width=15) (actual time=0.625..0.625 rows=0 loops=17)

  • Index Cond: (dvc_id = r.recipient_id)
43. 14.535 14.535 ↑ 1.0 1 17

Index Scan using persons_pk on persons p (cost=0.28..0.32 rows=1 width=26) (actual time=0.855..0.855 rows=1 loops=17)

  • Index Cond: (person_id = r.recipient_id)
Planning time : 2,384.035 ms
Execution time : 16,382.449 ms