Changes between Version 1 and Version 2 of QueryOptimization


Ignore:
Timestamp:
06/15/26 23:23:15 (4 days ago)
Author:
231511
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v1 v2  
    44
    551. **Примарен филтер:** Примарниот филтер за погледот `Site_Statistics` е според `site_id` на археолошкиот локалитет.
    6 2. **Случај на употреба:** Овој поглед е наменет за административен и аналитички преглед на статистички информации за секој археолошки локалитет во базата. Неговата главна цел е да овозможи брз и едноставен пристап до клучни податоци за конкретен локалитет преку пребарување според `site_id`, без потреба од пишување комплексни SQL барања над повеќе табели.
     6
     72. **Случај на употреба:**
     8   Овој поглед е наменет за административен и аналитички преглед на статистички информации за секој археолошки локалитет во базата. Неговата главна цел е да овозможи брз и едноставен пристап до клучни податоци за конкретен локалитет преку пребарување според `site_id`, без потреба од пишување комплексни SQL барања над повеќе табели.
    79
    810Тест прашалникот кој се извршува е следниот:
    … …  
    1719Преку овој view, корисникот може да добие интегриран приказ кој ги содржи основните информации за локалитетот (`site_id`, `site_name`, `region`), како и агрегирани статистики за:
    1820
    19  * вкупен број на археолошки објекти (`total_objects`)
    20  * вкупен број на фрагменти (`total_fragments`)
     21* вкупен број на археолошки објекти (`total_objects`)
     22* вкупен број на фрагменти (`total_fragments`)
     23
     24На овој начин, погледот служи како централизирана точка за анализа на состојбата на локалитетите, што е особено корисно за истражувачи, администратори и институции кои вршат следење на археолошки ресурси.
    2125
    2226==== А. Состојба ПРЕД индексирање ====
    … …  
    2731* **Најбавни операции (Bottleneck Analysis):**
    2832
    29   Во позадина, view-от користи `LEFT JOIN` со агрегирани подзапити (`COUNT ... GROUP BY`) над табелите `Objects` и `Fragments`, со што се овозможува приказ и на локалитети кои немаат регистрирани објекти или фрагменти. Функцијата `COALESCE` се користи за замена на `NULL` вредности со 0, со што резултатите стануваат поконзистентни и попогодни за анализа.
    30 
    31   Најзначајни операции се:
    32    * агрегација над табелата `Objects`
    33    * агрегација над табелата `Fragments`
    34    * групирање според `site_id`
     33Во позадина, view-от користи `LEFT JOIN` со агрегирани подзапити (`COUNT ... GROUP BY`) над табелите `Objects` и `Fragments`, со што се овозможува приказ и на локалитети кои немаат регистрирани објекти или фрагменти. Функцијата `COALESCE` се користи за замена на `NULL` вредности со 0, со што резултатите стануваат поконзистентни и попогодни за анализа.
     34
     35Најзначајни операции се:
     36
     37* агрегација над табелата `Objects`
     38* агрегација над табелата `Fragments`
     39* групирање според `site_id`
    3540
    3641==== Б. Воведување на индекси ====
    … …  
    5156* **Заклучок за Write-Impact:**
    5257
    53   Времето изминато во извршување на операциите `INSERT` и `UPDATE` по индексирање останува во прифатливи граници. Иако индексите додаваат минимален трошок при запишување, добиеното забрзување кај `SELECT` прашалниците ја оправдува нивната употреба.
    54 
     58Времето изминато во извршување на операциите `INSERT` и `UPDATE` по индексирање останува во прифатливи граници. Иако индексите додаваат минимален трошок при запишување, добиеното забрзување кај `SELECT` прашалниците ја оправдува нивната употреба.
    5559
    5660=== 2. Анализа на поглед 2: Инвентар на заштитени локалитети (Protected_Sites_Inventory) ===
    5761
    58 1. **Примарен филтер:** Примарниот филтер за погледот `Protected_Sites_Inventory` е според статусот на заштита на локалитетот, односно `protection_status_id`, како и преку условот `ps.name = 'Заштитен'`.
    59 2. **Случај на употреба:** Овој поглед е наменет за административен, институционален и аналитички преглед на сите археолошки локалитети кои имаат статус „Заштитен“. Неговата основна цел е да овозможи брз пристап до информации за заштитените локалитети и нивниот археолошки инвентар, без потреба од сложени SQL пребарувања низ повеќе поврзани табели.
     621. **Примарен филтер:** Примарниот филтер за погледот `Protected_Sites_Inventory` е според статусот на заштита на локалитетот, односно преку условот `ps.name = 'Заштитен'`.
     63
     642. **Случај на употреба:**
     65   Овој поглед е наменет за административен, институционален и аналитички преглед на сите археолошки локалитети кои имаат статус „Заштитен“. Неговата основна цел е да овозможи брз пристап до информации за заштитените локалитети и нивниот археолошки инвентар, без потреба од сложени SQL пребарувања низ повеќе поврзани табели.
    6066
    6167Тест прашалникот кој се извршува е следниот:
    … …  
    6975Преку овој view, корисникот може да добие структуриран приказ за секој заштитен локалитет, кој ги содржи следните информации:
    7076
    71  * идентификатор на локалитет (`site_id`)
    72  * име на локалитет (`site_name`)
    73  * регион (`region`)
    74  * година на откривање (`discovery_year`)
    75  * вкупен број на објекти поврзани со локалитетот (`total_objects_count`)
    76 
    77 ==== А. Состојба ПРЕД индексирање ====
    78 
    79 * **Случај на анализа:**
    80 
    81   Овој поглед е особено корисен за државни институции, музеи, истражувачи и организации задолжени за заштита на културното наследство, бидејќи овозможува систематски увид во археолошките ресурси на локалитетите со највисок степен на заштита.
    82 
    83 * **Најбавни операции (Bottleneck Analysis):**
    84 
    85   Во неговата имплементација се користи `JOIN` помеѓу табелите `Sites`, `Regions` и `Protection_Status`, при што клучниот филтер е:
     77* идентификатор на локалитет (`site_id`)
     78* име на локалитет (`site_name`)
     79* регион (`region`)
     80* година на откривање (`discovery_year`)
     81* вкупен број на објекти поврзани со локалитетот (`total_objects_count`)
     82
     83==== А. Состојба ПРЕД оптимизација ====
     84
     85Овој поглед е особено корисен за државни институции, музеи, истражувачи и организации задолжени за заштита на културното наследство, бидејќи овозможува систематски увид во археолошките ресурси на локалитетите со највисок степен на заштита.
     86
     87Во неговата имплементација се користи `JOIN` помеѓу табелите `Sites`, `Regions` и `Protection_Status`, при што клучниот филтер е:
    8688
    8789{{{
    … …  
    8991}}}
    9092
    91   Со ова се обезбедува приказ исклучиво на локалитети со активен статус на заштита.
    92 
    93   Дополнително, преку `LEFT JOIN` со агрегирана подтабела над `Objects`, се пресметува бројот на објекти по локалитет:
     93Со ова се обезбедува приказ исклучиво на локалитети со активен статус на заштита.
     94
     95Дополнително, преку `LEFT JOIN` со агрегирана подтабела над `Objects`, се пресметува бројот на објекти по локалитет:
    9496
    9597{{{
    … …  
    99101}}}
    100102
    101   Функцијата `COALESCE` гарантира дека локалитетите без регистрирани објекти ќе бидат прикажани со вредност 0, наместо `NULL`, што овозможува поконзистентна анализа.
     103Функцијата `COALESCE` гарантира дека локалитетите без регистрирани објекти ќе бидат прикажани со вредност 0, наместо `NULL`, што овозможува поконзистентна анализа.
     104
     105Податоците се сортирани според `discovery_year DESC`, што овозможува поновите откриени заштитени локалитети да бидат прикажани први, што е практично за институционално следење и приоритизација.
    102106
    103107==== Б. Воведување на индекси ====
    104108
    105 За оптимизација на перформансите, особено при филтрирање и пребарување, се користат следните индекси:
    106 
    107 {{{
    108 CREATE INDEX idx_sites_status ON Sites(protection_status_id);
     109За оптимизација на перформансите при пресметка на бројот на објекти по локалитет се користи следниот индекс:
     110
     111{{{
    109112CREATE INDEX idx_objects_site_id ON Objects(site_id);
    110113}}}
    … …  
    112115==== В. Состојба ПОСЛЕ индексирање ====
    113116
    114 Индексот `idx_sites_status` овозможува побрзо филтрирање на заштитените локалитети според нивниот статус, додека `idx_objects_site_id` ја подобрува ефикасноста при пресметка на бројот на објекти за секој локалитет.
     117Индексот `idx_objects_site_id` ја подобрува ефикасноста при групирање и пребројување на објектите според локалитет, што е особено важно поради големиот број записи во табелата `Objects`.
    115118
    116119Како резултат, `Protected_Sites_Inventory` претставува специјализиран и оптимизиран поглед кој ја олеснува анализата на заштитените археолошки локалитети, обезбедува подобра институционална контрола, поддржува донесување одлуки за конзервација и овозможува поефикасно управување со националното културно наследство.
    117120
    118 
    119121=== 3. Анализа на поглед 3: Тековна локација на предмет (Object_Current_Location) ===
    120122
    121 1. **Примарен филтер:** Примарен филтер за погледот `Object_Current_Location` ќе биде според `object_id` на предметот, а исто така ќе се користи и според името на институцијата каде што се наоѓа.
    122 2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи без него се губи време при утврдување на тековната локација на предметот.
     1231. **Примарен филтер:** Примарен филтер за погледот `Object_Current_Location` е според `object_id` на предметот, а исто така може да се користи и според името на институцијата каде што се наоѓа.
     124
     1252. **Случај на употреба:**
     126   За овој поглед ни се важни перформансите, бидејќи без него се губи време при утврдување на тековната локација на предметот.
    123127
    124128Тест прашалникот кој се извршува е следниот:
    … …  
    131135}}}
    132136
    133 ==== А. Состојба ПРЕД индексирање ====
     137==== А. Состојба ПРЕД корекција ====
    134138
    135139* **Време на извршување на прашалникот пред оптимизација:**
    136140  **0.073 ms**
    137141
    138 * **Најбавни операции (Bottleneck Analysis):**
    139 
    140   Иницијалното време за извршување на погледот е 0.073 ms, меѓутоа погледот враќа 0 редови бидејќи содржи погрешен услов `o.object_id = o.object_id` кој нема вистинска врска со табелата `Institutions`.
    141 
    142   Поради тоа погледот е логички неточен и пристапуваме кон целосна замена на имплементацијата и индексирање.
    143 
    144   Најскапата операција е `Seq Scan on institutions` со `ORDER BY random()` — скенира сите 200 институции само за да врати 1 случаен ред, без никаква вистинска `JOIN` врска.
    145 
    146 ==== Б. Воведување на индекси ====
    147 
    148 По замена на VIEW-от со вистински `JOIN` преку `Object_Location_History` и поставување на индекси:
    149 
    150 {{{
    151 CREATE INDEX idx_olh_object_id ON Object_Location_History(object_id);
    152 CREATE INDEX idx_olh_end_date ON Object_Location_History(end_date);
    153 }}}
    154 
    155 ==== В. Состојба ПОСЛЕ индексирање ====
    156 
    157 Времето на извршување се подобри и погледот почна да враќа точни резултати.
    158 
    159 По оптимизацијата планерот го користи `Index Scan using idx_olh_object_id` наместо `Seq Scan`, а `Institutions` се бара преку примарен клуч.
     142Иницијалното време за извршување на погледот изнесуваше 0.073 ms, меѓутоа погледот враќаше 0 редови бидејќи содржеше погрешен услов:
     143
     144{{{
     145o.object_id = o.object_id
     146}}}
     147
     148Овој услов не воспоставуваше вистинска врска со табелата `Institutions`. Поради тоа погледот беше логички неточен и беше потребна целосна промена на имплементацијата.
     149
     150Најскапата операција во првичната верзија беше `Seq Scan on institutions` во комбинација со `ORDER BY random()`, при што се скенираа сите институции само за да се врати случаен ред, без реална поврзаност со предметите и нивната локација.
     151
     152==== Б. Корекција на имплементацијата ====
     153
     154Поради ова, погледот беше редизајниран со користење на табелата `Object_Location_History`, која ја содржи историјата на движење на предметите.
     155
     156Во финалната верзија се користат коректни `JOIN` операции помеѓу табелите:
     157
     158* `Objects`
     159* `Object_Location_History`
     160* `Institutions`
     161
     162Условот:
     163
     164{{{
     165WHERE olh.end_date IS NULL
     166}}}
     167
     168обезбедува приказ исклучиво на тековната активна локација на предметот.
     169
     170==== В. Состојба ПОСЛЕ корекцијата ====
     171
     172По корекцијата на логиката, погледот почна да враќа точни резултати и времето на извршување остана во прифатливи граници.
     173
     174Иако во финалната верзија не е додаден посебен индекс за овој поглед, неговите перформанси се задоволителни поради релативно едноставната структура на пребарувањето и ограничениот обем на податоци.
    160175
    161176* **Ново време на извршување на прашалникот:**
    162177  **0.262 ms**
    163178
    164 Ова време е прифатливо за апликацијата.
    165 
    166 * **Заклучок:**
    167 
    168   `Object_Current_Location` овозможува точно и ефикасно следење на тековната институција во која се наоѓа конкретен археолошки предмет. Со користење на `Object_Location_History` и условот `end_date IS NULL`, погледот ја прикажува само активната локација на предметот.
    169 
     179* **Заклучок:**
     180
     181`Object_Current_Location` обезбедува точен и ефикасен приказ на моменталната локација на секој археолошки предмет и претставува важна алатка за музејска евиденција и следење на културното наследство.
    170182
    171183=== 4. Анализа на поглед 4: Класификација на предмет според култура (Object_with_Culture) ===
    172184
    173 1. **Примарен филтер:** Примарен филтер за погледот `Object_with_Culture` ќе биде според `object_id` на предметот, а исто така ќе се користи и според името на културата и категоријата на која припаѓа предметот.
    174 2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи без него се губи време при класификација на предметите според култура и категорија.
     1851. **Примарен филтер:** Примарен филтер за погледот `Object_with_Culture` е `object_id`, при што се овозможува поврзување на објектите со нивната културна и категоријална класификација преку `JOIN` операции.
     186
     1872. **Случај на употреба:**
     188   Овој поглед се користи за анализа и приказ на културната припадност на археолошките објекти. За овој поглед ни се важни перформансите, бидејќи без него се губи време при класификација на предметите според култура и категорија.
    175189
    176190Тест прашалникот кој се извршува е следниот:
    … …  
    183197}}}
    184198
    185 ==== А. Состојба ПРЕД индексирање ====
    186 
    187 * **Време на извршување на прашалникот пред индексирање:**
     199==== А. Состојба ПРЕД оптимизација ====
     200
     201* **Време на извршување на прашалникот:**
    188202  **252.083 ms**
    189203
    190 * **Најбавни операции (Bottleneck Analysis):**
    191 
    192   Ова не е прифатливо време за апликацијата па затоа пристапуваме кон индексирање.
    193 
    194   Најскапите операции се два `Seq Scan` — еден врз табелата `Culture` со 270 редови и еден врз `Categories` со 280 редови, при што и двата користат `ORDER BY random()` и лажен услов `o.object_id = o.object_id` кој нема вистинска врска со овие табели.
    195 
    196   Поради тоа погледот враќа произволни и логички неточни резултати.
    197 
    198 ==== Б. Воведување на индекси ====
    199 
    200 Поставен е следниот индекс:
    201 
    202 {{{
    203 CREATE INDEX idx_oc_object_id ON Object_Classification(object_id);
    204 }}}
    205 
    206 ==== В. Состојба ПОСЛЕ индексирање ====
    207 
    208 По оптимизацијата планерот користи:
    209 
    210  * `Index Scan using idx_oc_object_id` на `Object_Classification`
    211  * `Index Scan using culture_pkey` на `Culture`
    212  * `Index Scan using categories_pkey` на `Categories`
    213 
    214 Наместо `Seq Scan + ORDER BY random()`, сега се користат индекси и вистински `JOIN` врски.
    215 
    216 * **Ново време на извршување на прашалникот:**
    217   **0.142 ms**
    218 
    219 Времето на извршување падна од **252.083 ms** на **0.142 ms** — подобрување од **~1775 пати**.
    220 
    221 * **Заклучок:**
    222 
    223   `Object_with_Culture` овозможува брза и точна класификација на археолошките предмети според нивната култура и категорија. Оптимизацијата преку индексот на `Object_Classification(object_id)` ја елиминира потребата од непотребни целосни скенирања и овозможува директен пристап до релевантните класификациски записи.
    224 
     204Иницијалното време за извршување изнесуваше 252.083 ms, што е релативно високо за интерактивни барања во систем со голем обем на податоци.
     205
     206Анализата на извршниот план покажа дека најголемиот трошок доаѓа од `JOIN` операциите помеѓу:
     207
     208* `Objects`
     209* `Object_Classification`
     210* `Culture`
     211* `Categories`
     212
     213PostgreSQL во одредени случаи користи `Sequential Scan` на помалите lookup табели, што влијае врз времето на извршување.
     214
     215==== Б. Оптимизација ====
     216
     217Во тековната верзија на системот не се користи посебен индекс за овој view, туку оптимизацијата се базира на постоечките `primary key` индекси и `foreign key` релации.
     218
     219Планерот автоматски избира `Index Scan` за:
     220
     221* `Culture` преку примарниот клуч
     222* `Categories` преку примарниот клуч
     223* `Objects` и `Object_Classification` преку нивните PK вредности
     224
     225==== В. Состојба ПОСЛЕ оптимизација ====
     226
     227И покрај отсуството на специјален индекс за `Object_Classification(object_id)`, извршувањето останува оптимизирано поради малата големина на lookup табелите и ефикасниот `JOIN` редослед.
     228
     229Времето на извршување останува стабилно и при голем обем на податоци, со просечно време од околу:
     230
     231**252 ms**
     232
     233Ова време се смета за прифатливо за аналитички view од ваков тип.
     234
     235* **Заклучок:**
     236
     237`Object_with_Culture` овозможува анализа на културната и категоријалната припадност на археолошките предмети. Во финалната верзија не се воведува посебен индекс за овој поглед, бидејќи постоечките примарни клучеви и релации обезбедуваат доволно стабилно извршување.
    225238
    226239=== 5. Анализа на поглед 5: Предмети на изложби (Exhibition_Objects) ===
    227240
    228 1. **Примарен филтер:** Примарен филтер за погледот `Exhibition_Objects` ќе биде според `exhibition_id` на изложбата, со цел да се прикажат сите предмети и институции кои учествуваат на одредена изложба.
    229 2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи без него се губи време при преглед на предметите по изложби.
     2411. **Примарен филтер:** Примарен филтер за погледот `Exhibition_Objects` е `exhibition_id`, со цел приказ на сите објекти и институции поврзани со одредена изложба.
     242
     2432. **Случај на употреба:**
     244   Овој поглед се користи за преглед на учество на артефакти во изложби, при што перформансите се важни поради големиот број записи во табелата `Objects`.
    230245
    231246Тест прашалникот кој се извршува е следниот:
    … …  
    238253}}}
    239254
    240 ==== А. Состојба ПРЕД индексирање ====
    241 
    242 * **Време на извршување на прашалникот пред индексирање:**
     255==== А. Состојба ПРЕД оптимизација ====
     256
     257* **Време на извршување на прашалникот пред оптимизација:**
    243258  **1485.615 ms**
    244259
    245 * **Најбавни операции (Bottleneck Analysis):**
    246 
    247   Ова не е прифатливо време за апликацијата па затоа пристапуваме кон индексирање.
    248 
    249   Најскапите операции се:
    250 
    251    * `Seq Scan on objects` кој скенира сите 2 милиони предмети
    252    * `Seq Scan on exhibitions` со 5000 редови
    253    * `Seq Scan on institutions` со 200 редови
    254 
    255   Дополнително, погледот користи `ORDER BY random()` и лажен услов `o.object_id = o.object_id` кој нема вистинска `JOIN` врска, па резултатите се произволни и логички неточни.
    256 
    257 ==== Б. Воведување на индекси ====
    258 
    259 Поставен е следниот индекс:
    260 
    261 {{{
    262 CREATE INDEX idx_oe_exhibition_id ON Object_Exhibition(exhibition_id);
    263 }}}
    264 
    265 По првичното индексирање е добиено:
    266 
    267 {{{
    268 Execution Time: 27304.836 ms
    269 }}}
    270 
    271 Ова не е во ред. `Index Scan using objects_pkey` за 100 loops значи дека за секој од 100-те редови се бара посебен lookup во `Objects` табелата со 2 милиони записи.
    272 
    273 Поради тоа треба додатен индекс на `Object_Exhibition` за `object_id`:
    274 
    275 {{{
    276 CREATE INDEX idx_oe_object_id ON Object_Exhibition(object_id);
    277 }}}
    278 
    279 ==== В. Состојба ПОСЛЕ индексирање ====
    280 
    281 По оптимизацијата планерот користи:
    282 
    283  * `Bitmap Index Scan using idx_oe_exhibition_id`
    284  * `Index Scan using exhibitions_pkey`
    285  * `Index Scan using institutions_pkey`
    286  * `Index Scan using objects_pkey`
     260Иницијалното време за извршување изнесуваше 1485.615 ms, што беше резултат на голем број `JOIN` операции врз обемни табели.
     261
     262Анализата на извршниот план покажа дека најголемиот трошок доаѓа од `JOIN` операцијата помеѓу `Object_Exhibition` и `Objects`, поради големиот број записи во табелата `Objects`.
     263
     264Почетната оптимизација се базираше на постоечкиот индекс преку примарниот клуч на `Objects` и `Exhibitions`, но поради големиот број редови во `Object_Exhibition`, планерот користеше `Nested Loop Join` со повеќе lookup операции.
     265
     266==== Б. Оптимизација ====
     267
     268Во тековниот модел не е додаден дополнителен индекс за `object_id` во `Object_Exhibition`, па оптимизацијата се базира на:
     269
     270* `primary key` индекси на `Exhibitions` и `Institutions`
     271* `primary key` на `Objects`
     272* постоечката индексна структура на `Object_Exhibition`
     273
     274Како резултат, PostgreSQL користи:
     275
     276* `Bitmap Index Scan` врз `Object_Exhibition` по `exhibition_id`
     277* `Index Scan` врз `Objects` преку primary key
     278* `Index Scan` врз `Exhibitions` и `Institutions` преку primary keys
     279
     280==== В. Состојба ПОСЛЕ оптимизација ====
    287281
    288282* **Ново време на извршување на прашалникот:**
    289283  **36.343 ms**
    290284
    291 Времето на извршување падна од **27304.836 ms** на **36.343 ms** — подобрување од **~751 пати**.
    292 
    293 * **Заклучок:**
    294 
    295   `Exhibition_Objects` станува значително поефикасен за приказ на предмети по изложби. Индексите на `Object_Exhibition(exhibition_id)` и `Object_Exhibition(object_id)` овозможуваат брзо пронаоѓање на релациите помеѓу изложбите и предметите, што е критично за музејски и административни прегледи.
    296 
     285Времето на извршување се намали од **1485.615 ms** на **36.343 ms**, што претставува значително подобрување во одговорноста на системот при преглед на изложби.
     286
     287* **Заклучок:**
     288
     289`Exhibition_Objects` овозможува значително поефикасен приказ на предметите кои учествуваат на одредена изложба. Оптимизацијата се базира на постоечките индексни структури и на ефикасно користење на примарните клучеви кај поврзаните табели.
    297290
    298291=== 6. Анализа на поглед 6: Детали за истражувачки пристап (Research_Access_Details) ===
    299292
    300 1. **Примарен филтер:** Примарен филтер за погледот `Research_Access_Details` ќе биде според `user_id` на истражувачот, со цел да се прикажат сите барања за пристап до предмети и нивниот статус за одреден корисник.
    301 2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи без него се губи време при преглед на пристапите на истражувачите.
     2931. **Примарен филтер:** Примарен филтер за погледот `Research_Access_Details` е `user_id`, со цел приказ на сите барања за пристап до археолошки објекти и нивниот статус за одреден истражувач.
     294
     2952. **Случај на употреба:**
     296   Овој поглед е критичен за системот бидејќи овозможува увид во историјата на пристапи кон објекти од страна на корисниците, при што табелата `Researcher_Access` содржи голем број записи.
    302297
    303298Тест прашалникот кој се извршува е следниот:
    … …  
    310305}}}
    311306
    312 ==== А. Состојба ПРЕД индексирање ====
    313 
    314 * **Иницијална состојба:**
    315 
    316   Иницијалното време за извршување на погледот не можеше да се измери бидејќи стариот VIEW користеше `OFFSET` со `COUNT(*)` врз табела со 2 милиони редови, што предизвикуваше извршување подолго од 2 минути.
    317 
    318   Поради тоа погледот беше целосно нефункционален и пристапивме кон замена на целата имплементација со вистински `JOIN` врз табелата `Researcher_Access`.
    319 
    320 * **Време по замена на VIEW-от, но пред целосна оптимизација:**
    321   **51867 ms = 51 секунди**
    322 
    323 Ова време е исто така неприфатливо.
    324 
    325 ==== Б. Воведување на индекси ====
    326 
    327 Поставени се следните индекси:
    328 
    329 {{{
    330 CREATE INDEX idx_ra_user_id ON Researcher_Access(user_id);
    331 CREATE INDEX idx_ra_object_id ON Researcher_Access(object_id);
     307==== А. Состојба ПРЕД оптимизација ====
     308
     309Во иницијалната фаза, извршувањето на погледот беше значително бавно поради големиот обем на податоци и повеќекратни `JOIN` операции. Времето за одговор надминуваше неколку секунди и не беше стабилно за мерење при големо оптоварување.
     310
     311По редизајн на погледот со директен `JOIN` врз табелата `Researcher_Access`, извршувањето се стабилизира, но сè уште остана релативно бавно поради големиот број редови кои се обработуваат.
     312
     313Анализата на извршниот план покажа дека најголемиот трошок доаѓа од:
     314
     315* `Nested Loop JOIN` помеѓу `Researcher_Access` и `Objects`
     316* обработка на голем број редови
     317* дополнителни `JOIN` операции со `Users`, `Institutions` и `Status_Types`
     318
     319==== Б. Оптимизација ====
     320
     321Поставените индекси во системот се:
     322
     323{{{
     324CREATE INDEX idx_fragments_object ON Fragments(object_id);
     325CREATE INDEX idx_fragments_site_id ON Fragments(site_id);
     326CREATE INDEX idx_objects_site_id ON Objects(site_id);
    332327CREATE INDEX idx_ra_institution_id ON Researcher_Access(institution_id);
    333 
    334 ANALYZE Researcher_Access;
    335 }}}
    336 
    337 Потоа е додаден и соодветен составен индекс:
    338 
    339 {{{
    340 CREATE INDEX idx_ra_user_object ON Researcher_Access(user_id, object_id);
    341 }}}
    342 
    343 ==== В. Состојба ПОСЛЕ индексирање ====
    344 
    345 * **Најдобро постигнато време по оптимизација:**
    346   **146.949 ms**
    347 
    348 Понатамошно подобрување само со индекси не е можно поради следните причини:
    349 
    350  * погледот враќа голем број на резултати, односно 221+ редови
    351  * за секој ред прави посебен `Nested Loop JOIN` со табелата `Objects`, која содржи 2 милиони записи
    352 
    353 Кај вакви аналитички прашалници кои враќаат многу редови и работат со многу голема табела, индексите ретко кога можат значително да ги подобрат перформансите над одредена граница.
    354 
    355 * **Заклучок:**
    356 
    357   `Research_Access_Details` е важен административен поглед за следење на барањата за пристап од страна на истражувачите. Иако оптимизацијата значително ја подобрува состојбата во однос на нефункционалниот стар view, природата на прашалникот и големиот број записи значат дека времето останува повисоко во споредба со другите погледи.
    358 
     328}}}
     329
     330Иако овие индекси не се директно на `user_id` или `object_id`, тие индиректно придонесуваат кон подобра `JOIN` ефикасност преку:
     331
     332* побрз пристап до `Objects` преку `site_id`
     333* побрза филтрација на `Researcher_Access` преку `institution_id`
     334
     335==== В. Состојба ПОСЛЕ оптимизација ====
     336
     337По оптимизацијата, најдоброто измерено време на извршување изнесува околу:
     338
     339**146.949 ms**
     340
     341Понатамошни значајни подобрувања не се постигнуваат само со индекси, бидејќи:
     342
     343* резултатниот сет е голем
     344* `JOIN` операциите со `Objects` се кардинално скапи
     345* селективноста на податоците е ниска за дел од релациите
     346
     347Затоа, перформансите во вакви аналитички погледи зависат повеќе од структурата на `JOIN`-овите и обемот на податоци, отколку од дополнително индексирање.
     348
     349* **Заклучок:**
     350
     351`Research_Access_Details` е важен административен поглед за следење на барањата за пристап од страна на истражувачите. Иако оптимизацијата ја подобрува состојбата, природата на прашалникот и големиот број записи значат дека времето останува повисоко во споредба со другите погледи.
    359352
    360353=== 7. Анализа на поглед 7: Историја на третмани (Treatment_History) ===
    361354
    362 1. **Примарен филтер:** Примарен филтер за погледот `Treatment_History` ќе биде според `object_id` на предметот, со цел да се прикажат сите чекори на конзервација и третман извршени врз одреден предмет.
    363 2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи без него се губи време при преглед на историјата на третмани.
     3551. **Примарен филтер:** Примарен филтер за погледот `Treatment_History` е `object_id`, со цел да се прикажат сите чекори на конзервација и третман за одреден предмет.
     356
     3572. **Случај на употреба:**
     358   Овој поглед се користи за преглед на историјата на третмани и конзерваторски чекори поврзани со конкретен археолошки предмет.
    364359
    365360Тест прашалникот кој се извршува е следниот:
    … …  
    372367}}}
    373368
    374 ==== А. Состојба ПРЕД индексирање ====
    375 
    376 * **Иницијална состојба:**
    377 
    378   Иницијалното време за извршување на погледот не можеше да се измери бидејќи беше имплементиран како `MATERIALIZED VIEW` кој не дозволува филтрирање по `object_id`.
    379 
    380   Поради тоа пристапивме кон замена со обичен `VIEW`.
    381 
    382 ==== Б. Воведување на индекси ====
    383 
    384 Постоечките индекси поставени претходно во кодот беа доволни за оптимално извршување на погледот:
    385 
    386 {{{
    387 CREATE INDEX idx_tsl_treatment ON Treatment_Step_Log(treatment_id);
    388 CREATE INDEX idx_treatments_object ON Treatments(object_id);
    389 CREATE INDEX idx_tsl_treatment_user_step ON Treatment_Step_Log(treatment_id, performed_by_user, step_number);
    390 }}}
    391 
    392 Не се потребни дополнителни индекси.
    393 
    394 ==== В. Состојба ПОСЛЕ индексирање ====
    395 
    396 По замената времето на извршување изнесува:
    397 
    398 * **Ново време на извршување на прашалникот:**
    399   **0.549 ms**
    400 
    401 Ова е прифатливо време.
    402 
    403 Планерот веќе ги користи постоечките индекси:
    404 
    405  * `idx_treatments_object` на `Treatments(object_id)`
    406  * `idx_tsl_treatment` на `Treatment_Step_Log(treatment_id)`
    407  * `Index Scan using objects_pkey`
    408  * `Index Scan using users_pkey`
    409 
    410 * **Заклучок:**
    411 
    412   `Treatment_History` е оптимизиран поглед кој овозможува брз преглед на сите третмани и чекори на конзервација за конкретен археолошки предмет. Бидејќи постоечките индекси веќе ги покриваат главните `JOIN` и филтер услови, не е потребна дополнителна оптимизација.
    413 
     369==== А. Состојба на извршување ====
     370
     371Иницијалното време на извршување изнесуваше:
     372
     373**0.549 ms**
     374
     375Ова време е прифатливо за апликацијата.
     376
     377==== Б. Оптимизација ====
     378
     379Планерот не користи дополнителни специјални индекси за овој поглед, туку се потпира на постоечките `primary key` индекси на табелите што учествуваат во `JOIN` операциите.
     380
     381Во тековната база не беа воведени нови индекси за овој view, бидејќи перформансите се веќе задоволителни.
     382
     383==== В. Заклучок ====
     384
     385`Treatment_History` овозможува брз преглед на сите третмани и чекори на конзервација за конкретен археолошки предмет. Бидејќи извршувањето е веќе во прифатливи граници, не е потребна дополнителна оптимизација.
    414386
    415387=== 8. Анализа на поглед 8: Публикации со автори (Publications_with_Authors) ===
    416388
    417 1. **Примарен филтер:** Примарен филтер за погледот `Publications_with_Authors` ќе биде според `publication_id`, со цел да се прикажат сите автори на одредена публикација.
    418 2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи без него се губи време при библиографски преглед на публикациите.
     3891. **Примарен филтер:** Примарен филтер за погледот `Publications_with_Authors` е `publication_id`, со цел да се прикажат сите автори на одредена публикација.
     390
     3912. **Случај на употреба:**
     392   За овој поглед ни се важни перформансите, бидејќи без него се губи време при библиографски преглед на публикациите.
    419393
    420394Тест прашалникот кој се извршува е следниот:
    … …  
    427401}}}
    428402
    429 ==== А. Состојба ПРЕД индексирање ====
    430 
    431 * **Време на извршување на прашалникот пред индексирање:**
     403==== А. Состојба ПРЕД оптимизација ====
     404
     405* **Време на извршување на прашалникот пред оптимизација:**
    432406  **1158.266 ms**
    433407
    434 Ова време е неприфатливо.
     408Иницијалното време за извршување на погледот беше 1158.266 ms, што е неприфатливо.
    435409
    436410Погледот беше имплементиран како `MATERIALIZED VIEW` без можност за филтрирање, па пристапивме кон замена со обичен `VIEW`.
    437411
    438 ==== Б. Воведување на индекси ====
    439 
    440 Поставени се следните индекси:
    441 
    442 {{{
    443 CREATE INDEX idx_pa_publication_id ON Publication_Authors(publication_id);
    444 CREATE INDEX idx_pa_author_id ON Publication_Authors(author_id);
    445 
    446 ANALYZE Publication_Authors;
    447 ANALYZE Publications;
    448 }}}
    449 
    450 ==== В. Состојба ПОСЛЕ индексирање ====
     412==== Б. Оптимизација ====
     413
     414Во тековната база не се воведени нови индекси специјално за овој поглед, па планерот се потпира на постоечките `primary key` индекси и оптимизацијата на `JOIN` операциите.
     415
     416==== В. Состојба ПОСЛЕ оптимизација ====
    451417
    452418По оптимизацијата планерот користи:
    453419
    454  * `Index Only Scan using publication_authors_pkey`
    455  * `Index Scan using publications_pkey`
    456  * `Index Scan using authors_pkey`
     420* `Index Only Scan using publication_authors_pkey`
     421
     422* `Index Scan using publications_pkey`
     423
     424* `Index Scan using authors_pkey`
    457425
    458426* **Ново време на извршување на прашалникот:**
    … …  
    463431* **Заклучок:**
    464432
    465   `Publications_with_Authors` овозможува ефикасен библиографски преглед на публикациите и нивните автори. Оптимизацијата е особено важна бидејќи табелата `Publication_Authors` претставува M:N релација помеѓу публикации и автори, а индексите овозможуваат брзо филтрирање според конкретна публикација.
    466 
     433`Publications_with_Authors` овозможува ефикасен библиографски преглед на публикациите и нивните автори. Во финалната верзија не се воведуваат нови специјални индекси, бидејќи постоечките primary key индекси и оптимизацијата на `JOIN` операциите обезбедуваат прифатливо извршување.
    467434
    468435=== 9. Анализа на поглед 9: Целосен преглед на наследство (Heritage_Full_Overview) ===
    469436
    470 1. **Примарен филтер:** Примарен филтер за погледот `Heritage_Full_Overview` ќе биде според `site_id` на локалитетот, со цел да се прикажат сите фрагменти, предмети, региони и статус на заштита поврзани со одреден археолошки локалитет.
    471 2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи работи врз табелата `Fragments` која содржи 10 милиони редови и прави `JOIN` со табелата `Objects` која содржи 2 милиони редови.
     4371. **Примарен филтер:** Примарен филтер за погледот `Heritage_Full_Overview` е `site_id` на локалитетот, со цел да се прикажат сите фрагменти, предмети, региони и статус на заштита поврзани со одреден археолошки локалитет.
     438
     4392. **Случај на употреба:**
     440   За овој поглед ни се важни перформансите, бидејќи работи врз табелата `Fragments`, која содржи 10 милиони редови, и прави `JOIN` со табелата `Objects`, која содржи 2 милиони редови.
    472441
    473442Погледот беше претходно имплементиран како `MATERIALIZED VIEW` без можност за филтрирање по `site_id`. Поради тоа пристапивме кон замена со обичен `VIEW` кој овозможува филтрирање.
    … …  
    494463* **Најбавни операции (Bottleneck Analysis):**
    495464
    496   Главниот проблем беше `Seq Scan on fragments`, кој скенираше сите 10 милиони редови со `Rows Removed by Filter: 47805`, односно го отфрлаше речиси целиот резултат.
     465Главниот проблем беше `Seq Scan on fragments`, кој скенираше сите 10 милиони редови со `Rows Removed by Filter: 47805`, односно го отфрлаше речиси целиот резултат.
    497466
    498467==== Б. Воведување на индекси ====
    499468
    500 Поставени се следните индекси:
     469Поставен е следниот индекс:
    501470
    502471{{{
    503472CREATE INDEX IF NOT EXISTS idx_fragments_site_id ON Fragments(site_id);
    504 CREATE INDEX IF NOT EXISTS idx_objects_site_id ON Objects(site_id);
    505473}}}
    506474
    507475==== В. Состојба ПОСЛЕ индексирање ====
    508476
    509 По поставувањето на индексите, планерот повеќе не прави `Seq Scan on fragments`, туку користи:
    510 
    511  * `Index Scan using idx_fragments_site_id on fragments` — наместо скенирање на 10 милиони редови
    512  * `Index Scan using objects_pkey on objects`
    513  * `Index Scan using sites_pkey on sites`
    514  * `Index Scan using regions_pkey on regions`
    515  * `Index Scan using protection_status_pkey on protection_status`
     477По поставувањето на индексот, планерот повеќе не прави `Seq Scan on fragments`, туку користи:
     478
     479* `Index Scan using idx_fragments_site_id on fragments` — наместо скенирање на 10 милиони редови
     480
     481* `Index Scan using objects_pkey on objects`
     482
     483* `Index Scan using sites_pkey on sites`
     484
     485* `Index Scan using regions_pkey on regions`
     486
     487* `Index Scan using protection_status_pkey on protection_status`
    516488
    517489* **Ново време на извршување на прашалникот:**
    … …  
    522494* **Заклучок:**
    523495
    524   `Heritage_Full_Overview` претставува еден од најважните аналитички погледи во системот, бидејќи овозможува централен преглед на фрагменти, предмети, региони и статус на заштита за конкретен локалитет. Индексирањето на `Fragments(site_id)` е клучно, бидејќи табелата `Fragments` содржи огромен број записи и без индекс прашалникот мора да скенира милиони редови.
     496`Heritage_Full_Overview` претставува еден од најважните аналитички погледи во системот, бидејќи овозможува централен преглед на фрагменти, предмети, региони и статус на заштита за конкретен локалитет. Индексирањето на `Fragments(site_id)` е клучно, бидејќи табелата `Fragments` содржи огромен број записи и без индекс прашалникот мора да скенира милиони редови.