explain.depesz.com

PostgreSQL's explain analyze made readable

Result: u0BW

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 9,332.124 ↓ 5.0 10 1

Sort (cost=7,579.72..7,579.73 rows=2 width=432) (actual time=9,332.123..9,332.124 rows=10 loops=1)

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

CTE get_event

3. 0.003 0.127 ↑ 1.0 1 1

Limit (cost=0.56..3.58 rows=1 width=727) (actual time=0.125..0.127 rows=1 loops=1)

4. 0.000 0.124 ↑ 1.0 1 1

Append (cost=0.56..3.58 rows=1 width=727) (actual time=0.124..0.124 rows=1 loops=1)

5. 0.124 0.124 ↑ 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=727) (actual time=0.124..0.124 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-08-26 15:46:57+00'::timestamp with time zone)
  • Rows Removed by Filter: 1
6.          

CTE get_response_choices

7. 0.004 0.320 ↑ 20,000.0 1 1

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

8. 0.008 0.313 ↑ 10,000.0 1 1

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

9. 0.005 0.145 ↑ 1.0 1 1

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

10. 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)
11. 0.137 0.137 ↑ 1.0 1 1

Index Scan using forms_org_seq_vrange_uidx on forms f (cost=0.42..3.44 rows=1 width=1,026) (actual time=0.113..0.137 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
12. 0.118 0.160 ↑ 10,000.0 1 1

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

13. 0.041 0.042 ↑ 100.0 1 1

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

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

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

16. 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
17. 0.000 0.000 ↓ 0.0 0

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

18. 0.000 0.000 ↓ 0.0 0

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

19. 0.000 0.000 ↓ 0.0 0

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

20.          

CTE get_filtered_response_choices

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.          

CTE get_event_notifications

23. 5.937 445.491 ↓ 4,303.5 8,607 1

Unique (cost=3,023.97..3,024.06 rows=2 width=296) (actual time=437.430..445.491 rows=8,607 loops=1)

24. 15.386 439.554 ↓ 4,303.5 8,607 1

Sort (cost=3,023.97..3,023.98 rows=2 width=296) (actual time=437.426..439.554 rows=8,607 loops=1)

  • Sort Key: person_ntfn.recipient_uuid, person_ntfn.notification_uuid, person_ntfn.parent_ntfn_uuid, (CASE WHEN (person_ntfn.delinked_date <= '2019-08-26 15:46:57+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-08-26 15:46:57+00'::timestamp with time zone) THEN device_ntfn.failed_date ELSE NULL::timestamp with time zone END), (CASE WHEN (device_ntfn.delivered_date <= '2019-08-26 15:46:57+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-08-26 15:46:57+00'::timestamp with time zone)) THEN device_response.received ELSE NULL::timestamp with time zone END), (CASE WHEN ((device_response.received <= '2019-08-26 15:46:57+00'::timestamp with time zone) AND ((device_response.response)::text <> 'XMATTERS_REMOVE'::text)) THEN 'RESPONDED'::text WHEN (device_ntfn.delivered_date <= '2019-08-26 15:46:57+00'::timestamp with time zone) THEN 'DELIVERED'::text WHEN (device_ntfn.failed_date <= '2019-08-26 15:46:57+00'::timestamp with time zone) THEN 'FAILED'::text WHEN (live_ntfn.pending_date <= '2019-08-26 15:46:57+00'::timestamp with time zone) THEN 'PENDING'::text ELSE ''::text END), (CASE WHEN (live_ntfn.pending_date <= '2019-08-26 15:46:57+00'::timestamp with time zone) THEN live_ntfn.pending_date ELSE NULL::timestamp with time zone END), ((SubPlan 4)), (CASE WHEN (device_response.received <= '2019-08-26 15:46:57+00'::timestamp with time zone) THEN device_response.received ELSE NULL::timestamp with time zone END), device_response.recipient_type, (NULL::character varying), (NULL::timestamp with time zone), (NULL::character varying)
  • Sort Method: quicksort Memory: 2660kB
25. 1.260 424.168 ↓ 4,303.5 8,607 1

Append (cost=1.90..3,023.96 rows=2 width=296) (actual time=8.597..424.168 rows=8,607 loops=1)

