Changes between Initial Version and Version 1 of QueryOptimization

Timestamp:

06/16/26 00:37:34 (5 days ago)

Author:

231166

Comment:

--

QueryOptimization

@@ +1 @@
+= Оптимизација на Прашалници и Анализа на Перформанси (QueryOptimization) =
+
+Оваа страница го прикажува процесот на детекција на критичните точки (bottlenecks) во системот кои настанаа по популацијата на базата со над 40 милиони записи, како и успешната стратегија за оптимизација преку имплементација на соодветни индекси.
+
+---
+
+== 1. Детекција на критични точки во извршувањето ==
+
+При анализа на извршните планови на базата, утврдено е дека најголемо забавување се случува кај погледите и комплексните кверија кои вршат агрегација и филтрирање врз најобемните табели во системот: `Rating` (со преку 24 милиони записи), `WatchHistory` (10 милиони) и `Review` (10 милиони). 
+
+Како примарен тест-случај за анализа беше земен погледот '''view_top_rated_content'''. Овој поглед е од исклучителна важност бидејќи ги прикажува најпопуларните и најдобро оценетите содржини на почетната страна на корисничкиот интерфејс — првото нешто што корисникот го гледа при вклучување на сервисот.
+
+=== Иницијално мерење (Без индекси) ===
+Пред оптимизацијата, извршувањето на погледот генерираше енормно забавување поради потребата од целосно скенирање на табелите (Sequential Scan).
+* '''Почетно извршно време:''' ~13.5 секунди (13500 ms).
+* '''Причина:''' Секоја агрегација (`AVG`, `COUNT`) или филтрирање преку надворешни клучеви во `JOIN` операциите предизвикуваше базата да ги чита сите милиони записи директно од дискот во меморија без никаков референтен патоказ.
+
+---
+
+== 2. Имплементација на Индекси на Надворешни Клучеви (FK Indexes) ==
+
+За да се овозможи брзо и ефикасно поврзување (JOIN) на трансакциските табели со матичните табели за корисници (`User`) и содржини (`Media`), имплементиравме стратегија на индексирање на сите критични надворешни клучеви (`FOREIGN KEY`). 
+
+Со ова, наместо скапиот `Sequential Scan`, менаџерот на базата (PostgreSQL Query Planner) може да искористи `Index Scan` или `Bitmap Index Scan`.
+
+Ги извршивме следните оптимизациски наредби во базата:
+
+{{{
+#!sql
+-- Оптимизација на табелата Rating (24 милиони записи)
+-- Овозможува моментално групирање и пресметка на просечен рејтинг по содржина
+CREATE INDEX idx_rating_content ON Rating (ContentContentID);
+CREATE INDEX idx_rating_user ON Rating (UserUserID);
+
+-- Оптимизација на табелата WatchHistory (10 милиони записи)
+-- Овозможува брз дофат на историјата на гледање за конкретен корисник/медиум
+CREATE INDEX idx_watchhistory_content ON WatchHistory (ContentContentID);
+CREATE INDEX idx_watchhistory_user ON WatchHistory (UserUserID);
+
+-- Оптимизација на табелата Review (10 милиони записи)
+-- Овозможува брзо вчитување на коментари под деталниот приказ на филмови/серии
+CREATE INDEX idx_review_content ON Review (ContentContentID);
+CREATE INDEX idx_review_user ON Review (UserUserID);
+}}}
+
+---
+
+== 3. Анализа со EXPLAIN ANALYZE и Резултати ==
+
+За евалуација на успешноста на индексите, беше искористена наредбата `EXPLAIN ANALYZE`, која не само што го предвидува планот, туку реално го извршува прашалникот и враќа точни статистики за потрошеното време во милисекунди и користениот алгоритам за пребарување.
+
+{{{
+#!sql
+EXPLAIN ANALYZE 
+SELECT * FROM view_top_rated_content 
+WHERE content_type = 'Movie' AND title LIKE 'A%';
+}}}
+
+=== Приказот на извршниот план пред и по оптимизацијата ни ги откри следните клучни промени: ===
+
+1. '''Елиминација на Sequential Scan:''' На местото каде што претходно стоеше `Seq Scan on rating`, сега Query Planner-от резултира со `Index Scan using idx_rating_content`.
+2. '''Побрзи JOIN операции:''' Наместо тешки алгоритми за спојување кои бараат многу меморија, базата сега користи оптиматизиран `Hash Join` или `Merge Join` потпрен на претходно сортираните индексни дрва (B-Tree).
+
+=== Финален ефект врз перформансите: ===
+* Извршното време на комплексните кверија кои претходно траеја со секунди (или во потешки случаи со часови при посложени аналитички прашалници) се намали на **неколку милисекунди**.
+* Обезбедена е висока скалабилност, што значи дека дури и ако бројот на записи во `WatchHistory` или `Rating` се удвои, времето на одговор ќе остане во рамките на прифатливи граници за непречено корисничко искуство (Real-time Streaming Experience).
+
+> '''Заклучок:''' Иако креираните индекси зафаќаат дополнителен простор на дискот (физичка меморија) и минимално го забавуваат процесот на запишување (`INSERT`), драстичното намалување на времето на одговор при читање (`SELECT`) ја оправдува нивната имплементација во сооднос 1:100 во корист на перформансите на апликацијата.
+
+Тука може да ја видите деталната анализа на PDF file-от: [attachment:VidiDBQueryOptimization.pdf VidiDBQueryOptimization.pdf]