Оптимизација на Прашалници и Анализа на Перформанси (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.

Ги извршивме следните оптимизациски наредби во базата:

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

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-от: VidiDBQueryOptimization.pdf​