explain.depesz.com

PostgreSQL's explain analyze made readable

Result: Lohj

Settings
# exclusive inclusive rows x rows loops node
1. 0.017 4.835 ↑ 1.0 1 1

HashAggregate (cost=18,300.06..18,300.07 rows=1 width=76) (actual time=4.835..4.835 rows=1 loops=1)

2. 0.001 4.818 ↑ 1.0 1 1

Nested Loop Left Join (cost=18,272.16..18,300.05 rows=1 width=76) (actual time=4.803..4.818 rows=1 loops=1)

  • Join Filter: (((pd_1.mr_no)::text = (r.mr_no)::text) AND (pd_1.package_id = "*SELECT* 1".package_id))
3. 0.001 0.844 ↑ 1.0 1 1

Nested Loop Left Join (cost=27.19..55.04 rows=1 width=48) (actual time=0.829..0.844 rows=1 loops=1)

  • Join Filter: ((pd_1.mr_no)::text = (pd.mr_no)::text)
4. 0.005 0.831 ↑ 1.0 1 1

Nested Loop Left Join (cost=13.14..40.96 rows=1 width=12) (actual time=0.816..0.831 rows=1 loops=1)

  • Join Filter: ((pd.mr_no)::text = (patient_deposits.mr_no)::text)
5. 0.002 0.810 ↑ 1.0 1 1

Nested Loop Left Join (cost=8.84..26.87 rows=1 width=12) (actual time=0.795..0.810 rows=1 loops=1)

  • Join Filter: ((pd.mr_no)::text = (dst.mr_no)::text)
6. 0.003 0.731 ↑ 1.0 1 1

Nested Loop Left Join (cost=8.84..19.54 rows=1 width=9) (actual time=0.716..0.731 rows=1 loops=1)

7. 0.005 0.706 ↑ 1.0 1 1

Nested Loop (cost=8.56..11.23 rows=1 width=54) (actual time=0.693..0.706 rows=1 loops=1)

  • Join Filter: (pd.patient_group = cgm.confidentiality_grp_id)
  • Rows Removed by Join Filter: 2
8. 0.027 0.699 ↑ 1.0 1 1

Hash Right Join (cost=8.56..10.17 rows=1 width=58) (actual time=0.687..0.699 rows=1 loops=1)

  • Hash Cond: ((st.state_id)::text = (pd.patient_state)::text)
9. 0.004 0.004 ↑ 1.0 43 1

Seq Scan on state_master st (cost=0.00..1.43 rows=43 width=38) (actual time=0.003..0.004 rows=43 loops=1)

10. 0.003 0.668 ↑ 1.0 1 1

