explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VArp

Settings
# exclusive inclusive rows x rows loops node
1. 127.836 1,396.161 ↑ 1.8 29,247 1

Hash Left Join (cost=95,467.95..256,269.64 rows=52,339 width=854) (actual time=566.208..1,396.161 rows=29,247 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (dfd.patient_id)::text)
2. 5.410 1,259.807 ↑ 1.8 29,239 1

Hash Left Join (cost=93,959.06..225,765.85 rows=52,339 width=653) (actual time=557.610..1,259.807 rows=29,239 loops=1)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stm.tpa_id)::text)
3. 7.281 1,254.342 ↑ 1.8 29,239 1

Hash Left Join (cost=93,946.20..225,615.54 rows=52,339 width=631) (actual time=557.552..1,254.342 rows=29,239 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (tm.tpa_id)::text)
4. 6.117 1,246.972 ↑ 1.8 29,239 1

Hash Left Join (cost=93,933.34..225,463.58 rows=52,339 width=609) (actual time=557.461..1,246.972 rows=29,239 loops=1)

  • Hash Cond: ((pr.secondary_insurance_co)::text = (sicm.insurance_co_id)::text)
5. 6.601 1,240.780 ↑ 1.8 29,239 1

Hash Left Join (cost=93,920.78..225,313.57 rows=52,339 width=586) (actual time=557.381..1,240.780 rows=29,239 loops=1)

  • Hash Cond: ((pr.primary_insurance_co)::text = (icm.insurance_co_id)::text)
6. 7.536 1,234.088 ↑ 1.8 29,239 1

