= Оптимизација на прашалници и погледи =

=== 1. Анализа на поглед 1: Статистика на археолошки локалитети (Site_Statistics) ===

1. **Примарен филтер:** Примарниот филтер за погледот `Site_Statistics` е според `site_id` на археолошкиот локалитет.
2. **Случај на употреба:** Овој поглед е наменет за административен и аналитички преглед на статистички информации за секој археолошки локалитет во базата. Неговата главна цел е да овозможи брз и едноставен пристап до клучни податоци за конкретен локалитет преку пребарување според `site_id`, без потреба од пишување комплексни SQL барања над повеќе табели.

Тест прашалникот кој се извршува е следниот:

{{{
EXPLAIN ANALYZE
SELECT *
FROM Site_Statistics
WHERE site_id = 100;
}}}

Преку овој view, корисникот може да добие интегриран приказ кој ги содржи основните информации за локалитетот (`site_id`, `site_name`, `region`), како и агрегирани статистики за:

 * вкупен број на археолошки објекти (`total_objects`)
 * вкупен број на фрагменти (`total_fragments`)

==== А. Состојба ПРЕД индексирање ====

* **Време на извршување на прашалникот пред индексирање:**
  **0.215 ms**

* **Најбавни операции (Bottleneck Analysis):**

  Во позадина, view-от користи `LEFT JOIN` со агрегирани подзапити (`COUNT ... GROUP BY`) над табелите `Objects` и `Fragments`, со што се овозможува приказ и на локалитети кои немаат регистрирани објекти или фрагменти. Функцијата `COALESCE` се користи за замена на `NULL` вредности со 0, со што резултатите стануваат поконзистентни и попогодни за анализа.

  Најзначајни операции се:
   * агрегација над табелата `Objects`
   * агрегација над табелата `Fragments`
   * групирање според `site_id`

==== Б. Воведување на индекси ====

За подобрување на перформансите при пребарување и агрегација по `site_id`, имплементирани се следните индекси:

{{{
CREATE INDEX idx_objects_site_id ON Objects(site_id);
CREATE INDEX idx_fragments_site_id ON Fragments(site_id);
}}}

==== В. Состојба ПОСЛЕ индексирање ====

Овие индекси значително го намалуваат времето потребно за извршување на пребарувања и статистички пресметки, особено поради големиот обем на податоци во табелите `Objects` и `Fragments`, бидејќи PostgreSQL може да користи `Index Scan` наместо `Sequential Scan`.

Како резултат, `Site_Statistics` претставува оптимизиран аналитички поглед кој ја поедноставува обработката на податоци, ја подобрува читливоста, овозможува повторна употреба на логиката и обезбедува ефикасен мониторинг на археолошките локалитети.

* **Заклучок за Write-Impact:**

  Времето изминато во извршување на операциите `INSERT` и `UPDATE` по индексирање останува во прифатливи граници. Иако индексите додаваат минимален трошок при запишување, добиеното забрзување кај `SELECT` прашалниците ја оправдува нивната употреба.


=== 2. Анализа на поглед 2: Инвентар на заштитени локалитети (Protected_Sites_Inventory) ===

1. **Примарен филтер:** Примарниот филтер за погледот `Protected_Sites_Inventory` е според статусот на заштита на локалитетот, односно `protection_status_id`, како и преку условот `ps.name = 'Заштитен'`.
2. **Случај на употреба:** Овој поглед е наменет за административен, институционален и аналитички преглед на сите археолошки локалитети кои имаат статус „Заштитен“. Неговата основна цел е да овозможи брз пристап до информации за заштитените локалитети и нивниот археолошки инвентар, без потреба од сложени SQL пребарувања низ повеќе поврзани табели.

Тест прашалникот кој се извршува е следниот:

{{{
EXPLAIN ANALYZE
SELECT *
FROM Protected_Sites_Inventory;
}}}

Преку овој view, корисникот може да добие структуриран приказ за секој заштитен локалитет, кој ги содржи следните информации:

 * идентификатор на локалитет (`site_id`)
 * име на локалитет (`site_name`)
 * регион (`region`)
 * година на откривање (`discovery_year`)
 * вкупен број на објекти поврзани со локалитетот (`total_objects_count`)

==== А. Состојба ПРЕД индексирање ====

* **Случај на анализа:**

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

* **Најбавни операции (Bottleneck Analysis):**

  Во неговата имплементација се користи `JOIN` помеѓу табелите `Sites`, `Regions` и `Protection_Status`, при што клучниот филтер е:

