explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Esu8

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

Insert on recip_resp_stats (cost=634,798,096.22..634,994,719.97 rows=369 width=882) (actual rows=0 loops=1)

  • Buffers: shared hit=4,743,162 read=1,016,278 dirtied=56
  • I/O Timings: read=3,906.105
2. 0.000 0.000 ↑ 24.6 15 1

Subquery Scan on aggregated_data (cost=634,798,096.22..634,994,719.97 rows=369 width=882) (actual rows=15 loops=1)

  • Buffers: shared hit=4,742,816 read=1,016,177 dirtied=7
  • I/O Timings: read=3,905.527
3. 0.000 0.000 ↑ 24.6 15 1

GroupAggregate (cost=634,798,096.22..634,989,415.59 rows=369 width=166) (actual rows=15 loops=1)

  • Group Key: e.ev_id, (CASE WHEN ('DYNAMIC_TEAM'::text = ((SubPlan 5))::text) THEN ae.parent_ntfn_id WHEN (ae.parent_ntfn_id <> ae.root_ntfn_id) THEN (SubPlan 6) ELSE NULL::bigint END), r.recipient_id, (CASE WHEN ((r.recipient_cat)::text = 'GROUP'::text) THEN r.target_name WHEN ((r.recipient_cat)::text = 'PERSON'::text) THEN ((SubPlan 7))::character varying WHEN ((r.recipient_cat)::text = 'DYNAMIC_TEAM'::text) THEN r.target_name ELSE NULL::character varying END)
  • Buffers: shared hit=4,742,816 read=1,016,177 dirtied=7
  • I/O Timings: read=3,905.527
4. 0.000 0.000 ↑ 23.1 16 1

Sort (cost=634,798,096.22..634,798,097.14 rows=369 width=102) (actual rows=16 loops=1)

  • Sort Key: (CASE WHEN ('DYNAMIC_TEAM'::text = ((SubPlan 5))::text) THEN ae.parent_ntfn_id WHEN (ae.parent_ntfn_id <> ae.root_ntfn_id) THEN (SubPlan 6) ELSE NULL::bigint END), r.recipient_id, (CASE WHEN ((r.recipient_cat)::text = 'GROUP'::text) THEN r.target_name WHEN ((r.recipient_cat)::text = 'PERSON'::text) THEN ((SubPlan 7))::character varying WHEN ((r.recipient_cat)::text = 'DYNAMIC_TEAM'::text) THEN r.target_name ELSE NULL::character varying END)
  • Sort Method: quicksort Memory: 27kB
  • Buffers: shared hit=4,437 read=5
  • I/O Timings: read=0.060
5. 0.000 0.000 ↑ 23.1 16 1

Nested Loop Left Join (cost=5,928.52..634,798,080.48 rows=369 width=102) (actual rows=16 loops=1)

  • Buffers: shared hit=4,437 read=5
  • I/O Timings: read=0.060
6. 0.000 0.000 ↑ 23.1 16 1

Nested Loop (cost=5,928.23..634,794,027.06 rows=369 width=76) (actual rows=16 loops=1)

  • Buffers: shared hit=3,960
7. 0.000 0.000 ↑ 1.0 1 1

Index Scan using evs_ev_id_message_panel_id_idx on evs e (cost=0.42..3.44 rows=1 width=16) (actual rows=1 loops=1)

  • Index Cond: (ev_id = $1)
  • Buffers: shared hit=5
8. 0.000 0.000 ↑ 23.1 16 1

Nested Loop Left Join (cost=5,927.81..634,794,019.93 rows=369 width=68) (actual rows=16 loops=1)

  • Filter: (n.sub_id IS NULL)
  • Buffers: shared hit=3,955
9. 0.000 0.000 ↑ 23.7 16 1