26. 16.183 343.089 ↓ 8,566.0 8,566 1

Nested Loop Left Join (cost=1.90..778.72 rows=1 width=271) (actual time=8.594..343.089 rows=8,566 loops=1)

27.          

Initplan (forNested Loop Left Join)

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

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

30. 0.005 0.006 ↑ 1.0 1 1

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

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

32. 0.005 0.005 ↑ 1.0 1 1

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

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

34. 0.007 0.008 ↑ 1.0 1 1

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

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

36. 0.021 0.024 ↑ 1.0 1 1

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

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

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

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

40. 4.618 141.837 ↓ 8,385.0 8,385 1

Nested Loop (cost=1.13..73.12 rows=1 width=112) (actual time=7.985..141.837 rows=8,385 loops=1)

41. 5.853 86.909 ↓ 8,385.0 8,385 1

Nested Loop (cost=0.72..69.80 rows=1 width=72) (actual time=7.936..86.909 rows=8,385 loops=1)

42. 1.343 22.361 ↓ 8,385.0 8,385 1

Nested Loop (cost=0.31..66.34 rows=1 width=24) (actual time=7.835..22.361 rows=8,385 loops=1)

43. 0.008 0.139 ↑ 1.0 1 1

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

  • Group Key: get_event.event_uuid
44. 0.131 0.131 ↑ 1.0 1 1

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

45. 0.953 20.879 ↓ 8,385.0 8,385 1

Append (cost=0.29..66.30 rows=1 width=40) (actual time=7.691..20.879 rows=8,385 loops=1)

46. 19.926 19.926 ↓ 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..66.30 rows=1 width=40) (actual time=7.690..19.926 rows=8,385 loops=1)

  • Index Cond: (event_uuid = get_event.event_uuid)
  • Filter: ((created_date >= $13) AND (pending_date <= '2019-08-26 15:46:57+00'::timestamp with time zone) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (category = 'LIVE'::text) AND (created_date <= LEAST('2019-08-26 15:46:57+00'::timestamp with time zone, $12)))
  • Rows Removed by Filter: 12077
47. 0.000 58.695 ↑ 1.0 1 8,385

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

48. 58.695 58.695 ↑ 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.007..0.007 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-08-26 15:46:57+00'::timestamp with time zone, $8)))
49. 0.000 50.310 ↑ 1.0 1 8,385

Append (cost=0.41..3.31 rows=1 width=56) (actual time=0.006..0.006 rows=1 loops=8,385)

