explain.depesz.com

PostgreSQL's explain analyze made readable

Result: nVKu

Settings
# exclusive inclusive rows x rows loops node
1. 256,128.463 475,238.274 ↓ 1.0 75 1

GroupAggregate (cost=1.27..25,605,051.43 rows=74 width=12) (actual time=66,138.831..475,238.274 rows=75 loops=1)

  • Output: problems.business_entity_id, count(DISTINCT problems.patient_id)
  • Group Key: problems.business_entity_id
2. 155,172.849 219,109.811 ↓ 1.2 1,319,313,824 1

Nested Loop (cost=1.27..20,041,925.08 rows=1,112,625,122 width=8) (actual time=0.045..219,109.811 rows=1,319,313,824 loops=1)

  • Output: problems.business_entity_id, problems.patient_id
  • Join Filter: (problems.icd_indicator = bcm_problem_mapping.icd_indicator)
  • Rows Removed by Join Filter: 43759216
3. 865.555 5,519.546 ↓ 1.1 9,736,236 1

Nested Loop (cost=0.99..493,968.51 rows=8,926,002 width=12) (actual time=0.030..5,519.546 rows=9,736,236 loops=1)

  • Output: problems.business_entity_id, problems.patient_id, problems.icd_indicator
4. 104.948 295.480 ↓ 1.1 484,279 1

Nested Loop (cost=0.56..18,386.02 rows=451,634 width=12) (actual time=0.023..295.480 rows=484,279 loops=1)

  • Output: problems.business_entity_id, problems.patient_id, problems.icd_indicator
5. 0.161 0.161 ↓ 1.3 93 1

Index Only Scan using business_entities_pkey on public.business_entities (cost=0.14..7.18 rows=69 width=4) (actual time=0.008..0.161 rows=93 loops=1)

  • Output: business_entities.business_entity_id
  • Heap Fetches: 93
6. 190.371 190.371 ↑ 1.3 5,207 93

Index Scan using fki_business_entity_prob on public.problems (cost=0.42..200.91 rows=6,545 width=12) (actual time=0.009..2.047 rows=5,207 loops=93)

  • Output: problems.problem_id, problems.business_entity_id, problems.patient_id, problems.problem, problems.status, problems.onset_date, problems.icd_code, problems.icd_indicator, problems.chronicity, problems.condition
  • Index Cond: (problems.business_entity_id = business_entities.business_entity_id)
7. 4,358.511 4,358.511 ↑ 1.0 20 484,279

Index Only Scan using medications_mv_patient_id_idx on public.medications_mv (cost=0.43..0.85 rows=20 width=4) (actual time=0.002..0.009 rows=20 loops=484,279)

  • Output: medications_mv.patient_id
  • Index Cond: (medications_mv.patient_id = problems.patient_id)
  • Heap Fetches: 9736236
8. 58,417.369 58,417.416 ↑ 1.0 140 9,736,236

Materialize (cost=0.28..12.56 rows=146 width=4) (actual time=0.000..0.006 rows=140 loops=9,736,236)

  • Output: bcm_problem_mapping.icd_indicator
9. 0.047 0.047 ↑ 1.0 140 1

Index Scan using bcm_problem_mapping_mapped_problem_name_idx on public.bcm_problem_mapping (cost=0.28..11.83 rows=146 width=4) (actual time=0.013..0.047 rows=140 loops=1)

  • Output: bcm_problem_mapping.icd_indicator
  • Index Cond: ((bcm_problem_mapping.mapped_problem_name)::text = 'Rheumatoid Arthritis'::text)