explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NafS : older

Settings
# exclusive inclusive rows x rows loops node
1. 0.990 66.185 ↓ 0.0 0 1

Sort (cost=20,626.68..20,627.00 rows=125 width=1,980) (actual time=66.185..66.185 rows=0 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 25kB
2. 0.021 65.195 ↓ 0.0 0 1

Hash Left Join (cost=1,959.60..20,622.33 rows=125 width=1,980) (actual time=65.195..65.195 rows=0 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (ref.referal_no)::text)
3. 0.001 65.174 ↓ 0.0 0 1

Hash Left Join (cost=1,942.42..20,599.92 rows=125 width=1,958) (actual time=65.174..65.174 rows=0 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (rdoc.doctor_id)::text)
4. 0.001 65.173 ↓ 0.0 0 1

Hash Left Join (cost=1,929.21..20,585.88 rows=125 width=1,442) (actual time=65.173..65.173 rows=0 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
5. 0.001 65.172 ↓ 0.0 0 1

Hash Left Join (cost=1,916.00..20,571.26 rows=125 width=930) (actual time=65.172..65.172 rows=0 loops=1)

  • Hash Cond: ((pr.admitted_dept)::text = (admdep.dept_id)::text)
6. 0.001 65.171 ↓ 0.0 0 1

Hash Left Join (cost=1,914.12..20,567.66 rows=125 width=719) (actual time=65.171..65.171 rows=0 loops=1)

  • Hash Cond: (pr.visit_type = vn.visit_type)
7. 0.001 65.170 ↓ 0.0 0 1

Hash Left Join (cost=1,913.03..20,564.85 rows=125 width=689) (actual time=65.170..65.170 rows=0 loops=1)

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
8. 0.018 65.169 ↓ 0.0 0 1

Nested Loop (cost=1,911.63..20,561.91 rows=125 width=579) (actual time=65.169..65.169 rows=0 loops=1)

  • Join Filter: ((pr.center_id = bhcm.center_id) OR (isr.center_id = bhcm.center_id) OR (prc.center_id = bhcm.center_id))
  • Rows Removed by Join Filter: 3
9. 0.425 0.425 ↑ 1.0 1 1

Seq Scan on hospital_center_master bhcm (cost=0.00..1.35 rows=1 width=222) (actual time=0.415..0.425 rows=1 loops=1)

  • Filter: ((center_name)::text = 'CENTER RAJ'::text)
  • Rows Removed by Filter: 27
10. 0.033 64.726 ↑ 403.7 3 1

Hash Join (cost=1,911.63..20,539.37 rows=1,211 width=373) (actual time=64.125..64.726 rows=3 loops=1)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
11. 0.018 64.448 ↑ 403.7 3 1

Nested Loop (cost=1,907.69..20,518.78 rows=1,211 width=362) (actual time=63.852..64.448 rows=3 loops=1)

12. 0.035 63.207 ↑ 792.0 1 1

Hash Left Join (cost=1,907.27..9,300.21 rows=792 width=318) (actual time=62.885..63.207 rows=1 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
13. 0.026 33.881 ↑ 792.0 1 1

Hash Left Join (cost=1,098.28..8,474.39 rows=792 width=303) (actual time=33.562..33.881 rows=1 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
14. 0.049 33.195 ↑ 792.0 1 1

Hash Left Join (cost=1,087.05..8,460.17 rows=792 width=295) (actual time=32.876..33.195 rows=1 loops=1)

  • Hash Cond: (b.dyna_package_id = dy.dyna_package_id)
15. 4.224 32.130 ↑ 792.0 1 1

Hash Left Join (cost=1,085.94..8,448.16 rows=792 width=81) (actual time=31.811..32.130 rows=1 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
16. 0.013 0.906 ↑ 792.0 1 1

Nested Loop Left Join (cost=21.10..7,167.87 rows=792 width=64) (actual time=0.900..0.906 rows=1 loops=1)

17. 0.245 0.836 ↑ 792.0 1 1

Bitmap Heap Scan on bill b (cost=20.68..2,259.43 rows=792 width=36) (actual time=0.835..0.836 rows=1 loops=1)

  • Recheck Cond: ((date(finalized_date) >= '2020-08-01'::date) AND (date(finalized_date) <= '2020-08-31'::date))
  • Filter: (status <> 'X'::bpchar)
18. 0.591 0.591 ↑ 806.0 1 1

Bitmap Index Scan on bill_finalized_date_idx (cost=0.00..20.49 rows=806 width=0) (actual time=0.591..0.591 rows=1 loops=1)

  • Index Cond: ((date(finalized_date) >= '2020-08-01'::date) AND (date(finalized_date) <= '2020-08-31'::date))
19. 0.057 0.057 ↑ 1.0 1 1

Index Scan using idx_patient_registration_patientid_optype on patient_registration pr (cost=0.42..6.19 rows=1 width=37) (actual time=0.054..0.057 rows=1 loops=1)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
20. 13.860 27.000 ↑ 1.0 27,326 1

Hash (cost=536.26..536.26 rows=27,326 width=26) (actual time=27.000..27.000 rows=27,326 loops=1)

  • Buckets: 2,048 Batches: 2 Memory Usage: 808kB
21. 13.140 13.140 ↑ 1.0 27,326 1

Seq Scan on store_retail_customers prc (cost=0.00..536.26 rows=27,326 width=26) (actual time=0.228..13.140 rows=27,326 loops=1)

22. 0.009 1.016 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=222) (actual time=1.016..1.016 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
23. 1.007 1.007 ↑ 1.0 5 1

Seq Scan on dyna_packages dy (cost=0.00..1.05 rows=5 width=222) (actual time=1.004..1.007 rows=5 loops=1)

24. 0.147 0.660 ↑ 1.0 277 1

Hash (cost=7.77..7.77 rows=277 width=26) (actual time=0.660..0.660 rows=277 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
25. 0.513 0.513 ↑ 1.0 277 1

Seq Scan on incoming_sample_registration isr (cost=0.00..7.77 rows=277 width=26) (actual time=0.008..0.513 rows=277 loops=1)

26. 8.083 29.291 ↓ 1.0 14,046 1

Hash (cost=633.44..633.44 rows=14,044 width=33) (actual time=29.291..29.291 rows=14,046 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 908kB
27. 21.208 21.208 ↓ 1.0 14,046 1

Seq Scan on patient_details pd (cost=0.00..633.44 rows=14,044 width=33) (actual time=0.229..21.208 rows=14,046 loops=1)

28. 1.223 1.223 ↑ 1.0 3 1

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.42..14.13 rows=3 width=54) (actual time=0.955..1.223 rows=3 loops=1)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
29. 0.041 0.245 ↑ 1.0 86 1

Hash (cost=2.86..2.86 rows=86 width=23) (actual time=0.245..0.245 rows=86 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 5kB
30. 0.204 0.204 ↑ 1.0 86 1

Seq Scan on chargehead_constants chc (cost=0.00..2.86 rows=86 width=23) (actual time=0.008..0.204 rows=86 loops=1)

31. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.18..1.18 rows=18 width=156) (never executed)

32. 0.000 0.000 ↓ 0.0 0

Seq Scan on salutation_master smb (cost=0.00..1.18 rows=18 width=156) (never executed)

33. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.04..1.04 rows=4 width=40) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Seq Scan on visit_type_names vn (cost=0.00..1.04 rows=4 width=40) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.39..1.39 rows=39 width=256) (never executed)

36. 0.000 0.000 ↓ 0.0 0

Seq Scan on department admdep (cost=0.00..1.39 rows=39 width=256) (never executed)

37. 0.000 0.000 ↓ 0.0 0

Hash (cost=9.76..9.76 rows=276 width=523) (never executed)

38. 0.000 0.000 ↓ 0.0 0

Seq Scan on doctors dr (cost=0.00..9.76 rows=276 width=523) (never executed)

39. 0.000 0.000 ↓ 0.0 0

Hash (cost=9.76..9.76 rows=276 width=523) (never executed)

40. 0.000 0.000 ↓ 0.0 0

Seq Scan on doctors rdoc (cost=0.00..9.76 rows=276 width=523) (never executed)

41. 0.000 0.000 ↓ 0.0 0

Hash (cost=10.97..10.97 rows=497 width=27) (never executed)

42. 0.000 0.000 ↓ 0.0 0

Seq Scan on referral ref (cost=0.00..10.97 rows=497 width=27) (never executed)

Total runtime : 68.025 ms