explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JX7T

Settings
# exclusive inclusive rows x rows loops node
1. 16,172.081 27,430.213 ↑ 3,259,524.8 6 1

Hash Join (cost=2,302,742.23..6,704,794.42 rows=19,557,149 width=16) (actual time=52.414..27,430.213 rows=6 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date
  • Hash Cond: (s.id = m.id)
  • Buffers: shared hit=11886487 dirtied=1, temp read=47611 written=166554
2.          

CTE prm

3. 0.017 0.774 ↑ 1.0 1 1

Index Scan using fin_bill_main_pkey on public.fin_bill_main (cost=0.54..8.56 rows=1 width=12) (actual time=0.773..0.774 rows=1 loops=1)

  • Output: fin_bill_main.from_date, fin_bill_main.to_date, fin_bill_main.clinic_id
  • Index Cond: (fin_bill_main.id = $0)
  • Buffers: shared hit=35
4.          

Initplan (forIndex Scan)

5. 0.757 0.757 ↑ 1.0 1 1

Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.757..0.757 rows=1 loops=1)

  • Output: fin_bill__get_main_bill(31)
  • Buffers: shared hit=32
6.          

CTE x

7. 0.013 0.899 ↑ 5,710.8 4 1

Nested Loop (cost=1,207.95..121,890.91 rows=22,843 width=16) (actual time=0.854..0.899 rows=4 loops=1)

  • Output: c.id, c.closing_step_id, c.patient_id, c.close_date
  • Buffers: shared hit=46
8. 0.778 0.778 ↑ 1.0 1 1

CTE Scan on prm (cost=0.00..0.02 rows=1 width=12) (actual time=0.777..0.778 rows=1 loops=1)

  • Output: prm.bd, prm.ed, prm.cln
  • Buffers: shared hit=35
9. 0.077 0.108 ↑ 5,710.8 4 1

Bitmap Heap Scan on public.mc_case c (cost=1,207.95..121,662.46 rows=22,843 width=20) (actual time=0.066..0.108 rows=4 loops=1)

  • Output: c.id, c.closing_step_id, c.patient_id, c.close_date, c.clinic_id
  • Recheck Cond: ((c.clinic_id = prm.cln) AND (c.close_date >= prm.bd) AND (c.close_date <= prm.ed) AND (c.closing_step_id IS NOT NULL))
  • Filter: ((c.care_regimen_id <> ALL ('{3,5}'::integer[])) AND (c.init_goal_id = 1) AND (c.case_type_id = 1))
  • Rows Removed by Filter: 5
  • Heap Blocks: exact=7
  • Buffers: shared hit=11
10. 0.031 0.031 ↑ 4,139.3 9 1

Bitmap Index Scan on mc_case_clinic_id_close_date_idx (cost=0.00..1,202.24 rows=37,254 width=0) (actual time=0.031..0.031 rows=9 loops=1)

  • Index Cond: ((c.clinic_id = prm.cln) AND (c.close_date >= prm.bd) AND (c.close_date <= prm.ed))
  • Buffers: shared hit=4
11. 11,257.020 11,257.020 ↓ 1.0 64,970,217 1

Index Only Scan using sr_srv_rendered_pk on public.sr_srv_rendered s (cost=0.56..1,798,224.64 rows=64,952,968 width=4) (actual time=0.052..11,257.020 rows=64,970,217 loops=1)

  • Output: s.id
  • Heap Fetches: 1428283
  • Buffers: shared hit=11886402 dirtied=1
12. 0.141 1.112 ↑ 3,259,828.2 6 1

Hash (cost=1,821,751.08..1,821,751.08 rows=19,558,969 width=20) (actual time=1.112..1.112 rows=6 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.id
  • Buckets: 4194304 Batches: 8 Memory Usage: 32769kB
  • Buffers: shared hit=68
13. 0.012 0.971 ↑ 3,259,828.2 6 1

Nested Loop (cost=0.56..1,821,751.08 rows=19,558,969 width=20) (actual time=0.889..0.971 rows=6 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.id
  • Buffers: shared hit=68
14. 0.907 0.907 ↑ 5,710.8 4 1

CTE Scan on x (cost=0.00..456.86 rows=22,843 width=16) (actual time=0.859..0.907 rows=4 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date
  • Buffers: shared hit=46
15. 0.052 0.052 ↑ 428.0 2 4

Index Scan using md_srv_rendered_case_id_idx on public.md_srv_rendered m (cost=0.56..71.17 rows=856 width=8) (actual time=0.011..0.013 rows=2 loops=4)

  • Output: m.id, m.is_urgent, m.is_use_cryogenic, m.is_use_endoscopic, m.is_use_laser, m.anesthesia_type_id, m.step_id, m.complication_type_id, m.diagnosis_id, m.case_id, m.referral_id, m.result_category_id, m.patient_prescription_id, m.health_group_id, m.vmp_type_id, m.vmp_method_id, m.anatomic_zone_id, m.aud_who, m.aud_when, m.aud_source, m.aud_who_create, m.aud_when_create, m.aud_source_create, m.prescription_id, m.by_standard, m.complicated_value, m.vmp_profile_id
  • Index Cond: (m.case_id = x.id)
  • Buffers: shared hit=22
Planning time : 7.651 ms
Execution time : 27,435.997 ms