explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Yw4Ma

Settings
# exclusive inclusive rows x rows loops node
1. 0.386 12,404.265 ↓ 2.5 100 1

Sort (cost=2,347,169.38..2,347,169.48 rows=40 width=432) (actual time=12,404.259..12,404.265 rows=100 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), (min(pn.device_delivered_date_min))
  • Sort Method: quicksort Memory: 207kB
2.          

CTE get_event

3. 0.001 0.110 ↑ 1.0 1 1

Append (cost=0.55..3.58 rows=1 width=729) (actual time=0.109..0.110 rows=1 loops=1)

4. 0.109 0.109 ↑ 1.0 1 1

Index Scan using events_org_part_default_evn_seq_vrange_uidx on events_org_part_default e (cost=0.55..3.58 rows=1 width=729) (actual time=0.109..0.109 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-15 06:38:32.87+00'::timestamp with time zone)
  • Rows Removed by Filter: 1
5.          

CTE get_response_choices

6. 0.001 0.229 ↑ 20,000.0 1 1

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

7. 0.009 0.226 ↑ 10,000.0 1 1

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

8. 0.004 0.097 ↑ 1.0 1 1

Nested Loop (cost=0.42..3.47 rows=1 width=993) (actual time=0.085..0.097 rows=1 loops=1)

9. 0.001 0.001 ↑ 1.0 1 1

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

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

Index Scan using forms_org_seq_vrange_uidx on forms f (cost=0.42..3.44 rows=1 width=1,028) (actual time=0.080..0.092 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.067 0.120 ↑ 10,000.0 1 1

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

12. 0.052 0.053 ↑ 100.0 1 1

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.053..0.053 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.231 0.231 ↑ 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.215..0.231 rows=1 loops=1)

21.          

CTE get_lowered_responses_array

22. 0.147 0.379 ↑ 1.0 1 1

Aggregate (cost=500.00..500.01 rows=1 width=32) (actual time=0.379..0.379 rows=1 loops=1)

23. 0.232 0.232 ↑ 20,000.0 1 1

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

24.          

CTE get_person_device_notifications

25. 3.853 247.198 ↓ 4,303.5 8,607 1

Unique (cost=834.24..834.34 rows=2 width=344) (actual time=242.035..247.198 rows=8,607 loops=1)

26. 10.199 243.345 ↓ 4,303.5 8,607 1

Sort (cost=834.24..834.24 rows=2 width=344) (actual time=242.031..243.345 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-15 06:38:32.87+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-15 06:38:32.87+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-15 06:38:32.87+00'::timestamp with time zone) THEN device_ntfn.delivered_date ELSE NULL::timestamp with time zone END), (CASE WHEN ((device_response.received <= '2019-09-15 06:38:32.87+00'::timestamp with time zone) AND (lower((device_response.response)::text) = ANY ($8))) THEN device_response.received ELSE NULL::timestamp with time zone END), (CASE WHEN ((device_response.received <= '2019-09-15 06:38:32.87+00'::timestamp with time zone) AND (lower((device_response.response)::text) = ANY ($9))) THEN 'RESPONDED'::text WHEN (device_ntfn.delivered_date <= '2019-09-15 06:38:32.87+00'::timestamp with time zone) THEN 'DELIVERED'::text WHEN (device_ntfn.failed_date <= '2019-09-15 06:38:32.87+00'::timestamp with time zone) THEN 'FAILED'::text WHEN (live_ntfn.pending_date <= '2019-09-15 06:38:32.87+00'::timestamp with time zone) THEN 'PENDING'::text ELSE ''::text END), (CASE WHEN (live_ntfn.pending_date <= '2019-09-15 06:38:32.87+00'::timestamp with time zone) THEN live_ntfn.pending_date ELSE NULL::timestamp with time zone END), device_response.response_uuid, ((SubPlan 7)), (CASE WHEN (device_response.received <= '2019-09-15 06:38:32.87+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: 2,671kB
27. 0.847 233.146 ↓ 4,303.5 8,607 1

Append (cost=1.94..834.23 rows=2 width=344) (actual time=5.403..233.146 rows=8,607 loops=1)

28. 8.730 212.690 ↓ 8,566.0 8,566 1

Nested Loop Left Join (cost=1.94..771.46 rows=1 width=319) (actual time=5.401..212.690 rows=8,566 loops=1)

  • Join Filter: (device_ntfn.responded_date <= '2019-09-15 06:38:32.87+00'::timestamp with time zone)
29.          

Initplan (for Nested Loop Left Join)

30. 0.381 0.381 ↑ 1.0 1 1

CTE Scan on get_lowered_responses_array rc_2 (cost=0.00..0.02 rows=1 width=32) (actual time=0.381..0.381 rows=1 loops=1)

31. 0.000 0.000 ↑ 1.0 1 1

CTE Scan on get_lowered_responses_array rc_3 (cost=0.00..0.02 rows=1 width=32) (actual time=0.000..0.000 rows=1 loops=1)

32. 0.003 0.004 ↑ 1.0 1 1

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

33. 0.001 0.001 ↑ 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.001 rows=1 loops=1)

34. 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)

