explain.depesz.com

PostgreSQL's explain analyze made readable

Result: ZlI5 : Optimization for: Optimization for: plan #cCes; plan #aoJP

Settings

Optimization path:

# exclusive inclusive rows x rows loops node
1. 0.011 22,398.456 ↑ 1.0 1 1

Aggregate (cost=443,440.08..443,440.09 rows=1 width=96) (actual time=22,398.456..22,398.456 rows=1 loops=1)

2. 148.471 22,398.445 ↑ 96.5 4 1

Subquery Scan on dv (cost=338,068.50..443,439.11 rows=386 width=96) (actual time=15,758.038..22,398.445 rows=4 loops=1)

  • Filter: (date(dv.date) = '2020-07-29'::date)
  • Rows Removed by Filter: 727,248
3. 56.519 22,249.974 ↓ 9.4 727,252 1

Append (cost=338,068.50..442,280.36 rows=77,250 width=2,055) (actual time=14,025.021..22,249.974 rows=727,252 loops=1)

4. 217.022 22,193.259 ↓ 9.4 727,247 1

Subquery Scan on "*SELECT* 1" (cost=338,068.50..442,219.93 rows=77,248 width=2,055) (actual time=14,025.021..22,193.259 rows=727,247 loops=1)

5. 5,658.986 21,976.237 ↓ 9.4 727,247 1

Hash Left Join (cost=338,068.50..441,447.45 rows=77,248 width=2,055) (actual time=14,025.018..21,976.237 rows=727,247 loops=1)

  • Hash Cond: (r.currency_id = fc.currency_id)
6. 157.591 16,317.250 ↓ 9.4 727,247 1

Hash Left Join (cost=338,055.80..419,576.55 rows=77,248 width=1,539) (actual time=14,024.959..16,317.250 rows=727,247 loops=1)

  • Hash Cond: (r.card_type_id = ctm.card_type_id)
7. 219.404 16,159.652 ↓ 9.4 727,247 1

Hash Join (cost=338,054.74..418,513.40 rows=77,248 width=1,421) (actual time=14,024.947..16,159.652 rows=727,247 loops=1)

  • Hash Cond: (r.payment_mode_id = pm.mode_id)
8. 177.258 15,940.226 ↓ 9.4 727,247 1

Hash Left Join (cost=338,053.49..417,449.99 rows=77,248 width=1,343) (actual time=14,024.917..15,940.226 rows=727,247 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
9. 243.457 15,762.956 ↓ 9.4 727,247 1

Hash Join (cost=338,052.11..417,158.90 rows=77,248 width=836) (actual time=14,024.900..15,762.956 rows=727,247 loops=1)

  • Hash Cond: ((r.counter)::text = (c.counter_id)::text)
10. 233.970 15,519.469 ↓ 2.8 727,247 1

Hash Left Join (cost=338,049.83..415,418.54 rows=257,495 width=384) (actual time=14,024.864..15,519.469 rows=727,247 loops=1)

  • Hash Cond: ((pd.salutation)::text = (sm.salutation_id)::text)
11. 190.587 15,285.492 ↓ 2.8 727,247 1

Hash Left Join (cost=338,048.45..411,876.60 rows=257,495 width=275) (actual time=14,024.850..15,285.492 rows=727,247 loops=1)

  • Hash Cond: (pr.patient_category_id = pcm.category_id)
12. 843.757 15,094.838 ↓ 2.8 727,247 1

Hash Join (cost=338,042.28..408,329.87 rows=257,495 width=261) (actual time=14,024.772..15,094.838 rows=727,247 loops=1)

  • Hash Cond: ((r.receipt_id)::text = (br.receipt_no)::text)
13. 227.764 227.764 ↓ 1.0 772,647 1

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

14. 276.811 14,023.317 ↓ 2.8 727,247 1

Hash (cost=330,799.59..330,799.59 rows=257,495 width=97) (actual time=14,023.317..14,023.317 rows=727,247 loops=1)

  • Buckets: 1,024 Batches: 128 (originally 64) Memory Usage: 1,025kB
15. 395.923 13,746.506 ↓ 2.8 727,247 1

Hash Left Join (cost=279,884.52..330,799.59 rows=257,495 width=97) (actual time=12,560.347..13,746.506 rows=727,247 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
16. 686.307 13,338.328 ↓ 2.8 727,247 1

Hash Join (cost=278,256.64..319,525.26 rows=257,495 width=93) (actual time=12,494.128..13,338.328 rows=727,247 loops=1)

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

18. 374.751 12,476.119 ↓ 2.9 1,034,697 1

Hash (cost=269,185.49..269,185.49 rows=360,012 width=76) (actual time=12,476.119..12,476.119 rows=1,034,697 loops=1)

  • Buckets: 1,024 Batches: 128 (originally 64) Memory Usage: 1,025kB
19. 679.671 12,101.368 ↓ 2.9 1,034,697 1

Hash Join (cost=195,776.89..269,185.49 rows=360,012 width=76) (actual time=11,224.079..12,101.368 rows=1,034,697 loops=1)

  • Hash Cond: ((b.visit_id)::text = (pr.patient_id)::text)
20. 234.506 234.506 ↓ 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..234.506 rows=1,080,037 loops=1)

21. 246.811 11,187.191 ↓ 3.0 520,302 1

Hash (cost=191,914.96..191,914.96 rows=173,434 width=55) (actual time=11,187.191..11,187.191 rows=520,302 loops=1)

  • Buckets: 2,048 Batches: 64 (originally 16) Memory Usage: 1,025kB
22. 10,478.089 10,940.380 ↓ 3.0 520,302 1

Hash Left Join (cost=17,046.44..191,914.96 rows=173,434 width=55) (actual time=268.375..10,940.380 rows=520,302 loops=1)

  • Hash Cond: ((pr.mr_no)::text = (pd.mr_no)::text)
  • Filter: patient_confidentiality_check(COALESCE(pd.patient_group, 0), pd.mr_no)
23. 194.255 194.255 ↓ 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.009..194.255 rows=520,302 loops=1)

