explain.depesz.com

PostgreSQL's explain analyze made readable

Result: bQKr

Settings
# exclusive inclusive rows x rows loops node
1. 0.011 389.593 ↓ 0.0 0 1

Sort (cost=21,780.49..21,780.80 rows=125 width=1,980) (actual time=389.593..389.593 rows=0 loops=1)

  • Sort Method: quicksort Memory: 25kB
2. 0.012 389.582 ↓ 0.0 0 1

Merge Left Join (cost=21,768.82..21,776.14 rows=125 width=1,980) (actual time=389.582..389.582 rows=0 loops=1)

  • Merge Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
3. 0.008 389.570 ↓ 0.0 0 1

Sort (cost=21,747.87..21,748.18 rows=125 width=1,468) (actual time=389.570..389.570 rows=0 loops=1)

  • Sort Key: pr.doctor
  • Sort Method: quicksort Memory: 25kB
4. 0.003 389.562 ↓ 0.0 0 1

Merge Right Join (cost=21,741.44..21,743.51 rows=125 width=1,468) (actual time=389.562..389.562 rows=0 loops=1)

  • Merge Cond: ((admdep.dept_id)::text = (pr.admitted_dept)::text)
5. 0.266 3.118 ↑ 39.0 1 1

Sort (cost=2.42..2.52 rows=39 width=256) (actual time=3.118..3.118 rows=1 loops=1)

  • Sort Key: admdep.dept_id
  • Sort Method: quicksort Memory: 27kB
6. 2.852 2.852 ↑ 1.0 39 1

Seq Scan on department admdep (cost=0.00..1.39 rows=39 width=256) (actual time=2.843..2.852 rows=39 loops=1)

7. 0.016 386.441 ↓ 0.0 0 1

Sort (cost=21,739.02..21,739.34 rows=125 width=1,257) (actual time=386.441..386.441 rows=0 loops=1)

  • Sort Key: pr.admitted_dept
  • Sort Method: quicksort Memory: 25kB
8. 0.003 386.425 ↓ 0.0 0 1

Merge Right Join (cost=21,732.77..21,734.67 rows=125 width=1,257) (actual time=386.425..386.425 rows=0 loops=1)

  • Merge Cond: (vn.visit_type = pr.visit_type)
9. 0.137 1.677 ↑ 4.0 1 1

Sort (cost=1.08..1.09 rows=4 width=40) (actual time=1.677..1.677 rows=1 loops=1)

  • Sort Key: vn.visit_type
  • Sort Method: quicksort Memory: 25kB
10. 1.540 1.540 ↑ 1.0 4 1

Seq Scan on visit_type_names vn (cost=0.00..1.04 rows=4 width=40) (actual time=1.538..1.540 rows=4 loops=1)

11. 0.022 384.745 ↓ 0.0 0 1

Sort (cost=21,731.69..21,732.01 rows=125 width=1,227) (actual time=384.745..384.745 rows=0 loops=1)

  • Sort Key: pr.visit_type
  • Sort Method: quicksort Memory: 25kB
12. 0.002 384.723 ↓ 0.0 0 1

Merge Right Join (cost=21,725.56..21,727.34 rows=125 width=1,227) (actual time=384.723..384.723 rows=0 loops=1)

  • Merge Cond: ((smb.salutation_id)::text = (pd.salutation)::text)
13. 0.219 0.876 ↑ 18.0 1 1

Sort (cost=1.56..1.60 rows=18 width=156) (actual time=0.876..0.876 rows=1 loops=1)

  • Sort Key: smb.salutation_id
  • Sort Method: quicksort Memory: 25kB
14. 0.657 0.657 ↑ 1.0 18 1

Seq Scan on salutation_master smb (cost=0.00..1.18 rows=18 width=156) (actual time=0.649..0.657 rows=18 loops=1)

15. 0.022 383.845 ↓ 0.0 0 1

Sort (cost=21,724.00..21,724.31 rows=125 width=1,117) (actual time=383.845..383.845 rows=0 loops=1)

  • Sort Key: pd.salutation
  • Sort Method: quicksort Memory: 25kB
16. 0.000 383.823 ↓ 0.0 0 1

Merge Left Join (cost=21,714.09..21,719.65 rows=125 width=1,117) (actual time=383.823..383.823 rows=0 loops=1)

  • Merge Cond: ((pr.reference_docto_id)::text = (ref.referal_no)::text)
17. 0.000 383.823 ↓ 0.0 0 1

Merge Left Join (cost=21,680.87..21,683.24 rows=125 width=1,095) (actual time=383.823..383.823 rows=0 loops=1)

  • Merge Cond: ((pr.reference_docto_id)::text = (rdoc.doctor_id)::text)
18. 0.013 383.823 ↓ 0.0 0 1

Sort (cost=21,659.92..21,660.23 rows=125 width=579) (actual time=383.823..383.823 rows=0 loops=1)

  • Sort Key: pr.reference_docto_id
  • Sort Method: quicksort Memory: 25kB
