explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Ms2E : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #Dr7b; plan #Cuqw; plan #iBhy; plan #Og9h; plan #O2ZQ

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.065 2,030.830 ↑ 865.0 13 1

Hash Left Join (cost=231,343.14..345,437.34 rows=11,245 width=3,015) (actual time=2,028.571..2,030.830 rows=13 loops=1)

  • Hash Cond: (pr.patient_category_id = prcm.category_id)
2. 0.019 2,030.730 ↑ 865.0 13 1

Nested Loop Left Join (cost=231,336.97..344,264.50 rows=11,245 width=3,001) (actual time=2,028.510..2,030.730 rows=13 loops=1)

3. 0.008 2,030.529 ↑ 865.0 13 1

Hash Left Join (cost=231,329.16..330,158.28 rows=11,245 width=2,862) (actual time=2,028.480..2,030.529 rows=13 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
4. 0.003 2,030.515 ↑ 865.0 13 1

Hash Left Join (cost=231,327.78..330,114.72 rows=11,245 width=2,298) (actual time=2,028.471..2,030.515 rows=13 loops=1)

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stm.tpa_id)::text)
5. 0.011 2,030.430 ↑ 865.0 13 1

Hash Left Join (cost=231,311.61..330,056.35 rows=11,245 width=2,287) (actual time=2,028.386..2,030.430 rows=13 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptm.tpa_id)::text)
6. 0.012 2,030.331 ↑ 865.0 13 1

Hash Left Join (cost=231,295.45..329,888.37 rows=11,245 width=2,275) (actual time=2,028.295..2,030.331 rows=13 loops=1)

  • Hash Cond: ((bc.act_department_id)::text = tdep.dept_id)
7. 0.013 2,030.264 ↑ 865.0 13 1

Hash Left Join (cost=231,288.10..329,757.69 rows=11,245 width=2,250) (actual time=2,028.236..2,030.264 rows=13 loops=1)

  • Hash Cond: (bc.account_group = bcagm.account_group_id)
8. 25.138 2,030.243 ↑ 865.0 13 1

