explain.depesz.com

PostgreSQL's explain analyze made readable

Result: NYKA

Settings
# exclusive inclusive rows x rows loops node
1. 0.229 61,360.699 ↓ 4.0 20 1

Limit (cost=1,836,190.96..1,836,190.97 rows=5 width=253) (actual time=61,360.474..61,360.699 rows=20 loops=1)

2. 0.303 61,360.470 ↓ 4.0 20 1

Sort (cost=1,836,190.96..1,836,190.97 rows=5 width=253) (actual time=61,360.468..61,360.470 rows=20 loops=1)

  • Sort Key: "*SELECT* 1".visited_date DESC, "*SELECT* 1".pres_date DESC
  • Sort Method: top-N heapsort Memory: 32kB
3. 0.084 61,360.167 ↓ 28.2 141 1

Result (cost=25,710.29..1,836,190.90 rows=5 width=253) (actual time=5,959.805..61,360.167 rows=141 loops=1)

4. 0.144 61,360.083 ↓ 28.2 141 1

Append (cost=25,710.29..1,836,190.73 rows=5 width=285) (actual time=5,959.797..61,360.083 rows=141 loops=1)

5. 0.056 6,637.956 ↓ 125.0 125 1

Subquery Scan on *SELECT* 1 (cost=25,710.29..821,328.91 rows=1 width=261) (actual time=5,959.790..6,637.956 rows=125 loops=1)

6. 4.092 6,637.900 ↓ 125.0 125 1

Nested Loop (cost=25,710.29..821,328.88 rows=1 width=419) (actual time=5,959.775..6,637.900 rows=125 loops=1)

  • Join Filter: ((atp.test_id)::text = (d.test_id)::text)
7. 0.155 6,633.058 ↓ 125.0 125 1

Hash Join (cost=25,710.02..821,323.92 rows=1 width=221) (actual time=5,958.818..6,633.058 rows=125 loops=1)

  • Hash Cond: ((atp.ddept_id)::text = (dd.ddept_id)::text)
8. 0.408 6,632.875 ↓ 7.4 125 1

Hash Join (cost=25,708.27..821,322.13 rows=17 width=245) (actual time=5,958.747..6,632.875 rows=125 loops=1)

  • Hash Cond: ((tp.test_id)::text = (atp.test_id)::text)
9. 0.174 6,626.456 ↓ 20.8 125 1

Nested Loop (cost=25,119.72..820,731.46 rows=6 width=149) (actual time=5,952.634..6,626.456 rows=125 loops=1)

10. 0.213 6,626.032 ↓ 20.8 125 1

Nested Loop (cost=25,119.59..820,730.41 rows=6 width=153) (actual time=5,952.603..6,626.032 rows=125 loops=1)

11. 0.082 6,624.319 ↓ 20.8 125 1

Nested Loop (cost=25,119.16..820,705.98 rows=6 width=119) (actual time=5,952.519..6,624.319 rows=125 loops=1)

12. 0.055 0.055 ↑ 1.0 1 1

Index Scan using doctors_pkey on doctors doc (cost=0.28..4.32 rows=1 width=30) (actual time=0.046..0.055 rows=1 loops=1)

  • Index Cond: ((doctor_id)::text = 'DOC0887'::text)
13. 0.159 6,624.182 ↓ 20.8 125 1

Nested Loop Left Join (cost=25,118.88..820,701.48 rows=6 width=96) (actual time=5,952.443..6,624.182 rows=125 loops=1)

14. 0.086 6,624.023 ↓ 20.8 125 1

Nested Loop Left Join (cost=25,118.44..820,675.16 rows=6 width=87) (actual time=5,952.405..6,624.023 rows=125 loops=1)

15. 6.392 6,623.812 ↓ 20.8 125 1

Nested Loop (cost=25,118.15..820,670.69 rows=6 width=87) (actual time=5,952.323..6,623.812 rows=125 loops=1)

