explain.depesz.com

PostgreSQL's explain analyze made readable

Result: EUK9

Settings
# exclusive inclusive rows x rows loops node
1. 0.003 3.198 ↑ 1.0 1 1

Limit (cost=2.52..142.60 rows=1 width=802) (actual time=3.197..3.198 rows=1 loops=1)

2. 0.012 3.195 ↑ 18,036,325.0 1 1

Nested Loop Left Join (cost=2.52..2,526,607,127.64 rows=18,036,325 width=802) (actual time=3.195..3.195 rows=1 loops=1)

3. 0.001 2.988 ↑ 18,036,325.0 1 1

Nested Loop Left Join (cost=2.25..371,175,858.82 rows=18,036,325 width=503) (actual time=2.988..2.988 rows=1 loops=1)

4. 0.001 2.981 ↑ 18,036,325.0 1 1

Nested Loop Left Join (cost=1.97..365,719,620.81 rows=18,036,325 width=480) (actual time=2.981..2.981 rows=1 loops=1)

5. 0.003 2.979 ↑ 18,036,325.0 1 1

Nested Loop Left Join (cost=1.70..360,263,382.80 rows=18,036,325 width=456) (actual time=2.979..2.979 rows=1 loops=1)

  • Join Filter: ((eet.code)::text = ((pr.encounter_end_type)::character varying)::text)
6. 0.004 2.972 ↑ 10,609,603.0 1 1

Nested Loop Left Join (cost=1.70..288,118,068.15 rows=10,609,603 width=428) (actual time=2.972..2.972 rows=1 loops=1)

  • Join Filter: ((est.code)::text = ((pr.encounter_start_type)::character varying)::text)
7. 0.004 2.965 ↑ 6,240,943.0 1 1

Nested Loop Left Join (cost=1.70..245,679,641.50 rows=6,240,943 width=400) (actual time=2.965..2.965 rows=1 loops=1)

  • Join Filter: (etc.encounter_type_id = pr.encounter_type)
8. 0.001 2.957 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=1.70..226,956,798.08 rows=4,622,921 width=400) (actual time=2.957..2.957 rows=1 loops=1)

9. 0.285 2.951 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=1.42..225,558,300.47 rows=4,622,921 width=376) (actual time=2.951..2.951 rows=1 loops=1)

  • Join Filter: ((thd.transfer_hospital_id)::text = (pr.transfer_destination)::text)
  • Rows Removed by Join Filter: 952
10. 0.291 1.720 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=1.42..159,542,967.69 rows=4,622,921 width=350) (actual time=1.720..1.720 rows=1 loops=1)

  • Join Filter: ((ths.transfer_hospital_id)::text = (pr.transfer_source)::text)
  • Rows Removed by Join Filter: 952
11. 0.004 0.479 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=1.42..93,527,634.91 rows=4,622,921 width=324) (actual time=0.479..0.479 rows=1 loops=1)

  • Join Filter: (hcm.center_id = pr.center_id)
  • Rows Removed by Join Filter: 2
12. 0.001 0.468 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=1.42..91,794,037.23 rows=4,622,921 width=324) (actual time=0.468..0.468 rows=1 loops=1)

13. 0.061 0.454 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=1.13..90,321,067.81 rows=4,622,921 width=281) (actual time=0.454..0.454 rows=1 loops=1)

  • Join Filter: ((wn.ward_no)::text = (pr.ward_id)::text)
  • Rows Removed by Join Filter: 176
14. 0.015 0.203 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=1.13..76,174,925.00 rows=4,622,921 width=274) (actual time=0.203..0.203 rows=1 loops=1)

  • Join Filter: ((dept.dept_id)::text = (pr.dept_name)::text)
  • Rows Removed by Join Filter: 33
15. 0.001 0.149 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=1.13..72,083,638.18 rows=4,622,921 width=262) (actual time=0.149..0.149 rows=1 loops=1)

16. 0.002 0.135 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=0.85..70,673,431.27 rows=4,622,921 width=240) (actual time=0.135..0.135 rows=1 loops=1)

17. 0.004 0.110 ↑ 4,622,921.0 1 1

Nested Loop (cost=0.43..68,596,254.12 rows=4,622,921 width=222) (actual time=0.110..0.110 rows=1 loops=1)

18. 0.010 0.048 ↑ 2,472,340.0 1 1

Nested Loop Left Join (cost=0.00..65,733,361.52 rows=2,472,340 width=15) (actual time=0.048..0.048 rows=1 loops=1)

  • Join Filter: ((pd.salutation)::text = (sm.salutation)::text)
  • Rows Removed by Join Filter: 22
19. 0.009 0.009 ↑ 2,472,340.0 1 1

Seq Scan on s_patient_details pd (cost=0.00..92,702.40 rows=2,472,340 width=24) (actual time=0.009..0.009 rows=1 loops=1)

20. 0.018 0.029 ↑ 80.5 22 1

Materialize (cost=0.00..36.55 rows=1,770 width=5) (actual time=0.005..0.029 rows=22 loops=1)

