explain.depesz.com

PostgreSQL's explain analyze made readable

Result: iYRg

Settings
# exclusive inclusive rows x rows loops node
1. 125.005 170,692.117 ↓ 5.6 23,622 1

Sort (cost=7,808,193.33..7,808,203.87 rows=4,217 width=734) (actual time=170,689.263..170,692.117 rows=23,622 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 13227kB
2. 25.098 170,567.112 ↓ 5.6 23,622 1

Hash Left Join (cost=3,286,278.23..7,807,939.42 rows=4,217 width=734) (actual time=163,663.327..170,567.112 rows=23,622 loops=1)

  • Hash Cond: (pri_plan.plan_id = pipm.plan_id)
3. 9.336 170,504.210 ↓ 5.6 23,622 1

Hash Left Join (cost=3,283,914.39..7,805,416.92 rows=4,217 width=573) (actual time=163,625.258..170,504.210 rows=23,622 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
4. 7.605 170,494.431 ↓ 5.6 23,622 1

Hash Left Join (cost=3,283,867.26..7,805,358.65 rows=4,217 width=559) (actual time=163,624.782..170,494.431 rows=23,622 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (ref.referal_no)::text)
5. 7.270 170,411.065 ↓ 5.6 23,622 1

Hash Left Join (cost=3,278,677.90..7,800,158.22 rows=4,217 width=544) (actual time=163,548.231..170,411.065 rows=23,622 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (rdoc.doctor_id)::text)
6. 9.060 170,403.190 ↓ 5.6 23,622 1

Hash Left Join (cost=3,278,494.76..7,799,964.01 rows=4,217 width=522) (actual time=163,547.608..170,403.190 rows=23,622 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
7. 9.090 170,393.359 ↓ 5.6 23,622 1

Hash Left Join (cost=3,278,311.62..7,799,769.78 rows=4,217 width=508) (actual time=163,546.814..170,393.359 rows=23,622 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
8. 20.636 170,383.995 ↓ 5.6 23,622 1

Nested Loop Left Join (cost=3,278,310.51..7,799,748.22 rows=4,217 width=492) (actual time=163,546.520..170,383.995 rows=23,622 loops=1)

9. 9.386 169,890.919 ↓ 5.6 23,622 1

Hash Left Join (cost=3,278,309.12..7,763,457.73 rows=4,217 width=504) (actual time=163,544.783..169,890.919 rows=23,622 loops=1)

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
10. 20.857 169,881.254 ↓ 5.6 23,622 1

Nested Loop Left Join (cost=3,278,307.64..7,763,442.95 rows=4,217 width=508) (actual time=163,544.476..169,881.254 rows=23,622 loops=1)

11. 20.179 169,010.005 ↓ 5.6 23,622 1

Hash Left Join (cost=3,278,307.21..7,749,484.23 rows=4,217 width=475) (actual time=163,543.315..169,010.005 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. 10.573 168,785.892 ↑ 3.1 56,281 1

Nested Loop (cost=3,263,732.62..7,734,456.22 rows=172,732 width=461) (actual time=163,337.797..168,785.892 rows=56,281 loops=1)

13. 0.857 0.857 ↑ 1.0 1 1

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

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 34
14. 15.511 168,774.462 ↑ 3.1 56,281 1

Hash Left Join (cost=3,263,732.62..7,732,719.62 rows=172,732 width=442) (actual time=163,337.744..168,774.462 rows=56,281 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
15. 1,316.502 168,758.947 ↑ 3.1 56,281 1

Hash Left Join (cost=3,263,716.32..7,732,249.90 rows=172,732 width=320) (actual time=163,337.716..168,758.947 rows=56,281 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
16. 16.458 159,334.279 ↑ 3.1 56,281 1

Hash Left Join (cost=2,701,962.30..7,075,678.46 rows=172,732 width=254) (actual time=155,173.378..159,334.279 rows=56,281 loops=1)

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

Gather (cost=2,701,954.16..7,073,881.25 rows=172,732 width=229) (actual time=155,173.027..159,317.482 rows=56,281 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
18. 6.824 159,485.946 ↑ 3.8 18,760 3

Hash Left Join (cost=2,700,954.16..7,055,608.05 rows=71,972 width=229) (actual time=155,149.744..159,485.946 rows=18,760 loops=3)

  • Hash Cond: ((bc.prescribing_dr_id)::text = (predoc.doctor_id)::text)
19. 5.727 159,476.983 ↑ 3.8 18,760 3

Hash Left Join (cost=2,700,771.02..7,055,235.76 rows=71,972 width=214) (actual time=155,147.587..159,476.983 rows=18,760 loops=3)

  • Hash Cond: (ssg.service_group_id = sg.service_group_id)
20. 8.740 159,471.152 ↑ 3.8 18,760 3

Hash Left Join (cost=2,700,769.32..7,055,019.30 rows=71,972 width=208) (actual time=155,147.469..159,471.152 rows=18,760 loops=3)

  • Hash Cond: (bc.service_sub_group_id = ssg.service_sub_group_id)
21. 7.285 159,462.264 ↑ 3.8 18,760 3

Hash Join (cost=2,700,763.77..7,054,819.75 rows=71,972 width=192) (actual time=155,145.275..159,462.264 rows=18,760 loops=3)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
22. 5,437.715 159,454.942 ↑ 3.8 18,760 3

Merge Left Join (cost=2,700,761.42..7,054,615.12 rows=71,972 width=182) (actual time=155,145.181..159,454.942 rows=18,760 loops=3)

  • Merge Cond: ((bc.charge_id)::text = (bill_charge_claim.charge_id)::text)
23. 75.809 40,587.837 ↑ 3.8 18,760 3

Sort (cost=2,700,760.85..2,700,940.78 rows=71,972 width=160) (actual time=40,583.417..40,587.837 rows=18,760 loops=3)

  • Sort Key: bc.charge_id
  • Sort Method: quicksort Memory: 6030kB
24. 1,891.235 40,512.028 ↑ 3.8 18,760 3

Hash Join (cost=539,134.37..2,694,954.46 rows=71,972 width=160) (actual time=6,732.052..40,512.028 rows=18,760 loops=3)

  • Hash Cond: ((bc.bill_no)::text = (b.bill_no)::text)
25. 31,901.184 31,901.184 ↑ 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.626..31,901.184 rows=11,933,857 loops=3)

  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 640753
26. 8.207 6,719.609 ↑ 4.0 9,403 3

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

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

Seq Scan on bill b (cost=0.00..538,661.28 rows=37,847 width=56) (actual time=24.270..6,711.402 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. 20,175.588 113,429.390 ↑ 1.1 30,100,579 3

GroupAggregate (cost=0.56..3,936,119.92 rows=33,339,040 width=74) (actual time=0.725..113,429.390 rows=30,100,579 loops=3)

  • Group Key: bill_charge_claim.charge_id
29. 93,253.802 93,253.802 ↑ 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.711..93,253.802 rows=30,229,735 loops=3)

30. 0.013 0.037 ↑ 1.0 60 3

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

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

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

32. 0.022 0.148 ↑ 1.0 158 3

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

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

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

34. 0.007 0.104 ↑ 1.0 31 3

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

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

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

36. 0.322 2.139 ↑ 1.0 1,873 3

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

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

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

38. 0.019 0.339 ↑ 1.0 115 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 14kB
39. 0.012 0.320 ↑ 1.0 115 1

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

40. 0.009 0.308 ↑ 1.0 115 1

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

41. 0.017 0.017 ↑ 1.0 44 1

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

42. 0.002 0.013 ↑ 1.0 12 1

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

43. 0.011 0.011 ↑ 1.0 12 1

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

44. 0.006 0.269 ↑ 1.0 59 1

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

45. 0.263 0.263 ↑ 1.0 59 1

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

46. 2,315.209 8,108.166 ↓ 1.0 7,053,864 1

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

  • Buckets: 2097152 Batches: 8 Memory Usage: 95626kB
47. 5,792.957 5,792.957 ↓ 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.030..5,792.957 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.130 203.934 ↓ 1.0 342,002 1

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

  • Buckets: 524288 Batches: 1 Memory Usage: 28404kB
51. 104.804 104.804 ↓ 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.322..104.804 rows=342,002 loops=1)

52. 850.392 850.392 ↑ 1.0 1 23,622

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

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

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

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

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

55. 0.000 472.440 ↑ 1.0 1 23,622

Nested Loop Left Join (cost=1.39..8.60 rows=1 width=20) (actual time=0.020..0.020 rows=1 loops=23,622)

56. 7.614 330.708 ↑ 1.0 1 23,622

Nested Loop (cost=0.83..5.12 rows=1 width=20) (actual time=0.014..0.014 rows=1 loops=23,622)

57. 259.842 259.842 ↑ 1.0 1 23,622

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pri_plan (cost=0.56..3.46 rows=1 width=38) (actual time=0.011..0.011 rows=1 loops=23,622)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
  • Rows Removed by Filter: 0
58. 63.252 63.252 ↑ 1.0 1 21,084

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.28..1.66 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=21,084)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 0
59. 147.588 147.588 ↓ 0.0 0 21,084

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sec_plan (cost=0.56..3.46 rows=1 width=38) (actual time=0.007..0.007 rows=0 loops=21,084)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
60. 0.008 0.274 ↑ 1.0 5 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
61. 0.266 0.266 ↑ 1.0 5 1

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

62. 0.340 0.771 ↑ 1.0 1,873 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 131kB
63. 0.431 0.431 ↑ 1.0 1,873 1

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

64. 0.320 0.605 ↑ 1.0 1,873 1

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

  • Buckets: 2048 Batches: 1 Memory Usage: 131kB
65. 0.285 0.285 ↑ 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.285 rows=1,873 loops=1)

66. 32.560 75.761 ↑ 1.0 135,616 1

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

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

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

68. 0.168 0.443 ↑ 1.0 628 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 49kB
69. 0.275 0.275 ↑ 1.0 628 1

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

70. 10.604 37.804 ↓ 1.0 45,111 1

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

  • Buckets: 65536 Batches: 1 Memory Usage: 4083kB
71. 27.200 27.200 ↓ 1.0 45,111 1

Seq Scan on insurance_plan_main pipm (cost=0.00..1,800.04 rows=45,104 width=47) (actual time=0.335..27.200 rows=45,111 loops=1)

Planning time : 69.428 ms
Execution time : 170,694.215 ms