explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 8dy5X

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 45,747.856 ↑ 2.7 10 1

Sort (cost=179,938.56..179,938.63 rows=27 width=432) (actual time=45,747.853..45,747.856 rows=10 loops=1)

  • Sort Key: (min(pn.device_delivered_date_min))
  • Sort Method: quicksort Memory: 45kB
2.          

CTE get_event

3. 0.001 0.236 ↑ 1.0 1 1

Append (cost=0.56..3.58 rows=1 width=737) (actual time=0.234..0.236 rows=1 loops=1)

4. 0.235 0.235 ↑ 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.233..0.235 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.003 0.436 ↑ 20,000.0 1 1

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

7. 0.007 0.430 ↑ 10,000.0 1 1

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

8. 0.006 0.182 ↑ 1.0 1 1

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

9. 0.003 0.003 ↑ 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.003 rows=1 loops=1)

  • Filter: (choices IS NULL)
10. 0.173 0.173 ↑ 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.145..0.173 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.166 0.241 ↑ 10,000.0 1 1

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

12. 0.074 0.075 ↑ 100.0 1 1

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.074..0.075 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.003 ↓ 0.0 0 1

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

15. 0.002 0.002 ↓ 0.0 0 1

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

  • Filter: (choices IS NOT NULL)
  • Rows Removed by Filter: 1
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.003 0.003 ↑ 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.003 rows=1 loops=1)

21.          

CTE get_person_device_notifications

22. 5.832 633.838 ↓ 4,303.5 8,607 1

Unique (cost=1,319.38..1,319.49 rows=2 width=344) (actual time=625.880..633.838 rows=8,607 loops=1)

23. 23.583 628.006 ↓ 4,303.5 8,607 1

Sort (cost=1,319.38..1,319.39 rows=2 width=344) (actual time=625.877..628.006 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
24. 1.816 604.423 ↓ 4,303.5 8,607 1

Append (cost=1.90..1,319.37 rows=2 width=344) (actual time=11.932..604.423 rows=8,607 loops=1)

25. 29.184 492.120 ↓ 8,566.0 8,566 1

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

26.          

Initplan (forNested Loop Left Join)

27. 0.007 0.008 ↑ 1.0 1 1

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

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

29. 0.006 0.007 ↑ 1.0 1 1

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

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

31. 0.005 0.005 ↑ 1.0 1 1

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

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

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

34. 0.002 0.002 ↑ 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.002 rows=1 loops=1)

35. 0.025 0.029 ↑ 1.0 1 1

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

36. 0.004 0.004 ↑ 1.0 1 1

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

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

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

39. 2.967 185.446 ↓ 8,385.0 8,385 1

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

40. 6.866 123.784 ↓ 8,385.0 8,385 1

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

41. 1.648 33.068 ↓ 8,385.0 8,385 1

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

42. 0.010 0.250 ↑ 1.0 1 1

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

  • Group Key: get_event.event_uuid
43. 0.240 0.240 ↑ 1.0 1 1

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

44. 1.174 31.170 ↓ 8,385.0 8,385 1

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

45. 29.996 29.996 ↓ 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=10.618..29.996 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
46. 8.385 83.850 ↑ 1.0 1 8,385

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

47. 75.465 75.465 ↑ 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.009..0.009 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)))
48. 8.385 58.695 ↑ 1.0 1 8,385

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

49. 50.310 50.310 ↑ 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.005..0.006 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)))
50. 243.165 243.165 ↓ 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.029..0.029 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))
51.          

SubPlan (forNested Loop Left Join)

52. 8.566 34.264 ↓ 0.0 0 8,566

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

  • Group Key: get_response_choices.text_response
53. 25.698 25.698 ↓ 0.0 0 8,566

CTE Scan on get_response_choices (cost=0.00..700.00 rows=200 width=32) (actual time=0.002..0.003 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
54. 0.060 110.487 ↓ 41.0 41 1

Hash Join (cost=516.47..544.03 rows=1 width=303) (actual time=110.449..110.487 rows=41 loops=1)

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

Initplan (forHash Join)

56. 0.009 0.010 ↑ 1.0 1 1

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

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

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

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

60. 0.037 0.046 ↑ 2,000.0 1 1

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

  • Group Key: COALESCE(rc_1.translated_response, rc_1.text_response)
61. 0.009 0.009 ↑ 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.009..0.009 rows=1 loops=1)

