explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LAwM

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 32.080 ↓ 2.0 2 1

Sort (cost=4,339.73..4,339.74 rows=1 width=464) (actual time=32.059..32.080 rows=2 loops=1)

  • Sort Key: (CASE WHEN ((array_agg(pn.person_delivery_status)) @> '{RESPONDED}'::text[]) THEN 'RESPONDED'::text WHEN ((array_agg(pn.person_delivery_status)) @> '{DELIVERED}'::text[]) THEN 'DELIVERED'::text WHEN ((array_agg(pn.person_delivery_status)) @> '{NOTIFYING}'::text[]) THEN 'PENDING'::text WHEN ((array_agg(pn.person_delivery_status)) @> '{PENDING}'::text[]) THEN 'PENDING'::text WHEN ((array_agg(pn.person_delivery_status)) @> '{FAILED}'::text[]) THEN 'FAILED'::text ELSE 'PENDING'::text END) DESC, (min(pn.device_delivered_date_min)) DESC
  • Sort Method: quicksort Memory: 27kB
2.          

CTE get_event

3. 0.031 0.104 ↑ 1.0 1 1

Append (cost=4.31..17.67 rows=1 width=758) (actual time=0.074..0.104 rows=1 loops=1)

4. 0.045 0.073 ↑ 1.0 1 1

Bitmap Heap Scan on events_org_part_1 e (cost=4.31..17.66 rows=1 width=758) (actual time=0.058..0.073 rows=1 loops=1)

  • Recheck Cond: (event_uuid = '4ab89cc0-f0b0-44ad-b371-5457fd772eab'::uuid)
  • Filter: ((valid_range @> '2020-09-04 10:12:18-07'::timestamp with time zone) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid))
  • Heap Blocks: exact=1
5. 0.028 0.028 ↑ 4.0 1 1

Bitmap Index Scan on events_org_part_1_event_uuid_idx (cost=0.00..4.31 rows=4 width=0) (actual time=0.021..0.028 rows=1 loops=1)

  • Index Cond: (event_uuid = '4ab89cc0-f0b0-44ad-b371-5457fd772eab'::uuid)
6.          

CTE get_response_choices

7. 0.233 0.887 ↑ 2,222.2 9 1

Append (cost=0.14..1,013.25 rows=20,000 width=128) (actual time=0.234..0.887 rows=9 loops=1)

8. 0.016 0.048 ↓ 0.0 0 1

Nested Loop (cost=0.14..360.71 rows=10,000 width=128) (actual time=0.041..0.048 rows=0 loops=1)

9. 0.015 0.032 ↓ 0.0 0 1

Nested Loop (cost=0.14..8.19 rows=1 width=1,318) (actual time=0.025..0.032 rows=0 loops=1)

10. 0.017 0.017 ↓ 0.0 0 1

CTE Scan on get_event e_1 (cost=0.00..0.02 rows=1 width=24) (actual time=0.010..0.017 rows=0 loops=1)

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

Index Scan using forms_valid_range_gist on forms f (cost=0.14..8.16 rows=1 width=1,354) (never executed)

  • Index Cond: (valid_range @> e_1.created)
  • Filter: ((organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid) AND (e_1.form_uuid = form_uuid))
12. 0.000 0.000 ↓ 0.0 0

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

13. 0.000 0.000 ↓ 0.0 0

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

14. 0.000 0.000 ↓ 0.0 0

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

15. 0.226 0.606 ↑ 1,111.1 9 1

Nested Loop (cost=0.00..352.54 rows=10,000 width=128) (actual time=0.166..0.606 rows=9 loops=1)

16. 0.029 0.029 ↑ 1.0 1 1

CTE Scan on get_event e_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.009..0.029 rows=1 loops=1)

  • Filter: (choices IS NOT NULL)
17. 0.250 0.351 ↑ 1,111.1 9 1

ProjectSet (cost=0.00..52.52 rows=10,000 width=64) (actual time=0.113..0.351 rows=9 loops=1)

18. 0.073 0.101 ↑ 33.3 3 1

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.044..0.101 rows=3 loops=1)

19. 0.028 0.028 ↑ 1.0 1 1

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

20.          

CTE get_filtered_response_choices

21. 0.152 0.436 ↑ 3,333.3 3 1

Hash Join (cost=603.36..1,167.11 rows=10,000 width=128) (actual time=0.231..0.436 rows=3 loops=1)

  • Hash Cond: (rc.text_response = rc2.text_response)
22. 0.092 0.092 ↑ 2,222.2 9 1

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

23. 0.028 0.192 ↑ 127.0 1 1