24. 122.313 268.036 ↓ 1.0 284,687 1

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

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

26. 6.570 12.255 ↑ 1.0 45,328 1

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

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

28. 0.021 0.067 ↑ 1.0 141 1

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

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

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

30. 0.004 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.003 0.003 ↑ 1.0 17 1

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

32. 0.002 0.030 ↓ 3.3 10 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
33. 0.013 0.028 ↓ 3.3 10 1

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

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

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

35. 0.000 0.008 ↑ 1.0 3 1

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

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

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

37. 0.003 0.012 ↑ 1.0 17 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
38. 0.009 0.009 ↑ 1.0 17 1

Seq Scan on incoming_sample_registration isr (cost=0.00..1.17 rows=17 width=564) (actual time=0.008..0.009 rows=17 loops=1)

39. 0.005 0.022 ↓ 1.4 15 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 1kB
40. 0.017 0.017 ↓ 1.4 15 1

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

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

43. 0.001 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.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.007 0.099 ↓ 3.0 3 1

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

46. 0.022 0.092 ↓ 3.0 3 1

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

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

Hash Join (cost=3.34..4.50 rows=1 width=1,000) (actual time=0.047..0.049 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.005..0.005 rows=15 loops=1)

49. 0.002 0.033 ↓ 3.0 3 1

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

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

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

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

Hash Join (cost=1.07..2.24 rows=3 width=708) (actual time=0.022..0.023 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.003 0.014 ↑ 1.0 3 1

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

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

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

55. 0.001 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.003 0.003 ↑ 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.003 rows=3 loops=1)

57. 0.000 0.000 ↑ 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.000 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.021 0.021 ↑ 1.0 1 1

Index Scan using referral_pkey on referral (cost=0.28..8.30 rows=1 width=25) (actual time=0.021..0.021 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.097 ↓ 2.0 2 1

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

66. 0.008 0.094 ↓ 2.0 2 1

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

67. 0.002 0.086 ↓ 2.0 2 1

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

68. 0.002 0.078 ↓ 2.0 2 1

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

69. 0.004 0.060 ↓ 2.0 2 1

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

70. 0.004 0.030 ↓ 2.0 2 1

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

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

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

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

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

73. 0.002 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.005 0.009 ↓ 3.3 10 1

Hash Join (cost=1.07..2.24 rows=3 width=510) (actual time=0.006..0.009 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.001..0.001 rows=3 loops=1)

78. 0.003 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.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.026 0.026 ↑ 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.013..0.013 rows=1 loops=2)

  • Index Cond: ((tpa_id)::text = (cs.tpa_id)::text)
81. 0.016 0.016 ↑ 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.008..0.008 rows=1 loops=2)

  • Index Cond: ((insurance_co_id)::text = (cs.insurance_co_id)::text)
82. 0.006 0.006 ↓ 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.003..0.003 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 : 22,399.010 ms