19. 0.014 383.810 ↓ 0.0 0 1

Nested Loop (cost=8,946.31..21,655.56 rows=125 width=579) (actual time=383.810..383.810 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
20. 0.073 0.073 ↑ 1.0 1 1

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

  • Filter: ((center_name)::text = 'CENTER RAJ'::text)
  • Rows Removed by Filter: 27
21. 0.013 383.723 ↑ 403.7 3 1

Nested Loop (cost=8,946.31..21,633.02 rows=1,211 width=373) (actual time=383.670..383.723 rows=3 loops=1)

22. 0.017 383.677 ↑ 403.7 3 1

Nested Loop (cost=8,946.17..21,424.04 rows=1,211 width=362) (actual time=383.649..383.677 rows=3 loops=1)

23. 0.004 383.597 ↑ 792.0 1 1

Merge Left Join (cost=8,945.75..10,205.47 rows=792 width=318) (actual time=383.596..383.597 rows=1 loops=1)

  • Merge Cond: ((b.visit_id)::text = (prc.customer_id)::text)
24. 0.007 383.582 ↑ 792.0 1 1

Nested Loop Left Join (cost=8,945.46..9,011.28 rows=792 width=310) (actual time=383.581..383.582 rows=1 loops=1)

  • Join Filter: (dy.dyna_package_id = b.dyna_package_id)
  • Rows Removed by Join Filter: 5
25. 0.259 383.539 ↑ 792.0 1 1

Merge Left Join (cost=8,945.46..8,950.81 rows=792 width=96) (actual time=383.539..383.539 rows=1 loops=1)

  • Merge Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
26. 0.013 381.930 ↑ 792.0 1 1

Sort (cost=8,926.44..8,928.42 rows=792 width=79) (actual time=381.930..381.930 rows=1 loops=1)

  • Sort Key: b.visit_id
  • Sort Method: quicksort Memory: 25kB
27. 14.153 381.917 ↑ 792.0 1 1

Merge Left Join (cost=8,806.91..8,888.31 rows=792 width=79) (actual time=381.915..381.917 rows=1 loops=1)

  • Merge Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
28. 0.011 0.120 ↑ 792.0 1 1

Sort (cost=7,206.00..7,207.98 rows=792 width=64) (actual time=0.120..0.120 rows=1 loops=1)

  • Sort Key: pr.mr_no
  • Sort Method: quicksort Memory: 25kB
29. 0.016 0.109 ↑ 792.0 1 1

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

30. 0.024 0.067 ↑ 792.0 1 1

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

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

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

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
33. 330.931 367.644 ↓ 1.0 14,045 1

Sort (cost=1,600.91..1,636.02 rows=14,044 width=33) (actual time=363.970..367.644 rows=14,045 loops=1)

  • Sort Key: pd.mr_no
  • Sort Method: external sort Disk: 664kB
34. 36.713 36.713 ↓ 1.0 14,046 1

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

35. 1.201 1.350 ↑ 1.0 277 1

Sort (cost=19.01..19.70 rows=277 width=26) (actual time=1.336..1.350 rows=277 loops=1)

  • Sort Key: isr.incoming_visit_id
  • Sort Method: quicksort Memory: 46kB
36. 0.149 0.149 ↑ 1.0 277 1

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

37. 0.013 0.036 ↑ 1.0 5 1

Materialize (cost=0.00..1.07 rows=5 width=222) (actual time=0.034..0.036 rows=5 loops=1)

38. 0.023 0.023 ↑ 1.0 5 1

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

39. 0.011 0.011 ↑ 27,326.0 1 1

Index Scan using pharmacy_retail_customers_pkey on store_retail_customers prc (cost=0.29..1,122.35 rows=27,326 width=26) (actual time=0.011..0.011 rows=1 loops=1)

40. 0.063 0.063 ↑ 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.040..0.063 rows=3 loops=1)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
41. 0.033 0.033 ↑ 1.0 1 3

Index Scan using chargehead_constants_pkey on chargehead_constants chc (cost=0.14..0.16 rows=1 width=23) (actual time=0.010..0.011 rows=1 loops=3)

  • Index Cond: ((chargehead_id)::text = (bc.charge_head)::text)
42. 0.000 0.000 ↓ 0.0 0

Sort (cost=20.95..21.64 rows=276 width=523) (never executed)

  • Sort Key: rdoc.doctor_id
43. 0.000 0.000 ↓ 0.0 0

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

44. 0.000 0.000 ↓ 0.0 0

Sort (cost=33.23..34.47 rows=497 width=27) (never executed)

  • Sort Key: ref.referal_no
45. 0.000 0.000 ↓ 0.0 0

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

46. 0.000 0.000 ↓ 0.0 0

Sort (cost=20.95..21.64 rows=276 width=523) (never executed)

  • Sort Key: dr.doctor_id
47. 0.000 0.000 ↓ 0.0 0

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

Total runtime : 393.738 ms