35. 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)

36. 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)

37. 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)

38. 0.002 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)

39. 0.001 0.001 ↑ 1.0 1 1

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

40. 0.026 0.028 ↑ 1.0 1 1

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

41. 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)

42. 0.003 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)

43. 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.000..0.001 rows=1 loops=1)

44. 4.794 85.778 ↓ 8,385.0 8,385 1

Nested Loop (cost=1.13..65.79 rows=1 width=136) (actual time=4.938..85.778 rows=8,385 loops=1)

45. 0.000 55.829 ↓ 8,385.0 8,385 1

Nested Loop (cost=0.72..62.50 rows=1 width=80) (actual time=4.910..55.829 rows=8,385 loops=1)

46. 0.983 15.031 ↓ 8,385.0 8,385 1

Nested Loop (cost=0.31..59.04 rows=1 width=24) (actual time=4.851..15.031 rows=8,385 loops=1)

47. 0.012 0.125 ↑ 1.0 1 1

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

  • Group Key: get_event.event_uuid
48. 0.113 0.113 ↑ 1.0 1 1

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

49. 0.782 13.923 ↓ 8,385.0 8,385 1

Append (cost=0.29..59.00 rows=1 width=40) (actual time=4.722..13.923 rows=8,385 loops=1)

50. 13.141 13.141 ↓ 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..58.99 rows=1 width=40) (actual time=4.719..13.141 rows=8,385 loops=1)

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

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

52. 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=72) (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 >= $12) AND (category = 'DEVICE'::text) AND (created_date <= LEAST('2019-09-15 06:38:32.87+00'::timestamp with time zone, $11)))
53. 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)

54. 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 >= $14) AND (category = 'PERSON'::text) AND (created_date <= LEAST('2019-09-15 06:38:32.87+00'::timestamp with time zone, $13)))
55. 100.620 100.620 ↓ 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.012..0.012 rows=0 loops=8,385)

  • Index Cond: (notification_uuid = device_ntfn.notification_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-15 06:38:32.87+00'::timestamp with time zone) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid))
56.          

SubPlan (for Nested Loop Left Join)

57. 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
58. 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
59. 0.756 19.609 ↓ 41.0 41 1

Nested Loop (cost=0.96..62.74 rows=1 width=303) (actual time=0.334..19.609 rows=41 loops=1)

60.          

Initplan (for Nested Loop)

61. 0.001 0.001 ↑ 1.0 1 1

CTE Scan on get_lowered_responses_array rc_4 (cost=0.00..0.02 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=1)

62. 0.007 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)

63. 0.000 0.000 ↑ 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.000 rows=1 loops=1)

64. 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)

65. 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)

66. 0.162 7.200 ↓ 1,164.0 1,164 1

Nested Loop (cost=0.31..59.04 rows=1 width=72) (actual time=0.060..7.200 rows=1,164 loops=1)

67. 0.007 0.009 ↑ 1.0 1 1

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

  • Group Key: get_event_1.event_uuid
68. 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)

69. 0.103 7.029 ↓ 1,164.0 1,164 1

Append (cost=0.29..59.00 rows=1 width=72) (actual time=0.048..7.029 rows=1,164 loops=1)

70. 6.926 6.926 ↓ 1,164.0 1,164 1

Index Scan using notifications_summary_org_part_454_event_uuid_idx on notifications_summary_org_part_454 person_resp_ntfn (cost=0.29..58.99 rows=1 width=72) (actual time=0.047..6.926 rows=1,164 loops=1)

  • Index Cond: (event_uuid = get_event_1.event_uuid)
  • Filter: ((responded_date <= '2019-09-15 06:38:32.87+00'::timestamp with time zone) AND (created_date >= $19) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (category = 'PERSON'::text) AND (created_date <= LEAST('2019-09-15 06:38:32.87+00'::timestamp with time zone, $18)))
  • Rows Removed by Filter: 19,298