Hash Join (cost=5,927.25..634,792,662.76 rows=379 width=68) (actual rows=16 loops=1)

  • Hash Cond: (ae.ntfn_recipient_id = r.recipient_id)
  • Join Filter: ((NOT (alternatives: SubPlan 22 or hashed SubPlan 25)) OR (((r.recipient_cat)::text = 'DYNAMIC_TEAM'::text) AND (SubPlan 28)))
  • Buffers: shared hit=3,875
10. 0.000 0.000 ↑ 57.8 20 1

Index Scan using idx_audit_evs_all_eia on audit_evs_all ae (cost=0.57..634,786,733.05 rows=1,155 width=48) (actual rows=20 loops=1)

  • Index Cond: ((ev_id = $1) AND ((ap_audit_ev_type)::text = ANY ('{PERSON_NOTIFICATION_CREATED,GROUP_NOTIFICATION_CREATED,TEAM_NOTIFICATION_CREATED}'::text[])))
  • Filter: (((param_3)::text = 'Y'::text) AND ((NOT (alternatives: SubPlan 22 or hashed SubPlan 25)) OR (SubPlan 28)))
  • Buffers: shared hit=121
11.          

SubPlan (for Index Scan)

12. 0.000 0.000 ↓ 0.0 0 36

Index Scan using idx_audit_evs_all_anpni on audit_evs_all ae2_6 (cost=0.57..54,503.92 rows=1 width=0) (actual rows=0 loops=36)

  • Index Cond: (((ap_audit_ev_type)::text = 'DELIVERY_STATE_CHANGED'::text) AND (ntfn_id = ae.ntfn_id))
  • Filter: (((ap_audit_msg)::text = 'FAILED'::text) AND ((audit_ev_id = (SubPlan 20)) OR (audit_ev_id = (SubPlan 21))))
  • Rows Removed by Filter: 0
  • Buffers: shared hit=161
13.          

SubPlan (for Index Scan)

14. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=204.12..204.13 rows=1 width=8) (never executed)

15. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_audit_evs_all_anpni on audit_evs_all ae3_2 (cost=0.57..203.79 rows=133 width=8) (never executed)

  • Index Cond: (((ap_audit_ev_type)::text = 'DELIVERY_STATE_CHANGED'::text) AND (ntfn_id = ae2_6.ntfn_id))
16. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=204.12..204.13 rows=1 width=8) (never executed)

17. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_audit_evs_all_anpni on audit_evs_all ae3_3 (cost=0.57..203.79 rows=133 width=8) (never executed)

  • Index Cond: (((ap_audit_ev_type)::text = 'DELIVERY_STATE_CHANGED'::text) AND (ntfn_id = ae2_6.ntfn_id))
18. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_audit_evs_all_anpni on audit_evs_all ae2_7 (cost=0.57..754,537,369.07 rows=135 width=8) (never executed)

  • Index Cond: ((ap_audit_ev_type)::text = 'DELIVERY_STATE_CHANGED'::text)
  • Filter: (((ap_audit_msg)::text = 'FAILED'::text) AND ((audit_ev_id = (SubPlan 23)) OR (audit_ev_id = (SubPlan 24))))
19.          

SubPlan (for Index Scan)

20. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=204.12..204.13 rows=1 width=8) (never executed)

21. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_audit_evs_all_anpni on audit_evs_all ae3_4 (cost=0.57..203.79 rows=133 width=8) (never executed)

  • Index Cond: (((ap_audit_ev_type)::text = 'DELIVERY_STATE_CHANGED'::text) AND (ntfn_id = ae2_7.ntfn_id))
22. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=204.12..204.13 rows=1 width=8) (never executed)

23. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_audit_evs_all_anpni on audit_evs_all ae3_5 (cost=0.57..203.79 rows=133 width=8) (never executed)

  • Index Cond: (((ap_audit_ev_type)::text = 'DELIVERY_STATE_CHANGED'::text) AND (ntfn_id = ae2_7.ntfn_id))
24. 0.000 0.000 ↓ 0.0 0