16. 3,040.157 6,350.960 ↓ 1.1 8,882 1

Bitmap Heap Scan on patient_registration pr (cost=25,117.59..472,783.59 rows=8,368 width=41) (actual time=3,380.766..6,350.960 rows=8,882 loops=1)

  • Recheck Cond: (center_id = 12)
  • Filter: ((reg_date >= '2020-01-26'::date) AND ((observation_patient)::text = 'N'::text) AND (visit_type = 'o'::bpchar))
  • Rows Removed by Filter: 1032734
  • Heap Blocks: exact=343870
17. 3,310.803 3,310.803 ↓ 1.0 1,052,503 1

Bitmap Index Scan on patient_registarion_center_idx (cost=0.00..25,115.50 rows=1,025,475 width=0) (actual time=3,310.802..3,310.803 rows=1,052,503 loops=1)

  • Index Cond: (center_id = 12)
18. 266.460 266.460 ↓ 0.0 0 8,882

Index Scan using pat_id_index on tests_prescribed tp (cost=0.56..41.54 rows=1 width=61) (actual time=0.030..0.030 rows=0 loops=8,882)

  • Index Cond: ((pat_id)::text = (pr.patient_id)::text)
  • Filter: ((pres_doctor)::text = 'DOC0887'::text)
  • Rows Removed by Filter: 1
19. 0.125 0.125 ↓ 0.0 0 125

Index Scan using package_prescribed_pkey on package_prescribed pp (cost=0.29..0.75 rows=1 width=8) (actual time=0.001..0.001 rows=0 loops=125)

  • Index Cond: (tp.package_ref = prescription_id)
20. 0.000 0.000 ↓ 0.0 0 125

Index Scan using tests_prescribed_pkey on tests_prescribed tpp (cost=0.43..4.39 rows=1 width=13) (actual time=0.000..0.000 rows=0 loops=125)

  • Index Cond: (prescribed_id = tp.outsource_dest_prescribed_id)
21. 1.500 1.500 ↑ 1.0 1 125

Index Scan using patient_details_pkey on patient_details pd (cost=0.43..4.07 rows=1 width=49) (actual time=0.012..0.012 rows=1 loops=125)

  • Index Cond: ((mr_no)::text = (pr.mr_no)::text)
22. 0.250 0.250 ↑ 1.0 1 125

Index Scan using salutation_master_pkey on salutation_master sm (cost=0.14..0.18 rows=1 width=14) (actual time=0.002..0.002 rows=1 loops=125)

  • Index Cond: ((salutation_id)::text = (pd.salutation)::text)
23. 0.604 6.011 ↑ 1.0 2,173 1

Hash (cost=517.92..517.92 rows=2,173 width=96) (actual time=6.011..6.011 rows=2,173 loops=1)

  • Buckets: 4096 Batches: 1 Memory Usage: 201kB
24. 0.186 5.407 ↑ 1.0 2,173 1

Subquery Scan on atp (cost=387.54..517.92 rows=2,173 width=96) (actual time=4.423..5.407 rows=2,173 loops=1)

25. 2.790 5.221 ↑ 1.0 2,173 1

HashAggregate (cost=387.54..452.73 rows=2,173 width=420) (actual time=4.422..5.221 rows=2,173 loops=1)

  • Group Key: diagnostics.test_name, diagnostics.test_id, (false), ('item_master'::text), diagnostics.status, diagnostics.diag_code, diagnostics.prior_auth_required, diagnostics.ddept_id, (''::bpchar), ('A'::bpchar), diagnostics.stat, diagnostics.consent_required, diagnostics.clinical_information_form, diagnostics.allergen_test, diagnostics.vat_percent, diagnostics.vat_option, diagnostics.rateplan_category_id
26. 0.115 2.431 ↑ 1.0 2,173 1

Append (cost=0.00..295.19 rows=2,173 width=420) (actual time=0.020..2.431 rows=2,173 loops=1)