Hash (cost=601.77..601.77 rows=127 width=32) (actual time=0.185..0.192 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
24. 0.079 0.164 ↑ 127.0 1 1

HashAggregate (cost=600.50..601.77 rows=127 width=32) (actual time=0.147..0.164 rows=1 loops=1)

  • Group Key: rc2.text_response
25. 0.085 0.085 ↑ 66.7 3 1

CTE Scan on get_response_choices rc2 (cost=0.00..600.00 rows=200 width=32) (actual time=0.040..0.085 rows=3 loops=1)

  • Filter: ((lower(translated_response) = 'respond'::text) OR (lower(text_response) = 'respond'::text))
  • Rows Removed by Filter: 6
26.          

CTE get_person_device_notifications

27. 0.129 10.850 ↓ 3.0 6 1

Unique (cost=987.19..987.30 rows=2 width=344) (actual time=10.678..10.850 rows=6 loops=1)

28. 0.159 10.721 ↓ 3.0 6 1

Sort (cost=987.19..987.20 rows=2 width=344) (actual time=10.657..10.721 rows=6 loops=1)

  • Sort Key: person_ntfn.recipient_uuid, person_ntfn.notification_uuid, person_ntfn.event_uuid, person_ntfn.parent_ntfn_uuid, (CASE WHEN (person_ntfn.delinked_date <= '2020-09-04 10:12:18-07'::timestamp with time zone) THEN person_ntfn.delinked_date ELSE NULL::timestamp with time zone END), device_ntfn.recipient_uuid, device_ntfn.notification_uuid, (CASE WHEN (device_ntfn.failed_date <= '2020-09-04 10:12:18-07'::timestamp with time zone) THEN device_ntfn.failed_date ELSE NULL::timestamp with time zone END), (CASE WHEN (device_ntfn.delivered_date <= '2020-09-04 10:12:18-07'::timestamp with time zone) THEN device_ntfn.delivered_date ELSE NULL::timestamp with time zone END), (CASE WHEN (((device_response.response)::text <> 'XMATTERS_REMOVE'::text) AND (device_response.received <= '2020-09-04 10:12:18-07'::timestamp with time zone)) THEN device_response.received ELSE NULL::timestamp with time zone END), (CASE WHEN ((device_response.received <= '2020-09-04 10:12:18-07'::timestamp with time zone) AND ((device_response.response)::text <> 'XMATTERS_REMOVE'::text)) THEN 'RESPONDED'::text WHEN (device_ntfn.delivered_date <= '2020-09-04 10:12:18-07'::timestamp with time zone) THEN 'DELIVERED'::text WHEN (device_ntfn.failed_date <= '2020-09-04 10:12:18-07'::timestamp with time zone) THEN 'FAILED'::text WHEN (live_ntfn.pending_date <= '2020-09-04 10:12:18-07'::timestamp with time zone) THEN 'PENDING'::text ELSE ''::text END), (CASE WHEN (live_ntfn.pending_date <= '2020-09-04 10:12:18-07'::timestamp with time zone) THEN live_ntfn.pending_date ELSE NULL::timestamp with time zone END), device_response.response_uuid, ((SubPlan 4)), (CASE WHEN (device_response.received <= '2020-09-04 10:12:18-07'::timestamp with time zone) THEN device_response.received ELSE NULL::timestamp with time zone END), device_response.recipient_type, (NULL::uuid), (NULL::character varying), (NULL::timestamp with time zone), (NULL::character varying)
  • Sort Method: quicksort Memory: 26kB
29. 0.120 10.562 ↓ 3.0 6 1

Append (cost=20.42..987.18 rows=2 width=344) (actual time=3.826..10.562 rows=6 loops=1)

30. 0.000 4.910 ↓ 5.0 5 1

Nested Loop (cost=20.42..733.43 rows=1 width=318) (actual time=3.808..4.910 rows=5 loops=1)

31.          

Initplan (forNested Loop)

32. 0.031 0.053 ↑ 1.0 1 1

Aggregate (cost=0.02..0.04 rows=1 width=8) (actual time=0.039..0.053 rows=1 loops=1)

33. 0.022 0.022 ↑ 1.0 1 1

CTE Scan on get_event e_3 (cost=0.00..0.02 rows=1 width=8) (actual time=0.008..0.022 rows=1 loops=1)

34. 0.032 0.056 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.042..0.056 rows=1 loops=1)

35. 0.024 0.024 ↑ 1.0 1 1

CTE Scan on get_event e_4 (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.024 rows=1 loops=1)

36. 0.037 0.061 ↑ 1.0 1 1

Aggregate (cost=0.02..0.04 rows=1 width=8) (actual time=0.046..0.061 rows=1 loops=1)

37. 0.024 0.024 ↑ 1.0 1 1

CTE Scan on get_event e_5 (cost=0.00..0.02 rows=1 width=8) (actual time=0.008..0.024 rows=1 loops=1)

38. 0.037 0.062 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.047..0.062 rows=1 loops=1)

39. 0.025 0.025 ↑ 1.0 1 1

CTE Scan on get_event e_6 (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.025 rows=1 loops=1)

40. 0.034 0.076 ↑ 1.0 1 1

Aggregate (cost=0.02..0.04 rows=1 width=8) (actual time=0.062..0.076 rows=1 loops=1)

41. 0.042 0.042 ↑ 1.0 1 1

CTE Scan on get_event e_7 (cost=0.00..0.02 rows=1 width=8) (actual time=0.008..0.042 rows=1 loops=1)

42. 0.031 0.054 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.039..0.054 rows=1 loops=1)

43. 0.023 0.023 ↑ 1.0 1 1

CTE Scan on get_event e_8 (cost=0.00..0.02 rows=1 width=8) (actual time=0.008..0.023 rows=1 loops=1)

44. 0.717 2.704 ↓ 5.0 5 1

Hash Right Join (cost=19.95..23.11 rows=1 width=111) (actual time=2.446..2.704 rows=5 loops=1)

  • Hash Cond: (device_response.notification_uuid = device_ntfn.notification_uuid)
45. 0.683 0.683 ↓ 1.1 66 1

Seq Scan on responses device_response (cost=0.00..2.93 rows=59 width=55) (actual time=0.015..0.683 rows=66 loops=1)

  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2020-09-04 10:12:18-07'::timestamp with time zone) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid))
  • Rows Removed by Filter: 2
46. 0.057 1.304 ↓ 5.0 5 1

Hash (cost=19.93..19.93 rows=1 width=72) (actual time=1.296..1.304 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
47. 0.159 1.247 ↓ 5.0 5 1

Nested Loop (cost=4.53..19.93 rows=1 width=72) (actual time=0.581..1.247 rows=5 loops=1)

48. 0.096 0.593 ↓ 5.0 5 1

Nested Loop (cost=4.26..11.62 rows=1 width=24) (actual time=0.383..0.593 rows=5 loops=1)

49. 0.033 0.169 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=0.154..0.169 rows=1 loops=1)

  • Group Key: get_event.event_uuid
50. 0.136 0.136 ↑ 1.0 1 1

CTE Scan on get_event (cost=0.00..0.02 rows=1 width=16) (actual time=0.092..0.136 rows=1 loops=1)

51. 0.089 0.328 ↓ 5.0 5 1

Append (cost=4.24..11.57 rows=1 width=40) (actual time=0.205..0.328 rows=5 loops=1)

52. 0.216 0.239 ↓ 5.0 5 1