71. 11.640 11.640 ↓ 0.0 0 1,164

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

  • Index Cond: (notification_uuid = person_resp_ntfn.notification_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-09-15 06:38:32.87+00'::timestamp with time zone) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND ((recipient_type)::text = 'PERSON'::text) AND (lower((response)::text) = ANY ($17)))
72.          

CTE get_teams

73. 0.576 35.091 ↓ 6.0 6 1

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

  • Group Key: team_ntfns.team_ntfn_uuid, team_ntfns.team_parent_ntfn_uuid
74. 1.375 34.515 ↓ 3,725.0 3,725 1

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

  • Sort Key: team_ntfns.team_ntfn_uuid, team_ntfns.team_parent_ntfn_uuid
  • Sort Method: quicksort Memory: 460kB
75. 0.368 33.140 ↓ 3,725.0 3,725 1

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

76. 1.645 32.772 ↓ 3,725.0 3,725 1

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

  • Group Key: en.person_ntfn_uuid, en.team_ntfn_uuid, en.team_parent_ntfn_uuid
77. 2.362 31.127 ↓ 3,725.0 3,725 1

Sort (cost=7.09..7.09 rows=1 width=80) (actual time=30.640..31.127 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
78. 0.398 28.765 ↓ 3,725.0 3,725 1

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

79. 2.691 28.367 ↓ 3,725.0 3,725 1

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

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

Initplan (for GroupAggregate)

81. 0.006 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)

82. 0.000 0.000 ↑ 1.0 1 1

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

83. 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)

84. 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)

85. 4.018 25.665 ↓ 8,607.0 8,607 1

Sort (cost=6.96..6.97 rows=1 width=96) (actual time=25.197..25.665 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: 1,595kB
86. 3.103 21.647 ↓ 8,607.0 8,607 1

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

87. 1.330 1.330 ↓ 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.001..1.330 rows=8,607 loops=1)

88. 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)

89. 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 >= $28) AND (category = 'TEAM'::text) AND (created_date <= LEAST('2019-09-15 06:38:32.87+00'::timestamp with time zone, $27)))
90.          

CTE get_team_groups

91. 0.005 35.273 ↑ 1.8 6 1

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

92. 0.002 35.229 ↓ 6.0 6 1

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

93.          

Initplan (for Nested Loop)

94. 0.006 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)

95. 0.000 0.000 ↑ 1.0 1 1

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

96. 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)

97. 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.001..0.002 rows=1 loops=1)

98. 0.010 35.114 ↓ 6.0 6 1

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

  • Group Key: get_teams.team_ntfn_uuid
99. 35.104 35.104 ↓ 6.0 6 1

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

100. 0.000 0.102 ↑ 1.0 1 6

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

101. 0.102 0.102 ↑ 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.017..0.017 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 >= $33) AND (category = 'TEAM'::text) AND (created_date <= LEAST('2019-09-15 06:38:32.87+00'::timestamp with time zone, $32)))
102. 0.001 0.039 ↓ 0.0 0 1

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

103.          

Initplan (for Nested Loop)

104. 0.000 0.000 ↓ 0.0 0

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

105. 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)

106. 0.002 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)

107. 0.001 0.001 ↑ 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.001 rows=1 loops=1)

108. 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)

109. 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)

110. 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 >= $35) AND (category = ANY ('{TEAM,GROUP}'::text[])) AND (created_date <= LEAST('2019-09-15 06:38:32.87+00'::timestamp with time zone, $34)))
  • Rows Removed by Filter: 1
111.          

CTE get_targeted_groups

112. 0.001 35.348 ↓ 0.0 0 1

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

113.          

Initplan (for Nested Loop Left Join)

114. 0.000 0.000 ↓ 0.0 0

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

115. 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)

116. 0.003 35.347 ↓ 0.0 0 1

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

  • Group Key: team_group.notification_uuid, team_group.recipient_uuid
117. 0.001 35.344 ↓ 0.0 0 1

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

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

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

  • Filter: (team_group.""row"" = 1)
119. 0.016 35.342 ↓ 0.0 0 1

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

120. 0.047 35.326 ↓ 0.0 0 1

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

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

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

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

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

123. 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 @> $39))
124.          

CTE get_total_person_uuids