Hash Left Join (cost=231,287.01..329,601.98 rows=11,245 width=1,836) (actual time=2,028.218..2,030.243 rows=13 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
9. 6.638 1,609.961 ↑ 865.0 13 1

Hash Left Join (cost=194,139.21..282,654.40 rows=11,245 width=1,803) (actual time=1,587.606..1,609.961 rows=13 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
10. 0.008 1,576.800 ↑ 865.0 13 1

Hash Left Join (cost=192,511.33..275,736.59 rows=11,245 width=1,790) (actual time=1,536.976..1,576.800 rows=13 loops=1)

  • Hash Cond: (rpt.currency_id = fc.currency_id)
11. 0.011 1,576.792 ↑ 865.0 13 1

Hash Left Join (cost=192,498.63..275,577.84 rows=11,245 width=1,278) (actual time=1,536.973..1,576.792 rows=13 loops=1)

  • Hash Cond: (rpt.card_type_id = ctm.card_type_id)
12. 0.017 1,576.778 ↑ 865.0 13 1

Hash Left Join (cost=192,497.57..275,422.16 rows=11,245 width=1,164) (actual time=1,536.967..1,576.778 rows=13 loops=1)

  • Hash Cond: (bc.service_sub_group_id = ssg.service_sub_group_id)
13. 0.011 1,576.384 ↑ 865.0 13 1

Hash Left Join (cost=192,450.41..275,220.39 rows=11,245 width=935) (actual time=1,536.586..1,576.384 rows=13 loops=1)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
14. 0.028 1,576.304 ↑ 865.0 13 1

Hash Left Join (cost=192,443.44..275,058.79 rows=11,245 width=689) (actual time=1,536.513..1,576.304 rows=13 loops=1)

  • Hash Cond: (((bc.charge_id)::text = (bcra.charge_id)::text) AND (r.bill_receipt_id = bcra.bill_receipt_id))
15. 0.019 1,576.268 ↑ 865.0 13 1

Hash Join (cost=192,425.19..274,056.59 rows=11,245 width=671) (actual time=1,536.493..1,576.268 rows=13 loops=1)

  • Hash Cond: (rpt.payment_mode_id = pm.mode_id)
16. 0.014 1,576.234 ↑ 865.0 13 1

Hash Join (cost=192,423.94..273,900.73 rows=11,245 width=597) (actual time=1,536.468..1,576.234 rows=13 loops=1)

  • Hash Cond: (b.visit_type = vtn.visit_type)
17. 0.017 1,576.215 ↑ 865.0 13 1

Hash Join (cost=192,422.85..273,745.02 rows=11,245 width=567) (actual time=1,536.457..1,576.215 rows=13 loops=1)

  • Hash Cond: ((rpt.counter)::text = (c.counter_id)::text)
18. 298.742 1,576.190 ↑ 865.0 13 1

Hash Join (cost=192,421.62..273,589.18 rows=11,245 width=341) (actual time=1,536.441..1,576.190 rows=13 loops=1)

  • Hash Cond: ((b.bill_no)::text = (r.bill_no)::text)
19. 373.805 373.805 ↑ 1.0 1,080,041 1

Seq Scan on bill b (cost=0.00..51,407.63 rows=1,080,063 width=55) (actual time=0.014..373.805 rows=1,080,041 loops=1)

20. 0.077 903.643 ↑ 865.0 13 1

Hash (cost=191,830.06..191,830.06 rows=11,245 width=302) (actual time=903.643..903.643 rows=13 loops=1)

  • Buckets: 1,024 Batches: 4 Memory Usage: 2kB
21. 177.190 903.566 ↑ 865.0 13 1

Hash Join (cost=57,926.53..191,830.06 rows=11,245 width=302) (actual time=903.553..903.566 rows=13 loops=1)

  • Hash Cond: ((bc.bill_no)::text = (r.bill_no)::text)
22. 308.653 308.653 ↑ 1.0 2,239,924 1

Seq Scan on bill_charge bc (cost=0.00..125,391.33 rows=2,239,933 width=79) (actual time=0.007..308.653 rows=2,239,924 loops=1)

23. 0.002 417.723 ↑ 766.6 5 1

Hash (cost=57,878.62..57,878.62 rows=3,833 width=223) (actual time=417.723..417.723 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
24. 55.910 417.721 ↑ 766.6 5 1

Hash Join (cost=31,382.78..57,878.62 rows=3,833 width=223) (actual time=417.712..417.721 rows=5 loops=1)

  • Hash Cond: ((r.receipt_no)::text = (rpt.receipt_id)::text)
25. 64.358 64.358 ↓ 1.0 772,550 1

Seq Scan on bill_receipts r (cost=0.00..20,663.86 rows=772,486 width=31) (actual time=0.007..64.358 rows=772,550 loops=1)

26. 0.006 297.453 ↑ 766.8 5 1

Hash (cost=31,334.86..31,334.86 rows=3,834 width=201) (actual time=297.453..297.453 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
27. 297.447 297.447 ↑ 766.8 5 1

Seq Scan on receipts rpt (cost=0.00..31,334.86 rows=3,834 width=201) (actual time=297.443..297.447 rows=5 loops=1)

  • Filter: ((NOT is_deposit) AND (payment_mode_id <> (-9)) AND ((receipt_type)::text = 'R'::text) AND ((display_date)::date = '2020-08-03'::date))
  • Rows Removed by Filter: 772,645
28. 0.004 0.008 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=292) (actual time=0.008..0.008 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
29. 0.004 0.004 ↑ 1.0 10 1

Seq Scan on counters c (cost=0.00..1.10 rows=10 width=292) (actual time=0.001..0.004 rows=10 loops=1)

30. 0.002 0.005 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=40) (actual time=0.005..0.005 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
31. 0.003 0.003 ↑ 1.0 4 1

Seq Scan on visit_type_names vtn (cost=0.00..1.04 rows=4 width=40) (actual time=0.002..0.003 rows=4 loops=1)

32. 0.005 0.015 ↓ 1.4 15 1

Hash (cost=1.11..1.11 rows=11 width=82) (actual time=0.015..0.015 rows=15 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
33. 0.010 0.010 ↓ 1.4 15 1

Seq Scan on payment_mode_master pm (cost=0.00..1.11 rows=11 width=82) (actual time=0.005..0.010 rows=15 loops=1)

34. 0.003 0.008 ↑ 36.7 9 1

Hash (cost=13.30..13.30 rows=330 width=82) (actual time=0.008..0.008 rows=9 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
35. 0.005 0.005 ↑ 36.7 9 1

Seq Scan on bill_charge_receipt_allocation bcra (cost=0.00..13.30 rows=330 width=82) (actual time=0.004..0.005 rows=9 loops=1)

36. 0.012 0.069 ↑ 1.0 62 1

Hash (cost=6.20..6.20 rows=62 width=258) (actual time=0.069..0.069 rows=62 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 6kB
37. 0.018 0.057 ↑ 1.0 62 1

Hash Left Join (cost=2.88..6.20 rows=62 width=258) (actual time=0.024..0.057 rows=62 loops=1)

  • Hash Cond: (chc.account_head_id = bahc.account_head_id)
38. 0.021 0.032 ↑ 1.0 62 1

Hash Left Join (cost=1.41..3.88 rows=62 width=144) (actual time=0.013..0.032 rows=62 loops=1)

  • Hash Cond: ((chc.chargegroup_id)::text = (cgc.chargegroup_id)::text)
39. 0.005 0.005 ↑ 1.0 62 1

Seq Scan on chargehead_constants chc (cost=0.00..1.62 rows=62 width=30) (actual time=0.003..0.005 rows=62 loops=1)

40. 0.003 0.006 ↑ 1.0 18 1

Hash (cost=1.18..1.18 rows=18 width=156) (actual time=0.006..0.006 rows=18 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
41. 0.003 0.003 ↑ 1.0 18 1

Seq Scan on chargegroup_constants cgc (cost=0.00..1.18 rows=18 width=156) (actual time=0.002..0.003 rows=18 loops=1)

42. 0.006 0.007 ↑ 1.0 21 1

Hash (cost=1.21..1.21 rows=21 width=122) (actual time=0.007..0.007 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 2kB
43. 0.001 0.001 ↑ 1.0 21 1

Seq Scan on bill_account_heads bahc (cost=0.00..1.21 rows=21 width=122) (actual time=0.001..0.001 rows=21 loops=1)

44. 0.111 0.377 ↑ 1.0 921 1

Hash (cost=35.64..35.64 rows=921 width=237) (actual time=0.377..0.377 rows=921 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 60kB
45. 0.178 0.266 ↑ 1.0 921 1

Hash Left Join (cost=1.77..35.64 rows=921 width=237) (actual time=0.016..0.266 rows=921 loops=1)

  • Hash Cond: (ssg.service_group_id = sg.service_group_id)
46. 0.078 0.078 ↑ 1.0 921 1

Seq Scan on service_sub_groups ssg (cost=0.00..21.21 rows=921 width=23) (actual time=0.003..0.078 rows=921 loops=1)

47. 0.005 0.010 ↑ 1.0 34 1

Hash (cost=1.34..1.34 rows=34 width=222) (actual time=0.010..0.010 rows=34 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 2kB
48. 0.005 0.005 ↑ 1.0 34 1

Seq Scan on service_groups sg (cost=0.00..1.34 rows=34 width=222) (actual time=0.002..0.005 rows=34 loops=1)

49. 0.000 0.003 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=122) (actual time=0.003..0.003 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
50. 0.003 0.003 ↑ 1.0 3 1

Seq Scan on card_type_master ctm (cost=0.00..1.03 rows=3 width=122) (actual time=0.002..0.003 rows=3 loops=1)

51. 0.000 0.000 ↓ 0.0 0 1

Hash (cost=11.20..11.20 rows=120 width=520) (actual time=0.000..0.000 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
52. 0.000 0.000 ↓ 0.0 0 1

Seq Scan on foreign_currency fc (cost=0.00..11.20 rows=120 width=520) (actual time=0.000..0.000 rows=0 loops=1)

53. 21.835 26.523 ↑ 1.0 45,328 1

Hash (cost=839.28..839.28 rows=45,328 width=13) (actual time=26.523..26.523 rows=45,328 loops=1)

  • Buckets: 4,096 Batches: 4 Memory Usage: 508kB
54. 4.688 4.688 ↑ 1.0 45,328 1

Seq Scan on store_retail_customers prc (cost=0.00..839.28 rows=45,328 width=13) (actual time=0.002..4.688 rows=45,328 loops=1)

55. 171.290 395.144 ↓ 1.0 520,305 1

Hash (cost=26,071.02..26,071.02 rows=520,302 width=42) (actual time=395.144..395.144 rows=520,305 loops=1)

  • Buckets: 2,048 Batches: 64 Memory Usage: 548kB
56. 223.854 223.854 ↓ 1.0 520,305 1

Seq Scan on patient_registration pr (cost=0.00..26,071.02 rows=520,302 width=42) (actual time=0.007..223.854 rows=520,305 loops=1)

57. 0.002 0.008 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=422) (actual time=0.008..0.008 rows=4 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
58. 0.006 0.006 ↑ 1.0 4 1

Seq Scan on account_group_master bcagm (cost=0.00..1.04 rows=4 width=422) (actual time=0.006..0.006 rows=4 loops=1)

59. 0.013 0.055 ↑ 1.0 100 1

Hash (cost=6.10..6.10 rows=100 width=64) (actual time=0.055..0.055 rows=100 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 6kB
60. 0.006 0.042 ↑ 1.0 100 1

Subquery Scan on tdep (cost=0.00..6.10 rows=100 width=64) (actual time=0.004..0.042 rows=100 loops=1)

61. 0.002 0.036 ↑ 1.0 100 1

Append (cost=0.00..5.10 rows=100 width=123) (actual time=0.003..0.036 rows=100 loops=1)

62. 0.005 0.005 ↑ 1.0 7 1

Seq Scan on services_departments (cost=0.00..1.10 rows=7 width=162) (actual time=0.003..0.005 rows=7 loops=1)

63. 0.004 0.011 ↑ 1.0 25 1

Subquery Scan on "*SELECT* 2" (cost=0.00..1.56 rows=25 width=394) (actual time=0.003..0.011 rows=25 loops=1)

64. 0.007 0.007 ↑ 1.0 25 1

Seq Scan on diagnostics_departments (cost=0.00..1.31 rows=25 width=394) (actual time=0.001..0.007 rows=25 loops=1)

65. 0.005 0.018 ↑ 1.0 68 1

Subquery Scan on "*SELECT* 3" (cost=0.00..2.36 rows=68 width=20) (actual time=0.003..0.018 rows=68 loops=1)

66. 0.013 0.013 ↑ 1.0 68 1

Seq Scan on department (cost=0.00..1.68 rows=68 width=20) (actual time=0.002..0.013 rows=68 loops=1)

67. 0.056 0.088 ↑ 1.0 363 1

Hash (cost=11.63..11.63 rows=363 width=31) (actual time=0.088..0.088 rows=363 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
68. 0.032 0.032 ↑ 1.0 363 1

Seq Scan on tpa_master ptm (cost=0.00..11.63 rows=363 width=31) (actual time=0.002..0.032 rows=363 loops=1)

69. 0.041 0.082 ↑ 1.0 363 1

Hash (cost=11.63..11.63 rows=363 width=31) (actual time=0.082..0.082 rows=363 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
70. 0.041 0.041 ↑ 1.0 363 1

Seq Scan on tpa_master stm (cost=0.00..11.63 rows=363 width=31) (actual time=0.001..0.041 rows=363 loops=1)

71. 0.002 0.006 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=564) (actual time=0.006..0.006 rows=17 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
72. 0.004 0.004 ↑ 1.0 17 1

Seq Scan on incoming_sample_registration isr (cost=0.00..1.17 rows=17 width=564) (actual time=0.002..0.004 rows=17 loops=1)

73. 0.065 0.182 ↑ 1.0 1 13

Hash Right Join (cost=7.81..9.05 rows=1 width=149) (actual time=0.011..0.014 rows=1 loops=13)

  • Hash Cond: ((smb.salutation_id)::text = (pd.salutation)::text)
74. 0.013 0.013 ↑ 1.0 17 13

Seq Scan on salutation_master smb (cost=0.00..1.17 rows=17 width=156) (actual time=0.000..0.001 rows=17 loops=13)

75. 0.013 0.104 ↑ 1.0 1 13

Hash (cost=7.79..7.79 rows=1 width=40) (actual time=0.008..0.008 rows=1 loops=13)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
76. 0.091 0.091 ↑ 1.0 1 13

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..7.79 rows=1 width=40) (actual time=0.007..0.007 rows=1 loops=13)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
77. 0.016 0.035 ↑ 1.0 141 1

Hash (cost=4.41..4.41 rows=141 width=22) (actual time=0.035..0.035 rows=141 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
78. 0.019 0.019 ↑ 1.0 141 1

Seq Scan on patient_category_master prcm (cost=0.00..4.41 rows=141 width=22) (actual time=0.002..0.019 rows=141 loops=1)

Total runtime : 2,031.551 ms