explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bAjB

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 81,819.281 ↑ 20.9 10 1

Sort (cost=1,356,990.74..1,356,991.26 rows=209 width=464) (actual time=81,819.280..81,819.281 rows=10 loops=1)

  • Sort Key: get_person_uuids.last_response DESC NULLS LAST, (min(pn.device_delivered_date_min)) DESC
  • Sort Method: quicksort Memory: 45kB
2.          

CTE get_event

3. 0.001 0.119 ↑ 1.0 1 1

Append (cost=0.56..3.58 rows=1 width=737) (actual time=0.118..0.119 rows=1 loops=1)

4. 0.118 0.118 ↑ 1.0 1 1

Index Scan using events_org_part_default_evn_seq_vrange_uidx on events_org_part_default e (cost=0.56..3.58 rows=1 width=737) (actual time=0.117..0.118 rows=1 loops=1)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (event_uuid = 'f20cf6ff-64e4-4a07-a655-2c276bc37ba9'::uuid))
  • Filter: (valid_range @> '2019-09-04 17:12:18+00'::timestamp with time zone)
  • Rows Removed by Filter: 1
5.          

CTE get_response_choices

6. 0.001 0.281 ↑ 20,000.0 1 1

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

7. 0.007 0.278 ↑ 10,000.0 1 1

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

8. 0.004 0.130 ↑ 1.0 1 1

Nested Loop (cost=0.42..3.47 rows=1 width=986) (actual time=0.121..0.130 rows=1 loops=1)

9. 0.002 0.002 ↑ 1.0 1 1

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

  • Filter: (choices IS NULL)
10. 0.124 0.124 ↑ 1.0 1 1

Index Scan using forms_org_seq_vrange_uidx on forms f (cost=0.42..3.44 rows=1 width=1,021) (actual time=0.116..0.124 rows=1 loops=1)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (form_uuid = e_1.form_uuid))
  • Filter: (valid_range @> e_1.created)
  • Rows Removed by Filter: 2
11. 0.048 0.141 ↑ 10,000.0 1 1

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

12. 0.092 0.093 ↑ 100.0 1 1

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

13. 0.001 0.001 ↑ 1.0 1 1

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

14. 0.001 0.002 ↓ 0.0 0 1

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

15. 0.001 0.001 ↓ 0.0 0 1

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

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

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

17. 0.000 0.000 ↓ 0.0 0

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

18. 0.000 0.000 ↓ 0.0 0

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

19.          

CTE get_filtered_response_choices

20. 0.016 0.046 ↑ 10,000.0 1 1

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

  • Hash Cond: (rc.text_response = rc2.text_response)
21. 0.002 0.002 ↑ 20,000.0 1 1

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

22. 0.008 0.028 ↑ 127.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
23. 0.005 0.020 ↑ 127.0 1 1

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

  • Group Key: rc2.text_response
24. 0.015 0.015 ↑ 200.0 1 1

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

  • Filter: ((lower(translated_response) = 'acknowledge'::text) OR (lower(text_response) = 'acknowledge'::text))
25.          

CTE get_person_device_notifications

26. 4.105 291.703 ↓ 4,303.5 8,607 1

Unique (cost=1,080.60..1,080.71 rows=2 width=344) (actual time=285.965..291.703 rows=8,607 loops=1)

27. 17.801 287.598 ↓ 4,303.5 8,607 1

