Индекси и анализа на перформанси

---

Тука ќе ги опфатиме индексите што не се автоматски покриени со PK/UK и во реална примена би можеле да придонесат за подобри перформанси во SynergyMed. Фокусот е на најфреквентните пребарувања, JOIN операции и критични бизнис сценарија.

Со овие индекси:

• Покриваме најфреквентни пребарувања (username, e_mail, embg, medicine_name).
• Подобрена е временската анализа (payment_date, order_date, last_changed).
• Финансиски извештаи се оптимизирани (status + payment_date).
• JOIN операциите се побрзи (FK кон payment_id, manufacturer_id, branded_medicine_id).
• Клучни здравствени функции (рецепти, интеракции на лекови) се извршуваат ефикасно.

---

Сценарио 1: Финансиски извештаи

Овие анализи се вршат преку филтрирање на payment.status и payment.payment_date, како и преку JOIN помеѓу clientorder и payment.

Оптимизација: Со композитен индекс (status, payment_date) и индекс врз clientorder(payment_id), пребарувањето е значително побрзо, а ORDER BY операцијата често може да се изврши директно преку индекс.

CREATE INDEX IF NOT EXISTS idx_payment_status_date
  ON synergymed.payment(status, payment_date);
CREATE INDEX IF NOT EXISTS idx_clientorder_payment_id
  ON synergymed.clientorder(payment_id);

---

Сценарио 2: Анализа на залихи по аптека

Оптимизација:

• Индекс врз branded_medicine_id за брзо групирање и агрегација.
• Индекс врз last_changed за побрзи временски анализи.

CREATE INDEX IF NOT EXISTS idx_inventorybm_bmid
  ON synergymed.inventory_brandedmedicine(branded_medicine_id);
CREATE INDEX IF NOT EXISTS idx_inventorybm_last_changed
  ON synergymed.inventory_brandedmedicine(last_changed);

---

Сценарио 3: Пребарување по пациенти и рецепти

Лекарите пребаруваат рецепти според пациент (client_id) или според лек (medicine_id). Дополнително, се проверува важноста (valid_to) на рецептите за да се открие дали се активни.

Оптимизација:

• Индекс врз prescription(client_id)
• Индекс врз prescription(medicine_id)
• Индекс врз prescription(valid_to) за да се филтрираат активни рецепти

CREATE INDEX IF NOT EXISTS idx_prescription_client
  ON synergymed.prescription(client_id);
CREATE INDEX IF NOT EXISTS idx_prescription_medicine
  ON synergymed.prescription(medicine_id);
CREATE INDEX IF NOT EXISTS idx_prescription_valid_to
  ON synergymed.prescription(valid_to);

---

Сценарио 4: Проверка на интеракции на лекови

Со цел проверка дали ново препишан лек влегува во интеракција со постоечките лекови кај пациент, често се врши пребарување по комбинација од medicine_id_1 и medicine_id_2, како и по нивната severity.

Оптимизација:

• Композитен индекс на (medicine_id_1, medicine_id_2)
• Индекс врз severity за побрзи филтрирања

CREATE INDEX IF NOT EXISTS idx_mi_meds
  ON synergymed.medicineinteraction(medicine_id_1, medicine_id_2);
CREATE INDEX IF NOT EXISTS idx_mi_severity
  ON synergymed.medicineinteraction(severity);

---

Тестирање со EXPLAIN ANALYZE

Пример за тестирање на извештај од сценарио 2:

EXPLAIN (ANALYZE, BUFFERS)
SELECT c.company_name AS pharmacy,
       bm.name AS branded_medicine,
       mc.company_name AS manufacturer,
       SUM(ibm.quantity) AS total_stock,
       MAX(ibm.last_changed) AS last_update
FROM synergymed.inventory_brandedmedicine ibm
JOIN synergymed.inventory i ON ibm.inventory_id = i.id
JOIN synergymed.facility f ON i.id = f.id
JOIN synergymed.pharmacy ph ON f.company_id = ph.company_id
JOIN synergymed.company c ON ph.company_id = c.id         
JOIN synergymed.brandedmedicine bm ON ibm.branded_medicine_id = bm.id
JOIN synergymed.manufacturer m ON bm.manufacturer_id = m.company_id
JOIN synergymed.company mc ON m.company_id = mc.id
GROUP BY c.company_name, bm.name, mc.company_name
HAVING SUM(ibm.quantity) < 100
ORDER BY total_stock ASC, last_update DESC
LIMIT 20;

Пример за тестирање извештај од сценарио 3:

EXPLAIN (ANALYZE, BUFFERS)
SELECT u.username,
       p.issued_at,
       p.valid_to,
       m.name AS medicine_name
FROM synergymed.prescription p
JOIN synergymed.users u ON p.embg = u.e_mail  -- пример мапирање, зависи од FK
JOIN synergymed.medicine m ON p.medicine_id = m.id
WHERE p.valid_to >= CURRENT_DATE
  AND u.username ILIKE '%ivan%'
ORDER BY p.valid_to DESC;

Пример за тестирање извештај од сценарио 4:

EXPLAIN (ANALYZE, BUFFERS)
SELECT m1.medicine_name AS medicine_a,
       m2.medicine_name AS medicine_b,
       mi.severity,
       mi.type,
       mi.description
FROM synergymed.medicineinteraction mi
JOIN synergymed.medicine m1 ON mi.medicine_id_1 = m1.id
JOIN synergymed.medicine m2 ON mi.medicine_id_2 = m2.id
WHERE mi.severity IN ('умерена', 'висока')
ORDER BY mi.severity DESC, m1.medicine_name, m2.medicine_name
LIMIT 50;

---

Споредба на извршување на извештај за сценарио 2, 3 и 4 со и без индекси

Споредба на извршување на извештај 2 со и без индекси Без индекси: Со индекси: Споредба на извршување на извештај 3 со и без индекси Без индекси: Со индекси: Споредба на извршување на извештај 4 со и без индекси Без индекси: Со индекси:

---

Анализа на извештај 2: Забележуваме дека извршувањето и без индекси е релативно брзо поради тоа што моментално базата нема голем број записи. Сепак, во реална примена каде што секоја аптека би имала илјадници записи и чести ажурирања на количини, индексите врз branded_medicine_id и last_changed би биле важни. Анализа на извештај 3: И тука се гледа дека и без индекси пребарувањата се извршуваат брзо, бидејќи податоците се малку. Но во продукциска околина, каде секој клиент би имал десетици рецепти и системот постојано би проверувал дали тие се активни, индексите врз client_id, medicine_id и valid_to би биле клучни. Со нив филтрирањето на активни рецепти и пребарувањето по пациенти би станало скалабилно и одржливо дури и при голем обем на податоци. Анализа на извештај 4: За извештај 4, забележуваме дека извршувањето е побрзо без индекс, бидејќи табелата во нашиот случај е релативно мала и PostgreSQL претпочита целосно последователно читање (Seq Scan) наместо да користи индекс. Индекс значи дополнителен I/O lookup и кај мали табели е непотребен “overhead”. Заклучок: Сценарио 2 и 3: иако во тест околина индекси не носат голема добивка поради мал број записи, нивната примена е критична во реална продукциска средина каде табелите експоненцијално ќе растат. Сценарио 4: индекси теоретски се корисни, но кај навистина многу мали табели како во овој случај тие дури можат да бидат побавни. Сепак, со зголемување на бројот на интеракции (N:M релации со илјадници комбинации) индексот ќе ја покаже својата вистинска вредност.