explain.depesz.com

PostgreSQL's explain analyze made readable

Result: LlD6

Settings
# exclusive inclusive rows x rows loops node
1. 0.005 9,155.250 ↑ 1.0 1 1

Limit (cost=0.43..1,029,275.07 rows=1 width=802) (actual time=9,155.249..9,155.250 rows=1 loops=1)

2. 0.218 9,155.245 ↑ 18,036,325.0 1 1

Nested Loop Left Join (cost=0.43..18,564,331,958,433.48 rows=18,036,325 width=802) (actual time=9,155.245..9,155.245 rows=1 loops=1)

  • Join Filter: ((pr.secondary_sponsor_id)::text = (stm.tpa_id)::text)
  • Rows Removed by Join Filter: 566
3. 0.023 438.496 ↑ 18,036,325.0 1 1

Nested Loop Left Join (cost=0.43..14,386,847,027.09 rows=18,036,325 width=503) (actual time=438.496..438.496 rows=1 loops=1)

  • Join Filter: ((pr.primary_sponsor_id)::text = (ptm.tpa_id)::text)
  • Rows Removed by Join Filter: 50
4. 0.212 438.416 ↑ 18,036,325.0 1 1

Nested Loop Left Join (cost=0.43..14,233,718,610.77 rows=18,036,325 width=480) (actual time=438.416..438.416 rows=1 loops=1)

  • Join Filter: ((pr.secondary_insurance_co)::text = (sicm.insurance_co_id)::text)
  • Rows Removed by Join Filter: 653
5. 0.008 437.521 ↑ 18,036,325.0 1 1

Nested Loop Left Join (cost=0.43..14,057,052,789.23 rows=18,036,325 width=456) (actual time=437.521..437.521 rows=1 loops=1)

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

Nested Loop Left Join (cost=0.43..13,984,907,474.58 rows=10,609,603 width=428) (actual time=437.509..437.509 rows=1 loops=1)

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

Nested Loop Left Join (cost=0.43..13,942,469,047.93 rows=6,240,943 width=400) (actual time=437.492..437.492 rows=1 loops=1)

  • Join Filter: (etc.encounter_type_id = pr.encounter_type)
  • Rows Removed by Join Filter: 5
8. 0.135 437.475 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=0.43..13,923,746,204.50 rows=4,622,921 width=400) (actual time=437.475..437.475 rows=1 loops=1)

  • Join Filter: ((pr.primary_insurance_co)::text = (picm.insurance_co_id)::text)
  • Rows Removed by Join Filter: 400
9. 0.291 436.913 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=0.43..13,878,464,675.15 rows=4,622,921 width=376) (actual time=436.913..436.913 rows=1 loops=1)

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

Nested Loop Left Join (cost=0.43..13,812,449,342.36 rows=4,622,921 width=350) (actual time=435.666..435.666 rows=1 loops=1)

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

Nested Loop Left Join (cost=0.43..13,746,434,009.58 rows=4,622,921 width=324) (actual time=434.391..434.391 rows=1 loops=1)

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

Nested Loop Left Join (cost=0.43..13,744,700,411.90 rows=4,622,921 width=324) (actual time=434.361..434.361 rows=1 loops=1)

  • Join Filter: (ipm.plan_id = pr.plan_id)
  • Rows Removed by Join Filter: 23447
13. 0.068 225.027 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=0.43..10,932,391,004.81 rows=4,622,921 width=281) (actual time=225.027..225.027 rows=1 loops=1)

  • Join Filter: ((wn.ward_no)::text = (pr.ward_id)::text)
  • Rows Removed by Join Filter: 204
14. 0.018 224.745 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=0.43..10,918,244,862.00 rows=4,622,921 width=274) (actual time=224.745..224.745 rows=1 loops=1)

  • Join Filter: ((dept.dept_id)::text = (pr.dept_name)::text)
  • Rows Removed by Join Filter: 42
15. 0.253 224.677 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=0.43..10,914,153,575.17 rows=4,622,921 width=262) (actual time=224.677..224.677 rows=1 loops=1)

  • Join Filter: ((doc.doctor_id)::text = (pr.doctor)::text)
  • Rows Removed by Join Filter: 779
16. 0.021 223.524 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=0.43..10,795,298,209.84 rows=4,622,921 width=240) (actual time=223.524..223.524 rows=1 loops=1)

  • Join Filter: ((pd.salutation)::text = (sm.salutation)::text)
  • Rows Removed by Join Filter: 22
17. 52.543 223.470 ↑ 4,622,921.0 1 1

Nested Loop Left Join (cost=0.43..10,672,559,625.17 rows=4,622,921 width=249) (actual time=223.470..223.470 rows=1 loops=1)

  • Join Filter: ((bn.visit_id)::text = (pr.patient_id)::text)
  • Rows Removed by Join Filter: 153869
18. 0.027 0.077 ↑ 4,622,921.0 1 1