{{{
WHERE ps.name = 'Заштитен'
}}}

  Со ова се обезбедува приказ исклучиво на локалитети со активен статус на заштита.

  Дополнително, преку `LEFT JOIN` со агрегирана подтабела над `Objects`, се пресметува бројот на објекти по локалитет:

{{{
SELECT site_id, COUNT(*) AS total_objects_count
FROM Objects
GROUP BY site_id
}}}

  Функцијата `COALESCE` гарантира дека локалитетите без регистрирани објекти ќе бидат прикажани со вредност 0, наместо `NULL`, што овозможува поконзистентна анализа.

==== Б. Воведување на индекси ====

За оптимизација на перформансите, особено при филтрирање и пребарување, се користат следните индекси:

{{{
CREATE INDEX idx_sites_status ON Sites(protection_status_id);
CREATE INDEX idx_objects_site_id ON Objects(site_id);
}}}

==== В. Состојба ПОСЛЕ индексирање ====

Индексот `idx_sites_status` овозможува побрзо филтрирање на заштитените локалитети според нивниот статус, додека `idx_objects_site_id` ја подобрува ефикасноста при пресметка на бројот на објекти за секој локалитет.

Како резултат, `Protected_Sites_Inventory` претставува специјализиран и оптимизиран поглед кој ја олеснува анализата на заштитените археолошки локалитети, обезбедува подобра институционална контрола, поддржува донесување одлуки за конзервација и овозможува поефикасно управување со националното културно наследство.


=== 3. Анализа на поглед 3: Тековна локација на предмет (Object_Current_Location) ===

1. **Примарен филтер:** Примарен филтер за погледот `Object_Current_Location` ќе биде според `object_id` на предметот, а исто така ќе се користи и според името на институцијата каде што се наоѓа.
2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи без него се губи време при утврдување на тековната локација на предметот.

Тест прашалникот кој се извршува е следниот:

{{{
EXPLAIN ANALYZE
SELECT *
FROM Object_Current_Location
WHERE object_id = 1340;
}}}

==== А. Состојба ПРЕД индексирање ====

* **Време на извршување на прашалникот пред оптимизација:**
  **0.073 ms**

* **Најбавни операции (Bottleneck Analysis):**

  Иницијалното време за извршување на погледот е 0.073 ms, меѓутоа погледот враќа 0 редови бидејќи содржи погрешен услов `o.object_id = o.object_id` кој нема вистинска врска со табелата `Institutions`.

  Поради тоа погледот е логички неточен и пристапуваме кон целосна замена на имплементацијата и индексирање.

  Најскапата операција е `Seq Scan on institutions` со `ORDER BY random()` — скенира сите 200 институции само за да врати 1 случаен ред, без никаква вистинска `JOIN` врска.

==== Б. Воведување на индекси ====

По замена на VIEW-от со вистински `JOIN` преку `Object_Location_History` и поставување на индекси:

{{{
CREATE INDEX idx_olh_object_id ON Object_Location_History(object_id);
CREATE INDEX idx_olh_end_date ON Object_Location_History(end_date);
}}}

==== В. Состојба ПОСЛЕ индексирање ====

Времето на извршување се подобри и погледот почна да враќа точни резултати.

По оптимизацијата планерот го користи `Index Scan using idx_olh_object_id` наместо `Seq Scan`, а `Institutions` се бара преку примарен клуч.

* **Ново време на извршување на прашалникот:**
  **0.262 ms**

Ова време е прифатливо за апликацијата.

* **Заклучок:**

  `Object_Current_Location` овозможува точно и ефикасно следење на тековната институција во која се наоѓа конкретен археолошки предмет. Со користење на `Object_Location_History` и условот `end_date IS NULL`, погледот ја прикажува само активната локација на предметот.


=== 4. Анализа на поглед 4: Класификација на предмет според култура (Object_with_Culture) ===

1. **Примарен филтер:** Примарен филтер за погледот `Object_with_Culture` ќе биде според `object_id` на предметот, а исто така ќе се користи и според името на културата и категоријата на која припаѓа предметот.
2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи без него се губи време при класификација на предметите според култура и категорија.

Тест прашалникот кој се извршува е следниот:

{{{
EXPLAIN ANALYZE
SELECT *
FROM Object_with_Culture
WHERE object_id = 150;
}}}

==== А. Состојба ПРЕД индексирање ====

* **Време на извршување на прашалникот пред индексирање:**
  **252.083 ms**

