explain.depesz.com

PostgreSQL's explain analyze made readable

Result: rNQI

Settings
# exclusive inclusive rows x rows loops node
1. 0.882 178.659 ↑ 1.0 1,000 1

Sort (cost=1,863,894.40..1,863,896.90 rows=1,000 width=652) (actual time=178.593..178.659 rows=1,000 loops=1)

  • Sort Key: e.created DESC, e.event_id DESC
  • Sort Method: quicksort Memory: 552kB
2.          

CTE enriched_events

3. 2.240 43.493 ↑ 1.0 1,000 1

HashAggregate (cost=245.62..255.62 rows=1,000 width=592) (actual time=42.882..43.493 rows=1,000 loops=1)

  • Group Key: e_5.event_uuid, e_5.event_id, e_5.submitter_uuid, e_5.priority, e_5.incident_id, e_5.override_device_restrictions, e_5.escalation_override, e_5.bypass_phone_intro, e_5.require_phone_password, e_5.conference_properties, e_5.created, e_5.status, e_5.terminated, e_5.request_uuid, e_5.sequence, e_5.revision_uuid, e_5.event_type, e_5.system_event_type, e_5.form_uuid, e_5.plan_uuid, e_5.choices, (lower(e_5.valid_range)), (upper(e_5.valid_range)), e_5.recipients, pl.name, pl.integration_config_type, f.name, p_1.target_name, p_1.first_name, p_1.last_name
4. 1.238 41.253 ↑ 1.0 1,000 1

Hash Right Join (cost=46.64..170.62 rows=1,000 width=592) (actual time=39.162..41.253 rows=1,000 loops=1)

  • Hash Cond: (f.form_uuid = e_5.form_uuid)
  • Join Filter: (f.valid_range @> e_5.created)
  • Rows Removed by Join Filter: 2942
5. 1.456 1.571 ↓ 5.4 403 1

Bitmap Heap Scan on forms f (cost=2.52..116.92 rows=75 width=56) (actual time=0.149..1.571 rows=403 loops=1)

  • Recheck Cond: (organization_uuid = 'db95abb9-1b04-494f-81c2-be2ac21236bd'::uuid)
  • Filter: (is_visible IS TRUE)
  • Rows Removed by Filter: 1
  • Heap Blocks: exact=210
6. 0.115 0.115 ↓ 5.3 405 1

Bitmap Index Scan on forms_org_seq_vrange_uidx (cost=0.00..2.50 rows=77 width=0) (actual time=0.115..0.115 rows=405 loops=1)

  • Index Cond: (organization_uuid = 'db95abb9-1b04-494f-81c2-be2ac21236bd'::uuid)
7. 0.982 38.444 ↑ 1.0 1,000 1

