explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Abu6 : Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: Optimization for: plan #Dr7b; plan #Cuqw; plan #iBhy; plan #Og9h; plan #O2ZQ; plan #Ms2E; plan #Y0LC; plan #FWxt; plan #02J

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.064 1,971.935 ↑ 861.2 13 1

Hash Left Join (cost=231,400.70..344,909.79 rows=11,195 width=3,015) (actual time=1,969.630..1,971.935 rows=13 loops=1)

  • Hash Cond: (pr.patient_category_id = prcm.category_id)
2. 0.006 1,971.834 ↑ 861.2 13 1

Hash Left Join (cost=231,394.53..343,742.13 rows=11,195 width=3,001) (actual time=1,969.568..1,971.834 rows=13 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
3. 0.022 1,971.822 ↑ 861.2 13 1

Nested Loop Left Join (cost=231,393.15..343,698.76 rows=11,195 width=2,437) (actual time=1,969.559..1,971.822 rows=13 loops=1)

4. 0.008 1,971.618 ↑ 861.2 13 1

Hash Left Join (cost=231,385.34..329,655.22 rows=11,195 width=2,298) (actual time=1,969.528..1,971.618 rows=13 loops=1)

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

Hash Left Join (cost=231,369.17..329,597.04 rows=11,195 width=2,287) (actual time=1,969.442..1,971.529 rows=13 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptm.tpa_id)::text)
6. 0.010 1,971.430 ↑ 861.2 13 1

Hash Left Join (cost=231,353.01..329,429.74 rows=11,195 width=2,275) (actual time=1,969.348..1,971.430 rows=13 loops=1)

  • Hash Cond: ((bc.act_department_id)::text = tdep.dept_id)
7. 0.004 1,971.363 ↑ 861.2 13 1

Hash Left Join (cost=231,345.66..329,299.61 rows=11,195 width=2,250) (actual time=1,969.288..1,971.363 rows=13 loops=1)

  • Hash Cond: (bc.account_group = bcagm.account_group_id)
8. 0.017 1,971.357 ↑ 861.2 13 1

Hash Left Join (cost=231,344.57..329,144.58 rows=11,195 width=1,836) (actual time=1,969.283..1,971.357 rows=13 loops=1)

  • Hash Cond: (chc.account_head_id = bahc.account_head_id)
9. 6.522 1,971.329 ↑ 861.2 13 1