* **Најбавни операции (Bottleneck Analysis):**

  Ова не е прифатливо време за апликацијата па затоа пристапуваме кон индексирање.

  Најскапите операции се два `Seq Scan` — еден врз табелата `Culture` со 270 редови и еден врз `Categories` со 280 редови, при што и двата користат `ORDER BY random()` и лажен услов `o.object_id = o.object_id` кој нема вистинска врска со овие табели.

  Поради тоа погледот враќа произволни и логички неточни резултати.

==== Б. Воведување на индекси ====

Поставен е следниот индекс:

{{{
CREATE INDEX idx_oc_object_id ON Object_Classification(object_id);
}}}

==== В. Состојба ПОСЛЕ индексирање ====

По оптимизацијата планерот користи:

 * `Index Scan using idx_oc_object_id` на `Object_Classification`
 * `Index Scan using culture_pkey` на `Culture`
 * `Index Scan using categories_pkey` на `Categories`

Наместо `Seq Scan + ORDER BY random()`, сега се користат индекси и вистински `JOIN` врски.

* **Ново време на извршување на прашалникот:**
  **0.142 ms**

Времето на извршување падна од **252.083 ms** на **0.142 ms** — подобрување од **~1775 пати**.

* **Заклучок:**

  `Object_with_Culture` овозможува брза и точна класификација на археолошките предмети според нивната култура и категорија. Оптимизацијата преку индексот на `Object_Classification(object_id)` ја елиминира потребата од непотребни целосни скенирања и овозможува директен пристап до релевантните класификациски записи.


=== 5. Анализа на поглед 5: Предмети на изложби (Exhibition_Objects) ===

1. **Примарен филтер:** Примарен филтер за погледот `Exhibition_Objects` ќе биде според `exhibition_id` на изложбата, со цел да се прикажат сите предмети и институции кои учествуваат на одредена изложба.
2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи без него се губи време при преглед на предметите по изложби.

Тест прашалникот кој се извршува е следниот:

{{{
EXPLAIN ANALYZE
SELECT *
FROM Exhibition_Objects
WHERE exhibition_id = 204050;
}}}

==== А. Состојба ПРЕД индексирање ====

* **Време на извршување на прашалникот пред индексирање:**
  **1485.615 ms**

* **Најбавни операции (Bottleneck Analysis):**

  Ова не е прифатливо време за апликацијата па затоа пристапуваме кон индексирање.

  Најскапите операции се:

   * `Seq Scan on objects` кој скенира сите 2 милиони предмети
   * `Seq Scan on exhibitions` со 5000 редови
   * `Seq Scan on institutions` со 200 редови

  Дополнително, погледот користи `ORDER BY random()` и лажен услов `o.object_id = o.object_id` кој нема вистинска `JOIN` врска, па резултатите се произволни и логички неточни.

==== Б. Воведување на индекси ====

Поставен е следниот индекс:

{{{
CREATE INDEX idx_oe_exhibition_id ON Object_Exhibition(exhibition_id);
}}}

По првичното индексирање е добиено:

{{{
Execution Time: 27304.836 ms
}}}

Ова не е во ред. `Index Scan using objects_pkey` за 100 loops значи дека за секој од 100-те редови се бара посебен lookup во `Objects` табелата со 2 милиони записи.

Поради тоа треба додатен индекс на `Object_Exhibition` за `object_id`:

{{{
CREATE INDEX idx_oe_object_id ON Object_Exhibition(object_id);
}}}

==== В. Состојба ПОСЛЕ индексирање ====

По оптимизацијата планерот користи:

 * `Bitmap Index Scan using idx_oe_exhibition_id`
 * `Index Scan using exhibitions_pkey`
 * `Index Scan using institutions_pkey`
 * `Index Scan using objects_pkey`

* **Ново време на извршување на прашалникот:**
  **36.343 ms**

Времето на извршување падна од **27304.836 ms** на **36.343 ms** — подобрување од **~751 пати**.

* **Заклучок:**

  `Exhibition_Objects` станува значително поефикасен за приказ на предмети по изложби. Индексите на `Object_Exhibition(exhibition_id)` и `Object_Exhibition(object_id)` овозможуваат брзо пронаоѓање на релациите помеѓу изложбите и предметите, што е критично за музејски и административни прегледи.


=== 6. Анализа на поглед 6: Детали за истражувачки пристап (Research_Access_Details) ===

1. **Примарен филтер:** Примарен филтер за погледот `Research_Access_Details` ќе биде според `user_id` на истражувачот, со цел да се прикажат сите барања за пристап до предмети и нивниот статус за одреден корисник.
2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи без него се губи време при преглед на пристапите на истражувачите.

Тест прашалникот кој се извршува е следниот:

