explain.depesz.com

PostgreSQL's explain analyze made readable

Result: lca4 : Optimization for: plan #kX4l

Settings

Optimization path:

Optimization(s) for this plan:

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

Sort (cost=2,138,774.86..2,138,774.87 rows=5 width=65) (actual rows= loops=)

  • Sort Key: w.userid, w.username
2.          

CTE cte_org

3. 0.000 0.000 ↓ 0.0

Result (cost=0.00..0.01 rows=1 width=40) (actual rows= loops=)

4.          

CTE ev_ids

5. 0.000 0.000 ↓ 0.0

Append (cost=103.13..12,599.89 rows=10,062 width=8) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on evs evs_1 (cost=103.13..6,062.44 rows=4,927 width=8) (actual rows= loops=)

  • Recheck Cond: (company_id = $1)
7.          

Initplan (for Bitmap Heap Scan)

8. 0.000 0.000 ↓ 0.0

CTE Scan on cte_org (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on evs_company_id_idx (cost=0.00..101.88 rows=4,927 width=0) (actual rows= loops=)

  • Index Cond: (company_id = $1)
10. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on evs_arc evs_arc_1 (cost=62.74..6,386.53 rows=5,135 width=8) (actual rows= loops=)

  • Recheck Cond: (company_id = $2)
11.          

Initplan (for Bitmap Heap Scan)

12. 0.000 0.000 ↓ 0.0

CTE Scan on cte_org cte_org_1 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

13. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on evs_arc_company_id_idx (cost=0.00..61.44 rows=5,135 width=0) (actual rows= loops=)

  • Index Cond: (company_id = $2)
14. 0.000 0.000 ↓ 0.0

HashAggregate (cost=2,126,171.69..2,126,174.84 rows=5 width=65) (actual rows= loops=)

  • Group Key: w.userid, w.username
  • Filter: ((max(w.last_login) IS NULL) OR (max("*SELECT* 1_1".max_date) IS NULL) OR (max(("*SELECT* 1".creation_date)::date) IS NULL))
15. 0.000 0.000 ↓ 0.0

Merge Right Join (cost=1,623,720.73..1,801,104.67 rows=11,820,619 width=69) (actual rows= loops=)

  • Merge Cond: ("*SELECT* 1_1".person_id = w.person_id)
16. 0.000 0.000 ↓ 0.0

Sort (cost=1,571,735.16..1,571,772.49 rows=14,932 width=12) (actual rows= loops=)

  • Sort Key: "*SELECT* 1_1".person_id
17. 0.000 0.000 ↓ 0.0

Append (cost=152,607.83..1,570,699.91 rows=14,932 width=12) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1_1 (cost=152,607.83..152,967.04 rows=10,263 width=12) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=152,607.83..152,864.41 rows=10,263 width=28) (actual rows= loops=)

  • Group Key: audit_evs_all.ntfn_id, audit_evs_all.person_id, audit_evs_all.dvc_id
20.          

Initplan (for GroupAggregate)

21. 0.000 0.000 ↓ 0.0

CTE Scan on cte_org cte_org_5 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Sort (cost=152,607.81..152,633.47 rows=10,263 width=32) (actual rows= loops=)

  • Sort Key: audit_evs_all.ntfn_id, audit_evs_all.person_id, audit_evs_all.dvc_id
23. 0.000 0.000 ↓ 0.0

Merge Join (cost=151,634.98..151,924.03 rows=10,263 width=32) (actual rows= loops=)

  • Merge Cond: (ev_ids.ev_id = audit_evs_all.ev_id)
24. 0.000 0.000 ↓ 0.0

Sort (cost=870.19..895.35 rows=10,062 width=8) (actual rows= loops=)

  • Sort Key: ev_ids.ev_id
25. 0.000 0.000 ↓ 0.0

CTE Scan on ev_ids (cost=0.00..201.24 rows=10,062 width=8) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Sort (cost=150,764.79..150,832.84 rows=27,222 width=40) (actual rows= loops=)

  • Sort Key: audit_evs_all.ev_id
27. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on audit_evs_all (cost=109,512.30..148,759.55 rows=27,222 width=40) (actual rows= loops=)

  • Recheck Cond: ((org_id = $7) AND ((ap_audit_ev_type)::text = ANY ('{LIVE_NOTIFICATION_PROVIDER_DELIVERED,LIVE_NOTIFICATION_DEVICE_DELIVERED,LIVE_NOTIFICATION_DEVICE_READ}'::text[])))