Hash Left Join (cost=93,908.22..225,162.07 rows=52,339 width=563) (actual time=557.285..1,234.088 rows=29,239 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
7. 7.184 1,226.267 ↑ 1.8 29,239 1

Hash Left Join (cost=93,860.02..224,975.93 rows=52,339 width=549) (actual time=556.996..1,226.267 rows=29,239 loops=1)

  • Hash Cond: ((pr.dept_name)::text = (dept.dept_id)::text)
8. 7.835 1,219.065 ↑ 1.8 29,239 1

Hash Join (cost=93,857.94..224,824.40 rows=52,339 width=339) (actual time=556.968..1,219.065 rows=29,239 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
9. 66.961 1,210.973 ↑ 1.8 29,239 1

Hash Join (cost=93,856.54..224,653.25 rows=52,339 width=221) (actual time=556.707..1,210.973 rows=29,239 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
10. 205.452 830.894 ↑ 1.8 29,239 1

Hash Right Join (cost=56,618.15..177,456.47 rows=52,339 width=160) (actual time=239.488..830.894 rows=29,239 loops=1)

  • Hash Cond: ((md.visit_id)::text = (pr.patient_id)::text)
11. 386.038 386.038 ↑ 1.0 949,996 1

Seq Scan on mrd_diagnosis md (cost=0.00..98,842.45 rows=951,187 width=56) (actual time=0.010..386.038 rows=949,996 loops=1)

  • Filter: (diag_type = 'P'::bpchar)
  • Rows Removed by Filter: 1995148
12. 13.165 239.404 ↑ 1.8 29,210 1

Hash (cost=55,042.91..55,042.91 rows=52,339 width=120) (actual time=239.404..239.404 rows=29,210 loops=1)

  • Buckets: 32768 Batches: 4 Memory Usage: 1190kB
13. 216.514 226.239 ↑ 1.8 29,210 1

Bitmap Heap Scan on patient_registration pr (cost=4,758.43..55,042.91 rows=52,339 width=120) (actual time=14.124..226.239 rows=29,210 loops=1)

  • Recheck Cond: (center_id = 9)
  • Filter: ((codification_status = 'R'::bpchar) OR (codification_status = 'P'::bpchar))
  • Rows Removed by Filter: 227233
  • Heap Blocks: exact=38012
14. 9.725 9.725 ↑ 1.0 256,517 1

Bitmap Index Scan on patient_registarion_center_idx (cost=0.00..4,745.35 rows=256,656 width=0) (actual time=9.725..9.725 rows=256,517 loops=1)

  • Index Cond: (center_id = 9)
15. 152.779 313.118 ↑ 1.0 588,096 1

Hash (cost=22,415.84..22,415.84 rows=588,284 width=76) (actual time=313.118..313.118 rows=588,096 loops=1)

  • Buckets: 65536 Batches: 32 Memory Usage: 2486kB
16. 160.339 160.339 ↑ 1.0 588,096 1

Seq Scan on patient_details pd (cost=0.00..22,415.84 rows=588,284 width=76) (actual time=0.006..160.339 rows=588,096 loops=1)

17. 0.004 0.257 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=156) (actual time=0.257..0.257 rows=18 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
18. 0.253 0.253 ↑ 1.0 18 1

Seq Scan on salutation_master sm (cost=0.00..1.18 rows=18 width=156) (actual time=0.251..0.253 rows=18 loops=1)

19. 0.008 0.018 ↑ 1.0 48 1

Hash (cost=1.48..1.48 rows=48 width=256) (actual time=0.018..0.018 rows=48 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
20. 0.010 0.010 ↑ 1.0 48 1

Seq Scan on department dept (cost=0.00..1.48 rows=48 width=256) (actual time=0.006..0.010 rows=48 loops=1)

21. 0.122 0.285 ↑ 1.0 990 1

Hash (cost=35.31..35.31 rows=1,031 width=30) (actual time=0.285..0.285 rows=990 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 77kB
22. 0.163 0.163 ↑ 1.0 990 1

Seq Scan on doctors dr (cost=0.00..35.31 rows=1,031 width=30) (actual time=0.006..0.163 rows=990 loops=1)

23. 0.045 0.091 ↑ 1.0 336 1

Hash (cost=8.36..8.36 rows=336 width=31) (actual time=0.091..0.091 rows=336 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
24. 0.046 0.046 ↑ 1.0 336 1

Seq Scan on insurance_company_master icm (cost=0.00..8.36 rows=336 width=31) (actual time=0.006..0.046 rows=336 loops=1)

25. 0.043 0.075 ↑ 1.0 336 1

Hash (cost=8.36..8.36 rows=336 width=31) (actual time=0.075..0.075 rows=336 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
26. 0.032 0.032 ↑ 1.0 336 1

Seq Scan on insurance_company_master sicm (cost=0.00..8.36 rows=336 width=31) (actual time=0.004..0.032 rows=336 loops=1)

27. 0.033 0.089 ↑ 1.2 255 1

Hash (cost=9.05..9.05 rows=305 width=32) (actual time=0.089..0.089 rows=255 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
28. 0.056 0.056 ↑ 1.2 255 1

Seq Scan on tpa_master tm (cost=0.00..9.05 rows=305 width=32) (actual time=0.005..0.056 rows=255 loops=1)

29. 0.032 0.055 ↑ 1.2 255 1

Hash (cost=9.05..9.05 rows=305 width=32) (actual time=0.055..0.055 rows=255 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
30. 0.023 0.023 ↑ 1.2 255 1

Seq Scan on tpa_master stm (cost=0.00..9.05 rows=305 width=32) (actual time=0.002..0.023 rows=255 loops=1)

31. 3.103 8.518 ↑ 1.0 23,284 1

Hash (cost=1,217.84..1,217.84 rows=23,284 width=65) (actual time=8.518..8.518 rows=23,284 loops=1)

  • Buckets: 32768 Batches: 1 Memory Usage: 2368kB
32. 5.415 5.415 ↑ 1.0 23,284 1

Seq Scan on discharge_format_detail dfd (cost=0.00..1,217.84 rows=23,284 width=65) (actual time=0.006..5.415 rows=23,284 loops=1)

Planning time : 4.517 ms
Execution time : 1,397.184 ms