{{{
EXPLAIN ANALYZE
SELECT *
FROM Research_Access_Details
WHERE user_id = 1000;
}}}

==== А. Состојба ПРЕД индексирање ====

* **Иницијална состојба:**

  Иницијалното време за извршување на погледот не можеше да се измери бидејќи стариот VIEW користеше `OFFSET` со `COUNT(*)` врз табела со 2 милиони редови, што предизвикуваше извршување подолго од 2 минути.

  Поради тоа погледот беше целосно нефункционален и пристапивме кон замена на целата имплементација со вистински `JOIN` врз табелата `Researcher_Access`.

* **Време по замена на VIEW-от, но пред целосна оптимизација:**
  **51867 ms = 51 секунди**

Ова време е исто така неприфатливо.

==== Б. Воведување на индекси ====

Поставени се следните индекси:

{{{
CREATE INDEX idx_ra_user_id ON Researcher_Access(user_id);
CREATE INDEX idx_ra_object_id ON Researcher_Access(object_id);
CREATE INDEX idx_ra_institution_id ON Researcher_Access(institution_id);

ANALYZE Researcher_Access;
}}}

Потоа е додаден и соодветен составен индекс:

{{{
CREATE INDEX idx_ra_user_object ON Researcher_Access(user_id, object_id);
}}}

==== В. Состојба ПОСЛЕ индексирање ====

* **Најдобро постигнато време по оптимизација:**
  **146.949 ms**

Понатамошно подобрување само со индекси не е можно поради следните причини:

 * погледот враќа голем број на резултати, односно 221+ редови
 * за секој ред прави посебен `Nested Loop JOIN` со табелата `Objects`, која содржи 2 милиони записи

Кај вакви аналитички прашалници кои враќаат многу редови и работат со многу голема табела, индексите ретко кога можат значително да ги подобрат перформансите над одредена граница.

* **Заклучок:**

  `Research_Access_Details` е важен административен поглед за следење на барањата за пристап од страна на истражувачите. Иако оптимизацијата значително ја подобрува состојбата во однос на нефункционалниот стар view, природата на прашалникот и големиот број записи значат дека времето останува повисоко во споредба со другите погледи.


=== 7. Анализа на поглед 7: Историја на третмани (Treatment_History) ===

1. **Примарен филтер:** Примарен филтер за погледот `Treatment_History` ќе биде според `object_id` на предметот, со цел да се прикажат сите чекори на конзервација и третман извршени врз одреден предмет.
2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи без него се губи време при преглед на историјата на третмани.

Тест прашалникот кој се извршува е следниот:

{{{
EXPLAIN ANALYZE
SELECT *
FROM Treatment_History
WHERE object_id = 100;
}}}

==== А. Состојба ПРЕД индексирање ====

* **Иницијална состојба:**

  Иницијалното време за извршување на погледот не можеше да се измери бидејќи беше имплементиран како `MATERIALIZED VIEW` кој не дозволува филтрирање по `object_id`.

  Поради тоа пристапивме кон замена со обичен `VIEW`.

==== Б. Воведување на индекси ====

Постоечките индекси поставени претходно во кодот беа доволни за оптимално извршување на погледот:

{{{
CREATE INDEX idx_tsl_treatment ON Treatment_Step_Log(treatment_id);
CREATE INDEX idx_treatments_object ON Treatments(object_id);
CREATE INDEX idx_tsl_treatment_user_step ON Treatment_Step_Log(treatment_id, performed_by_user, step_number);
}}}

Не се потребни дополнителни индекси.

==== В. Состојба ПОСЛЕ индексирање ====

По замената времето на извршување изнесува:

* **Ново време на извршување на прашалникот:**
  **0.549 ms**

Ова е прифатливо време.

Планерот веќе ги користи постоечките индекси:

 * `idx_treatments_object` на `Treatments(object_id)`
 * `idx_tsl_treatment` на `Treatment_Step_Log(treatment_id)`
 * `Index Scan using objects_pkey`
 * `Index Scan using users_pkey`

* **Заклучок:**

  `Treatment_History` е оптимизиран поглед кој овозможува брз преглед на сите третмани и чекори на конзервација за конкретен археолошки предмет. Бидејќи постоечките индекси веќе ги покриваат главните `JOIN` и филтер услови, не е потребна дополнителна оптимизација.


=== 8. Анализа на поглед 8: Публикации со автори (Publications_with_Authors) ===

1. **Примарен филтер:** Примарен филтер за погледот `Publications_with_Authors` ќе биде според `publication_id`, со цел да се прикажат сите автори на одредена публикација.
2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи без него се губи време при библиографски преглед на публикациите.

