explain.depesz.com

PostgreSQL's explain analyze made readable

Result: RlNo

Settings
# exclusive inclusive rows x rows loops node
1. 1.522 450.825 ↓ 1,862.5 3,725 1

Subquery Scan on en (cost=4,436.23..4,436.37 rows=2 width=200) (actual time=444.144..450.825 rows=3,725 loops=1)

2.          

CTE get_event

3. 0.004 0.173 ↑ 1.0 1 1

Limit (cost=0.56..3.58 rows=1 width=727) (actual time=0.171..0.173 rows=1 loops=1)

4. 0.000 0.169 ↑ 1.0 1 1

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

5. 0.169 0.169 ↑ 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.168..0.169 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
6.          

CTE get_response_choices

7. 0.002 0.299 ↑ 20,000.0 1 1

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

8. 0.008 0.295 ↑ 10,000.0 1 1

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

9. 0.005 0.117 ↑ 1.0 1 1

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

10. 0.002 0.002 ↑ 1.0 1 1

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

  • Filter: (choices IS NULL)
11. 0.110 0.110 ↑ 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.091..0.110 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.095 0.170 ↑ 10,000.0 1 1

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

13. 0.074 0.075 ↑ 100.0 1 1

ProjectSet (cost=0.00..0.52 rows=100 width=32) (actual time=0.075..0.075 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.001..0.001 rows=1 loops=1)

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

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

22.          

CTE get_event_notifications

23. 4.082 434.607 ↓ 4,303.5 8,607 1

Unique (cost=3,023.97..3,024.07 rows=2 width=312) (actual time=428.913..434.607 rows=8,607 loops=1)

24. 15.144 430.525 ↓ 4,303.5 8,607 1

Sort (cost=3,023.97..3,023.98 rows=2 width=312) (actual time=428.909..430.525 rows=8,607 loops=1)

  • Sort Key: person_ntfn.recipient_uuid, person_ntfn.notification_uuid, person_ntfn.parent_ntfn_uuid, person_ntfn.event_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), ((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::character varying), (NULL::timestamp with time zone), (NULL::character varying)
  • Sort Method: quicksort Memory: 2666kB
25. 1.323 415.381 ↓ 4,303.5 8,607 1

Append (cost=1.90..3,023.96 rows=2 width=312) (actual time=8.061..415.381 rows=8,607 loops=1)

26. 12.244 335.434 ↓ 8,566.0 8,566 1

Nested Loop Left Join (cost=1.90..778.72 rows=1 width=287) (actual time=8.059..335.434 rows=8,566 loops=1)

27.          

Initplan (forNested Loop Left Join)

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

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

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.003 0.003 ↑ 1.0 1 1

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

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.003 0.004 ↑ 1.0 1 1

Aggregate (cost=0.02..0.03 rows=1 width=8) (actual time=0.003..0.004 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.000..0.001 rows=1 loops=1)

36. 0.024 0.029 ↑ 1.0 1 1

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

37. 0.005 0.005 ↑ 1.0 1 1

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

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

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. 8.477 129.742 ↓ 8,385.0 8,385 1

Nested Loop (cost=1.13..73.12 rows=1 width=128) (actual time=7.496..129.742 rows=8,385 loops=1)

41. 6.062 87.725 ↓ 8,385.0 8,385 1

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

42. 1.305 22.968 ↓ 8,385.0 8,385 1

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

43. 0.007 0.184 ↑ 1.0 1 1

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

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

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

45. 0.999 21.479 ↓ 8,385.0 8,385 1

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

46. 20.480 20.480 ↓ 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.161..20.480 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
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-09-04 17:12:18+00'::timestamp with time zone, $8)))
49. 0.000 33.540 ↑ 1.0 1 8,385

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

50. 33.540 33.540 ↑ 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=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 = 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)))
51. 167.700 167.700 ↓ 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.020..0.020 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))
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.067 78.624 ↓ 41.0 41 1

Nested Loop (cost=450.63..2,245.22 rows=1 width=271) (actual time=9.103..78.624 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.006 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)

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

61. 0.597 78.289 ↓ 42.0 42 1

Nested Loop (cost=0.56..1,750.15 rows=1 width=103) (actual time=7.662..78.289 rows=42 loops=1)

62. 0.330 16.220 ↓ 147.8 3,842 1

Append (cost=0.00..1,656.68 rows=26 width=72) (actual time=7.587..16.220 rows=3,842 loops=1)

63. 15.890 15.890 ↓ 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=72) (actual time=7.587..15.890 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-09-04 17:12:18+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-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))
65. 0.245 0.252 ↑ 2,000.0 1 42

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

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

67. 4.693 449.303 ↓ 1,862.5 3,725 1

GroupAggregate (cost=0.05..0.14 rows=2 width=152) (actual time=444.122..449.303 rows=3,725 loops=1)

  • Group Key: get_event_notifications.person_uuid, get_event_notifications.person_ntfn_uuid, get_event_notifications.event_uuid
68. 4.827 444.610 ↓ 4,303.5 8,607 1

Sort (cost=0.05..0.06 rows=2 width=120) (actual time=444.070..444.610 rows=8,607 loops=1)

  • Sort Key: get_event_notifications.person_uuid, get_event_notifications.person_ntfn_uuid, get_event_notifications.event_uuid
  • Sort Method: quicksort Memory: 1595kB
69. 439.783 439.783 ↓ 4,303.5 8,607 1

CTE Scan on get_event_notifications (cost=0.00..0.04 rows=2 width=120) (actual time=428.924..439.783 rows=8,607 loops=1)

Planning time : 768.342 ms
Execution time : 454.951 ms