explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9Noj

Settings
# exclusive inclusive rows x rows loops node
1. 0.054 711,864.320 ↓ 6.0 6 1

Sort (cost=3,399,469.02..3,399,469.02 rows=1 width=645) (actual time=711,864.318..711,864.320 rows=6 loops=1)

  • Sort Key: (min(person_ntfns.delivered_date_min))
  • Sort Method: quicksort Memory: 49kB
2.          

CTE get_event

3. 0.001 0.041 ↑ 1.0 1 1

Append (cost=0.42..12.50 rows=1 width=802) (actual time=0.039..0.041 rows=1 loops=1)

4. 0.040 0.040 ↑ 1.0 1 1

Index Scan using events_org_part_203_event_uuid_idx on events_org_part_203 e (cost=0.42..12.50 rows=1 width=802) (actual time=0.038..0.040 rows=1 loops=1)

  • Index Cond: (event_uuid = '66158d5f-217d-4b9b-87f1-0b0ff713cc74'::uuid)
  • Filter: ((valid_range @> '2019-09-04 06:03:43+00'::timestamp with time zone) AND (organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid))
  • Rows Removed by Filter: 1
5.          

CTE get_event_notifications

6. 6.816 222.448 ↑ 1.0 15,543 1

Nested Loop Semi Join (cost=215.77..19,412.20 rows=15,730 width=177) (actual time=11.874..222.448 rows=15,543 loops=1)

7. 1.584 215.632 ↑ 1.0 15,543 1

Append (cost=215.35..19,203.07 rows=15,730 width=177) (actual time=11.824..215.632 rows=15,543 loops=1)

8. 204.359 214.048 ↑ 1.0 15,543 1

Bitmap Heap Scan on notifications_summary_org_part_94 n (cost=215.35..19,124.42 rows=15,730 width=177) (actual time=11.823..214.048 rows=15,543 loops=1)

  • Recheck Cond: (event_uuid = '66158d5f-217d-4b9b-87f1-0b0ff713cc74'::uuid)
  • Filter: ((created_date <= '2019-09-04 06:03:43+00'::timestamp with time zone) AND (organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid))
  • Heap Blocks: exact=373
9. 9.689 9.689 ↑ 1.0 15,543 1

Bitmap Index Scan on notifications_summary_org_part_94_event_uuid_idx (cost=0.00..211.42 rows=15,732 width=0) (actual time=9.689..9.689 rows=15,543 loops=1)

  • Index Cond: (event_uuid = '66158d5f-217d-4b9b-87f1-0b0ff713cc74'::uuid)
10. 0.000 0.000 ↑ 1.0 1 15,543

Materialize (cost=0.42..12.51 rows=1 width=16) (actual time=0.000..0.000 rows=1 loops=15,543)

11. 0.002 0.033 ↑ 1.0 1 1

Append (cost=0.42..12.50 rows=1 width=16) (actual time=0.033..0.033 rows=1 loops=1)

12. 0.031 0.031 ↑ 1.0 1 1

Index Scan using events_org_part_203_event_uuid_idx on events_org_part_203 e_1 (cost=0.42..12.50 rows=1 width=16) (actual time=0.031..0.031 rows=1 loops=1)

  • Index Cond: (event_uuid = '66158d5f-217d-4b9b-87f1-0b0ff713cc74'::uuid)
  • Filter: ((valid_range @> '2019-09-04 06:03:43+00'::timestamp with time zone) AND (organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid))
13.          

CTE get_child_notifications

14. 18.224 359.651 ↑ 43.2 43,012 1

Recursive Union (cost=0.00..81,366.13 rows=1,856,376 width=294) (actual time=0.006..359.651 rows=43,012 loops=1)

15. 227.662 227.662 ↓ 40.5 9,568 1

