Changes between Initial Version and Version 1 of QueryOptimization

Timestamp:

05/26/26 14:46:20 (28 hours ago)

Author:

231511

Comment:

--

QueryOptimization

@@ +1 @@
+= Оптимизација на прашалници и погледи =
+
+=== 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` содржи огромен број записи и без индекс прашалникот мора да скенира милиони редови.