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

---

Тука ќе ги опфатиме индексите што не се автоматски покриени со 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);

Овој индекс е уште покорисен доколку често имаме ORDER BY payment_date DESC, бидејќи овозможува побрзо извлекување на последните плаќања без дополнително сортирање:

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

status е ниско-кардинална колона (завршено, во тек, неуспешно), а payment_date е високо-кардинална и често се користи за ORDER BY, затоа комбинацијата е оптимална.

---

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

Оптимизација: Композитен индекс врз (branded_medicine_id, last_changed), бидејќи овие колони секогаш заедно се проверуваат (последната промена се зема во предвид) во условите и редоследот по last_changed ни е важен -> ваков индекс би довел подобри перформанси кога се комбинираат филтрирање и временска анализа.

CREATE INDEX IF NOT EXISTS idx_inventorybm_bmid_lastchanged
  ON synergymed.inventory_brandedmedicine(branded_medicine_id, last_changed DESC);

---

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

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

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

• Индекс врз client_id – за пребарувања на сите рецепти кај конкретен пациент.

• Индекс врз medicine_id – за брзо проверување дали еден лек е препишан.

• Композитен индекс (client_id, valid_to) – особено корисен за сценарија кога често проверуваме кои рецепти се валидни за конкретен пациент

• Индекс врз 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_client_valid
  ON synergymed.prescription(client_id, valid_to);

-- opcionalno
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,
       f.facility_name,
       ibm.quantity,
       ibm.last_changed
FROM synergymed.inventory_brandedmedicine ibm
JOIN synergymed.inventory i ON ibm.inventory_id = i.id
JOIN synergymed.facility f ON i.facility_id = f.id
JOIN synergymed.pharmacy ph ON f.company_id = ph.company_id
JOIN synergymed.company c ON ph.company_id = c.id
WHERE ibm.branded_medicine_id = 6432
  AND ibm.last_changed BETWEEN CURRENT_DATE - INTERVAL '6 months' AND CURRENT_DATE
ORDER BY ibm.last_changed DESC
LIMIT 50;

Овој извештај е оперативен извештај за залихи по аптека за еден конкретен лек, со фокус на последните промени и помага кога ни е потребна брза проверка на достапност.

---

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

Без индекси:

По природа, во еден ваков систем, inventory_brandedmedicine е релација која многу ќе расте. Еднен inventory може да има илјадници различни лекови (branded_medicine). Истовремено, еден лек (branded_medicine) може да се чува во повеќе различни магацини на аптеки. Поради тоа, додадовме дополнителни записи за тестирање.

Можеме да приметиме дека:

Seq Scan on inventory_brandedmedicine ibm
  Filter: ((branded_medicine_id = 6432)
           AND (last_changed <= CURRENT_DATE)
           AND (last_changed >= (CURRENT_DATE - '6 mons'::interval)))
  Rows Removed by Filter: 100032
  Buffers: shared hit=541

Без индекс, postgres извршува seq scan на табелата inventory_brandedmedicine. Во postgres постојат два начини на читање на табелите: ред по ред, секоја редица до крај (seq scan) и index scan. Можеме да забележиме дека:

• Сите 100.032 реда се прочитани.
• Ниеден ред не поминал филтер (rows=0).
• Дури и кога ќе има редови -> пак ќе мора да помине низ целата табела.

На останатите места каде што се користи index scan, е поради тоа што имаме join услови по PK и FK, PRIMARY KEY автоматски создава индекс -> затоа PostgreSQL прави index scan.

Со индекси:

Bitmap Index Scan on idx_inventorybm_bmid_lastchanged
  Index Cond: (branded_medicine_id = 6432 
               AND last_changed BETWEEN ...)

Можеме да забележиме дека:

• Се користи bitmap index scan на композитниот индекс (branded_medicine_id, last_changed).
• Условот branded_medicine_id = 6432 AND last_changed BETWEEN ... се извршува директно преку индексот -> нема потреба да се чита целата табела.
• Наместо seq scan низ сите 100.032 реда, се пристапува само до оние редови што реално го исполнуваат условот.
• Сортирањето по last_changed DESC исто така е оптимизирано, бидејќи индексот е креиран со редослед по last_changed.

Резултат: Execution Time падна од 5.229ms (со Seq Scan) на околу 0.1 ms со индекс.