Тест прашалникот кој се извршува е следниот:

{{{
EXPLAIN ANALYZE
SELECT *
FROM Publications_with_Authors
WHERE publication_id = 100;
}}}

==== А. Состојба ПРЕД индексирање ====

* **Време на извршување на прашалникот пред индексирање:**
  **1158.266 ms**

Ова време е неприфатливо.

Погледот беше имплементиран како `MATERIALIZED VIEW` без можност за филтрирање, па пристапивме кон замена со обичен `VIEW`.

==== Б. Воведување на индекси ====

Поставени се следните индекси:

{{{
CREATE INDEX idx_pa_publication_id ON Publication_Authors(publication_id);
CREATE INDEX idx_pa_author_id ON Publication_Authors(author_id);

ANALYZE Publication_Authors;
ANALYZE Publications;
}}}

==== В. Состојба ПОСЛЕ индексирање ====

По оптимизацијата планерот користи:

 * `Index Only Scan using publication_authors_pkey`
 * `Index Scan using publications_pkey`
 * `Index Scan using authors_pkey`

* **Ново време на извршување на прашалникот:**
  **24.479 ms**

Времето на извршување падна од **1158.266 ms** на **24.479 ms** — подобрување од **~47 пати**.

* **Заклучок:**

  `Publications_with_Authors` овозможува ефикасен библиографски преглед на публикациите и нивните автори. Оптимизацијата е особено важна бидејќи табелата `Publication_Authors` претставува M:N релација помеѓу публикации и автори, а индексите овозможуваат брзо филтрирање според конкретна публикација.


=== 9. Анализа на поглед 9: Целосен преглед на наследство (Heritage_Full_Overview) ===

1. **Примарен филтер:** Примарен филтер за погледот `Heritage_Full_Overview` ќе биде според `site_id` на локалитетот, со цел да се прикажат сите фрагменти, предмети, региони и статус на заштита поврзани со одреден археолошки локалитет.
2. **Случај на употреба:** За овој поглед ни се важни перформансите, бидејќи работи врз табелата `Fragments` која содржи 10 милиони редови и прави `JOIN` со табелата `Objects` која содржи 2 милиони редови.

Погледот беше претходно имплементиран како `MATERIALIZED VIEW` без можност за филтрирање по `site_id`. Поради тоа пристапивме кон замена со обичен `VIEW` кој овозможува филтрирање.

Тест прашалникот кој се извршува е следниот:

{{{
EXPLAIN ANALYZE
SELECT *
FROM Heritage_Full_Overview
WHERE site_id = 100
LIMIT 10;
}}}

==== А. Состојба ПРЕД индексирање ====

При мерење на иницијалното време на извршување, поради екстремно големата табела `Fragments` со 10 милиони редови, беше додаден `LIMIT 10` за да се добие мерливо време без да се чека неколку минути.

* **Време на извршување на прашалникот пред индексирање:**
  **4055.439 ms = ~4 секунди**

Ова е неприфатливо.

* **Најбавни операции (Bottleneck Analysis):**

  Главниот проблем беше `Seq Scan on fragments`, кој скенираше сите 10 милиони редови со `Rows Removed by Filter: 47805`, односно го отфрлаше речиси целиот резултат.

==== Б. Воведување на индекси ====

Поставени се следните индекси:

{{{
CREATE INDEX IF NOT EXISTS idx_fragments_site_id ON Fragments(site_id);
CREATE INDEX IF NOT EXISTS idx_objects_site_id ON Objects(site_id);
}}}

==== В. Состојба ПОСЛЕ индексирање ====

По поставувањето на индексите, планерот повеќе не прави `Seq Scan on fragments`, туку користи:

 * `Index Scan using idx_fragments_site_id on fragments` — наместо скенирање на 10 милиони редови
 * `Index Scan using objects_pkey on objects`
 * `Index Scan using sites_pkey on sites`
 * `Index Scan using regions_pkey on regions`
 * `Index Scan using protection_status_pkey on protection_status`

* **Ново време на извршување на прашалникот:**
  **1.264 ms**

Времето на извршување падна од **4055.439 ms** на **1.264 ms** — подобрување од **~3208 пати**.

* **Заклучок:**

  `Heritage_Full_Overview` претставува еден од најважните аналитички погледи во системот, бидејќи овозможува централен преглед на фрагменти, предмети, региони и статус на заштита за конкретен локалитет. Индексирањето на `Fragments(site_id)` е клучно, бидејќи табелата `Fragments` содржи огромен број записи и без индекс прашалникот мора да скенира милиони редови.