explain.depesz.com

PostgreSQL's explain analyze made readable

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

Settings

Optimization path:

Optimization(s) for this plan:

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

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

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

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

3. 0.008 1,965.638 ↑ 865.0 13 1

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

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
4. 0.005 1,965.621 ↑ 865.0 13 1

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

  • Hash Cond: ((pr.secondary_sponsor_id)::text = (stm.tpa_id)::text)
5. 0.009 1,965.534 ↑ 865.0 13 1

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

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptm.tpa_id)::text)
6. 0.009 1,965.435 ↑ 865.0 13 1

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

  • Hash Cond: ((bc.act_department_id)::text = tdep.dept_id)
7. 0.016 1,965.360 ↑ 865.0 13 1

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

  • Hash Cond: (bc.account_group = bcagm.account_group_id)
8. 7.159 1,965.336 ↑ 865.0 13 1

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

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

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

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

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

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

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

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

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

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

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

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

Hash Left Join (cost=192,443.44..275,058.79 rows=11,245 width=689) (actual time=1,485.168..1,524.907 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.012 1,524.863 ↑ 865.0 13 1

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

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

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

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

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

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

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

  • Hash Cond: ((b.bill_no)::text = (r.bill_no)::text)
19. 374.061 374.061 ↑ 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.004..374.061 rows=1,080,041 loops=1)

20. 15.140 878.271 ↑ 865.0 13 1

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

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

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

  • Hash Cond: ((bc.bill_no)::text = (r.bill_no)::text)
22. 308.298 308.298 ↑ 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.003..308.298 rows=2,239,924 loops=1)

23. 0.005 377.185 ↑ 766.6 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
24. 55.820 377.180 ↑ 766.6 5 1

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

  • Hash Cond: ((r.receipt_no)::text = (rpt.receipt_id)::text)
25. 64.245 64.245 ↓ 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.004..64.245 rows=772,550 loops=1)

26. 0.006 257.115 ↑ 766.8 5 1

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

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

Seq Scan on receipts rpt (cost=0.00..31,334.86 rows=3,834 width=201) (actual time=257.103..257.109 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.003 0.007 ↑ 1.0 10 1

Hash (cost=1.10..1.10 rows=10 width=292) (actual time=0.007..0.007 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.003..0.004 rows=10 loops=1)

30. 0.002 0.006 ↑ 1.0 4 1

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

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

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

32. 0.003 0.012 ↓ 1.4 15 1

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

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

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

34. 0.003 0.015 ↑ 36.7 9 1

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

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

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

36. 0.008 0.076 ↑ 1.0 62 1

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

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

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

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

Hash Left Join (cost=1.41..3.88 rows=62 width=144) (actual time=0.017..0.035 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.010 ↑ 1.0 18 1

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

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

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

42. 0.001 0.010 ↑ 1.0 21 1

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

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

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

44. 0.123 0.433 ↑ 1.0 921 1

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

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

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

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

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

47. 0.006 0.011 ↑ 1.0 34 1

Hash (cost=1.34..1.34 rows=34 width=222) (actual time=0.011..0.011 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.001 0.007 ↑ 1.0 3 1

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

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

Seq Scan on card_type_master ctm (cost=0.00..1.03 rows=3 width=122) (actual time=0.006..0.006 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. 20.795 26.019 ↑ 1.0 45,328 1

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

  • Buckets: 4,096 Batches: 4 Memory Usage: 508kB
54. 5.224 5.224 ↑ 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.006..5.224 rows=45,328 loops=1)

55. 182.659 405.714 ↓ 1.0 520,305 1

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

  • Buckets: 2,048 Batches: 64 Memory Usage: 548kB
56. 223.055 223.055 ↓ 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.055 rows=520,305 loops=1)

57. 0.001 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.007 0.007 ↑ 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.007 rows=4 loops=1)

59. 0.014 0.066 ↑ 1.0 100 1

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

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

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

61. 0.007 0.045 ↑ 1.0 100 1

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

62. 0.011 0.011 ↑ 1.0 7 1

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

63. 0.002 0.014 ↑ 1.0 25 1

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

64. 0.012 0.012 ↑ 1.0 25 1

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

65. 0.003 0.013 ↑ 1.0 68 1

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

66. 0.010 0.010 ↑ 1.0 68 1

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

67. 0.043 0.090 ↑ 1.0 363 1

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

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

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

69. 0.049 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.033 0.033 ↑ 1.0 363 1

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

71. 0.002 0.009 ↑ 1.0 17 1

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

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

Seq Scan on incoming_sample_registration isr (cost=0.00..1.17 rows=17 width=564) (actual time=0.005..0.007 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.018 0.036 ↑ 1.0 141 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
78. 0.018 0.018 ↑ 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.018 rows=141 loops=1)

Total runtime : 1,966.293 ms