Changes between Version 5 and Version 6 of Indexes


Ignore:
Timestamp:
09/12/25 15:40:23 (7 weeks ago)
Author:
222004
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • Indexes

    v5 v6  
    3232---- 
    3333
    34 === **Сценарио 2: Анализа на залихи по аптека**// 
     34=== **Сценарио 2: Анализа на залихи по аптека за конкретен лек во некој временски интервал**// 
    3535
    3636**Оптимизација:**// 
    37 - Индекс врз branded_medicine_id за брзо групирање и агрегација.// 
    38 - Индекс врз last_changed за побрзи временски анализи.// 
     37Композитен индекс врз (branded_medicine_id, last_changed), бидејќи овие колони секогаш заедно се проверуваат (последната промена се зема во предвид) во условите и редоследот по last_changed ни е важен -> ваков индекс би довел подобри перформанси кога се комбинираат филтрирање и временска анализа.
    3938
    4039{{{
    4140
    42 CREATE INDEX IF NOT EXISTS idx_inventorybm_bmid
    43   ON synergymed.inventory_brandedmedicine(branded_medicine_id);
    44 CREATE INDEX IF NOT EXISTS idx_inventorybm_last_changed
    45   ON synergymed.inventory_brandedmedicine(last_changed);
     41CREATE INDEX IF NOT EXISTS idx_inventorybm_bmid_lastchanged
     42  ON synergymed.inventory_brandedmedicine(branded_medicine_id, last_changed DESC);
    4643
    4744}}} 
    … …  
    9592EXPLAIN (ANALYZE, BUFFERS)
    9693SELECT c.company_name AS pharmacy,
    97        bm.name AS branded_medicine,
    98        mc.company_name AS manufacturer,
    99        SUM(ibm.quantity) AS total_stock,
    100        MAX(ibm.last_changed) AS last_update
     94       f.facility_name,
     95       ibm.quantity,
     96       ibm.last_changed
    10197FROM synergymed.inventory_brandedmedicine ibm
    10298JOIN synergymed.inventory i ON ibm.inventory_id = i.id
    103 JOIN synergymed.facility f ON i.id = f.id
     99JOIN synergymed.facility f ON i.facility_id = f.id
    104100JOIN synergymed.pharmacy ph ON f.company_id = ph.company_id
    105 JOIN synergymed.company c ON ph.company_id = c.id         
    106 JOIN synergymed.brandedmedicine bm ON ibm.branded_medicine_id = bm.id
    107 JOIN synergymed.manufacturer m ON bm.manufacturer_id = m.company_id
    108 JOIN synergymed.company mc ON m.company_id = mc.id
    109 GROUP BY c.company_name, bm.name, mc.company_name
    110 HAVING SUM(ibm.quantity) < 100
    111 ORDER BY total_stock ASC, last_update DESC
    112 LIMIT 20;
     101JOIN synergymed.company c ON ph.company_id = c.id
     102WHERE ibm.branded_medicine_id = 6432
     103  AND ibm.last_changed BETWEEN CURRENT_DATE - INTERVAL '6 months' AND CURRENT_DATE
     104ORDER BY ibm.last_changed DESC
     105LIMIT 50;
    113106
    114107}}} 
    115108
    116 **Пример за тестирање извештај од сценарио 3:**//
     109----
     110== **Споредба на извршување на извештај за сценарио 2 со и без индекси**//
     111=== Без индекси:
     112По природа, во еден ваков систем, inventory_brandedmedicine е релација која многу ќе расте.//
     113Еднен inventory може да има илјадници различни лекови (branded_medicine). Истовремено, еден лек (branded_medicine) може да се чува во повеќе различни магацини на аптеки. Поради тоа, додадовме дополнителни записи за тестирање.
     114
     115[[Image(2-noindex.PNG)]]
     116
     117Можеме да приметиме дека:
    117118{{{
    118119
    119 EXPLAIN (ANALYZE, BUFFERS)
    120 SELECT u.username,
    121        p.issued_at,
    122        p.valid_to,
    123        m.name AS medicine_name
    124 FROM synergymed.prescription p
    125 JOIN synergymed.users u ON p.embg = u.e_mail  -- пример мапирање, зависи од FK
    126 JOIN synergymed.medicine m ON p.medicine_id = m.id
    127 WHERE p.valid_to >= CURRENT_DATE
    128   AND u.username ILIKE '%ivan%'
    129 ORDER BY p.valid_to DESC;
     120Seq Scan on inventory_brandedmedicine ibm
     121  Filter: ((branded_medicine_id = 6432)
     122           AND (last_changed <= CURRENT_DATE)
     123           AND (last_changed >= (CURRENT_DATE - '6 mons'::interval)))
     124  Rows Removed by Filter: 100032
     125  Buffers: shared hit=541
    130126
    131127}}}
    132 
    133 **Пример за тестирање извештај од сценарио 4:**//
     128Без индекс, postgres извршува seq scan на табелата inventory_brandedmedicine.//
     129Во postgres постојат два начини на читање на табелите: ред по ред, секоја редица до крај (seq scan) и index scan.//
     130//
     131Можеме да забележиме дека://
     132- Сите 100.032 реда се прочитани.//
     133- Ниеден ред не поминал филтер (rows=0).//
     134- Дури и кога ќе има редови -> пак ќе мора да помине низ целата табела.//
     135На останатите места каде што се користи index scan, е поради тоа што имаме join услови по PK и FK, PRIMARY KEY автоматски создава индекс -> затоа PostgreSQL прави
     136index scan.//
     137//
     138=== Со индекси:
     139[[Image(2-index.PNG)]]
    134140{{{
    135141
    136 EXPLAIN (ANALYZE, BUFFERS)
    137 SELECT m1.medicine_name AS medicine_a,
    138        m2.medicine_name AS medicine_b,
    139        mi.severity,
    140        mi.type,
    141        mi.description
    142 FROM synergymed.medicineinteraction mi
    143 JOIN synergymed.medicine m1 ON mi.medicine_id_1 = m1.id
    144 JOIN synergymed.medicine m2 ON mi.medicine_id_2 = m2.id
    145 WHERE mi.severity IN ('умерена', 'висока')
    146 ORDER BY mi.severity DESC, m1.medicine_name, m2.medicine_name
    147 LIMIT 50;
     142Bitmap Index Scan on idx_inventorybm_bmid_lastchanged
     143  Index Cond: (branded_medicine_id = 6432
     144               AND last_changed BETWEEN ...)
    148145
    149146}}}
     147Можеме да забележиме дека://
     148- Се користи bitmap index scan на композитниот индекс (branded_medicine_id, last_changed).//
     149- Условот branded_medicine_id = 6432 AND last_changed BETWEEN ... се извршува директно преку индексот -> нема потреба да се чита целата табела.//
     150- Наместо seq scan низ сите 100.032 реда, се пристапува само до оние редови што реално го исполнуваат условот.//
     151- Сортирањето по last_changed DESC исто така е оптимизирано, бидејќи индексот е креиран со редослед по last_changed.//
     152//
     153Резултат: Execution Time падна од 5.229ms (со Seq Scan) на околу 0.1 ms со индекс.//
    150154
    151 ----
    152 == **Споредба на извршување на извештај за сценарио 2, 3 и 4 со и без индекси**//
    153 **Споредба на извршување на извештај 2 со и без индекси**//
    154 //
    155 Без индекси://
    156 //
    157 [[Image(2-noindex.PNG)]]
    158 //
    159 Со индекси:
    160 //
    161 [[Image(2-index.PNG​)]]
    162 //
    163 //
    164 **Споредба на извршување на извештај 3 со и без индекси**//
    165 //
    166 Без индекси://
    167 //
    168 [[Image(3-noindex.PNG)]]
    169 //
    170 Со индекси:
    171 //
    172 [[Image(3-index.PNG​)]]
    173 //
    174 //
    175 **Споредба на извршување на извештај 4 со и без индекси**//
    176 //
    177 Без индекси://
    178 //
    179 [[Image(4-noindex.PNG)]]
    180 //
    181 Со индекси:
    182 //
    183 [[Image(4-index.PNG​)]]
    184 //
    185 //
    186 ----
    187 **Анализа на извештај 2:**//
    188 Забележуваме дека извршувањето и без индекси е релативно брзо поради тоа што моментално базата нема голем број записи. Сепак, во реална примена каде што секоја аптека би имала илјадници записи и чести ажурирања на количини, индексите врз branded_medicine_id и last_changed би биле важни.//
    189 //
    190 **Анализа на извештај 3:**//
    191 И тука се гледа дека и без индекси пребарувањата се извршуваат брзо, бидејќи податоците се малку. Но во продукциска околина, каде секој клиент би имал десетици рецепти и системот постојано би проверувал дали тие се активни, индексите врз client_id, medicine_id и valid_to би биле клучни. Со нив филтрирањето на активни рецепти и пребарувањето по пациенти би станало скалабилно и одржливо дури и при голем обем на податоци.//
    192 //
    193 **Анализа на извештај 4:**//
    194 За извештај 4, забележуваме дека извршувањето е побрзо без индекс, бидејќи табелата во нашиот случај е релативно мала и PostgreSQL претпочита целосно последователно читање (Seq Scan) наместо да користи индекс.
    195 Индекс значи дополнителен I/O lookup и кај мали табели е непотребен “overhead”.//
    196 //
    197 **Заклучок:**//
    198 Сценарио 2 и 3: иако во тест околина индекси не носат голема добивка поради мал број записи, нивната примена е критична во реална продукциска средина каде табелите експоненцијално ќе растат.//
    199 //
    200 Сценарио 4: индекси теоретски се корисни, но кај навистина многу мали табели како во овој случај тие дури можат да бидат побавни. Сепак, со зголемување на бројот на интеракции (N:M релации со илјадници комбинации) индексот ќе ја покаже својата вистинска вредност.//
     155
     156
     157
     158
     159
     160