Bitmap Heap Scan on notifications_summary_org_part_default live_ntfn (cost=4.24..11.57 rows=1 width=40) (actual time=0.188..0.239 rows=5 loops=1)

  • Recheck Cond: (event_uuid = get_event.event_uuid)
  • Filter: ((created_date >= $13) AND (pending_date <= '2020-09-04 10:12:18-07'::timestamp with time zone) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid) AND (category = 'LIVE'::text) AND (created_date <= LEAST('2020-09-04 10:12:18-07'::timestamp with time zone, $12)))
  • Rows Removed by Filter: 22
  • Heap Blocks: exact=1
53. 0.023 0.023 ↓ 2.2 27 1

Bitmap Index Scan on notifications_summary_org_part_default_event_uuid_idx (cost=0.00..4.24 rows=12 width=0) (actual time=0.016..0.023 rows=27 loops=1)

  • Index Cond: (event_uuid = get_event.event_uuid)
54. 0.195 0.495 ↑ 1.0 1 5

Append (cost=0.27..8.31 rows=1 width=64) (actual time=0.056..0.099 rows=1 loops=5)

55. 0.300 0.300 ↑ 1.0 1 5

Index Scan using notifications_summary_org_part_default_pkey on notifications_summary_org_part_default device_ntfn (cost=0.27..8.30 rows=1 width=64) (actual time=0.039..0.060 rows=1 loops=5)

  • Index Cond: ((organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid) AND (notification_uuid = live_ntfn.parent_ntfn_uuid))
  • Filter: ((created_date >= $9) AND (category = 'DEVICE'::text) AND (created_date <= LEAST('2020-09-04 10:12:18-07'::timestamp with time zone, $8)))
56. 0.175 0.500 ↑ 1.0 1 5

Append (cost=0.27..8.31 rows=1 width=72) (actual time=0.067..0.100 rows=1 loops=5)

57. 0.325 0.325 ↑ 1.0 1 5

Index Scan using notifications_summary_org_part_default_pkey on notifications_summary_org_part_default person_ntfn (cost=0.27..8.30 rows=1 width=72) (actual time=0.049..0.065 rows=1 loops=5)

  • Index Cond: ((organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid) AND (notification_uuid = device_ntfn.parent_ntfn_uuid))
  • Filter: ((created_date >= $11) AND (category = 'PERSON'::text) AND (created_date <= LEAST('2020-09-04 10:12:18-07'::timestamp with time zone, $10)))
58.          

SubPlan (forNested Loop)

59. 0.135 1.345 ↓ 0.0 0 5

HashAggregate (cost=700.50..701.77 rows=127 width=32) (actual time=0.266..0.269 rows=0 loops=5)

  • Group Key: get_response_choices.text_response
60. 1.210 1.210 ↑ 200.0 1 5

CTE Scan on get_response_choices (cost=0.00..700.00 rows=200 width=32) (actual time=0.232..0.242 rows=1 loops=5)

  • Filter: ((lower((device_response.response)::text) = lower(text_response)) OR (lower((device_response.response)::text) = lower(translated_response)))
  • Rows Removed by Filter: 8
61. 0.000 5.532 ↑ 1.0 1 1

Hash Join (cost=239.93..253.73 rows=1 width=295) (actual time=5.501..5.532 rows=1 loops=1)

  • Hash Cond: (COALESCE(rc_1.translated_response, rc_1.text_response) = (pr.response)::text)
62.          

Initplan (forHash Join)

63. 0.034 0.056 ↑ 1.0 1 1

Aggregate (cost=0.02..0.04 rows=1 width=8) (actual time=0.041..0.056 rows=1 loops=1)

64. 0.022 0.022 ↑ 1.0 1 1

CTE Scan on get_event e_9 (cost=0.00..0.02 rows=1 width=8) (actual time=0.008..0.022 rows=1 loops=1)

65. 0.036 0.061 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.046..0.061 rows=1 loops=1)

66. 0.025 0.025 ↑ 1.0 1 1

CTE Scan on get_event e_10 (cost=0.00..0.02 rows=1 width=8) (actual time=0.009..0.025 rows=1 loops=1)

67. 0.064 0.629 ↑ 1,000.0 1 1

HashAggregate (cost=225.00..235.00 rows=1,000 width=64) (actual time=0.615..0.629 rows=1 loops=1)

  • Group Key: COALESCE(rc_1.translated_response, rc_1.text_response)
68. 0.565 0.565 ↑ 3,333.3 3 1

CTE Scan on get_filtered_response_choices rc_1 (cost=0.00..200.00 rows=10,000 width=64) (actual time=0.251..0.565 rows=3 loops=1)

69. 0.038 4.852 ↓ 2.0 2 1

Hash (cost=14.85..14.85 rows=1 width=111) (actual time=4.845..4.852 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
70. 1.853 4.814 ↓ 2.0 2 1

Nested Loop (cost=4.26..14.85 rows=1 width=111) (actual time=0.557..4.814 rows=2 loops=1)

  • Join Filter: (person_resp_ntfn.notification_uuid = pr.notification_uuid)
  • Rows Removed by Join Filter: 168
71. 0.000 0.711 ↓ 10.0 10 1

Nested Loop (cost=4.26..11.59 rows=1 width=72) (actual time=0.260..0.711 rows=10 loops=1)

72. 0.327 0.365 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=0.045..0.365 rows=1 loops=1)

  • Group Key: get_event_1.event_uuid
73. 0.038 0.038 ↑ 1.0 1 1

CTE Scan on get_event get_event_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.009..0.038 rows=1 loops=1)

74. 0.182 0.458 ↓ 10.0 10 1

Append (cost=4.24..11.54 rows=1 width=72) (actual time=0.189..0.458 rows=10 loops=1)

75. 0.257 0.276 ↓ 10.0 10 1