Nested Loop Anti Join (cost=0.57..760,509,377.85 rows=27,216 width=0) (never executed)

  • Join Filter: (ae3_6.ntfn_id = ae2_8.ntfn_id)
25. 0.000 0.000 ↓ 0.0 0

Seq Scan on audit_evs_all ae2_8 (cost=0.00..4,221,631.09 rows=27,466 width=8) (never executed)

  • Filter: (parent_ntfn_id = ae.ntfn_id)
26. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.57..756,232,128.45 rows=135 width=8) (never executed)

27. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_audit_evs_all_niaaet on audit_evs_all ae3_6 (cost=0.57..756,232,127.78 rows=135 width=8) (never executed)

  • Index Cond: ((ap_audit_ev_type)::text = 'DELIVERY_STATE_CHANGED'::text)
  • Filter: (((ap_audit_msg)::text = 'FAILED'::text) AND ((audit_ev_id = (SubPlan 26)) OR (audit_ev_id = (SubPlan 27))))
28.          

SubPlan (for Index Scan)

29. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=204.12..204.13 rows=1 width=8) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_audit_evs_all_anpni on audit_evs_all ae4 (cost=0.57..203.79 rows=133 width=8) (never executed)

  • Index Cond: (((ap_audit_ev_type)::text = 'DELIVERY_STATE_CHANGED'::text) AND (ntfn_id = ae3_6.ntfn_id))
31. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=204.12..204.13 rows=1 width=8) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_audit_evs_all_anpni on audit_evs_all ae4_1 (cost=0.57..203.79 rows=133 width=8) (never executed)

  • Index Cond: (((ap_audit_ev_type)::text = 'DELIVERY_STATE_CHANGED'::text) AND (ntfn_id = ae3_6.ntfn_id))
33. 0.000 0.000 ↑ 1.8 32,883 1

Hash (cost=5,169.17..5,169.17 rows=60,601 width=28) (actual rows=32,883 loops=1)

  • Buckets: 65,536 Batches: 1 Memory Usage: 2,502kB
  • Buffers: shared hit=3,686
34. 0.000 0.000 ↑ 1.8 32,883 1

Bitmap Heap Scan on recipients r (cost=707.90..5,169.17 rows=60,601 width=28) (actual rows=32,883 loops=1)

  • Recheck Cond: ((recipient_cat)::text = ANY ('{GROUP,PERSON,DYNAMIC_TEAM}'::text[]))
  • Heap Blocks: exact=3,520
  • Buffers: shared hit=3,686
35. 0.000 0.000 ↑ 1.5 40,094 1

Bitmap Index Scan on idx_recipients_recipient_cat (cost=0.00..692.75 rows=60,601 width=0) (actual rows=40,094 loops=1)

  • Index Cond: ((recipient_cat)::text = ANY ('{GROUP,PERSON,DYNAMIC_TEAM}'::text[]))
  • Buffers: shared hit=165
36. 0.000 0.000 ↑ 1.0 1 16

Index Scan using ntfn_pk on ntfn n (cost=0.56..3.57 rows=1 width=16) (actual rows=1 loops=16)

  • Index Cond: (ntfn_id = ae.ntfn_id)
  • Buffers: shared hit=80
37. 0.000 0.000 ↑ 1.0 1 16

Index Scan using web_creds_pk on web_creds wc (cost=0.29..0.31 rows=1 width=17) (actual rows=1 loops=16)

  • Index Cond: (web_cred_id = r.recipient_id)
  • Buffers: shared hit=41 read=3
  • I/O Timings: read=0.012
38.          

SubPlan (for Nested Loop Left Join)

39. 0.000 0.000 ↑ 1.0 1 16

Limit (cost=0.98..6.06 rows=1 width=6) (actual rows=1 loops=16)

  • Buffers: shared hit=261 read=1
  • I/O Timings: read=0.018
40. 0.000 0.000 ↑ 350.0 1 16