62. 0.135 110.365 ↓ 41.0 41 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 15kB
63. 2.637 110.230 ↓ 41.0 41 1

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

64. 1.013 18.193 ↓ 3,725.0 3,725 1

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

65. 0.008 0.010 ↑ 1.0 1 1

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

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

67. 0.536 17.170 ↓ 3,725.0 3,725 1

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

68. 16.634 16.634 ↓ 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.048..16.634 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
69. 89.400 89.400 ↓ 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.024..0.024 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))
70.          

CTE get_teams

71. 1.117 67.361 ↓ 6.0 6 1

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

  • Group Key: team_ntfns.team_ntfn_uuid, team_ntfns.team_parent_ntfn_uuid
72. 2.400 66.244 ↓ 3,725.0 3,725 1

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

  • Sort Key: team_ntfns.team_ntfn_uuid, team_ntfns.team_parent_ntfn_uuid
  • Sort Method: quicksort Memory: 460kB
73. 0.555 63.844 ↓ 3,725.0 3,725 1

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

74. 2.476 63.289 ↓ 3,725.0 3,725 1

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

  • Group Key: en.person_ntfn_uuid, en.team_ntfn_uuid, en.team_parent_ntfn_uuid
75. 4.609 60.813 ↓ 3,725.0 3,725 1

Sort (cost=7.09..7.09 rows=1 width=80) (actual time=60.038..60.813 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
76. 0.547 56.204 ↓ 3,725.0 3,725 1

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

77. 3.846 55.657 ↓ 3,725.0 3,725 1

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

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

Initplan (forGroupAggregate)

79. 0.007 0.008 ↑ 1.0 1 1

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

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

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

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

83. 8.452 51.796 ↓ 8,607.0 8,607 1

Sort (cost=6.96..6.97 rows=1 width=96) (actual time=50.961..51.796 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
84. 5.903 43.344 ↓ 8,607.0 8,607 1

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

85. 3.013 3.013 ↓ 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..3.013 rows=8,607 loops=1)

86. 8.607 34.428 ↑ 1.0 1 8,607

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

87. 25.821 25.821 ↑ 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.003..0.003 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)))
88.          

CTE get_team_groups

89. 0.005 67.613 ↑ 1.8 6 1

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

90. 0.000 67.560 ↓ 6.0 6 1

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

91.          

Initplan (forNested Loop)

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

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

94. 0.014 0.017 ↑ 1.0 1 1

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

95. 0.003 0.003 ↑ 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.003 rows=1 loops=1)

96. 0.014 67.386 ↓ 6.0 6 1

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

  • Group Key: get_teams.team_ntfn_uuid
97. 67.372 67.372 ↓ 6.0 6 1

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

98. 0.036 0.162 ↑ 1.0 1 6

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

99. 0.126 0.126 ↑ 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.021..0.021 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)))
100. 0.000 0.048 ↓ 0.0 0 1

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

101.          

Initplan (forNested Loop)

102. 0.000 0.000 ↓ 0.0 0

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

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

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

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

106. 0.006 0.006 ↑ 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.006 rows=6 loops=1)

107. 0.000 0.036 ↓ 0.0 0 6

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

108. 0.036 0.036 ↓ 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.006..0.006 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
109.          

CTE get_targeted_groups

110. 0.001 67.728 ↓ 0.0 0 1

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

111.          

Initplan (forNested Loop Left Join)

112. 0.000 0.000 ↓ 0.0 0

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

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

114. 0.003 67.727 ↓ 0.0 0 1

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

  • Group Key: team_group.notification_uuid, team_group.recipient_uuid
115. 0.002 67.724 ↓ 0.0 0 1

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

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

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

  • Filter: (team_group.""row"" = 1)
117. 0.020 67.721 ↓ 0.0 0 1

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

118. 0.078 67.701 ↓ 0.0 0 1

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

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

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

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

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

121. 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))
122.          

CTE get_total_person_uuids

123. 6.931 22.066 ↓ 1,862.5 3,725 1

HashAggregate (cost=0.05..0.07 rows=2 width=24) (actual time=20.867..22.066 rows=3,725 loops=1)

  • Group Key: en_1.person_uuid
124. 15.135 15.135 ↓ 4,303.5 8,607 1

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

