explain.depesz.com

PostgreSQL's explain analyze made readable

Result: cCes

Settings

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.001 32,042.943 ↑ 1.0 1 1

Aggregate (cost=586,773.80..586,773.81 rows=1 width=96) (actual time=32,042.943..32,042.943 rows=1 loops=1)

2. 250.634 32,042.942 ↓ 0.0 0 1

Subquery Scan on dv (cost=198,197.84..586,772.84 rows=386 width=96) (actual time=32,042.942..32,042.942 rows=0 loops=1)

  • Filter: (date(dv.date) = '2020-07-28'::date)
  • Rows Removed by Filter: 772,677
3. 76.841 31,792.308 ↓ 10.0 772,677 1

Append (cost=198,197.84..585,613.85 rows=77,266 width=2,055) (actual time=3,220.105..31,792.308 rows=772,677 loops=1)

4. 332.125 31,715.323 ↓ 10.0 772,672 1

Subquery Scan on "*SELECT* 1" (cost=198,197.84..585,553.41 rows=77,264 width=2,055) (actual time=3,220.105..31,715.323 rows=772,672 loops=1)

5. 6,933.319 31,383.198 ↓ 10.0 772,672 1

Hash Left Join (cost=198,197.84..584,780.77 rows=77,264 width=2,055) (actual time=3,220.102..31,383.198 rows=772,672 loops=1)

  • Hash Cond: (r.currency_id = fc.currency_id)
6. 203.805 24,449.879 ↓ 10.0 772,672 1

Hash Left Join (cost=198,185.14..562,905.35 rows=77,264 width=1,539) (actual time=3,220.059..24,449.879 rows=772,672 loops=1)

  • Hash Cond: (r.card_type_id = ctm.card_type_id)
7. 307.094 24,246.072 ↓ 10.0 772,672 1

Hash Join (cost=198,184.07..561,841.99 rows=77,264 width=1,421) (actual time=3,220.054..24,246.072 rows=772,672 loops=1)

  • Hash Cond: (r.payment_mode_id = pm.mode_id)
8. 261.748 23,938.958 ↓ 10.0 772,672 1