Bitmap Heap Scan on notifications_summary_org_part_default person_resp_ntfn (cost=4.24..11.54 rows=1 width=72) (actual time=0.168..0.276 rows=10 loops=1)

  • Recheck Cond: (event_uuid = get_event_1.event_uuid)
  • Filter: ((created_date >= $15) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid) AND (category = 'PERSON'::text) AND (created_date <= LEAST('2020-09-04 10:12:18-07'::timestamp with time zone, $14)))
  • Rows Removed by Filter: 17
  • Heap Blocks: exact=1
76. 0.019 0.019 ↓ 2.2 27 1

Bitmap Index Scan on notifications_summary_org_part_default_event_uuid_idx (cost=0.00..4.24 rows=12 width=0) (actual time=0.012..0.019 rows=27 loops=1)

  • Index Cond: (event_uuid = get_event_1.event_uuid)
77. 2.250 2.250 ↓ 1.2 17 10

Seq Scan on responses pr (cost=0.00..3.08 rows=14 width=55) (actual time=0.014..0.225 rows=17 loops=10)

  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2020-09-04 10:12:18-07'::timestamp with time zone) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid) AND ((recipient_type)::text = 'PERSON'::text))
  • Rows Removed by Filter: 51
78.          

CTE get_teams

79. 0.078 2.907 ↓ 3.0 3 1

GroupAggregate (cost=13.56..13.58 rows=1 width=56) (actual time=2.840..2.907 rows=3 loops=1)

  • Group Key: team_ntfns.team_ntfn_uuid, team_ntfns.team_parent_ntfn_uuid
80. 0.080 2.829 ↓ 3.0 3 1

Sort (cost=13.56..13.56 rows=1 width=56) (actual time=2.797..2.829 rows=3 loops=1)

  • Sort Key: team_ntfns.team_ntfn_uuid, team_ntfns.team_parent_ntfn_uuid
  • Sort Method: quicksort Memory: 25kB
81. 0.075 2.749 ↓ 3.0 3 1

Subquery Scan on team_ntfns (cost=13.50..13.55 rows=1 width=56) (actual time=2.634..2.749 rows=3 loops=1)

82. 0.082 2.674 ↓ 3.0 3 1

GroupAggregate (cost=13.50..13.54 rows=1 width=72) (actual time=2.607..2.674 rows=3 loops=1)

  • Group Key: en.person_ntfn_uuid, en.team_ntfn_uuid, en.team_parent_ntfn_uuid
83. 0.082 2.592 ↓ 3.0 3 1

Sort (cost=13.50..13.51 rows=1 width=80) (actual time=2.560..2.592 rows=3 loops=1)

  • Sort Key: en.person_ntfn_uuid, en.team_ntfn_uuid, en.team_parent_ntfn_uuid
  • Sort Method: quicksort Memory: 25kB
84. 0.072 2.510 ↓ 3.0 3 1

Subquery Scan on en (cost=13.45..13.49 rows=1 width=80) (actual time=2.359..2.510 rows=3 loops=1)

85. 0.000 2.438 ↓ 3.0 3 1

GroupAggregate (cost=13.45..13.48 rows=1 width=128) (actual time=2.337..2.438 rows=3 loops=1)

  • Group Key: n.person_uuid, n.person_ntfn_uuid, team_ntfn.notification_uuid, team_ntfn.parent_ntfn_uuid
86.          

Initplan (forGroupAggregate)

87. 0.072 0.116 ↑ 1.0 1 1

Aggregate (cost=0.02..0.04 rows=1 width=8) (actual time=0.094..0.116 rows=1 loops=1)

88. 0.044 0.044 ↑ 1.0 1 1

CTE Scan on get_event e_11 (cost=0.00..0.02 rows=1 width=8) (actual time=0.020..0.044 rows=1 loops=1)

89. 0.075 0.123 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.103..0.123 rows=1 loops=1)

90. 0.048 0.048 ↑ 1.0 1 1

CTE Scan on get_event e_12 (cost=0.00..0.02 rows=1 width=8) (actual time=0.022..0.048 rows=1 loops=1)

91. 0.121 2.337 ↓ 5.0 5 1

Sort (cost=13.38..13.39 rows=1 width=96) (actual time=2.290..2.337 rows=5 loops=1)

  • Sort Key: n.person_uuid, n.person_ntfn_uuid, team_ntfn.notification_uuid, team_ntfn.parent_ntfn_uuid
  • Sort Method: quicksort Memory: 25kB
92. 0.870 2.216 ↓ 5.0 5 1

Nested Loop (cost=0.00..13.37 rows=1 width=96) (actual time=0.994..2.216 rows=5 loops=1)

  • Join Filter: (n.team_uuid = team_ntfn.notification_uuid)
  • Rows Removed by Join Filter: 49
93. 0.171 0.770 ↓ 9.0 9 1

Append (cost=0.00..13.31 rows=1 width=32) (actual time=0.511..0.770 rows=9 loops=1)

94. 0.599 0.599 ↓ 9.0 9 1

Seq Scan on notifications_summary_org_part_default team_ntfn (cost=0.00..13.30 rows=1 width=32) (actual time=0.485..0.599 rows=9 loops=1)

  • Filter: ((created_date >= $22) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid) AND (category = 'TEAM'::text) AND (created_date <= LEAST('2020-09-04 10:12:18-07'::timestamp with time zone, $21)))
  • Rows Removed by Filter: 260
95. 0.576 0.576 ↓ 3.0 6 9

CTE Scan on get_person_device_notifications n (cost=0.00..0.04 rows=2 width=80) (actual time=0.010..0.064 rows=6 loops=9)

96.          

CTE get_team_groups

97. 0.138 6.626 ↑ 1.8 6 1

Recursive Union (cost=0.36..145.64 rows=11 width=74) (actual time=3.274..6.626 rows=6 loops=1)

98. 0.000 3.504 ↓ 3.0 3 1

Nested Loop (cost=0.36..8.42 rows=1 width=74) (actual time=3.247..3.504 rows=3 loops=1)

99.          

Initplan (forNested Loop)

100. 0.038 0.062 ↑ 1.0 1 1

Aggregate (cost=0.02..0.04 rows=1 width=8) (actual time=0.047..0.062 rows=1 loops=1)

