explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 4eLq

Settings
# exclusive inclusive rows x rows loops node
1. 0.034 7,105.736 ↑ 118.5 17 1

Sort (cost=482,546.13..482,551.17 rows=2,014 width=1,014) (actual time=7,105.735..7,105.736 rows=17 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 33kB
2. 0.045 7,105.702 ↑ 118.5 17 1

Nested Loop Left Join (cost=158,925.09..482,435.61 rows=2,014 width=1,014) (actual time=7,105.083..7,105.702 rows=17 loops=1)

3. 0.018 7,105.589 ↑ 118.5 17 1

Hash Left Join (cost=158,924.81..481,742.40 rows=2,014 width=970) (actual time=7,105.045..7,105.589 rows=17 loops=1)

  • Hash Cond: ((pr.primary_sponsor_id)::text = (ptpa.tpa_id)::text)
4. 0.033 7,105.476 ↑ 118.5 17 1

Nested Loop Left Join (cost=158,914.97..481,727.19 rows=2,014 width=958) (actual time=7,104.940..7,105.476 rows=17 loops=1)

  • -> Index Scan using referral_pkey on referral ref (cost=0.29..0.32 rows=1 width=28) (actual time=0.001..0.001 rows=0 loops:
5. 0.015 7,105.443 ↑ 118.5 17 1

Hash Left Join (cost=158,914.68..481,082.85 rows=2,014 width=943) (actual time=7,104.921..7,105.443 rows=17 loops=1)

  • Hash Cond: ((pr.reference_docto_id)::text = (rdoc.doctor_id)::text)
  • Index Cond: ((pr.reference_docto_id)::text = (referal_no)::text)
6. 0.018 7,105.175 ↑ 118.5 17 1

Hash Left Join (cost=158,878.71..481,041.59 rows=2,014 width=921) (actual time=7,104.658..7,105.175 rows=17 loops=1)

  • Hash Cond: ((pr.doctor)::text = (dr.doctor_id)::text)
7. 0.062 7,104.909 ↑ 118.5 17 1

Hash Left Join (cost=158,842.75..481,000.31 rows=2,014 width=907) (actual time=7,104.402..7,104.909 rows=17 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
8. 0.012 7,104.836 ↑ 118.5 17 1

Nested Loop Left Join (cost=158,841.63..480,989.43 rows=2,014 width=791) (actual time=7,104.337..7,104.836 rows=17 loops=1)

9. 0.019 7,104.501 ↑ 118.5 17 1

Hash Left Join (cost=158,840.64..478,536.79 rows=2,014 width=803) (actual time=7,104.256..7,104.501 rows=17 loops=1)

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
10. 0.029 7,104.468 ↑ 118.5 17 1

Nested Loop Left Join (cost=158,839.24..478,528.86 rows=2,014 width=694) (actual time=7,104.232..7,104.468 rows=17 loops=1)

11. 0.022 7,104.303 ↑ 118.5 17 1

Hash Left Join (cost=158,838.82..477,155.75 rows=2,014 width=662) (actual time=7,104.195..7,104.303 rows=17 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: 2
12. 0.008 7,103.697 ↑ 596.4 19 1

Nested Loop (cost=158,788.25..477,075.44 rows=11,332 width=650) (actual time=7,103.577..7,103.697 rows=19 loops=1)

13. 0.019 0.019 ↑ 1.0 1 1

Seq Scan on hospital_center_master bhcm (cost=0.00..1.14 rows=1 width=222) (actual time=0.017..0.019 rows=1 loops=1)

  • Filter: ((center_name)::text = 'Brightpoint'::text)
  • Rows Removed by Filter: 10
14. 0.017 7,103.670 ↑ 596.4 19 1

Hash Left Join (cost=158,788.25..476,960.98 rows=11,332 width=428) (actual time=7,103.557..7,103.670 rows=19 loops=1)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
15. 0.000 7,103.651 ↑ 596.4 19 1

Hash Left Join (cost=158,771.95..476,914.94 rows=11,332 width=306) (actual time=7,103.546..7,103.651 rows=19 loops=1)

  • Hash Cond: ((bc.act_department_id)::text = tdep.dept_id)
16. 2,799.726 7,103.706 ↑ 596.4 19 1

Gather (cost=158,764.56..476,792.94 rows=11,332 width=281) (actual time=7,103.446..7,103.706 rows=19 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
17. 0.000 4,303.980 ↑ 787.0 6 3

Hash Left Join (cost=157,764.56..474,659.74 rows=4,722 width=281) (actual time=4,303.951..4,303.980 rows=6 loops=3)

  • Hash Cond: ((bc.prescribing_dr_id)::text = (predoc.doctor_id)::text)
18. 0.078 4,303.871 ↑ 787.0 6 3

Hash Join (cost=157,728.59..474,611.34 rows=4,722 width=266) (actual time=4,303.844..4,303.871 rows=6 loops=3)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
19. 0.000 4,303.753 ↑ 787.0 6 3

Merge Left Join (cost=157,726.24..474,595.72 rows=4,722 width=256) (actual time=4,303.729..4,303.753 rows=6 loops=3)

  • Merge Cond: ((bc.charge_id)::text = (bill_charge_claim.charge_id)::text)
20. 0.042 3,133.699 ↑ 787.0 6 3

Sort (cost=157,725.81..157,737.62 rows=4,722 width=233) (actual time=3,133.698..3,133.699 rows=6 loops=3)

  • Sort Key: bc.charge_id
  • Sort Method: quicksort Memory: 30kB
21. 22.452 3,133.657 ↑ 787.0 6 3

Nested Loop (cost=0.85..157,437.65 rows=4,722 width=233) (actual time=2,558.330..3,133.657 rows=6 loops=3)

  • -> Nested Loop Left Join (:
22. 7.425 3,077.542 ↑ 588.0 2 3

Nested Loop Left Join (cost=0.42..41,047.62 rows=1,176 width=132) (actual time=2,537.055..3,077.542 rows=2 loops=3)

23. 3,058.998 3,058.998 ↑ 588.0 2 3

Parallel Seq Scan on bill b (cost=0.00..33,348.37 rows=1,176 width=66) (actual time=2,527.423..3,058.998 rows=2 loops=3)

  • Filter: ((status <> 'X'::bpchar) AND (date(open_date) >= '2018-12-31'::date) AND (date(open_date) <= '2018-12-31'::date))
  • Rows Removed by Filter: 193545
24. 11.119 11.119 ↑ 1.0 1 5

Index Scan using patient_registration_pkey on patient_registration pr (cost=0.42..6.55 rows=1 width=66) (actual time=11.119..11.119 rows=1 loops=5)

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
25. 33.663 33.663 ↑ 7.8 4 5

Index Scan using bill_charge_bill_no_index on bill_charge bc (cost=0.43..98.66 rows=31 width=115) (actual time=33.401..33.663 rows=4 loops=5)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
26. 1,326.931 3,101.496 ↑ 1.3 1,785,753 1

GroupAggregate (cost=0.43..288,641.23 rows=2,253,064 width=73) (actual time=0.154..3,101.496 rows=1,785,753 loops=1)

  • Group Key: bill_charge_claim.charge_id
27. 1,774.565 1,774.565 ↑ 1.3 1,855,583 1

Index Scan using bill_charge_claim_charge_id on bill_charge_claim (cost=0.43..248,722.26 rows=2,351,133 width=13) (actual time=0.134..1,774.565 rows=1,855,583 loops=1)

28. 0.017 0.040 ↑ 1.0 60 3

Hash (cost=1.60..1.60 rows=60 width=22) (actual time=0.040..0.040 rows=60 loops=3)

  • Buckets: 1024 Batches: 1 Memory Usage: 12kB
29. 0.023 0.023 ↑ 1.0 60 3

Seq Scan on chargehead_constants chc (cost=0.00..1.60 rows=60 width=22) (actual time=0.015..0.023 rows=60 loops=3)

30. 0.142 0.295 ↑ 1.0 754 1

Hash (cost=26.54..26.54 rows=754 width=30) (actual time=0.295..0.295 rows=754 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
31. 0.153 0.153 ↑ 1.0 754 1

Seq Scan on doctors predoc (cost=0.00..26.54 rows=754 width=30) (actual time=0.009..0.153 rows=754 loops=1)

32. 0.020 0.082 ↑ 1.0 98 1

Hash (cost=6.17..6.17 rows=98 width=64) (actual time=0.082..0.082 rows=98 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 13kB
33. 0.010 0.062 ↑ 1.0 98 1

Subquery Scan on tdep (cost=0.00..6.17 rows=98 width=64) (actual time=0.010..0.062 rows=98 loops=1)

34. 0.007 0.052 ↑ 1.0 98 1

Append (cost=0.00..5.19 rows=98 width=96) (actual time=0.010..0.052 rows=98 loops=1)

35. 0.016 0.016 ↑ 1.0 40 1

Seq Scan on services_departments (cost=0.00..1.60 rows=40 width=222) (actual time=0.009..0.016 rows=40 loops=1)

36. 0.002 0.013 ↑ 1.0 12 1

Subquery Scan on *SELECT* 2 (cost=0.00..1.27 rows=12 width=382) (actual time=0.009..0.013 rows=12 loops=1)

37. 0.011 0.011 ↑ 1.0 12 1

Seq Scan on diagnostics_departments (cost=0.00..1.15 rows=12 width=388) (actual time=0.009..0.011 rows=12 loops=1)

38. 0.005 0.016 ↑ 1.0 46 1

Subquery Scan on *SELECT* 3 (cost=0.00..1.92 rows=46 width=282) (actual time=0.007..0.016 rows=46 loops=1)

39. 0.011 0.011 ↑ 1.0 46 1

Seq Scan on department (cost=0.00..1.46 rows=46 width=288) (actual time=0.006..0.011 rows=46 loops=1)

40. 0.001 0.002 ↓ 0.0 0 1

Hash (cost=12.80..12.80 rows=280 width=170) (actual time=0.001..0.002 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
41. 0.001 0.001 ↓ 0.0 0 1

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

42. 0.268 0.584 ↑ 1.0 1,225 1

Hash (cost=35.25..35.25 rows=1,225 width=37) (actual time=0.584..0.584 rows=1,225 loops=1)

  • Buckets: 2048 Batches: 1 Memory Usage: 101kB
43. 0.316 0.316 ↑ 1.0 1,225 1

Seq Scan on incoming_sample_registration isr (cost=0.00..35.25 rows=1,225 width=37) (actual time=0.018..0.316 rows=1,225 loops=1)

44. 0.136 0.136 ↑ 1.0 1 17

Index Scan using patient_details_pkey on patient_details pd (cost=0.42..0.68 rows=1 width=47) (actual time=0.008..0.008 rows=1 loops=17)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
45. 0.006 0.014 ↑ 1.0 18 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
46. 0.008 0.008 ↑ 1.0 18 1

Seq Scan on salutation_master smb (cost=0.00..1.18 rows=18 width=156) (actual time=0.006..0.008 rows=18 loops=1)

47. 0.017 0.323 ↑ 1.0 1 17

Nested Loop Left Join (cost=0.99..1.21 rows=1 width=20) (actual time=0.018..0.019 rows=1 loops=17)

48. 0.034 0.221 ↑ 1.0 1 17

Nested Loop (cost=0.57..0.68 rows=1 width=20) (actual time=0.012..0.013 rows=1 loops=17)

49. 0.119 0.119 ↑ 1.0 1 17

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans pri_plan (cost=0.42..0.51 rows=1 width=38) (actual time=0.007..0.007 rows=1 loops=17)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
50. 0.068 0.068 ↑ 1.0 1 17

Index Only Scan using tpa_master_pkey on tpa_master pri_tm (cost=0.14..0.16 rows=1 width=10) (actual time=0.004..0.004 rows=1 loops=17)

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 17
51. 0.085 0.085 ↓ 0.0 0 17

Index Scan using patient_insurance_plans_patient_id on patient_insurance_plans sec_plan (cost=0.42..0.52 rows=1 width=38) (actual time=0.005..0.005 rows=0 loops=17)

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

Hash (cost=1.05..1.05 rows=5 width=126) (actual time=0.011..0.011 rows=5 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
53. 0.007 0.007 ↑ 1.0 5 1

Seq Scan on op_type_names otn (cost=0.00..1.05 rows=5 width=126) (actual time=0.007..0.007 rows=5 loops=1)

54. 0.141 0.248 ↑ 1.0 754 1

Hash (cost=26.54..26.54 rows=754 width=30) (actual time=0.248..0.248 rows=754 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
55. 0.107 0.107 ↑ 1.0 754 1

Seq Scan on doctors dr (cost=0.00..26.54 rows=754 width=30) (actual time=0.003..0.107 rows=754 loops=1)

56. 0.151 0.253 ↑ 1.0 754 1

Hash (cost=26.54..26.54 rows=754 width=30) (actual time=0.253..0.253 rows=754 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 54kB
57. 0.102 0.102 ↑ 1.0 754 1

Seq Scan on doctors rdoc (cost=0.00..26.54 rows=754 width=30) (actual time=0.002..0.102 rows=754 loops=1)

58. 0.053 0.095 ↑ 1.0 215 1

Hash (cost=7.15..7.15 rows=215 width=32) (actual time=0.095..0.095 rows=215 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 22kB
59. 0.042 0.042 ↑ 1.0 215 1

Seq Scan on tpa_master ptpa (cost=0.00..7.15 rows=215 width=32) (actual time=0.007..0.042 rows=215 loops=1)

60. 0.068 0.068 ↑ 1.0 1 17

Index Scan using insurance_plan_main_pkey on insurance_plan_main pipm (cost=0.29..0.31 rows=1 width=42) (actual time=0.004..0.004 rows=1 loops=17)

  • Index Cond: (pri_plan.plan_id = plan_id)