Sort (cost=1,080.60..1,080.61 rows=2 width=344) (actual time=285.962..287.598 rows=8,607 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 <= '2019-09-04 17:12:18+00'::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 <= '2019-09-04 17:12:18+00'::timestamp with time zone) THEN device_ntfn.failed_date ELSE NULL::timestamp with time zone END), (CASE WHEN (device_ntfn.delivered_date <= '2019-09-04 17:12:18+00'::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 <= '2019-09-04 17:12:18+00'::timestamp with time zone)) THEN device_response.received ELSE NULL::timestamp with time zone END), (CASE WHEN ((device_response.received <= '2019-09-04 17:12:18+00'::timestamp with time zone) AND ((device_response.response)::text <> 'XMATTERS_REMOVE'::text)) THEN 'RESPONDED'::text WHEN (device_ntfn.delivered_date <= '2019-09-04 17:12:18+00'::timestamp with time zone) THEN 'DELIVERED'::text WHEN (device_ntfn.failed_date <= '2019-09-04 17:12:18+00'::timestamp with time zone) THEN 'FAILED'::text WHEN (live_ntfn.pending_date <= '2019-09-04 17:12:18+00'::timestamp with time zone) THEN 'PENDING'::text ELSE ''::text END), (CASE WHEN (live_ntfn.pending_date <= '2019-09-04 17:12:18+00'::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 <= '2019-09-04 17:12:18+00'::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: 2671kB
28. 0.847 269.797 ↓ 4,303.5 8,607 1

Append (cost=1.90..1,080.59 rows=2 width=344) (actual time=6.079..269.797 rows=8,607 loops=1)

29. 7.667 220.714 ↓ 8,566.0 8,566 1

Nested Loop Left Join (cost=1.90..775.31 rows=1 width=319) (actual time=6.077..220.714 rows=8,566 loops=1)

30.          

Initplan (forNested Loop Left Join)

31. 0.004 0.004 ↑ 1.0 1 1

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

32. 0.000 0.000 ↑ 1.0 1 1

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

33. 0.041 0.042 ↑ 1.0 1 1

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

34. 0.001 0.001 ↑ 1.0 1 1

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

35. 0.003 0.003 ↑ 1.0 1 1

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

36. 0.000 0.000 ↑ 1.0 1 1

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

37. 0.003 0.003 ↑ 1.0 1 1

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

38. 0.000 0.000 ↑ 1.0 1 1

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

39. 0.015 0.017 ↑ 1.0 1 1

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

40. 0.002 0.002 ↑ 1.0 1 1

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

41. 0.005 0.006 ↑ 1.0 1 1

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

42. 0.001 0.001 ↑ 1.0 1 1

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

43. 4.830 86.835 ↓ 8,385.0 8,385 1

Nested Loop (cost=1.13..69.71 rows=1 width=128) (actual time=5.689..86.835 rows=8,385 loops=1)

44. 0.000 56.850 ↓ 8,385.0 8,385 1

Nested Loop (cost=0.72..66.41 rows=1 width=72) (actual time=5.591..56.850 rows=8,385 loops=1)

45. 0.992 15.460 ↓ 8,385.0 8,385 1

Nested Loop (cost=0.31..62.96 rows=1 width=24) (actual time=5.416..15.460 rows=8,385 loops=1)

46. 0.006 0.129 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=0.128..0.129 rows=1 loops=1)

  • Group Key: get_event.event_uuid
47. 0.123 0.123 ↑ 1.0 1 1

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

48. 0.775 14.339 ↓ 8,385.0 8,385 1

Append (cost=0.29..62.91 rows=1 width=40) (actual time=5.283..14.339 rows=8,385 loops=1)

49. 13.564 13.564 ↓ 8,385.0 8,385 1

Index Scan using notifications_summary_org_part_454_event_uuid_idx on notifications_summary_org_part_454 live_ntfn (cost=0.29..62.91 rows=1 width=40) (actual time=5.281..13.564 rows=8,385 loops=1)

  • Index Cond: (event_uuid = get_event.event_uuid)
  • Filter: ((created_date >= $13) AND (pending_date <= '2019-09-04 17:12:18+00'::timestamp with time zone) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (category = 'LIVE'::text) AND (created_date <= LEAST('2019-09-04 17:12:18+00'::timestamp with time zone, $12)))
  • Rows Removed by Filter: 12077
50. 8.385 41.925 ↑ 1.0 1 8,385

Append (cost=0.41..3.45 rows=1 width=64) (actual time=0.004..0.005 rows=1 loops=8,385)

51. 33.540 33.540 ↑ 1.0 1 8,385

Index Scan using notifications_summary_org_part_454_pkey on notifications_summary_org_part_454 device_ntfn (cost=0.41..3.44 rows=1 width=64) (actual time=0.004..0.004 rows=1 loops=8,385)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (notification_uuid = live_ntfn.parent_ntfn_uuid))
  • Filter: ((created_date >= $9) AND (category = 'DEVICE'::text) AND (created_date <= LEAST('2019-09-04 17:12:18+00'::timestamp with time zone, $8)))
52. 0.000 25.155 ↑ 1.0 1 8,385

Append (cost=0.41..3.29 rows=1 width=72) (actual time=0.003..0.003 rows=1 loops=8,385)

53. 25.155 25.155 ↑ 1.0 1 8,385

Index Scan using notifications_summary_org_part_454_pkey on notifications_summary_org_part_454 person_ntfn (cost=0.41..3.28 rows=1 width=72) (actual time=0.003..0.003 rows=1 loops=8,385)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (notification_uuid = device_ntfn.parent_ntfn_uuid))
  • Filter: ((created_date >= $11) AND (category = 'PERSON'::text) AND (created_date <= LEAST('2019-09-04 17:12:18+00'::timestamp with time zone, $10)))
54. 109.005 109.005 ↓ 0.0 0 8,385