125. 1.231 16.161 ↓ 1,862.5 3,725 1

Subquery Scan on pen (cost=0.07..0.13 rows=2 width=104) (actual time=13.030..16.161 rows=3,725 loops=1)

126. 6.818 14.930 ↓ 1,862.5 3,725 1

HashAggregate (cost=0.07..0.09 rows=2 width=72) (actual time=13.008..14.930 rows=3,725 loops=1)

  • Group Key: en_1.person_uuid
127. 8.112 8.112 ↓ 4,303.5 8,607 1

CTE Scan on get_person_device_notifications en_1 (cost=0.00..0.04 rows=2 width=72) (actual time=0.001..8.112 rows=8,607 loops=1)

128.          

CTE get_total_person_count

129. 0.205 0.592 ↑ 1.0 1 1

Aggregate (cost=0.04..0.06 rows=1 width=8) (actual time=0.592..0.592 rows=1 loops=1)

130. 0.387 0.387 ↓ 1,862.5 3,725 1

CTE Scan on get_total_person_uuids (cost=0.00..0.04 rows=2 width=0) (actual time=0.001..0.387 rows=3,725 loops=1)

131.          

CTE get_person_uuids

132. 0.019 19.129 ↓ 100.0 100 1

Limit (cost=0.06..0.06 rows=1 width=104) (actual time=19.113..19.129 rows=100 loops=1)

133. 1.196 19.110 ↓ 100.0 200 1

Sort (cost=0.05..0.06 rows=2 width=104) (actual time=19.099..19.110 rows=200 loops=1)

  • Sort Key: en_agg.delivery_status, en_agg.delivered_date_min
  • Sort Method: top-N heapsort Memory: 75kB
134. 17.914 17.914 ↓ 1,862.5 3,725 1

CTE Scan on get_total_person_uuids en_agg (cost=0.00..0.04 rows=2 width=104) (actual time=13.034..17.914 rows=3,725 loops=1)

135.          

CTE get_person_and_roles

136. 0.262 7,660.716 ↑ 40.1 100 1

Hash Left Join (cost=587.25..2,341,646.39 rows=4,014 width=202) (actual time=128.538..7,660.716 rows=100 loops=1)

  • Hash Cond: (p.site_uuid = site.site_uuid)
137.          

Initplan (for Hash Left Join)

138. 0.008 0.012 ↑ 1.0 1 1

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

139. 0.004 0.004 ↑ 1.0 1 1

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

140. 0.005 0.007 ↑ 1.0 1 1

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

141. 0.002 0.002 ↑ 1.0 1 1

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

142. 0.913 7,659.522 ↑ 40.1 100 1

Nested Loop Left Join (cost=583.87..2,341,627.94 rows=4,014 width=191) (actual time=127.613..7,659.522 rows=100 loops=1)

143. 0.637 4,092.009 ↑ 40.1 100 1

Nested Loop (cost=0.57..170.18 rows=4,014 width=180) (actual time=48.119..4,092.009 rows=100 loops=1)

144. 0.357 0.372 ↓ 100.0 100 1

HashAggregate (cost=0.02..0.03 rows=1 width=16) (actual time=0.046..0.372 rows=100 loops=1)

  • Group Key: get_person_uuids.person_uuid
145. 0.015 0.015 ↓ 100.0 100 1

CTE Scan on get_person_uuids (cost=0.00..0.02 rows=1 width=16) (actual time=0.001..0.015 rows=100 loops=1)

146. 0.500 4,091.000 ↑ 1.0 1 100

Append (cost=0.55..170.14 rows=1 width=180) (actual time=27.341..40.910 rows=1 loops=100)

147. 4,090.500 4,090.500 ↑ 1.0 1 100

Index Scan using persons_org_part_17_organization_uuid_person_uuid_sequence__idx on persons_org_part_17 p (cost=0.55..170.14 rows=1 width=180) (actual time=27.338..40.905 rows=1 loops=100)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (person_uuid = get_person_uuids.person_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> $45))
  • Rows Removed by Filter: 340
148. 3.800 3,566.600 ↑ 1.0 1 100

Aggregate (cost=583.29..583.30 rows=1 width=32) (actual time=35.665..35.666 rows=1 loops=100)

149. 0.674 3,562.800 ↑ 4,500.0 1 100

Hash Left Join (cost=35.67..380.79 rows=4,500 width=57) (actual time=35.623..35.628 rows=1 loops=100)

  • Hash Cond: ((((jsonb_array_elements_text(pr_1.roles)))::uuid) = role.role_uuid)