125.          

CTE get_total_person_count

126. 0.299 0.801 ↑ 1.0 1 1

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

127. 0.502 0.502 ↓ 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.502 rows=3,725 loops=1)

128.          

CTE get_person_uuids

129. 0.007 24.446 ↓ 10.0 10 1

Limit (cost=0.06..0.06 rows=1 width=24) (actual time=24.442..24.446 rows=10 loops=1)

130. 0.535 24.439 ↓ 15.0 30 1

Sort (cost=0.05..0.06 rows=2 width=24) (actual time=24.435..24.439 rows=30 loops=1)

  • Sort Key: en_agg.delivered_date_min
  • Sort Method: top-N heapsort Memory: 29kB
131. 23.904 23.904 ↓ 1,862.5 3,725 1

CTE Scan on get_total_person_uuids en_agg (cost=0.00..0.04 rows=2 width=24) (actual time=20.871..23.904 rows=3,725 loops=1)

132.          

CTE get_person_and_roles

133. 0.018 28,788.963 ↑ 270.3 10 1

Nested Loop Left Join (cost=65.47..174,575.06 rows=2,703 width=421) (actual time=2,258.524..28,788.963 rows=10 loops=1)

  • Join Filter: (site.site_uuid = p.site_uuid)
134.          

Initplan (forNested Loop Left Join)

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

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

137. 0.005 0.009 ↑ 1.0 1 1

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

138. 0.004 0.004 ↑ 1.0 1 1

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

139. 0.094 28,771.024 ↑ 270.3 10 1

Nested Loop Left Join (cost=65.13..174,531.15 rows=2,703 width=408) (actual time=2,240.618..28,771.024 rows=10 loops=1)

140. 0.072 25,306.530 ↑ 270.3 10 1

Nested Loop (cost=0.59..9.72 rows=2,703 width=397) (actual time=1,212.811..25,306.530 rows=10 loops=1)

141. 0.074 0.078 ↓ 10.0 10 1

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

  • Group Key: get_person_uuids.person_uuid
142. 0.004 0.004 ↓ 10.0 10 1

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

143. 0.050 25,306.380 ↑ 1.0 1 10

Append (cost=0.57..9.67 rows=1 width=397) (actual time=872.739..2,530.638 rows=1 loops=10)

144. 25,306.330 25,306.330 ↑ 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=872.735..2,530.633 rows=1 loops=10)

  • 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 @> $41))
  • Rows Removed by Filter: 299
145. 0.680 3,464.400 ↑ 1.0 1 10

Aggregate (cost=64.54..64.55 rows=1 width=32) (actual time=346.439..346.440 rows=1 loops=10)

146. 0.300 3,463.720 ↑ 200.0 1 10

Hash Right Join (cost=22.31..55.53 rows=200 width=58) (actual time=346.357..346.372 rows=1 loops=10)

  • Hash Cond: (role.role_uuid = (((jsonb_array_elements_text(pr_1.roles)))::uuid))
147. 0.570 0.870 ↓ 2.9 23 10

