explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Oa0L : Optimization for: Optimization for: plan #M9gRc; plan #eJzX

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 30.323 10,122.610 ↓ 3.7 5,350 1

Sort (cost=2,053,826.47..2,053,830.12 rows=1,461 width=267) (actual time=10,121.880..10,122.610 rows=5,350 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 1,626kB
2. 14.226 10,092.287 ↓ 3.7 5,350 1

Nested Loop Left Join (cost=21,600.69..2,053,749.67 rows=1,461 width=267) (actual time=8,907.844..10,092.287 rows=5,350 loops=1)

3. 5.659 10,051.311 ↓ 3.7 5,350 1

Nested Loop Left Join (cost=21,600.40..2,053,238.26 rows=1,461 width=257) (actual time=8,907.818..10,051.311 rows=5,350 loops=1)

4. 4.220 10,029.602 ↓ 3.7 5,350 1

Nested Loop Left Join (cost=21,600.12..2,052,781.60 rows=1,461 width=239) (actual time=8,907.811..10,029.602 rows=5,350 loops=1)

5. 3.488 9,993.282 ↓ 3.7 5,350 1

Hash Left Join (cost=21,599.83..2,052,324.94 rows=1,461 width=229) (actual time=8,907.798..9,993.282 rows=5,350 loops=1)

  • Hash Cond: ((pr.admitted_dept)::text = (admdep.dept_id)::text)
6. 3.263 9,989.708 ↓ 3.7 5,350 1

Hash Left Join (cost=21,594.05..2,052,299.07 rows=1,461 width=219) (actual time=8,907.706..9,989.708 rows=5,350 loops=1)

  • Hash Cond: (pr.visit_type = vn.visit_type)
7. 3.188 9,986.440 ↓ 3.7 5,350 1

Hash Left Join (cost=21,592.96..2,052,277.90 rows=1,461 width=217) (actual time=8,907.694..9,986.440 rows=5,350 loops=1)

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
8. 110.112 9,983.220 ↓ 3.7 5,350 1

Nested Loop (cost=21,590.70..2,052,255.55 rows=1,461 width=218) (actual time=8,907.647..9,983.220 rows=5,350 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: 385,970
9. 0.024 0.024 ↑ 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.024 rows=1 loops=1)

  • Index Cond: ((center_name)::text = 'Spectra Chembur'::text)
10. 258.915 9,873.084 ↓ 1.7 391,320 1

Hash Join (cost=21,590.43..2,048,143.86 rows=234,811 width=212) (actual time=1,159.146..9,873.084 rows=391,320 loops=1)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
11. 506.317 9,614.086 ↓ 1.7 391,320 1

Nested Loop (cost=21,586.00..2,044,910.78 rows=234,811 width=199) (actual time=1,159.055..9,614.086 rows=391,320 loops=1)

12. 155.779 6,070.647 ↓ 1.2 92,034 1

Nested Loop Left Join (cost=21,585.44..253,904.37 rows=76,413 width=151) (actual time=1,158.898..6,070.647 rows=92,034 loops=1)

13. 123.057 4,258.256 ↓ 1.2 92,034 1

Nested Loop Left Join (cost=21,585.01..216,722.38 rows=76,413 width=143) (actual time=1,158.872..4,258.256 rows=92,034 loops=1)

14. 47.654 2,570.621 ↓ 1.2 92,034 1

Hash Left Join (cost=21,584.57..78,708.10 rows=76,413 width=112) (actual time=1,158.765..2,570.621 rows=92,034 loops=1)

  • Hash Cond: (b.dyna_package_id = dy.dyna_package_id)
15. 524.508 2,521.038 ↓ 1.2 92,034 1

Merge Left Join (cost=21,453.72..77,240.02 rows=76,413 width=82) (actual time=1,156.825..2,521.038 rows=92,034 loops=1)

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

Merge Left Join (cost=21,453.29..21,835.41 rows=76,413 width=60) (actual time=1,156.815..1,270.865 rows=92,034 loops=1)

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

Sort (cost=21,452.09..21,643.13 rows=76,413 width=46) (actual time=1,156.785..1,204.969 rows=92,034 loops=1)

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

Index Scan using bill_finalized_date_idx on bill b (cost=0.44..15,254.41 rows=76,413 width=46) (actual time=0.062..275.389 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.018 0.026 ↑ 1.0 8 1

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

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

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

21. 725.665 725.665 ↑ 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.008..725.665 rows=1,283,350 loops=1)

22. 1.072 1.929 ↑ 1.0 3,949 1

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

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

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

24. 1,564.578 1,564.578 ↑ 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.017..0.017 rows=1 loops=92,034)

25. 1,656.612 1,656.612 ↑ 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.018..0.018 rows=1 loops=92,034)

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

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..21.95 rows=149 width=61) (actual time=0.024..0.033 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.031 0.083 ↑ 1.0 108 1

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

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

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

29. 0.014 0.032 ↑ 1.0 56 1

Hash (cost=1.56..1.56 rows=56 width=16) (actual time=0.032..0.032 rows=56 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 3kB
30. 0.018 0.018 ↑ 1.0 56 1

Seq Scan on salutation_master smb (cost=0.00..1.56 rows=56 width=16) (actual time=0.003..0.018 rows=56 loops=1)

31. 0.002 0.005 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=6) (actual time=0.005..0.005 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
32. 0.003 0.003 ↑ 1.0 4 1

Seq Scan on visit_type_names vn (cost=0.00..1.04 rows=4 width=6) (actual time=0.002..0.003 rows=4 loops=1)

33. 0.047 0.086 ↑ 1.0 168 1

Hash (cost=3.68..3.68 rows=168 width=26) (actual time=0.086..0.086 rows=168 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
34. 0.039 0.039 ↑ 1.0 168 1

Seq Scan on department admdep (cost=0.00..3.68 rows=168 width=26) (actual time=0.002..0.039 rows=168 loops=1)

35. 32.100 32.100 ↑ 1.0 1 5,350

Index Scan using doctors_pkey on doctors dr (cost=0.29..0.30 rows=1 width=26) (actual time=0.006..0.006 rows=1 loops=5,350)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
36. 16.050 16.050 ↓ 0.0 0 5,350

Index Scan using doctors_pkey on doctors rdoc (cost=0.29..0.30 rows=1 width=26) (actual time=0.003..0.003 rows=0 loops=5,350)

  • Index Cond: ((pr.reference_docto_id)::text = (doctor_id)::text)
37. 26.750 26.750 ↑ 1.0 1 5,350

Index Scan using referral_pkey on referral ref (cost=0.29..0.31 rows=1 width=21) (actual time=0.005..0.005 rows=1 loops=5,350)

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