Hash (cost=31.62..31.62 rows=1,000 width=571) (actual time=38.443..38.444 rows=1,000 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 412kB
8. 0.110 37.462 ↑ 1.0 1,000 1

Limit (cost=2.81..21.62 rows=1,000 width=571) (actual time=0.696..37.462 rows=1,000 loops=1)

9. 0.278 37.352 ↑ 29,813.9 1,000 1

Nested Loop Left Join (cost=2.81..560,737.97 rows=29,813,939 width=571) (actual time=0.695..37.352 rows=1,000 loops=1)

10. 0.785 34.074 ↑ 75.4 1,000 1

Nested Loop Left Join (cost=2.26..221,265.84 rows=75,381 width=543) (actual time=0.641..34.074 rows=1,000 loops=1)

11. 0.174 6.289 ↑ 75.4 1,000 1

Merge Append (cost=1.71..108,238.46 rows=75,381 width=515) (actual time=0.105..6.289 rows=1,000 loops=1)

  • Sort Key: e_5.created DESC, e_5.event_id DESC
12. 6.041 6.041 ↑ 12.0 1,000 1

Index Scan using events_org_part_12_l3_t_202002_created_event_id_idx on events_org_part_12_l3_t_202002 e_5 (cost=0.41..14,316.08 rows=11,953 width=516) (actual time=0.028..6.041 rows=1,000 loops=1)

  • Index Cond: ((created >= '2019-11-15 19:28:43+00'::timestamp with time zone) AND (created <= '2020-02-14 19:28:43+00'::timestamp with time zone))
  • Filter: ((is_visible IS TRUE) AND (upper_inf(valid_range) IS TRUE) AND (organization_uuid = 'db95abb9-1b04-494f-81c2-be2ac21236bd'::uuid))
13. 0.027 0.027 ↑ 27,073.0 1 1

Index Scan using events_org_part_12_l3_t_202001_created_event_id_idx on events_org_part_12_l3_t_202001 e_4 (cost=0.42..31,679.78 rows=27,073 width=516) (actual time=0.027..0.027 rows=1 loops=1)

  • Index Cond: ((created >= '2019-11-15 19:28:43+00'::timestamp with time zone) AND (created <= '2020-02-14 19:28:43+00'::timestamp with time zone))
  • Filter: ((is_visible IS TRUE) AND (upper_inf(valid_range) IS TRUE) AND (organization_uuid = 'db95abb9-1b04-494f-81c2-be2ac21236bd'::uuid))
14. 0.025 0.025 ↑ 23,938.0 1 1

Index Scan using events_org_part_12_l3_t_201912_created_event_id_idx on events_org_part_12_l3_t_201912 e_3 (cost=0.42..28,186.00 rows=23,938 width=517) (actual time=0.025..0.025 rows=1 loops=1)

  • Index Cond: ((created >= '2019-11-15 19:28:43+00'::timestamp with time zone) AND (created <= '2020-02-14 19:28:43+00'::timestamp with time zone))
  • Filter: ((is_visible IS TRUE) AND (upper_inf(valid_range) IS TRUE) AND (organization_uuid = 'db95abb9-1b04-494f-81c2-be2ac21236bd'::uuid))
15. 0.022 0.022 ↑ 12,417.0 1 1

Index Scan using events_org_part_12_l3_t_201911_created_event_id_idx on events_org_part_12_l3_t_201911 e_2 (cost=0.42..32,925.83 rows=12,417 width=511) (actual time=0.022..0.022 rows=1 loops=1)

  • Index Cond: ((created >= '2019-11-15 19:28:43+00'::timestamp with time zone) AND (created <= '2020-02-14 19:28:43+00'::timestamp with time zone))
  • Filter: ((is_visible IS TRUE) AND (upper_inf(valid_range) IS TRUE) AND (organization_uuid = 'db95abb9-1b04-494f-81c2-be2ac21236bd'::uuid))
16. 27.000 27.000 ↑ 1.0 1 1,000

Index Scan using plans_org_seq_vrange_uidx on plans pl (cost=0.55..1.49 rows=1 width=64) (actual time=0.027..0.027 rows=1 loops=1,000)

  • Index Cond: ((organization_uuid = 'db95abb9-1b04-494f-81c2-be2ac21236bd'::uuid) AND (plan_uuid = e_5.plan_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> e_5.created))
  • Rows Removed by Filter: 59
17. 3.000 3.000 ↑ 1.0 1 1,000

Index Scan using persons_org_part_default_organization_uuid_person_uuid_sequ_idx on persons_org_part_default p_1 (cost=0.55..2.52 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=1,000)

  • Index Cond: ((organization_uuid = 'db95abb9-1b04-494f-81c2-be2ac21236bd'::uuid) AND (person_uuid = e_5.submitter_uuid))
  • Filter: (is_visible IS TRUE)
18.          

CTE final_events

19. 1.228 63.665 ↑ 1.0 1,000 1

Sort (cost=1,237.93..1,240.43 rows=1,000 width=652) (actual time=63.561..63.665 rows=1,000 loops=1)

  • Sort Key: e_6.created DESC, e_6.event_id DESC
  • Sort Method: quicksort Memory: 662kB
20. 0.746 62.437 ↑ 1.0 1,000 1

Hash Right Join (cost=797.18..1,188.10 rows=1,000 width=652) (actual time=60.922..62.437 rows=1,000 loops=1)

  • Hash Cond: (m.event_uuid = e_6.event_uuid)
21. 0.101 16.675 ↓ 20.6 989 1

Subquery Scan on m (cost=764.68..1,147.08 rows=48 width=71) (actual time=15.871..16.675 rows=989 loops=1)

  • Filter: (m.row_number = 1)
22. 0.647 16.574 ↑ 9.7 989 1

WindowAgg (cost=764.68..1,027.58 rows=9,560 width=102) (actual time=15.868..16.574 rows=989 loops=1)

23. 0.654 15.927 ↑ 9.7 989 1

Sort (cost=764.68..788.58 rows=9,560 width=78) (actual time=15.843..15.927 rows=989 loops=1)

  • Sort Key: msg.event_uuid, (CASE msg.language WHEN p_2.language THEN 1 WHEN 'en'::text THEN 2 ELSE 3 END), msg.language
  • Sort Method: quicksort Memory: 167kB
24. 0.371 15.273 ↑ 9.7 989 1

Hash Left Join (cost=4.01..132.63 rows=9,560 width=78) (actual time=0.112..15.273 rows=989 loops=1)

  • Hash Cond: ((msg.language)::text = (p_2.language)::text)
25. 0.593 14.878 ↑ 9.7 989 1

Nested Loop (cost=0.43..44.91 rows=9,560 width=74) (actual time=0.064..14.878 rows=989 loops=1)

26. 0.439 0.439 ↓ 197.8 989 1

CTE Scan on enriched_events e_7 (cost=0.00..20.00 rows=5 width=16) (actual time=0.002..0.439 rows=989 loops=1)

  • Filter: (system_event_type IS NULL)
  • Rows Removed by Filter: 11
27. 13.846 13.846 ↑ 1.0 1 989

Index Scan using messages_org_part_12_event_uuid_idx on messages_org_part_12 msg (cost=0.43..4.97 rows=1 width=74) (actual time=0.014..0.014 rows=1 loops=989)

  • Index Cond: (event_uuid = e_7.event_uuid)
  • Filter: ((is_visible IS TRUE) AND (upper_inf(valid_range) IS TRUE) AND (organization_uuid = 'db95abb9-1b04-494f-81c2-be2ac21236bd'::uuid))
28. 0.004 0.024 ↑ 1.0 1 1

Hash (cost=3.57..3.57 rows=1 width=3) (actual time=0.024..0.024 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
29. 0.020 0.020 ↑ 1.0 1 1

Index Scan using persons_org_part_default_organization_uuid_person_uuid_sequ_idx on persons_org_part_default p_2 (cost=0.55..3.57 rows=1 width=3) (actual time=0.019..0.020 rows=1 loops=1)

  • Index Cond: ((organization_uuid = 'db95abb9-1b04-494f-81c2-be2ac21236bd'::uuid) AND (person_uuid = '5262d57c-4cfa-4a82-a9d0-74ed1a5eaedd'::uuid))
  • Filter: (is_visible IS TRUE)
30. 0.412 45.016 ↑ 1.0 1,000 1

Hash (cost=20.00..20.00 rows=1,000 width=620) (actual time=45.016..45.016 rows=1,000 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 427kB
31. 44.604 44.604 ↑ 1.0 1,000 1

CTE Scan on enriched_events e_6 (cost=0.00..20.00 rows=1,000 width=620) (actual time=42.885..44.604 rows=1,000 loops=1)

32. 0.549 177.777 ↑ 1.0 1,000 1

Hash Left Join (cost=1,862,325.85..1,862,348.53 rows=1,000 width=652) (actual time=177.028..177.777 rows=1,000 loops=1)

  • Hash Cond: (e.event_uuid = tr.event_uuid)
33. 63.805 63.805 ↑ 1.0 1,000 1

CTE Scan on final_events e (cost=0.00..20.00 rows=1,000 width=620) (actual time=63.564..63.805 rows=1,000 loops=1)

34. 0.265 113.423 ↓ 5.0 1,000 1

Hash (cost=1,862,323.35..1,862,323.35 rows=200 width=48) (actual time=113.423..113.423 rows=1,000 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 149kB
35. 0.093 113.158 ↓ 5.0 1,000 1

Subquery Scan on tr (cost=1,862,318.85..1,862,323.35 rows=200 width=48) (actual time=112.539..113.158 rows=1,000 loops=1)

36. 5.659 113.065 ↓ 5.0 1,000 1

HashAggregate (cost=1,862,318.85..1,862,321.35 rows=200 width=48) (actual time=112.538..113.065 rows=1,000 loops=1)

  • Group Key: e_1.event_uuid
37. 1.009 107.406 ↑ 1,083.0 1,002 1

Nested Loop Left Join (cost=4,149.56..1,840,614.63 rows=1,085,211 width=82) (actual time=53.683..107.406 rows=1,002 loops=1)

  • Join Filter: ((r.value ->> 'recipientType'::text) = 'PERSON'::text)
38. 16.425 104.393 ↑ 298.9 1,002 1

Hash Right Join (cost=4,149.00..763,984.03 rows=299,480 width=67) (actual time=53.650..104.393 rows=1,002 loops=1)

  • Hash Cond: (d.device_uuid = ((r.value ->> 'id'::text))::uuid)
  • Join Filter: ((r.value ->> 'recipientType'::text) = 'DEVICE'::text)
39. 85.191 85.191 ↑ 1.4 43,698 1

Seq Scan on devices_org_part_52 d (cost=0.00..9,170.28 rows=59,896 width=35) (actual time=0.059..85.191 rows=43,698 loops=1)

  • Filter: ((is_visible IS TRUE) AND (is_visible IS TRUE) AND (upper_inf(valid_range) IS TRUE) AND (organization_uuid = 'db95abb9-1b04-494f-81c2-be2ac21236bd'::uuid))
  • Rows Removed by Filter: 140260
40. 0.889 2.777 ↑ 99.8 1,002 1

Hash (cost=2,020.00..2,020.00 rows=100,000 width=48) (actual time=2.777..2.777 rows=1,002 loops=1)

  • Buckets: 131072 Batches: 2 Memory Usage: 1110kB
41. 0.234 1.888 ↑ 99.8 1,002 1

Nested Loop Left Join (cost=0.00..2,020.00 rows=100,000 width=48) (actual time=0.040..1.888 rows=1,002 loops=1)

42. 0.654 0.654 ↑ 1.0 1,000 1

CTE Scan on final_events e_1 (cost=0.00..20.00 rows=1,000 width=48) (actual time=0.004..0.654 rows=1,000 loops=1)

43. 1.000 1.000 ↑ 100.0 1 1,000

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

44. 2.004 2.004 ↓ 0.0 0 1,002

Index Scan using persons_org_part_default_organization_uuid_person_uuid_sequ_idx on persons_org_part_default p (cost=0.56..3.58 rows=1 width=31) (actual time=0.002..0.002 rows=0 loops=1,002)

  • Index Cond: ((organization_uuid = 'db95abb9-1b04-494f-81c2-be2ac21236bd'::uuid) AND (person_uuid = ((r.value ->> 'id'::text))::uuid))
  • Filter: ((is_visible IS TRUE) AND (is_visible IS TRUE))
Planning time : 13.102 ms
Execution time : 180.098 ms