explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4FfF

Settings
# exclusive inclusive rows x rows loops node
1. 46.184 1,569.238 ↓ 0.0 0 1

Merge Join (cost=122,790.61..123,249.36 rows=1 width=0) (actual time=1,569.238..1,569.238 rows=0 loops=1)

  • Merge Cond: ((fbc.patient_id = mc.patient_id) AND ((((fbc.region_data -> 'funding_id'::text))::integer) = mc.funding_id))
  • Join Filter: ((mc.id <> fbc.real_case_id) AND (daterange(mc.open_date, COALESCE(mc.close_date, 'infinity'::date), '()'::text) && daterange(fbc.open_date, fbc.close_date, '[]'::text)))
  • Rows Removed by Join Filter: 32
  • Buffers: shared hit=230566
2. 0.031 0.066 ↑ 570.3 7 1

Sort (cost=12,152.93..12,162.91 rows=3,992 width=621) (actual time=0.060..0.066 rows=7 loops=1)

  • Output: fbc.patient_id, fbc.real_case_id, fbc.open_date, fbc.close_date, fbc.region_data, (((fbc.region_data -> 'funding_id'::text))::integer)
  • Sort Key: fbc.patient_id, (((fbc.region_data -> 'funding_id'::text))::integer)
  • Sort Method: quicksort Memory: 32kB
  • Buffers: shared hit=12
3. 0.035 0.035 ↑ 570.3 7 1

Index Scan using new_cases_pkey on billing.fin_bill_cases fbc (cost=0.43..11,914.16 rows=3,992 width=621) (actual time=0.021..0.035 rows=7 loops=1)

  • Output: fbc.patient_id, fbc.real_case_id, fbc.open_date, fbc.close_date, fbc.region_data, ((fbc.region_data -> 'funding_id'::text))::integer
  • Index Cond: (fbc.bill_id = 12554)
  • Buffers: shared hit=7
4. 373.481 1,522.988 ↓ 13.7 364,206 1

Sort (cost=110,637.52..110,704.04 rows=26,605 width=20) (actual time=1,430.283..1,522.988 rows=364,206 loops=1)

  • Output: mc.patient_id, mc.id, mc.open_date, mc.close_date, mc.funding_id
  • Sort Key: mc.patient_id, mc.funding_id
  • Sort Method: quicksort Memory: 51559kB
  • Buffers: shared hit=230554
5. 756.392 1,149.507 ↓ 18.9 502,671 1

Bitmap Heap Scan on public.mc_case mc (cost=24,827.60..108,682.14 rows=26,605 width=20) (actual time=488.168..1,149.507 rows=502,671 loops=1)

  • Output: mc.patient_id, mc.id, mc.open_date, mc.close_date, mc.funding_id
  • Recheck Cond: ((mc.care_regimen_id = 2) AND (mc.case_type_id = 2))
  • Filter: (mc.closing_step_id IS NOT NULL)
  • Rows Removed by Filter: 6793
  • Heap Blocks: exact=226099
  • Buffers: shared hit=230554
6. 68.735 393.115 ↓ 0.0 0 1

BitmapAnd (cost=24,827.60..24,827.60 rows=26,762 width=0) (actual time=393.115..393.115 rows=0 loops=1)

  • Buffers: shared hit=4455
7. 135.642 135.642 ↑ 1.0 522,054 1

Bitmap Index Scan on mc_case_care_regimen_id_id_ix (cost=0.00..10,773.24 rows=525,441 width=0) (actual time=135.642..135.642 rows=522,054 loops=1)

  • Index Cond: (mc.care_regimen_id = 2)
  • Buffers: shared hit=1950
8. 188.738 188.738 ↑ 1.0 677,926 1

Bitmap Index Scan on mc_case_case_type_id_clinic_id_idx (cost=0.00..14,040.80 rows=682,716 width=0) (actual time=188.738..188.738 rows=677,926 loops=1)

  • Index Cond: (mc.case_type_id = 2)
  • Buffers: shared hit=2505
Planning time : 3.412 ms
Execution time : 1,577.838 ms