Nested Loop (cost=0.98..1,777.91 rows=350 width=6) (actual rows=1 loops=16)

  • Buffers: shared hit=261 read=1
  • I/O Timings: read=0.018
41. 0.000 0.000 ↑ 399.0 1 16

Index Scan using idx_audit_evs_all_anpni on audit_evs_all parent (cost=0.57..611.35 rows=399 width=8) (actual rows=1 loops=16)

  • Index Cond: (((ap_audit_ev_type)::text = ANY ('{PERSON_NOTIFICATION_CREATED,GROUP_NOTIFICATION_CREATED,TEAM_NOTIFICATION_CREATED}'::text[])) AND (ntfn_id = ae.parent_ntfn_id))
  • Buffers: shared hit=205 read=1
  • I/O Timings: read=0.018
42. 0.000 0.000 ↑ 1.0 1 14

Index Scan using recipients_pk on recipients r2 (cost=0.42..2.92 rows=1 width=14) (actual rows=1 loops=14)

  • Index Cond: (recipient_id = parent.ntfn_recipient_id)
  • Buffers: shared hit=56
43. 0.000 0.000 ↑ 1.0 1 14

Limit (cost=1.14..1.28 rows=1 width=8) (actual rows=1 loops=14)

  • Buffers: shared hit=140
44. 0.000 0.000 ↑ 14,336.0 1 14

Nested Loop (cost=1.14..2,061.72 rows=14,336 width=8) (actual rows=1 loops=14)

  • Buffers: shared hit=140
45. 0.000 0.000 ↑ 133.0 1 14

Index Only Scan using idx_audit_evs_all_anpni on audit_evs_all tm (cost=0.57..15.15 rows=133 width=8) (actual rows=1 loops=14)

  • Index Cond: ((ap_audit_ev_type = 'TEAM_NOTIFICATION_CREATED'::text) AND (ntfn_id = ae.parent_ntfn_id))
  • Heap Fetches: 14
  • Buffers: shared hit=70
46. 0.000 0.000 ↑ 133.0 1 14

Index Only Scan using idx_audit_evs_all_anpni on audit_evs_all grp (cost=0.57..14.06 rows=133 width=8) (actual rows=1 loops=14)

  • Index Cond: ((ap_audit_ev_type = 'GROUP_NOTIFICATION_CREATED'::text) AND (ntfn_id = tm.parent_ntfn_id))
  • Heap Fetches: 14
  • Buffers: shared hit=70
47. 0.000 0.000 ↑ 1.0 1 12

Index Scan using persons_person_id_upper_first_name_upper_last_name_fbi on persons (cost=0.29..3.32 rows=1 width=32) (actual rows=1 loops=12)

  • Index Cond: (person_id = r.recipient_id)
  • Buffers: shared hit=35 read=1
  • I/O Timings: read=0.029
48.          

SubPlan (for GroupAggregate)

49. 0.000 0.000 ↓ 0.0 0 15

Nested Loop Left Join (cost=16.38..8,680,965.23 rows=231,242 width=0) (actual rows=0 loops=15)

  • Filter: ((ae2_4.parent_ntfn_id = ae2_4.root_ntfn_id) OR (ae2_4.parent_ntfn_id = CASE WHEN ('DYNAMIC_TEAM'::text = ($16)::text) THEN ae.parent_ntfn_id WHEN (ae.parent_ntfn_id <> ae.root_ntfn_id) THEN $18 ELSE NULL::bigint END) OR (grp_3.ntfn_id = CASE WHEN ('DYNAMIC_TEAM'::text = ($20)::text) THEN ae.parent_ntfn_id WHEN (ae.parent_ntfn_id <> ae.root_ntfn_id) THEN $22 ELSE NULL::bigint END))
  • Buffers: shared hit=75
50.          

Initplan (for Nested Loop Left Join)

51. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.98..6.06 rows=1 width=6) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.98..1,777.91 rows=350 width=6) (never executed)

53. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_audit_evs_all_anpni on audit_evs_all parent_1 (cost=0.57..611.35 rows=399 width=8) (never executed)

  • Index Cond: (((ap_audit_ev_type)::text = ANY ('{PERSON_NOTIFICATION_CREATED,GROUP_NOTIFICATION_CREATED,TEAM_NOTIFICATION_CREATED}'::text[])) AND (ntfn_id = ae.parent_ntfn_id))
54. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_pk on recipients r2_1 (cost=0.42..2.92 rows=1 width=14) (never executed)

  • Index Cond: (recipient_id = parent_1.ntfn_recipient_id)
55. 0.000 0.000 ↓ 0.0 0

Limit (cost=1.14..1.28 rows=1 width=8) (never executed)

56. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.14..2,061.72 rows=14,336 width=8) (never executed)

57. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_audit_evs_all_anpni on audit_evs_all tm_1 (cost=0.57..15.15 rows=133 width=8) (never executed)

  • Index Cond: ((ap_audit_ev_type = 'TEAM_NOTIFICATION_CREATED'::text) AND (ntfn_id = ae.parent_ntfn_id))
  • Heap Fetches: 0
58. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_audit_evs_all_anpni on audit_evs_all grp_1 (cost=0.57..14.06 rows=133 width=8) (never executed)

  • Index Cond: ((ap_audit_ev_type = 'GROUP_NOTIFICATION_CREATED'::text) AND (ntfn_id = tm_1.parent_ntfn_id))
  • Heap Fetches: 0
59. 0.000 0.000 ↓ 0.0 0

Limit (cost=0.98..6.06 rows=1 width=6) (never executed)

60. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=0.98..1,777.91 rows=350 width=6) (never executed)

61. 0.000 0.000 ↓ 0.0 0

Index Scan using idx_audit_evs_all_anpni on audit_evs_all parent_2 (cost=0.57..611.35 rows=399 width=8) (never executed)

  • Index Cond: (((ap_audit_ev_type)::text = ANY ('{PERSON_NOTIFICATION_CREATED,GROUP_NOTIFICATION_CREATED,TEAM_NOTIFICATION_CREATED}'::text[])) AND (ntfn_id = ae.parent_ntfn_id))
62. 0.000 0.000 ↓ 0.0 0

Index Scan using recipients_pk on recipients r2_2 (cost=0.42..2.92 rows=1 width=14) (never executed)

  • Index Cond: (recipient_id = parent_2.ntfn_recipient_id)
63. 0.000 0.000 ↓ 0.0 0

Limit (cost=1.14..1.28 rows=1 width=8) (never executed)

64. 0.000 0.000 ↓ 0.0 0

Nested Loop (cost=1.14..2,061.72 rows=14,336 width=8) (never executed)

65. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_audit_evs_all_anpni on audit_evs_all tm_2 (cost=0.57..15.15 rows=133 width=8) (never executed)

  • Index Cond: ((ap_audit_ev_type = 'TEAM_NOTIFICATION_CREATED'::text) AND (ntfn_id = ae.parent_ntfn_id))
  • Heap Fetches: 0
66. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_audit_evs_all_anpni on audit_evs_all grp_2 (cost=0.57..14.06 rows=133 width=8) (never executed)

  • Index Cond: ((ap_audit_ev_type = 'GROUP_NOTIFICATION_CREATED'::text) AND (ntfn_id = tm_2.parent_ntfn_id))
  • Heap Fetches: 0
67. 0.000 0.000 ↓ 0.0 0 15

Nested Loop Left Join (cost=1.14..6,304,501.12 rows=9,239 width=24) (actual rows=0 loops=15)

  • Join Filter: (ae2_4.parent_ntfn_id = tm_3.ntfn_id)
  • Buffers: shared hit=75
68. 0.000 0.000 ↓ 0.0 0 15