Hash Left Join (cost=231,343.10..328,989.18 rows=11,195 width=1,722) (actual time=1,969.262..1,971.329 rows=13 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
10. 0.924 1,558.661 ↑ 861.2 13 1

Hash Left Join (cost=194,195.30..282,370.47 rows=11,195 width=1,689) (actual time=1,536.207..1,558.661 rows=13 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
11. 0.008 1,546.611 ↑ 861.2 13 1

Hash Left Join (cost=192,567.42..275,781.03 rows=11,195 width=1,676) (actual time=1,506.519..1,546.611 rows=13 loops=1)

  • Hash Cond: (rpt.currency_id = fc.currency_id)
12. 0.011 1,546.603 ↑ 861.2 13 1

Hash Left Join (cost=192,554.72..275,622.93 rows=11,195 width=1,164) (actual time=1,506.516..1,546.603 rows=13 loops=1)

  • Hash Cond: (rpt.card_type_id = ctm.card_type_id)
13. 0.017 1,546.589 ↑ 861.2 13 1

Hash Left Join (cost=192,553.65..275,467.93 rows=11,195 width=1,050) (actual time=1,506.510..1,546.589 rows=13 loops=1)

  • Hash Cond: (((bc.charge_id)::text = (bcra.charge_id)::text) AND (r.bill_receipt_id = bcra.bill_receipt_id))
14. 0.016 1,546.567 ↑ 861.2 13 1

Hash Left Join (cost=192,535.40..274,470.11 rows=11,195 width=1,032) (actual time=1,506.499..1,546.567 rows=13 loops=1)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
15. 0.017 1,546.508 ↑ 861.2 13 1

Hash Left Join (cost=192,530.75..274,311.53 rows=11,195 width=900) (actual time=1,506.451..1,546.508 rows=13 loops=1)

  • Hash Cond: (bc.service_sub_group_id = ssg.service_sub_group_id)
16. 0.017 1,546.108 ↑ 861.2 13 1

Hash Join (cost=192,483.60..274,110.45 rows=11,195 width=671) (actual time=1,506.058..1,546.108 rows=13 loops=1)

  • Hash Cond: (rpt.payment_mode_id = pm.mode_id)
17. 0.014 1,546.080 ↑ 861.2 13 1

Hash Join (cost=192,482.35..273,955.27 rows=11,195 width=597) (actual time=1,506.038..1,546.080 rows=13 loops=1)

  • Hash Cond: (b.visit_type = vtn.visit_type)
18. 0.015 1,546.063 ↑ 861.2 13 1

Hash Join (cost=192,481.26..273,800.25 rows=11,195 width=567) (actual time=1,506.029..1,546.063 rows=13 loops=1)

  • Hash Cond: ((rpt.counter)::text = (c.counter_id)::text)
19. 275.710 1,546.042 ↑ 861.2 13 1

Hash Join (cost=192,480.04..273,645.09 rows=11,195 width=341) (actual time=1,506.015..1,546.042 rows=13 loops=1)

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

21. 0.078 900.819 ↑ 861.2 13 1

Hash (cost=191,891.10..191,891.10 rows=11,195 width=302) (actual time=900.819..900.819 rows=13 loops=1)

  • Buckets: 1,024 Batches: 4 Memory Usage: 2kB
22. 178.234 900.741 ↑ 861.2 13 1

Hash Join (cost=57,988.07..191,891.10 rows=11,195 width=302) (actual time=900.731..900.741 rows=13 loops=1)

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

24. 0.003 413.558 ↑ 763.2 5 1

Hash (cost=57,940.37..57,940.37 rows=3,816 width=223) (actual time=413.558..413.558 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
25. 0.012 413.555 ↑ 763.2 5 1

Hash Left Join (cost=31,430.39..57,940.37 rows=3,816 width=223) (actual time=413.546..413.555 rows=5 loops=1)

  • Hash Cond: ((rpt.receipt_id)::text = (rrr.receipt_id)::text)
26. 55.952 413.383 ↑ 763.2 5 1

Hash Join (cost=31,382.57..57,878.24 rows=3,816 width=232) (actual time=413.375..413.383 rows=5 loops=1)

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

28. 0.005 295.121 ↑ 763.4 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
29. 295.116 295.116 ↑ 763.4 5 1

Seq Scan on receipts rpt (cost=0.00..31,334.86 rows=3,817 width=201) (actual time=295.112..295.116 rows=5 loops=1)

  • Filter: ((NOT is_deposit) AND (amount > 0::numeric) AND (payment_mode_id <> (-9)) AND ((display_date)::date = '2020-08-03'::date))
  • Rows Removed by Filter: 772,645
30. 0.000 0.160 ↓ 0.0 0 1

Hash (cost=47.80..47.80 rows=1 width=9) (actual time=0.160..0.160 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 0kB
31. 0.160 0.160 ↓ 0.0 0 1

Seq Scan on receipt_refund_reference rrr (cost=0.00..47.80 rows=1 width=9) (actual time=0.160..0.160 rows=0 loops=1)

  • Filter: (refund_receipt_id IS NULL)
  • Rows Removed by Filter: 2,581
32. 0.001 0.006 ↑ 1.0 10 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
33. 0.005 0.005 ↑ 1.0 10 1

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

34. 0.000 0.003 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
35. 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.001..0.003 rows=4 loops=1)

36. 0.005 0.011 ↓ 1.4 15 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
37. 0.006 0.006 ↓ 1.4 15 1

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

38. 0.118 0.383 ↑ 1.0 921 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 60kB
39. 0.187 0.265 ↑ 1.0 921 1

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

  • Hash Cond: (ssg.service_group_id = sg.service_group_id)
40. 0.068 0.068 ↑ 1.0 921 1

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

41. 0.006 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
42. 0.004 0.004 ↑ 1.0 34 1

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

43. 0.010 0.043 ↑ 1.0 62 1

Hash (cost=3.88..3.88 rows=62 width=144) (actual time=0.043..0.043 rows=62 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 5kB
44. 0.020 0.033 ↑ 1.0 62 1

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

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

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

46. 0.004 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
47. 0.002 0.002 ↑ 1.0 18 1

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

48. 0.003 0.005 ↑ 36.7 9 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
49. 0.002 0.002 ↑ 36.7 9 1

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

50. 0.002 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
51. 0.001 0.001 ↑ 1.0 3 1

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

52. 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
53. 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)

54. 6.391 11.126 ↑ 1.0 45,328 1

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

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

56. 182.785 406.146 ↓ 1.0 520,305 1

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

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

58. 0.005 0.011 ↑ 1.0 21 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 2kB
59. 0.006 0.006 ↑ 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.006 rows=21 loops=1)

60. 0.000 0.002 ↑ 1.0 4 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
61. 0.002 0.002 ↑ 1.0 4 1

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

62. 0.015 0.057 ↑ 1.0 100 1

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

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

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

64. 0.003 0.032 ↑ 1.0 100 1

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

65. 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)

66. 0.005 0.010 ↑ 1.0 25 1

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

67. 0.005 0.005 ↑ 1.0 25 1

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

68. 0.003 0.014 ↑ 1.0 68 1

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

69. 0.011 0.011 ↑ 1.0 68 1

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

70. 0.038 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
71. 0.052 0.052 ↑ 1.0 363 1

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

72. 0.050 0.081 ↑ 1.0 363 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 23kB
73. 0.031 0.031 ↑ 1.0 363 1

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

74. 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)
75. 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.001..0.001 rows=17 loops=13)

76. 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
77. 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)
78. 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
79. 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)

80. 0.021 0.037 ↑ 1.0 141 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
81. 0.016 0.016 ↑ 1.0 141 1

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

Total runtime : 1,972.528 ms