Index Scan using responses_notification_uuid_idx on responses device_response (cost=0.56..3.58 rows=1 width=63) (actual time=0.013..0.013 rows=0 loops=8,385)

  • Index Cond: (notification_uuid = device_ntfn.notification_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-04 17:12:18+00'::timestamp with time zone) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid))
55.          

SubPlan (forNested Loop Left Join)

56. 8.566 17.132 ↓ 0.0 0 8,566

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

  • Group Key: get_response_choices.text_response
57. 8.566 8.566 ↓ 0.0 0 8,566

CTE Scan on get_response_choices (cost=0.00..700.00 rows=200 width=32) (actual time=0.001..0.001 rows=0 loops=8,566)

  • Filter: ((lower((device_response.response)::text) = lower(text_response)) OR (lower((device_response.response)::text) = lower(translated_response)))
  • Rows Removed by Filter: 1
58. 0.038 48.236 ↓ 41.0 41 1

Hash Join (cost=291.47..305.25 rows=1 width=303) (actual time=48.223..48.236 rows=41 loops=1)

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

Initplan (forHash Join)

60. 0.006 0.007 ↑ 1.0 1 1

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

61. 0.001 0.001 ↑ 1.0 1 1

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

62. 0.004 0.005 ↑ 1.0 1 1

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

63. 0.001 0.001 ↑ 1.0 1 1

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

64. 0.015 0.070 ↑ 1,000.0 1 1

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

  • Group Key: COALESCE(rc_1.translated_response, rc_1.text_response)
65. 0.055 0.055 ↑ 10,000.0 1 1

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

66. 0.046 48.116 ↓ 41.0 41 1

Hash (cost=66.39..66.39 rows=1 width=119) (actual time=48.116..48.116 rows=41 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
67. 1.814 48.070 ↓ 41.0 41 1

Nested Loop (cost=0.87..66.39 rows=1 width=119) (actual time=1.017..48.070 rows=41 loops=1)

68. 0.434 9.006 ↓ 3,725.0 3,725 1

Nested Loop (cost=0.31..62.80 rows=1 width=72) (actual time=0.049..9.006 rows=3,725 loops=1)

69. 0.006 0.008 ↑ 1.0 1 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1)

  • Group Key: get_event_1.event_uuid
70. 0.002 0.002 ↑ 1.0 1 1

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

71. 0.325 8.564 ↓ 3,725.0 3,725 1

Append (cost=0.29..62.75 rows=1 width=72) (actual time=0.038..8.564 rows=3,725 loops=1)

72. 8.239 8.239 ↓ 3,725.0 3,725 1

Index Scan using notifications_summary_org_part_454_event_uuid_idx on notifications_summary_org_part_454 person_resp_ntfn (cost=0.29..62.75 rows=1 width=72) (actual time=0.037..8.239 rows=3,725 loops=1)

  • Index Cond: (event_uuid = get_event_1.event_uuid)
  • Filter: ((created_date >= $15) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (category = 'PERSON'::text) AND (created_date <= LEAST('2019-09-04 17:12:18+00'::timestamp with time zone, $14)))
  • Rows Removed by Filter: 16737
73. 37.250 37.250 ↓ 0.0 0 3,725

Index Scan using responses_notification_uuid_idx on responses pr (cost=0.56..3.58 rows=1 width=63) (actual time=0.010..0.010 rows=0 loops=3,725)

  • Index Cond: (notification_uuid = person_resp_ntfn.notification_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-04 17:12:18+00'::timestamp with time zone) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND ((recipient_type)::text = 'PERSON'::text))
74.          

CTE get_teams

75. 0.564 42.384 ↓ 6.0 6 1

GroupAggregate (cost=7.14..7.17 rows=1 width=56) (actual time=41.598..42.384 rows=6 loops=1)

  • Group Key: team_ntfns.team_ntfn_uuid, team_ntfns.team_parent_ntfn_uuid
76. 2.338 41.820 ↓ 3,725.0 3,725 1

Sort (cost=7.14..7.14 rows=1 width=56) (actual time=41.585..41.820 rows=3,725 loops=1)

  • Sort Key: team_ntfns.team_ntfn_uuid, team_ntfns.team_parent_ntfn_uuid
  • Sort Method: quicksort Memory: 460kB
77. 0.462 39.482 ↓ 3,725.0 3,725 1

Subquery Scan on team_ntfns (cost=7.09..7.13 rows=1 width=56) (actual time=36.823..39.482 rows=3,725 loops=1)

78. 1.706 39.020 ↓ 3,725.0 3,725 1

GroupAggregate (cost=7.09..7.12 rows=1 width=72) (actual time=36.822..39.020 rows=3,725 loops=1)

  • Group Key: en.person_ntfn_uuid, en.team_ntfn_uuid, en.team_parent_ntfn_uuid
79. 3.612 37.314 ↓ 3,725.0 3,725 1

Sort (cost=7.09..7.09 rows=1 width=80) (actual time=36.810..37.314 rows=3,725 loops=1)

  • Sort Key: en.person_ntfn_uuid, en.team_ntfn_uuid, en.team_parent_ntfn_uuid
  • Sort Method: quicksort Memory: 620kB
80. 0.488 33.702 ↓ 3,725.0 3,725 1

Subquery Scan on en (cost=7.03..7.08 rows=1 width=80) (actual time=29.883..33.702 rows=3,725 loops=1)

81. 2.748 33.214 ↓ 3,725.0 3,725 1

GroupAggregate (cost=7.03..7.07 rows=1 width=128) (actual time=29.882..33.214 rows=3,725 loops=1)

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

Initplan (forGroupAggregate)

83. 0.005 0.006 ↑ 1.0 1 1

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

84. 0.001 0.001 ↑ 1.0 1 1

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

85. 0.003 0.005 ↑ 1.0 1 1

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

86. 0.002 0.002 ↑ 1.0 1 1

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

87. 7.139 30.455 ↓ 8,607.0 8,607 1

Sort (cost=6.96..6.97 rows=1 width=96) (actual time=29.865..30.455 rows=8,607 loops=1)

  • Sort Key: n.person_uuid, n.person_ntfn_uuid, team_ntfn.notification_uuid, team_ntfn.parent_ntfn_uuid
  • Sort Method: quicksort Memory: 1595kB
88. 4.501 23.316 ↓ 8,607.0 8,607 1

Nested Loop (cost=0.41..6.96 rows=1 width=96) (actual time=0.089..23.316 rows=8,607 loops=1)

89. 1.601 1.601 ↓ 4,303.5 8,607 1

CTE Scan on get_person_device_notifications n (cost=0.00..0.04 rows=2 width=80) (actual time=0.002..1.601 rows=8,607 loops=1)

90. 0.000 17.214 ↑ 1.0 1 8,607

Append (cost=0.41..3.45 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=8,607)

91. 17.214 17.214 ↑ 1.0 1 8,607

Index Scan using notifications_summary_org_part_454_pkey on notifications_summary_org_part_454 team_ntfn (cost=0.41..3.44 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=8,607)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (notification_uuid = n.team_uuid))
  • Filter: ((created_date >= $24) AND (category = 'TEAM'::text) AND (created_date <= LEAST('2019-09-04 17:12:18+00'::timestamp with time zone, $23)))
92.          

CTE get_team_groups

93. 0.005 42.585 ↑ 1.8 6 1

Recursive Union (cost=0.50..352.23 rows=11 width=74) (actual time=42.498..42.585 rows=6 loops=1)

94. 0.000 42.544 ↓ 6.0 6 1

Nested Loop (cost=0.50..3.56 rows=1 width=74) (actual time=42.495..42.544 rows=6 loops=1)

95.          

Initplan (forNested Loop)

96. 0.012 0.013 ↑ 1.0 1 1

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

97. 0.001 0.001 ↑ 1.0 1 1

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

98. 0.003 0.005 ↑ 1.0 1 1

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

99. 0.002 0.002 ↑ 1.0 1 1

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

100. 0.008 42.399 ↓ 6.0 6 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=42.398..42.399 rows=6 loops=1)

  • Group Key: get_teams.team_ntfn_uuid
