explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2K3c : Optimization for: Optimization for: Optimization for: plan #M9gRc; plan #eJzX; plan #xCcAU

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.006 13,725.474 ↓ 0.0 0 1

Sort (cost=2,055,274.03..2,055,277.70 rows=1,467 width=267) (actual time=13,725.474..13,725.474 rows=0 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 25kB
2. 0.001 13,725.468 ↓ 0.0 0 1

Nested Loop Left Join (cost=21,640.84..2,055,196.88 rows=1,467 width=267) (actual time=13,725.468..13,725.468 rows=0 loops=1)

3. 0.000 13,725.467 ↓ 0.0 0 1

Nested Loop Left Join (cost=21,640.55..2,054,683.37 rows=1,467 width=257) (actual time=13,725.467..13,725.467 rows=0 loops=1)

4. 0.001 13,725.467 ↓ 0.0 0 1

Nested Loop Left Join (cost=21,640.26..2,054,224.83 rows=1,467 width=239) (actual time=13,725.467..13,725.467 rows=0 loops=1)

5. 0.000 13,725.466 ↓ 0.0 0 1

Hash Left Join (cost=21,639.98..2,053,766.30 rows=1,467 width=229) (actual time=13,725.466..13,725.466 rows=0 loops=1)

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

Hash Left Join (cost=21,634.20..2,053,740.35 rows=1,467 width=219) (actual time=13,725.466..13,725.466 rows=0 loops=1)

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

Hash Left Join (cost=21,633.11..2,053,719.09 rows=1,467 width=217) (actual time=13,725.466..13,725.466 rows=0 loops=1)

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
8. 132.556 13,725.465 ↓ 0.0 0 1

Nested Loop (cost=21,630.85..2,053,696.66 rows=1,467 width=218) (actual time=13,725.465..13,725.465 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: 391,320
9. 0.025 0.025 ↑ 1.0 1 1

Index Scan using center_name_idx on hospital_center_master bhcm (cost=0.27..2.49 rows=1 width=22) (actual time=0.023..0.025 rows=1 loops=1)

  • Index Cond: ((center_name)::text = 'Sugar Chattisgarh'::text)
10. 298.606 13,592.884 ↓ 1.7 391,320 1

Hash Join (cost=21,630.58..2,049,568.33 rows=235,762 width=212) (actual time=1,945.427..13,592.884 rows=391,320 loops=1)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
11. 523.660 13,294.187 ↓ 1.7 391,320 1

Nested Loop (cost=21,626.15..2,046,322.17 rows=235,762 width=199) (actual time=1,945.327..13,294.187 rows=391,320 loops=1)

12. 116.952 8,721.031 ↓ 1.2 92,034 1

Nested Loop Left Join (cost=21,625.58..254,252.60 rows=76,584 width=151) (actual time=1,944.987..8,721.031 rows=92,034 loops=1)

13. 86.180 6,119.161 ↓ 1.2 92,034 1

Nested Loop Left Join (cost=21,625.15..216,987.36 rows=76,584 width=143) (actual time=1,944.913..6,119.161 rows=92,034 loops=1)

14. 52.703 3,732.131 ↓ 1.2 92,034 1

Hash Left Join (cost=21,624.72..78,752.52 rows=76,584 width=112) (actual time=1,944.874..3,732.131 rows=92,034 loops=1)

  • Hash Cond: (b.dyna_package_id = dy.dyna_package_id)
15. 621.273 3,677.154 ↓ 1.2 92,034 1

Merge Left Join (cost=21,493.87..77,281.45 rows=76,584 width=82) (actual time=1,942.588..3,677.154 rows=92,034 loops=1)

  • Merge Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
16. 70.659 2,070.204 ↓ 1.2 92,034 1

Merge Left Join (cost=21,493.44..21,876.41 rows=76,584 width=60) (actual time=1,942.572..2,070.204 rows=92,034 loops=1)

  • Merge Cond: ((b.visit_id)::text = (prc.customer_id)::text)
17. 1,421.294 1,999.510 ↓ 1.2 92,034 1

Sort (cost=21,492.24..21,683.70 rows=76,584 width=46) (actual time=1,942.535..1,999.510 rows=92,034 loops=1)

  • Sort Key: b.visit_id
  • Sort Method: external sort Disk: 5,720kB
18. 578.216 578.216 ↓ 1.2 92,034 1

Index Scan using bill_finalized_date_idx on bill b (cost=0.44..15,279.46 rows=76,584 width=46) (actual time=0.041..578.216 rows=92,034 loops=1)

  • Index Cond: ((date(finalized_date) >= '2020-08-01'::date) AND (date(finalized_date) <= '2020-08-31'::date))
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 921
19. 0.025 0.035 ↑ 1.0 8 1

Sort (cost=1.20..1.22 rows=8 width=23) (actual time=0.032..0.035 rows=8 loops=1)

  • Sort Key: prc.customer_id
  • Sort Method: quicksort Memory: 25kB
20. 0.010 0.010 ↑ 1.0 8 1

Seq Scan on store_retail_customers prc (cost=0.00..1.08 rows=8 width=23) (actual time=0.006..0.010 rows=8 loops=1)

21. 985.677 985.677 ↑ 1.0 1,283,350 1

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.43..51,874.66 rows=1,283,350 width=31) (actual time=0.012..985.677 rows=1,283,350 loops=1)

22. 1.259 2.274 ↑ 1.0 3,949 1

Hash (cost=81.49..81.49 rows=3,949 width=38) (actual time=2.274..2.274 rows=3,949 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 278kB
23. 1.015 1.015 ↑ 1.0 3,949 1

Seq Scan on dyna_packages dy (cost=0.00..81.49 rows=3,949 width=38) (actual time=0.009..1.015 rows=3,949 loops=1)

24. 2,300.850 2,300.850 ↑ 1.0 1 92,034

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..1.80 rows=1 width=56) (actual time=0.024..0.025 rows=1 loops=92,034)

25. 2,484.918 2,484.918 ↑ 1.0 1 92,034

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.48 rows=1 width=38) (actual time=0.026..0.027 rows=1 loops=92,034)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
26. 4,049.496 4,049.496 ↑ 37.2 4 92,034

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..21.91 rows=149 width=61) (actual time=0.031..0.044 rows=4 loops=92,034)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 0
27. 0.042 0.091 ↑ 1.0 108 1

