explain.depesz.com

PostgreSQL's explain analyze made readable

Result: fHZI

Settings
# exclusive inclusive rows x rows loops node
1. 6,543.906 6,543.906 ↓ 27.0 27 1

CTE Scan on cte (cost=24,010.77..24,010.79 rows=1 width=104) (actual time=6,543.612..6,543.906 rows=27 loops=1)

  • Output: cte.bill_id, cte.case_id, cte.r_diags_arr, cte.r_diags_dt_arr, cte.r_diags_dn_arr
  • Buffers: shared hit=1971 read=1747
  • I/O Timings: read=6498.745
2.          

CTE cte

3. 0.312 6,543.879 ↓ 27.0 27 1

GroupAggregate (cost=24,010.73..24,010.77 rows=1 width=31) (actual time=6,543.607..6,543.879 rows=27 loops=1)

  • Output: f.bill_id, f.case_id, array_agg(d.code ORDER BY m.id), array_agg(dt.code ORDER BY m.id), array_agg(CASE WHEN (d_1.id IS NULL) THEN 3 ELSE CASE WHEN (f.open_date <= d_1.reg_in_dt) THEN 2 ELSE 1 END END ORDER BY m.id)
  • Group Key: f.bill_id, f.case_id
  • Buffers: shared hit=1971 read=1747
  • I/O Timings: read=6498.745
4. 0.170 6,543.567 ↓ 39.0 39 1

Sort (cost=24,010.73..24,010.73 rows=1 width=31) (actual time=6,543.557..6,543.567 rows=39 loops=1)

  • Output: f.bill_id, f.case_id, d.code, m.id, dt.code, d_1.id, f.open_date, d_1.reg_in_dt
  • Sort Key: f.bill_id, f.case_id
  • Sort Method: quicksort Memory: 28kB
  • Buffers: shared hit=1971 read=1747
  • I/O Timings: read=6498.745
5. 0.143 6,543.397 ↓ 39.0 39 1

Nested Loop Left Join (cost=5.86..24,010.72 rows=1 width=31) (actual time=5,600.960..6,543.397 rows=39 loops=1)

  • Output: f.bill_id, f.case_id, d.code, m.id, dt.code, d_1.id, f.open_date, d_1.reg_in_dt
  • Buffers: shared hit=1971 read=1747
  • I/O Timings: read=6498.745
6. 0.305 6,065.309 ↓ 39.0 39 1

Nested Loop Left Join (cost=1.41..23,994.41 rows=1 width=35) (actual time=5,576.613..6,065.309 rows=39 loops=1)

  • Output: f.bill_id, f.case_id, f.open_date, f.patient_id, f.close_date, m.id, m.diagnos_id, d.code, dt.code
  • Join Filter: (dt.id = m.disease_type_id)
  • Rows Removed by Join Filter: 273
  • Buffers: shared hit=1801 read=1678
  • I/O Timings: read=6025.042
7. 0.200 6,064.770 ↓ 39.0 39 1

Nested Loop (cost=1.41..23,993.23 rows=1 width=37) (actual time=5,576.598..6,064.770 rows=39 loops=1)

  • Output: f.bill_id, f.case_id, f.open_date, f.patient_id, f.close_date, m.id, m.diagnos_id, m.disease_type_id, d.code
  • Buffers: shared hit=1762 read=1678
  • I/O Timings: read=6025.042
8. 1.660 6,028.768 ↓ 39.0 39 1

Nested Loop (cost=1.13..23,988.03 rows=1 width=32) (actual time=5,576.563..6,028.768 rows=39 loops=1)

  • Output: f.bill_id, f.case_id, f.open_date, f.patient_id, f.close_date, m.id, m.diagnos_id, m.disease_type_id
  • Buffers: shared hit=1646 read=1677
  • I/O Timings: read=5989.870
9. 85.050 85.050 ↑ 1.1 446 1