101. 0.024 0.024 ↑ 1.0 1 1

CTE Scan on get_event e_13 (cost=0.00..0.02 rows=1 width=8) (actual time=0.009..0.024 rows=1 loops=1)

102. 0.036 0.060 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.045..0.060 rows=1 loops=1)

103. 0.024 0.024 ↑ 1.0 1 1

CTE Scan on get_event e_14 (cost=0.00..0.02 rows=1 width=8) (actual time=0.009..0.024 rows=1 loops=1)

104. 0.077 3.060 ↓ 3.0 3 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=3.026..3.060 rows=3 loops=1)

  • Group Key: get_teams.team_ntfn_uuid
105. 2.983 2.983 ↓ 3.0 3 1

CTE Scan on get_teams (cost=0.00..0.02 rows=1 width=16) (actual time=2.865..2.983 rows=3 loops=1)

106. 0.093 0.327 ↑ 1.0 1 3

Append (cost=0.27..8.31 rows=1 width=54) (actual time=0.082..0.109 rows=1 loops=3)

107. 0.234 0.234 ↑ 1.0 1 3

Index Scan using notifications_summary_org_part_default_pkey on notifications_summary_org_part_default team_ntfn_1 (cost=0.27..8.30 rows=1 width=54) (actual time=0.066..0.078 rows=1 loops=3)

  • Index Cond: ((organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid) AND (notification_uuid = get_teams.team_ntfn_uuid))
  • Filter: ((created_date >= $26) AND (category = 'TEAM'::text) AND (created_date <= LEAST('2020-09-04 10:12:18-07'::timestamp with time zone, $25)))
108. 1.050 2.984 ↓ 2.0 2 2

Nested Loop (cost=0.07..13.70 rows=1 width=74) (actual time=0.883..1.492 rows=2 loops=2)

  • Join Filter: (n_1.notification_uuid = tg_1.parent_ntfn_uuid)
  • Rows Removed by Join Filter: 40
109.          

Initplan (forNested Loop)

110. 0.037 0.062 ↑ 1.0 1 1

Aggregate (cost=0.02..0.04 rows=1 width=8) (actual time=0.047..0.062 rows=1 loops=1)

111. 0.025 0.025 ↑ 1.0 1 1

CTE Scan on get_event e_15 (cost=0.00..0.02 rows=1 width=8) (actual time=0.009..0.025 rows=1 loops=1)

112. 0.034 0.058 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.043..0.058 rows=1 loops=1)

113. 0.024 0.024 ↑ 1.0 1 1

CTE Scan on get_event e_16 (cost=0.00..0.02 rows=1 width=8) (actual time=0.009..0.024 rows=1 loops=1)

114. 0.444 0.946 ↓ 14.0 14 2

Append (cost=0.00..13.31 rows=1 width=54) (actual time=0.130..0.473 rows=14 loops=2)

115. 0.502 0.502 ↓ 14.0 14 2

Seq Scan on notifications_summary_org_part_default n_1 (cost=0.00..13.30 rows=1 width=54) (actual time=0.114..0.251 rows=14 loops=2)

  • Filter: ((created_date >= $28) AND (category = ANY ('{TEAM,GROUP}'::text[])) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid) AND (created_date <= LEAST('2020-09-04 10:12:18-07'::timestamp with time zone, $27)))
  • Rows Removed by Filter: 255
116. 0.868 0.868 ↑ 3.3 3 28

WorkTable Scan on get_team_groups tg_1 (cost=0.00..0.20 rows=10 width=36) (actual time=0.008..0.031 rows=3 loops=28)

117.          

CTE get_targeted_groups

118. 0.288 8.141 ↓ 2.0 2 1

Nested Loop Left Join (cost=0.37..2.94 rows=1 width=129) (actual time=7.513..8.141 rows=2 loops=1)

  • Join Filter: (g.group_uuid = team_group.recipient_uuid)
  • Rows Removed by Join Filter: 26
119.          

Initplan (forNested Loop Left Join)

120. 0.031 0.054 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.040..0.054 rows=1 loops=1)

121. 0.023 0.023 ↑ 1.0 1 1

CTE Scan on get_event e_17 (cost=0.00..0.02 rows=1 width=8) (actual time=0.008..0.023 rows=1 loops=1)

122. 0.156 7.363 ↓ 2.0 2 1

HashAggregate (cost=0.34..0.35 rows=1 width=88) (actual time=7.337..7.363 rows=2 loops=1)

  • Group Key: team_group.notification_uuid, team_group.recipient_uuid
123. 0.148 7.207 ↓ 3.0 3 1

Nested Loop (cost=0.26..0.33 rows=1 width=72) (actual time=6.895..7.207 rows=3 loops=1)

  • Join Filter: (team_group.root_uuid = team.team_ntfn_uuid)
  • Rows Removed by Join Filter: 6
124. 0.068 6.963 ↓ 3.0 3 1

Subquery Scan on team_group (cost=0.26..0.29 rows=1 width=48) (actual time=6.857..6.963 rows=3 loops=1)

  • Filter: (team_group."row" = 1)
125. 0.076 6.895 ↓ 3.0 3 1

WindowAgg (cost=0.26..0.28 rows=1 width=60) (actual time=6.832..6.895 rows=3 loops=1)

126. 0.092 6.819 ↓ 3.0 3 1

Sort (cost=0.26..0.26 rows=1 width=52) (actual time=6.790..6.819 rows=3 loops=1)

  • Sort Key: tg_2.root_uuid, tg_2.group_level DESC
  • Sort Method: quicksort Memory: 25kB
127. 6.727 6.727 ↓ 3.0 3 1

CTE Scan on get_team_groups tg_2 (cost=0.00..0.25 rows=1 width=52) (actual time=4.091..6.727 rows=3 loops=1)

  • Filter: (category = 'GROUP'::text)
  • Rows Removed by Filter: 3
128. 0.096 0.096 ↓ 3.0 3 3

CTE Scan on get_teams team (cost=0.00..0.02 rows=1 width=40) (actual time=0.007..0.032 rows=3 loops=3)

