explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4gtuk

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Sort (cost=869.07..869.07 rows=1 width=212) (actual rows= loops=)

  • Sort Key: (COALESCE(d.pin, (((((('+'::text || (c.dialing_code)::text) || ' '::text) || (v.area_code)::text) || ' '::text) || (v.phone_num)::text))::character varying))
2. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=855.85..869.06 rows=1 width=212) (actual rows= loops=)

3. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=855.72..866.17 rows=1 width=157) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=855.57..864.68 rows=1 width=157) (actual rows= loops=)

  • Join Filter: (n.dvc_id = v.dvc_id)
5. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=855.57..863.50 rows=1 width=142) (actual rows= loops=)

  • Join Filter: (n.dvc_id = d.dvc_id)
6. 0.000 0.000 ↓ 0.0

Nested Loop (cost=855.57..862.46 rows=1 width=132) (actual rows= loops=)

  • Join Filter: (tdvc.dvc_id = dvc.dvc_id)
7. 0.000 0.000 ↓ 0.0

Nested Loop (cost=855.44..862.18 rows=1 width=142) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Nested Loop (cost=855.30..861.85 rows=1 width=141) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Nested Loop (cost=855.16..861.27 rows=1 width=77) (actual rows= loops=)

  • Join Filter: (n.dvc_id = tdvc.dvc_id)
10. 0.000 0.000 ↓ 0.0

Index Scan using idx_dvc2 on dvc tdvc (cost=0.14..4.92 rows=15 width=16) (actual rows= loops=)

  • Filter: ((status)::text = 'ACTIVE'::text)
11. 0.000 0.000 ↓ 0.0

Materialize (cost=855.02..856.13 rows=1 width=61) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash Join (cost=855.02..856.13 rows=1 width=61) (actual rows= loops=)

  • Hash Cond: (orgs.org_id = a.org_id)
13. 0.000 0.000 ↓ 0.0

Seq Scan on orgs (cost=0.00..1.07 rows=7 width=37) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Hash (cost=855.01..855.01 rows=1 width=32) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=854.93..855.00 rows=1 width=32) (actual rows= loops=)

  • Group Key: a.org_id, n.dvc_id
  • Filter: ((sum(COALESCE(CASE WHEN ((array_agg(a.ap_audit_ev_type)) && '{CNS_FAILED,LIVE_NOTIFICATION_DELIVERY_ERROR,DEVICE_NOTIFICATION_PROVIDER_EXHAUSTED,LIVE_NOTIFICATION_EXCEEDED_MAX_RETRIES}'::character varying[]) THEN 1 ELSE NULL::integer END, 0)) >= '100'::bigint) AND (sum(COALESCE(CASE WHEN ((array_agg(a.ap_audit_ev_type)) && '{CNS_FAILED,LIVE_NOTIFICATION_DELIVERY_ERROR,DEVICE_NOTIFICATION_PROVIDER_EXHAUSTED,LIVE_NOTIFICATION_EXCEEDED_MAX_RETRIES}'::character varying[]) THEN 0 WHEN ((array_agg(a.ap_audit_ev_type)) && '{LIVE_NOTIFICATION_REPLY_RECEIVED,LIVE_NOTIFICATION_PROVIDER_DELIVERED,LIVE_NOTIFICATION_CNS_PROVIDER_DELIVERED}'::character varying[]) THEN 1 ELSE NULL::integer END, 0)) = 0))
16. 0.000 0.000 ↓ 0.0

Unique (cost=854.93..854.94 rows=1 width=56) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Sort (cost=854.93..854.93 rows=1 width=56) (actual rows= loops=)

  • Sort Key: a.org_id, n.dvc_id, a.ev_id, (array_agg(a.ap_audit_ev_type))
18. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=854.89..854.92 rows=1 width=56) (actual rows= loops=)

  • Group Key: a.org_id, n.dvc_id, a.ev_id
19. 0.000 0.000 ↓ 0.0

Sort (cost=854.89..854.90 rows=1 width=49) (actual rows= loops=)

  • Sort Key: a.org_id, n.dvc_id, a.ev_id
20. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.28..854.88 rows=1 width=49) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on audit_evs_all a (cost=0.00..851.58 rows=1 width=49) (actual rows= loops=)

  • Filter: (when_created >= (date_trunc('day'::text, now()) - '7 days'::interval))
22. 0.000 0.000 ↓ 0.0

Index Scan using idx_dispatch on live_ntfn n (cost=0.28..3.29 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (ntfn_id = a.ntfn_id)
23. 0.000 0.000 ↓ 0.0

Index Scan using recipients_rid_when_updated_desc_pidx on recipients r (cost=0.14..0.56 rows=1 width=64) (actual rows= loops=)

  • Index Cond: (recipient_id = tdvc.dvc_id)
24. 0.000 0.000 ↓ 0.0

Index Scan using org_dvc_names_pk on org_dvc_names dn (cost=0.14..0.33 rows=1 width=17) (actual rows= loops=)

  • Index Cond: (org_dvc_name_id = r.org_dvc_name_id)
25. 0.000 0.000 ↓ 0.0

Index Scan using dvc_dvc_id_status_idx on dvc (cost=0.14..0.26 rows=1 width=14) (actual rows= loops=)

  • Index Cond: (dvc_id = r.recipient_id)
  • Filter: ((dvc_type)::text = ANY ('{TEXT_PHONE,VOICE}'::text[]))
26. 0.000 0.000 ↓ 0.0

Seq Scan on txt_phone_dvc_dtl d (cost=0.00..1.02 rows=2 width=18) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Seq Scan on voc_dvc_dtl v (cost=0.00..1.08 rows=8 width=23) (actual rows= loops=)

28. 0.000 0.000 ↓ 0.0

Index Scan using country_code_uc on countries c (cost=0.14..1.48 rows=1 width=6) (actual rows= loops=)

  • Index Cond: ((country_code)::text = (v.country_code)::text)
29. 0.000 0.000 ↓ 0.0

Index Scan using persons_person_id_upper_first_name_upper_last_name_fbi on persons owner (cost=0.14..0.45 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (person_id = tdvc.person_id)
30.          

SubPlan (for Nested Loop Left Join)

31. 0.000 0.000 ↓ 0.0

Aggregate (cost=2.37..2.38 rows=1 width=32) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

Hash Join (cost=1.30..2.37 rows=1 width=22) (actual rows= loops=)

  • Hash Cond: (e.dvc_id = edvc.dvc_id)
33. 0.000 0.000 ↓ 0.0

Seq Scan on email_dvc_dtl e (cost=0.00..1.05 rows=5 width=30) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Hash (cost=1.28..1.28 rows=2 width=8) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Seq Scan on dvc edvc (cost=0.00..1.28 rows=2 width=8) (actual rows= loops=)

  • Filter: ((person_id = tdvc.person_id) AND (deleted_id = '-1'::integer) AND ((status)::text = 'ACTIVE'::text))