Bitmap Heap Scan on roles role (cost=1.96..35.14 rows=8 width=58) (actual time=0.054..0.087 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
148. 0.300 0.300 ↓ 1.5 35 10

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

  • Index Cond: (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid)
149. 0.130 3,462.550 ↑ 200.0 1 10

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
150. 0.130 3,462.420 ↑ 200.0 1 10

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

151. 3,462.010 3,462.010 ↑ 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=346.089..346.201 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: 140
152. 0.060 0.280 ↑ 100.0 1 10

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

153. 0.210 0.220 ↑ 100.0 1 10

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

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

155. 0.062 17.900 ↓ 0.0 0 10

Materialize (cost=0.28..3.30 rows=1 width=29) (actual time=1.790..1.790 rows=0 loops=10)

156. 17.838 17.838 ↓ 0.0 0 1

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

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

CTE get_person_notifications

158. 0.017 16,887.237 ↓ 5.0 10 1

Subquery Scan on p_1 (cost=88.31..90.17 rows=2 width=304) (actual time=16,861.249..16,887.237 rows=10 loops=1)

159. 26.284 16,887.220 ↓ 5.0 10 1

GroupAggregate (cost=88.31..90.12 rows=2 width=272) (actual time=16,861.242..16,887.220 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
160.          

Initplan (forGroupAggregate)

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

162. 0.005 0.005 ↑ 1.0 1 1

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

163. 0.267 16,860.924 ↑ 1.3 27 1

Sort (cost=88.27..88.36 rows=34 width=326) (actual time=16,860.917..16,860.924 rows=27 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
164. 0.227 16,860.657 ↑ 1.3 27 1

Nested Loop Left Join (cost=0.59..87.41 rows=34 width=326) (actual time=1,187.002..16,860.657 rows=27 loops=1)

165. 1.575 653.491 ↓ 27.0 27 1

Hash Semi Join (cost=0.03..0.09 rows=1 width=280) (actual time=650.705..653.491 rows=27 loops=1)

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

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

167. 0.019 24.518 ↓ 10.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
168. 24.499 24.499 ↓ 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=24.446..24.499 rows=10 loops=1)

169. 0.162 16,206.939 ↑ 1.0 1 27

Append (cost=0.56..87.31 rows=1 width=62) (actual time=126.196..600.257 rows=1 loops=27)

170. 16,206.777 16,206.777 ↑ 1.0 1 27

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=126.192..600.251 rows=1 loops=27)

  • Index Cond: (recipient_uuid = get_person_device_notifications.device_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> $48) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid))
  • Rows Removed by Filter: 150
171.          

Initplan (forSort)

172. 0.805 0.805 ↑ 1.0 1 1

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

173. 0.005 0.005 ↑ 1.0 1 1

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

174. 1.000 45,747.638 ↑ 2.7 10 1

Hash Right Join (cost=2,112.59..2,177.46 rows=27 width=432) (actual time=19,217.045..45,747.638 rows=10 loops=1)

  • Hash Cond: (get_person_and_roles.person_uuid = pn.person_uuid)
175. 28,789.026 28,789.026 ↑ 270.3 10 1

CTE Scan on get_person_and_roles (cost=0.00..54.06 rows=2,703 width=224) (actual time=2,258.533..28,789.026 rows=10 loops=1)

176. 0.061 16,957.612 ↓ 5.0 10 1

Hash (cost=2,112.57..2,112.57 rows=2 width=232) (actual time=16,957.612..16,957.612 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
177. 0.019 16,957.551 ↓ 5.0 10 1

Nested Loop Left Join (cost=354.37..2,112.57 rows=2 width=232) (actual time=16,955.534..16,957.551 rows=10 loops=1)

178. 0.022 16,889.792 ↓ 5.0 10 1

Nested Loop Left Join (cost=354.34..2,112.45 rows=2 width=232) (actual time=16,887.794..16,889.792 rows=10 loops=1)

179. 0.026 16,889.660 ↓ 5.0 10 1

Nested Loop Left Join (cost=176.57..1,756.85 rows=2 width=232) (actual time=16,887.780..16,889.660 rows=10 loops=1)

180. 1.136 16,888.594 ↓ 5.0 10 1

GroupAggregate (cost=0.05..1,403.73 rows=2 width=248) (actual time=16,887.621..16,888.594 rows=10 loops=1)

  • Group Key: pn.person_uuid, pn.event_uuid
181. 0.139 16,887.458 ↓ 5.0 10 1

Sort (cost=0.05..0.06 rows=2 width=288) (actual time=16,887.452..16,887.458 rows=10 loops=1)

  • Sort Key: pn.person_uuid, pn.event_uuid
  • Sort Method: quicksort Memory: 42kB
182. 16,887.319 16,887.319 ↓ 5.0 10 1

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

183.          

SubPlan (forGroupAggregate)

184. 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
185. 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)))
186. 0.890 1.040 ↑ 1.0 1 10

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

187. 0.070 0.150 ↑ 3,333.3 3 10

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

188. 0.080 0.080 ↑ 100.0 1 10

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

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

190. 0.080 0.110 ↑ 1.0 1 10

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

191. 0.020 0.030 ↓ 0.0 0 10

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

192. 0.010 0.010 ↓ 0.0 0 10

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

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

194. 0.010 67.740 ↑ 1.0 1 10

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

195. 67.730 67.730 ↓ 0.0 0 10

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

  • Filter: (team_ntfn_uuids && (array_agg(pn.team_uuid)))
Planning time : 2,739.079 ms
Execution time : 45,761.187 ms