explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jC1wX : Optimization for: plan #cMG0

Settings

Optimization path:

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

Limit (cost=572,215,138.02..572,215,138.27 rows=101 width=112) (actual rows= loops=)

2. 0.000 0.000 ↓ 0.0

Sort (cost=572,215,138.02..572,215,138.27 rows=101 width=112) (actual rows= loops=)

  • Sort Key: (count(DISTINCT CASE WHEN (date(care_request_flat.complete_date) IS NOT NULL) THEN care_request_flat.care_request_id ELSE NULL::integer END)) DESC
3. 0.000 0.000 ↓ 0.0

GroupAggregate (cost=34,755,720.96..572,215,134.66 rows=101 width=112) (actual rows= loops=)

  • Group Key: (CASE WHEN (((athenadwh_medical_history_clone.question)::text = 'Notes'::text) OR ((athenadwh_medical_history_clone.question)::text = 'Reviewed Date'::text)) THEN NULL::character varying ELSE athenadwh_medical_history_clone.question END)
4. 0.000 0.000 ↓ 0.0

Sort (cost=34,755,720.96..34,823,479.33 rows=27,103,349 width=532) (actual rows= loops=)

  • Sort Key: (CASE WHEN (((athenadwh_medical_history_clone.question)::text = 'Notes'::text) OR ((athenadwh_medical_history_clone.question)::text = 'Reviewed Date'::text)) THEN NULL::character varying ELSE athenadwh_medical_history_clone.question END)
5. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,783,989.54..5,470,793.85 rows=27,103,349 width=532) (actual rows= loops=)

  • Hash Cond: (visit_facts_clone.care_request_id = ed_diversion_survey_response_clone.care_request_id)
6. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,782,859.78..5,137,927.87 rows=27,103,349 width=504) (actual rows= loops=)

  • Hash Cond: (care_requests.channel_item_id = channel_items.id)
7. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,782,730.04..4,786,638.11 rows=27,103,349 width=451) (actual rows= loops=)

  • Hash Cond: (care_requests.id = vitals_flat.care_request_id)
8. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=2,780,595.57..4,474,024.99 rows=27,103,349 width=431) (actual rows= loops=)

  • Hash Cond: (diversion_flat.diversion_type_id = diversion_type.id)
9. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=2,780,582.42..4,101,340.79 rows=27,103,349 width=431) (actual rows= loops=)

  • Hash Cond: (medical_necessity_notes.care_request_id = care_requests.id)
10. 0.000 0.000 ↓ 0.0

Seq Scan on notes medical_necessity_notes (cost=0.00..25,271.53 rows=24,771 width=92) (actual rows= loops=)

  • Filter: ((note_type)::text = 'medical-necessity'::text)
11. 0.000 0.000 ↓ 0.0

Hash (cost=1,224,256.55..1,224,256.55 rows=27,103,349 width=343) (actual rows= loops=)

12. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=263,740.74..1,224,256.55 rows=27,103,349 width=343) (actual rows= loops=)

  • Hash Cond: ("substring"(btrim((athenadwh_icdcodeall.diagnosis_code)::text), 0, 4) = (diversion_flat.diagnosis_code)::text)
  • Join Filter: ((athenadwh_clinicalencounter_diagnosis.ordering <= 2) OR (athenadwh_clinicalencounter_diagnosis.ordering IS NULL))
13. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=263,623.66..598,713.35 rows=13,376,868 width=173) (actual rows= loops=)

  • Hash Cond: (care_requests.id = visit_dimensions_clone.care_request_id)
14. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=185,000.14..250,593.22 rows=2,433,221 width=167) (actual rows= loops=)

  • Hash Cond: (athenadwh_clinicalencounter_diagnosis.clinical_encounter_id = athenadwh_clinical_encounters_clone.clinical_encounter_id)
15. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=6,937.15..19,176.32 rows=163,043 width=12) (actual rows= loops=)

  • Hash Cond: (athenadwh_clinicalencounter_dxicd10.icd_code_id = athenadwh_icdcodeall.icd_code_id)
16. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=5,527.07..15,524.40 rows=163,043 width=10) (actual rows= loops=)

  • Hash Cond: (athenadwh_clinicalencounter_dxicd10.clinical_encounter_dx_id = athenadwh_clinicalencounter_diagnosis.clinical_encounter_dx_id)
17. 0.000 0.000 ↓ 0.0

Seq Scan on athenadwh_clinicalencounter_dxicd10 (cost=0.00..3,500.39 rows=172,339 width=8) (actual rows= loops=)

18. 0.000 0.000 ↓ 0.0

Hash (cost=2,972.91..2,972.91 rows=146,893 width=10) (actual rows= loops=)

19. 0.000 0.000 ↓ 0.0

Seq Scan on athenadwh_clinicalencounter_diagnosis (cost=0.00..2,972.91 rows=146,893 width=10) (actual rows= loops=)

  • Filter: (deleted_datetime IS NULL)
