explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U59i

Settings
# exclusive inclusive rows x rows loops node
1. 0.826 100,873.021 ↑ 1.0 1,000 1

Sort (cost=1,837,171.55..1,837,174.05 rows=1,000 width=652) (actual time=100,872.924..100,873.021 rows=1,000 loops=1)

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

CTE enriched_events

3. 3.154 10,797.020 ↑ 1.0 1,000 1

Unique (cost=63,376.19..63,453.69 rows=1,000 width=1,319) (actual time=10,792.900..10,797.020 rows=1,000 loops=1)

4. 2.757 10,793.866 ↑ 1.0 1,000 1

Sort (cost=63,376.19..63,378.69 rows=1,000 width=1,319) (actual time=10,792.897..10,793.866 rows=1,000 loops=1)

  • Sort Key: e_2.event_uuid, e_2.event_id, e_2.submitter_uuid, e_2.priority, e_2.incident_id, e_2.override_device_restrictions, e_2.escalation_override, e_2.bypass_phone_intro, e_2.require_phone_password, e_2.conference_properties, e_2.created, e_2.status, e_2.terminated, e_2.request_uuid, e_2.sequence, e_2.revision_uuid, e_2.event_type, e_2.system_event_type, e_2.form_uuid, e_2.plan_uuid, e_2.choices, (lower(e_2.valid_range)), (upper(e_2.valid_range)), e_2.recipients, pl.name, pl.integration_config_type, f.name, p_1.target_name, p_1.first_name, p_1.last_name
  • Sort Method: quicksort Memory: 750kB
5. 1.600 10,791.109 ↑ 1.0 1,000 1

Nested Loop Left Join (cost=60,297.44..63,326.36 rows=1,000 width=1,319) (actual time=6,786.740..10,791.109 rows=1,000 loops=1)

6. 0.166 6,686.509 ↑ 1.0 1,000 1

Limit (cost=60,297.02..60,415.36 rows=1,000 width=1,305) (actual time=6,652.268..6,686.509 rows=1,000 loops=1)

7. 136.516 6,686.343 ↑ 62.7 1,000 1

Gather Merge (cost=60,297.02..67,710.84 rows=62,652 width=1,305) (actual time=6,652.266..6,686.343 rows=1,000 loops=1)

  • Workers Planned: 3
  • Workers Launched: 3
8. 66.794 6,549.827 ↑ 62.9 332 4 / 4

Sort (cost=59,296.98..59,349.19 rows=20,884 width=1,305) (actual time=6,549.689..6,549.827 rows=332 loops=4)

  • Sort Key: e_2.created DESC, e_2.event_id DESC
  • Sort Method: top-N heapsort Memory: 1162kB
  • Worker 0: Sort Method: top-N heapsort Memory: 1314kB
  • Worker 1: Sort Method: top-N heapsort Memory: 1137kB
  • Worker 2: Sort Method: top-N heapsort Memory: 1098kB
9. 50.337 6,483.033 ↓ 1.2 24,580 4 / 4

Parallel Hash Left Join (cost=54,027.98..58,151.94 rows=20,884 width=1,305) (actual time=3,645.154..6,483.033 rows=24,580 loops=4)

  • Hash Cond: (e_2.submitter_uuid = p_1.person_uuid)
10. 2,166.043 6,105.750 ↓ 1.2 24,580 4 / 4

Merge Left Join (cost=53,695.46..57,514.52 rows=20,884 width=1,235) (actual time=3,317.570..6,105.750 rows=24,580 loops=4)

  • Merge Cond: (e_2.plan_uuid = pl.plan_uuid)
  • Join Filter: (pl.valid_range @> e_2.created)
  • Rows Removed by Join Filter: 2791848
11. 225.296 1,357.410 ↓ 1.2 24,580 4 / 4

Sort (cost=30,740.20..30,792.41 rows=20,884 width=1,209) (actual time=1,330.025..1,357.410 rows=24,580 loops=4)

  • Sort Key: e_2.plan_uuid
  • Sort Method: external merge Disk: 10512kB
  • Worker 0: Sort Method: external merge Disk: 10048kB
  • Worker 1: Sort Method: external merge Disk: 11904kB
  • Worker 2: Sort Method: external merge Disk: 11472kB
