explain.depesz.com

PostgreSQL's explain analyze made readable

Result: 2UCL

Settings
# exclusive inclusive rows x rows loops node
1. 61.790 8,038.455 ↓ 1.4 14,509 1

Sort (cost=3,106,815.34..3,106,840.96 rows=10,247 width=707) (actual time=8,036.888..8,038.455 rows=14,509 loops=1)

  • Sort Key: b.bill_no
  • Sort Method: quicksort Memory: 7,477kB
2. 75.263 7,976.665 ↓ 1.4 14,509 1

Nested Loop Left Join (cost=1,272.75..3,106,132.74 rows=10,247 width=707) (actual time=44.158..7,976.665 rows=14,509 loops=1)

3. 14.497 6,351.920 ↓ 1.4 14,509 1

Nested Loop Left Join (cost=1,272.46..3,007,347.18 rows=10,247 width=529) (actual time=44.134..6,351.920 rows=14,509 loops=1)

4. 12.293 6,264.878 ↓ 1.4 14,509 1

Nested Loop Left Join (cost=1,272.18..3,004,145.41 rows=10,247 width=515) (actual time=44.123..6,264.878 rows=14,509 loops=1)

5. 19.295 6,223.567 ↓ 1.4 14,509 1

Nested Loop Left Join (cost=1,271.76..2,999,449.88 rows=10,247 width=500) (actual time=44.113..6,223.567 rows=14,509 loops=1)

6. 10.855 6,189.763 ↓ 1.4 14,509 1

Nested Loop Left Join (cost=1,271.48..2,996,196.26 rows=10,247 width=478) (actual time=44.102..6,189.763 rows=14,509 loops=1)

7. 12.765 6,091.854 ↓ 1.4 14,509 1

Hash Left Join (cost=1,271.20..2,992,942.79 rows=10,247 width=464) (actual time=44.084..6,091.854 rows=14,509 loops=1)

  • Hash Cond: (pr.op_type = otn.op_type)
8. 16.172 6,079.073 ↓ 1.4 14,509 1

Nested Loop Left Join (cost=1,269.89..2,992,850.80 rows=10,247 width=448) (actual time=44.044..6,079.073 rows=14,509 loops=1)

9. 11.599 5,613.122 ↓ 1.4 14,509 1

Hash Left Join (cost=1,268.50..2,975,881.41 rows=10,247 width=460) (actual time=44.028..5,613.122 rows=14,509 loops=1)

  • Hash Cond: ((pd.salutation)::text = (smb.salutation_id)::text)
10. 13.670 5,601.490 ↓ 1.4 14,509 1

Nested Loop Left Join (cost=1,266.19..2,975,837.01 rows=10,247 width=464) (actual time=43.951..5,601.490 rows=14,509 loops=1)

11. 15.363 5,239.604 ↓ 1.4 14,509 1

Nested Loop (cost=1,265.76..2,970,772.69 rows=10,247 width=430) (actual time=43.935..5,239.604 rows=14,509 loops=1)

  • Join Filter: ((pr.center_id = bhcm.center_id) OR (isr.center_id = bhcm.center_id) OR (prc.center_id = bhcm.center_id))
  • Rows Removed by Join Filter: 36,816
12. 0.039 0.039 ↑ 1.0 1 1

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

  • Filter: ((center_name)::text = 'NMC Royal'::text)
  • Rows Removed by Filter: 43
13. 0.000 5,224.202 ↑ 4.0 51,325 1

Hash Left Join (cost=1,265.76..2,963,037.80 rows=206,119 width=424) (actual time=8.093..5,224.202 rows=51,325 loops=1)

  • Hash Cond: ((bc.act_department_id)::text = tdep.dept_id)
14. 0.000 5,321.918 ↑ 4.0 51,325 1

Gather (cost=1,248.30..2,958,131.36 rows=206,119 width=399) (actual time=7.853..5,321.918 rows=51,325 loops=1)

  • Workers Planned: 2
  • Workers Launched: 2
15. 14.149 6,840.794 ↑ 5.0 17,108 3 / 3

Hash Left Join (cost=248.30..2,936,519.46 rows=85,883 width=399) (actual time=13.609..6,840.794 rows=17,108 loops=3)

  • Hash Cond: ((bc.prescribing_dr_id)::text = (predoc.doctor_id)::text)
16. 14.407 6,824.944 ↑ 5.0 17,108 3 / 3

Hash Join (cost=33.80..2,936,078.83 rows=85,883 width=384) (actual time=11.862..6,824.944 rows=17,108 loops=3)

  • Hash Cond: ((bc.charge_head)::text = (chc.chargehead_id)::text)
17. 12.444 6,810.475 ↑ 5.0 17,108 3 / 3

Nested Loop (cost=29.05..2,935,804.10 rows=85,883 width=374) (actual time=11.706..6,810.475 rows=17,108 loops=3)

18. 3.354 3,912.577 ↑ 4.6 4,110 3 / 3

Hash Left Join (cost=28.48..699,530.23 rows=18,957 width=274) (actual time=10.437..3,912.577 rows=4,110 loops=3)

  • Hash Cond: ((b.visit_id)::text = (prc.customer_id)::text)
19. 3.622 3,909.214 ↑ 4.6 4,110 3 / 3

Nested Loop Left Join (cost=0.98..699,452.97 rows=18,957 width=152) (actual time=10.410..3,909.214 rows=4,110 loops=3)

20. 9.314 3,880.930 ↑ 4.6 4,110 3 / 3

Nested Loop Left Join (cost=0.56..681,787.79 rows=18,957 width=122) (actual time=10.367..3,880.930 rows=4,110 loops=3)

21. 3,772.968 3,772.968 ↑ 4.6 4,110 3 / 3

