explain.depesz.com

PostgreSQL's explain analyze made readable

Result: OgKu

Settings
# exclusive inclusive rows x rows loops node
1. 0.000 2,254.595 ↑ 8.4 12 1

Limit (cost=2,866,458.70..2,866,458.96 rows=101 width=112) (actual time=2,254.585..2,254.595 rows=12 loops=1)

2.          

Initplan (forLimit)

3. 0.021 0.021 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=8) (actual time=0.021..0.021 rows=1 loops=1)

4. 0.008 0.008 ↑ 1.0 1 1

Result (cost=0.00..0.03 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=1)

5. 0.097 2,254.587 ↑ 8.4 12 1

Sort (cost=2,866,458.65..2,866,458.90 rows=101 width=112) (actual time=2,254.582..2,254.587 rows=12 loops=1)

  • 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
  • Sort Method: quicksort Memory: 26kB
6. 1,233.901 2,254.490 ↑ 8.4 12 1

GroupAggregate (cost=184,999.99..2,866,455.28 rows=101 width=112) (actual time=1,106.245..2,254.490 rows=12 loops=1)

  • 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)
7. 38.616 1,020.589 ↑ 16.5 8,199 1

Sort (cost=184,999.99..185,338.05 rows=135,222 width=532) (actual time=1,012.885..1,020.589 rows=8,199 loops=1)

  • 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)
  • Sort Method: external merge Disk: 2856kB
8. 9.679 981.973 ↑ 16.5 8,199 1

Hash Left Join (cost=69,445.04..108,767.72 rows=135,222 width=532) (actual time=929.032..981.973 rows=8,199 loops=1)

  • Hash Cond: (visit_facts_clone.care_request_id = ed_diversion_survey_response_clone.care_request_id)
9. 8.515 944.052 ↑ 16.5 8,199 1

Hash Left Join (cost=68,315.27..105,982.88 rows=135,222 width=504) (actual time=900.561..944.052 rows=8,199 loops=1)

  • Hash Cond: (care_requests.channel_item_id = channel_items.id)
10. 8.907 932.890 ↑ 16.5 8,199 1

Hash Left Join (cost=68,185.54..104,101.16 rows=135,222 width=451) (actual time=897.885..932.890 rows=8,199 loops=1)

  • Hash Cond: (care_requests.id = vitals_flat.care_request_id)
11. 8.068 871.577 ↑ 16.5 8,199 1

Hash Left Join (cost=66,051.06..100,417.67 rows=135,222 width=431) (actual time=845.225..871.577 rows=8,199 loops=1)

  • Hash Cond: (diversion_flat.diversion_type_id = diversion_type.id)
12. 33.589 863.492 ↑ 16.5 8,199 1

Hash Right Join (cost=66,037.91..98,545.22 rows=135,222 width=431) (actual time=845.188..863.492 rows=8,199 loops=1)

  • Hash Cond: (medical_necessity_notes.care_request_id = care_requests.id)
13. 197.720 197.720 ↑ 1.0 24,271 1

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

  • Filter: ((note_type)::text = 'medical-necessity'::text)
  • Rows Removed by Filter: 719611
14. 10.694 632.183 ↑ 16.5 8,199 1

Hash (cost=58,272.64..58,272.64 rows=135,222 width=343) (actual time=632.183..632.183 rows=8,199 loops=1)

  • Buckets: 16384 Batches: 16 Memory Usage: 262kB
15. 10.368 621.489 ↑ 16.5 8,199 1

Hash Left Join (cost=45,040.66..58,272.64 rows=135,222 width=343) (actual time=595.553..621.489 rows=8,199 loops=1)

  • 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))
  • Rows Removed by Join Filter: 360
16. 1.680 605.708 ↑ 30.2 2,208 1

Hash Left Join (cost=44,923.58..55,035.22 rows=66,739 width=173) (actual time=590.096..605.708 rows=2,208 loops=1)

  • Hash Cond: (athenadwh_clinicalencounter_dxicd10.icd_code_id = athenadwh_icdcodeall.icd_code_id)
17. 1.482 595.308 ↑ 30.2 2,208 1

Hash Left Join (cost=43,513.50..52,707.48 rows=66,739 width=171) (actual time=581.320..595.308 rows=2,208 loops=1)

  • Hash Cond: (transaction_facts_clone.cpt_code_dim_id = cpt_code_dimensions_clone.id)
