explain.depesz.com

PostgreSQL's explain analyze made readable

Result: PYPd

Settings
# exclusive inclusive rows x rows loops node
1. 18.684 12,557.615 ↓ 1,573.0 1,573 1

GroupAggregate (cost=37,627.70..37,627.74 rows=1 width=31) (actual time=12,538.141..12,557.615 rows=1,573 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=56841 read=11486
  • I/O Timings: read=12197.703
2. 11.605 12,538.931 ↓ 4,583.0 4,583 1

Sort (cost=37,627.70..37,627.71 rows=1 width=31) (actual time=12,538.080..12,538.931 rows=4,583 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: 551kB
  • Buffers: shared hit=56841 read=11486
  • I/O Timings: read=12197.703
3. 9.683 12,527.326 ↓ 4,583.0 4,583 1

Nested Loop Left Join (cost=5.72..37,627.69 rows=1 width=31) (actual time=33.491..12,527.326 rows=4,583 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=56841 read=11486
  • I/O Timings: read=12197.703
4. 22.968 9,630.353 ↓ 4,583.0 4,583 1

Nested Loop Left Join (cost=1.28..37,611.38 rows=1 width=35) (actual time=33.243..9,630.353 rows=4,583 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: 32083
  • Buffers: shared hit=37048 read=9491
  • I/O Timings: read=9408.456
5. 9.957 9,593.636 ↓ 4,583.0 4,583 1

Nested Loop (cost=1.28..37,610.20 rows=1 width=37) (actual time=33.227..9,593.636 rows=4,583 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=32465 read=9491
  • I/O Timings: read=9408.456
6. 11.111 9,542.432 ↓ 4,583.0 4,583 1

Nested Loop (cost=0.99..37,605.01 rows=1 width=32) (actual time=33.209..9,542.432 rows=4,583 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=18717 read=9489
  • I/O Timings: read=9397.613
7. 243.677 243.677 ↓ 1.7 4,964 1

Index Scan using billing_fin_bill_cases_where_some_init_goal_code_arr1_bill_idx on billing.fin_bill_cases f (cost=0.43..5,770.14 rows=2,846 width=20) (actual time=8.732..243.677 rows=4,964 loops=1)

  • Output: f.bill_id, f.case_id, f.open_date, f.patient_id, f.close_date
  • Index Cond: (f.bill_id = 5805)
  • Buffers: shared hit=2 read=2858
  • I/O Timings: read=216.667
8. 9,287.644 9,287.644 ↑ 1.0 1 4,964

Index Scan using mc_diagnosis_case_id_idx on public.mc_diagnosis m (cost=0.56..11.18 rows=1 width=16) (actual time=1.817..1.871 rows=1 loops=4,964)

  • 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: 2
  • Buffers: shared hit=18715 read=6631
  • I/O Timings: read=9180.946
9. 41.247 41.247 ↑ 1.0 1 4,583

Index Scan using md_diagnosis_pk on public.md_diagnosis d (cost=0.29..5.19 rows=1 width=9) (actual time=0.009..0.009 rows=1 loops=4,583)

  • 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=13748 read=2
  • I/O Timings: read=10.843
10. 13.749 13.749 ↑ 1.0 8 4,583

Seq Scan on public.mc_disease_type dt (cost=0.00..1.08 rows=8 width=6) (actual time=0.002..0.003 rows=8 loops=4,583)

  • 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=4583
11. 9.166 2,887.290 ↓ 0.0 0 4,583

Limit (cost=4.45..16.29 rows=1 width=8) (actual time=0.630..0.630 rows=0 loops=4,583)

  • Output: d_1.id, d_1.reg_in_dt, (NULL::date)
  • Buffers: shared hit=19793 read=1995
  • I/O Timings: read=2789.247
12. 2,140.261 2,878.124 ↓ 0.0 0 4,583

Bitmap Heap Scan on public.pci_dispensary d_1 (cost=4.45..16.29 rows=1 width=8) (actual time=0.628..0.628 rows=0 loops=4,583)

  • 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: 2
  • Heap Blocks: exact=7994
  • Buffers: shared hit=19793 read=1995
  • I/O Timings: read=2789.247
13. 737.863 737.863 ↑ 1.5 2 4,583

Bitmap Index Scan on pci_dispensary_patient_id_idx (cost=0.00..4.45 rows=3 width=0) (actual time=0.161..0.161 rows=2 loops=4,583)

  • Index Cond: (d_1.patient_id = f.patient_id)
  • Buffers: shared hit=13143 read=651
  • I/O Timings: read=705.298
Planning time : 2.493 ms
Execution time : 12,558.018 ms