explain.depesz.com

PostgreSQL's explain analyze made readable

Result: k4ST

Settings
# exclusive inclusive rows x rows loops node
1. 1.093 13,142.601 ↑ 16.0 100 1

GroupAggregate (cost=10,082,592.13..10,083,562.13 rows=1,600 width=652) (actual time=13,141.527..13,142.601 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.063 637.851 ↑ 1.0 100 1

Limit (cost=1.80..10.31 rows=100 width=721) (actual time=262.146..637.851 rows=100 loops=1)

4. 0.276 637.788 ↑ 786.0 100 1

Nested Loop Left Join (cost=1.80..6,689.60 rows=78,603 width=721) (actual time=262.143..637.788 rows=100 loops=1)

5. 0.250 512.612 ↑ 2.7 100 1

Nested Loop Left Join (cost=1.39..4,497.43 rows=267 width=702) (actual time=155.488..512.612 rows=100 loops=1)

6. 0.401 68.662 ↑ 2.7 100 1

Nested Loop Left Join (cost=0.97..3,593.09 rows=267 width=681) (actual time=2.365..68.662 rows=100 loops=1)

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

Index Scan using events_org_part_252_event_id_created_idx on events_org_part_252 e_2 (cost=0.42..2,611.72 rows=275 width=654) (actual time=0.080..2.777 rows=102 loops=1)

  • Index Cond: ((created <= now()) AND (created >= '2020-01-07 18:07:12+00'::timestamp with time zone) AND (created <= '2020-01-14 18:07:12+00'::timestamp with time zone))
  • 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 (organization_uuid = '8d3a618b-96c0-4af3-a2fb-d10df4e12b6e'::uuid))
  • Rows Removed by Filter: 189
8. 65.484 65.484 ↑ 1.0 1 102

Index Scan using plans_org_seq_vrange_uidx on plans pl (cost=0.55..3.56 rows=1 width=69) (actual time=0.034..0.642 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. 443.700 443.700 ↑ 1.0 1 100

Index Scan using forms_org_seq_vrange_uidx on forms f (cost=0.42..3.38 rows=1 width=56) (actual time=3.272..4.437 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.300 124.900 ↑ 2.0 1 100

Append (cost=0.42..6.72 rows=2 width=38) (actual time=1.247..1.249 rows=1 loops=100)

11. 87.600 87.600 ↓ 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.876..0.876 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. 37.000 37.000 ↑ 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..3.27 rows=1 width=38) (actual time=0.368..0.370 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. 2.287 13,141.508 ↑ 57.8 173 1

Sort (cost=10,082,581.81..10,082,606.81 rows=10,000 width=689) (actual time=13,141.490..13,141.508 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.630 13,139.221 ↑ 57.8 173 1

Nested Loop Left Join (cost=357.83..10,081,917.43 rows=10,000 width=689) (actual time=9,274.980..13,139.221 rows=173 loops=1)

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

Hash Right Join (cost=357.40..9,997,494.33 rows=10,000 width=690) (actual time=9,090.037..10,712.612 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. 564.878 8,735.063 ↑ 1.0 6,781,521 1

Append (cost=0.00..637,043.72 rows=6,794,986 width=52) (actual time=9.217..8,735.063 rows=6,781,521 loops=1)

17. 7,960.943 7,960.943 ↑ 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=9.214..7,960.943 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. 209.242 209.242 ↑ 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=1.168..209.242 rows=58,783 loops=1)

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

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

  • Buckets: 16384 Batches: 1 Memory Usage: 200kB
20. 0.101 1,055.571 ↑ 57.8 173 1

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

21. 0.168 984.270 ↑ 1.0 100 1

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

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

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

23. 0.047 721.919 ↓ 100.0 100 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
24. 0.017 721.872 ↓ 100.0 100 1

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

  • Filter: (m.row_number = 1)
25. 0.097 721.855 ↓ 1.2 100 1

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

26. 0.513 721.758 ↓ 1.2 100 1

Sort (cost=26.56..26.77 rows=86 width=79) (actual time=721.748..721.758 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.592 721.245 ↓ 1.2 100 1

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

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

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

29. 376.401 376.401 ↓ 100.0 100 1

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

  • Filter: (system_event_type IS NULL)
30. 333.800 333.800 ↑ 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=3.336..3.338 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.019 10.180 ↑ 2.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
32. 0.005 10.161 ↑ 2.0 1 1

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

33. 0.130 0.130 ↓ 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.130..0.130 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. 10.026 10.026 ↑ 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=9.064..10.026 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. 71.200 71.200 ↑ 100.0 1 100

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

36. 1.730 2,425.979 ↓ 0.0 0 173

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

37. 1,117.234 1,117.234 ↓ 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=6.458..6.458 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. 1,307.015 1,307.015 ↓ 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=7.555..7.555 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 : 14.243 ms
Execution time : 13,144.079 ms