28. 0.000 0.000 ↓ 0.0

BitmapAnd (cost=109,512.30..109,512.30 rows=27,222 width=0) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_audit_evs_all_orgid (cost=0.00..5,087.14 rows=320,410 width=0) (actual rows= loops=)

  • Index Cond: (org_id = $7)
30. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_audit_evs_all_anpni (cost=0.00..104,411.30 rows=3,076,080 width=0) (actual rows= loops=)

  • Index Cond: ((ap_audit_ev_type)::text = ANY ('{LIVE_NOTIFICATION_PROVIDER_DELIVERED,LIVE_NOTIFICATION_DEVICE_DELIVERED,LIVE_NOTIFICATION_DEVICE_READ}'::text[]))
31. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2_1 (cost=1,417,494.80..1,417,658.22 rows=4,669 width=12) (actual rows= loops=)

32. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=1,417,494.80..1,417,611.53 rows=4,669 width=28) (actual rows= loops=)

  • Group Key: audit_evs_all_arc.ntfn_id, audit_evs_all_arc.person_id, audit_evs_all_arc.dvc_id
33.          

Initplan (for GroupAggregate)

34. 0.000 0.000 ↓ 0.0

CTE Scan on cte_org cte_org_6 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Sort (cost=1,417,494.78..1,417,506.45 rows=4,669 width=32) (actual rows= loops=)

  • Sort Key: audit_evs_all_arc.ntfn_id, audit_evs_all_arc.person_id, audit_evs_all_arc.dvc_id
36. 0.000 0.000 ↓ 0.0

Merge Join (cost=1,416,999.42..1,417,210.23 rows=4,669 width=32) (actual rows= loops=)

  • Merge Cond: (audit_evs_all_arc.ev_id = ev_ids_1.ev_id)
37. 0.000 0.000 ↓ 0.0

Sort (cost=1,416,129.22..1,416,186.13 rows=22,763 width=40) (actual rows= loops=)

  • Sort Key: audit_evs_all_arc.ev_id
38. 0.000 0.000 ↓ 0.0

Gather (cost=69,395.27..1,414,481.82 rows=22,763 width=40) (actual rows= loops=)

  • Workers Planned: 4
  • Params Evaluated: $8
39. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on audit_evs_all_arc (cost=68,395.27..1,411,205.52 rows=5,691 width=40) (actual rows= loops=)

  • Recheck Cond: ((ap_audit_ev_type)::text = ANY ('{LIVE_NOTIFICATION_PROVIDER_DELIVERED,LIVE_NOTIFICATION_DEVICE_DELIVERED,LIVE_NOTIFICATION_DEVICE_READ}'::text[]))
  • Filter: (org_id = $8)
40. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_audit_evs_all_arc_anpni (cost=0.00..68,389.57 rows=2,594,986 width=0) (actual rows= loops=)

  • Index Cond: ((ap_audit_ev_type)::text = ANY ('{LIVE_NOTIFICATION_PROVIDER_DELIVERED,LIVE_NOTIFICATION_DEVICE_DELIVERED,LIVE_NOTIFICATION_DEVICE_READ}'::text[]))
41. 0.000 0.000 ↓ 0.0

Sort (cost=870.19..895.35 rows=10,062 width=8) (actual rows= loops=)

  • Sort Key: ev_ids_1.ev_id
42. 0.000 0.000 ↓ 0.0

CTE Scan on ev_ids ev_ids_1 (cost=0.00..201.24 rows=10,062 width=8) (actual rows= loops=)

43. 0.000 0.000 ↓ 0.0

Sort (cost=51,985.57..52,381.38 rows=158,326 width=73) (actual rows= loops=)

  • Sort Key: w.person_id
44. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=35,921.48..38,312.11 rows=158,326 width=73) (actual rows= loops=)

  • Merge Cond: ((w.userid)::text = ("*SELECT* 1".sender)::text)
45. 0.000 0.000 ↓ 0.0

Sort (cost=22,652.64..22,660.50 rows=3,147 width=65) (actual rows= loops=)

  • Sort Key: w.userid