150. 0.700 3,561.900 ↑ 4,500.0 1 100

Nested Loop Left Join (cost=0.42..289.29 rows=4,500 width=16) (actual time=35.615..35.619 rows=1 loops=100)

151. 0.500 3,558.400 ↑ 45.0 1 100

Append (cost=0.42..97.25 rows=45 width=36) (actual time=35.581..35.584 rows=1 loops=100)

152. 3,557.900 3,557.900 ↑ 45.0 1 100

Index Scan using person_roles_org_part_2_organization_uuid_person_uuid_seque_idx on person_roles_org_part_2 pr_1 (cost=0.42..97.02 rows=45 width=36) (actual time=35.577..35.579 rows=1 loops=100)

  • 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: 164
153. 0.800 2.800 ↑ 100.0 1 100

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

154. 2.000 2.000 ↑ 100.0 1 100

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

155. 0.000 0.000 ↑ 1.0 1 100

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

156. 0.097 0.226 ↓ 2.9 23 1

Hash (cost=35.14..35.14 rows=8 width=57) (actual time=0.226..0.226 rows=23 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 11kB
157. 0.074 0.129 ↓ 2.9 23 1

Bitmap Heap Scan on roles role (cost=1.96..35.14 rows=8 width=57) (actual time=0.092..0.129 rows=23 loops=1)

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

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

  • Index Cond: (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid)
159. 0.001 0.913 ↓ 0.0 0 1

Hash (cost=3.30..3.30 rows=1 width=27) (actual time=0.912..0.913 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
160. 0.912 0.912 ↓ 0.0 0 1

Index Scan using sites_org_valid_range_gist on sites site (cost=0.28..3.30 rows=1 width=27) (actual time=0.911..0.912 rows=0 loops=1)

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

CTE get_person_notifications

162. 0.032 4,693.466 ↓ 50.0 100 1

Subquery Scan on p_1 (cost=200.54..202.88 rows=2 width=304) (actual time=4,686.838..4,693.466 rows=100 loops=1)

163. 6.840 4,693.434 ↓ 50.0 100 1

GroupAggregate (cost=200.54..202.83 rows=2 width=272) (actual time=4,686.833..4,693.434 rows=100 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
164.          

Initplan (for GroupAggregate)

165. 0.004 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)

166. 0.002 0.002 ↑ 1.0 1 1

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

167. 1.476 4,686.588 ↓ 5.0 217 1

Sort (cost=200.51..200.62 rows=43 width=321) (actual time=4,686.570..4,686.588 rows=217 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: 82kB
168. 1.352 4,685.112 ↓ 5.0 217 1

Nested Loop Left Join (cost=197.75..199.34 rows=43 width=321) (actual time=286.561..4,685.112 rows=217 loops=1)

169. 2.322 265.423 ↓ 217.0 217 1

Hash Semi Join (cost=0.03..0.09 rows=1 width=280) (actual time=261.295..265.423 rows=217 loops=1)

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

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

171. 0.034 19.198 ↓ 100.0 100 1

Hash (cost=0.02..0.02 rows=1 width=16) (actual time=19.198..19.198 rows=100 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 13kB
172. 19.164 19.164 ↓ 100.0 100 1

CTE Scan on get_person_uuids get_person_uuids_1 (cost=0.00..0.02 rows=1 width=16) (actual time=19.117..19.164 rows=100 loops=1)

173. 1.953 4,418.337 ↑ 1.0 1 217

Append (cost=197.72..199.24 rows=1 width=57) (actual time=20.359..20.361 rows=1 loops=217)

174. 143.220 4,416.384 ↑ 1.0 1 217

Bitmap Heap Scan on devices_org_part_2 d (cost=197.72..199.24 rows=1 width=57) (actual time=20.351..20.352 rows=1 loops=217)

  • Recheck Cond: ((recipient_uuid = get_person_device_notifications.device_uuid) AND (valid_range @> $52))
  • Filter: ((is_visible IS TRUE) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid))
  • Heap Blocks: exact=217
175. 9.114 4,273.164 ↓ 0.0 0 217

BitmapAnd (cost=197.72..197.72 rows=1 width=0) (actual time=19.692..19.692 rows=0 loops=217)

176. 3,718.078 3,718.078 ↓ 1.2 172 217

Bitmap Index Scan on devices_org_part_2_recipient_uuid_idx (cost=0.00..2.97 rows=139 width=0) (actual time=17.134..17.134 rows=172 loops=217)

  • Index Cond: (recipient_uuid = get_person_device_notifications.device_uuid)
177. 545.972 545.972 ↓ 1.3 11,613 217

Bitmap Index Scan on devices_org_part_2_valid_range_idx (cost=0.00..192.37 rows=8,795 width=0) (actual time=2.516..2.516 rows=11,613 loops=217)

  • Index Cond: (valid_range @> $52)
178.          

Initplan (for Sort)

179. 0.594 0.594 ↑ 1.0 1 1

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

180. 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)

181. 1.616 12,403.283 ↓ 2.5 100 1

Hash Right Join (cost=2,112.59..2,208.92 rows=40 width=432) (actual time=4,869.722..12,403.283 rows=100 loops=1)

  • Hash Cond: (get_person_and_roles.person_uuid = pn.person_uuid)
182. 7,661.116 7,661.116 ↑ 40.1 100 1

CTE Scan on get_person_and_roles (cost=0.00..80.28 rows=4,014 width=224) (actual time=128.543..7,661.116 rows=100 loops=1)

183. 0.108 4,740.551 ↓ 50.0 100 1

Hash (cost=2,112.57..2,112.57 rows=2 width=232) (actual time=4,740.550..4,740.551 rows=100 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 99kB
184. 0.034 4,740.443 ↓ 50.0 100 1

Nested Loop Left Join (cost=354.37..2,112.57 rows=2 width=232) (actual time=4,729.479..4,740.443 rows=100 loops=1)

185. 0.018 4,705.009 ↓ 50.0 100 1

Nested Loop Left Join (cost=354.34..2,112.45 rows=2 width=232) (actual time=4,694.121..4,705.009 rows=100 loops=1)

186. 0.100 4,704.891 ↓ 50.0 100 1

Nested Loop Left Join (cost=176.57..1,756.85 rows=2 width=232) (actual time=4,694.114..4,704.891 rows=100 loops=1)

187. 5.395 4,699.291 ↓ 50.0 100 1

GroupAggregate (cost=0.05..1,403.73 rows=2 width=248) (actual time=4,694.000..4,699.291 rows=100 loops=1)

  • Group Key: pn.person_uuid, pn.event_uuid
188. 0.154 4,693.896 ↓ 50.0 100 1

Sort (cost=0.05..0.06 rows=2 width=288) (actual time=4,693.880..4,693.896 rows=100 loops=1)

  • Sort Key: pn.person_uuid, pn.event_uuid
  • Sort Method: quicksort Memory: 149kB
189. 4,693.742 4,693.742 ↓ 50.0 100 1

CTE Scan on get_person_notifications pn (cost=0.00..0.04 rows=2 width=288) (actual time=4,686.846..4,693.742 rows=100 loops=1)

190.          

SubPlan (for GroupAggregate)

191. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=700.50..701.77 rows=127 width=32) (never executed)

  • Group 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..700.00 rows=200 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. 5.100 5.500 ↑ 1.0 1 100

Aggregate (cost=176.52..176.53 rows=1 width=32) (actual time=0.055..0.055 rows=1 loops=100)

194. 0.200 0.400 ↑ 5,000.0 2 100

ProjectSet (cost=0.00..51.52 rows=10,000 width=32) (actual time=0.003..0.004 rows=2 loops=100)

195. 0.200 0.200 ↑ 100.0 1 100

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

196. 0.000 0.000 ↑ 1.0 1 100

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

197. 0.100 0.100 ↑ 1.0 1 100

Aggregate (cost=177.77..177.78 rows=1 width=32) (actual time=0.001..0.001 rows=1 loops=100)

198. 0.000 0.000 ↓ 0.0 0 100

ProjectSet (cost=0.00..52.77 rows=10,000 width=32) (actual time=0.000..0.000 rows=0 loops=100)

199. 0.000 0.000 ↓ 0.0 0 100

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

200. 0.000 0.000 ↑ 1.0 1 100

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

201. 0.000 35.400 ↑ 1.0 1 100

Aggregate (cost=0.03..0.04 rows=1 width=32) (actual time=0.354..0.354 rows=1 loops=100)

202. 35.400 35.400 ↓ 0.0 0 100

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

  • Filter: (team_ntfn_uuids && (array_agg(pn.team_uuid)))
Planning time : 824.850 ms
Execution time : 12,411.147 ms