50. 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.30 rows=1 width=56) (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-08-26 15:46:57+00'::timestamp with time zone, $10)))
51. 159.315 159.315 ↓ 0.0 0 8,385

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

  • Index Cond: (notification_uuid = device_ntfn.notification_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-08-26 15:46:57+00'::timestamp with time zone) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid))
52.          

SubPlan (forNested Loop Left Join)

53. 8.566 25.698 ↓ 0.0 0 8,566

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

  • Group Key: get_response_choices.text_response
54. 17.132 17.132 ↓ 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.002 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
55. 0.118 79.819 ↓ 41.0 41 1

Nested Loop (cost=450.63..2,245.22 rows=1 width=255) (actual time=7.447..79.819 rows=41 loops=1)

  • Join Filter: ((pr.response)::text = COALESCE(rc_1.translated_response, rc_1.text_response))
  • Rows Removed by Join Filter: 1
56.          

Initplan (forNested Loop)

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

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

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

60. 0.003 0.003 ↑ 1.0 1 1

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

61. 1.878 79.309 ↓ 42.0 42 1

Nested Loop (cost=0.56..1,750.15 rows=1 width=87) (actual time=5.992..79.309 rows=42 loops=1)

62. 0.424 15.959 ↓ 147.8 3,842 1

Append (cost=0.00..1,656.68 rows=26 width=56) (actual time=5.946..15.959 rows=3,842 loops=1)

63. 15.535 15.535 ↓ 147.8 3,842 1

Seq Scan on notifications_summary_org_part_454 person_resp_ntfn (cost=0.00..1,656.55 rows=26 width=56) (actual time=5.945..15.535 rows=3,842 loops=1)

  • Filter: ((created_date >= $15) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (category = 'PERSON'::text) AND (created_date <= LEAST('2019-08-26 15:46:57+00'::timestamp with time zone, $14)))
  • Rows Removed by Filter: 24136
64. 61.472 61.472 ↓ 0.0 0 3,842

Index Scan using responses_notification_uuid_idx on responses pr (cost=0.56..3.58 rows=1 width=47) (actual time=0.016..0.016 rows=0 loops=3,842)

  • Index Cond: (notification_uuid = person_resp_ntfn.notification_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-08-26 15:46:57+00'::timestamp with time zone) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND ((recipient_type)::text = 'PERSON'::text))
65. 0.372 0.378 ↑ 2,000.0 1 42

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

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

67.          

CTE get_total_person_uuids

68. 15.895 35.597 ↓ 1,862.5 3,725 1

GroupAggregate (cost=0.05..0.11 rows=2 width=96) (actual time=18.417..35.597 rows=3,725 loops=1)

  • Group Key: en.person_uuid
69. 5.359 19.702 ↓ 4,303.5 8,607 1

Sort (cost=0.05..0.06 rows=2 width=64) (actual time=18.358..19.702 rows=8,607 loops=1)

  • Sort Key: en.person_uuid
  • Sort Method: quicksort Memory: 1592kB
70. 14.343 14.343 ↓ 4,303.5 8,607 1

CTE Scan on get_event_notifications en (cost=0.00..0.04 rows=2 width=64) (actual time=0.004..14.343 rows=8,607 loops=1)

71.          

CTE get_person_uuids

72. 0.019 40.781 ↓ 10.0 10 1

Limit (cost=0.06..0.06 rows=1 width=96) (actual time=40.776..40.781 rows=10 loops=1)

73. 1.275 40.762 ↓ 150.0 300 1

Sort (cost=0.05..0.06 rows=2 width=96) (actual time=40.658..40.762 rows=300 loops=1)

  • Sort Key: en_agg.delivered_date_min
  • Sort Method: top-N heapsort Memory: 154kB
74. 39.487 39.487 ↓ 1,862.5 3,725 1

CTE Scan on get_total_person_uuids en_agg (cost=0.00..0.04 rows=2 width=96) (actual time=18.421..39.487 rows=3,725 loops=1)

75.          

CTE get_person_notifications

76. 0.012 499.479 ↓ 5.0 10 1

Subquery Scan on p (cost=93.48..93.66 rows=2 width=288) (actual time=498.025..499.479 rows=10 loops=1)

77. 1.877 499.467 ↓ 5.0 10 1

GroupAggregate (cost=93.48..93.61 rows=2 width=256) (actual time=498.020..499.467 rows=10 loops=1)

  • Group Key: get_event_notifications.person_uuid, get_event_notifications.person_ntfn_uuid, get_event_notifications.person_delinked_date, get_event_notifications.team_uuid, get_event_notifications.person_response_value, get_event_notifications.person_response_received, get_event_notifications.person_response_recipient_type
78. 0.128 497.590 ↓ 12.0 24 1

Sort (cost=93.48..93.49 rows=2 width=310) (actual time=497.585..497.590 rows=24 loops=1)

  • Sort Key: get_event_notifications.person_uuid, get_event_notifications.person_ntfn_uuid, get_event_notifications.person_delinked_date, get_event_notifications.team_uuid, get_event_notifications.person_response_value, get_event_notifications.person_response_received, get_event_notifications.person_response_recipient_type
  • Sort Method: quicksort Memory: 31kB
79. 0.085 497.462 ↓ 12.0 24 1

Nested Loop Left Join (cost=0.59..93.47 rows=2 width=310) (actual time=478.623..497.462 rows=24 loops=1)

80. 1.398 481.057 ↓ 24.0 24 1

Hash Semi Join (cost=0.03..0.09 rows=1 width=264) (actual time=478.430..481.057 rows=24 loops=1)

  • Hash Cond: (get_event_notifications.person_uuid = get_person_uuids.person_uuid)
81. 438.852 438.852 ↓ 4,303.5 8,607 1

CTE Scan on get_event_notifications (cost=0.00..0.04 rows=2 width=264) (actual time=437.434..438.852 rows=8,607 loops=1)

82. 0.015 40.807 ↓ 10.0 10 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
83. 40.792 40.792 ↓ 10.0 10 1

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

84. 16.320 16.320 ↑ 2.0 1 24

Index Scan using devices_recipient_uuid_idx on devices d (cost=0.56..93.36 rows=2 width=62) (actual time=0.098..0.680 rows=1 loops=24)

  • Index Cond: (recipient_uuid = get_event_notifications.device_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-08-26 15:46:57+00'::timestamp with time zone) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid))
  • Rows Removed by Filter: 152
85.          

CTE get_person_and_roles

86. 0.080 8,730.569 ↓ 10.0 10 1

Nested Loop Left Join (cost=64.34..75.00 rows=1 width=420) (actual time=4.788..8,730.569 rows=10 loops=1)

87. 0.077 8,730.259 ↓ 10.0 10 1

Nested Loop Left Join (cost=63.92..74.32 rows=1 width=406) (actual time=4.720..8,730.259 rows=10 loops=1)

88. 0.054 7,968.602 ↓ 10.0 10 1

Nested Loop (cost=0.71..11.09 rows=1 width=395) (actual time=3.001..7,968.602 rows=10 loops=1)

89. 0.034 0.038 ↓ 10.0 10 1

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

  • Group Key: get_person_uuids_1.person_uuid
90. 0.004 0.004 ↓ 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.002..0.004 rows=10 loops=1)

91. 7,968.510 7,968.510 ↑ 1.0 1 10

Index Scan using persons_org_seq_vrange_uidx on persons p_1 (cost=0.69..11.04 rows=1 width=395) (actual time=760.866..796.851 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-08-26 15:46:57+00'::timestamp with time zone))
  • Rows Removed by Filter: 305
92. 0.630 761.580 ↑ 1.0 1 10

Aggregate (cost=63.20..63.21 rows=1 width=32) (actual time=76.158..76.158 rows=1 loops=10)

93. 0.270 760.950 ↑ 200.0 1 10

Hash Right Join (cost=22.38..54.20 rows=200 width=57) (actual time=76.082..76.095 rows=1 loops=10)

  • Hash Cond: (role.role_uuid = (((jsonb_array_elements_text(pr_1.roles)))::uuid))
94. 0.470 0.720 ↓ 3.3 23 10

Bitmap Heap Scan on roles role (cost=1.96..33.73 rows=7 width=57) (actual time=0.045..0.072 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
95. 0.250 0.250 ↓ 1.6 35 10

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

  • Index Cond: (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid)
96. 0.050 759.960 ↑ 200.0 1 10

Hash (cost=17.93..17.93 rows=200 width=16) (actual time=75.996..75.996 rows=1 loops=10)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
97. 0.090 759.910 ↑ 200.0 1 10

Nested Loop Left Join (cost=0.56..17.93 rows=200 width=16) (actual time=75.973..75.991 rows=1 loops=10)

98. 759.550 759.550 ↑ 2.0 1 10

Index Scan using person_roles_org_seq_vrange_uidx on person_roles pr_1 (cost=0.56..9.39 rows=2 width=34) (actual time=75.940..75.955 rows=1 loops=10)

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

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

100. 0.180 0.190 ↑ 100.0 1 10

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

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

102. 0.230 0.230 ↑ 1.0 1 10

Index Scan using sites_org_site_uuid_idx on sites site (cost=0.42..0.68 rows=1 width=30) (actual time=0.023..0.023 rows=1 loops=10)

  • Index Cond: ((organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid) AND (site_uuid = p_1.site_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-08-26 15:46:57+00'::timestamp with time zone))
103.          

CTE get_teams

104. 0.550 97.689 ↓ 6.0 6 1

GroupAggregate (cost=505.73..505.76 rows=1 width=56) (actual time=96.969..97.689 rows=6 loops=1)

  • Group Key: team_ntfns.team_ntfn_uuid, team_ntfns.team_parent_ntfn_uuid
105. 1.647 97.139 ↓ 3,725.0 3,725 1

Sort (cost=505.73..505.73 rows=1 width=56) (actual time=96.953..97.139 rows=3,725 loops=1)

  • Sort Key: team_ntfns.team_ntfn_uuid, team_ntfns.team_parent_ntfn_uuid
  • Sort Method: quicksort Memory: 460kB
106. 0.516 95.492 ↓ 3,725.0 3,725 1

Subquery Scan on team_ntfns (cost=505.68..505.72 rows=1 width=56) (actual time=91.744..95.492 rows=3,725 loops=1)

107. 2.499 94.976 ↓ 3,725.0 3,725 1

GroupAggregate (cost=505.68..505.71 rows=1 width=72) (actual time=91.741..94.976 rows=3,725 loops=1)

  • Group Key: n.person_ntfn_uuid, team_ntfn.notification_uuid, team_ntfn.parent_ntfn_uuid
108. 3.533 92.477 ↓ 3,731.0 3,731 1

Sort (cost=505.68..505.68 rows=1 width=80) (actual time=91.726..92.477 rows=3,731 loops=1)

  • Sort Key: n.person_ntfn_uuid, team_ntfn.notification_uuid, team_ntfn.parent_ntfn_uuid
  • Sort Method: quicksort Memory: 621kB
109. 2.894 88.944 ↓ 3,731.0 3,731 1

Nested Loop Left Join (cost=457.59..505.67 rows=1 width=80) (actual time=46.398..88.944 rows=3,731 loops=1)

110. 4.959 52.525 ↓ 3,725.0 3,725 1

GroupAggregate (cost=7.03..7.06 rows=1 width=120) (actual time=46.358..52.525 rows=3,725 loops=1)

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

Initplan (forGroupAggregate)

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

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

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

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

116. 7.239 47.552 ↓ 8,607.0 8,607 1

Sort (cost=6.96..6.97 rows=1 width=88) (actual time=46.341..47.552 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
117. 2.684 40.313 ↓ 8,607.0 8,607 1

Nested Loop (cost=0.41..6.96 rows=1 width=88) (actual time=0.061..40.313 rows=8,607 loops=1)

118. 3.201 3.201 ↓ 4,303.5 8,607 1

CTE Scan on get_event_notifications n (cost=0.00..0.04 rows=2 width=72) (actual time=0.001..3.201 rows=8,607 loops=1)

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

120. 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 >= $33) AND (category = 'TEAM'::text) AND (created_date <= LEAST('2019-08-26 15:46:57+00'::timestamp with time zone, $32)))
121. 0.000 33.525 ↓ 0.0 0 3,725

Nested Loop (cost=450.56..498.58 rows=1 width=24) (actual time=0.009..0.009 rows=0 loops=3,725)

  • Join Filter: ((person_response.response)::text = COALESCE(rc_2.translated_response, rc_2.text_response))
122. 33.525 33.525 ↓ 0.0 0 3,725

Index Scan using responses_notification_uuid_idx on responses person_response (cost=0.56..3.58 rows=1 width=40) (actual time=0.009..0.009 rows=0 loops=3,725)

  • Index Cond: (notification_uuid = n.person_ntfn_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2019-08-26 15:46:57+00'::timestamp with time zone) AND (organization_uuid = 'fe857c89-72ec-4b77-ab22-96bd295ece2c'::uuid))
123. 0.242 0.246 ↑ 2,000.0 1 41

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

  • Group Key: COALESCE(rc_2.translated_response, rc_2.text_response)
124. 0.004 0.004 ↑ 20,000.0 1 1

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

125.          

CTE get_team_groups

126. 0.005 97.869 ↑ 1.8 6 1

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

127. 0.000 97.824 ↓ 6.0 6 1

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

128.          

Initplan (forNested Loop)

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

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

131. 0.004 0.006 ↑ 1.0 1 1

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

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

133. 0.009 97.704 ↓ 6.0 6 1

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

  • Group Key: get_teams.team_ntfn_uuid
134. 97.695 97.695 ↓ 6.0 6 1

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

135. 0.000 0.108 ↑ 1.0 1 6

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

136. 0.108 0.108 ↑ 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.018 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 >= $39) AND (category = 'TEAM'::text) AND (created_date <= LEAST('2019-08-26 15:46:57+00'::timestamp with time zone, $38)))
137. 0.000 0.040 ↓ 0.0 0 1

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

138.          

Initplan (forNested Loop)

139. 0.000 0.000 ↓ 0.0 0

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

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

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

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

143. 0.004 0.004 ↑ 1.7 6 1

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

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

145. 0.030 0.030 ↓ 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.005..0.005 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 >= $41) AND (category = ANY ('{TEAM,GROUP}'::text[])) AND (created_date <= LEAST('2019-08-26 15:46:57+00'::timestamp with time zone, $40)))
  • Rows Removed by Filter: 1
146.          

CTE get_targeted_groups

147. 0.001 97.955 ↓ 0.0 0 1

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

148.          

Initplan (forNested Loop Left Join)

149. 0.000 0.000 ↓ 0.0 0

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

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

151. 0.002 97.954 ↓ 0.0 0 1

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

  • Group Key: team_group.notification_uuid, team_group.recipient_uuid
152. 0.001 97.952 ↓ 0.0 0 1

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

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

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

  • Filter: (team_group.""row"" = 1)
154. 0.015 97.950 ↓ 0.0 0 1

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

155. 0.052 97.935 ↓ 0.0 0 1

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

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

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

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

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

158. 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 @> $45))
159.          

Initplan (forSort)

160. 0.354 1.028 ↑ 1.0 1 1

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

161. 0.674 0.674 ↓ 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.002..0.674 rows=3,725 loops=1)

