explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 9qZ6

Settings
# exclusive inclusive rows x rows loops node
1. 0.850 5,150.847 ↑ 16.0 100 1

GroupAggregate (cost=10,082,587.64..10,083,557.64 rows=1,600 width=652) (actual time=5,150.010..5,150.847 rows=100 loops=1)

  • Group Key: e.event_id, e.event_uuid, e.submitter_uuid, e.priority, e.incident_id, e.override_device_restrictions, e.escalation_override, e.bypass_phone_intro, e.require_phone_password, e.conference_properties, e.created, e.status, e.terminated, e.request_uuid, e.sequence, e.revision_uuid, e.event_type, e.system_event_type, e.form_uuid, e.plan_uuid, e.override_choices, e.valid_from, e.valid_to, (COALESCE(e.system_event_type, m.subject)), e.person_target_name, e.person_first_name, e.person_last_name, e.form_name, e.plan_name, e.integration_config_type
2.          

CTE filtered_events

3. 0.018 3.896 ↑ 1.0 100 1

Limit (cost=1.80..5.82 rows=100 width=721) (actual time=0.332..3.896 rows=100 loops=1)

4. 0.104 3.878 ↑ 40,573.2 100 1

Nested Loop Left Join (cost=1.80..163,174.62 rows=4,057,320 width=721) (actual time=0.332..3.878 rows=100 loops=1)

5. 0.070 3.074 ↑ 138.0 100 1

Nested Loop Left Join (cost=1.39..75,950.57 rows=13,804 width=702) (actual time=0.192..3.074 rows=100 loops=1)

6. 0.102 2.504 ↑ 138.0 100 1

Nested Loop Left Join (cost=0.97..48,779.46 rows=13,804 width=681) (actual time=0.135..2.504 rows=100 loops=1)

  • Filter: ((pl.plan_uuid IS NULL) OR ((pl.type)::text <> 'SYSTEM'::text))
  • Rows Removed by Filter: 2
7. 1.280 1.280 ↑ 139.2 102 1

Index Scan Backward using events_org_part_252_event_id_idx on events_org_part_252 e_2 (cost=0.41..8,934.88 rows=14,195 width=654) (actual time=0.066..1.280 rows=102 loops=1)

  • Filter: ((is_visible IS TRUE) AND (system_event_type IS NULL) AND (valid_range @> '2020-01-14 18:07:44+00'::timestamp with time zone) AND (created >= '2019-01-07 18:07:12+00'::timestamp with time zone) AND (created <= '2020-01-14 18:07:12+00'::timestamp with time zone) AND (organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid) AND (created <= now()))
  • Rows Removed by Filter: 188
8. 1.122 1.122 ↑ 1.0 1 102

Index Scan using plans_org_seq_vrange_uidx on plans pl (cost=0.55..2.79 rows=1 width=69) (actual time=0.004..0.011 rows=1 loops=102)

  • Index Cond: ((organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid) AND (plan_uuid = e_2.plan_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> e_2.created))
  • Rows Removed by Filter: 18
9. 0.500 0.500 ↑ 1.0 1 100

Index Scan using forms_org_seq_vrange_uidx on forms f (cost=0.42..1.96 rows=1 width=56) (actual time=0.005..0.005 rows=1 loops=100)

  • Index Cond: ((organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid) AND (form_uuid = e_2.form_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> e_2.created))
  • Rows Removed by Filter: 3
10. 0.100 0.700 ↑ 2.0 1 100

Append (cost=0.42..4.83 rows=2 width=38) (actual time=0.007..0.007 rows=1 loops=100)

11. 0.300 0.300 ↓ 0.0 0 100

Index Scan using persons_org_part_10_l2_f_valid_range_idx on persons_org_part_10_l2_f p_2 (cost=0.42..3.44 rows=1 width=38) (actual time=0.003..0.003 rows=0 loops=100)

  • Index Cond: (valid_range @> '2020-01-14 18:07:44+00'::timestamp with time zone)
  • Filter: ((is_visible IS TRUE) AND (organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid) AND (e_2.submitter_uuid = person_uuid))
12. 0.300 0.300 ↑ 1.0 1 100