21. 0.011 0.011 ↑ 80.5 22 1

Seq Scan on m_salutation_master sm (cost=0.00..27.70 rows=1,770 width=5) (actual time=0.004..0.011 rows=22 loops=1)

22. 0.058 0.058 ↑ 13.0 1 1

Index Scan using pr_mr_no_index on s_patient_registration pr (cost=0.43..1.03 rows=13 width=222) (actual time=0.058..0.058 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = (pd.mr_no)::text)
23. 0.023 0.023 ↑ 1.0 1 1

Index Scan using bd_visit_id_index on s_bed_details bn (cost=0.42..0.44 rows=1 width=34) (actual time=0.023..0.023 rows=1 loops=1)

  • Index Cond: ((visit_id)::text = (pr.patient_id)::text)
24. 0.013 0.013 ↑ 1.0 1 1

Index Scan using dr_doctor_index on m_doctors doc (cost=0.28..0.30 rows=1 width=30) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: ((doctor_id)::text = (pr.doctor)::text)
25. 0.022 0.039 ↑ 1.7 34 1

Materialize (cost=0.00..1.88 rows=59 width=20) (actual time=0.006..0.039 rows=34 loops=1)

26. 0.017 0.017 ↑ 1.7 34 1

Seq Scan on m_department dept (cost=0.00..1.59 rows=59 width=20) (actual time=0.006..0.017 rows=34 loops=1)

27. 0.122 0.190 ↑ 1.2 177 1

Materialize (cost=0.00..5.06 rows=204 width=25) (actual time=0.009..0.190 rows=177 loops=1)

28. 0.068 0.068 ↑ 1.2 177 1

Seq Scan on m_ward_names wn (cost=0.00..4.04 rows=204 width=25) (actual time=0.005..0.068 rows=177 loops=1)

29. 0.013 0.013 ↑ 1.0 1 1

Index Scan using ipm_plan_id_index on m_insurance_plan_main ipm (cost=0.29..0.31 rows=1 width=47) (actual time=0.013..0.013 rows=1 loops=1)

  • Index Cond: (plan_id = pr.plan_id)
30. 0.002 0.007 ↑ 8.3 3 1

Materialize (cost=0.00..2.38 rows=25 width=4) (actual time=0.005..0.007 rows=3 loops=1)

31. 0.005 0.005 ↑ 8.3 3 1

Seq Scan on m_hospital_center_master hcm (cost=0.00..2.25 rows=25 width=4) (actual time=0.004..0.005 rows=3 loops=1)

32. 0.605 0.950 ↑ 1.0 952 1

Materialize (cost=0.00..23.28 rows=952 width=34) (actual time=0.008..0.950 rows=952 loops=1)

33. 0.345 0.345 ↑ 1.0 952 1

Seq Scan on m_transfer_hospitals ths (cost=0.00..18.52 rows=952 width=34) (actual time=0.007..0.345 rows=952 loops=1)

34. 0.612 0.946 ↑ 1.0 952 1

Materialize (cost=0.00..23.28 rows=952 width=34) (actual time=0.003..0.946 rows=952 loops=1)

35. 0.334 0.334 ↑ 1.0 952 1

Seq Scan on m_transfer_hospitals thd (cost=0.00..18.52 rows=952 width=34) (actual time=0.002..0.334 rows=952 loops=1)

36. 0.005 0.005 ↑ 1.0 1 1

Index Scan using icm_ins_co_id_index on m_insurance_company_master picm (cost=0.28..0.29 rows=1 width=32) (actual time=0.005..0.005 rows=1 loops=1)

  • Index Cond: ((pr.primary_insurance_co)::text = (insurance_co_id)::text)
37. 0.001 0.004 ↑ 270.0 1 1

Materialize (cost=0.00..14.05 rows=270 width=4) (actual time=0.004..0.004 rows=1 loops=1)

38. 0.003 0.003 ↑ 270.0 1 1

Seq Scan on m_encounter_type_codes etc (cost=0.00..12.70 rows=270 width=4) (actual time=0.003..0.003 rows=1 loops=1)

39. 0.000 0.003 ↑ 340.0 1 1

Materialize (cost=0.00..15.10 rows=340 width=80) (actual time=0.003..0.003 rows=1 loops=1)

40. 0.003 0.003 ↑ 340.0 1 1

Seq Scan on m_encounter_start_types est (cost=0.00..13.40 rows=340 width=80) (actual time=0.003..0.003 rows=1 loops=1)

41. 0.001 0.004 ↑ 340.0 1 1

Materialize (cost=0.00..15.10 rows=340 width=80) (actual time=0.004..0.004 rows=1 loops=1)

42. 0.003 0.003 ↑ 340.0 1 1

Seq Scan on m_encounter_end_types eet (cost=0.00..13.40 rows=340 width=80) (actual time=0.003..0.003 rows=1 loops=1)

43. 0.001 0.001 ↓ 0.0 0 1

