explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 74wf : new

Settings
# exclusive inclusive rows x rows loops node
1. 0.016 28.378 ↓ 0.0 0 1

Sort (cost=9,864.37..9,864.65 rows=110 width=1,980) (actual time=28.378..28.378 rows=0 loops=1)

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

Hash Left Join (cost=1,124.77..9,860.64 rows=110 width=1,980) (actual time=28.362..28.362 rows=0 loops=1)

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

Nested Loop Left Join (cost=1,107.59..9,838.86 rows=110 width=1,958) (actual time=28.362..28.362 rows=0 loops=1)

4. 0.001 28.362 ↓ 0.0 0 1

Nested Loop Left Join (cost=1,107.44..9,756.73 rows=110 width=1,442) (actual time=28.362..28.362 rows=0 loops=1)

5. 0.001 28.361 ↓ 0.0 0 1

Hash Left Join (cost=1,107.29..9,674.59 rows=110 width=930) (actual time=28.361..28.361 rows=0 loops=1)

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

Hash Left Join (cost=1,105.42..9,671.20 rows=110 width=719) (actual time=28.360..28.360 rows=0 loops=1)

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

Hash Left Join (cost=1,104.33..9,668.60 rows=110 width=689) (actual time=28.360..28.360 rows=0 loops=1)

  • Hash Cond: (b.dyna_package_id = dy.dyna_package_id)
8. 0.001 28.360 ↓ 0.0 0 1

Hash Left Join (cost=1,103.21..9,665.98 rows=110 width=475) (actual time=28.360..28.360 rows=0 loops=1)

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

Hash Join (cost=1,101.81..9,663.22 rows=110 width=365) (actual time=28.359..28.359 rows=0 loops=1)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
10. 0.000 28.241 ↓ 0.0 0 1

Nested Loop (cost=1,097.87..9,657.77 rows=110 width=354) (actual time=28.241..28.241 rows=0 loops=1)

11. 0.001 28.241 ↓ 0.0 0 1

Nested Loop (cost=1,097.45..8,637.90 rows=72 width=310) (actual time=28.241..28.241 rows=0 loops=1)

12. 0.023 28.240 ↓ 0.0 0 1

Hash Left Join (cost=1,097.17..8,469.04 rows=72 width=295) (actual time=28.240..28.240 rows=0 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
  • Filter: ((pr.center_id = bhcm.center_id) OR (isr.center_id = bhcm.center_id) OR (prc.center_id = bhcm.center_id))
  • Rows Removed by Filter: 1
13. 0.009 27.971 ↑ 696.0 1 1

Nested Loop (cost=1,085.94..8,455.18 rows=696 width=303) (actual time=26.890..27.971 rows=1 loops=1)

14. 0.022 0.022 ↑ 1.0 1 1

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

  • Filter: ((center_name)::text = 'CENTER RAJ'::text)
  • Rows Removed by Filter: 27
15. 4.841 27.940 ↑ 696.0 1 1

Hash Left Join (cost=1,085.94..8,446.87 rows=696 width=81) (actual time=26.873..27.940 rows=1 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
16. 0.006 0.071 ↑ 696.0 1 1

Nested Loop (cost=21.10..7,169.85 rows=696 width=64) (actual time=0.067..0.071 rows=1 loops=1)

17. 0.007 0.023 ↑ 792.0 1 1

Bitmap Heap Scan on bill b (cost=20.68..2,259.43 rows=792 width=36) (actual time=0.022..0.023 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.016 0.016 ↑ 806.0 1 1

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

  • Index Cond: ((date(finalized_date) >= '2020-08-01'::date) AND (date(finalized_date) <= '2020-08-31'::date))
19. 0.042 0.042 ↑ 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.039..0.042 rows=1 loops=1)

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

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

  • Buckets: 2,048 Batches: 2 Memory Usage: 808kB
21. 9.378 9.378 ↑ 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.010..9.378 rows=27,326 loops=1)

22. 0.119 0.246 ↑ 1.0 277 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 17kB
23. 0.127 0.127 ↑ 1.0 277 1

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

24. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_details_pkey on patient_details pd (cost=0.29..2.34 rows=1 width=33) (never executed)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
25. 0.000 0.000 ↓ 0.0 0

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.42..14.13 rows=3 width=54) (never executed)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
26. 0.056 0.100 ↑ 1.0 86 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 5kB
27. 0.044 0.044 ↑ 1.0 86 1

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

28. 0.000 0.000 ↓ 0.0 0

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

29. 0.000 0.000 ↓ 0.0 0

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

30. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.05..1.05 rows=5 width=222) (never executed)

31. 0.000 0.000 ↓ 0.0 0

Seq Scan on dyna_packages dy (cost=0.00..1.05 rows=5 width=222) (never executed)

32. 0.000 0.000 ↓ 0.0 0

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

33. 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)

34. 0.000 0.000 ↓ 0.0 0

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

35. 0.000 0.000 ↓ 0.0 0

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

36. 0.000 0.000 ↓ 0.0 0

Index Scan using doctors_pkey on doctors dr (cost=0.15..0.74 rows=1 width=523) (never executed)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
37. 0.000 0.000 ↓ 0.0 0

Index Scan using doctors_pkey on doctors rdoc (cost=0.15..0.74 rows=1 width=523) (never executed)

  • Index Cond: ((pr.reference_docto_id)::text = (doctor_id)::text)
38. 0.000 0.000 ↓ 0.0 0

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

39. 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 : 29.401 ms