12. 1,132.114 1,132.114 ↓ 1.2 24,580 4 / 4

Parallel Seq Scan on events_org_part_215 e_2 (cost=0.00..22,127.26 rows=20,884 width=1,209) (actual time=125.932..1,132.114 rows=24,580 loops=4)

  • Filter: ((is_visible IS TRUE) AND (upper_inf(valid_range) IS TRUE) AND (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) AND (organization_uuid = '92759640-5f84-4945-add0-87c8f4b65e87'::uuid))
  • Rows Removed by Filter: 26792
13. 631.779 2,582.297 ↓ 124.0 2,835,088 4 / 4

Sort (cost=22,955.26..23,012.43 rows=22,869 width=63) (actual time=1,987.190..2,582.297 rows=2,835,088 loops=4)

  • Sort Key: pl.plan_uuid
  • Sort Method: quicksort Memory: 4166kB
  • Worker 0: Sort Method: quicksort Memory: 4166kB
  • Worker 1: Sort Method: quicksort Memory: 4166kB
  • Worker 2: Sort Method: quicksort Memory: 4166kB
14. 160.752 1,950.518 ↓ 1.2 27,283 4 / 4

Bitmap Heap Scan on plans pl (cost=510.80..21,299.42 rows=22,869 width=63) (actual time=1,811.512..1,950.518 rows=27,283 loops=4)

  • Recheck Cond: (organization_uuid = '92759640-5f84-4945-add0-87c8f4b65e87'::uuid)
  • Filter: (is_visible IS TRUE)
  • Rows Removed by Filter: 354
  • Heap Blocks: exact=2248
15. 1,789.766 1,789.766 ↓ 1.3 30,620 4 / 4

Bitmap Index Scan on plans_organization_uuid_idx (cost=0.00..505.08 rows=23,687 width=0) (actual time=1,789.766..1,789.766 rows=30,620 loops=4)

  • Index Cond: (organization_uuid = '92759640-5f84-4945-add0-87c8f4b65e87'::uuid)
16. 20.559 326.946 ↑ 2.4 48 4 / 4

Parallel Hash (cost=331.05..331.05 rows=117 width=87) (actual time=326.946..326.946 rows=48 loops=4)

  • Buckets: 1024 Batches: 1 Memory Usage: 136kB
17. 292.731 306.387 ↑ 2.4 48 4 / 4

Parallel Bitmap Heap Scan on persons_org_part_default p_1 (cost=6.59..331.05 rows=117 width=87) (actual time=98.033..306.387 rows=48 loops=4)

  • Recheck Cond: ((organization_uuid = '92759640-5f84-4945-add0-87c8f4b65e87'::uuid) AND (upper_inf(valid_range) IS TRUE))
  • Filter: (is_visible IS TRUE)
  • Heap Blocks: exact=12
18. 13.656 13.656 ↓ 1.0 221 1 / 4

Bitmap Index Scan on persons_org_part_default_organization_uuid_person_uuid_seq_idx1 (cost=0.00..6.54 rows=216 width=0) (actual time=54.626..54.626 rows=221 loops=1)

  • Index Cond: (organization_uuid = '92759640-5f84-4945-add0-87c8f4b65e87'::uuid)
19. 4,103.000 4,103.000 ↑ 1.0 1 1,000

Index Scan using forms_org_seq_vrange_uidx on forms f (cost=0.42..2.89 rows=1 width=50) (actual time=3.958..4.103 rows=1 loops=1,000)

  • Index Cond: ((organization_uuid = '92759640-5f84-4945-add0-87c8f4b65e87'::uuid) AND (form_uuid = e_2.form_uuid))
  • Filter: ((is_visible IS TRUE) AND (valid_range @> e_2.created))
  • Rows Removed by Filter: 11
20.          

CTE final_events

21. 0.991 18,434.508 ↑ 1.0 1,000 1