Parallel Seq Scan on bill b (cost=0.00..607,840.42 rows=18,957 width=56) (actual time=10.278..3,772.968 rows=4,110 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,604
22. 98.648 98.648 ↑ 1.0 1 12,331 / 3

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

  • Index Cond: ((patient_id)::text = (b.visit_id)::text)
23. 24.662 24.662 ↓ 0.0 0 12,331 / 3

Index Scan using incoming_sample_registration_pkey on incoming_sample_registration isr (cost=0.42..0.93 rows=1 width=39) (actual time=0.006..0.006 rows=0 loops=12,331)

  • Index Cond: ((incoming_visit_id)::text = (b.visit_id)::text)
24. 0.000 0.009 ↓ 0.0 0 3 / 3

Hash (cost=18.40..18.40 rows=280 width=170) (actual time=0.009..0.009 rows=0 loops=3)

  • Buckets: 1,024 Batches: 1 Memory Usage: 8kB
25. 0.009 0.009 ↓ 0.0 0 3 / 3

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

26. 2,885.454 2,885.454 ↑ 48.2 4 12,331 / 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.217..0.702 rows=4 loops=12,331)

  • Index Cond: ((bill_no)::text = (b.bill_no)::text)
  • Filter: (status <> 'X'::bpchar)
  • Rows Removed by Filter: 0
27. 0.023 0.062 ↑ 1.0 60 3 / 3

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

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

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

29. 0.776 1.701 ↑ 1.0 2,072 3 / 3

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

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

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

31. 0.065 0.215 ↑ 1.0 116 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 14kB
32. 0.029 0.150 ↑ 1.0 116 1

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

33. 0.021 0.121 ↑ 1.0 116 1

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

34. 0.039 0.039 ↑ 1.0 44 1

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

35. 0.004 0.017 ↑ 1.0 12 1

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

36. 0.013 0.013 ↑ 1.0 12 1

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

37. 0.015 0.044 ↑ 1.0 60 1

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

38. 0.029 0.029 ↑ 1.0 60 1

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

39. 348.216 348.216 ↑ 1.0 1 14,509

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

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
40. 0.017 0.033 ↑ 1.0 21 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
41. 0.016 0.016 ↑ 1.0 21 1

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

42. 13.012 449.779 ↑ 1.0 1 14,509

Nested Loop Left Join (cost=1.39..1.63 rows=1 width=20) (actual time=0.030..0.031 rows=1 loops=14,509)

43. 25.094 275.671 ↑ 1.0 1 14,509

Nested Loop (cost=0.83..0.95 rows=1 width=20) (actual time=0.018..0.019 rows=1 loops=14,509)

44. 188.617 188.617 ↑ 1.0 1 14,509

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.013..0.013 rows=1 loops=14,509)

  • Index Cond: ((patient_id)::text = (pr.patient_id)::text)
  • Filter: (priority = 1)
45. 61.960 61.960 ↑ 1.0 1 12,392

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

  • Index Cond: (tpa_id = (pri_plan.sponsor_id)::text)
  • Heap Fetches: 0
46. 161.096 161.096 ↓ 0.0 0 12,392

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.013..0.013 rows=0 loops=12,392)

  • Index Cond: ((pri_plan.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
47. 0.009 0.016 ↑ 1.0 5 1

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

  • Buckets: 1,024 Batches: 1 Memory Usage: 9kB
48. 0.007 0.007 ↑ 1.0 5 1

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

49. 87.054 87.054 ↑ 1.0 1 14,509

Index Scan using doctors_pkey on doctors dr (cost=0.28..0.32 rows=1 width=30) (actual time=0.006..0.006 rows=1 loops=14,509)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
50. 14.509 14.509 ↓ 0.0 0 14,509

Index Scan using doctors_pkey on doctors rdoc (cost=0.28..0.32 rows=1 width=30) (actual time=0.001..0.001 rows=0 loops=14,509)

  • Index Cond: ((pr.reference_docto_id)::text = (doctor_id)::text)
51. 29.018 29.018 ↓ 0.0 0 14,509

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

  • Index Cond: ((pr.reference_docto_id)::text = (referal_no)::text)
52. 72.545 72.545 ↑ 1.0 1 14,509

Index Scan using tpa_master_pkey on tpa_master ptpa (cost=0.28..0.31 rows=1 width=34) (actual time=0.005..0.005 rows=1 loops=14,509)

  • Index Cond: ((tpa_id)::text = (pr.primary_sponsor_id)::text)
53. 58.036 58.036 ↑ 1.0 1 14,509

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

  • Index Cond: (pri_plan.plan_id = plan_id)
54.          

SubPlan (for Nested Loop Left Join)

55. 24.652 1,318.882 ↑ 1.0 1 12,326

Aggregate (cost=4.61..4.64 rows=1 width=32) (actual time=0.107..0.107 rows=1 loops=12,326)

56. 1,294.230 1,294.230 ↑ 1.0 1 12,326

Index Scan using bill_charge_claim_charge_id on bill_charge_claim (cost=0.56..4.60 rows=1 width=4) (actual time=0.105..0.105 rows=1 loops=12,326)

  • Index Cond: ((charge_id)::text = (bc.charge_id)::text)
57. 24.652 172.564 ↑ 1.0 1 12,326

Aggregate (cost=4.61..4.64 rows=1 width=32) (actual time=0.014..0.014 rows=1 loops=12,326)

58. 147.912 147.912 ↑ 1.0 1 12,326

Index Scan using bill_charge_claim_charge_id on bill_charge_claim bill_charge_claim_1 (cost=0.56..4.60 rows=1 width=4) (actual time=0.012..0.012 rows=1 loops=12,326)

  • Index Cond: ((charge_id)::text = (bc.charge_id)::text)