27. 1.932 1.932 ↑ 1.0 1,941 1

Seq Scan on diagnostics (cost=0.00..163.23 rows=1,941 width=173) (actual time=0.019..1.932 rows=1,941 loops=1)

28. 0.027 0.384 ↑ 1.0 232 1

Subquery Scan on *SELECT* 2_1 (cost=0.00..73.73 rows=232 width=219) (actual time=0.028..0.384 rows=232 loops=1)

29. 0.357 0.357 ↑ 1.0 232 1

Seq Scan on pack_master (cost=0.00..66.77 rows=232 width=219) (actual time=0.026..0.357 rows=232 loops=1)

  • Filter: (package_type = 'd'::bpchar)
  • Rows Removed by Filter: 1079
30. 0.008 0.028 ↑ 1.0 12 1

Hash (cost=1.36..1.36 rows=12 width=18) (actual time=0.028..0.028 rows=12 loops=1)

  • Buckets: 1024 Batches: 1 Memory Usage: 9kB
31. 0.020 0.020 ↑ 1.0 12 1

Seq Scan on diagnostics_departments dd (cost=0.00..1.36 rows=12 width=18) (actual time=0.018..0.020 rows=12 loops=1)

32. 0.750 0.750 ↑ 1.0 1 125

Index Scan using diagnostics_pkey on diagnostics d (cost=0.28..0.35 rows=1 width=10) (actual time=0.006..0.006 rows=1 loops=125)

  • Index Cond: ((test_id)::text = (tp.test_id)::text)
  • Filter: conduction_applicable
33.          

SubPlan (for Nested Loop)

34. 0.000 0.000 ↓ 0.0 0 125

Index Scan using pack_temp__id_primary on pack_master pm (cost=0.28..4.32 rows=1 width=32) (actual time=0.000..0.000 rows=0 loops=125)

  • Index Cond: (pp.package_id = package_id)
35. 0.008 45,720.334 ↓ 0.0 0 1

Subquery Scan on *SELECT* 2 (cost=390.23..20,920.72 rows=1 width=291) (actual time=45,720.334..45,720.334 rows=0 loops=1)

36. 0.004 45,720.326 ↓ 0.0 0 1

Nested Loop (cost=390.23..20,920.69 rows=1 width=291) (actual time=45,720.326..45,720.326 rows=0 loops=1)

37. 0.009 45,720.322 ↓ 0.0 0 1

Nested Loop (cost=390.09..20,920.26 rows=1 width=155) (actual time=45,720.322..45,720.322 rows=0 loops=1)

38. 0.004 45,720.313 ↓ 0.0 0 1

Nested Loop (cost=389.66..20,916.19 rows=1 width=121) (actual time=45,720.313..45,720.313 rows=0 loops=1)

39. 0.005 45,720.309 ↓ 0.0 0 1

Nested Loop Left Join (cost=389.38..20,911.84 rows=1 width=99) (actual time=45,720.309..45,720.309 rows=0 loops=1)

40. 0.004 45,720.304 ↓ 0.0 0 1

Nested Loop (cost=389.25..20,911.66 rows=1 width=123) (actual time=45,720.304..45,720.304 rows=0 loops=1)

  • Join Filter: ((ptp.test_id)::text = (diagnostics_1.test_id)::text)
41. 0.004 45,720.300 ↓ 0.0 0 1

Nested Loop (cost=1.71..20,323.12 rows=1 width=74) (actual time=45,720.300..45,720.300 rows=0 loops=1)

42. 0.004 45,720.296 ↓ 0.0 0 1

Nested Loop (cost=1.15..20,318.51 rows=1 width=49) (actual time=45,720.296..45,720.296 rows=0 loops=1)

43. 0.009 45,720.292 ↓ 0.0 0 1

Nested Loop (cost=0.87..20,318.19 rows=1 width=41) (actual time=45,720.292..45,720.292 rows=0 loops=1)

