explain.depesz.com

PostgreSQL's explain analyze made readable

Result: aoJP : Optimization for: plan #cCes

Settings

Optimization path:

Optimization(s) for this plan:

# exclusive inclusive rows x rows loops node
1. 0.011 31,495.637 ↑ 1.0 1 1

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

2. 248.787 31,495.626 ↑ 96.5 4 1

Subquery Scan on dv (cost=198,197.84..586,772.84 rows=386 width=96) (actual time=7,019.793..31,495.626 rows=4 loops=1)

  • Filter: (date(dv.date) = '2020-07-29'::date)
  • Rows Removed by Filter: 772,673
3. 75.392 31,246.839 ↓ 10.0 772,677 1

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

4. 328.997 31,171.310 ↓ 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,391.216..31,171.310 rows=772,672 loops=1)

5. 6,866.120 30,842.313 ↓ 10.0 772,672 1

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

  • Hash Cond: (r.currency_id = fc.currency_id)
6. 202.012 23,976.192 ↓ 10.0 772,672 1

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

  • Hash Cond: (r.card_type_id = ctm.card_type_id)
7. 302.179 23,774.177 ↓ 10.0 772,672 1

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

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

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

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

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

  • Hash Cond: ((r.counter)::text = (c.counter_id)::text)
10. 383.167 22,863.477 ↓ 3.0 772,672 1

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

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

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

  • Hash Cond: (pr.patient_category_id = pcm.category_id)
12. 17,640.227 22,173.144 ↓ 3.0 772,672 1

Hash Right Join (cost=198,171.61..551,656.36 rows=257,548 width=261) (actual time=3,391.066..22,173.144 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. 568.622 2,952.729 ↓ 1.0 772,547 1

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

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

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

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

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

  • Hash Cond: ((br.bill_no)::text = (b.bill_no)::text)
16. 165.858 165.858 ↓ 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.012..165.858 rows=772,547 loops=1)

17. 252.220 482.795 ↓ 1.0 1,080,037 1

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

  • Buckets: 2,048 Batches: 64 Memory Usage: 898kB
18. 230.575 230.575 ↓ 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.007..230.575 rows=1,080,037 loops=1)

19. 6.416 11.180 ↑ 1.0 45,328 1

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

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

21. 151.152 887.133 ↓ 1.0 520,302 1

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

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

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

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
23. 183.724 183.724 ↓ 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.724 rows=520,302 loops=1)

24. 113.811 250.658 ↓ 1.0 284,687 1

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

  • Buckets: 2,048 Batches: 32 Memory Usage: 697kB
25. 136.847 136.847 ↓ 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.003..136.847 rows=284,687 loops=1)

26. 1,353.551 1,580.188 ↓ 1.0 772,647 1

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

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

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

28. 0.017 0.040 ↑ 1.0 141 1

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

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

Seq Scan on patient_category_master pcm (cost=0.00..4.41 rows=141 width=22) (actual time=0.005..0.023 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.002 0.013 ↓ 3.3 10 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
33. 0.008 0.011 ↓ 3.3 10 1

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

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

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

35. 0.000 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
36. 0.002 0.002 ↑ 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.002 rows=3 loops=1)

37. 0.003 0.007 ↑ 1.0 17 1

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

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

39. 0.003 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
40. 0.004 0.004 ↓ 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.004 rows=15 loops=1)

41. 0.001 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
42. 0.002 0.002 ↑ 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.002 rows=3 loops=1)

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

45. 0.004 0.071 ↓ 3.0 3 1

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

46. 0.018 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.008 0.034 ↓ 3.0 3 1

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

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

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

49. 0.001 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.021 ↓ 3.0 3 1

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

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

Hash Join (cost=1.07..2.24 rows=3 width=708) (actual time=0.011..0.013 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.005 ↑ 1.0 3 1

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

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

Seq Scan on payments p (cost=0.00..1.03 rows=3 width=470) (actual time=0.003..0.003 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.000..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.004 0.066 ↓ 2.0 2 1

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

66. 0.006 0.062 ↓ 2.0 2 1

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

67. 0.001 0.056 ↓ 2.0 2 1

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

68. 0.004 0.053 ↓ 2.0 2 1

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

69. 0.000 0.041 ↓ 2.0 2 1

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

70. 0.005 0.025 ↓ 2.0 2 1

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

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

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

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

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

73. 0.001 0.011 ↓ 3.3 10 1

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

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

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

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

Seq Scan on counters c_2 (cost=0.00..1.10 rows=10 width=296) (actual time=0.001..0.002 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.000..0.001 rows=3 loops=1)

78. 0.002 0.004 ↓ 1.4 15 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
79. 0.002 0.002 ↓ 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.002 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 : 31,496.019 ms