Index Scan using fin_bill_cases_bill_id_idx on billing.fin_bill_cases f (cost=0.56..18,348.87 rows=498 width=20) (actual time=62.505..85.050 rows=446 loops=1)

  • Output: f.bill_id, f.case_id, f.open_date, f.patient_id, f.close_date
  • Index Cond: (f.bill_id = 2301)
  • Filter: (f.init_goal_code_arr[1] = ANY ('{10,11,12,13,25,28,34,36,37,40,41}'::text[]))
  • Rows Removed by Filter: 3840
  • Buffers: shared read=367
  • I/O Timings: read=73.384
10. 5,942.058 5,942.058 ↓ 0.0 0 446

Index Scan using mc_diagnosis_case_id_idx on public.mc_diagnosis m (cost=0.56..11.31 rows=1 width=16) (actual time=13.317..13.323 rows=0 loops=446)

  • Output: m.id, m.diagnos_id, m.case_id, m.disease_type_id
  • Index Cond: (m.case_id = f.case_id)
  • Filter: (((NOT m.is_main) OR (m.is_main IS NULL)) AND (m.type_id = 2))
  • Rows Removed by Filter: 8
  • Buffers: shared hit=1646 read=1310
  • I/O Timings: read=5916.486
11. 35.802 35.802 ↑ 1.0 1 39

Index Scan using md_diagnosis_pk on public.md_diagnosis d (cost=0.29..5.19 rows=1 width=9) (actual time=0.916..0.918 rows=1 loops=39)

  • Output: d.id, d.code, d.name, d.parent_id, d.is_injury, d.level, d.is_leaf, d.e_code, d.oms, d.stomat, d.aud_who, d.aud_when, d.aud_source, d.aud_who_create, d.aud_when_create, d.aud_source_create, d.from_dt, d.to_dt
  • Index Cond: (d.id = m.diagnos_id)
  • Buffers: shared hit=116 read=1
  • I/O Timings: read=35.172
12. 0.234 0.234 ↑ 1.0 8 39

Seq Scan on public.mc_disease_type dt (cost=0.00..1.08 rows=8 width=6) (actual time=0.003..0.006 rows=8 loops=39)

  • Output: dt.id, dt.code, dt.name, dt.e_code, dt.aud_who, dt.aud_when, dt.aud_source, dt.aud_who_create, dt.aud_when_create, dt.aud_source_create, dt.from_dt, dt.to_dt, dt.billing_code
  • Buffers: shared hit=39
13. 0.234 477.945 ↓ 0.0 0 39

Limit (cost=4.45..16.29 rows=1 width=8) (actual time=12.255..12.255 rows=0 loops=39)

  • Output: d_1.id, d_1.reg_in_dt, (NULL::date)
  • Buffers: shared hit=170 read=69
  • I/O Timings: read=473.703
14. 348.933 477.711 ↓ 0.0 0 39

Bitmap Heap Scan on public.pci_dispensary d_1 (cost=4.45..16.29 rows=1 width=8) (actual time=12.249..12.249 rows=0 loops=39)

  • Output: d_1.id, d_1.reg_in_dt, NULL::date
  • Recheck Cond: (d_1.patient_id = f.patient_id)
  • Filter: ((f.close_date >= COALESCE(d_1.reg_in_dt, '-infinity'::date)) AND (f.close_date <= COALESCE(d_1.reg_out_dt, 'infinity'::date)) AND (d_1.diagnosis_id = m.diagnos_id))
  • Rows Removed by Filter: 3
  • Heap Blocks: exact=119
  • Buffers: shared hit=170 read=69
  • I/O Timings: read=473.703
15. 128.778 128.778 ↑ 1.0 3 39

Bitmap Index Scan on pci_dispensary_patient_id_idx (cost=0.00..4.45 rows=3 width=0) (actual time=3.302..3.302 rows=3 loops=39)

  • Index Cond: (d_1.patient_id = f.patient_id)
  • Buffers: shared hit=100 read=20
  • I/O Timings: read=127.991
Planning time : 5.581 ms
Execution time : 6,544.181 ms