Index Scan using idx_audit_evs_all_eia on audit_evs_all ae2_4 (cost=0.57..6,645.24 rows=2 width=16) (actual rows=0 loops=15)

  • Index Cond: ((ev_id = e.ev_id) AND ((ap_audit_ev_type)::text = 'NOTIFICATION_PEER_ESCALATION'::text))
  • Filter: (ntfn_recipient_id = r.recipient_id)
  • Buffers: shared hit=75
69. 0.000 0.000 ↓ 0.0 0

Materialize (cost=0.57..3,740,439.48 rows=78,974,487 width=16) (never executed)

70. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_audit_evs_all_anpni on audit_evs_all tm_3 (cost=0.57..2,959,949.05 rows=78,974,487 width=16) (never executed)

  • Heap Fetches: 0
71. 0.000 0.000 ↓ 0.0 0

Index Only Scan using idx_audit_evs_all_niaaet on audit_evs_all grp_3 (cost=0.57..100.39 rows=5,703 width=8) (never executed)

  • Index Cond: (ntfn_id = tm_3.parent_ntfn_id)
  • Heap Fetches: 0
72. 0.000 0.000 ↓ 0.0 0 15

Nested Loop Left Join (cost=20.80..5,731,382.97 rows=231,242 width=0) (actual rows=0 loops=15)

  • Filter: ((ae2_5.parent_ntfn_id = ae2_5.root_ntfn_id) OR (ae2_5.parent_ntfn_id = CASE WHEN ('DYNAMIC_TEAM'::text = ($29)::text) THEN ae.parent_ntfn_id WHEN (ae.parent_ntfn_id <> ae.root_ntfn_id) THEN $31 ELSE NULL::bigint END) OR (grp_6.ntfn_id = CASE WHEN ('DYNAMIC_TEAM'::text = ($33)::text) THEN ae.parent_ntfn_id WHEN (ae.parent_ntfn_id <> ae.root_ntfn_id) THEN $35 ELSE NULL::bigint END))
  • Buffers: shared hit=4,738,176 read=1,016,172 dirtied=7
  • I/O Timings: read=3,905.467
73.          

Initplan (for Nested Loop Left Join)

74. 0.000 0.000 ↑ 1.0 1 1

Limit (cost=0.98..6.06 rows=1 width=6) (actual rows=1 loops=1)

  • Buffers: shared hit=17
75. 0.000 0.000 ↑ 350.0 1 1

Nested Loop (cost=0.98..1,777.91 rows=350 width=6) (actual rows=1 loops=1)

  • Buffers: shared hit=17
76. 0.000 0.000 ↑ 399.0 1 1

Index Scan using idx_audit_evs_all_anpni on audit_evs_all parent_3 (cost=0.57..611.35 rows=399 width=8) (actual rows=1 loops=1)

  • Index Cond: (((ap_audit_ev_type)::text = ANY ('{PERSON_NOTIFICATION_CREATED,GROUP_NOTIFICATION_CREATED,TEAM_NOTIFICATION_CREATED}'::text[])) AND (ntfn_id = ae.parent_ntfn_id))
  • Buffers: shared hit=13
77. 0.000 0.000 ↑ 1.0 1 1

Index Scan using recipients_pk on recipients r2_3 (cost=0.42..2.92 rows=1 width=14) (actual rows=1 loops=1)

  • Index Cond: (recipient_id = parent_3.ntfn_recipient_id)
  • Buffers: shared hit=4
78. 0.000 0.000 ↑ 1.0 1 1

Limit (cost=1.14..1.28 rows=1 width=8) (actual rows=1 loops=1)

  • Buffers: shared hit=10
79. 0.000 0.000 ↑ 14,336.0 1 1

Nested Loop (cost=1.14..2,061.72 rows=14,336 width=8) (actual rows=1 loops=1)

  • Buffers: shared hit=10
80. 0.000 0.000 ↑ 133.0 1 1

