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

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 е според статусот на заштита на локалитетот, односно преку условот ps.name = 'Заштитен'.

2. Случај на употреба: Овој поглед е наменет за административен, институционален и аналитички преглед на сите археолошки локалитети кои имаат статус „Заштитен“. Неговата основна цел е да овозможи брз пристап до информации за заштитените локалитети и нивниот археолошки инвентар, без потреба од сложени SQL пребарувања низ повеќе поврзани табели.

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

EXPLAIN ANALYZE
SELECT *
FROM Protected_Sites_Inventory;

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

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

А. Состојба ПРЕД оптимизација

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

Во неговата имплементација се користи 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, што овозможува поконзистентна анализа.

Податоците се сортирани според discovery_year DESC, што овозможува поновите откриени заштитени локалитети да бидат прикажани први, што е практично за институционално следење и приоритизација.

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

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

CREATE INDEX idx_objects_site_id ON Objects(site_id);

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

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

Како резултат, 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

Иницијалното време за извршување на погледот изнесуваше 0.073 ms, меѓутоа погледот враќаше 0 редови бидејќи содржеше погрешен услов:

o.object_id = o.object_id

Овој услов не воспоставуваше вистинска врска со табелата Institutions. Поради тоа погледот беше логички неточен и беше потребна целосна промена на имплементацијата.

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

Б. Корекција на имплементацијата

Поради ова, погледот беше редизајниран со користење на табелата Object_Location_History, која ја содржи историјата на движење на предметите.

Во финалната верзија се користат коректни JOIN операции помеѓу табелите:

• Objects
• Object_Location_History
• Institutions

Условот:

WHERE olh.end_date IS NULL

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

В. Состојба ПОСЛЕ корекцијата

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

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

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

• Заклучок:

Object_Current_Location обезбедува точен и ефикасен приказ на моменталната локација на секој археолошки предмет и претставува важна алатка за музејска евиденција и следење на културното наследство.

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

1. Примарен филтер: Примарен филтер за погледот Object_with_Culture е object_id, при што се овозможува поврзување на објектите со нивната културна и категоријална класификација преку JOIN операции.

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

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

EXPLAIN ANALYZE
SELECT *
FROM Object_with_Culture
WHERE object_id = 150;

А. Состојба ПРЕД оптимизација

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

Иницијалното време за извршување изнесуваше 252.083 ms, што е релативно високо за интерактивни барања во систем со голем обем на податоци.

Анализата на извршниот план покажа дека најголемиот трошок доаѓа од JOIN операциите помеѓу:

• Objects
• Object_Classification
• Culture
• Categories

PostgreSQL во одредени случаи користи Sequential Scan на помалите lookup табели, што влијае врз времето на извршување.

Б. Оптимизација

Во тековната верзија на системот не се користи посебен индекс за овој view, туку оптимизацијата се базира на постоечките primary key индекси и foreign key релации.

Планерот автоматски избира Index Scan за:

• Culture преку примарниот клуч
• Categories преку примарниот клуч
• Objects и Object_Classification преку нивните PK вредности

В. Состојба ПОСЛЕ оптимизација

И покрај отсуството на специјален индекс за Object_Classification(object_id), извршувањето останува оптимизирано поради малата големина на lookup табелите и ефикасниот JOIN редослед.

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

252 ms

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

• Заклучок:

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

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

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

2. Случај на употреба: Овој поглед се користи за преглед на учество на артефакти во изложби, при што перформансите се важни поради големиот број записи во табелата Objects.

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

EXPLAIN ANALYZE
SELECT *
FROM Exhibition_Objects
WHERE exhibition_id = 204050;

А. Состојба ПРЕД оптимизација

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

Иницијалното време за извршување изнесуваше 1485.615 ms, што беше резултат на голем број JOIN операции врз обемни табели.

Анализата на извршниот план покажа дека најголемиот трошок доаѓа од JOIN операцијата помеѓу Object_Exhibition и Objects, поради големиот број записи во табелата Objects.