129. 0.436 0.436 ↓ 14.0 14 2

Seq Scan on groups g (cost=0.00..2.52 rows=1 width=25) (actual time=0.086..0.218 rows=14 loops=2)

  • Filter: ((is_visible IS TRUE) AND (valid_range @> $31) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid))
  • Rows Removed by Filter: 23
130.          

CTE get_total_person_uuids

131. 0.054 3.238 ↓ 2.0 2 1

Hash Join (cost=241.03..254.89 rows=1 width=177) (actual time=3.195..3.238 rows=2 loops=1)

  • Hash Cond: (COALESCE(rc_2.translated_response, rc_2.text_response) = (resp.response)::text)
132. 0.062 0.105 ↑ 1,000.0 1 1

HashAggregate (cost=225.00..235.00 rows=1,000 width=64) (actual time=0.089..0.105 rows=1 loops=1)

  • Group Key: COALESCE(rc_2.translated_response, rc_2.text_response)
133. 0.043 0.043 ↑ 3,333.3 3 1

CTE Scan on get_filtered_response_choices rc_2 (cost=0.00..200.00 rows=10,000 width=64) (actual time=0.010..0.043 rows=3 loops=1)

134. 0.044 3.079 ↑ 1.0 2 1

Hash (cost=16.00..16.00 rows=2 width=145) (actual time=3.071..3.079 rows=2 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
135. 0.192 3.035 ↑ 1.0 2 1

Hash Join (cost=3.72..16.00 rows=2 width=145) (actual time=2.348..3.035 rows=2 loops=1)

  • Hash Cond: ((SubPlan 24) = resp.notification_uuid)
136. 0.235 0.647 ↓ 1.5 3 1

GroupAggregate (cost=0.05..0.12 rows=2 width=136) (actual time=0.407..0.647 rows=3 loops=1)

  • Group Key: en_1.person_uuid
137. 0.128 0.412 ↓ 3.0 6 1

Sort (cost=0.05..0.06 rows=2 width=104) (actual time=0.354..0.412 rows=6 loops=1)

  • Sort Key: en_1.person_uuid
  • Sort Method: quicksort Memory: 25kB
138. 0.284 0.284 ↓ 3.0 6 1

CTE Scan on get_person_device_notifications en_1 (cost=0.00..0.04 rows=2 width=104) (actual time=0.009..0.284 rows=6 loops=1)

139. 0.726 1.521 ↓ 1.1 66 1

Hash (cost=2.93..2.93 rows=59 width=25) (actual time=1.514..1.521 rows=66 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
140. 0.795 0.795 ↓ 1.1 66 1

Seq Scan on responses resp (cost=0.00..2.93 rows=59 width=25) (actual time=0.018..0.795 rows=66 loops=1)

  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2020-09-04 10:12:18-07'::timestamp with time zone) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid))
  • Rows Removed by Filter: 2
141.          

SubPlan (forHash Join)

142. 0.220 0.675 ↑ 1.0 1 5

Limit (cost=4.05..4.05 rows=1 width=24) (actual time=0.098..0.135 rows=1 loops=5)

143. 0.140 0.455 ↑ 7.0 1 5

Sort (cost=4.05..4.07 rows=7 width=24) (actual time=0.081..0.091 rows=1 loops=5)

  • Sort Key: r.received DESC
  • Sort Method: quicksort Memory: 25kB
144. 0.315 0.315 ↑ 7.0 1 5

Seq Scan on responses r (cost=0.00..4.02 rows=7 width=24) (actual time=0.044..0.063 rows=1 loops=5)

  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2020-09-04 10:12:18-07'::timestamp with time zone) AND ((response)::text <> 'XMATTERS_REMOVE'::text) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid) AND (notification_uuid = ANY (((array_agg(DISTINCT en_1.device_ntfn_uuid) FILTER (WHERE (en_1.device_ntfn_uuid IS NOT NULL))) || (array_agg(DISTINCT en_1.person_ntfn_uuid) FILTER (WHERE (en_1.person_ntfn_uuid IS NOT NULL)))))))
  • Rows Removed by Filter: 67
145.          

CTE get_person_uuids

146. 0.051 3.402 ↓ 2.0 2 1

Limit (cost=0.03..0.04 rows=1 width=200) (actual time=3.343..3.402 rows=2 loops=1)

147. 0.061 3.351 ↓ 2.0 2 1

Sort (cost=0.03..0.04 rows=1 width=200) (actual time=3.325..3.351 rows=2 loops=1)

  • Sort Key: en_agg.delivery_status DESC, en_agg.delivered_date_min DESC
  • Sort Method: quicksort Memory: 25kB
148. 3.290 3.290 ↓ 2.0 2 1

CTE Scan on get_total_person_uuids en_agg (cost=0.00..0.02 rows=1 width=200) (actual time=3.213..3.290 rows=2 loops=1)

149.          

CTE get_person_and_roles

150. 0.000 3.103 ↓ 2.0 2 1

Nested Loop Left Join (cost=18.16..23.15 rows=1 width=104) (actual time=1.485..3.103 rows=2 loops=1)

  • Join Filter: (site.site_uuid = p.site_uuid)
  • Rows Removed by Join Filter: 2
151.          

Initplan (forNested Loop Left Join)

152. 0.036 0.061 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.045..0.061 rows=1 loops=1)

153. 0.025 0.025 ↑ 1.0 1 1

CTE Scan on get_event e_18 (cost=0.00..0.02 rows=1 width=8) (actual time=0.009..0.025 rows=1 loops=1)

154. 0.041 0.068 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.052..0.068 rows=1 loops=1)

155. 0.027 0.027 ↑ 1.0 1 1

CTE Scan on get_event e_19 (cost=0.00..0.02 rows=1 width=8) (actual time=0.011..0.027 rows=1 loops=1)

156. 0.092 2.819 ↓ 2.0 2 1

Nested Loop Left Join (cost=18.09..22.04 rows=1 width=91) (actual time=1.338..2.819 rows=2 loops=1)

