explain.depesz.com

PostgreSQL's explain analyze made readable

Result: jskp

Settings
# exclusive inclusive rows x rows loops node
1. 1,184.199 26,589.991 ↓ 1.5 391,340 1

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

  • Hash Cond: ((pr.reference_docto_id)::text = (ref.referal_no)::text)
2. 366.782 25,393.661 ↓ 1.5 391,340 1

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

  • Hash Cond: ((pr.reference_docto_id)::text = (rdoc.doctor_id)::text)
3. 372.318 25,020.209 ↓ 1.5 391,340 1

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

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
4. 352.508 24,640.675 ↓ 1.5 391,340 1

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

  • Hash Cond: ((pr.admitted_dept)::text = (admdep.dept_id)::text)
5. 360.681 24,288.048 ↓ 1.5 391,340 1

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

  • Hash Cond: (pr.visit_type = vn.visit_type)
6. 357.532 23,927.360 ↓ 1.5 391,340 1

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

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
7. 364.496 23,569.782 ↓ 1.5 391,340 1

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

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
8. 272.838 23,205.195 ↓ 1.5 391,340 1

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

9. 241.077 12,899.779 ↓ 1.1 92,042 1

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

10. 179.207 8,884.980 ↓ 1.1 92,042 1

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

11. 71.055 5,024.093 ↓ 1.1 92,042 1

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

  • Hash Cond: (b.dyna_package_id = dy.dyna_package_id)
12. 679.081 4,950.288 ↓ 1.1 92,042 1

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

  • Merge Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
13. 84.995 1,916.602 ↓ 1.1 92,042 1

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

  • Merge Cond: ((b.visit_id)::text = (prc.customer_id)::text)
14. 1,417.885 1,831.579 ↓ 1.1 92,042 1

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

  • Sort Key: b.visit_id
  • Sort Method: external merge Disk: 5,360kB
15. 413.694 413.694 ↓ 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.057..413.694 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
16. 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
17. 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.005..0.007 rows=8 loops=1)

18. 2,354.605 2,354.605 ↑ 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..2,354.605 rows=1,283,350 loops=1)

19. 1.451 2.750 ↑ 1.0 3,949 1

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

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

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

21. 3,681.680 3,681.680 ↑ 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.040..0.040 rows=1 loops=92,042)

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

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
23. 10,032.578 10,032.578 ↑ 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.064..0.109 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.047 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
25. 0.044 0.044 ↑ 1.0 108 1

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

26. 0.025 0.046 ↑ 1.0 56 1

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

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

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

28. 0.003 0.007 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=6) (actual time=0.007..0.007 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.066 0.119 ↑ 1.0 168 1

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

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

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

32. 3.250 7.216 ↑ 1.0 8,235 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 468kB
33. 3.966 3.966 ↑ 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.966 rows=8,235 loops=1)

34. 3.395 6.670 ↑ 1.0 8,235 1

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

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

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

36. 6.852 12.131 ↑ 1.0 16,434 1

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

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

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

Total runtime : 26,665.505 ms