Hash (cost=8.55..8.55 rows=1 width=64) (actual time=0.668..0.668 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
11. 0.665 0.665 ↑ 1.0 1 1

Index Scan using patient_details_pkey on patient_details pd (cost=0.29..8.55 rows=1 width=64) (actual time=0.664..0.665 rows=1 loops=1)

  • Index Cond: ((mr_no)::text = 'SG20000098'::text)
  • Filter: patient_confidentiality_check(patient_group, mr_no)
12. 0.002 0.002 ↑ 1.0 3 1

Seq Scan on confidentiality_grp_master cgm (cost=0.00..1.03 rows=3 width=4) (actual time=0.001..0.002 rows=3 loops=1)

13. 0.022 0.022 ↑ 1.0 1 1

Index Scan using city_pkey on city ci (cost=0.28..8.29 rows=1 width=45) (actual time=0.021..0.022 rows=1 loops=1)

  • Index Cond: ((pd.patient_city)::text = (city_id)::text)
14. 0.077 0.077 ↓ 0.0 0 1

Seq Scan on deposit_setoff_total dst (cost=0.00..7.33 rows=1 width=12) (actual time=0.077..0.077 rows=0 loops=1)

  • Filter: ((mr_no)::text = 'SG20000098'::text)
  • Rows Removed by Filter: 348
15. 0.001 0.016 ↓ 0.0 0 1

GroupAggregate (cost=4.30..14.06 rows=1 width=14) (actual time=0.016..0.016 rows=0 loops=1)

16. 0.001 0.015 ↓ 0.0 0 1

Bitmap Heap Scan on patient_deposits (cost=4.30..14.05 rows=1 width=14) (actual time=0.015..0.015 rows=0 loops=1)

  • Recheck Cond: ((mr_no)::text = 'SG20000098'::text)
  • Filter: (realized <> 'Y'::bpchar)
17. 0.014 0.014 ↓ 0.0 0 1

Bitmap Index Scan on pd_mr_no_idx (cost=0.00..4.30 rows=3 width=0) (actual time=0.014..0.014 rows=0 loops=1)

  • Index Cond: ((mr_no)::text = 'SG20000098'::text)
18. 0.000 0.012 ↓ 0.0 0 1

HashAggregate (cost=14.05..14.06 rows=1 width=18) (actual time=0.012..0.012 rows=0 loops=1)

19. 0.001 0.012 ↓ 0.0 0 1

Bitmap Heap Scan on patient_deposits pd_1 (cost=4.30..14.04 rows=1 width=18) (actual time=0.012..0.012 rows=0 loops=1)

  • Recheck Cond: ((mr_no)::text = 'SG20000098'::text)
  • Filter: (package_id IS NOT NULL)
20. 0.011 0.011 ↓ 0.0 0 1

Bitmap Index Scan on pd_mr_no_idx (cost=0.00..4.30 rows=3 width=0) (actual time=0.011..0.011 rows=0 loops=1)

  • Index Cond: ((mr_no)::text = 'SG20000098'::text)
21. 0.001 3.973 ↓ 0.0 0 1

HashAggregate (cost=18,244.97..18,244.98 rows=1 width=16) (actual time=3.973..3.973 rows=0 loops=1)

22. 0.000 3.972 ↓ 0.0 0 1

Nested Loop (cost=18,140.39..18,244.96 rows=1 width=16) (actual time=3.972..3.972 rows=0 loops=1)

23. 0.010 3.972 ↓ 0.0 0 1

Hash Join (cost=18,140.11..18,236.40 rows=1 width=16) (actual time=3.972..3.972 rows=0 loops=1)

  • Hash Cond: ((bc.bill_no)::text = (b.bill_no)::text)
24. 0.100 3.893 ↑ 150.1 27 1

HashAggregate (cost=17,865.79..17,906.33 rows=4,054 width=14) (actual time=3.877..3.893 rows=27 loops=1)

25. 0.069 3.793 ↑ 25.7 158 1

Nested Loop (cost=45.41..17,845.52 rows=4,054 width=14) (actual time=0.550..3.793 rows=158 loops=1)

26. 0.011 3.414 ↑ 17.0 62 1

Append (cost=44.99..811.79 rows=1,054 width=8) (actual time=0.532..3.414 rows=62 loops=1)

27. 0.002 0.582 ↑ 1.8 4 1

Subquery Scan on *SELECT* 1 (cost=44.99..48.20 rows=7 width=8) (actual time=0.532..0.582 rows=4 loops=1)

28. 0.037 0.580 ↑ 1.8 4 1

Merge Join (cost=44.99..48.13 rows=7 width=8) (actual time=0.530..0.580 rows=4 loops=1)

  • Merge Cond: (sp.package_ref = pp.prescription_id)
29. 0.046 0.046 ↑ 1,981.5 64 1

Index Scan using services_prescribed_pkg_ref_idx on services_prescribed sp (cost=0.42..5,225.63 rows=126,814 width=8) (actual time=0.016..0.046 rows=64 loops=1)

30. 0.020 0.497 ↑ 4.4 33 1

Sort (cost=44.55..44.92 rows=146 width=8) (actual time=0.494..0.497 rows=33 loops=1)

  • Sort Key: pp.prescription_id
  • Sort Method: quicksort Memory: 26kB
31. 0.240 0.477 ↑ 4.6 32 1

Hash Join (cost=5.42..39.31 rows=146 width=8) (actual time=0.405..0.477 rows=32 loops=1)

  • Hash Cond: (pp.package_id = p_1.package_id)
32. 0.168 0.168 ↑ 1.0 1,081 1

Seq Scan on package_prescribed pp (cost=0.00..28.22 rows=1,122 width=8) (actual time=0.005..0.168 rows=1,081 loops=1)

33. 0.006 0.069 ↑ 1.4 19 1

Hash (cost=5.08..5.08 rows=27 width=4) (actual time=0.069..0.069 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
34. 0.063 0.063 ↑ 1.4 19 1

Seq Scan on pack_master p_1 (cost=0.00..5.08 rows=27 width=4) (actual time=0.025..0.063 rows=19 loops=1)

  • Filter: multi_visit_package
  • Rows Removed by Filter: 133
35. 0.006 0.571 ↑ 36.7 28 1

Subquery Scan on *SELECT* 2 (cost=5.71..637.72 rows=1,028 width=8) (actual time=0.370..0.571 rows=28 loops=1)

36. 0.013 0.565 ↑ 36.7 28 1

Nested Loop (cost=5.71..627.44 rows=1,028 width=8) (actual time=0.369..0.565 rows=28 loops=1)

37. 0.230 0.424 ↑ 4.6 32 1

Hash Join (cost=5.42..39.31 rows=146 width=8) (actual time=0.357..0.424 rows=32 loops=1)

  • Hash Cond: (pp_1.package_id = p_2.package_id)
38. 0.134 0.134 ↑ 1.0 1,081 1

Seq Scan on package_prescribed pp_1 (cost=0.00..28.22 rows=1,122 width=8) (actual time=0.002..0.134 rows=1,081 loops=1)

39. 0.005 0.060 ↑ 1.4 19 1

Hash (cost=5.08..5.08 rows=27 width=4) (actual time=0.060..0.060 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
40. 0.055 0.055 ↑ 1.4 19 1

Seq Scan on pack_master p_2 (cost=0.00..5.08 rows=27 width=4) (actual time=0.021..0.055 rows=19 loops=1)

  • Filter: multi_visit_package
  • Rows Removed by Filter: 133
41. 0.128 0.128 ↑ 8.0 1 32

Index Scan using tests_prescribed_package_ref_is_null_idx on tests_prescribed tp (cost=0.29..3.95 rows=8 width=8) (actual time=0.003..0.004 rows=1 loops=32)

  • Index Cond: (package_ref = pp_1.prescription_id)
42. 0.006 1.617 ↓ 1.6 29 1

Subquery Scan on *SELECT* 3 (cost=45.28..79.79 rows=18 width=8) (actual time=0.614..1.617 rows=29 loops=1)

43. 0.033 1.611 ↓ 1.6 29 1

Nested Loop Left Join (cost=45.28..79.61 rows=18 width=8) (actual time=0.614..1.611 rows=29 loops=1)

44. 0.035 0.708 ↓ 1.6 29 1

Merge Join (cost=44.85..55.98 rows=18 width=12) (actual time=0.568..0.708 rows=29 loops=1)

  • Merge Cond: (dc.package_ref = pp_2.prescription_id)
45. 0.245 0.245 ↑ 92.7 144 1

Index Scan using doctor_consultation_pkg_ref_idx on doctor_consultation dc (cost=0.29..950.17 rows=13,352 width=12) (actual time=0.010..0.245 rows=144 loops=1)

46. 0.024 0.428 ↑ 3.5 42 1

Sort (cost=44.55..44.92 rows=146 width=8) (actual time=0.416..0.428 rows=42 loops=1)

  • Sort Key: pp_2.prescription_id
  • Sort Method: quicksort Memory: 26kB
47. 0.222 0.404 ↑ 4.6 32 1

Hash Join (cost=5.42..39.31 rows=146 width=8) (actual time=0.346..0.404 rows=32 loops=1)

  • Hash Cond: (pp_2.package_id = p_3.package_id)
48. 0.123 0.123 ↑ 1.0 1,081 1

Seq Scan on package_prescribed pp_2 (cost=0.00..28.22 rows=1,122 width=8) (actual time=0.002..0.123 rows=1,081 loops=1)

49. 0.004 0.059 ↑ 1.4 19 1

Hash (cost=5.08..5.08 rows=27 width=4) (actual time=0.059..0.059 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
50. 0.055 0.055 ↑ 1.4 19 1

Seq Scan on pack_master p_3 (cost=0.00..5.08 rows=27 width=4) (actual time=0.021..0.055 rows=19 loops=1)

  • Filter: multi_visit_package
  • Rows Removed by Filter: 133
51. 0.870 0.870 ↑ 1.0 1 29

Index Scan using bac_activity_id_index on bill_activity_charge bac (cost=0.42..1.30 rows=1 width=15) (actual time=0.030..0.030 rows=1 loops=29)

  • Index Cond: ((activity_id)::text = (dc.consultation_id)::text)
  • Filter: ((activity_code)::text = 'DOC'::text)
  • Rows Removed by Filter: 3
52. 0.000 0.633 ↑ 1.0 1 1

Subquery Scan on *SELECT* 4 (cost=44.85..46.07 rows=1 width=8) (actual time=0.621..0.633 rows=1 loops=1)

53. 0.018 0.633 ↑ 1.0 1 1

Merge Join (cost=44.85..46.06 rows=1 width=8) (actual time=0.621..0.633 rows=1 loops=1)

  • Merge Cond: (osp.package_ref = pp_3.prescription_id)
54. 0.041 0.041 ↑ 956.4 14 1

Index Scan using other_services_prescribed_pkg_ref_idx on other_services_prescribed osp (cost=0.29..558.12 rows=13,389 width=8) (actual time=0.024..0.041 rows=14 loops=1)

55. 0.029 0.574 ↑ 4.7 31 1

Sort (cost=44.55..44.92 rows=146 width=8) (actual time=0.573..0.574 rows=31 loops=1)

  • Sort Key: pp_3.prescription_id
  • Sort Method: quicksort Memory: 26kB
56. 0.285 0.545 ↑ 4.6 32 1

Hash Join (cost=5.42..39.31 rows=146 width=8) (actual time=0.482..0.545 rows=32 loops=1)

  • Hash Cond: (pp_3.package_id = p_4.package_id)
57. 0.191 0.191 ↑ 1.0 1,081 1

Seq Scan on package_prescribed pp_3 (cost=0.00..28.22 rows=1,122 width=8) (actual time=0.039..0.191 rows=1,081 loops=1)

58. 0.005 0.069 ↑ 1.4 19 1

Hash (cost=5.08..5.08 rows=27 width=4) (actual time=0.069..0.069 rows=19 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
59. 0.064 0.064 ↑ 1.4 19 1

Seq Scan on pack_master p_4 (cost=0.00..5.08 rows=27 width=4) (actual time=0.026..0.064 rows=19 loops=1)

  • Filter: multi_visit_package
  • Rows Removed by Filter: 133
60. 0.310 0.310 ↑ 1.3 3 62

Index Scan using idx_bill_charge_order_no on bill_charge bc (cost=0.42..16.12 rows=4 width=14) (actual time=0.004..0.005 rows=3 loops=62)

  • Index Cond: (order_number = "*SELECT* 1".common_order_id)
61. 0.002 0.069 ↑ 25.0 1 1

Hash (cost=274.00..274.00 rows=25 width=22) (actual time=0.069..0.069 rows=1 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 1kB
62. 0.002 0.067 ↑ 25.0 1 1

Nested Loop (cost=5.01..274.00 rows=25 width=22) (actual time=0.066..0.067 rows=1 loops=1)

63. 0.003 0.032 ↑ 22.0 1 1

Bitmap Heap Scan on patient_registration r (cost=4.59..88.10 rows=22 width=18) (actual time=0.031..0.032 rows=1 loops=1)

  • Recheck Cond: ((mr_no)::text = 'SG20000098'::text)
64. 0.029 0.029 ↑ 7.3 3 1

Bitmap Index Scan on patient_registration_mr_no_index (cost=0.00..4.58 rows=22 width=0) (actual time=0.029..0.029 rows=3 loops=1)

  • Index Cond: ((mr_no)::text = 'SG20000098'::text)
65. 0.033 0.033 ↑ 1.0 1 1

Index Scan using bill_visit_id_idx on bill b (cost=0.42..8.44 rows=1 width=22) (actual time=0.033..0.033 rows=1 loops=1)

  • Index Cond: ((visit_id)::text = (r.patient_id)::text)
66. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_details_pkey on patient_details p (cost=0.29..8.55 rows=1 width=9) (never executed)

  • Index Cond: ((mr_no)::text = 'SG20000098'::text)
  • Filter: patient_confidentiality_check(COALESCE(patient_group, 0), mr_no)