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

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