explain.depesz.com

PostgreSQL's explain analyze made readable

Result: yCNR

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 0.000 ↓ 0.0

Limit (cost=809.54..902.68 rows=11 width=2,870) (actual rows= loops=)

2.          

CTE filtered_tasks

3. 0.000 0.000 ↓ 0.0

Nested Loop Anti Join (cost=1.14..336.31 rows=11 width=679) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Index Scan using capsule_os_template_task_count on capsule_os_task (cost=0.29..221.42 rows=11 width=675) (actual rows= loops=)

  • Index Cond: ((template_id = ANY ('{5061,5059,5086}'::integer[])) AND (starts_at <= now()))
  • Filter: (assigned_to_id IS NULL)
5. 0.000 0.000 ↓ 0.0

Nested Loop (cost=0.85..9.64 rows=1 width=4) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Index Scan using capsule_os_taskdependency_to_task_id_8226e3ce on capsule_os_taskdependency dependency (cost=0.42..8.44 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (to_task_id = capsule_os_task.id)
7. 0.000 0.000 ↓ 0.0

Index Scan using capsule_os_task_pkey on capsule_os_task dependent_task (cost=0.43..1.19 rows=1 width=4) (actual rows= loops=)

  • Index Cond: (id = dependency.from_task_id)
  • Filter: ((status)::text = 'incomplete'::text)
8. 0.000 0.000 ↓ 0.0

Result (cost=473.23..566.37 rows=11 width=2,870) (actual rows= loops=)

9. 0.000 0.000 ↓ 0.0

Sort (cost=473.23..473.26 rows=11 width=2,838) (actual rows= loops=)

  • Sort Key: (COALESCE(group_sort.is_urgent_ordinal, task.is_urgent_ordinal)), (COALESCE(group_sort.due_at, task.due_at)), (COALESCE(patient.last_name, identity.last_name)), (COALESCE(patient.first_name, identity.first_name)), group_sort.patient_id, (CASE WHEN (group_sort.patient_id IS NOT NULL) THEN NULL::integer ELSE group_sort.identity_id END), task.is_urgent_ordinal, task.due_at
10. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.82..473.04 rows=11 width=2,838) (actual rows= loops=)

  • Join Filter: (task.facility_id = facility.id)
11. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.82..471.69 rows=11 width=1,678) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.53..380.22 rows=11 width=1,664) (actual rows= loops=)

  • Join Filter: ((dt.id = new_delivery_window.tier_id) AND (dt.facility_id = task.facility_id))
13. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.53..378.40 rows=11 width=1,394) (actual rows= loops=)

14. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=2.25..375.01 rows=11 width=1,378) (actual rows= loops=)

15. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.97..371.55 rows=11 width=1,366) (actual rows= loops=)

16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.54..278.60 rows=11 width=1,357) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=1.25..187.08 rows=11 width=1,327) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.83..94.16 rows=11 width=1,313) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.41..1.24 rows=11 width=1,299) (actual rows= loops=)

  • Join Filter: (((task.patient_id IS NOT NULL) AND (group_sort.patient_id = task.patient_id)) OR ((task.identity_id IS NOT NULL) AND (group_sort.identity_id = task.identity_id)))
20. 0.000 0.000 ↓ 0.0

CTE Scan on filtered_tasks task (cost=0.00..0.22 rows=11 width=1,279) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Materialize (cost=0.41..0.83 rows=1 width=20) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Subquery Scan on group_sort (cost=0.41..0.82 rows=1 width=20) (actual rows= loops=)

  • Filter: (group_sort.rank = 1)
23. 0.000 0.000 ↓ 0.0

WindowAgg (cost=0.41..0.69 rows=11 width=64) (actual rows= loops=)

24. 0.000 0.000 ↓ 0.0

Sort (cost=0.41..0.44 rows=11 width=56) (actual rows= loops=)

  • Sort Key: (CASE WHEN (filtered_tasks.patient_id IS NOT NULL) THEN ROW(1, filtered_tasks.patient_id) WHEN (filtered_tasks.identity_id IS NOT NULL) THEN ROW(2, filtered_tasks.identity_id) ELSE NULL::record END), filtered_tasks.is_urgent_ordinal, filtered_tasks.due_at, filtered_tasks.id
25. 0.000 0.000 ↓ 0.0

CTE Scan on filtered_tasks (cost=0.00..0.22 rows=11 width=56) (actual rows= loops=)

26. 0.000 0.000 ↓ 0.0

Index Scan using capsule_core_identity_pkey on capsule_core_identity identity (cost=0.42..8.44 rows=1 width=18) (actual rows= loops=)

  • Index Cond: (task.identity_id = id)
27. 0.000 0.000 ↓ 0.0

Index Scan using capsule_core_patient_pkey on capsule_core_patient patient (cost=0.42..8.44 rows=1 width=18) (actual rows= loops=)

  • Index Cond: (task.patient_id = id)
28. 0.000 0.000 ↓ 0.0

Index Scan using capsule_auth_user_pkey on capsule_auth_user assignee (cost=0.29..8.31 rows=1 width=34) (actual rows= loops=)

  • Index Cond: (task.assigned_to_id = id)
29. 0.000 0.000 ↓ 0.0

Index Scan using capsule_core_order_pkey on capsule_core_order o (cost=0.42..8.44 rows=1 width=13) (actual rows= loops=)

  • Index Cond: (task.order_id = id)
30. 0.000 0.000 ↓ 0.0

Index Scan using capsule_core_deliverywindow_pkey on capsule_core_deliverywindow old_delivery_window (cost=0.29..0.30 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (o.delivery_window_id = id)
31. 0.000 0.000 ↓ 0.0

Index Scan using delivery_window_pkey on delivery_window new_delivery_window (cost=0.28..0.30 rows=1 width=24) (actual rows= loops=)

  • Index Cond: (o.new_delivery_window_id = id)
32. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.06 rows=4 width=282) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Seq Scan on delivery_tier dt (cost=0.00..1.04 rows=4 width=282) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Index Scan using capsule_core_provider_pkey on capsule_core_provider provider (cost=0.29..8.30 rows=1 width=18) (actual rows= loops=)

  • Index Cond: (task.provider_id = id)
35. 0.000 0.000 ↓ 0.0

Materialize (cost=0.00..1.03 rows=2 width=148) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on facility (cost=0.00..1.02 rows=2 width=148) (actual rows= loops=)

37.          

SubPlan (forResult)

38. 0.000 0.000 ↓ 0.0

Aggregate (cost=8.44..8.45 rows=1 width=32) (actual rows= loops=)

39. 0.000 0.000 ↓ 0.0

Index Scan using capsule_os_taskmetadata_task_id_name_type_b20119c1_uniq on capsule_os_taskmetadata (cost=0.42..8.44 rows=1 width=21) (actual rows= loops=)

  • Index Cond: ((task_id = task.id) AND ((type)::text = 'tag'::text))
  • Filter: value_boolean