44. 45,720.283 45,720.283 ↓ 0.0 0 1

Index Scan using patient_prescription_consult_idx on patient_prescription pp_1 (cost=0.44..20,309.19 rows=2 width=30) (actual time=45,720.283..45,720.283 rows=0 loops=1)

  • Index Cond: (consultation_id IS NULL)
  • Filter: (((doctor_id)::text = 'DOC0887'::text) AND ((presc_type)::text = 'Inv.'::text) AND (((status)::text = 'P'::text) OR ((status)::text = 'X'::text)))
  • Rows Removed by Filter: 285621
45. 0.000 0.000 ↓ 0.0 0

Index Scan using ptp_prescription_idx on patient_test_prescriptions ptp (cost=0.43..4.47 rows=1 width=19) (never executed)

  • Index Cond: (op_test_pres_id = pp_1.patient_presc_id)
46. 0.000 0.000 ↓ 0.0 0

Index Scan using diagnostics_pkey on diagnostics d_1 (cost=0.28..0.32 rows=1 width=8) (never executed)

  • Index Cond: ((test_id)::text = (ptp.test_id)::text)
  • Filter: conduction_applicable
47. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_registration_pkey on patient_registration pr_1 (cost=0.56..4.61 rows=1 width=41) (never executed)

  • Index Cond: ((patient_id)::text = (pp_1.visit_id)::text)
  • Filter: ((reg_date >= '2020-01-26'::date) AND ((observation_patient)::text = 'N'::text) AND (visit_type = 'o'::bpchar) AND (center_id = 12))
48. 0.000 0.000 ↓ 0.0 0

HashAggregate (cost=387.54..452.73 rows=2,173 width=420) (never executed)

  • Group Key: diagnostics_1.test_name, diagnostics_1.test_id, (false), ('item_master'::text), diagnostics_1.status, diagnostics_1.diag_code, diagnostics_1.prior_auth_required, diagnostics_1.ddept_id, (''::bpchar), ('A'::bpchar), diagnostics_1.stat, diagnostics_1.consent_required, diagnostics_1.clinical_information_form, diagnostics_1.allergen_test, diagnostics_1.vat_percent, diagnostics_1.vat_option, diagnostics_1.rateplan_category_id
49. 0.000 0.000 ↓ 0.0 0

Append (cost=0.00..295.19 rows=2,173 width=420) (never executed)

50. 0.000 0.000 ↓ 0.0 0

Seq Scan on diagnostics diagnostics_1 (cost=0.00..163.23 rows=1,941 width=173) (never executed)

51. 0.000 0.000 ↓ 0.0 0

Subquery Scan on *SELECT* 2_2 (cost=0.00..73.73 rows=232 width=219) (never executed)

52. 0.000 0.000 ↓ 0.0 0

Seq Scan on pack_master pack_master_1 (cost=0.00..66.77 rows=232 width=219) (never executed)

  • Filter: (package_type = 'd'::bpchar)
53. 0.000 0.000 ↓ 0.0 0

Index Scan using diagnostics_departments_pkey on diagnostics_departments dd_1 (cost=0.14..0.18 rows=1 width=18) (never executed)

  • Index Cond: ((diagnostics_1.ddept_id)::text = (ddept_id)::text)
54. 0.000 0.000 ↓ 0.0 0

Index Scan using doctors_pkey on doctors doc1 (cost=0.28..4.32 rows=1 width=30) (never executed)

  • Index Cond: ((doctor_id)::text = 'DOC0887'::text)
55. 0.000 0.000 ↓ 0.0 0

Index Scan using patient_details_pkey on patient_details pd_1 (cost=0.43..4.07 rows=1 width=49) (never executed)

  • Index Cond: ((mr_no)::text = (pr_1.mr_no)::text)
56. 0.000 0.000 ↓ 0.0 0