Index Scan using icm_ins_co_id_index on m_insurance_company_master sicm (cost=0.28..0.29 rows=1 width=32) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((pr.secondary_insurance_co)::text = (insurance_co_id)::text)
44. 0.006 0.006 ↑ 1.0 1 1

Index Scan using tpm_tpa_id_index on m_tpa_master ptm (cost=0.28..0.29 rows=1 width=33) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: ((pr.primary_sponsor_id)::text = (tpa_id)::text)
45. 0.001 0.001 ↓ 0.0 0 1

Index Scan using tpm_tpa_id_index on m_tpa_master stm (cost=0.28..0.29 rows=1 width=33) (actual time=0.001..0.001 rows=0 loops=1)

  • Index Cond: ((pr.secondary_sponsor_id)::text = (tpa_id)::text)
46.          

SubPlan (forNested Loop Left Join)

47. 0.054 0.090 ↑ 1.0 1 1

Hash Right Join (cost=8.46..43.06 rows=1 width=32) (actual time=0.089..0.090 rows=1 loops=1)

  • Hash Cond: ((sm_1.salutation)::text = (pd_1.salutation)::text)
48. 0.008 0.008 ↑ 80.5 22 1

Seq Scan on m_salutation_master sm_1 (cost=0.00..27.70 rows=1,770 width=5) (actual time=0.002..0.008 rows=22 loops=1)

49. 0.004 0.028 ↑ 1.0 1 1

Hash (cost=8.45..8.45 rows=1 width=33) (actual time=0.028..0.028 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
50. 0.024 0.024 ↑ 1.0 1 1

Index Scan using pd_mr_no_index on s_patient_details pd_1 (cost=0.43..8.45 rows=1 width=33) (actual time=0.024..0.024 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
51. 0.021 0.021 ↑ 1.0 1 1

Index Scan using pd_mr_no_index on s_patient_details pd_2 (cost=0.43..8.49 rows=1 width=32) (actual time=0.020..0.021 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
52. 0.004 0.056 ↑ 1.0 1 1

Nested Loop (cost=0.86..16.91 rows=1 width=12) (actual time=0.054..0.056 rows=1 loops=1)

53. 0.034 0.034 ↑ 1.0 1 1

Index Scan using pip_patient_id_index on s_patient_insurance_plans pip (cost=0.43..8.45 rows=1 width=4) (actual time=0.034..0.034 rows=1 loops=1)

  • Index Cond: ((pr.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 1)
54. 0.018 0.018 ↑ 1.0 1 1

Index Scan using ppd_policy_index on s_patient_policy_details ppd (cost=0.43..8.45 rows=1 width=16) (actual time=0.018..0.018 rows=1 loops=1)

  • Index Cond: (patient_policy_id = pip.patient_policy_id)
55. 0.001 0.007 ↓ 0.0 0 1

Nested Loop (cost=0.86..16.91 rows=1 width=12) (actual time=0.007..0.007 rows=0 loops=1)

56. 0.006 0.006 ↓ 0.0 0 1

Index Scan using pip_patient_id_index on s_patient_insurance_plans pip_1 (cost=0.43..8.45 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((pr.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
57. 0.000 0.000 ↓ 0.0 0

Index Scan using ppd_policy_index on s_patient_policy_details ppd_1 (cost=0.43..8.45 rows=1 width=16) (never executed)

  • Index Cond: (patient_policy_id = pip_1.patient_policy_id)
58. 0.003 0.012 ↑ 1.0 1 1

Nested Loop (cost=0.86..16.91 rows=1 width=8) (actual time=0.010..0.012 rows=1 loops=1)

59. 0.006 0.006 ↑ 1.0 1 1

Index Scan using pip_patient_id_index on s_patient_insurance_plans pip_2 (cost=0.43..8.45 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=1)

  • Index Cond: ((pr.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 1)
60. 0.003 0.003 ↑ 1.0 1 1

Index Scan using ppd_policy_index on s_patient_policy_details ppd_2 (cost=0.43..8.45 rows=1 width=12) (actual time=0.002..0.003 rows=1 loops=1)

  • Index Cond: (patient_policy_id = pip_2.patient_policy_id)
61. 0.002 0.008 ↓ 0.0 0 1

Nested Loop (cost=0.86..16.91 rows=1 width=8) (actual time=0.008..0.008 rows=0 loops=1)

62. 0.006 0.006 ↓ 0.0 0 1

Index Scan using pip_patient_id_index on s_patient_insurance_plans pip_3 (cost=0.43..8.45 rows=1 width=4) (actual time=0.006..0.006 rows=0 loops=1)

  • Index Cond: ((pr.patient_id)::text = (patient_id)::text)
  • Filter: (priority = 2)
  • Rows Removed by Filter: 1
63. 0.000 0.000 ↓ 0.0 0

Index Scan using ppd_policy_index on s_patient_policy_details ppd_3 (cost=0.43..8.45 rows=1 width=12) (never executed)

  • Index Cond: (patient_policy_id = pip_3.patient_policy_id)