CTE Scan on get_event_notifications n_1 (cost=0.00..373.59 rows=236 width=294) (actual time=0.004..227.662 rows=9,568 loops=1)

  • Filter: (category = ANY ('{GROUP,PERSON,DEVICE}'::text[]))
  • Rows Removed by Filter: 5975
16. 38.435 113.765 ↑ 27.7 6,689 5

Merge Join (cost=1,590.49..4,386.50 rows=185,614 width=294) (actual time=11.932..22.753 rows=6,689 loops=5)

  • Merge Cond: (p.notification_uuid = n_2.parent_ntfn_uuid)
17. 28.065 38.920 ↓ 3.6 8,602 5

Sort (cost=179.41..185.31 rows=2,360 width=32) (actual time=5.907..7.784 rows=8,602 loops=5)

  • Sort Key: p.notification_uuid
  • Sort Method: quicksort Memory: 659kB
18. 10.855 10.855 ↓ 3.6 8,602 5

WorkTable Scan on get_child_notifications p (cost=0.00..47.20 rows=2,360 width=32) (actual time=0.003..2.171 rows=8,602 loops=5)

19. 28.667 36.410 ↑ 1.0 15,543 5

Sort (cost=1,411.08..1,450.40 rows=15,730 width=278) (actual time=4.011..7.282 rows=15,543 loops=5)

  • Sort Key: n_2.parent_ntfn_uuid
  • Sort Method: quicksort Memory: 2601kB
20. 7.743 7.743 ↑ 1.0 15,543 1

CTE Scan on get_event_notifications n_2 (cost=0.00..314.60 rows=15,730 width=278) (actual time=0.006..7.743 rows=15,543 loops=1)

21.          

CTE get_notifications

22. 18.430 546.176 ↓ 40.5 9,510 1

Hash Left Join (cost=229,733.53..230,111.29 rows=235 width=232) (actual time=521.609..546.176 rows=9,510 loops=1)

  • Hash Cond: (n_3.notification_uuid = agg.root_uuid)
  • Filter: (CASE WHEN (agg.delivery_statuses @> '{RESPONDED}'::text[]) THEN 'RESPONDED'::text WHEN (agg.delivery_statuses @> '{DELIVERED}'::text[]) THEN 'DELIVERED'::text WHEN (agg.delivery_statuses @> '{PENDING}'::text[]) THEN 'PENDING'::text WHEN (agg.delivery_statuses @> '{FAILED}'::text[]) THEN 'FAILED'::text ELSE ''::text END <> ''::text)
  • Rows Removed by Filter: 58
23. 18.083 18.083 ↓ 40.5 9,568 1

CTE Scan on get_event_notifications n_3 (cost=0.00..373.59 rows=236 width=128) (actual time=11.888..18.083 rows=9,568 loops=1)

  • Filter: (category = ANY ('{GROUP,PERSON,DEVICE}'::text[]))
  • Rows Removed by Filter: 5975
24. 4.568 509.663 ↓ 47.8 9,568 1

Hash (cost=229,731.03..229,731.03 rows=200 width=112) (actual time=509.663..509.663 rows=9,568 loops=1)

  • Buckets: 16384 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1825kB
25. 2.394 505.095 ↓ 47.8 9,568 1

Subquery Scan on agg (cost=229,726.53..229,731.03 rows=200 width=112) (actual time=490.443..505.095 rows=9,568 loops=1)

26. 86.586 502.701 ↓ 47.8 9,568 1

HashAggregate (cost=229,726.53..229,729.03 rows=200 width=112) (actual time=490.439..502.701 rows=9,568 loops=1)

  • Group Key: p_1.root_uuid
27. 416.115 416.115 ↑ 43.2 43,012 1

CTE Scan on get_child_notifications p_1 (cost=0.00..37,127.52 rows=1,856,376 width=80) (actual time=0.007..416.115 rows=43,012 loops=1)

28.          

CTE get_response_choices

29. 0.002 0.091 ↓ 0.0 0 1