157. 0.669 1.451 ↓ 2.0 2 1

Nested Loop Semi Join (cost=0.00..3.92 rows=1 width=78) (actual time=0.453..1.451 rows=2 loops=1)

  • Join Filter: (p.person_uuid = get_person_uuids_1.person_uuid)
  • Rows Removed by Join Filter: 33
158. 0.314 0.314 ↓ 18.0 18 1

Seq Scan on persons p (cost=0.00..3.88 rows=1 width=78) (actual time=0.093..0.314 rows=18 loops=1)

  • Filter: ((is_visible IS TRUE) AND (valid_range @> $37) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid))
  • Rows Removed by Filter: 41
159. 0.468 0.468 ↓ 2.0 2 18

CTE Scan on get_person_uuids get_person_uuids_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.009..0.026 rows=2 loops=18)

160. 0.168 1.276 ↑ 1.0 1 2

Aggregate (cost=18.09..18.10 rows=1 width=32) (actual time=0.625..0.638 rows=1 loops=2)

161. 0.090 1.108 ↑ 100.0 1 2

Hash Left Join (cost=5.00..13.59 rows=100 width=57) (actual time=0.448..0.554 rows=1 loops=2)

  • Hash Cond: ((((jsonb_array_elements_text(pr_1.roles)))::uuid) = role.role_uuid)
162. 0.110 0.492 ↑ 100.0 1 2

Nested Loop Left Join (cost=0.00..7.49 rows=100 width=16) (actual time=0.158..0.246 rows=1 loops=2)

163. 0.098 0.098 ↑ 1.0 1 2

Seq Scan on person_roles pr_1 (cost=0.00..3.23 rows=1 width=50) (actual time=0.030..0.049 rows=1 loops=2)

  • Filter: (upper_inf(valid_range) AND (person_uuid = p.person_uuid) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid))
  • Rows Removed by Filter: 69
164. 0.082 0.284 ↑ 100.0 1 2

Result (cost=0.00..2.27 rows=100 width=16) (actual time=0.094..0.142 rows=1 loops=2)

165. 0.148 0.202 ↑ 100.0 1 2

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.069..0.101 rows=1 loops=2)

166. 0.054 0.054 ↑ 1.0 1 2

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.015..0.027 rows=1 loops=2)

167. 0.251 0.526 ↑ 1.3 26 1

Hash (cost=4.56..4.56 rows=35 width=57) (actual time=0.518..0.526 rows=26 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
168. 0.275 0.275 ↑ 1.3 26 1

Seq Scan on roles role (cost=0.00..4.56 rows=35 width=57) (actual time=0.018..0.275 rows=26 loops=1)

  • Filter: (upper_inf(valid_range) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid))
  • Rows Removed by Filter: 78
169. 0.176 0.176 ↓ 2.0 2 2

Seq Scan on sites site (cost=0.00..1.03 rows=1 width=29) (actual time=0.065..0.088 rows=2 loops=2)

  • Filter: ((is_visible IS TRUE) AND (valid_range @> $38) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid))
170.          

CTE get_person_notifications

171. 0.093 18.610 ↓ 2.0 2 1

Subquery Scan on p_1 (cost=5.61..5.70 rows=1 width=304) (actual time=18.469..18.610 rows=2 loops=1)

172. 0.494 18.517 ↓ 2.0 2 1

GroupAggregate (cost=5.61..5.68 rows=1 width=272) (actual time=18.432..18.517 rows=2 loops=1)

  • Group Key: get_person_device_notifications.person_uuid, get_person_device_notifications.person_ntfn_uuid, get_person_device_notifications.person_delinked_date, get_person_device_notifications.team_uuid, get_person_device_notifications.event_uuid, get_person_device_notifications.person_response_value, get_person_device_notifications.person_response_received, get_person_device_notifications.person_response_recipient_type
173.          

Initplan (forGroupAggregate)

174. 0.037 0.062 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.046..0.062 rows=1 loops=1)

175. 0.025 0.025 ↑ 1.0 1 1

CTE Scan on get_event e_20 (cost=0.00..0.02 rows=1 width=8) (actual time=0.009..0.025 rows=1 loops=1)

176. 0.077 17.961 ↓ 2.0 2 1

Sort (cost=5.58..5.58 rows=1 width=325) (actual time=17.923..17.961 rows=2 loops=1)

  • Sort Key: get_person_device_notifications.person_uuid, get_person_device_notifications.person_ntfn_uuid, get_person_device_notifications.person_delinked_date, get_person_device_notifications.team_uuid, get_person_device_notifications.event_uuid, get_person_device_notifications.person_response_value, get_person_device_notifications.person_response_received, get_person_device_notifications.person_response_recipient_type
  • Sort Method: quicksort Memory: 25kB
177. 0.238 17.884 ↓ 2.0 2 1

Nested Loop Semi Join (cost=0.00..5.57 rows=1 width=325) (actual time=14.576..17.884 rows=2 loops=1)

  • Join Filter: (get_person_device_notifications.person_uuid = get_person_uuids_2.person_uuid)
  • Rows Removed by Join Filter: 9
178. 1.514 14.070 ↓ 3.0 6 1

Nested Loop Left Join (cost=0.00..5.51 rows=2 width=325) (actual time=11.140..14.070 rows=6 loops=1)

  • Join Filter: (d.recipient_uuid = get_person_device_notifications.device_uuid)
  • Rows Removed by Join Filter: 115
179. 10.768 10.768 ↓ 3.0 6 1

CTE Scan on get_person_device_notifications (cost=0.00..0.04 rows=2 width=280) (actual time=10.695..10.768 rows=6 loops=1)

180. 1.442 1.788 ↓ 20.0 20 6

Materialize (cost=0.00..5.44 rows=1 width=61) (actual time=0.027..0.298 rows=20 loops=6)

181. 0.346 0.346 ↓ 20.0 20 1

Seq Scan on devices d (cost=0.00..5.44 rows=1 width=61) (actual time=0.097..0.346 rows=20 loops=1)

  • Filter: ((is_visible IS TRUE) AND (valid_range @> $43) AND (organization_uuid = '539525d4-1fe5-4075-abf2-8b92eae0e96b'::uuid))
  • Rows Removed by Filter: 76