Index Scan using salutation_master_pkey on salutation_master sm_1 (cost=0.14..0.18 rows=1 width=14) (never executed)

  • Index Cond: ((salutation_id)::text = (pd_1.salutation)::text)
57. 0.038 9,001.649 ↓ 5.3 16 1

Subquery Scan on *SELECT* 3 (cost=390.23..993,941.10 rows=3 width=291) (actual time=4,401.510..9,001.649 rows=16 loops=1)

58. 0.535 9,001.611 ↓ 5.3 16 1

Nested Loop (cost=390.23..993,941.01 rows=3 width=291) (actual time=4,401.487..9,001.611 rows=16 loops=1)

59. 0.035 9,001.028 ↓ 5.3 16 1

Nested Loop (cost=390.09..993,939.72 rows=3 width=155) (actual time=4,401.251..9,001.028 rows=16 loops=1)

60. 0.047 9,000.801 ↓ 5.3 16 1

Nested Loop (cost=389.66..993,927.50 rows=3 width=121) (actual time=4,401.185..9,000.801 rows=16 loops=1)

  • Join Filter: ((diagnostics_2.test_id)::text = (d_2.test_id)::text)
61. 0.041 9,000.642 ↓ 5.3 16 1

Nested Loop Left Join (cost=389.39..993,926.46 rows=3 width=160) (actual time=4,401.140..9,000.642 rows=16 loops=1)

62. 2.593 9,000.521 ↓ 5.3 16 1

Nested Loop (cost=389.25..993,925.93 rows=3 width=184) (actual time=4,401.075..9,000.521 rows=16 loops=1)

  • Join Filter: ((ptp_1.test_id)::text = (diagnostics_2.test_id)::text)
  • Rows Removed by Join Filter: 34752
63. 0.034 8,978.696 ↓ 16.0 16 1

Nested Loop (cost=1.71..993,337.39 rows=1 width=88) (actual time=4,392.217..8,978.696 rows=16 loops=1)

64. 0.043 8,978.566 ↓ 16.0 16 1

Nested Loop (cost=1.43..993,333.04 rows=1 width=66) (actual time=4,392.174..8,978.566 rows=16 loops=1)

65. 1.928 8,975.739 ↓ 5.3 16 1

Nested Loop (cost=1.00..993,331.37 rows=3 width=55) (actual time=4,391.522..8,975.739 rows=16 loops=1)

66. 8.177 8,803.864 ↑ 3.6 821 1

Nested Loop (cost=0.44..991,025.27 rows=2,992 width=30) (actual time=1.736..8,803.864 rows=821 loops=1)

67. 3,891.408 3,891.408 ↓ 1.6 6,709 1

Seq Scan on doctor_consultation dc (cost=0.00..961,887.57 rows=4,297 width=12) (actual time=1.308..3,891.408 rows=6,709 loops=1)

  • Filter: (((COALESCE(cancel_status, ''::character varying))::text <> 'C'::text) AND ((doctor_name)::text = 'DOC0887'::text))
  • Rows Removed by Filter: 8171241
68. 4,904.279 4,904.279 ↓ 0.0 0 6,709

Index Scan using patient_prescription_consult_idx on patient_prescription pp_2 (cost=0.44..6.51 rows=9 width=26) (actual time=0.724..0.731 rows=0 loops=6,709)

  • Index Cond: ((consultation_id = dc.consultation_id) AND (consultation_id IS NOT NULL))
  • Filter: (((presc_type)::text = 'Inv.'::text) AND (((status)::text = 'P'::text) OR ((status)::text = 'X'::text)))
  • Rows Removed by Filter: 4
69. 169.947 169.947 ↓ 0.0 0 821