Sort (cost=248.71..251.21 rows=1,000 width=652) (actual time=18,434.357..18,434.508 rows=1,000 loops=1)

  • Sort Key: e_3.created DESC, e_3.event_id DESC
  • Sort Method: quicksort Memory: 797kB
22. 0.551 18,433.517 ↑ 1.0 1,000 1

Hash Left Join (cost=174.83..198.88 rows=1,000 width=652) (actual time=18,432.776..18,433.517 rows=1,000 loops=1)

  • Hash Cond: (e_3.event_uuid = m.event_uuid)
23. 10,793.133 10,793.133 ↑ 1.0 1,000 1

CTE Scan on enriched_events e_3 (cost=0.00..20.00 rows=1,000 width=620) (actual time=10,792.903..10,793.133 rows=1,000 loops=1)

24. 0.131 7,639.833 ↓ 105.0 630 1

Hash (cost=174.76..174.76 rows=6 width=59) (actual time=7,639.833..7,639.833 rows=630 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 66kB
25. 0.123 7,639.702 ↓ 105.0 630 1

Subquery Scan on m (cost=124.08..174.76 rows=6 width=59) (actual time=7,638.900..7,639.702 rows=630 loops=1)

  • Filter: (m.row_number = 1)
  • Rows Removed by Filter: 336
26. 0.741 7,639.579 ↑ 1.3 966 1

WindowAgg (cost=124.08..158.92 rows=1,267 width=90) (actual time=7,638.893..7,639.579 rows=966 loops=1)

27. 2.826 7,638.838 ↑ 1.3 966 1

Sort (cost=124.08..127.24 rows=1,267 width=66) (actual time=7,638.777..7,638.838 rows=966 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: 152kB
28. 3.827 7,636.012 ↑ 1.3 966 1

Hash Left Join (cost=3.74..58.78 rows=1,267 width=66) (actual time=180.061..7,636.012 rows=966 loops=1)

  • Hash Cond: ((msg.language)::text = (p_2.language)::text)
29. 2.946 7,632.149 ↑ 1.3 966 1

Nested Loop (cost=0.28..44.17 rows=1,267 width=62) (actual time=179.987..7,632.149 rows=966 loops=1)

30. 6.035 6.035 ↓ 134.4 672 1

CTE Scan on enriched_events e_4 (cost=0.00..20.00 rows=5 width=16) (actual time=0.005..6.035 rows=672 loops=1)

  • Filter: (system_event_type IS NULL)
  • Rows Removed by Filter: 328
31. 7,623.168 7,623.168 ↑ 1.0 1 672

Index Scan using messages_org_part_41_event_uuid_valid_range_idx on messages_org_part_41 msg (cost=0.28..4.83 rows=1 width=62) (actual time=10.800..11.344 rows=1 loops=672)

  • Index Cond: (event_uuid = e_4.event_uuid)
  • Filter: ((is_visible IS TRUE) AND (upper_inf(valid_range) IS TRUE) AND (organization_uuid = '92759640-5f84-4945-add0-87c8f4b65e87'::uuid))
  • Rows Removed by Filter: 0
32. 0.005 0.036 ↑ 1.0 1 1

Hash (cost=3.45..3.45 rows=1 width=3) (actual time=0.036..0.036 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
33. 0.031 0.031 ↑ 1.0 1 1

Index Scan using persons_org_part_default_organization_uuid_person_uuid_seq_idx1 on persons_org_part_default p_2 (cost=0.42..3.45 rows=1 width=3) (actual time=0.030..0.031 rows=1 loops=1)

  • Index Cond: ((organization_uuid = '92759640-5f84-4945-add0-87c8f4b65e87'::uuid) AND (person_uuid = 'c88b142a-64b1-4abe-8871-321c0064ebbf'::uuid))
  • Filter: (is_visible IS TRUE)
34. 0.728 100,872.195 ↑ 1.0 1,000 1

Hash Left Join (cost=1,773,394.15..1,773,416.83 rows=1,000 width=652) (actual time=100,871.214..100,872.195 rows=1,000 loops=1)

  • Hash Cond: (e.event_uuid = tr.event_uuid)
35. 18,434.643 18,434.643 ↑ 1.0 1,000 1

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

36. 0.352 82,436.824 ↓ 5.0 1,000 1

Hash (cost=1,773,391.65..1,773,391.65 rows=200 width=48) (actual time=82,436.824..82,436.824 rows=1,000 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 194kB
37. 0.133 82,436.472 ↓ 5.0 1,000 1

Subquery Scan on tr (cost=1,771,137.15..1,773,391.65 rows=200 width=48) (actual time=82,427.541..82,436.472 rows=1,000 loops=1)

38. 8.663 82,436.339 ↓ 5.0 1,000 1

GroupAggregate (cost=1,771,137.15..1,773,389.65 rows=200 width=48) (actual time=82,427.538..82,436.339 rows=1,000 loops=1)

  • Group Key: e_1.event_uuid
39. 0.972 82,427.676 ↑ 92.3 1,084 1

Sort (cost=1,771,137.15..1,771,387.15 rows=100,000 width=103) (actual time=82,427.448..82,427.676 rows=1,084 loops=1)

  • Sort Key: e_1.event_uuid
  • Sort Method: quicksort Memory: 306kB
40. 1.650 82,426.704 ↑ 92.3 1,084 1

Hash Left Join (cost=4,483.69..1,759,315.58 rows=100,000 width=103) (actual time=82,391.261..82,426.704 rows=1,084 loops=1)

  • Hash Cond: (((r.value ->> 'id'::text))::uuid = p.person_uuid)
  • Join Filter: ((r.value ->> 'recipientType'::text) = 'PERSON'::text)
41. 34.495 82,424.614 ↑ 92.3 1,084 1

Hash Right Join (cost=4,149.00..1,756,730.90 rows=100,000 width=65) (actual time=82,390.789..82,424.614 rows=1,084 loops=1)

  • Hash Cond: (d.device_uuid = ((r.value ->> 'id'::text))::uuid)
  • Join Filter: ((r.value ->> 'recipientType'::text) = 'DEVICE'::text)
42. 82,386.857 82,386.857 ↑ 5.3 288 1

Seq Scan on devices_org_part_default d (cost=0.00..1,732,460.55 rows=1,537 width=33) (actual time=19.866..82,386.857 rows=288 loops=1)

  • Filter: ((is_visible IS TRUE) AND (is_visible IS TRUE) AND (upper_inf(valid_range) IS TRUE) AND (organization_uuid = '92759640-5f84-4945-add0-87c8f4b65e87'::uuid))
  • Rows Removed by Filter: 1460970
43. 0.986 3.262 ↑ 92.3 1,084 1

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

  • Buckets: 131072 Batches: 2 Memory Usage: 1131kB
44. 0.759 2.276 ↑ 92.3 1,084 1

Nested Loop Left Join (cost=0.00..2,020.00 rows=100,000 width=48) (actual time=0.011..2.276 rows=1,084 loops=1)

45. 0.517 0.517 ↑ 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.001..0.517 rows=1,000 loops=1)

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

47. 0.044 0.440 ↓ 1.1 194 1

Hash (cost=332.38..332.38 rows=184 width=54) (actual time=0.440..0.440 rows=194 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 20kB
48. 0.347 0.396 ↓ 1.1 194 1

Bitmap Heap Scan on persons_org_part_default p (cost=6.58..332.38 rows=184 width=54) (actual time=0.068..0.396 rows=194 loops=1)

  • Recheck Cond: ((organization_uuid = '92759640-5f84-4945-add0-87c8f4b65e87'::uuid) AND (upper_inf(valid_range) IS TRUE))
  • Filter: ((is_visible IS TRUE) AND (is_visible IS TRUE))
  • Heap Blocks: exact=49
49. 0.049 0.049 ↓ 1.0 221 1

Bitmap Index Scan on persons_org_part_default_organization_uuid_person_uuid_seq_idx1 (cost=0.00..6.54 rows=216 width=0) (actual time=0.048..0.049 rows=221 loops=1)

  • Index Cond: (organization_uuid = '92759640-5f84-4945-add0-87c8f4b65e87'::uuid)
Planning time : 107.686 ms
Execution time : 100,879.099 ms