explain.depesz.com

PostgreSQL's explain analyze made readable

Result: JXtr

Settings
# exclusive inclusive rows x rows loops node
1. 40.637 376,930.459 ↓ 1.4 14,486 1

Sort (cost=9,301,610.85..9,301,636.46 rows=10,247 width=707) (actual time=376,928.939..376,930.459 rows=14,486 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 7,466kB
2. 13.924 376,889.822 ↓ 1.4 14,486 1

Nested Loop Left Join (cost=2,962,697.53..9,300,928.25 rows=10,247 width=707) (actual time=375,780.365..376,889.822 rows=14,486 loops=1)

3. 5.909 376,760.010 ↓ 1.4 14,486 1

Hash Left Join (cost=2,962,697.24..9,297,183.61 rows=10,247 width=551) (actual time=375,780.316..376,760.010 rows=14,486 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
4. 7.087 376,753.604 ↓ 1.4 14,486 1

Nested Loop Left Join (cost=2,962,622.36..9,297,081.40 rows=10,247 width=537) (actual time=375,779.784..376,753.604 rows=14,486 loops=1)

5. 5.262 376,732.031 ↓ 1.4 14,486 1

Hash Left Join (cost=2,962,621.94..9,292,385.88 rows=10,247 width=522) (actual time=375,779.737..376,732.031 rows=14,486 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (rdoc.doctor_id)::text)
6. 6.915 376,724.940 ↓ 1.4 14,486 1

Hash Left Join (cost=2,962,407.44..9,292,144.45 rows=10,247 width=500) (actual time=375,777.838..376,724.940 rows=14,486 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
7. 6.016 376,715.980 ↓ 1.4 14,486 1

Hash Left Join (cost=2,962,192.94..9,291,902.89 rows=10,247 width=486) (actual time=375,775.734..376,715.980 rows=14,486 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
8. 3.139 376,709.941 ↓ 1.4 14,486 1

Nested Loop Left Join (cost=2,962,191.63..9,291,810.91 rows=10,247 width=470) (actual time=375,775.481..376,709.941 rows=14,486 loops=1)

9. 6.101 376,446.054 ↓ 1.4 14,486 1

Hash Left Join (cost=2,962,190.24..9,274,841.55 rows=10,247 width=482) (actual time=375,771.862..376,446.054 rows=14,486 loops=1)

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
10. 11.683 376,439.912 ↓ 1.4 14,486 1

Nested Loop Left Join (cost=2,962,187.93..9,274,797.15 rows=10,247 width=486) (actual time=375,771.735..376,439.912 rows=14,486 loops=1)

11. 28.240 376,109.537 ↓ 1.4 14,486 1

Hash Left Join (cost=2,962,187.50..9,269,732.83 rows=10,247 width=452) (actual time=375,771.577..376,109.537 rows=14,486 loops=1)

  • Hash Cond: ((b.visit_id)::text = (isr.incoming_visit_id)::text)
  • Filter: ((pr.center_id = bhcm.center_id) OR (isr.center_id = bhcm.center_id) OR (prc.center_id = bhcm.center_id))
  • Rows Removed by Filter: 36,685
12. 9.492 375,801.293 ↑ 4.0 51,171 1

Nested Loop (cost=2,926,361.25..9,233,365.52 rows=206,119 width=438) (actual time=375,489.190..375,801.293 rows=51,171 loops=1)

13. 0.052 0.052 ↑ 1.0 1 1

Seq Scan on hospital_center_master bhcm (cost=0.00..5.43 rows=1 width=22) (actual time=0.036..0.052 rows=1 loops=1)

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 43
14. 13.825 375,791.749 ↑ 4.0 51,171 1

Hash Left Join (cost=2,926,361.25..9,227,176.52 rows=206,119 width=416) (actual time=375,489.148..375,791.749 rows=51,171 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
15. 0.000 375,777.921 ↑ 4.0 51,171 1

Hash Left Join (cost=2,926,333.75..9,226,607.96 rows=206,119 width=294) (actual time=375,489.129..375,777.921 rows=51,171 loops=1)

  • Hash Cond: ((bc.act_department_id)::text = tdep.dept_id)
16. 47.974 375,807.203 ↑ 4.0 51,171 1

Gather (cost=2,926,316.29..9,221,701.52 rows=206,119 width=269) (actual time=375,489.004..375,807.203 rows=51,171 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
17. 6.684 375,759.229 ↑ 5.0 17,057 3 / 3

Hash Left Join (cost=2,925,316.29..9,200,089.62 rows=85,883 width=269) (actual time=375,479.659..375,759.229 rows=17,057 loops=3)

  • Hash Cond: ((bc.prescribing_dr_id)::text = (predoc.doctor_id)::text)
18. 6.772 375,751.114 ↑ 5.0 17,057 3 / 3

Hash Join (cost=2,925,101.79..9,199,648.99 rows=85,883 width=254) (actual time=375,478.110..375,751.114 rows=17,057 loops=3)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
19. 12,158.070 375,744.287 ↑ 5.0 17,057 3 / 3

Merge Left Join (cost=2,925,097.04..9,199,374.26 rows=85,883 width=244) (actual time=375,477.990..375,744.287 rows=17,057 loops=3)

  • Merge Cond: ((bc.charge_id)::text = (bill_charge_claim.charge_id)::text)
20. 83.603 4,938.804 ↑ 5.0 17,057 3 / 3

Sort (cost=2,925,096.47..2,925,311.18 rows=85,883 width=222) (actual time=4,933.514..4,938.804 rows=17,057 loops=3)

  • Sort Key: bc.charge_id
  • Sort Method: quicksort Memory: 6,443kB
21. 10.121 4,855.201 ↑ 5.0 17,057 3 / 3

Nested Loop (cost=1.12..2,918,058.32 rows=85,883 width=222) (actual time=7.884..4,855.201 rows=17,057 loops=3)

22. 10.311 1,790.855 ↑ 4.6 4,111 3 / 3

Nested Loop Left Join (cost=0.56..681,784.46 rows=18,957 width=122) (actual time=7.376..1,790.855 rows=4,111 loops=3)

23. 1,694.220 1,694.220 ↑ 4.6 4,111 3 / 3

Parallel Seq Scan on bill b (cost=0.00..607,840.42 rows=18,957 width=56) (actual time=7.288..1,694.220 rows=4,111 loops=3)

  • Filter: ((status <> 'X'::bpchar) AND (date(open_date) >= '2020-01-06'::date) AND (date(open_date) <= '2020-01-06'::date))
  • Rows Removed by Filter: 3,128,297
24. 86.324 86.324 ↑ 1.0 1 12,332 / 3

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.56..3.90 rows=1 width=66) (actual time=0.021..0.021 rows=1 loops=12,332)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
25. 3,054.225 3,054.225 ↑ 48.2 4 12,332 / 3

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.56..112.18 rows=193 width=114) (actual time=0.247..0.743 rows=4 loops=12,332)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 0
26. 40,210.308 358,647.413 ↑ 1.0 36,682,530 3 / 3

GroupAggregate (cost=0.56..5,061,499.95 rows=37,237,118 width=74) (actual time=0.090..358,647.413 rows=36,682,530 loops=3)

  • Group Key: bill_charge_claim.charge_id
27. 318,437.105 318,437.105 ↑ 1.1 36,823,746 3 / 3

Index Scan using bill_charge_claim_charge_id on bill_charge_claim (cost=0.56..3,653,761.77 rows=39,506,368 width=14) (actual time=0.060..318,437.105 rows=36,823,746 loops=3)

28. 0.020 0.055 ↑ 1.0 60 3 / 3

Hash (cost=2.80..2.80 rows=60 width=22) (actual time=0.055..0.055 rows=60 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 12kB
29. 0.035 0.035 ↑ 1.0 60 3 / 3

Seq Scan on chargehead_constants chc (cost=0.00..2.80 rows=60 width=22) (actual time=0.025..0.035 rows=60 loops=3)

30. 0.755 1.431 ↑ 1.0 2,072 3 / 3

Hash (cost=147.16..147.16 rows=2,072 width=30) (actual time=1.431..1.431 rows=2,072 loops=3)

  • Buckets: 4,096 Batches: 1 Memory Usage: 160kB
31. 0.676 0.676 ↑ 1.0 2,072 3 / 3

Seq Scan on doctors predoc (cost=0.00..147.16 rows=2,072 width=30) (actual time=0.044..0.676 rows=2,072 loops=3)

32. 0.026 0.102 ↑ 1.0 116 1

Hash (cost=13.69..13.69 rows=116 width=64) (actual time=0.102..0.102 rows=116 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
33. 0.012 0.076 ↑ 1.0 116 1

Subquery Scan on tdep (cost=0.00..13.69 rows=116 width=64) (actual time=0.018..0.076 rows=116 loops=1)

34. 0.012 0.064 ↑ 1.0 116 1

Append (cost=0.00..10.21 rows=116 width=96) (actual time=0.017..0.064 rows=116 loops=1)

35. 0.022 0.022 ↑ 1.0 44 1

Seq Scan on services_departments (cost=0.00..2.54 rows=44 width=77) (actual time=0.014..0.022 rows=44 loops=1)

36. 0.002 0.011 ↑ 1.0 12 1

Subquery Scan on *SELECT* 2 (cost=0.00..1.75 rows=12 width=77) (actual time=0.008..0.011 rows=12 loops=1)

37. 0.009 0.009 ↑ 1.0 12 1

Seq Scan on diagnostics_departments (cost=0.00..1.39 rows=12 width=55) (actual time=0.006..0.009 rows=12 loops=1)

38. 0.006 0.019 ↑ 1.0 60 1

Subquery Scan on *SELECT* 3 (cost=0.00..4.60 rows=60 width=77) (actual time=0.007..0.019 rows=60 loops=1)

39. 0.013 0.013 ↑ 1.0 60 1

Seq Scan on department (cost=0.00..2.80 rows=60 width=53) (actual time=0.007..0.013 rows=60 loops=1)

40. 0.000 0.003 ↓ 0.0 0 1

Hash (cost=18.40..18.40 rows=280 width=170) (actual time=0.003..0.003 rows=0 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
41. 0.003 0.003 ↓ 0.0 0 1

Seq Scan on store_retail_customers prc (cost=0.00..18.40 rows=280 width=170) (actual time=0.003..0.003 rows=0 loops=1)

42. 113.040 280.004 ↓ 1.0 429,022 1

Hash (cost=21,883.36..21,883.36 rows=429,012 width=39) (actual time=280.004..280.004 rows=429,022 loops=1)

  • Buckets: 524,288 Batches: 1 Memory Usage: 34,630kB
43. 166.964 166.964 ↓ 1.0 429,022 1

Seq Scan on incoming_sample_registration isr (cost=0.00..21,883.36 rows=429,012 width=39) (actual time=0.010..166.964 rows=429,022 loops=1)

44. 318.692 318.692 ↑ 1.0 1 14,486

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..0.49 rows=1 width=49) (actual time=0.022..0.022 rows=1 loops=14,486)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
45. 0.018 0.041 ↑ 1.0 21 1

Hash (cost=1.63..1.63 rows=21 width=14) (actual time=0.041..0.041 rows=21 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
46. 0.023 0.023 ↑ 1.0 21 1

Seq Scan on salutation_master smb (cost=0.00..1.63 rows=21 width=14) (actual time=0.016..0.023 rows=21 loops=1)

47. 14.819 260.748 ↑ 1.0 1 14,486

Nested Loop Left Join (cost=1.39..1.63 rows=1 width=20) (actual time=0.017..0.018 rows=1 loops=14,486)

48. 6.351 159.346 ↑ 1.0 1 14,486

Nested Loop (cost=0.83..0.95 rows=1 width=20) (actual time=0.011..0.011 rows=1 loops=14,486)

49. 115.888 115.888 ↑ 1.0 1 14,486

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pri_plan (cost=0.56..0.64 rows=1 width=38) (actual time=0.007..0.008 rows=1 loops=14,486)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
50. 37.107 37.107 ↑ 1.0 1 12,369

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.28..0.31 rows=1 width=10) (actual time=0.003..0.003 rows=1 loops=12,369)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 0
51. 86.583 86.583 ↓ 0.0 0 12,369

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sec_plan (cost=0.56..0.64 rows=1 width=38) (actual time=0.007..0.007 rows=0 loops=12,369)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
52. 0.012 0.023 ↑ 1.0 5 1

Hash (cost=1.15..1.15 rows=5 width=20) (actual time=0.022..0.023 rows=5 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
53. 0.011 0.011 ↑ 1.0 5 1

Seq Scan on op_type_names otn (cost=0.00..1.15 rows=5 width=20) (actual time=0.009..0.011 rows=5 loops=1)

54. 1.131 2.045 ↑ 1.0 2,072 1

Hash (cost=147.16..147.16 rows=2,072 width=30) (actual time=2.045..2.045 rows=2,072 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 160kB
55. 0.914 0.914 ↑ 1.0 2,072 1

Seq Scan on doctors dr (cost=0.00..147.16 rows=2,072 width=30) (actual time=0.019..0.914 rows=2,072 loops=1)

56. 1.086 1.829 ↑ 1.0 2,072 1

Hash (cost=147.16..147.16 rows=2,072 width=30) (actual time=1.829..1.829 rows=2,072 loops=1)

  • Buckets: 4,096 Batches: 1 Memory Usage: 160kB
57. 0.743 0.743 ↑ 1.0 2,072 1

Seq Scan on doctors rdoc (cost=0.00..147.16 rows=2,072 width=30) (actual time=0.007..0.743 rows=2,072 loops=1)

58. 14.486 14.486 ↓ 0.0 0 14,486

Index Scan using referral_pkey on referral ref (cost=0.42..0.46 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops=14,486)

  • Index Cond: ((pr.reference_docto_id)::text = (referal_no)::text)
59. 0.190 0.497 ↑ 1.0 670 1

Hash (cost=53.10..53.10 rows=670 width=34) (actual time=0.497..0.497 rows=670 loops=1)

  • Buckets: 1,024 Batches: 1 Memory Usage: 52kB
60. 0.307 0.307 ↑ 1.0 670 1

Seq Scan on tpa_master ptpa (cost=0.00..53.10 rows=670 width=34) (actual time=0.011..0.307 rows=670 loops=1)

61. 115.888 115.888 ↑ 1.0 1 14,486

Index Scan using insurance_plan_main_pkey on insurance_plan_main pipm (cost=0.29..0.33 rows=1 width=47) (actual time=0.008..0.008 rows=1 loops=14,486)

  • Index Cond: (pri_plan.plan_id = plan_id)