Hash Left Join (cost=198,182.82..560,778.36 rows=77,264 width=1,343) (actual time=3,220.023..23,938.958 rows=772,672 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
9. 354.520 23,677.202 ↓ 10.0 772,672 1

Hash Join (cost=198,181.44..560,487.21 rows=77,264 width=836) (actual time=3,220.011..23,677.202 rows=772,672 loops=1)

  • Hash Cond: ((r.counter)::text = (c.counter_id)::text)
10. 389.006 23,322.648 ↓ 3.0 772,672 1

Hash Left Join (cost=198,179.17..558,746.49 rows=257,548 width=384) (actual time=3,219.970..23,322.648 rows=772,672 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
11. 308.920 22,933.635 ↓ 3.0 772,672 1

Hash Left Join (cost=198,177.78..555,203.82 rows=257,548 width=275) (actual time=3,219.959..22,933.635 rows=772,672 loops=1)

  • Hash Cond: (pr.patient_category_id = pcm.category_id)
12. 18,297.204 22,624.676 ↓ 3.0 772,672 1

Hash Right Join (cost=198,171.61..551,656.36 rows=257,548 width=261) (actual time=3,219.913..22,624.676 rows=772,672 loops=1)

  • Hash Cond: ((br.receipt_no)::text = (r.receipt_id)::text)
  • Filter: patient_confidentiality_check(COALESCE(pd.patient_group, 0), pd.mr_no)
13. 570.373 3,590.939 ↓ 1.0 772,547 1

Hash Left Join (cost=146,797.14..252,356.76 rows=772,485 width=101) (actual time=2,047.007..3,590.939 rows=772,547 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
14. 305.970 1,925.353 ↓ 1.0 772,547 1

Hash Left Join (cost=72,863.67..145,734.80 rows=772,485 width=42) (actual time=935.547..1,925.353 rows=772,547 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
15. 845.442 1,599.025 ↓ 1.0 772,547 1

Hash Left Join (cost=71,235.79..126,171.55 rows=772,485 width=38) (actual time=632.544..1,599.025 rows=772,547 loops=1)

  • Hash Cond: ((br.bill_no)::text = (b.bill_no)::text)
16. 164.431 164.431 ↓ 1.0 772,547 1

Seq Scan on bill_receipts br (cost=0.00..20,663.85 rows=772,485 width=25) (actual time=0.011..164.431 rows=772,547 loops=1)

17. 356.078 589.152 ↓ 1.0 1,080,037 1

Hash (cost=51,406.35..51,406.35 rows=1,080,035 width=21) (actual time=589.152..589.152 rows=1,080,037 loops=1)

  • Buckets: 2,048 Batches: 64 Memory Usage: 898kB
18. 233.074 233.074 ↓ 1.0 1,080,037 1

Seq Scan on bill b (cost=0.00..51,406.35 rows=1,080,035 width=21) (actual time=0.008..233.074 rows=1,080,037 loops=1)

19. 15.641 20.358 ↑ 1.0 45,328 1

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

  • Buckets: 4,096 Batches: 4 Memory Usage: 511kB
20. 4.717 4.717 ↑ 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..4.717 rows=45,328 loops=1)

21. 338.252 1,095.213 ↓ 1.0 520,302 1

Hash (cost=61,839.71..61,839.71 rows=520,301 width=59) (actual time=1,095.213..1,095.213 rows=520,302 loops=1)

  • Buckets: 2,048 Batches: 64 Memory Usage: 790kB
22. 329.996 756.961 ↓ 1.0 520,302 1

Hash Left Join (cost=17,046.44..61,839.71 rows=520,301 width=59) (actual time=263.952..756.961 rows=520,302 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
23. 183.528 183.528 ↓ 1.0 520,302 1

Seq Scan on patient_registration pr (cost=0.00..26,071.01 rows=520,301 width=25) (actual time=0.004..183.528 rows=520,302 loops=1)

24. 128.038 243.437 ↓ 1.0 284,687 1

Hash (cost=10,984.86..10,984.86 rows=284,686 width=44) (actual time=243.437..243.437 rows=284,687 loops=1)

  • Buckets: 2,048 Batches: 32 Memory Usage: 697kB
25. 115.399 115.399 ↓ 1.0 284,687 1

Seq Scan on patient_details pd (cost=0.00..10,984.86 rows=284,686 width=44) (actual time=0.004..115.399 rows=284,687 loops=1)

26. 479.482 736.533 ↓ 1.0 772,647 1

Hash (cost=23,607.43..23,607.43 rows=772,643 width=164) (actual time=736.533..736.533 rows=772,647 loops=1)

  • Buckets: 1,024 Batches: 256 Memory Usage: 301kB
27. 257.051 257.051 ↓ 1.0 772,647 1

Seq Scan on receipts r (cost=0.00..23,607.43 rows=772,643 width=164) (actual time=0.050..257.051 rows=772,647 loops=1)

28. 0.020 0.039 ↑ 1.0 141 1

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

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

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

30. 0.002 0.007 ↑ 1.0 17 1

Hash (cost=1.17..1.17 rows=17 width=156) (actual time=0.007..0.007 rows=17 loops=1)

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

Seq Scan on salutation_master sm (cost=0.00..1.17 rows=17 width=156) (actual time=0.002..0.005 rows=17 loops=1)

32. 0.007 0.034 ↓ 3.3 10 1

Hash (cost=2.24..2.24 rows=3 width=510) (actual time=0.034..0.034 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
33. 0.018 0.027 ↓ 3.3 10 1

Hash Join (cost=1.07..2.24 rows=3 width=510) (actual time=0.017..0.027 rows=10 loops=1)

  • Hash Cond: (c.center_id = hcm.center_id)
34. 0.004 0.004 ↑ 1.0 10 1

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

35. 0.002 0.005 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=222) (actual time=0.005..0.005 rows=3 loops=1)

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

Seq Scan on hospital_center_master hcm (cost=0.00..1.03 rows=3 width=222) (actual time=0.002..0.003 rows=3 loops=1)

37. 0.002 0.008 ↑ 1.0 17 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
38. 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.003..0.006 rows=17 loops=1)

39. 0.009 0.020 ↓ 1.4 15 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
40. 0.011 0.011 ↓ 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.011 rows=15 loops=1)

41. 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
42. 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)

43. 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
44. 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)