Index Scan using persons_org_part_10_l2_t_org_seq_vrange_uidx on persons_org_part_10_l2_t p_3 (cost=0.41..1.38 rows=1 width=38) (actual time=0.003..0.003 rows=1 loops=100)

  • Index Cond: (person_uuid = e_2.submitter_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2020-01-14 18:07:44+00'::timestamp with time zone) AND (organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid))
13. 1.385 5,149.997 ↑ 57.8 173 1

Sort (cost=10,082,581.81..10,082,606.81 rows=10,000 width=689) (actual time=5,149.985..5,149.997 rows=173 loops=1)

  • Sort Key: e.event_id DESC, e.event_uuid, e.submitter_uuid, e.priority, e.incident_id, e.override_device_restrictions, e.escalation_override, e.bypass_phone_intro, e.require_phone_password, e.conference_properties, e.created, e.status, e.terminated, e.request_uuid, e.sequence, e.revision_uuid, e.event_type, e.system_event_type, e.form_uuid, e.plan_uuid, e.override_choices, e.valid_from, e.valid_to, (COALESCE(e.system_event_type, m.subject)), e.person_target_name, e.person_first_name, e.person_last_name, e.form_name, e.plan_name, e.integration_config_type
  • Sort Method: quicksort Memory: 151kB
14. 0.085 5,148.612 ↑ 57.8 173 1

Nested Loop Left Join (cost=357.83..10,081,917.43 rows=10,000 width=689) (actual time=4,509.848..5,148.612 rows=173 loops=1)

  • Join Filter: ((d.valid_range @> e.created) AND ((r.value ->> 'recipientType'::text) = 'DEVICE'::text))
15. 806.144 5,146.624 ↑ 57.8 173 1

Hash Right Join (cost=357.40..9,997,494.33 rows=10,000 width=690) (actual time=4,509.711..5,146.624 rows=173 loops=1)

  • Hash Cond: (p.person_uuid = ((r.value ->> 'id'::text))::uuid)
  • Join Filter: ((p.valid_range @> e.created) AND ((r.value ->> 'recipientType'::text) = 'PERSON'::text))
  • Rows Removed by Join Filter: 201125
16. 483.921 4,332.860 ↑ 1.0 6,781,521 1

Append (cost=0.00..637,043.72 rows=6,794,986 width=52) (actual time=0.062..4,332.860 rows=6,781,521 loops=1)

17. 3,799.851 3,799.851 ↑ 1.0 6,722,738 1

Seq Scan on persons_org_part_10_l2_f p (cost=0.00..593,945.00 rows=6,736,203 width=52) (actual time=0.062..3,799.851 rows=6,722,738 loops=1)

  • Filter: ((is_visible IS TRUE) AND (organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid))
  • Rows Removed by Filter: 3103050
18. 49.088 49.088 ↑ 1.0 58,783 1

Seq Scan on persons_org_part_10_l2_t p_1 (cost=0.00..9,123.79 rows=58,783 width=44) (actual time=0.011..49.088 rows=58,783 loops=1)

  • Filter: ((is_visible IS TRUE) AND (organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid))
19. 0.226 7.620 ↑ 57.8 173 1

Hash (cost=232.40..232.40 rows=10,000 width=676) (actual time=7.620..7.620 rows=173 loops=1)

  • Buckets: 16384 Batches: 1 Memory Usage: 200kB
20. 0.002 7.394 ↑ 57.8 173 1

Nested Loop Left Join (cost=30.01..232.40 rows=10,000 width=676) (actual time=6.932..7.394 rows=173 loops=1)

21. 0.074 6.992 ↑ 1.0 100 1

Hash Left Join (cost=30.01..32.39 rows=100 width=676) (actual time=6.925..6.992 rows=100 loops=1)

  • Hash Cond: (e.event_uuid = m.event_uuid)
22. 0.356 0.356 ↑ 1.0 100 1

CTE Scan on filtered_events e (cost=0.00..2.00 rows=100 width=620) (actual time=0.341..0.356 rows=100 loops=1)

23. 0.034 6.562 ↓ 100.0 100 1

Hash (cost=30.00..30.00 rows=1 width=72) (actual time=6.562..6.562 rows=100 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
24. 0.014 6.528 ↓ 100.0 100 1

Subquery Scan on m (cost=26.56..30.00 rows=1 width=72) (actual time=6.453..6.528 rows=100 loops=1)

  • Filter: (m.row_number = 1)
25. 0.080 6.514 ↓ 1.2 100 1

WindowAgg (cost=26.56..28.92 rows=86 width=103) (actual time=6.450..6.514 rows=100 loops=1)

26. 0.141 6.434 ↓ 1.2 100 1

Sort (cost=26.56..26.77 rows=86 width=79) (actual time=6.426..6.434 rows=100 loops=1)

  • Sort Key: msg.event_uuid, (CASE msg.language WHEN persons_org_part_10_l2_f.language THEN 1 WHEN 'en'::text THEN 2 ELSE 3 END), msg.language
  • Sort Method: quicksort Memory: 39kB
27. 0.094 6.293 ↓ 1.2 100 1

Hash Left Join (cost=7.34..23.79 rows=86 width=79) (actual time=0.160..6.293 rows=100 loops=1)

  • Hash Cond: ((msg.language)::text = (persons_org_part_10_l2_f.language)::text)
28. 0.056 6.110 ↓ 1.2 100 1

Nested Loop (cost=0.43..16.12 rows=86 width=75) (actual time=0.047..6.110 rows=100 loops=1)

29. 3.754 3.754 ↓ 100.0 100 1

CTE Scan on filtered_events e_1 (cost=0.00..2.00 rows=1 width=16) (actual time=0.002..3.754 rows=100 loops=1)

  • Filter: (system_event_type IS NULL)
30. 2.300 2.300 ↑ 1.0 1 100

Index Scan using messages_org_part_default_event_uuid_idx on messages_org_part_default msg (cost=0.43..14.11 rows=1 width=75) (actual time=0.023..0.023 rows=1 loops=100)

  • Index Cond: (event_uuid = e_1.event_uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2020-01-14 18:07:44+00'::timestamp with time zone) AND (organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid))
31. 0.005 0.089 ↑ 2.0 1 1

Hash (cost=6.89..6.89 rows=2 width=3) (actual time=0.089..0.089 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.000 0.084 ↑ 2.0 1 1

Append (cost=0.42..6.89 rows=2 width=3) (actual time=0.073..0.084 rows=1 loops=1)

33. 0.036 0.036 ↓ 0.0 0 1

Index Scan using persons_org_part_10_l2_f_valid_range_idx on persons_org_part_10_l2_f (cost=0.42..3.44 rows=1 width=3) (actual time=0.035..0.036 rows=0 loops=1)

  • Index Cond: (valid_range @> '2020-01-14 18:07:44+00'::timestamp with time zone)
  • Filter: ((is_visible IS TRUE) AND (organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid) AND (person_uuid = '1595bcae-87ad-45e5-a82f-29dff9c69296'::uuid))
34. 0.048 0.048 ↑ 1.0 1 1

Index Scan using persons_org_part_10_l2_t_org_seq_vrange_uidx on persons_org_part_10_l2_t (cost=0.41..3.44 rows=1 width=3) (actual time=0.037..0.048 rows=1 loops=1)

  • Index Cond: (person_uuid = '1595bcae-87ad-45e5-a82f-29dff9c69296'::uuid)
  • Filter: ((is_visible IS TRUE) AND (valid_range @> '2020-01-14 18:07:44+00'::timestamp with time zone) AND (organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid))
35. 0.400 0.400 ↑ 100.0 1 100

Function Scan on jsonb_array_elements r (cost=0.00..1.00 rows=100 width=32) (actual time=0.003..0.004 rows=1 loops=100)

36. 0.346 1.903 ↓ 0.0 0 173

Append (cost=0.43..8.39 rows=3 width=58) (actual time=0.011..0.011 rows=0 loops=173)

37. 0.865 0.865 ↓ 0.0 0 173

Index Scan using devices_org_part_141_l2_f_org_seq_vrange_uidx on devices_org_part_141_l2_f d (cost=0.43..4.94 rows=2 width=61) (actual time=0.005..0.005 rows=0 loops=173)

  • Index Cond: (device_uuid = ((r.value ->> 'id'::text))::uuid)
  • Filter: ((is_visible IS TRUE) AND (organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid))
38. 0.692 0.692 ↓ 0.0 0 173

Index Scan using devices_org_part_141_l2_t_org_seq_vrange_uidx on devices_org_part_141_l2_t d_1 (cost=0.43..3.44 rows=1 width=54) (actual time=0.004..0.004 rows=0 loops=173)

  • Index Cond: (device_uuid = ((r.value ->> 'id'::text))::uuid)
  • Filter: ((is_visible IS TRUE) AND (organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid))
Planning time : 12.770 ms
Execution time : 5,151.882 ms