Index Only Scan using idx_audit_evs_all_anpni on audit_evs_all tm_4 (cost=0.57..15.15 rows=133 width=8) (actual rows=1 loops=1)

  • Index Cond: ((ap_audit_ev_type = 'TEAM_NOTIFICATION_CREATED'::text) AND (ntfn_id = ae.parent_ntfn_id))
  • Heap Fetches: 1
  • Buffers: shared hit=5
81. 0.000 0.000 ↑ 133.0 1 1

Index Only Scan using idx_audit_evs_all_anpni on audit_evs_all grp_4 (cost=0.57..14.06 rows=133 width=8) (actual rows=1 loops=1)

  • Index Cond: ((ap_audit_ev_type = 'GROUP_NOTIFICATION_CREATED'::text) AND (ntfn_id = tm_4.parent_ntfn_id))
  • Heap Fetches: 1
  • Buffers: shared hit=5
82. 0.000 0.000 ↑ 1.0 1 1

Limit (cost=0.98..6.06 rows=1 width=6) (actual rows=1 loops=1)

  • Buffers: shared hit=17
83. 0.000 0.000 ↑ 350.0 1 1

Nested Loop (cost=0.98..1,777.91 rows=350 width=6) (actual rows=1 loops=1)

  • Buffers: shared hit=17
84. 0.000 0.000 ↑ 399.0 1 1

Index Scan using idx_audit_evs_all_anpni on audit_evs_all parent_4 (cost=0.57..611.35 rows=399 width=8) (actual rows=1 loops=1)

  • Index Cond: (((ap_audit_ev_type)::text = ANY ('{PERSON_NOTIFICATION_CREATED,GROUP_NOTIFICATION_CREATED,TEAM_NOTIFICATION_CREATED}'::text[])) AND (ntfn_id = ae.parent_ntfn_id))
  • Buffers: shared hit=13
85. 0.000 0.000 ↑ 1.0 1 1

Index Scan using recipients_pk on recipients r2_4 (cost=0.42..2.92 rows=1 width=14) (actual rows=1 loops=1)

  • Index Cond: (recipient_id = parent_4.ntfn_recipient_id)
  • Buffers: shared hit=4
86. 0.000 0.000 ↑ 1.0 1 1

Limit (cost=1.14..1.28 rows=1 width=8) (actual rows=1 loops=1)

  • Buffers: shared hit=10
87. 0.000 0.000 ↑ 14,336.0 1 1

Nested Loop (cost=1.14..2,061.72 rows=14,336 width=8) (actual rows=1 loops=1)

  • Buffers: shared hit=10
88. 0.000 0.000 ↑ 133.0 1 1

Index Only Scan using idx_audit_evs_all_anpni on audit_evs_all tm_5 (cost=0.57..15.15 rows=133 width=8) (actual rows=1 loops=1)

  • Index Cond: ((ap_audit_ev_type = 'TEAM_NOTIFICATION_CREATED'::text) AND (ntfn_id = ae.parent_ntfn_id))
  • Heap Fetches: 1
  • Buffers: shared hit=5
89. 0.000 0.000 ↑ 133.0 1 1

Index Only Scan using idx_audit_evs_all_anpni on audit_evs_all grp_5 (cost=0.57..14.06 rows=133 width=8) (actual rows=1 loops=1)

  • Index Cond: ((ap_audit_ev_type = 'GROUP_NOTIFICATION_CREATED'::text) AND (ntfn_id = tm_5.parent_ntfn_id))
  • Heap Fetches: 1
  • Buffers: shared hit=5
90. 0.000 0.000 ↓ 0.0 0 15

Hash Right Join (cost=5.55..3,354,918.86 rows=9,239 width=24) (actual rows=0 loops=15)

  • Hash Cond: (tm_6.ntfn_id = ae2_5.parent_ntfn_id)
  • Buffers: shared hit=4,738,117 read=1,016,172 dirtied=7
  • I/O Timings: read=3,905.467
91. 0.000 0.000 ↑ 1.8 43,191,177 2