Nested Loop (cost=0.43..2,692,903.57 rows=4,622,921 width=231) (actual time=0.077..0.077 rows=1 loops=1)

19. 0.018 0.018 ↑ 4,622,921.0 1 1

Seq Scan on s_patient_registration pr (cost=0.00..250,461.21 rows=4,622,921 width=222) (actual time=0.018..0.018 rows=1 loops=1)

20. 0.032 0.032 ↑ 1.0 1 1

Index Scan using pd_mr_no_index on s_patient_details pd (cost=0.43..0.52 rows=1 width=24) (actual time=0.032..0.032 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
21. 110.563 170.850 ↑ 1.0 153,869 1

Materialize (cost=0.00..3,636.03 rows=153,869 width=34) (actual time=0.025..170.850 rows=153,869 loops=1)

22. 60.287 60.287 ↑ 1.0 153,869 1

Seq Scan on s_bed_details bn (cost=0.00..2,866.69 rows=153,869 width=34) (actual time=0.011..60.287 rows=153,869 loops=1)

23. 0.017 0.033 ↑ 80.5 22 1

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

24. 0.016 0.016 ↑ 80.5 22 1

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

25. 0.549 0.900 ↑ 2.2 780 1

Materialize (cost=0.00..70.71 rows=1,714 width=30) (actual time=0.008..0.900 rows=780 loops=1)

26. 0.351 0.351 ↑ 2.2 780 1

Seq Scan on m_doctors doc (cost=0.00..62.14 rows=1,714 width=30) (actual time=0.005..0.351 rows=780 loops=1)

27. 0.030 0.050 ↑ 1.4 43 1

Materialize (cost=0.00..1.88 rows=59 width=20) (actual time=0.008..0.050 rows=43 loops=1)

28. 0.020 0.020 ↑ 1.4 43 1

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

29. 0.142 0.214 ↑ 1.0 204 1

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

30. 0.072 0.072 ↑ 1.0 204 1

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

31. 192.698 202.043 ↑ 1.7 23,448 1

Materialize (cost=0.00..1,747.34 rows=40,556 width=47) (actual time=0.011..202.043 rows=23,448 loops=1)

32. 9.345 9.345 ↑ 1.7 23,448 1

Seq Scan on m_insurance_plan_main ipm (cost=0.00..1,544.56 rows=40,556 width=47) (actual time=0.005..9.345 rows=23,448 loops=1)

33. 0.006 0.016 ↑ 8.3 3 1

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

34. 0.010 0.010 ↑ 8.3 3 1

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

35. 0.617 0.973 ↑ 1.0 952 1

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

36. 0.356 0.356 ↑ 1.0 952 1

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

37. 0.613 0.956 ↑ 1.0 952 1

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

38. 0.343 0.343 ↑ 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.343 rows=952 loops=1)

39. 0.275 0.427 ↑ 1.6 401 1

Materialize (cost=0.00..19.80 rows=653 width=32) (actual time=0.008..0.427 rows=401 loops=1)

40. 0.152 0.152 ↑ 1.6 401 1

Seq Scan on m_insurance_company_master picm (cost=0.00..16.53 rows=653 width=32) (actual time=0.006..0.152 rows=401 loops=1)

41. 0.004 0.010 ↑ 45.0 6 1

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

42. 0.006 0.006 ↑ 45.0 6 1

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

43. 0.000 0.009 ↑ 340.0 1 1

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

44. 0.009 0.009 ↑ 340.0 1 1

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

45. 0.002 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)

46. 0.002 0.002 ↑ 340.0 1 1

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

47. 0.440 0.683 ↑ 1.0 653 1

Materialize (cost=0.00..19.80 rows=653 width=32) (actual time=0.006..0.683 rows=653 loops=1)

48. 0.243 0.243 ↑ 1.0 653 1

Seq Scan on m_insurance_company_master sicm (cost=0.00..16.53 rows=653 width=32) (actual time=0.003..0.243 rows=653 loops=1)

49. 0.035 0.057 ↑ 11.1 51 1

Materialize (cost=0.00..18.49 rows=566 width=33) (actual time=0.007..0.057 rows=51 loops=1)

50. 0.022 0.022 ↑ 11.1 51 1

Seq Scan on m_tpa_master ptm (cost=0.00..15.66 rows=566 width=33) (actual time=0.005..0.022 rows=51 loops=1)

51. 0.384 0.612 ↑ 1.0 566 1

Materialize (cost=0.00..18.49 rows=566 width=33) (actual time=0.004..0.612 rows=566 loops=1)

52. 0.228 0.228 ↑ 1.0 566 1

Seq Scan on m_tpa_master stm (cost=0.00..15.66 rows=566 width=33) (actual time=0.002..0.228 rows=566 loops=1)

53.          

SubPlan (forNested Loop Left Join)

54. 0.070 0.111 ↑ 1.0 1 1

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

  • Hash Cond: ((sm_1.salutation)::text = (pd_1.salutation)::text)