18. 98.787 585.586 ↑ 30.2 2,208 1

Hash Right Join (cost=43,132.28..51,408.60 rows=66,739 width=169) (actual time=573.006..585.586 rows=2,208 loops=1)

  • Hash Cond: (athenadwh_clinicalencounter_dxicd10.clinical_encounter_dx_id = athenadwh_clinicalencounter_diagnosis.clinical_encounter_dx_id)
19. 73.315 73.315 ↑ 1.0 172,339 1

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

20. 1.680 413.484 ↑ 27.2 2,208 1

Hash (cost=40,912.68..40,912.68 rows=60,128 width=169) (actual time=413.484..413.484 rows=2,208 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 330kB
21. 67.289 411.804 ↑ 27.2 2,208 1

Hash Right Join (cost=34,326.41..40,912.68 rows=60,128 width=169) (actual time=403.598..411.804 rows=2,208 loops=1)

  • Hash Cond: (athenadwh_clinicalencounter_diagnosis.clinical_encounter_id = athenadwh_clinical_encounters_clone.clinical_encounter_id)
22. 69.870 69.870 ↑ 1.0 146,867 1

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

  • Filter: (deleted_datetime IS NULL)
  • Rows Removed by Filter: 14424
23. 1.126 274.645 ↑ 23.4 1,200 1

Hash (cost=33,317.87..33,317.87 rows=28,043 width=167) (actual time=274.645..274.645 rows=1,200 loops=1)

  • Buckets: 32768 Batches: 2 Memory Usage: 329kB
24. 1.649 273.519 ↑ 23.4 1,200 1

Nested Loop Left Join (cost=4,856.70..33,317.87 rows=28,043 width=167) (actual time=18.500..273.519 rows=1,200 loops=1)

25. 0.822 271.870 ↑ 4.3 1,200 1

Nested Loop Left Join (cost=4,856.27..18,533.59 rows=5,101 width=168) (actual time=18.498..271.870 rows=1,200 loops=1)

26. 128.596 268.648 ↑ 4.3 1,200 1

Hash Right Join (cost=4,855.85..15,543.40 rows=5,101 width=163) (actual time=18.486..268.648 rows=1,200 loops=1)

  • Hash Cond: (athenadwh_medical_history_clone.chart_id = athenadwh_clinical_encounters_clone.chart_id)
27. 129.877 129.877 ↑ 1.0 365,271 1

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

28. 0.218 10.175 ↑ 2.5 195 1

Hash (cost=4,849.87..4,849.87 rows=479 width=156) (actual time=10.175..10.175 rows=195 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 29kB
29. 0.312 9.957 ↑ 2.5 195 1

Nested Loop Left Join (cost=17.14..4,849.87 rows=479 width=156) (actual time=0.264..9.957 rows=195 loops=1)

30. 1.280 9.255 ↑ 2.5 195 1

Nested Loop Left Join (cost=16.73..4,455.72 rows=479 width=146) (actual time=0.256..9.255 rows=195 loops=1)

  • 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
  • Rows Removed by Join Filter: 204
31. 0.385 4.855 ↑ 2.5 195 1

Nested Loop Left Join (cost=16.43..4,213.89 rows=479 width=272) (actual time=0.220..4.855 rows=195 loops=1)

32. 0.238 2.715 ↑ 2.5 195 1

Hash Join (cost=16.14..4,024.02 rows=479 width=263) (actual time=0.199..2.715 rows=195 loops=1)

  • Hash Cond: (care_requests.market_id = markets.id)
33. 0.519 2.400 ↑ 2.5 195 1

Nested Loop (cost=0.58..4,001.90 rows=479 width=143) (actual time=0.109..2.400 rows=195 loops=1)

34. 0.711 0.711 ↑ 2.5 195 1

Index Scan using idx7n9x4niul1hsgmp9axa4d_care_request_flat_3 on "lr$7n9x4niul1hsgmp9axa4d_care_request_flat" care_request_flat (cost=0.29..1,035.02 rows=484 width=101) (actual time=0.080..0.711 rows=195 loops=1)

  • Index Cond: ((created_date >= $0) AND (created_date < $1))
  • Filter: ((CASE WHEN (COALESCE(complete_comment, archive_comment) ~~ '%Spoke to my family doctor%'::text) THEN 'Spoke to my Family Doctor'::text ELSE btrim(split_part(COALESCE(complete_comment, archive_comment), ':'::text, 2)) END <> ALL ('{"Test Case",Duplicate}'::text[])) OR (CASE WHEN (COALESCE(complete_comment, archive_comment) ~~ '%Spoke to my family doctor%'::text) THEN 'Spoke to my Family Doctor'::text ELSE btrim(split_part(COALESCE(complete_comment, archive_comment), ':'::text, 2)) END IS NULL))
  • Rows Removed by Filter: 7
35. 1.170 1.170 ↑ 1.0 1 195

Index Scan using index_care_requests_on_id on care_requests (cost=0.29..6.12 rows=1 width=42) (actual time=0.006..0.006 rows=1 loops=195)

  • Index Cond: (id = care_request_flat.care_request_id)
36. 0.008 0.077 ↑ 1.0 12 1

Hash (cost=15.41..15.41 rows=12 width=128) (actual time=0.076..0.077 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
37. 0.034 0.069 ↑ 1.0 12 1

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

  • Hash Cond: (timezones.rails_tz = (markets.sa_time_zone)::bpchar)
38. 0.006 0.006 ↑ 58.0 5 1

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

39. 0.010 0.029 ↑ 1.0 12 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
40. 0.019 0.019 ↑ 1.0 12 1

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

  • Filter: ((name)::text ~~ '%'::text)
41. 1.755 1.755 ↑ 1.0 1 195

Index Scan using patients_pkey on patients (cost=0.29..0.39 rows=1 width=13) (actual time=0.008..0.009 rows=1 loops=195)

  • Index Cond: (care_requests.patient_id = id)
42. 3.120 3.120 ↑ 1.0 2 195

Index Scan using clinical_encounters_clone_pt_id_idx on athenadwh_clinical_encounters_clone (cost=0.29..0.43 rows=2 width=27) (actual time=0.010..0.016 rows=2 loops=195)

  • Index Cond: ((patients.ehr_id)::text = ((patient_id)::character varying)::text)
43. 0.390 0.390 ↓ 0.0 0 195

Index Scan using survey_responses_clone_index on visit_facts_clone (cost=0.42..0.81 rows=1 width=10) (actual time=0.002..0.002 rows=0 loops=195)

  • Index Cond: (care_requests.id = care_request_id)
44. 2.400 2.400 ↓ 0.0 0 1,200

Index Scan using visit_dimensions_clone_index on visit_dimensions_clone (cost=0.42..0.58 rows=1 width=9) (actual time=0.002..0.002 rows=0 loops=1,200)

  • Index Cond: (care_requests.id = care_request_id)
45. 0.000 0.000 ↓ 0.0 0 1,200

Index Scan using transaction_facts_clone_index on transaction_facts_clone (cost=0.42..2.75 rows=15 width=9) (actual time=0.000..0.000 rows=0 loops=1,200)

  • Index Cond: ((visit_dim_number)::text = (visit_dimensions_clone.visit_number)::text)
  • Filter: (voided_date IS NULL)
46. 3.972 8.240 ↑ 1.0 9,032 1

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

  • Buckets: 16384 Batches: 1 Memory Usage: 517kB
47. 4.268 4.268 ↑ 1.0 9,032 1

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

48. 3.198 8.720 ↑ 1.0 5,648 1

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

  • Buckets: 8192 Batches: 1 Memory Usage: 307kB
49. 5.522 5.522 ↑ 1.0 5,648 1

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

50. 2.763 5.413 ↑ 1.0 2,448 1

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

  • Buckets: 4096 Batches: 1 Memory Usage: 520kB
51. 2.650 2.650 ↑ 1.0 2,448 1

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

52. 0.010 0.017 ↑ 35.0 4 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
53. 0.007 0.007 ↑ 35.0 4 1

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

54. 24.110 52.406 ↑ 1.0 50,999 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 3447kB
55. 28.296 28.296 ↑ 1.0 50,999 1

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

56. 1.338 2.647 ↑ 1.0 2,566 1

Hash (cost=97.66..97.66 rows=2,566 width=61) (actual time=2.646..2.647 rows=2,566 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 271kB
57. 1.309 1.309 ↑ 1.0 2,566 1

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

58. 13.866 28.242 ↑ 1.0 33,632 1

Hash (cost=709.34..709.34 rows=33,634 width=15) (actual time=28.242..28.242 rows=33,632 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 2092kB
59. 14.376 14.376 ↑ 1.0 33,634 1

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 time=0.007..14.376 rows=33,634 loops=1)