46. 0.000 0.000 ↓ 0.0

Subquery Scan on w (cost=22,005.23..22,469.80 rows=3,147 width=65) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Finalize GroupAggregate (cost=22,005.23..22,438.33 rows=3,147 width=73) (actual rows= loops=)

  • Group Key: r.recipient_id, p.person_id, ((((p.first_name)::text || ' '::text) || (p.last_name)::text)), ((p.when_created)::date)
48.          

Initplan (for Finalize GroupAggregate)

49. 0.000 0.000 ↓ 0.0

CTE Scan on cte_org cte_org_2 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

50. 0.000 0.000 ↓ 0.0

Gather Merge (cost=22,005.21..22,350.46 rows=2,622 width=73) (actual rows= loops=)

  • Workers Planned: 2
  • Params Evaluated: $4
51. 0.000 0.000 ↓ 0.0

Partial GroupAggregate (cost=21,005.19..21,047.79 rows=1,311 width=73) (actual rows= loops=)

  • Group Key: r.recipient_id, p.person_id, ((((p.first_name)::text || ' '::text) || (p.last_name)::text)), ((p.when_created)::date)
52. 0.000 0.000 ↓ 0.0

Sort (cost=21,005.19..21,008.46 rows=1,311 width=73) (actual rows= loops=)

  • Sort Key: r.recipient_id, p.person_id, ((((p.first_name)::text || ' '::text) || (p.last_name)::text)), ((p.when_created)::date)
53. 0.000 0.000 ↓ 0.0

Nested Loop (cost=159.59..20,937.30 rows=1,311 width=73) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Nested Loop (cost=159.17..19,934.75 rows=2,161 width=29) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Parallel Bitmap Heap Scan on recipients r (cost=158.74..10,527.13 rows=3,350 width=21) (actual rows= loops=)

  • Recheck Cond: ((org_id = $4) AND (deleted_id = '-1'::integer))
56. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on idx_recipients_del_org_id_cat (cost=0.00..156.73 rows=8,040 width=0) (actual rows= loops=)

  • Index Cond: (org_id = $4)
57. 0.000 0.000 ↓ 0.0

Index Scan using dvc_pk on dvc d (cost=0.43..2.81 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (dvc_id = r.recipient_id)
58. 0.000 0.000 ↓ 0.0

Index Scan using idx_persons_person_id_not_deleted on persons p (cost=0.42..0.46 rows=1 width=40) (actual rows= loops=)

  • Index Cond: (person_id = d.person_id)
59. 0.000 0.000 ↓ 0.0

Sort (cost=13,268.85..13,294.00 rows=10,062 width=20) (actual rows= loops=)

  • Sort Key: "*SELECT* 1".sender
60. 0.000 0.000 ↓ 0.0

Append (cost=103.13..12,599.89 rows=10,062 width=20) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 1 (cost=103.13..6,111.71 rows=4,927 width=20) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on evs (cost=103.13..6,062.44 rows=4,927 width=20) (actual rows= loops=)

  • Recheck Cond: (company_id = $5)
63.          

Initplan (for Bitmap Heap Scan)

64. 0.000 0.000 ↓ 0.0

CTE Scan on cte_org cte_org_3 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

65. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on evs_company_id_idx (cost=0.00..101.88 rows=4,927 width=0) (actual rows= loops=)

  • Index Cond: (company_id = $5)
66. 0.000 0.000 ↓ 0.0

Subquery Scan on *SELECT* 2 (cost=62.74..6,437.88 rows=5,135 width=20) (actual rows= loops=)

67. 0.000 0.000 ↓ 0.0

Bitmap Heap Scan on evs_arc (cost=62.74..6,386.53 rows=5,135 width=20) (actual rows= loops=)

  • Recheck Cond: (company_id = $6)
68.          

Initplan (for Bitmap Heap Scan)

69. 0.000 0.000 ↓ 0.0

CTE Scan on cte_org cte_org_4 (cost=0.00..0.02 rows=1 width=8) (actual rows= loops=)

70. 0.000 0.000 ↓ 0.0

Bitmap Index Scan on evs_arc_company_id_idx (cost=0.00..61.44 rows=5,135 width=0) (actual rows= loops=)

  • Index Cond: (company_id = $6)