explain.depesz.com

PostgreSQL's explain analyze made readable

Result: QDGA

Settings
# exclusive inclusive rows x rows loops node
1. 11,742.128 36,851.838 ↓ 1.5 391,340 1

Sort (cost=2,181,899.59..2,182,539.62 rows=256,014 width=246) (actual time=36,592.610..36,851.838 rows=391,340 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: external merge Disk: 67,872kB
2. 1,219.398 25,109.710 ↓ 1.5 391,340 1

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

  • Hash Cond: ((pr.reference_docto_id)::text = (ref.referal_no)::text)
3. 370.781 23,878.162 ↓ 1.5 391,340 1

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

  • Hash Cond: ((pr.reference_docto_id)::text = (rdoc.doctor_id)::text)
4. 371.239 23,498.449 ↓ 1.5 391,340 1

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

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
5. 351.201 23,119.531 ↓ 1.5 391,340 1

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

  • Hash Cond: ((pr.admitted_dept)::text = (admdep.dept_id)::text)
6. 371.551 22,768.205 ↓ 1.5 391,340 1

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

  • Hash Cond: (pr.visit_type = vn.visit_type)
7. 328.339 22,396.646 ↓ 1.5 391,340 1

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

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
8. 371.710 22,068.019 ↓ 1.5 391,340 1

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

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
9. 292.823 21,696.217 ↓ 1.5 391,340 1

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

10. 216.130 13,579.824 ↓ 1.1 92,042 1

Nested Loop Left Join (cost=23,468.51..267,944.58 rows=83,313 width=139) (actual time=2,135.804..13,579.824 rows=92,042 loops=1)

11. 101.557 9,405.888 ↓ 1.1 92,042 1

Nested Loop Left Join (cost=23,468.08..227,405.45 rows=83,313 width=131) (actual time=2,135.775..9,405.888 rows=92,042 loops=1)

12. 71.973 5,346.525 ↓ 1.1 92,042 1

Hash Left Join (cost=23,467.65..80,775.47 rows=83,313 width=104) (actual time=2,135.694..5,346.525 rows=92,042 loops=1)

  • Hash Cond: (b.dyna_package_id = dy.dyna_package_id)
13. 721.022 5,272.444 ↓ 1.1 92,042 1

Merge Left Join (cost=23,336.80..79,186.64 rows=83,313 width=74) (actual time=2,133.577..5,272.444 rows=92,042 loops=1)

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

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

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

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

  • Sort Key: b.visit_id
  • Sort Method: external merge Disk: 5,360kB
16. 792.903 792.903 ↓ 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.226..792.903 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.018 0.027 ↑ 1.0 8 1

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

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

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

19. 2,185.950 2,185.950 ↑ 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.011..2,185.950 rows=1,283,350 loops=1)

20. 2.108 2.108 ↑ 1.0 3,949 1

Hash (cost=81.49..81.49 rows=3,949 width=38) (actual time=2.108..2.108 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.005..1.026 rows=394:
21. 3,957.806 3,957.806 ↑ 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.042..0.043 rows=1 loops=92,042)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
22. 3,957.806 3,957.806 ↑ 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.042..0.043 rows=1 loops=92,042)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
23. 7,823.570 7,823.570 ↑ 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.059..0.085 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.050 0.092 ↑ 1.0 108 1

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

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

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

26. 0.021 0.288 ↑ 1.0 56 1

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

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

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

28. 0.003 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.005 0.005 ↑ 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.005 rows=4 loops=1)

30. 0.068 0.125 ↑ 1.0 168 1

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

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

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

32. 3.243 7.679 ↑ 1.0 8,235 1

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

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

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

34. 3.569 8.932 ↑ 1.0 8,235 1

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

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

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

36. 7.041 12.150 ↑ 1.0 16,434 1

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

  • Buckets: 2,048 Batches: 1 Memory Usage: 861kB
37. 5.109 5.109 ↑ 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.109 rows=16,434 loops=1)

Total runtime : 36,920.921 ms