Index Scan using patient_registration_pkey on patient_registration pr_2 (cost=0.56..0.77 rows=1 width=41) (actual time=0.207..0.207 rows=0 loops=821)

  • Index Cond: ((patient_id)::text = (pp_2.visit_id)::text)
  • Filter: ((reg_date >= '2020-01-26'::date) AND ((observation_patient)::text = 'N'::text) AND (visit_type = 'o'::bpchar) AND (center_id = 12))
  • Rows Removed by Filter: 1
70. 2.784 2.784 ↑ 1.0 1 16

Index Scan using ptp_prescription_idx on patient_test_prescriptions ptp_1 (cost=0.43..0.53 rows=1 width=19) (actual time=0.174..0.174 rows=1 loops=16)

  • Index Cond: (op_test_pres_id = pp_2.patient_presc_id)
71. 0.096 0.096 ↑ 1.0 1 16

Index Scan using doctors_pkey on doctors doc_1 (cost=0.28..4.32 rows=1 width=30) (actual time=0.006..0.006 rows=1 loops=16)

  • Index Cond: ((doctor_id)::text = 'DOC0887'::text)
72. 16.429 19.232 ↑ 1.0 2,173 16

HashAggregate (cost=387.54..452.73 rows=2,173 width=420) (actual time=0.458..1.202 rows=2,173 loops=16)

  • Group Key: diagnostics_2.test_name, diagnostics_2.test_id, (false), ('item_master'::text), diagnostics_2.status, diagnostics_2.diag_code, diagnostics_2.prior_auth_required, diagnostics_2.ddept_id, (''::bpchar), ('A'::bpchar), diagnostics_2.stat, diagnostics_2.consent_required, diagnostics_2.clinical_information_form, diagnostics_2.allergen_test, diagnostics_2.vat_percent, diagnostics_2.vat_option, diagnostics_2.rateplan_category_id
73. 0.322 2.803 ↑ 1.0 2,173 1

Append (cost=0.00..295.19 rows=2,173 width=420) (actual time=0.019..2.803 rows=2,173 loops=1)

74. 1.815 1.815 ↑ 1.0 1,941 1

Seq Scan on diagnostics diagnostics_2 (cost=0.00..163.23 rows=1,941 width=173) (actual time=0.017..1.815 rows=1,941 loops=1)

75. 0.071 0.666 ↑ 1.0 232 1

Subquery Scan on *SELECT* 2_3 (cost=0.00..73.73 rows=232 width=219) (actual time=0.044..0.666 rows=232 loops=1)

76. 0.595 0.595 ↑ 1.0 232 1

Seq Scan on pack_master pack_master_2 (cost=0.00..66.77 rows=232 width=219) (actual time=0.038..0.595 rows=232 loops=1)

  • Filter: (package_type = 'd'::bpchar)
  • Rows Removed by Filter: 1079
77. 0.080 0.080 ↑ 1.0 1 16

Index Scan using diagnostics_departments_pkey on diagnostics_departments dd_2 (cost=0.14..0.18 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=16)

  • Index Cond: ((diagnostics_2.ddept_id)::text = (ddept_id)::text)
78. 0.112 0.112 ↑ 1.0 1 16

Index Scan using diagnostics_pkey on diagnostics d_2 (cost=0.28..0.32 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=16)

  • Index Cond: ((test_id)::text = (ptp_1.test_id)::text)
  • Filter: conduction_applicable
79. 0.192 0.192 ↑ 1.0 1 16

Index Scan using patient_details_pkey on patient_details pd_2 (cost=0.43..4.07 rows=1 width=49) (actual time=0.012..0.012 rows=1 loops=16)

  • Index Cond: ((mr_no)::text = (pr_2.mr_no)::text)
80. 0.048 0.048 ↑ 1.0 1 16

Index Scan using salutation_master_pkey on salutation_master sm_2 (cost=0.14..0.18 rows=1 width=14) (actual time=0.003..0.003 rows=1 loops=16)

  • Index Cond: ((salutation_id)::text = (pd_2.salutation)::text)
Planning time : 110.890 ms
Execution time : 61,376.528 ms