Index Only Scan using idx_audit_evs_all_anpni on audit_evs_all tm_6 (cost=0.57..2,959,949.05 rows=78,974,487 width=16) (actual rows=43,191,177 loops=2)

  • Heap Fetches: 6,485,342
  • Buffers: shared hit=4,738,084 read=1,016,158 dirtied=7
  • I/O Timings: read=3,905.403
92. 0.000 0.000 ↓ 0.0 0 15

Hash (cost=4.96..4.96 rows=2 width=16) (actual rows=0 loops=15)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
  • Buffers: shared hit=33 read=14
  • I/O Timings: read=0.064
93. 0.000 0.000 ↓ 0.0 0 15

Index Scan using aea_ev_id_nri_ei_pidx on audit_evs_all ae2_5 (cost=0.42..4.96 rows=2 width=16) (actual rows=0 loops=15)

  • Index Cond: ((ntfn_recipient_id = r.recipient_id) AND (ev_id = e.ev_id))
  • Buffers: shared hit=33 read=14
  • I/O Timings: read=0.064
94. 0.000 0.000 ↓ 0.0 0 2

Index Only Scan using idx_audit_evs_all_niaaet on audit_evs_all grp_6 (cost=0.57..100.39 rows=5,703 width=8) (actual rows=0 loops=2)

  • Index Cond: (ntfn_id = tm_6.parent_ntfn_id)
  • Heap Fetches: 1
  • Buffers: shared hit=5
95. 0.000 0.000 ↑ 1.0 1 16

Aggregate (cost=204.12..204.13 rows=1 width=8) (actual rows=1 loops=16)

  • Buffers: shared hit=64
96. 0.000 0.000 ↓ 0.0 0 16

Index Scan using idx_audit_evs_all_anpni on audit_evs_all ae3_1 (cost=0.57..203.79 rows=133 width=8) (actual rows=0 loops=16)

  • Index Cond: (((ap_audit_ev_type)::text = 'NOTIFICATION_RECEIVED_RESPONSE'::text) AND (ntfn_id = ae.ntfn_id))
  • Buffers: shared hit=64
97. 0.000 0.000 ↑ 1.0 1 16

Aggregate (cost=204.12..204.13 rows=1 width=8) (actual rows=1 loops=16)

  • Buffers: shared hit=64
98. 0.000 0.000 ↓ 0.0 0 16

Index Scan using idx_audit_evs_all_anpni on audit_evs_all ae3 (cost=0.57..203.79 rows=133 width=8) (actual rows=0 loops=16)

  • Index Cond: (((ap_audit_ev_type)::text = 'NOTIFICATION_RECEIVED_RESPONSE'::text) AND (ntfn_id = ae.ntfn_id))
  • Buffers: shared hit=64
99.          

SubPlan (for Subquery Scan)

100. 0.000 0.000 ↓ 0.0 0 15

Index Scan using audit_evs_all_pk on audit_evs_all ae2 (cost=0.57..3.59 rows=1 width=32) (actual rows=0 loops=15)

  • Index Cond: (audit_ev_id = aggregated_data.response_audit_id)
101. 0.000 0.000 ↓ 0.0 0 15

Index Scan using audit_evs_all_pk on audit_evs_all ae2_1 (cost=0.57..3.59 rows=1 width=32) (actual rows=0 loops=15)

  • Index Cond: (audit_ev_id = aggregated_data.response_audit_id)
102. 0.000 0.000 ↓ 0.0 0 15

Index Scan using audit_evs_all_pk on audit_evs_all ae2_2 (cost=0.57..3.58 rows=1 width=8) (actual rows=0 loops=15)

  • Index Cond: (audit_ev_id = aggregated_data.response_audit_id)
103. 0.000 0.000 ↓ 0.0 0 15

Index Scan using audit_evs_all_pk on audit_evs_all ae2_3 (cost=0.57..3.58 rows=1 width=8) (actual rows=0 loops=15)

  • Index Cond: (audit_ev_id = aggregated_data.first_response_audit_id)