explain.depesz.com

PostgreSQL's explain analyze made readable

Result: h8Bk

Settings
# exclusive inclusive rows x rows loops node
1. 8,722.602 27,323.980 ↓ 1.5 391,340 1

Sort (cost=2,181,899.59..2,182,539.62 rows=256,014 width=246) (actual time=27,054.235..27,323.980 rows=391,340 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: external merge Disk: 67,872kB
2. 1,129.332 18,601.378 ↓ 1.5 391,340 1

Hash Left Join (cost=25,161.98..2,141,474.97 rows=256,014 width=246) (actual time=1,916.743..18,601.378 rows=391,340 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (ref.referal_no)::text)
3. 344.184 17,459.406 ↓ 1.5 391,340 1

Hash Left Join (cost=24,587.21..2,126,827.89 rows=256,014 width=236) (actual time=1,904.057..17,459.406 rows=391,340 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (rdoc.doctor_id)::text)
4. 359.034 17,107.787 ↓ 1.5 391,340 1

Hash Left Join (cost=24,034.92..2,120,523.74 rows=256,014 width=218) (actual time=1,896.609..17,107.787 rows=391,340 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
5. 336.275 16,741.613 ↓ 1.5 391,340 1

Hash Left Join (cost=23,482.64..2,115,609.82 rows=256,014 width=208) (actual time=1,889.460..16,741.613 rows=391,340 loops=1)

  • Hash Cond: ((pr.admitted_dept)::text = (admdep.dept_id)::text)
6. 331.105 16,405.175 ↓ 1.5 391,340 1

Hash Left Join (cost=23,476.86..2,112,083.85 rows=256,014 width=198) (actual time=1,889.290..16,405.175 rows=391,340 loops=1)

  • Hash Cond: (pr.visit_type = vn.visit_type)
7. 316.107 16,074.062 ↓ 1.5 391,340 1

Hash Left Join (cost=23,475.77..2,108,562.57 rows=256,014 width=196) (actual time=1,889.276..16,074.062 rows=391,340 loops=1)

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
8. 359.931 15,757.910 ↓ 1.5 391,340 1

Hash Join (cost=23,473.51..2,105,040.12 rows=256,014 width=197) (actual time=1,889.219..15,757.910 rows=391,340 loops=1)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
9. 264.859 15,397.910 ↓ 1.5 391,340 1

Nested Loop (cost=23,469.08..2,101,515.49 rows=256,014 width=184) (actual time=1,889.142..15,397.910 rows=391,340 loops=1)

10. 203.620 9,610.531 ↓ 1.1 92,042 1

Nested Loop Left Join (cost=23,468.51..267,944.58 rows=83,313 width=139) (actual time=1,888.909..9,610.531 rows=92,042 loops=1)

11. 169.679 6,645.651 ↓ 1.1 92,042 1

Nested Loop Left Join (cost=23,468.08..227,405.45 rows=83,313 width=131) (actual time=1,888.868..6,645.651 rows=92,042 loops=1)

12. 68.612 3,622.670 ↓ 1.1 92,042 1

Hash Left Join (cost=23,467.65..80,775.47 rows=83,313 width=104) (actual time=1,888.804..3,622.670 rows=92,042 loops=1)

  • Hash Cond: (b.dyna_package_id = dy.dyna_package_id)
13. 550.085 3,551.268 ↓ 1.1 92,042 1

Merge Left Join (cost=23,336.80..79,186.64 rows=83,313 width=74) (actual time=1,886.004..3,551.268 rows=92,042 loops=1)

  • Merge Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
14. 81.923 2,103.753 ↓ 1.1 92,042 1

Merge Left Join (cost=23,336.37..23,752.98 rows=83,313 width=56) (actual time=1,885.988..2,103.753 rows=92,042 loops=1)

  • Merge Cond: ((b.visit_id)::text = (prc.customer_id)::text)
15. 1,433.886 2,021.802 ↓ 1.1 92,042 1

Sort (cost=23,335.17..23,543.45 rows=83,313 width=46) (actual time=1,885.957..2,021.802 rows=92,042 loops=1)

  • Sort Key: b.visit_id
  • Sort Method: external merge Disk: 5,360kB
16. 587.916 587.916 ↓ 1.1 92,042 1

Index Scan using bill_finalized_date_idx on bill b (cost=0.44..16,525.89 rows=83,313 width=46) (actual time=0.062..587.916 rows=92,042 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: 912
17. 0.021 0.028 ↑ 1.0 8 1

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

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

Seq Scan on store_retail_customers prc (cost=0.00..1.08 rows=8 width=19) (actual time=0.004..0.007 rows=8 loops=1)

19. 897.430 897.430 ↑ 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=27) (actual time=0.012..897.430 rows=1,283,350 loops=1)

20. 2.790 2.790 ↑ 1.0 3,949 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 278kB
  • -> Seq Scan on dyna_packages dy (cost=0.00..81.49 rows=3,949 width=38) (actual time=0.006..1.286 rows=394:
21. 2,853.302 2,853.302 ↑ 1.0 1 92,042

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.43..1.75 rows=1 width=52) (actual time=0.031..0.031 rows=1 loops=92,042)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
22. 2,761.260 2,761.260 ↑ 1.0 1 92,042

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

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
23. 5,522.520 5,522.520 ↑ 37.2 4 92,042

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..20.52 rows=149 width=58) (actual time=0.040..0.060 rows=4 loops=92,042)

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

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 7kB
25. 0.045 0.045 ↑ 1.0 108 1

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

26. 0.030 0.045 ↑ 1.0 56 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 3kB
27. 0.015 0.015 ↑ 1.0 56 1

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

28. 0.004 0.008 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
29. 0.004 0.004 ↑ 1.0 4 1

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

30. 0.062 0.163 ↑ 1.0 168 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 10kB
31. 0.101 0.101 ↑ 1.0 168 1

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

32. 3.403 7.140 ↑ 1.0 8,235 1

Hash (cost=449.35..449.35 rows=8,235 width=26) (actual time=7.140..7.140 rows=8,235 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 468kB
33. 3.737 3.737 ↑ 1.0 8,235 1

Seq Scan on doctors dr (cost=0.00..449.35 rows=8,235 width=26) (actual time=0.005..3.737 rows=8,235 loops=1)

34. 3.544 7.435 ↑ 1.0 8,235 1

Hash (cost=449.35..449.35 rows=8,235 width=26) (actual time=7.435..7.435 rows=8,235 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 468kB
35. 3.891 3.891 ↑ 1.0 8,235 1

Seq Scan on doctors rdoc (cost=0.00..449.35 rows=8,235 width=26) (actual time=0.005..3.891 rows=8,235 loops=1)

36. 7.207 12.640 ↑ 1.0 16,434 1

Hash (cost=369.34..369.34 rows=16,434 width=21) (actual time=12.640..12.640 rows=16,434 loops=1)

  • Buckets: 2,048 Batches: 1 Memory Usage: 861kB
37. 5.433 5.433 ↑ 1.0 16,434 1

Seq Scan on referral ref (cost=0.00..369.34 rows=16,434 width=21) (actual time=0.004..5.433 rows=16,434 loops=1)

Total runtime : 27,398.805 ms