Hash (cost=3.08..3.08 rows=108 width=25) (actual time=0.091..0.091 rows=108 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 7kB
28. 0.049 0.049 ↑ 1.0 108 1

Seq Scan on chargehead_constants chc (cost=0.00..3.08 rows=108 width=25) (actual time=0.004..0.049 rows=108 loops=1)

29. 0.000 0.000 ↓ 0.0 0

Hash (cost=1.56..1.56 rows=56 width=16) (never executed)

30. 0.000 0.000 ↓ 0.0 0

Seq Scan on salutation_master smb (cost=0.00..1.56 rows=56 width=16) (never executed)

31. 0.000 0.000 ↓ 0.0 0

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

32. 0.000 0.000 ↓ 0.0 0

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

33. 0.000 0.000 ↓ 0.0 0

Hash (cost=3.68..3.68 rows=168 width=26) (never executed)

34. 0.000 0.000 ↓ 0.0 0

Seq Scan on department admdep (cost=0.00..3.68 rows=168 width=26) (never executed)

35. 0.000 0.000 ↓ 0.0 0

Index Scan using doctors_pkey on doctors dr (cost=0.29..0.30 rows=1 width=26) (never executed)

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

Index Scan using doctors_pkey on doctors rdoc (cost=0.29..0.30 rows=1 width=26) (never executed)

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

Index Scan using referral_pkey on referral ref (cost=0.29..0.31 rows=1 width=21) (never executed)

  • Index Cond: ((pr.reference_docto_id)::text = (referal_no)::text)
Total runtime : 13,727.618 ms