101. 42.391 42.391 ↓ 6.0 6 1

CTE Scan on get_teams (cost=0.00..0.02 rows=1 width=16) (actual time=41.602..42.391 rows=6 loops=1)

102. 0.000 0.132 ↑ 1.0 1 6

Append (cost=0.41..3.45 rows=1 width=54) (actual time=0.022..0.022 rows=1 loops=6)

103. 0.132 0.132 ↑ 1.0 1 6

Index Scan using notifications_summary_org_part_454_pkey on notifications_summary_org_part_454 team_ntfn_1 (cost=0.41..3.44 rows=1 width=54) (actual time=0.022..0.022 rows=1 loops=6)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (notification_uuid = get_teams.team_ntfn_uuid))
  • Filter: ((created_date >= $29) AND (category = 'TEAM'::text) AND (created_date <= LEAST('2019-09-04 17:12:18+00'::timestamp with time zone, $28)))
104. 0.000 0.036 ↓ 0.0 0 1

Nested Loop (cost=0.48..34.85 rows=1 width=74) (actual time=0.036..0.036 rows=0 loops=1)

105.          

Initplan (forNested Loop)

106. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=0.02..0.04 rows=1 width=8) (never executed)

107. 0.000 0.000 ↓ 0.0 0

CTE Scan on get_event e_15 (cost=0.00..0.02 rows=1 width=8) (never executed)

108. 0.004 0.004 ↑ 1.0 1 1

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

109. 0.000 0.000 ↑ 1.0 1 1

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

110. 0.005 0.005 ↑ 1.7 6 1

WorkTable Scan on get_team_groups tg_1 (cost=0.00..0.20 rows=10 width=36) (actual time=0.005..0.005 rows=6 loops=1)

111. 0.006 0.030 ↓ 0.0 0 6

Append (cost=0.41..3.45 rows=1 width=54) (actual time=0.005..0.005 rows=0 loops=6)

112. 0.024 0.024 ↓ 0.0 0 6

Index Scan using notifications_summary_org_part_454_pkey on notifications_summary_org_part_454 n_1 (cost=0.41..3.44 rows=1 width=54) (actual time=0.004..0.004 rows=0 loops=6)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (notification_uuid = tg_1.parent_ntfn_uuid))
  • Filter: ((created_date >= $31) AND (category = ANY ('{TEAM,GROUP}'::text[])) AND (created_date <= LEAST('2019-09-04 17:12:18+00'::timestamp with time zone, $30)))
  • Rows Removed by Filter: 1
113.          

