explain.depesz.com

PostgreSQL's explain analyze made readable

Result: VNiD

Settings
# exclusive inclusive rows x rows loops node
1. 2.478 222.430 ↓ 1.9 403 1

Hash Left Join (cost=6,309.75..66,109.88 rows=216 width=854) (actual time=197.962..222.430 rows=403 loops=1)

  • Hash Cond: ((pr.patient_id)::text = (dfd.patient_id)::text)
2. 0.105 211.793 ↓ 1.9 403 1

Hash Left Join (cost=4,800.86..64,481.33 rows=216 width=653) (actual time=189.727..211.793 rows=403 loops=1)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stm.tpa_id)::text)
3. 0.124 211.630 ↓ 1.9 403 1

Hash Left Join (cost=4,788.00..64,467.90 rows=216 width=631) (actual time=189.666..211.630 rows=403 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (tm.tpa_id)::text)
4. 0.104 211.429 ↓ 1.9 403 1

Hash Left Join (cost=4,775.14..64,454.46 rows=216 width=609) (actual time=189.585..211.429 rows=403 loops=1)

  • Hash Cond: ((pr.secondary_insurance_co)::text = (sicm.insurance_co_id)::text)
5. 0.128 211.249 ↓ 1.9 403 1

Hash Left Join (cost=4,762.58..64,441.33 rows=216 width=586) (actual time=189.506..211.249 rows=403 loops=1)

  • Hash Cond: ((pr.primary_insurance_co)::text = (icm.insurance_co_id)::text)
6. 0.297 211.033 ↓ 1.9 403 1

Nested Loop Left Join (cost=4,750.02..64,428.19 rows=216 width=563) (actual time=189.415..211.033 rows=403 loops=1)

7. 0.180 209.930 ↓ 1.9 403 1

Hash Left Join (cost=4,749.74..64,244.47 rows=216 width=549) (actual time=189.409..209.930 rows=403 loops=1)

  • Hash Cond: ((pr.dept_name)::text = (dept.dept_id)::text)
8. 0.163 209.730 ↓ 1.9 403 1

Hash Join (cost=4,747.66..64,241.78 rows=216 width=339) (actual time=189.374..209.730 rows=403 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
9. 0.305 209.556 ↓ 1.9 403 1

Nested Loop (cost=4,746.25..64,239.67 rows=216 width=221) (actual time=189.352..209.556 rows=403 loops=1)

10. 0.073 207.236 ↓ 1.9 403 1

Nested Loop Left Join (cost=4,745.83..62,456.09 rows=216 width=160) (actual time=189.341..207.236 rows=403 loops=1)

11. 191.704 205.148 ↓ 1.9 403 1

Bitmap Heap Scan on patient_registration pr (cost=4,745.40..56,313.16 rows=216 width=120) (actual time=189.320..205.148 rows=403 loops=1)

  • Recheck Cond: (center_id = 9)
  • Filter: ((reg_date >= '2018-02-07'::date) AND ((status)::text <> 'X'::text) AND ((codification_status = 'R'::bpchar) OR (codification_status = 'P'::bpchar)))
  • Rows Removed by Filter: 256040
  • Heap Blocks: exact=38012
12. 13.444 13.444 ↑ 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=13.444..13.444 rows=256,517 loops=1)

  • Index Cond: (center_id = 9)
13. 2.015 2.015 ↓ 0.0 0 403

Index Scan using mrd_diagnosis_visit_index on mrd_diagnosis md (cost=0.43..28.42 rows=2 width=56) (actual time=0.005..0.005 rows=0 loops=403)

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
  • Filter: (diag_type = 'P'::bpchar)
  • Rows Removed by Filter: 0
14. 2.015 2.015 ↑ 1.0 1 403

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..8.26 rows=1 width=76) (actual time=0.005..0.005 rows=1 loops=403)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
15. 0.006 0.011 ↑ 1.0 18 1

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

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

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

17. 0.009 0.020 ↑ 1.0 48 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 11kB
18. 0.011 0.011 ↑ 1.0 48 1

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

19. 0.806 0.806 ↑ 1.0 1 403

Index Scan using doctors_pkey on doctors dr (cost=0.28..0.85 rows=1 width=30) (actual time=0.002..0.002 rows=1 loops=403)

  • Index Cond: ((pr.doctor)::text = (doctor_id)::text)
20. 0.046 0.088 ↑ 1.0 336 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
21. 0.042 0.042 ↑ 1.0 336 1

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

22. 0.045 0.076 ↑ 1.0 336 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 30kB
23. 0.031 0.031 ↑ 1.0 336 1

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

24. 0.034 0.077 ↑ 1.2 255 1

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

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

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

26. 0.034 0.058 ↑ 1.2 255 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 25kB
27. 0.024 0.024 ↑ 1.2 255 1

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

28. 3.170 8.159 ↑ 1.0 23,284 1

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

  • Buckets: 32768 Batches: 1 Memory Usage: 2368kB
29. 4.989 4.989 ↑ 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.005..4.989 rows=23,284 loops=1)

Planning time : 5.691 ms
Execution time : 222.694 ms