162. 0.003 0.003 ↑ 1.0 1 1

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

163. 1.117 9,331.990 ↓ 5.0 10 1

Hash Left Join (cost=354.40..2,112.64 rows=2 width=432) (actual time=9,329.776..9,331.990 rows=10 loops=1)

  • Hash Cond: (pn.person_uuid = get_person_and_roles.person_uuid)
164. 0.015 600.203 ↓ 5.0 10 1

Nested Loop Left Join (cost=354.37..2,112.56 rows=2 width=232) (actual time=598.011..600.203 rows=10 loops=1)

165. 0.013 502.218 ↓ 5.0 10 1

Nested Loop Left Join (cost=354.34..2,112.44 rows=2 width=232) (actual time=500.046..502.218 rows=10 loops=1)

166. 0.023 502.115 ↓ 5.0 10 1

Nested Loop Left Join (cost=176.57..1,756.84 rows=2 width=232) (actual time=499.996..502.115 rows=10 loops=1)

167. 1.298 500.872 ↓ 5.0 10 1

GroupAggregate (cost=0.05..1,403.72 rows=2 width=232) (actual time=499.808..500.872 rows=10 loops=1)

  • Group Key: pn.person_uuid
168. 0.051 499.574 ↓ 5.0 10 1

Sort (cost=0.05..0.06 rows=2 width=272) (actual time=499.569..499.574 rows=10 loops=1)

  • Sort Key: pn.person_uuid
  • Sort Method: quicksort Memory: 41kB
169. 499.523 499.523 ↓ 5.0 10 1

CTE Scan on get_person_notifications pn (cost=0.00..0.04 rows=2 width=272) (actual time=498.037..499.523 rows=10 loops=1)

170.          

SubPlan (forGroupAggregate)

171. 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
172. 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)))
173. 1.080 1.220 ↑ 1.0 1 10

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

174. 0.080 0.140 ↑ 5,000.0 2 10

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

175. 0.060 0.060 ↑ 100.0 1 10

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

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

177. 0.060 0.090 ↑ 1.0 1 10

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

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

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

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

181. 0.010 97.970 ↑ 1.0 1 10

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

182. 97.960 97.960 ↓ 0.0 0 10

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

  • Filter: (team_ntfn_uuids && (array_agg(pn.team_uuid)))
183. 0.033 8,730.670 ↓ 10.0 10 1

Hash (cost=0.02..0.02 rows=1 width=224) (actual time=8,730.670..8,730.670 rows=10 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
184. 8,730.637 8,730.637 ↓ 10.0 10 1

CTE Scan on get_person_and_roles (cost=0.00..0.02 rows=1 width=224) (actual time=4.793..8,730.637 rows=10 loops=1)

Planning time : 856.934 ms
Execution time : 9,341.556 ms