CTE get_targeted_groups

114. 0.001 42.658 ↓ 0.0 0 1

Nested Loop Left Join (cost=0.93..3.99 rows=1 width=168) (actual time=42.658..42.658 rows=0 loops=1)

115.          

Initplan (forNested Loop Left Join)

116. 0.000 0.000 ↓ 0.0 0

Aggregate (cost=0.02..0.03 rows=1 width=8) (never executed)

117. 0.000 0.000 ↓ 0.0 0

CTE Scan on get_event e_17 (cost=0.00..0.02 rows=1 width=8) (never executed)

118. 0.002 42.657 ↓ 0.0 0 1

HashAggregate (cost=0.34..0.35 rows=1 width=88) (actual time=42.657..42.657 rows=0 loops=1)

  • Group Key: team_group.notification_uuid, team_group.recipient_uuid
119. 0.002 42.655 ↓ 0.0 0 1

Nested Loop (cost=0.26..0.33 rows=1 width=72) (actual time=42.655..42.655 rows=0 loops=1)

  • Join Filter: (team_group.root_uuid = team.team_ntfn_uuid)
120. 0.001 42.653 ↓ 0.0 0 1

Subquery Scan on team_group (cost=0.26..0.29 rows=1 width=48) (actual time=42.653..42.653 rows=0 loops=1)

  • Filter: (team_group.""row"" = 1)
121. 0.017 42.652 ↓ 0.0 0 1

WindowAgg (cost=0.26..0.28 rows=1 width=60) (actual time=42.652..42.652 rows=0 loops=1)

122. 0.042 42.635 ↓ 0.0 0 1

Sort (cost=0.26..0.26 rows=1 width=52) (actual time=42.635..42.635 rows=0 loops=1)

  • Sort Key: tg_2.root_uuid, tg_2.group_level DESC
  • Sort Method: quicksort Memory: 25kB
123. 42.593 42.593 ↓ 0.0 0 1

CTE Scan on get_team_groups tg_2 (cost=0.00..0.25 rows=1 width=52) (actual time=42.593..42.593 rows=0 loops=1)

  • Filter: (category = 'GROUP'::text)
  • Rows Removed by Filter: 6
124. 0.000 0.000 ↓ 0.0 0

CTE Scan on get_teams team (cost=0.00..0.02 rows=1 width=40) (never executed)

125. 0.000 0.000 ↓ 0.0 0

Index Scan using groups_org_seq_vrange_uidx on groups g (cost=0.56..3.58 rows=1 width=64) (never executed)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (group_uuid = team_group.recipient_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> $35))
126.          

CTE get_total_person_uuids

127. 0.713 65.545 ↓ 1,338.0 1,338 1

Hash Join (cost=272.27..286.06 rows=1 width=184) (actual time=64.964..65.545 rows=1,338 loops=1)

  • Hash Cond: (COALESCE(rc_2.translated_response, rc_2.text_response) = (resp.response)::text)
128. 0.017 0.022 ↑ 1,000.0 1 1

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

  • Group Key: COALESCE(rc_2.translated_response, rc_2.text_response)
129. 0.005 0.005 ↑ 10,000.0 1 1

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

130. 1.466 64.810 ↓ 1,338.0 1,338 1

Hash (cost=47.25..47.25 rows=1 width=152) (actual time=64.809..64.810 rows=1,338 loops=1)

  • Buckets: 2048 (originally 1024) Batches: 1 (originally 1) Memory Usage: 339kB
131. 27.841 63.344 ↓ 1,338.0 1,338 1

Nested Loop (cost=44.06..47.25 rows=1 width=152) (actual time=20.031..63.344 rows=1,338 loops=1)

132. 12.467 33.175 ↓ 1,164.0 1,164 1

GroupAggregate (cost=0.05..0.20 rows=1 width=136) (actual time=19.947..33.175 rows=1,164 loops=1)

  • Group Key: en_1.person_uuid
  • Filter: (CASE WHEN ((array_agg(en_1.device_delivery_status) FILTER (WHERE (en_1.device_delivery_status IS NOT NULL)) @> '{RESPONDED}'::text[]) OR (min(en_1.person_response_received) IS NOT NULL)) THEN 'RESPONDED'::text WHEN (array_agg(en_1.device_delivery_status) FILTER (WHERE (en_1.device_delivery_status IS NOT NULL)) @> '{DELIVERED}'::text[]) THEN 'DELIVERED'::text WHEN (array_agg(en_1.device_delivery_status) FILTER (WHERE (en_1.device_delivery_status IS NOT NULL)) @> '{PENDING}'::text[]) THEN 'PENDING'::text WHEN ((array_agg(en_1.device_delivery_status) FILTER (WHERE (en_1.device_delivery_status IS NOT NULL)) @> '{FAILED}'::text[]) AND (min(en_1.person_delinked_date) IS NOT NULL)) THEN 'FAILED'::text ELSE 'PENDING'::text END = 'RESPONDED'::text)
  • Rows Removed by Filter: 2561
