explain.depesz.com

PostgreSQL's explain analyze made readable

Result: U6fN

Settings
# exclusive inclusive rows x rows loops node
1. 0.037 37,447.197 ↑ 21,132,431.3 9 1

Merge Join (cost=9,862,429.37..13,491,675.46 rows=190,191,882 width=16) (actual time=37,447.173..37,447.197 rows=9 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date
  • Merge Cond: (ms.case_id = m.case_id)
  • Buffers: shared hit=1257160 dirtied=10, temp read=55528 written=194251
2.          

CTE prm

3. 0.021 2.079 ↑ 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=2.079..2.079 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=60
4.          

Initplan (forIndex Scan)

5. 2.058 2.058 ↑ 1.0 1 1

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

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

CTE x

7. 0.011 2.174 ↑ 5,710.8 4 1

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

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

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

  • Output: prm.bd, prm.ed, prm.cln
  • Buffers: shared hit=60
9. 0.043 0.081 ↑ 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.081 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.038 0.038 ↑ 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.038..0.038 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. 0.181 0.181 ↑ 192,446.2 148 1

Index Only Scan using idx_mc_step on public.mc_step ms (cost=0.44..849,188.27 rows=28,482,036 width=4) (actual time=0.063..0.181 rows=148 loops=1)

  • Output: ms.case_id
  • Heap Fetches: 1
  • Buffers: shared hit=13
12. 0.035 37,446.979 ↑ 2,167,485.6 9 1

Materialize (cost=9,740,414.94..9,837,951.79 rows=19,507,370 width=20) (actual time=37,446.976..37,446.979 rows=9 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.case_id
  • Buffers: shared hit=1257147 dirtied=10, temp read=55528 written=194251
13. 0.111 37,446.944 ↑ 3,251,228.3 6 1

Sort (cost=9,740,414.94..9,789,183.37 rows=19,507,370 width=20) (actual time=37,446.943..37,446.944 rows=6 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.case_id
  • Sort Key: x.id
  • Sort Method: quicksort Memory: 25kB
  • Buffers: shared hit=1257147 dirtied=10, temp read=55528 written=194251
14. 4.816 37,446.833 ↑ 3,251,228.3 6 1

Hash Join (cost=2,206,105.87..6,978,261.22 rows=19,507,370 width=20) (actual time=3,069.264..37,446.833 rows=6 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.case_id
  • Hash Cond: (s.service_id = v.id)
  • Buffers: shared hit=1257142 dirtied=10, temp read=55528 written=194251
15. 20,298.518 37,199.068 ↑ 3,259,485.7 6 1

Hash Join (cost=2,180,835.11..6,684,578.34 rows=19,556,914 width=24) (actual time=2,821.675..37,199.068 rows=6 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.case_id, s.service_id
  • Hash Cond: (s.id = m.id)
  • Buffers: shared hit=1250517 dirtied=10, temp read=55528 written=194251
16. 16,898.229 16,898.229 ↓ 1.0 64,969,990 1

Seq Scan on public.sr_srv_rendered s (cost=0.00..1,899,945.89 rows=64,952,189 width=8) (actual time=0.007..16,898.229 rows=64,969,990 loops=1)

  • Output: s.id, s.service_id
  • Buffers: shared hit=1250424 dirtied=10
17. 0.097 2.321 ↑ 3,259,801.8 6 1

Hash (cost=1,821,746.97..1,821,746.97 rows=19,558,811 width=24) (actual time=2.321..2.321 rows=6 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.case_id, m.id
  • Buckets: 4194304 Batches: 8 Memory Usage: 32769kB
  • Buffers: shared hit=93
18. 0.009 2.224 ↑ 3,259,801.8 6 1

Nested Loop (cost=0.56..1,821,746.97 rows=19,558,811 width=24) (actual time=2.182..2.224 rows=6 loops=1)

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date, m.case_id, m.id
  • Buffers: shared hit=93
19. 2.179 2.179 ↑ 5,710.8 4 1

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

  • Output: x.id, x.closing_step_id, x.patient_id, x.close_date
  • Buffers: shared hit=71
20. 0.036 0.036 ↑ 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.008..0.009 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
21. 144.780 242.949 ↓ 1.0 426,040 1

Hash (cost=19,955.12..19,955.12 rows=425,251 width=4) (actual time=242.949..242.949 rows=426,040 loops=1)

  • Output: v.id
  • Buckets: 524288 Batches: 1 Memory Usage: 19074kB
  • Buffers: shared hit=6622
22. 98.169 98.169 ↓ 1.0 426,040 1

Index Only Scan using sr_service_pk on public.sr_service v (cost=0.42..19,955.12 rows=425,251 width=4) (actual time=0.047..98.169 rows=426,040 loops=1)

  • Output: v.id
  • Heap Fetches: 10601
  • Buffers: shared hit=6622
Planning time : 12.758 ms
Execution time : 37,462.765 ms