explain.depesz.com

PostgreSQL's explain analyze made readable

Result: FWxt : 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

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.067 1,959.776 ↑ 861.2 13 1

Hash Left Join (cost=231,339.42..345,306.57 rows=11,195 width=3,015) (actual time=1,957.480..1,959.776 rows=13 loops=1)

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

Nested Loop Left Join (cost=231,333.25..344,138.92 rows=11,195 width=3,001) (actual time=1,957.417..1,959.673 rows=13 loops=1)

3. 0.007 1,959.468 ↑ 861.2 13 1

Hash Left Join (cost=231,325.44..330,095.38 rows=11,195 width=2,862) (actual time=1,957.386..1,959.468 rows=13 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
4. 0.007 1,959.452 ↑ 861.2 13 1

Hash Left Join (cost=231,324.06..330,052.01 rows=11,195 width=2,298) (actual time=1,957.374..1,959.452 rows=13 loops=1)

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

Hash Left Join (cost=231,307.89..329,993.83 rows=11,195 width=2,287) (actual time=1,957.289..1,959.363 rows=13 loops=1)

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

Hash Left Join (cost=231,291.73..329,826.53 rows=11,195 width=2,275) (actual time=1,957.194..1,959.265 rows=13 loops=1)

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

Hash Left Join (cost=231,284.38..329,696.39 rows=11,195 width=2,250) (actual time=1,957.123..1,959.188 rows=13 loops=1)

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

Hash Left Join (cost=231,283.29..329,541.37 rows=11,195 width=1,836) (actual time=1,957.106..1,959.165 rows=13 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
9. 8.445 1,563.586 ↑ 861.2 13 1

Hash Left Join (cost=194,135.49..282,616.67 rows=11,195 width=1,803) (actual time=1,541.731..1,563.586 rows=13 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
10. 0.013 1,535.871 ↑ 861.2 13 1

Hash Left Join (cost=192,507.61..275,721.23 rows=11,195 width=1,790) (actual time=1,496.771..1,535.871 rows=13 loops=1)

  • Hash Cond: (rpt.currency_id = fc.currency_id)
11. 0.008 1,535.857 ↑ 861.2 13 1

Hash Left Join (cost=192,494.91..275,563.12 rows=11,195 width=1,278) (actual time=1,496.767..1,535.857 rows=13 loops=1)

  • Hash Cond: (rpt.card_type_id = ctm.card_type_id)
12. 0.014 1,535.847 ↑ 861.2 13 1

Hash Left Join (cost=192,493.85..275,408.13 rows=11,195 width=1,164) (actual time=1,496.761..1,535.847 rows=13 loops=1)

  • Hash Cond: (bc.service_sub_group_id = ssg.service_sub_group_id)
13. 0.016 1,535.436 ↑ 861.2 13 1

Hash Left Join (cost=192,446.69..275,207.04 rows=11,195 width=935) (actual time=1,496.359..1,535.436 rows=13 loops=1)

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

Hash Left Join (cost=192,439.72..275,046.14 rows=11,195 width=689) (actual time=1,496.278..1,535.343 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.015 1,535.302 ↑ 861.2 13 1

Hash Join (cost=192,421.47..274,048.31 rows=11,195 width=671) (actual time=1,496.252..1,535.302 rows=13 loops=1)

  • Hash Cond: (rpt.payment_mode_id = pm.mode_id)
16. 0.016 1,535.280 ↑ 861.2 13 1

Hash Join (cost=192,420.22..273,893.13 rows=11,195 width=597) (actual time=1,496.238..1,535.280 rows=13 loops=1)

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

Hash Join (cost=192,419.13..273,738.11 rows=11,195 width=567) (actual time=1,496.226..1,535.257 rows=13 loops=1)

  • Hash Cond: ((rpt.counter)::text = (c.counter_id)::text)
18. 269.097 1,535.234 ↑ 861.2 13 1

Hash Join (cost=192,417.90..273,582.96 rows=11,195 width=341) (actual time=1,496.213..1,535.234 rows=13 loops=1)

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

20. 6.279 893.961 ↑ 861.2 13 1

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

  • Buckets: 1,024 Batches: 4 Memory Usage: 2kB
21. 177.716 887.682 ↑ 861.2 13 1

Hash Join (cost=57,925.94..191,828.97 rows=11,195 width=302) (actual time=887.662..887.682 rows=13 loops=1)

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

23. 0.002 402.423 ↑ 763.2 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
24. 55.839 402.421 ↑ 763.2 5 1

Hash Join (cost=31,382.57..57,878.24 rows=3,816 width=223) (actual time=402.412..402.421 rows=5 loops=1)

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

26. 0.006 282.598 ↑ 763.4 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
27. 282.592 282.592 ↑ 763.4 5 1

Seq Scan on receipts rpt (cost=0.00..31,334.86 rows=3,817 width=201) (actual time=282.587..282.592 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
28. 0.002 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.006 0.006 ↑ 1.0 10 1

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

30. 0.002 0.007 ↑ 1.0 4 1

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

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

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

32. 0.002 0.007 ↓ 1.4 15 1

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

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

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

34. 0.004 0.013 ↑ 36.7 9 1

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

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

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

36. 0.014 0.077 ↑ 1.0 62 1

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

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

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

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

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

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

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

40. 0.003 0.009 ↑ 1.0 18 1

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

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

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

42. 0.004 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
43. 0.007 0.007 ↑ 1.0 21 1

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

44. 0.124 0.397 ↑ 1.0 921 1

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

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

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

  • Hash Cond: (ssg.service_group_id = sg.service_group_id)
46. 0.075 0.075 ↑ 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.075 rows=921 loops=1)

47. 0.003 0.013 ↑ 1.0 34 1

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

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

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

49. 0.001 0.002 ↑ 1.0 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
50. 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)

51. 0.000 0.001 ↓ 0.0 0 1

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

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

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

53. 14.067 19.270 ↑ 1.0 45,328 1

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

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

55. 166.529 389.106 ↓ 1.0 520,305 1

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

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

57. 0.001 0.007 ↑ 1.0 4 1

Hash (cost=1.04..1.04 rows=4 width=422) (actual time=0.007..0.007 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.005..0.006 rows=4 loops=1)

59. 0.015 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.006 0.051 ↑ 1.0 100 1

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

61. 0.005 0.045 ↑ 1.0 100 1

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

62. 0.010 0.010 ↑ 1.0 7 1

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

63. 0.005 0.015 ↑ 1.0 25 1

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

64. 0.010 0.010 ↑ 1.0 25 1

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

65. 0.005 0.015 ↑ 1.0 68 1

Subquery Scan on "*SELECT* 3" (cost=0.00..2.36 rows=68 width=20) (actual time=0.003..0.015 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.052 0.092 ↑ 1.0 363 1

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

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

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

69. 0.040 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.042 0.042 ↑ 1.0 363 1

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

71. 0.003 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.006 0.006 ↑ 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.006 rows=17 loops=1)

73. 0.078 0.195 ↑ 1.0 1 13

Hash Right Join (cost=7.81..9.05 rows=1 width=149) (actual time=0.011..0.015 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.001..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.017 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.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 : 1,960.126 ms