133. 6.490 20.708 ↓ 4,303.5 8,607 1

Sort (cost=0.05..0.06 rows=2 width=104) (actual time=19.875..20.708 rows=8,607 loops=1)

  • Sort Key: en_1.person_uuid
  • Sort Method: quicksort Memory: 1592kB
134. 14.218 14.218 ↓ 4,303.5 8,607 1

CTE Scan on get_person_device_notifications en_1 (cost=0.00..0.04 rows=2 width=104) (actual time=0.003..14.218 rows=8,607 loops=1)

135. 0.000 2.328 ↑ 1.0 1 1,164

Index Scan using responses_notification_uuid_idx on responses resp (cost=44.01..47.03 rows=1 width=32) (actual time=0.002..0.002 rows=1 loops=1,164)

  • Index Cond: (notification_uuid = (SubPlan 24))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-04 17:12:18+00'::timestamp with time zone) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid))
136.          

SubPlan (forIndex Scan)

137. 1.164 26.772 ↑ 1.0 1 1,164

Limit (cost=43.45..43.45 rows=1 width=24) (actual time=0.023..0.023 rows=1 loops=1,164)

138. 1.164 25.608 ↑ 1.0 1 1,164

Sort (cost=43.45..43.45 rows=1 width=24) (actual time=0.022..0.022 rows=1 loops=1,164)

  • Sort Key: r.received DESC
  • Sort Method: quicksort Memory: 25kB
139. 24.444 24.444 ↑ 1.0 1 1,164

Index Scan using responses_notification_uuid_idx on responses r (cost=0.56..43.44 rows=1 width=24) (actual time=0.014..0.021 rows=1 loops=1,164)

  • Index Cond: (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))))))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-04 17:12:18+00'::timestamp with time zone) AND ((response)::text <> 'XMATTERS_REMOVE'::text) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid))
140.          

CTE get_person_uuids

141. 0.006 67.921 ↓ 10.0 10 1

Limit (cost=0.04..0.04 rows=1 width=200) (actual time=67.917..67.921 rows=10 loops=1)

142. 0.400 67.915 ↓ 20.0 20 1

Sort (cost=0.03..0.04 rows=1 width=200) (actual time=67.913..67.915 rows=20 loops=1)

  • Sort Key: en_agg.last_response DESC NULLS LAST, en_agg.delivered_date_min DESC
  • Sort Method: top-N heapsort Memory: 39kB
143. 67.515 67.515 ↓ 1,338.0 1,338 1

CTE Scan on get_total_person_uuids en_agg (cost=0.00..0.02 rows=1 width=200) (actual time=64.967..67.515 rows=1,338 loops=1)

144.          

CTE get_person_and_roles

145. 0.100 71,164.992 ↑ 2,089.3 10 1

Nested Loop Left Join (cost=65.41..1,350,874.82 rows=20,893 width=421) (actual time=7,986.572..71,164.992 rows=10 loops=1)

  • Join Filter: (site.site_uuid = p.site_uuid)
  • Rows Removed by Join Filter: 590
146. 0.107 71,046.442 ↑ 2,089.3 10 1

Nested Loop Left Join (cost=65.13..1,348,983.54 rows=20,893 width=408) (actual time=7,893.159..71,046.442 rows=10 loops=1)

147. 0.051 51,029.425 ↑ 2,089.3 10 1

Nested Loop (cost=0.59..9.72 rows=20,893 width=397) (actual time=5,418.344..51,029.425 rows=10 loops=1)

148. 0.041 0.044 ↓ 10.0 10 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=0.013..0.044 rows=10 loops=1)

  • Group Key: get_person_uuids_1.person_uuid
149. 0.003 0.003 ↓ 10.0 10 1

CTE Scan on get_person_uuids get_person_uuids_1 (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.003 rows=10 loops=1)

150. 0.050 51,029.330 ↑ 1.0 1 10

Append (cost=0.57..9.67 rows=1 width=397) (actual time=5,086.886..5,102.933 rows=1 loops=10)

151. 51,029.280 51,029.280 ↑ 1.0 1 10