45. 0.008 0.075 ↓ 3.0 3 1

Subquery Scan on "*SELECT* 2" (cost=3.34..41.50 rows=1 width=1,118) (actual time=0.064..0.075 rows=3 loops=1)

46. 0.019 0.067 ↓ 3.0 3 1

Nested Loop Left Join (cost=3.34..41.49 rows=1 width=1,118) (actual time=0.059..0.067 rows=3 loops=1)

  • Join Filter: (p.card_type_id = ctm_1.card_type_id)
  • Rows Removed by Join Filter: 9
47. 0.006 0.033 ↓ 3.0 3 1

Hash Join (cost=3.34..4.50 rows=1 width=1,000) (actual time=0.032..0.033 rows=3 loops=1)

  • Hash Cond: (pm_1.mode_id = p.payment_mode_id)
48. 0.005 0.005 ↓ 1.4 15 1

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

49. 0.002 0.022 ↓ 3.0 3 1

Hash (cost=3.32..3.32 rows=1 width=922) (actual time=0.022..0.022 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
50. 0.004 0.020 ↓ 3.0 3 1

Hash Join (cost=2.14..3.32 rows=1 width=922) (actual time=0.018..0.020 rows=3 loops=1)

  • Hash Cond: (c_1.center_id = hcm_1.center_id)
51. 0.007 0.012 ↑ 1.0 3 1

Hash Join (cost=1.07..2.24 rows=3 width=708) (actual time=0.011..0.012 rows=3 loops=1)

  • Hash Cond: ((c_1.counter_id)::text = (p.counter)::text)
52. 0.001 0.001 ↑ 1.0 10 1

Seq Scan on counters c_1 (cost=0.00..1.10 rows=10 width=296) (actual time=0.001..0.001 rows=10 loops=1)

53. 0.002 0.004 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=470) (actual time=0.004..0.004 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
54. 0.002 0.002 ↑ 1.0 3 1

Seq Scan on payments p (cost=0.00..1.03 rows=3 width=470) (actual time=0.002..0.002 rows=3 loops=1)

55. 0.002 0.004 ↑ 1.0 3 1

Hash (cost=1.03..1.03 rows=3 width=222) (actual time=0.004..0.004 rows=3 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
56. 0.002 0.002 ↑ 1.0 3 1

Seq Scan on hospital_center_master hcm_1 (cost=0.00..1.03 rows=3 width=222) (actual time=0.002..0.002 rows=3 loops=1)

57. 0.003 0.003 ↑ 1.0 3 3

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

58.          

SubPlan (for Nested Loop Left Join)

59. 0.000 0.000 ↓ 0.0 0

Seq Scan on doctors (cost=0.00..6.06 rows=1 width=516) (never executed)

  • Filter: ((doctor_id)::text = (p.payee_name)::text)
60. 0.000 0.000 ↓ 0.0 0

Seq Scan on doctors doctors_1 (cost=0.00..6.06 rows=1 width=516) (never executed)

  • Filter: ((doctor_id)::text = (p.payee_name)::text)
61. 0.012 0.012 ↑ 1.0 1 1

Index Scan using referral_pkey on referral (cost=0.28..8.30 rows=1 width=25) (actual time=0.012..0.012 rows=1 loops=1)

  • Index Cond: ((referal_no)::text = (p.payee_name)::text)
62. 0.000 0.000 ↓ 0.0 0

Seq Scan on outhouse_master (cost=0.00..1.12 rows=1 width=218) (never executed)

  • Filter: ((oh_id)::text = (p.payee_name)::text)
63. 0.000 0.000 ↓ 0.0 0

Seq Scan on doctors doctors_2 (cost=0.00..6.06 rows=1 width=516) (never executed)

  • Filter: ((doctor_id)::text = (p.payee_name)::text)
64. 0.000 0.000 ↓ 0.0 0

Index Scan using supplier_master_pkey on supplier_master (cost=0.27..8.29 rows=1 width=24) (never executed)

  • Index Cond: ((supplier_code)::text = (p.payee_name)::text)
65. 0.003 0.069 ↓ 2.0 2 1

Subquery Scan on "*SELECT* 3" (cost=4.21..18.94 rows=1 width=3,014) (actual time=0.055..0.069 rows=2 loops=1)

66. 0.007 0.066 ↓ 2.0 2 1

Nested Loop Left Join (cost=4.21..18.93 rows=1 width=3,014) (actual time=0.052..0.066 rows=2 loops=1)

67. 0.002 0.059 ↓ 2.0 2 1

Nested Loop Left Join (cost=4.07..17.28 rows=1 width=2,498) (actual time=0.046..0.059 rows=2 loops=1)

68. 0.003 0.055 ↓ 2.0 2 1

Nested Loop Left Join (cost=3.94..16.72 rows=1 width=2,380) (actual time=0.043..0.055 rows=2 loops=1)

69. 0.001 0.044 ↓ 2.0 2 1

Nested Loop Left Join (cost=3.79..15.88 rows=1 width=2,393) (actual time=0.036..0.044 rows=2 loops=1)

70. 0.003 0.027 ↓ 2.0 2 1

Hash Join (cost=3.52..13.98 rows=1 width=2,420) (actual time=0.025..0.027 rows=2 loops=1)

  • Hash Cond: (cs.payment_mode_id = pm_2.mode_id)
71. 0.005 0.019 ↑ 1.5 2 1

Hash Join (cost=2.27..12.71 rows=3 width=2,342) (actual time=0.017..0.019 rows=2 loops=1)

  • Hash Cond: ((cs.counter)::text = (c_2.counter_id)::text)
72. 0.002 0.002 ↑ 15.0 2 1

Seq Scan on insurance_claim_receipt cs (cost=0.00..10.30 rows=30 width=1,890) (actual time=0.002..0.002 rows=2 loops=1)

73. 0.002 0.012 ↓ 3.3 10 1

Hash (cost=2.24..2.24 rows=3 width=510) (actual time=0.012..0.012 rows=10 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
74. 0.007 0.010 ↓ 3.3 10 1

Hash Join (cost=1.07..2.24 rows=3 width=510) (actual time=0.007..0.010 rows=10 loops=1)

  • Hash Cond: (c_2.center_id = hcm_2.center_id)
75. 0.001 0.001 ↑ 1.0 10 1

Seq Scan on counters c_2 (cost=0.00..1.10 rows=10 width=296) (actual time=0.000..0.001 rows=10 loops=1)

76. 0.001 0.002 ↑ 1.0 3 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
77. 0.001 0.001 ↑ 1.0 3 1

Seq Scan on hospital_center_master hcm_2 (cost=0.00..1.03 rows=3 width=222) (actual time=0.001..0.001 rows=3 loops=1)

78. 0.002 0.005 ↓ 1.4 15 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
79. 0.003 0.003 ↓ 1.4 15 1

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

80. 0.016 0.016 ↑ 1.0 1 2

Index Scan using tpa_master_pkey on tpa_master tp (cost=0.27..1.89 rows=1 width=31) (actual time=0.008..0.008 rows=1 loops=2)

  • Index Cond: ((tpa_id)::text = (cs.tpa_id)::text)
81. 0.008 0.008 ↑ 1.0 1 2

Index Scan using insurance_company_master_pkey on insurance_company_master ic (cost=0.14..0.83 rows=1 width=33) (actual time=0.004..0.004 rows=1 loops=2)

  • Index Cond: ((insurance_co_id)::text = (cs.insurance_co_id)::text)
82. 0.002 0.002 ↓ 0.0 0 2

Index Scan using card_type_master_pkey on card_type_master ctm_2 (cost=0.13..0.55 rows=1 width=122) (actual time=0.001..0.001 rows=0 loops=2)

  • Index Cond: (cs.card_type_id = card_type_id)
83. 0.000 0.000 ↓ 0.0 0 2

Index Scan using foreign_currency_pkey on foreign_currency fc_1 (cost=0.14..1.63 rows=1 width=520) (actual time=0.000..0.000 rows=0 loops=2)

  • Index Cond: (currency_id = cs.currency_id)
Total runtime : 32,043.851 ms