182. 3.576 3.576 ↓ 2.0 2 6

CTE Scan on get_person_uuids get_person_uuids_2 (cost=0.00..0.02 rows=1 width=16) (actual time=0.569..0.596 rows=2 loops=6)

183.          

Initplan (forSort)

184. 0.039 0.069 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.055..0.069 rows=1 loops=1)

185. 0.030 0.030 ↓ 2.0 2 1

CTE Scan on get_total_person_uuids (cost=0.00..0.02 rows=1 width=0) (actual time=0.008..0.030 rows=2 loops=1)

186. 0.023 0.023 ↑ 1.0 1 1

CTE Scan on get_event get_event_2 (cost=0.00..0.02 rows=1 width=16) (actual time=0.009..0.023 rows=1 loops=1)

187. 0.198 32.024 ↓ 2.0 2 1

Nested Loop Left Join (cost=354.35..708.42 rows=1 width=464) (actual time=30.329..32.024 rows=2 loops=1)

  • Join Filter: (get_person_uuids.person_uuid = pn.person_uuid)
  • Rows Removed by Join Filter: 2
188. 0.092 31.774 ↓ 2.0 2 1

Nested Loop Left Join (cost=354.35..708.37 rows=1 width=440) (actual time=30.166..31.774 rows=2 loops=1)

  • Join Filter: (get_person_and_roles.person_uuid = pn.person_uuid)
  • Rows Removed by Join Filter: 2
189. 0.078 28.498 ↓ 2.0 2 1

Nested Loop Left Join (cost=354.35..708.34 rows=1 width=232) (actual time=27.765..28.498 rows=2 loops=1)

190. 0.102 20.126 ↓ 2.0 2 1

Nested Loop Left Join (cost=354.32..708.28 rows=1 width=232) (actual time=19.486..20.126 rows=2 loops=1)

191. 0.099 19.734 ↓ 2.0 2 1

Nested Loop Left Join (cost=176.55..530.48 rows=1 width=232) (actual time=19.244..19.734 rows=2 loops=1)

192. 0.257 19.257 ↓ 2.0 2 1

GroupAggregate (cost=0.03..353.92 rows=1 width=248) (actual time=18.919..19.257 rows=2 loops=1)

  • Group Key: pn.person_uuid, pn.event_uuid
193. 0.094 18.806 ↓ 2.0 2 1

Sort (cost=0.03..0.04 rows=1 width=288) (actual time=18.774..18.806 rows=2 loops=1)

  • Sort Key: pn.person_uuid, pn.event_uuid
  • Sort Method: quicksort Memory: 26kB
194. 18.712 18.712 ↓ 2.0 2 1

CTE Scan on get_person_notifications pn (cost=0.00..0.02 rows=1 width=288) (actual time=18.502..18.712 rows=2 loops=1)

195.          

SubPlan (forGroupAggregate)

196. 0.053 0.194 ↑ 79.0 1 1

Unique (cost=353.32..353.82 rows=79 width=32) (actual time=0.129..0.194 rows=1 loops=1)

197. 0.079 0.141 ↑ 33.3 3 1

Sort (cost=353.32..353.57 rows=100 width=32) (actual time=0.111..0.141 rows=3 loops=1)

  • Sort Key: get_filtered_response_choices.text_response
  • Sort Method: quicksort Memory: 25kB
198. 0.062 0.062 ↑ 33.3 3 1

CTE Scan on get_filtered_response_choices (cost=0.00..350.00 rows=100 width=32) (actual time=0.027..0.062 rows=3 loops=1)

  • Filter: ((lower((pn.person_response_value)::text) = lower(text_response)) OR (lower((pn.person_response_value)::text) = lower(translated_response)))
199. 0.142 0.378 ↑ 1.0 1 2

Aggregate (cost=176.52..176.53 rows=1 width=32) (actual time=0.178..0.189 rows=1 loops=2)

200. 0.082 0.236 ↓ 0.0 0 2

ProjectSet (cost=0.00..51.52 rows=10,000 width=32) (actual time=0.085..0.118 rows=0 loops=2)

201. 0.102 0.154 ↓ 0.0 0 2

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.055..0.077 rows=0 loops=2)

202. 0.052 0.052 ↑ 1.0 1 2

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.011..0.026 rows=1 loops=2)

203. 0.092 0.290 ↑ 1.0 1 2

Aggregate (cost=177.77..177.78 rows=1 width=32) (actual time=0.135..0.145 rows=1 loops=2)

204. 0.074 0.198 ↓ 0.0 0 2

ProjectSet (cost=0.00..52.77 rows=10,000 width=32) (actual time=0.066..0.099 rows=0 loops=2)

205. 0.078 0.124 ↓ 0.0 0 2

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.041..0.062 rows=0 loops=2)

206. 0.046 0.046 ↑ 1.0 1 2

Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.010..0.023 rows=1 loops=2)

207. 0.092 8.294 ↑ 1.0 1 2

Aggregate (cost=0.03..0.04 rows=1 width=32) (actual time=4.136..4.147 rows=1 loops=2)

208. 8.202 8.202 ↓ 0.0 0 2

CTE Scan on get_targeted_groups tg (cost=0.00..0.02 rows=1 width=120) (actual time=3.774..4.101 rows=0 loops=2)

  • Filter: (team_ntfn_uuids && (array_agg(pn.team_uuid)))
  • Rows Removed by Filter: 2
209. 3.184 3.184 ↓ 2.0 2 2

CTE Scan on get_person_and_roles (cost=0.00..0.02 rows=1 width=224) (actual time=0.757..1.592 rows=2 loops=2)

210. 0.052 0.052 ↓ 2.0 2 2

CTE Scan on get_person_uuids (cost=0.00..0.02 rows=1 width=48) (actual time=0.008..0.026 rows=2 loops=2)

Planning time : 10.553 ms
Execution time : 36.193 ms