explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Blfm

Settings
# exclusive inclusive rows x rows loops node
1. 36.926 368,832.311 ↓ 1.4 14,324 1

Sort (cost=9,434,888.40..9,434,913.98 rows=10,235 width=707) (actual time=368,830.931..368,832.311 rows=14,324 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 7380kB
2. 15.450 368,795.385 ↓ 1.4 14,324 1

Nested Loop Left Join (cost=2,966,166.35..9,434,206.68 rows=10,235 width=707) (actual time=367,966.424..368,795.385 rows=14,324 loops=1)

3. 4.963 368,751.287 ↓ 1.4 14,324 1

Hash Left Join (cost=2,966,166.06..9,430,466.42 rows=10,235 width=551) (actual time=367,966.332..368,751.287 rows=14,324 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
4. 9.785 368,746.017 ↓ 1.4 14,324 1

Nested Loop Left Join (cost=2,966,091.19..9,430,364.24 rows=10,235 width=537) (actual time=367,966.001..368,746.017 rows=14,324 loops=1)

5. 4.038 368,721.908 ↓ 1.4 14,324 1

Hash Left Join (cost=2,966,090.77..9,425,674.21 rows=10,235 width=522) (actual time=367,965.951..368,721.908 rows=14,324 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (rdoc.doctor_id)::text)
6. 5.579 368,717.293 ↓ 1.4 14,324 1

Hash Left Join (cost=2,965,876.27..9,425,432.81 rows=10,235 width=500) (actual time=367,965.333..368,717.293 rows=14,324 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
7. 5.049 368,710.953 ↓ 1.4 14,324 1

Hash Left Join (cost=2,965,661.77..9,425,191.29 rows=10,235 width=486) (actual time=367,964.536..368,710.953 rows=14,324 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
8. 4.897 368,705.893 ↓ 1.4 14,324 1

Nested Loop Left Join (cost=2,965,660.45..9,425,099.40 rows=10,235 width=470) (actual time=367,964.230..368,705.893 rows=14,324 loops=1)

9. 5.016 368,471.812 ↓ 1.4 14,324 1

Hash Left Join (cost=2,965,659.06..9,408,149.04 rows=10,235 width=482) (actual time=367,963.440..368,471.812 rows=14,324 loops=1)

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
10. 9.085 368,466.778 ↓ 1.4 14,324 1

Nested Loop Left Join (cost=2,965,656.75..9,408,104.70 rows=10,235 width=486) (actual time=367,963.376..368,466.778 rows=14,324 loops=1)

11. 22.325 368,328.777 ↓ 1.4 14,324 1

Hash Left Join (cost=2,965,656.32..9,403,046.13 rows=10,235 width=452) (actual time=367,963.272..368,328.777 rows=14,324 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: 35932
12. 7.927 368,096.660 ↑ 4.1 50,256 1

Nested Loop (cost=2,929,868.70..9,366,718.09 rows=205,872 width=438) (actual time=367,751.458..368,096.660 rows=50,256 loops=1)

13. 0.050 0.050 ↑ 1.0 1 1

Seq Scan on hospital_center_master bhcm (cost=0.00..5.43 rows=1 width=22) (actual time=0.024..0.050 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 43
14. 11.336 368,088.683 ↑ 4.1 50,256 1

Hash Left Join (cost=2,929,868.70..9,360,536.50 rows=205,872 width=416) (actual time=367,751.417..368,088.683 rows=50,256 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
15. 0.000 368,077.342 ↑ 4.1 50,256 1

Hash Left Join (cost=2,929,841.20..9,359,968.58 rows=205,872 width=294) (actual time=367,751.385..368,077.342 rows=50,256 loops=1)

  • Hash Cond: ((bc.act_department_id)::text = tdep.dept_id)
16. 46.880 368,103.568 ↑ 4.1 50,256 1

Gather (cost=2,929,823.74..9,355,051.88 rows=205,872 width=269) (actual time=367,751.221..368,103.568 rows=50,256 loops=1)

  • Workers Planned: 2
  • Workers Launched: 1
17. 7.754 368,056.688 ↑ 3.4 25,128 2 / 2

Hash Left Join (cost=2,928,823.74..9,333,464.68 rows=85,780 width=269) (actual time=367,740.899..368,056.688 rows=25,128 loops=2)

  • Hash Cond: ((bc.prescribing_dr_id)::text = (predoc.doctor_id)::text)
18. 7.527 368,048.140 ↑ 3.4 25,128 2 / 2

Hash Join (cost=2,928,609.24..9,333,024.33 rows=85,780 width=254) (actual time=367,740.031..368,048.140 rows=25,128 loops=2)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
19. 10,798.875 368,040.572 ↑ 3.4 25,128 2 / 2

Merge Left Join (cost=2,928,604.49..9,332,749.90 rows=85,780 width=244) (actual time=367,739.938..368,040.572 rows=25,128 loops=2)

  • Merge Cond: ((bc.charge_id)::text = (bill_charge_claim.charge_id)::text)
20. 122.833 13,946.184 ↑ 3.4 25,128 2 / 2

Sort (cost=2,928,603.92..2,928,818.37 rows=85,780 width=222) (actual time=13,940.024..13,946.184 rows=25,128 loops=2)

  • Sort Key: bc.charge_id
  • Sort Method: quicksort Memory: 8865kB
21. 10,784.201 13,823.351 ↑ 3.4 25,128 2 / 2

Nested Loop (cost=1.12..2,921,574.96 rows=85,780 width=222) (actual time=4.170..13,823.351 rows=25,128 loops=2)

  • -> Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..112.37 rows=196 width=114) (actual time=0.
22. 3,039.150 3,039.150 ↑ 3.1 6,166 2 / 2

Nested Loop Left Join (cost=0.56..681,503.50 rows=18,944 width=122) (actual time=2.728..3,039.150 rows=6,166 loops=2)

  • -> Parallel Seq Scan on bill b (cost=0.00..607619.18 rows=18944 width=56) (actual time=2.635..2867.928 rows=6166
  • Filter: ((status <> 'X'::bpchar) AND (date(open_date) >= '2020-01-06'::date) AND (date(open_date) <= '2020-0
  • Rows Removed by Filter: 4690681
  • -> Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..3.90 rows=1 width=66) (actu
  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 0
23. 36,849.724 343,295.513 ↑ 1.1 36,664,896 2 / 2

GroupAggregate (cost=0.56..5,119,010.57 rows=39,459,544 width=74) (actual time=0.086..343,295.513 rows=36,664,896 loops=2)

  • Group Key: bill_charge_claim.charge_id
24. 306,445.789 306,445.789 ↑ 1.1 36,806,096 2 / 2

Index Scan using bill_charge_claim_charge_id on bill_charge_claim (cost=0.56..3,639,277.67 rows=39,459,544 width=14) (actual time=0.059..306,445.789 rows=36,806,096 loops=2)

25. 0.014 0.041 ↑ 1.0 60 2 / 2

Hash (cost=2.80..2.80 rows=60 width=22) (actual time=0.041..0.041 rows=60 loops=2)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
26. 0.027 0.027 ↑ 1.0 60 2 / 2

Seq Scan on chargehead_constants chc (cost=0.00..2.80 rows=60 width=22) (actual time=0.020..0.027 rows=60 loops=2)

27. 0.372 0.794 ↑ 1.0 2,072 2 / 2

Hash (cost=147.16..147.16 rows=2,072 width=30) (actual time=0.794..0.794 rows=2,072 loops=2)

  • Buckets: 4096 Batches: 1 Memory Usage: 160kB
28. 0.422 0.422 ↑ 1.0 2,072 2 / 2

Seq Scan on doctors predoc (cost=0.00..147.16 rows=2,072 width=30) (actual time=0.023..0.422 rows=2,072 loops=2)

29. 0.021 0.123 ↑ 1.0 116 1

Hash (cost=13.69..13.69 rows=116 width=64) (actual time=0.123..0.123 rows=116 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
30. 0.016 0.102 ↑ 1.0 116 1

Subquery Scan on tdep (cost=0.00..13.69 rows=116 width=64) (actual time=0.031..0.102 rows=116 loops=1)

31. 0.013 0.086 ↑ 1.0 116 1

Append (cost=0.00..10.21 rows=116 width=96) (actual time=0.024..0.086 rows=116 loops=1)

32. 0.027 0.027 ↑ 1.0 44 1

Seq Scan on services_departments (cost=0.00..2.54 rows=44 width=77) (actual time=0.021..0.027 rows=44 loops=1)

33. 0.004 0.023 ↑ 1.0 12 1

Subquery Scan on *SELECT* 2 (cost=0.00..1.75 rows=12 width=77) (actual time=0.021..0.023 rows=12 loops=1)

34. 0.019 0.019 ↑ 1.0 12 1

Seq Scan on diagnostics_departments (cost=0.00..1.39 rows=12 width=55) (actual time=0.018..0.019 rows=12 loops=1)

35. 0.004 0.023 ↑ 1.0 60 1

Subquery Scan on *SELECT* 3 (cost=0.00..4.60 rows=60 width=77) (actual time=0.014..0.023 rows=60 loops=1)

36. 0.019 0.019 ↑ 1.0 60 1

Seq Scan on department (cost=0.00..2.80 rows=60 width=53) (actual time=0.014..0.019 rows=60 loops=1)

37. 0.000 0.005 ↓ 0.0 0 1

Hash (cost=18.40..18.40 rows=280 width=170) (actual time=0.005..0.005 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
38. 0.005 0.005 ↓ 0.0 0 1

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

39. 110.422 209.792 ↓ 1.0 428,865 1

Hash (cost=21,864.82..21,864.82 rows=428,394 width=39) (actual time=209.792..209.792 rows=428,865 loops=1)

  • Buckets: 524288 Batches: 1 Memory Usage: 34619kB
40. 99.370 99.370 ↓ 1.0 428,865 1

Seq Scan on incoming_sample_registration isr (cost=0.00..21,864.82 rows=428,394 width=39) (actual time=0.016..99.370 rows=428,865 loops=1)

41. 128.916 128.916 ↑ 1.0 1 14,324

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.49 rows=1 width=49) (actual time=0.009..0.009 rows=1 loops=14,324)

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

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
43. 0.012 0.012 ↑ 1.0 21 1

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

44. 0.495 229.184 ↑ 1.0 1 14,324

Nested Loop Left Join (cost=1.39..1.63 rows=1 width=20) (actual time=0.016..0.016 rows=1 loops=14,324)

45. 6.351 143.240 ↑ 1.0 1 14,324

Nested Loop (cost=0.83..0.95 rows=1 width=20) (actual time=0.010..0.010 rows=1 loops=14,324)

46. 100.268 100.268 ↑ 1.0 1 14,324

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pri_plan (cost=0.56..0.64 rows=1 width=38) (actual time=0.007..0.007 rows=1 loops=14,324)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
47. 36.621 36.621 ↑ 1.0 1 12,207

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.28..0.31 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=12,207)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 0
48. 85.449 85.449 ↓ 0.0 0 12,207

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sec_plan (cost=0.56..0.64 rows=1 width=38) (actual time=0.007..0.007 rows=0 loops=12,207)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
49. 0.006 0.011 ↑ 1.0 5 1

Hash (cost=1.15..1.15 rows=5 width=20) (actual time=0.010..0.011 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.005 0.005 ↑ 1.0 5 1

Seq Scan on op_type_names otn (cost=0.00..1.15 rows=5 width=20) (actual time=0.004..0.005 rows=5 loops=1)

51. 0.376 0.761 ↑ 1.0 2,072 1

Hash (cost=147.16..147.16 rows=2,072 width=30) (actual time=0.761..0.761 rows=2,072 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 160kB
52. 0.385 0.385 ↑ 1.0 2,072 1

Seq Scan on doctors dr (cost=0.00..147.16 rows=2,072 width=30) (actual time=0.005..0.385 rows=2,072 loops=1)

53. 0.344 0.577 ↑ 1.0 2,072 1

Hash (cost=147.16..147.16 rows=2,072 width=30) (actual time=0.577..0.577 rows=2,072 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 160kB
54. 0.233 0.233 ↑ 1.0 2,072 1

Seq Scan on doctors rdoc (cost=0.00..147.16 rows=2,072 width=30) (actual time=0.004..0.233 rows=2,072 loops=1)

55. 14.324 14.324 ↓ 0.0 0 14,324

Index Scan using referral_pkey on referral ref (cost=0.42..0.46 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=14,324)

  • Index Cond: ((pr.reference_docto_id)::text = (referal_no)::text)
56. 0.126 0.307 ↑ 1.0 670 1

Hash (cost=53.10..53.10 rows=670 width=34) (actual time=0.307..0.307 rows=670 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 52kB
57. 0.181 0.181 ↑ 1.0 670 1

Seq Scan on tpa_master ptpa (cost=0.00..53.10 rows=670 width=34) (actual time=0.006..0.181 rows=670 loops=1)

58. 28.648 28.648 ↑ 1.0 1 14,324

Index Scan using insurance_plan_main_pkey on insurance_plan_main pipm (cost=0.29..0.33 rows=1 width=47) (actual time=0.002..0.002 rows=1 loops=14,324)

  • Index Cond: (pri_plan.plan_id = plan_id)
Planning time : 67.935 ms
Execution time : 368,879.420 ms