20. 0.000 0.000 ↓ 0.0

Hash (cost=1,339.48..1,339.48 rows=5,648 width=10) (actual rows= loops=)

21. 0.000 0.000 ↓ 0.0

Seq Scan on athenadwh_icdcodeall (cost=0.00..1,339.48 rows=5,648 width=10) (actual rows= loops=)

22. 0.000 0.000 ↓ 0.0

Hash (cost=141,319.79..141,319.79 rows=1,022,416 width=163) (actual rows= loops=)

23. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=84,536.56..141,319.79 rows=1,022,416 width=163) (actual rows= loops=)

  • Hash Cond: (athenadwh_clinical_encounters_clone.chart_id = athenadwh_medical_history_clone.chart_id)
24. 0.000 0.000 ↓ 0.0

Merge Left Join (cost=68,919.23..74,098.10 rows=96,006 width=156) (actual rows= loops=)

  • Merge Cond: ((patients.ehr_id)::text = (((athenadwh_clinical_encounters_clone.patient_id)::character varying)::text))
  • Join Filter: CASE WHEN (athenadwh_clinical_encounters_clone.appointment_id IS NOT NULL) THEN (((athenadwh_clinical_encounters_clone.appointment_id)::character varying)::text = (care_requests.ehr_id)::text) WHEN (athenadwh_clinical_encounters_clone.appointment_id IS NULL) THEN (timezone((timezones.pg_tz)::text, timezone('UTC'::text, ((athenadwh_clinical_encounters_clone.encounter_date)::date)::timestamp with time zone)) = date(care_request_flat.on_scene_date)) ELSE NULL::boolean END
25. 0.000 0.000 ↓ 0.0

Sort (cost=60,891.49..61,131.51 rows=96,006 width=282) (actual rows= loops=)

  • Sort Key: patients.ehr_id
26. 0.000 0.000 ↓ 0.0

Hash Join (cost=27,634.38..40,147.10 rows=96,006 width=282) (actual rows= loops=)

  • Hash Cond: (care_requests.market_id = markets.id)
27. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=27,618.82..38,814.56 rows=96,006 width=162) (actual rows= loops=)

  • Hash Cond: (care_request_flat.care_request_id = care_requests.id)
  • Filter: ((CASE WHEN (COALESCE(care_request_flat.complete_comment, care_request_flat.archive_comment) ~~ '%Spoke to my family doctor%'::text) THEN 'Spoke to my Family Doctor'::text ELSE btrim(split_part(COALESCE(care_request_flat.complete_comment, care_request_flat.archive_comment), ':'::text, 2)) END <> ALL ('{"Test Case",Duplicate}'::text[])) OR (CASE WHEN (COALESCE(care_request_flat.complete_comment, care_request_flat.archive_comment) ~~ '%Spoke to my family doctor%'::text) THEN 'Spoke to my Family Doctor'::text ELSE btrim(split_part(COALESCE(care_request_flat.complete_comment, care_request_flat.archive_comment), ':'::text, 2)) END IS NULL))
28. 0.000 0.000 ↓ 0.0

Seq Scan on "lr$7n9x4niul1hsgmp9axa4d_care_request_flat" care_request_flat (cost=0.00..4,073.27 rows=97,727 width=101) (actual rows= loops=)

29. 0.000 0.000 ↓ 0.0

Hash (cost=25,364.68..25,364.68 rows=96,971 width=61) (actual rows= loops=)

30. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=9,937.12..25,364.68 rows=96,971 width=61) (actual rows= loops=)

  • Hash Cond: (care_requests.patient_id = patients.id)
31. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=6,048.88..17,858.91 rows=96,971 width=52) (actual rows= loops=)

  • Hash Cond: (visit_facts_clone.care_request_id = care_requests.id)
32. 0.000 0.000 ↓ 0.0

Seq Scan on visit_facts_clone (cost=0.00..8,699.75 rows=96,375 width=10) (actual rows= loops=)

33. 0.000 0.000 ↓ 0.0

Hash (cost=3,983.74..3,983.74 rows=96,971 width=42) (actual rows= loops=)

34. 0.000 0.000 ↓ 0.0

Seq Scan on care_requests (cost=0.00..3,983.74 rows=96,971 width=42) (actual rows= loops=)

  • Filter: (deleted_at IS NULL)
35. 0.000 0.000 ↓ 0.0

Hash (cost=2,450.22..2,450.22 rows=82,722 width=13) (actual rows= loops=)

36. 0.000 0.000 ↓ 0.0

Seq Scan on patients (cost=0.00..2,450.22 rows=82,722 width=13) (actual rows= loops=)

37. 0.000 0.000 ↓ 0.0

Hash (cost=15.41..15.41 rows=12 width=128) (actual rows= loops=)

38. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=1.30..15.41 rows=12 width=128) (actual rows= loops=)

  • Hash Cond: (timezones.rails_tz = (markets.sa_time_zone)::bpchar)
39. 0.000 0.000 ↓ 0.0

Seq Scan on timezones (cost=0.00..12.90 rows=290 width=248) (actual rows= loops=)

40. 0.000 0.000 ↓ 0.0

Hash (cost=1.15..1.15 rows=12 width=36) (actual rows= loops=)

41. 0.000 0.000 ↓ 0.0

Seq Scan on markets (cost=0.00..1.15 rows=12 width=36) (actual rows= loops=)

  • Filter: ((name)::text ~~ '%'::text)
42. 0.000 0.000 ↓ 0.0

Sort (cost=8,027.73..8,199.01 rows=68,509 width=27) (actual rows= loops=)

  • Sort Key: (((athenadwh_clinical_encounters_clone.patient_id)::character varying)::text)
43. 0.000 0.000 ↓ 0.0

Seq Scan on athenadwh_clinical_encounters_clone (cost=0.00..2,525.09 rows=68,509 width=27) (actual rows= loops=)

44. 0.000 0.000 ↓ 0.0

Hash (cost=9,266.48..9,266.48 rows=365,348 width=15) (actual rows= loops=)

45. 0.000 0.000 ↓ 0.0

Seq Scan on athenadwh_medical_history_clone (cost=0.00..9,266.48 rows=365,348 width=15) (actual rows= loops=)

46. 0.000 0.000 ↓ 0.0

Hash (cost=69,414.86..69,414.86 rows=529,732 width=10) (actual rows= loops=)

47. 0.000 0.000 ↓ 0.0

Hash Left Join (cost=5,147.25..69,414.86 rows=529,732 width=10) (actual rows= loops=)

  • Hash Cond: (transaction_facts_clone.cpt_code_dim_id = cpt_code_dimensions_clone.id)
48. 0.000 0.000 ↓ 0.0

Hash Right Join (cost=4,766.03..61,749.83 rows=529,732 width=8) (actual rows= loops=)

  • Hash Cond: ((transaction_facts_clone.visit_dim_number)::text = (visit_dimensions_clone.visit_number)::text)
49. 0.000 0.000 ↓ 0.0

Seq Scan on transaction_facts_clone (cost=0.00..44,054.97 rows=529,735 width=9) (actual rows= loops=)

  • Filter: (voided_date IS NULL)
50. 0.000 0.000 ↓ 0.0

Hash (cost=3,090.57..3,090.57 rows=96,357 width=9) (actual rows= loops=)

51. 0.000 0.000 ↓ 0.0

Seq Scan on visit_dimensions_clone (cost=0.00..3,090.57 rows=96,357 width=9) (actual rows= loops=)

52. 0.000 0.000 ↓ 0.0

Hash (cost=268.32..268.32 rows=9,032 width=10) (actual rows= loops=)

53. 0.000 0.000 ↓ 0.0

Seq Scan on cpt_code_dimensions_clone (cost=0.00..268.32 rows=9,032 width=10) (actual rows= loops=)

54. 0.000 0.000 ↓ 0.0

Hash (cost=86.48..86.48 rows=2,448 width=172) (actual rows= loops=)

55. 0.000 0.000 ↓ 0.0

Seq Scan on "lr$7n7caow2iw30f33r6yizd_diversion_flat" diversion_flat (cost=0.00..86.48 rows=2,448 width=172) (actual rows= loops=)

56. 0.000 0.000 ↓ 0.0

Hash (cost=11.40..11.40 rows=140 width=4) (actual rows= loops=)

57. 0.000 0.000 ↓ 0.0

Seq Scan on diversion_type (cost=0.00..11.40 rows=140 width=4) (actual rows= loops=)

58. 0.000 0.000 ↓ 0.0

Hash (cost=1,496.99..1,496.99 rows=50,999 width=28) (actual rows= loops=)

59. 0.000 0.000 ↓ 0.0

Seq Scan on "lr$7nqlsfxho8zhoeucfr3yg_vitals_flat" vitals_flat (cost=0.00..1,496.99 rows=50,999 width=28) (actual rows= loops=)

60. 0.000 0.000 ↓ 0.0

Hash (cost=97.66..97.66 rows=2,566 width=61) (actual rows= loops=)

61. 0.000 0.000 ↓ 0.0

Seq Scan on channel_items (cost=0.00..97.66 rows=2,566 width=61) (actual rows= loops=)

62. 0.000 0.000 ↓ 0.0

Hash (cost=709.34..709.34 rows=33,634 width=15) (actual rows= loops=)

63. 0.000 0.000 ↓ 0.0

Seq Scan on "lr$7nmny7kaof4b8e3w2ebzg_ed_diversion_survey_response_clone" ed_diversion_survey_response_clone (cost=0.00..709.34 rows=33,634 width=15) (actual rows= loops=)