Index Scan using persons_org_part_default_organization_uuid_person_uuid_sequ_idx on persons_org_part_default p (cost=0.57..9.67 rows=1 width=397) (actual time=5,086.882..5,102.928 rows=1 loops=10)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (person_uuid = get_person_uuids_1.person_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-04 17:12:18+00'::timestamp with time zone))
  • Rows Removed by Filter: 303
152. 0.640 20,016.910 ↑ 1.0 1 10

Aggregate (cost=64.54..64.55 rows=1 width=32) (actual time=2,001.691..2,001.691 rows=1 loops=10)

153. 0.330 20,016.270 ↑ 200.0 1 10

Hash Right Join (cost=22.31..55.53 rows=200 width=58) (actual time=2,001.617..2,001.627 rows=1 loops=10)

  • Hash Cond: (role.role_uuid = (((jsonb_array_elements_text(pr_1.roles)))::uuid))
154. 103.500 120.630 ↓ 2.9 23 10

Bitmap Heap Scan on roles role (cost=1.96..35.14 rows=8 width=58) (actual time=4.515..12.063 rows=23 loops=10)

  • Recheck Cond: (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid)
  • Filter: upper_inf(valid_range)
  • Rows Removed by Filter: 12
  • Heap Blocks: exact=60
155. 17.130 17.130 ↓ 1.5 35 10

Bitmap Index Scan on roles_organization_uuid_idx (cost=0.00..1.96 rows=23 width=0) (actual time=1.713..1.713 rows=35 loops=10)

  • Index Cond: (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid)
156. 0.080 19,895.310 ↑ 200.0 1 10

Hash (cost=17.84..17.84 rows=200 width=16) (actual time=1,989.531..1,989.531 rows=1 loops=10)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
157. 0.100 19,895.230 ↑ 200.0 1 10

Nested Loop Left Join (cost=0.56..17.84 rows=200 width=16) (actual time=1,989.519..1,989.523 rows=1 loops=10)

158. 19,894.850 19,894.850 ↑ 2.0 1 10

Index Scan using person_roles_org_seq_vrange_uidx on person_roles pr_1 (cost=0.56..9.31 rows=2 width=34) (actual time=1,989.483..1,989.485 rows=1 loops=10)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (person_uuid = p.person_uuid))
  • Filter: upper_inf(valid_range)
  • Rows Removed by Filter: 145
159. 0.080 0.280 ↑ 100.0 1 10

Result (cost=0.00..2.27 rows=100 width=16) (actual time=0.026..0.028 rows=1 loops=10)

160. 0.190 0.200 ↑ 100.0 1 10

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.019..0.020 rows=1 loops=10)

161. 0.010 0.010 ↑ 1.0 1 10

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

162. 0.098 118.450 ↓ 10.0 60 10

Materialize (cost=0.28..10.93 rows=6 width=29) (actual time=9.261..11.845 rows=60 loops=10)

163. 118.352 118.352 ↓ 10.0 60 1

Index Scan using sites_org_valid_range_gist on sites site (cost=0.28..10.90 rows=6 width=29) (actual time=92.599..118.352 rows=60 loops=1)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (valid_range @> '2019-09-04 17:12:18+00'::timestamp with time zone))
  • Filter: (is_visible IS TRUE)
164.          

CTE get_person_notifications

165. 0.008 10,607.801 ↓ 5.0 10 1

Subquery Scan on p_1 (cost=185.22..280.22 rows=2 width=304) (actual time=10,606.653..10,607.801 rows=10 loops=1)

166. 1.480 10,607.793 ↓ 5.0 10 1