Append (cost=0.42..1,008.53 rows=20,000 width=128) (actual time=0.091..0.091 rows=0 loops=1)

30. 0.003 0.086 ↓ 0.0 0 1

Nested Loop (cost=0.42..355.99 rows=10,000 width=128) (actual time=0.086..0.086 rows=0 loops=1)

31. 0.004 0.080 ↑ 1.0 1 1

Nested Loop (cost=0.42..3.47 rows=1 width=991) (actual time=0.073..0.080 rows=1 loops=1)

32. 0.046 0.046 ↑ 1.0 1 1

CTE Scan on get_event e_2 (cost=0.00..0.02 rows=1 width=24) (actual time=0.044..0.046 rows=1 loops=1)

  • Filter: (choices IS NULL)
33. 0.030 0.030 ↑ 1.0 1 1

Index Scan using forms_org_seq_vrange_uidx on forms f (cost=0.42..3.44 rows=1 width=1,026) (actual time=0.026..0.030 rows=1 loops=1)

  • Index Cond: ((organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid) AND (form_uuid = e_2.form_uuid))
  • Filter: (valid_range @> e_2.created)
  • Rows Removed by Filter: 1
34. 0.001 0.003 ↓ 0.0 0 1

ProjectSet (cost=0.00..52.52 rows=10,000 width=64) (actual time=0.003..0.003 rows=0 loops=1)

35. 0.002 0.002 ↓ 0.0 0 1

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.002..0.002 rows=0 loops=1)

36. 0.000 0.000 ↑ 1.0 1 1

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=1)

37. 0.001 0.003 ↓ 0.0 0 1

Nested Loop (cost=0.00..352.54 rows=10,000 width=128) (actual time=0.003..0.003 rows=0 loops=1)

38. 0.002 0.002 ↓ 0.0 0 1

CTE Scan on get_event e_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.002..0.002 rows=0 loops=1)

  • Filter: (choices IS NOT NULL)
  • Rows Removed by Filter: 1
39. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..52.52 rows=10,000 width=64) (never executed)

40. 0.000 0.000 ↓ 0.0 0

ProjectSet (cost=0.00..0.52 rows=100 width=32) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Result (cost=0.00..0.01 rows=1 width=0) (never executed)

42.          

CTE get_filtered_response_choices

43. 0.092 0.092 ↓ 0.0 0 1

CTE Scan on get_response_choices rc (cost=0.00..400.00 rows=20,000 width=128) (actual time=0.092..0.092 rows=0 loops=1)

44.          

CTE get_person_notifications

45. 534.156 534.156 ↓ 3,538.0 3,538 1

CTE Scan on get_notifications n_4 (cost=0.00..5.29 rows=1 width=176) (actual time=521.629..534.156 rows=3,538 loops=1)

  • Filter: (category = 'PERSON'::text)
  • Rows Removed by Filter: 5972
46.          

CTE get_team_groups

47. 0.011 9.771 ↑ 3,139.5 2 1

Recursive Union (cost=0.00..4,451.63 rows=6,279 width=100) (actual time=1.233..9.771 rows=2 loops=1)

48. 4.324 4.324 ↑ 79.0 1 1

CTE Scan on get_event_notifications n_5 (cost=0.00..353.93 rows=79 width=100) (actual time=1.230..4.324 rows=1 loops=1)

  • Filter: (category = 'TEAM'::text)
  • Rows Removed by Filter: 15542
49. 0.031 5.436 ↓ 0.0 0 2

Hash Join (cost=355.89..397.21 rows=620 width=100) (actual time=2.717..2.718 rows=0 loops=2)

  • Hash Cond: (tg.parent_ntfn_uuid = n_6.notification_uuid)
50. 0.006 0.006 ↑ 790.0 1 2

WorkTable Scan on get_team_groups tg (cost=0.00..15.80 rows=790 width=36) (actual time=0.002..0.003 rows=1 loops=2)