Почетната оптимизација се базираше на постоечкиот индекс преку примарниот клуч на Objects и Exhibitions, но поради големиот број редови во Object_Exhibition, планерот користеше Nested Loop Join со повеќе lookup операции.

Б. Оптимизација

Во тековниот модел не е додаден дополнителен индекс за object_id во Object_Exhibition, па оптимизацијата се базира на:

• primary key индекси на Exhibitions и Institutions
• primary key на Objects
• постоечката индексна структура на Object_Exhibition

Како резултат, PostgreSQL користи:

• Bitmap Index Scan врз Object_Exhibition по exhibition_id
• Index Scan врз Objects преку primary key
• Index Scan врз Exhibitions и Institutions преку primary keys

В. Состојба ПОСЛЕ оптимизација

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

Времето на извршување се намали од 1485.615 ms на 36.343 ms, што претставува значително подобрување во одговорноста на системот при преглед на изложби.

• Заклучок:

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

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

1. Примарен филтер: Примарен филтер за погледот Research_Access_Details е user_id, со цел приказ на сите барања за пристап до археолошки објекти и нивниот статус за одреден истражувач.

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

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

EXPLAIN ANALYZE
SELECT *
FROM Research_Access_Details
WHERE user_id = 1000;

А. Состојба ПРЕД оптимизација

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

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

Анализата на извршниот план покажа дека најголемиот трошок доаѓа од:

• Nested Loop JOIN помеѓу Researcher_Access и Objects
• обработка на голем број редови
• дополнителни JOIN операции со Users, Institutions и Status_Types

Б. Оптимизација

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

CREATE INDEX idx_fragments_object ON Fragments(object_id);
CREATE INDEX idx_fragments_site_id ON Fragments(site_id);
CREATE INDEX idx_objects_site_id ON Objects(site_id);
CREATE INDEX idx_ra_institution_id ON Researcher_Access(institution_id);

Иако овие индекси не се директно на user_id или object_id, тие индиректно придонесуваат кон подобра JOIN ефикасност преку:

• побрз пристап до Objects преку site_id
• побрза филтрација на Researcher_Access преку institution_id

В. Состојба ПОСЛЕ оптимизација

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

146.949 ms

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

• резултатниот сет е голем
• JOIN операциите со Objects се кардинално скапи
• селективноста на податоците е ниска за дел од релациите

Затоа, перформансите во вакви аналитички погледи зависат повеќе од структурата на JOIN-овите и обемот на податоци, отколку од дополнително индексирање.

• Заклучок:

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

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

1. Примарен филтер: Примарен филтер за погледот Treatment_History е object_id, со цел да се прикажат сите чекори на конзервација и третман за одреден предмет.

2. Случај на употреба: Овој поглед се користи за преглед на историјата на третмани и конзерваторски чекори поврзани со конкретен археолошки предмет.

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

EXPLAIN ANALYZE
SELECT *
FROM Treatment_History
WHERE object_id = 100;

А. Состојба на извршување

Иницијалното време на извршување изнесуваше:

0.549 ms

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

Б. Оптимизација

Планерот не користи дополнителни специјални индекси за овој поглед, туку се потпира на постоечките primary key индекси на табелите што учествуваат во JOIN операциите.

Во тековната база не беа воведени нови индекси за овој view, бидејќи перформансите се веќе задоволителни.

В. Заклучок

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

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

Иницијалното време за извршување на погледот беше 1158.266 ms, што е неприфатливо.

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

Б. Оптимизација

Во тековната база не се воведени нови индекси специјално за овој поглед, па планерот се потпира на постоечките primary key индекси и оптимизацијата на JOIN операциите.

В. Состојба ПОСЛЕ оптимизација

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

• 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 овозможува ефикасен библиографски преглед на публикациите и нивните автори. Во финалната верзија не се воведуваат нови специјални индекси, бидејќи постоечките primary key индекси и оптимизацијата на JOIN операциите обезбедуваат прифатливо извршување.

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);

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

По поставувањето на индексот, планерот повеќе не прави 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 содржи огромен број записи и без индекс прашалникот мора да скенира милиони редови.