= Профилирање и оптимизација на прашалници

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

== Преглед на првичниот проблем

Како пример за овој дел од проектот го земавме погледот за извесна статистичка анализа на резултатите на студентите по задача, `v_statistiki_vremenski_po_student_i_zadacha`. Иако навидум овој поглед сам по себе изгледа доста комплексен, проблем всушност прозилезе дека е во `v_activity_with_interval_and_payload_next`, каде што во потпрашалници се прави пресметка за времето на првата активност пред моментално разгледуваната и првата активност по моментално разгледуваната. Времето на извршување на прашалникот вклучувајќи ги вакви операции во просек надминуваше 3 минути врз табела со околу 500.000 записи.

{{{#!sql

(SELECT ait3.payload AS payload_next

     FROM activity_in_task ait3

     WHERE ait3.person_id = pppppp.person_id AND ait3.task_in_test_instance_id = pppppp.task_in_test_instance_id AND
         ait3.when_occured = pppppp.when_occured_next) AS payload_next

(SELECT min(ait2.when_occured) AS min

           FROM activity_in_task ait2

           WHERE ait2.person_id = ait.person_id AND ait2.task_in_test_instance_id = ait.task_in_test_instance_id AND
               ait2.when_occured > ait.when_occured) AS when_occured_next
}}}



== Како дојдовме до ваков заклучок?

Детална анализа на Execution Plan

Execution планот (план на извршување) е најмоќната алатка за да го разбереме "начинот на размислување" на оптимизаторот, и да најдеме потенцијални точки за подобрување. Со негова анализа може да се утврди:
- кои индекси (не) се користат,
- дали се прават целосни скенирања на табела (table scans),
- дали се прават непотребни сортирања или спојувања,
- колкави се проценетите и реалните броеви на редови што се процесираат.

Основни концепти за читање и разбирање на Execution Plans
1. Структура на планот
Execution планот е во форма на хиерархиска структура (дрво) каде што:

Корен е конечниот резултат на прашачникот
Листови се основните табели или индекси
Внатрешни јазли се операциите (JOIN, WHERE, GROUP BY, итн.)
Текот на податоците е оздола нагоре

2. Клучни метрики во планот
Cost (Цена на извршување):
cost=0.43..156789.45

Првиот број (0.43) е startup cost - цена до враќање на првиот ред
Вториот број (156789.45) е total cost - цена по враќање на сите редови
Цената е релативна единица, не се мери во време

Rows (Редови):
rows=1000

Проценет број на редови што ќе се вратат од оваа операција
Базиран на статистики на планерот

Width (Ширина):
width=64

Просечна големина на редот во бајти

Actual Time (Реално време):
actual time=180000.123..180456.789

Реалното време во милисекунди
Првиот број е време до првиот ред
Вториот број е вкупно време

Loops (Циклуси):
loops=1000

Колку пати се извршила операцијата
Вкупното време = actual time × loops

3. Типови операции во планот
Scan операции:

`Seq Scan` - Секвенцијално скенирање (ја чита цела табела)
`Index Scan` - Користи индекс за пронаоѓање на потребните редици
`Index Only Scan` - Сите потребни податоци се во индексот
`Bitmap Heap Scan` - Комбинира повеќе индекси

`Join` операции:

`Nested Loop` - За секој ред од левата табела, пребарува во десната (многу спора операција)
`Hash Join` - Создава hash табела и спојува по клучот
`Merge Join` - Спојува два сортирани сетови

`Aggregate` операции:

`Aggregate` - GROUP BY, COUNT, SUM, итн.
`WindowAgg` - Window функции (LEAD, LAG, ROW_NUMBER, итн.)
`HashAggregate` - GROUP BY со hash табела

== Анализа на нашиот конкретен случај

За визуелизација на execution plan-от ја користевме следната алатка [https://explain.dalibo.com/]

За да имаме преглед и врз меморијата што се користи, ја извршивме следната команда за да генерираме execution plan

{{{#!sql

explain (analyze, buffers)
select *
from v_statistiki_vremenski_po_student_i_zadacha;

}}}

Очигледно беше дека операцијата `NestedLoopJoin` во рамки на која понатаму се прави `IndexScan` е онаа операција која е причина за лошите перформанси на прашалникот. На сликата подолу е видливо дека на овие операции практично отпаѓа целото време на извршување. Дополнително, можеме да воочиме дека естимацијата направена во однос на редови кои треба да се вратат е непрезицна и дека за секој ред од табелата ова скенирање се повторува.

=== Аплицирање на промената во прашалникот

{{{#!sql

    lead(ait.when_occured) over (
              partition by ait.person_id, ait.task_in_test_instance_id
              order by ait.when_occured
              ) AS when_occured_next,

    lead(ait.payload) over (
              partition by ait.person_id, ait.task_in_test_instance_id
              order by ait.when_occured
              ) as payload_next

}}}

Главната оптимизација се состоеше во замена на корелираните потпрашалници со window функции во погледот `v_activity_with_interval_and_payload_next`.

По аплицирање на оптимизацијата промената беше веднаш видлива и во планот на извршување. Претходно скапата операција на NestedLoop која траеше околу 3 минути, сега е заменета со WindowAgg која трае вкупно 144 ms. По ова веднаш беше видливо и подобрување во вкупното време на извршување на прашалникот кое драстично се намали на ~200ms.

== Зошто Window функциите се толку поефикасни?

1. Еднократно скенирање на табелата: Window функциите поминуваат само еднаш низ податоците, додека корелираните потпрашалници го скенираат истиот сет на податоци повторно за секој ред
2. Нема повторливи пребарувања: Корелираните потпрашалници се извршуваат N пати (каде што N е бројот на редови), што резултира со O(N²) сложеност
3. Подобра оптимизација: Современите планери (PostgreSQL, SQL Server, Oracle) користат специјализирани алгоритми за оптимизација на window функции
4. Намален броја на I/O операции: Наместо повеќе извршувања на потпрашалници со потенцијални пристапи до диск, сè се процесира во РАМ во една ефикасна операција
5. Подобро искористување на индексите: Планерот може лесно да ги оптимизира window функциите, особено кога има индекси на колоните во `PARTITION BY` и `ORDER BY`, што најчесто ќе биде случај бидејќи тоа се колони на надворешни клучеви и посекако се користат за спојувања во обични прашалници.