51. 0.009 5.399 ↑ 78.5 2 1

Hash (cost=353.93..353.93 rows=157 width=80) (actual time=5.399..5.399 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
52. 5.390 5.390 ↑ 78.5 2 1

CTE Scan on get_event_notifications n_6 (cost=0.00..353.93 rows=157 width=80) (actual time=1.378..5.390 rows=2 loops=1)

  • Filter: (category = ANY ('{TEAM,GROUP}'::text[]))
  • Rows Removed by Filter: 15541
53.          

CTE get_targeted_groups

54. 0.014 159.542 ↑ 1.0 1 1

Nested Loop Left Join (cost=143.63..157.76 rows=1 width=208) (actual time=155.378..159.542 rows=1 loops=1)

55. 2.878 20.075 ↑ 1.0 1 1

Hash Right Join (cost=143.07..148.66 rows=1 width=160) (actual time=15.911..20.075 rows=1 loops=1)

  • Hash Cond: (ntfn.notification_uuid = team_group.notification_uuid)
56. 7.382 7.382 ↓ 40.5 9,510 1

CTE Scan on get_notifications ntfn (cost=0.00..4.70 rows=235 width=128) (actual time=0.001..7.382 rows=9,510 loops=1)

57. 0.005 9.815 ↑ 1.0 1 1

Hash (cost=143.05..143.05 rows=1 width=48) (actual time=9.815..9.815 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
58. 0.004 9.810 ↑ 1.0 1 1

Subquery Scan on team_group (cost=142.05..143.05 rows=1 width=48) (actual time=9.808..9.810 rows=1 loops=1)

  • Filter: (team_group."row" = 1)
59. 0.011 9.806 ↑ 31.0 1 1

WindowAgg (cost=142.05..142.67 rows=31 width=60) (actual time=9.805..9.806 rows=1 loops=1)

60. 0.015 9.795 ↑ 31.0 1 1

Sort (cost=142.05..142.12 rows=31 width=52) (actual time=9.794..9.795 rows=1 loops=1)

  • Sort Key: tg_1.root_uuid, tg_1.group_level DESC
  • Sort Method: quicksort Memory: 25kB
61. 9.780 9.780 ↑ 31.0 1 1

CTE Scan on get_team_groups tg_1 (cost=0.00..141.28 rows=31 width=52) (actual time=9.771..9.780 rows=1 loops=1)

  • Filter: (category = 'GROUP'::text)
  • Rows Removed by Filter: 1
62. 139.453 139.453 ↓ 0.0 0 1

Index Scan using groups_org_seq_vrange_uidx on groups g_1 (cost=0.56..9.09 rows=1 width=64) (actual time=139.453..139.453 rows=0 loops=1)

  • Index Cond: ((organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid) AND (group_uuid = team_group.recipient_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-04 06:03:43+00'::timestamp with time zone))
  • Rows Removed by Filter: 13
63.          

CTE get_enriched_notifications

64. 0.231 1,628.685 ↓ 30.0 30 1

Nested Loop Left Join (cost=0.56..98.73 rows=1 width=390) (actual time=719.182..1,628.685 rows=30 loops=1)

65. 3.145 739.494 ↓ 30.0 30 1

Nested Loop Left Join (cost=0.00..5.35 rows=1 width=344) (actual time=688.975..739.494 rows=30 loops=1)

  • Join Filter: (device.parent_ntfn_uuid = ntfns.notification_uuid)
  • Rows Removed by Join Filter: 35796
66. 0.035 696.557 ↓ 6.0 6 1

Nested Loop Left Join (cost=0.00..0.05 rows=1 width=272) (actual time=687.695..696.557 rows=6 loops=1)

  • Join Filter: (targeted_group.team_ntfn_uuid = ntfns.parent_ntfn_uuid)
67. 536.964 536.964 ↓ 6.0 6 1

CTE Scan on get_person_notifications ntfns (cost=0.00..0.02 rows=1 width=176) (actual time=532.302..536.964 rows=6 loops=1)

  • Filter: (delivery_status = 'RESPONDED'::text)
  • Rows Removed by Filter: 3532
68. 159.558 159.558 ↑ 1.0 1 6

CTE Scan on get_targeted_groups targeted_group (cost=0.00..0.02 rows=1 width=112) (actual time=25.898..26.593 rows=1 loops=6)

69. 39.792 39.792 ↓ 5,971.0 5,971 6

CTE Scan on get_notifications device (cost=0.00..5.29 rows=1 width=88) (actual time=0.002..6.632 rows=5,971 loops=6)

  • Filter: (category = 'DEVICE'::text)
  • Rows Removed by Filter: 3539
70. 888.960 888.960 ↓ 0.0 0 30

Index Scan using devices_recipient_uuid_idx on devices d (cost=0.56..93.36 rows=1 width=62) (actual time=28.049..29.632 rows=0 loops=30)

  • Index Cond: (recipient_uuid = device.recipient_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-04 06:03:43+00'::timestamp with time zone) AND (organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid))
  • Rows Removed by Filter: 0
71.          

CTE get_person_info

72. 0.005 909.965 ↓ 6.0 6 1

Subquery Scan on ordered_person_uuids (cost=0.06..0.07 rows=1 width=16) (actual time=909.955..909.965 rows=6 loops=1)

73. 0.028 909.960 ↓ 6.0 6 1

Sort (cost=0.06..0.06 rows=1 width=88) (actual time=909.953..909.960 rows=6 loops=1)

  • Sort Key: (min(ntfns_1.delivered_date_min))
  • Sort Method: quicksort Memory: 25kB
74. 0.207 909.932 ↓ 6.0 6 1

HashAggregate (cost=0.02..0.03 rows=1 width=88) (actual time=909.930..909.932 rows=6 loops=1)

  • Group Key: ntfns_1.recipient_uuid
75. 909.725 909.725 ↓ 30.0 30 1

CTE Scan on get_enriched_notifications ntfns_1 (cost=0.00..0.02 rows=1 width=24) (actual time=0.002..909.725 rows=30 loops=1)

76.          

CTE get_person_uuid_counts

77. 0.007 0.017 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.016..0.017 rows=1 loops=1)

78. 0.010 0.010 ↓ 6.0 6 1

CTE Scan on get_person_info (cost=0.00..0.02 rows=1 width=0) (actual time=0.006..0.010 rows=6 loops=1)

79.          

CTE get_pageable_notifications

80. 0.335 2,649.080 ↓ 30.0 30 1

Nested Loop Semi Join (cost=454.14..1,205.53 rows=1 width=535) (actual time=1,727.623..2,649.080 rows=30 loops=1)

  • Join Filter: (en.recipient_uuid = get_person_info_1.ntfn_person_uuid)
  • Rows Removed by Join Filter: 74
81. 0.163 1,738.605 ↓ 30.0 30 1

Nested Loop Left Join (cost=454.14..503.72 rows=1 width=519) (actual time=817.647..1,738.605 rows=30 loops=1)

82. 719.252 719.252 ↓ 30.0 30 1

CTE Scan on get_enriched_notifications en (cost=0.00..0.02 rows=1 width=472) (actual time=719.188..719.252 rows=30 loops=1)

83. 0.330 1,019.190 ↓ 0.0 0 30

Nested Loop (cost=454.14..503.69 rows=1 width=47) (actual time=33.973..33.973 rows=0 loops=30)

  • Join Filter: ((r.response)::text = COALESCE(rc_1.translated_response, rc_1.text_response))
84. 119.760 1,018.050 ↑ 1.0 1 30

Bitmap Heap Scan on responses r (cost=4.14..8.69 rows=1 width=47) (actual time=33.935..33.935 rows=1 loops=30)

  • Recheck Cond: ((notification_uuid = en.notification_uuid) OR (notification_uuid = en.device_ntfn_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-04 06:03:43+00'::timestamp with time zone) AND (organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid))
  • Heap Blocks: exact=30
85. 0.150 898.290 ↓ 0.0 0 30

BitmapOr (cost=4.14..4.14 rows=3 width=0) (actual time=29.943..29.943 rows=0 loops=30)

86. 159.210 159.210 ↑ 1.0 1 30

Bitmap Index Scan on responses_notification_uuid_idx (cost=0.00..2.07 rows=1 width=0) (actual time=5.307..5.307 rows=1 loops=30)

  • Index Cond: (notification_uuid = en.notification_uuid)
87. 738.930 738.930 ↓ 0.0 0 30

Bitmap Index Scan on responses_notification_uuid_idx (cost=0.00..2.07 rows=1 width=0) (actual time=24.631..24.631 rows=0 loops=30)

  • Index Cond: (notification_uuid = en.device_ntfn_uuid)
88. 0.715 0.810 ↓ 0.0 0 30

HashAggregate (cost=450.00..470.00 rows=2,000 width=64) (actual time=0.027..0.027 rows=0 loops=30)

  • Group Key: COALESCE(rc_1.translated_response, rc_1.text_response)
89. 0.095 0.095 ↓ 0.0 0 1

CTE Scan on get_filtered_response_choices rc_1 (cost=0.00..400.00 rows=20,000 width=64) (actual time=0.095..0.095 rows=0 loops=1)

90. 0.030 910.080 ↓ 3.0 3 30

Limit (cost=0.00..0.02 rows=1 width=16) (actual time=30.334..30.336 rows=3 loops=30)

91. 910.050 910.050 ↓ 3.0 3 30

CTE Scan on get_person_info get_person_info_1 (cost=0.00..0.02 rows=1 width=16) (actual time=30.334..30.335 rows=3 loops=30)

92.          

SubPlan (forNested Loop Semi Join)

93. 0.060 0.060 ↓ 0.0 0 30

HashAggregate (cost=700.50..701.77 rows=127 width=32) (actual time=0.002..0.002 rows=0 loops=30)

  • Group Key: get_filtered_response_choices.text_response
94. 0.000 0.000 ↓ 0.0 0 30

CTE Scan on get_filtered_response_choices (cost=0.00..700.00 rows=200 width=32) (actual time=0.000..0.000 rows=0 loops=30)

  • Filter: ((lower((r.response)::text) = lower(text_response)) OR (lower((r.response)::text) = lower(translated_response)))
95.          

CTE get_person_roles

96. 839.918 708,029.505 ↓ 4.0 411,300 1

GroupAggregate (cost=405,190.76..3,058,773.64 rows=102,755 width=48) (actual time=704,305.586..708,029.505 rows=411,300 loops=1)

  • Group Key: p_2.person_uuid
97. 1,416.936 707,189.587 ↑ 33.0 411,300 1

Nested Loop Left Join (cost=405,190.76..2,412,282.46 rows=13,583,300 width=94) (actual time=704,305.526..707,189.587 rows=411,300 loops=1)

  • Join Filter: (role.role_uuid = (((jsonb_array_elements_text(pr.roles)))::uuid))
  • Rows Removed by Join Filter: 14395164
98. 0.000 704,950.051 ↑ 33.0 411,300 1

Nested Loop Left Join (cost=405,188.80..986,002.21 rows=13,583,300 width=53) (actual time=704,255.704..704,950.051 rows=411,300 loops=1)

99. 143.502 704,563.515 ↓ 3.0 411,300 1

Merge Left Join (cost=405,188.80..406,334.88 rows=135,833 width=71) (actual time=704,255.691..704,563.515 rows=411,300 loops=1)

  • Merge Cond: (p_2.person_uuid = pr.person_uuid)
100. 813.009 680,382.686 ↓ 3.0 411,300 1

Sort (cost=281,050.39..281,389.97 rows=135,833 width=37) (actual time=680,243.544..680,382.686 rows=411,300 loops=1)

  • Sort Key: p_2.person_uuid
  • Sort Method: external merge Disk: 16120kB
101. 679,569.677 679,569.677 ↓ 3.0 411,300 1

Index Scan using persons_org_valid_range_gist on persons p_2 (cost=0.55..267,080.13 rows=135,833 width=37) (actual time=149.105..679,569.677 rows=411,300 loops=1)

  • Index Cond: ((organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid) AND (valid_range @> '2019-09-04 06:03:43+00'::timestamp with time zone))
  • Filter: (is_visible IS TRUE)
102. 131.233 24,037.327 ↓ 2.6 92,903 1

Sort (cost=124,137.52..124,227.69 rows=36,069 width=50) (actual time=24,012.132..24,037.327 rows=92,903 loops=1)

  • Sort Key: pr.person_uuid
  • Sort Method: external sort Disk: 3208kB
103. 19,303.990 23,906.094 ↓ 2.6 92,903 1

Bitmap Heap Scan on person_roles pr (cost=2,168.13..121,407.37 rows=36,069 width=50) (actual time=4,605.956..23,906.094 rows=92,903 loops=1)

  • Recheck Cond: (organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid)
  • Filter: upper_inf(valid_range)
  • Rows Removed by Filter: 6270
  • Heap Blocks: exact=15516
104. 4,602.104 4,602.104 ↑ 1.1 99,644 1

Bitmap Index Scan on person_roles_organization_uuid_idx (cost=0.00..2,159.11 rows=108,207 width=0) (actual time=4,602.104..4,602.104 rows=99,644 loops=1)

  • Index Cond: (organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid)
105. 411.300 411.300 ↓ 0.0 0 411,300

Result (cost=0.00..2.27 rows=100 width=16) (actual time=0.001..0.001 rows=0 loops=411,300)

106. 0.000 0.000 ↓ 0.0 0 411,300

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.000..0.000 rows=0 loops=411,300)

107. 0.000 0.000 ↑ 1.0 1 411,300

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.000 rows=1 loops=411,300)

108. 772.811 822.600 ↓ 5.0 35 411,300

Materialize (cost=1.96..33.76 rows=7 width=57) (actual time=0.000..0.002 rows=35 loops=411,300)

109. 39.016 49.789 ↓ 5.0 35 1

Bitmap Heap Scan on roles role (cost=1.96..33.73 rows=7 width=57) (actual time=49.782..49.789 rows=35 loops=1)

  • Recheck Cond: (organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid)
  • Filter: upper_inf(valid_range)
  • Heap Blocks: exact=1
110. 10.773 10.773 ↓ 1.6 35 1

Bitmap Index Scan on roles_organization_uuid_idx (cost=0.00..1.96 rows=22 width=0) (actual time=10.773..10.773 rows=35 loops=1)

  • Index Cond: (organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid)
111.          

Initplan (forSort)

112. 0.020 0.020 ↑ 1.0 1 1

CTE Scan on get_person_uuid_counts (cost=0.00..0.02 rows=1 width=8) (actual time=0.019..0.020 rows=1 loops=1)

113. 0.098 711,864.246 ↓ 6.0 6 1

Nested Loop (cost=25.01..2,465.66 rows=1 width=645) (actual time=707,325.164..711,864.246 rows=6 loops=1)

114. 0.059 711,863.512 ↓ 6.0 6 1

Nested Loop Left Join (cost=24.91..2,465.53 rows=1 width=573) (actual time=707,324.994..711,863.512 rows=6 loops=1)

115. 0.063 711,703.283 ↓ 6.0 6 1

Nested Loop Left Join (cost=24.49..2,465.01 rows=1 width=559) (actual time=707,228.339..711,703.283 rows=6 loops=1)

116. 1.750 2,651.222 ↓ 6.0 6 1

GroupAggregate (cost=0.03..0.08 rows=1 width=168) (actual time=2,649.706..2,651.222 rows=6 loops=1)

  • Group Key: person_ntfns.event_uuid, person_ntfns.recipient_uuid
117. 0.145 2,649.472 ↓ 30.0 30 1

Sort (cost=0.03..0.04 rows=1 width=384) (actual time=2,649.457..2,649.472 rows=30 loops=1)

  • Sort Key: person_ntfns.event_uuid, person_ntfns.recipient_uuid
  • Sort Method: quicksort Memory: 32kB
118. 2,649.327 2,649.327 ↓ 30.0 30 1

CTE Scan on get_pageable_notifications person_ntfns (cost=0.00..0.02 rows=1 width=384) (actual time=1,727.630..2,649.327 rows=30 loops=1)

119. 369.396 709,051.998 ↑ 1.0 1 6

Hash Right Join (cost=24.46..2,464.90 rows=1 width=407) (actual time=117,496.763..118,175.333 rows=1 loops=6)

  • Hash Cond: (get_person_roles.person_uuid = person.person_uuid)
120. 708,631.416 708,631.416 ↓ 4.0 411,300 6

CTE Scan on get_person_roles (cost=0.00..2,055.10 rows=102,755 width=48) (actual time=117,384.280..118,105.236 rows=411,300 loops=6)

121. 0.090 51.186 ↑ 1.0 1 6

Hash (cost=24.45..24.45 rows=1 width=375) (actual time=8.531..8.531 rows=1 loops=6)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
122. 51.096 51.096 ↑ 1.0 1 6

Index Scan using persons_org_seq_vrange_uidx on persons person (cost=0.69..24.45 rows=1 width=375) (actual time=8.506..8.516 rows=1 loops=6)

  • Index Cond: ((organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid) AND (person_uuid = person_ntfns.recipient_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-04 06:03:43+00'::timestamp with time zone))
  • Rows Removed by Filter: 8
123. 160.170 160.170 ↑ 1.0 1 6

Index Scan using sites_org_site_uuid_idx on sites site (cost=0.42..0.51 rows=1 width=30) (actual time=26.694..26.695 rows=1 loops=6)

  • Index Cond: ((organization_uuid = 'f3185a16-1604-4839-ba8b-c251a95023d2'::uuid) AND (site_uuid = person.site_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-04 06:03:43+00'::timestamp with time zone))
124. 0.402 0.636 ↑ 1.0 1 6

Aggregate (cost=0.10..0.10 rows=1 width=32) (actual time=0.106..0.106 rows=1 loops=6)

125. 0.042 0.234 ↑ 1.0 1 6

HashAggregate (cost=0.07..0.08 rows=1 width=120) (actual time=0.038..0.039 rows=1 loops=6)

  • Group Key: g.targeted_group_ntfn_uuid, g.targeted_group_uuid, g.targeted_group_name, g.group_delivery_status, min(g.first_attempted_date_min), min(g.delivered_date_min), min(g.responded_date_min)
126. 0.090 0.192 ↑ 1.0 1 6

HashAggregate (cost=0.04..0.05 rows=1 width=120) (actual time=0.032..0.032 rows=1 loops=6)

  • Group Key: g.targeted_group_ntfn_uuid, g.targeted_group_uuid, g.targeted_group_name, g.group_delivery_status
127. 0.102 0.102 ↓ 5.0 5 6

CTE Scan on get_pageable_notifications g (cost=0.00..0.02 rows=1 width=120) (actual time=0.009..0.017 rows=5 loops=6)

  • Filter: ((targeted_group_uuid IS NOT NULL) AND (recipient_uuid = person_ntfns.recipient_uuid))
  • Rows Removed by Filter: 25