GroupAggregate (cost=185.22..280.17 rows=2 width=272) (actual time=10,606.648..10,607.793 rows=10 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
167. 0.199 10,606.313 ↑ 64.6 28 1

Sort (cost=185.22..189.74 rows=1,808 width=326) (actual time=10,606.309..10,606.313 rows=28 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: 32kB
168. 0.161 10,606.114 ↑ 64.6 28 1

Nested Loop Left Join (cost=0.59..87.41 rows=1,808 width=326) (actual time=354.713..10,606.114 rows=28 loops=1)

169. 1.092 356.245 ↓ 28.0 28 1

Hash Semi Join (cost=0.03..0.09 rows=1 width=280) (actual time=354.169..356.245 rows=28 loops=1)

  • Hash Cond: (get_person_device_notifications.person_uuid = get_person_uuids_2.person_uuid)
170. 287.183 287.183 ↓ 4,303.5 8,607 1

CTE Scan on get_person_device_notifications (cost=0.00..0.04 rows=2 width=280) (actual time=285.967..287.183 rows=8,607 loops=1)

171. 0.026 67.970 ↓ 10.0 10 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=67.970..67.970 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
172. 67.944 67.944 ↓ 10.0 10 1

CTE Scan on get_person_uuids get_person_uuids_2 (cost=0.00..0.02 rows=1 width=16) (actual time=67.921..67.944 rows=10 loops=1)

173. 0.084 10,249.708 ↑ 1.0 1 28

Append (cost=0.56..87.31 rows=1 width=62) (actual time=133.926..366.061 rows=1 loops=28)

174. 10,249.624 10,249.624 ↑ 1.0 1 28

Index Scan using devices_org_part_default_recipient_uuid_idx on devices_org_part_default d (cost=0.56..87.30 rows=1 width=62) (actual time=133.923..366.058 rows=1 loops=28)

  • Index Cond: (recipient_uuid = get_person_device_notifications.device_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-04 17:12:18+00'::timestamp with time zone) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid))
  • Rows Removed by Filter: 150
175.          

Initplan (forSort)

176. 0.077 0.292 ↑ 1.0 1 1

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

177. 0.215 0.215 ↓ 1,338.0 1,338 1

CTE Scan on get_total_person_uuids (cost=0.00..0.02 rows=1 width=0) (actual time=0.002..0.215 rows=1,338 loops=1)

178. 0.002 0.002 ↑ 1.0 1 1

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

179. 0.429 81,819.016 ↑ 20.9 10 1

Hash Right Join (cost=1,416.75..1,918.18 rows=209 width=464) (actual time=18,640.443..81,819.016 rows=10 loops=1)

  • Hash Cond: (get_person_and_roles.person_uuid = pn.person_uuid)
180. 71,165.069 71,165.069 ↑ 2,089.3 10 1

CTE Scan on get_person_and_roles (cost=0.00..417.86 rows=20,893 width=224) (actual time=7,986.578..71,165.069 rows=10 loops=1)

181. 0.147 10,653.518 ↓ 5.0 10 1

Hash (cost=1,416.72..1,416.72 rows=2 width=264) (actual time=10,653.518..10,653.518 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
182. 0.019 10,653.371 ↓ 5.0 10 1

Merge Left Join (cost=354.40..1,416.72 rows=2 width=264) (actual time=10,651.084..10,653.371 rows=10 loops=1)

  • Merge Cond: (pn.person_uuid = get_person_uuids.person_uuid)
183. 0.014 10,653.277 ↓ 5.0 10 1

Nested Loop Left Join (cost=354.37..1,416.67 rows=2 width=232) (actual time=10,651.000..10,653.277 rows=10 loops=1)

184. 0.017 10,610.593 ↓ 5.0 10 1

Nested Loop Left Join (cost=354.34..1,416.55 rows=2 width=232) (actual time=10,608.330..10,610.593 rows=10 loops=1)

185. 0.017 10,610.436 ↓ 5.0 10 1

Nested Loop Left Join (cost=176.57..1,060.95 rows=2 width=232) (actual time=10,608.299..10,610.436 rows=10 loops=1)

186. 1.304 10,609.349 ↓ 5.0 10 1

GroupAggregate (cost=0.05..707.83 rows=2 width=248) (actual time=10,608.177..10,609.349 rows=10 loops=1)

  • Group Key: pn.person_uuid, pn.event_uuid
187. 0.100 10,608.045 ↓ 5.0 10 1

Sort (cost=0.05..0.06 rows=2 width=288) (actual time=10,608.040..10,608.045 rows=10 loops=1)

  • Sort Key: pn.person_uuid, pn.event_uuid
  • Sort Method: quicksort Memory: 45kB
188. 10,607.945 10,607.945 ↓ 5.0 10 1

CTE Scan on get_person_notifications pn (cost=0.00..0.04 rows=2 width=288) (actual time=10,606.664..10,607.945 rows=10 loops=1)

189.          

SubPlan (forGroupAggregate)

190. 0.000 0.000 ↓ 0.0 0

Unique (cost=353.32..353.82 rows=79 width=32) (never executed)

191. 0.000 0.000 ↓ 0.0 0

Sort (cost=353.32..353.57 rows=100 width=32) (never executed)

  • Sort Key: get_filtered_response_choices.text_response
192. 0.000 0.000 ↓ 0.0 0

CTE Scan on get_filtered_response_choices (cost=0.00..350.00 rows=100 width=32) (never executed)

  • Filter: ((lower((pn.person_response_value)::text) = lower(text_response)) OR (lower((pn.person_response_value)::text) = lower(translated_response)))
193. 0.880 1.070 ↑ 1.0 1 10

Aggregate (cost=176.52..176.53 rows=1 width=32) (actual time=0.107..0.107 rows=1 loops=10)

194. 0.070 0.190 ↑ 3,333.3 3 10

ProjectSet (cost=0.00..51.52 rows=10,000 width=32) (actual time=0.018..0.019 rows=3 loops=10)

195. 0.120 0.120 ↑ 100.0 1 10

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

196. 0.000 0.000 ↑ 1.0 1 10

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

197. 0.070 0.140 ↑ 1.0 1 10

Aggregate (cost=177.77..177.78 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=10)

198. 0.050 0.070 ↑ 10,000.0 1 10

ProjectSet (cost=0.00..52.77 rows=10,000 width=32) (actual time=0.007..0.007 rows=1 loops=10)

199. 0.020 0.020 ↑ 100.0 1 10

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

200. 0.000 0.000 ↑ 1.0 1 10

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

201. 0.010 42.670 ↑ 1.0 1 10

Aggregate (cost=0.03..0.04 rows=1 width=32) (actual time=4.267..4.267 rows=1 loops=10)

202. 42.660 42.660 ↓ 0.0 0 10

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

  • Filter: (team_ntfn_uuids && (array_agg(pn.team_uuid)))
203. 0.067 0.075 ↓ 10.0 10 1

Sort (cost=0.03..0.04 rows=1 width=48) (actual time=0.073..0.075 rows=10 loops=1)

  • Sort Key: get_person_uuids.person_uuid
  • Sort Method: quicksort Memory: 25kB
204. 0.008 0.008 ↓ 10.0 10 1

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

Planning time : 880.151 ms
Execution time : 81,875.664 ms