explain.depesz.com

PostgreSQL's explain analyze made readable

Result: BfEt

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

Unique (cost=1,181,180.78..1,280,516.66 rows=1,370,150 width=756) (actual rows= loops=)

2.          

Initplan (forUnique)

3. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_filters (cost=0.00..23.10 rows=1,310 width=32) (actual rows= loops=)

4. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_filters tmp_filters_1 (cost=0.00..23.10 rows=1,310 width=32) (actual rows= loops=)

5. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_filters tmp_filters_2 (cost=0.00..23.10 rows=1,310 width=32) (actual rows= loops=)

6. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_filters tmp_filters_3 (cost=0.00..23.10 rows=1,310 width=32) (actual rows= loops=)

7. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_filters tmp_filters_4 (cost=0.00..23.10 rows=1,310 width=32) (actual rows= loops=)

8. 0.000 0.000 ↓ 0.0

Sort (cost=1,181,065.28..1,184,490.66 rows=1,370,150 width=756) (actual rows= loops=)

  • Sort Key: ((i.participant_id)::text), ((i.last_name)::text), ((i.first_name)::text), i.birth_date, i.internal_ids, ((i.address_line_1)::text), ((i.county)::text), ((i.phone_number)::text), i.case_manager, ((d.gender)::text), ((d.hispanic)::text), ((d.race)::text), ((d.urban_rural)::text), ((d.veteran_status)::text), d.functionally_impaired, vpca.home_visit_date, ((serviceutilization.units_received)::text), ((viewservice.provider)::text), ((viewservice.service_name)::text), viewservice.service_detail, viewservice.status, viewservice.start_date, viewservice.end_date, note1.note_date, note1.medicaid_alternatives, (to_char((p_1.created_date)::timestamp with time zone, 'MM/DD/YYYY'::text))
9. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,911.27..123,510.56 rows=1,370,150 width=756) (actual rows= loops=)

  • Merge Cond: (p.participant_id = note1.participant_id)
  • Join Filter: CASE WHEN (note1.service_plan_id IS NOT NULL) THEN (note1.service_plan_id = viewservice.service_plan_id) ELSE (viewservice.provider_id = ANY (note1.provider_ids)) END
10. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,851.20..12,468.33 rows=548,060 width=716) (actual rows= loops=)

  • Merge Cond: (p.participant_id = viewservice.participant_id)
11. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,791.12..3,913.32 rows=109,612 width=508) (actual rows= loops=)

  • Merge Cond: (p.participant_id = vpca.participant_id)
12. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,731.04..2,154.29 rows=21,922 width=476) (actual rows= loops=)

  • Merge Cond: (p.participant_id = d.participant_id)
13. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,670.96..1,754.45 rows=4,384 width=284) (actual rows= loops=)

  • Merge Cond: (p.participant_id = i.participant_id)
14. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=1,610.88..1,626.41 rows=877 width=20) (actual rows= loops=)

  • Merge Cond: (p.participant_id = serviceutilization.participant_id)
15. 0.000 0.000 ↓ 0.0

Sort (cost=1,177.78..1,178.96 rows=474 width=12) (actual rows= loops=)

  • Sort Key: p.participant_id
16. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.86..1,156.71 rows=474 width=12) (actual rows= loops=)

17. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_participant p (cost=0.00..237.35 rows=235 width=8) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.86..3.90 rows=1 width=12) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Nested Loop Left Join (cost=0.57..3.42 rows=1 width=20) (actual rows= loops=)

20. 0.000 0.000 ↓ 0.0

Index Scan using idx_action_plan_participant_id on action_plan p_1 (cost=0.29..2.99 rows=1 width=20) (actual rows= loops=)

  • Index Cond: (participant_id = p.participant_id)
21. 0.000 0.000 ↓ 0.0

Index Scan using idx_acpg_action_plan_id on action_plan_goal g (cost=0.29..0.42 rows=1 width=16) (actual rows= loops=)

  • Index Cond: (p_1.action_plan_id = action_plan_id)
22. 0.000 0.000 ↓ 0.0

Index Only Scan using idx_acpgt_action_plan_goal_id on action_plan_goal_task t (cost=0.29..0.47 rows=1 width=8) (actual rows= loops=)

  • Index Cond: (action_plan_goal_id = g.action_plan_goal_id)
23. 0.000 0.000 ↓ 0.0

Sort (cost=433.10..434.03 rows=370 width=16) (actual rows= loops=)

  • Sort Key: serviceutilization.participant_id
24. 0.000 0.000 ↓ 0.0

Subquery Scan on serviceutilization (cost=409.92..417.32 rows=370 width=16) (actual rows= loops=)

25. 0.000 0.000 ↓ 0.0

HashAggregate (cost=409.92..413.62 rows=370 width=80) (actual rows= loops=)

  • Group Key: tmp_main.participant_id, tmp_main.unit_start_date, tmp_main.unit_end_date
26. 0.000 0.000 ↓ 0.0

Seq Scan on tmp_main (cost=0.00..372.96 rows=3,696 width=80) (actual rows= loops=)

27. 0.000 0.000 ↓ 0.0

Sort (cost=60.08..62.58 rows=1,000 width=264) (actual rows= loops=)

  • Sort Key: i.participant_id
28. 0.000 0.000 ↓ 0.0

Function Scan on fn_client_custom_report_identification i (cost=0.25..10.25 rows=1,000 width=264) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Sort (cost=60.08..62.58 rows=1,000 width=200) (actual rows= loops=)

  • Sort Key: d.participant_id
30. 0.000 0.000 ↓ 0.0

Function Scan on fn_client_custom_report_demographics d (cost=0.25..10.25 rows=1,000 width=200) (actual rows= loops=)

31. 0.000 0.000 ↓ 0.0

Sort (cost=60.08..62.58 rows=1,000 width=40) (actual rows= loops=)

  • Sort Key: vpca.participant_id
32. 0.000 0.000 ↓ 0.0

Function Scan on fn_client_custom_report_assessment vpca (cost=0.25..10.25 rows=1,000 width=40) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Sort (cost=60.08..62.58 rows=1,000 width=216) (actual rows= loops=)

  • Sort Key: viewservice.participant_id
34. 0.000 0.000 ↓ 0.0

Function Scan on fn_client_custom_report_service_enrollment viewservice (cost=0.25..10.25 rows=1,000 width=216) (actual rows= loops=)

35. 0.000 0.000 ↓ 0.0

Sort (cost=60.08..62.58 rows=1,000 width=112) (actual rows= loops=)

  • Sort Key: note1.participant_id
36. 0.000 0.000 ↓ 0.0

Function Scan on fn_client_custom_report_progress_notes note1 (cost=0.25..10.25 rows=1,000 width=112) (actual rows= loops=)