explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZyLh

Settings
# exclusive inclusive rows x rows loops node
1. 201.938 254,055.177 ↓ 5.6 23,622 1

Sort (cost=7,785,630.06..7,785,640.60 rows=4,217 width=734) (actual time=254,052.779..254,055.177 rows=23,622 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 13227kB
2. 28.391 253,853.239 ↓ 5.6 23,622 1

Hash Left Join (cost=3,286,277.40..7,785,376.15 rows=4,217 width=734) (actual time=246,679.704..253,853.239 rows=23,622 loops=1)

  • Hash Cond: (vsd.plan_id = pipm.plan_id)
3. 10.228 253,738.934 ↓ 5.6 23,622 1

Hash Left Join (cost=3,283,913.56..7,782,853.65 rows=4,217 width=573) (actual time=246,593.538..253,738.934 rows=23,622 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
4. 8.502 253,727.574 ↓ 5.6 23,622 1

Hash Left Join (cost=3,283,866.43..7,782,795.38 rows=4,217 width=559) (actual time=246,592.382..253,727.574 rows=23,622 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (ref.referal_no)::text)
5. 7.939 253,638.049 ↓ 5.6 23,622 1

Hash Left Join (cost=3,278,677.07..7,777,594.95 rows=4,217 width=544) (actual time=246,510.557..253,638.049 rows=23,622 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (rdoc.doctor_id)::text)
6. 10.614 253,629.546 ↓ 5.6 23,622 1

Hash Left Join (cost=3,278,493.93..7,777,400.74 rows=4,217 width=522) (actual time=246,509.975..253,629.546 rows=23,622 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
7. 9.986 253,618.180 ↓ 5.6 23,622 1

Hash Left Join (cost=3,278,310.79..7,777,206.50 rows=4,217 width=508) (actual time=246,509.212..253,618.180 rows=23,622 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
8. 15.715 253,608.180 ↓ 5.6 23,622 1

Nested Loop Left Join (cost=3,278,309.67..7,777,184.94 rows=4,217 width=492) (actual time=246,509.185..253,608.180 rows=23,622 loops=1)

9. 10.682 253,309.001 ↓ 5.6 23,622 1

Hash Left Join (cost=3,278,309.12..7,762,997.17 rows=4,217 width=504) (actual time=246,507.850..253,309.001 rows=23,622 loops=1)

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
10. 19.053 253,298.301 ↓ 5.6 23,622 1

Nested Loop Left Join (cost=3,278,307.64..7,762,982.39 rows=4,217 width=508) (actual time=246,507.813..253,298.301 rows=23,622 loops=1)

11. 23.316 252,145.392 ↓ 5.6 23,622 1

Hash Left Join (cost=3,278,307.21..7,749,484.23 rows=4,217 width=475) (actual time=246,506.720..252,145.392 rows=23,622 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
  • Filter: ((pr.center_id = bhcm.center_id) OR (isr.center_id = bhcm.center_id) OR (prc.center_id = bhcm.center_id))
  • Rows Removed by Filter: 32659
12. 12.321 251,917.501 ↑ 3.1 56,281 1

Nested Loop (cost=3,263,732.62..7,734,456.22 rows=172,732 width=461) (actual time=246,300.374..251,917.501 rows=56,281 loops=1)

13. 0.041 0.041 ↑ 1.0 1 1

Seq Scan on hospital_center_master bhcm (cost=0.00..9.28 rows=1 width=19) (actual time=0.031..0.041 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 34
14. 17.816 251,905.139 ↑ 3.1 56,281 1

Hash Left Join (cost=3,263,732.62..7,732,719.62 rows=172,732 width=442) (actual time=246,300.340..251,905.139 rows=56,281 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
15. 1,272.644 251,887.319 ↑ 3.1 56,281 1

Hash Left Join (cost=3,263,716.32..7,732,249.90 rows=172,732 width=320) (actual time=246,300.320..251,887.319 rows=56,281 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
16. 17.061 242,455.756 ↑ 3.1 56,281 1

Hash Left Join (cost=2,701,962.30..7,075,678.46 rows=172,732 width=254) (actual time=238,129.313..242,455.756 rows=56,281 loops=1)

  • Hash Cond: ((bc.act_department_id)::text = tdep.dept_id)
17. 0.000 242,436.071 ↑ 3.1 56,281 1

Gather (cost=2,701,954.16..7,073,881.25 rows=172,732 width=229) (actual time=238,126.674..242,436.071 rows=56,281 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 7.078 242,614.738 ↑ 3.8 18,760 3

Hash Left Join (cost=2,700,954.16..7,055,608.05 rows=71,972 width=229) (actual time=238,113.799..242,614.738 rows=18,760 loops=3)

  • Hash Cond: ((bc.prescribing_dr_id)::text = (predoc.doctor_id)::text)
19. 5.889 242,588.156 ↑ 3.8 18,760 3

Hash Left Join (cost=2,700,771.02..7,055,235.76 rows=71,972 width=214) (actual time=238,094.259..242,588.156 rows=18,760 loops=3)

  • Hash Cond: (ssg.service_group_id = sg.service_group_id)
20. 8.894 242,582.124 ↑ 3.8 18,760 3

Hash Left Join (cost=2,700,769.32..7,055,019.30 rows=71,972 width=208) (actual time=238,094.105..242,582.124 rows=18,760 loops=3)

  • Hash Cond: (bc.service_sub_group_id = ssg.service_sub_group_id)
21. 7.639 242,572.836 ↑ 3.8 18,760 3

Hash Join (cost=2,700,763.77..7,054,819.75 rows=71,972 width=192) (actual time=238,091.747..242,572.836 rows=18,760 loops=3)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
22. 6,783.044 242,565.125 ↑ 3.8 18,760 3

Merge Left Join (cost=2,700,761.42..7,054,615.12 rows=71,972 width=182) (actual time=238,091.567..242,565.125 rows=18,760 loops=3)

  • Merge Cond: ((bc.charge_id)::text = (bill_charge_claim.charge_id)::text)
23. 75.094 36,843.450 ↑ 3.8 18,760 3

Sort (cost=2,700,760.85..2,700,940.78 rows=71,972 width=160) (actual time=36,839.172..36,843.450 rows=18,760 loops=3)

  • Sort Key: bc.charge_id
  • Sort Method: quicksort Memory: 6121kB
24. 1,749.495 36,768.356 ↑ 3.8 18,760 3

Hash Join (cost=539,134.37..2,694,954.46 rows=71,972 width=160) (actual time=8,758.471..36,768.356 rows=18,760 loops=3)

  • Hash Cond: ((bc.bill_no)::text = (b.bill_no)::text)
25. 26,269.783 26,269.783 ↑ 1.3 11,933,857 3

Parallel Seq Scan on bill_charge bc (cost=0.00..2,116,634.42 rows=14,927,868 width=118) (actual time=0.310..26,269.783 rows=11,933,857 loops=3)

  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 640753
26. 9.046 8,749.078 ↑ 4.0 9,403 3

Hash (cost=538,661.28..538,661.28 rows=37,847 width=56) (actual time=8,749.078..8,749.078 rows=9,403 loops=3)

  • Buckets: 65536 Batches: 1 Memory Usage: 1327kB
27. 8,740.032 8,740.032 ↑ 4.0 9,403 3

Seq Scan on bill b (cost=0.00..538,661.28 rows=37,847 width=56) (actual time=17.312..8,740.032 rows=9,403 loops=3)

  • Filter: ((status <> 'X'::bpchar) AND (date(open_date) >= '2019-08-01'::date) AND (date(open_date) <= '2019-08-01'::date))
  • Rows Removed by Filter: 7846057
28. 24,990.928 198,938.631 ↑ 1.1 30,100,579 3

GroupAggregate (cost=0.56..3,936,119.92 rows=33,339,040 width=74) (actual time=0.872..198,938.631 rows=30,100,579 loops=3)

  • Group Key: bill_charge_claim.charge_id
29. 173,947.703 173,947.703 ↑ 1.1 30,229,735 3

Index Scan using bill_charge_claim_charge_id on bill_charge_claim (cost=0.56..3,352,686.72 rows=33,339,040 width=14) (actual time=0.855..173,947.703 rows=30,229,735 loops=3)

30. 0.027 0.072 ↑ 1.0 60 3

Hash (cost=1.60..1.60 rows=60 width=22) (actual time=0.072..0.072 rows=60 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
31. 0.045 0.045 ↑ 1.0 60 3

Seq Scan on chargehead_constants chc (cost=0.00..1.60 rows=60 width=22) (actual time=0.031..0.045 rows=60 loops=3)

32. 0.054 0.394 ↑ 1.0 158 3

Hash (cost=3.58..3.58 rows=158 width=24) (actual time=0.394..0.394 rows=158 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 17kB
33. 0.340 0.340 ↑ 1.0 158 3

Seq Scan on service_sub_groups ssg (cost=0.00..3.58 rows=158 width=24) (actual time=0.294..0.340 rows=158 loops=3)

34. 0.023 0.143 ↑ 1.0 31 3

Hash (cost=1.31..1.31 rows=31 width=14) (actual time=0.143..0.143 rows=31 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 10kB
35. 0.120 0.120 ↑ 1.0 31 3

Seq Scan on service_groups sg (cost=0.00..1.31 rows=31 width=14) (actual time=0.110..0.120 rows=31 loops=3)

36. 1.015 19.504 ↑ 1.0 1,873 3

Hash (cost=159.73..159.73 rows=1,873 width=30) (actual time=19.504..19.504 rows=1,873 loops=3)

  • Buckets: 2048 Batches: 1 Memory Usage: 131kB
37. 18.489 18.489 ↑ 1.0 1,873 3

Seq Scan on doctors predoc (cost=0.00..159.73 rows=1,873 width=30) (actual time=0.486..18.489 rows=1,873 loops=3)

38. 0.062 2.624 ↑ 1.0 115 1

Hash (cost=6.70..6.70 rows=115 width=64) (actual time=2.624..2.624 rows=115 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
39. 0.033 2.562 ↑ 1.0 115 1

Subquery Scan on tdep (cost=0.00..6.70 rows=115 width=64) (actual time=1.468..2.562 rows=115 loops=1)

40. 0.024 2.529 ↑ 1.0 115 1

Append (cost=0.00..5.55 rows=115 width=96) (actual time=1.467..2.529 rows=115 loops=1)

41. 1.487 1.487 ↑ 1.0 44 1

Seq Scan on services_departments (cost=0.00..1.66 rows=44 width=77) (actual time=1.466..1.487 rows=44 loops=1)

42. 0.004 0.319 ↑ 1.0 12 1

Subquery Scan on *SELECT* 2 (cost=0.00..1.27 rows=12 width=77) (actual time=0.310..0.319 rows=12 loops=1)

43. 0.315 0.315 ↑ 1.0 12 1

Seq Scan on diagnostics_departments (cost=0.00..1.15 rows=12 width=55) (actual time=0.309..0.315 rows=12 loops=1)

44. 0.016 0.699 ↑ 1.0 59 1

Subquery Scan on *SELECT* 3 (cost=0.00..2.18 rows=59 width=77) (actual time=0.665..0.699 rows=59 loops=1)

45. 0.683 0.683 ↑ 1.0 59 1

Seq Scan on department (cost=0.00..1.59 rows=59 width=53) (actual time=0.665..0.683 rows=59 loops=1)

46. 2,280.529 8,158.919 ↓ 1.0 7,053,864 1

Hash (cost=391,139.12..391,139.12 rows=7,044,712 width=66) (actual time=8,158.919..8,158.919 rows=7,053,864 loops=1)

  • Buckets: 2097152 Batches: 8 Memory Usage: 95626kB
47. 5,878.390 5,878.390 ↓ 1.0 7,053,864 1

Seq Scan on patient_registration pr (cost=0.00..391,139.12 rows=7,044,712 width=66) (actual time=0.028..5,878.390 rows=7,053,864 loops=1)

48. 0.000 0.004 ↓ 0.0 0 1

Hash (cost=12.80..12.80 rows=280 width=170) (actual time=0.004..0.004 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
49. 0.004 0.004 ↓ 0.0 0 1

Seq Scan on store_retail_customers prc (cost=0.00..12.80 rows=280 width=170) (actual time=0.004..0.004 rows=0 loops=1)

50. 99.692 204.575 ↓ 1.0 342,002 1

Hash (cost=10,304.82..10,304.82 rows=341,582 width=39) (actual time=204.575..204.575 rows=342,002 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 28404kB
51. 104.883 104.883 ↓ 1.0 342,002 1

Seq Scan on incoming_sample_registration isr (cost=0.00..10,304.82 rows=341,582 width=39) (actual time=0.015..104.883 rows=342,002 loops=1)

52. 1,133.856 1,133.856 ↑ 1.0 1 23,622

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..3.20 rows=1 width=48) (actual time=0.048..0.048 rows=1 loops=23,622)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
53. 0.008 0.018 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=14) (actual time=0.018..0.018 rows=21 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
54. 0.010 0.010 ↑ 1.0 21 1

Seq Scan on salutation_master smb (cost=0.00..1.21 rows=21 width=14) (actual time=0.008..0.010 rows=21 loops=1)

55. 283.464 283.464 ↑ 1.0 1 23,622

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans vsd (cost=0.56..3.35 rows=1 width=20) (actual time=0.012..0.012 rows=1 loops=23,622)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
  • Rows Removed by Filter: 0
56. 0.004 0.014 ↑ 1.0 5 1

Hash (cost=1.05..1.05 rows=5 width=20) (actual time=0.014..0.014 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.010 0.010 ↑ 1.0 5 1

Seq Scan on op_type_names otn (cost=0.00..1.05 rows=5 width=20) (actual time=0.010..0.010 rows=5 loops=1)

58. 0.332 0.752 ↑ 1.0 1,873 1

Hash (cost=159.73..159.73 rows=1,873 width=30) (actual time=0.752..0.752 rows=1,873 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 131kB
59. 0.420 0.420 ↑ 1.0 1,873 1

Seq Scan on doctors dr (cost=0.00..159.73 rows=1,873 width=30) (actual time=0.005..0.420 rows=1,873 loops=1)

60. 0.317 0.564 ↑ 1.0 1,873 1

Hash (cost=159.73..159.73 rows=1,873 width=30) (actual time=0.564..0.564 rows=1,873 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 131kB
61. 0.247 0.247 ↑ 1.0 1,873 1

Seq Scan on doctors rdoc (cost=0.00..159.73 rows=1,873 width=30) (actual time=0.004..0.247 rows=1,873 loops=1)

62. 34.630 81.023 ↑ 1.0 135,616 1

Hash (cost=3,494.16..3,494.16 rows=135,616 width=28) (actual time=81.023..81.023 rows=135,616 loops=1)

  • Buckets: 262144 Batches: 1 Memory Usage: 10134kB
63. 46.393 46.393 ↑ 1.0 135,616 1

Seq Scan on referral ref (cost=0.00..3,494.16 rows=135,616 width=28) (actual time=0.280..46.393 rows=135,616 loops=1)

64. 0.120 1.132 ↑ 1.0 628 1

Hash (cost=39.28..39.28 rows=628 width=34) (actual time=1.132..1.132 rows=628 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
65. 1.012 1.012 ↑ 1.0 628 1

Seq Scan on tpa_master ptpa (cost=0.00..39.28 rows=628 width=34) (actual time=0.011..1.012 rows=628 loops=1)

66. 12.533 85.914 ↓ 1.0 45,112 1

Hash (cost=1,800.04..1,800.04 rows=45,104 width=47) (actual time=85.914..85.914 rows=45,112 loops=1)

  • Buckets: 65536 Batches: 1 Memory Usage: 4083kB
67. 73.381 73.381 ↓ 1.0 45,112 1

Seq Scan on insurance_plan_main pipm (cost=0.00..1,800.04 rows=45,104 width=47) (actual time=0.018..73.381 rows=45,112 loops=1)

Planning time : 21.043 ms
Execution time : 254,056.997 ms