== **Индекси и анализа на перформанси** ---- Тука ќе ги опфатиме индексите што не се автоматски покриени со 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), бидејќи овие колони секогаш заедно се проверуваат (последната промена се зема во предвид) во условите и редоследот по 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**) на рецептите за да се открие дали се активни.// **Оптимизација:**// - Индекс врз 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, 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) може да се чува во повеќе различни магацини на аптеки. Поради тоа, додадовме дополнителни записи за тестирање. [[Image(2-noindex.PNG)]] Можеме да приметиме дека: {{{ 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.// // === Со индекси: [[Image(2-index.PNG)]] {{{ 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 со индекс.//