55. 0.011 0.011 ↑ 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.005..0.011 rows=22 loops=1)

56. 0.008 0.030 ↑ 1.0 1 1

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

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
57. 0.022 0.022 ↑ 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.021..0.022 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
58. 0.040 0.040 ↑ 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.039..0.040 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
59. 1,524.055 3,747.081 ↑ 1.0 1 1

Hash Join (cost=131,348.69..257,104.24 rows=1 width=12) (actual time=1,058.688..3,747.081 rows=1 loops=1)

  • Hash Cond: (ppd.patient_policy_id = pip.patient_policy_id)
60. 1,442.914 1,442.914 ↑ 1.0 4,412,773 1

Seq Scan on s_patient_policy_details ppd (cost=0.00..109,198.66 rows=4,415,166 width=16) (actual time=0.007..1,442.914 rows=4,412,773 loops=1)

61. 0.010 780.112 ↑ 1.0 1 1

Hash (cost=131,348.68..131,348.68 rows=1 width=4) (actual time=780.112..780.112 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
62. 780.102 780.102 ↑ 1.0 1 1

Seq Scan on s_patient_insurance_plans pip (cost=0.00..131,348.68 rows=1 width=4) (actual time=689.769..780.102 rows=1 loops=1)

  • Filter: ((priority = 1) AND ((pr.patient_id)::text = (patient_id)::text))
  • Rows Removed by Filter: 4322778
63. 0.015 601.401 ↓ 0.0 0 1

Hash Join (cost=131,348.69..257,104.24 rows=1 width=12) (actual time=601.401..601.401 rows=0 loops=1)

  • Hash Cond: (ppd_1.patient_policy_id = pip_1.patient_policy_id)
64. 0.010 0.010 ↑ 4,415,166.0 1 1

Seq Scan on s_patient_policy_details ppd_1 (cost=0.00..109,198.66 rows=4,415,166 width=16) (actual time=0.010..0.010 rows=1 loops=1)

65. 0.001 601.376 ↓ 0.0 0 1

Hash (cost=131,348.68..131,348.68 rows=1 width=4) (actual time=601.376..601.376 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
66. 601.375 601.375 ↓ 0.0 0 1

Seq Scan on s_patient_insurance_plans pip_1 (cost=0.00..131,348.68 rows=1 width=4) (actual time=601.375..601.375 rows=0 loops=1)

  • Filter: ((priority = 2) AND ((pr.patient_id)::text = (patient_id)::text))
  • Rows Removed by Filter: 4322779
67. 1,533.263 3,768.421 ↑ 1.0 1 1

Hash Join (cost=131,348.69..257,104.24 rows=1 width=8) (actual time=1,064.473..3,768.421 rows=1 loops=1)

  • Hash Cond: (ppd_2.patient_policy_id = pip_2.patient_policy_id)
68. 1,450.069 1,450.069 ↑ 1.0 4,412,773 1

Seq Scan on s_patient_policy_details ppd_2 (cost=0.00..109,198.66 rows=4,415,166 width=12) (actual time=0.018..1,450.069 rows=4,412,773 loops=1)

69. 0.008 785.089 ↑ 1.0 1 1

Hash (cost=131,348.68..131,348.68 rows=1 width=4) (actual time=785.089..785.089 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
70. 785.081 785.081 ↑ 1.0 1 1

Seq Scan on s_patient_insurance_plans pip_2 (cost=0.00..131,348.68 rows=1 width=4) (actual time=694.600..785.081 rows=1 loops=1)

  • Filter: ((priority = 1) AND ((pr.patient_id)::text = (patient_id)::text))
  • Rows Removed by Filter: 4322778
71. 0.023 598.865 ↓ 0.0 0 1

Hash Join (cost=131,348.69..257,104.24 rows=1 width=8) (actual time=598.865..598.865 rows=0 loops=1)

  • Hash Cond: (ppd_3.patient_policy_id = pip_3.patient_policy_id)
72. 0.010 0.010 ↑ 4,415,166.0 1 1

Seq Scan on s_patient_policy_details ppd_3 (cost=0.00..109,198.66 rows=4,415,166 width=12) (actual time=0.010..0.010 rows=1 loops=1)

73. 0.001 598.832 ↓ 0.0 0 1

Hash (cost=131,348.68..131,348.68 rows=1 width=4) (actual time=598.832..598.832 rows=0 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 8kB
74. 598.831 598.831 ↓ 0.0 0 1

Seq Scan on s_patient_insurance_plans pip_3 (cost=0.00..131,348.68 rows=1 width=4) (actual time=598.831..598.831 rows=0 loops=1)

  • Filter: ((priority = 2) AND ((pr.patient_id)::text = (patient_id)::text))
  • Rows Removed by Filter: 4322779
Planning time : 30.708 ms
Execution time : 9,159.397 ms