Changes between Initial Version and Version 1 of Optimization

Timestamp:

06/12/25 07:59:22 (2 weeks ago)

Author:

211012

Comment:

--

Optimization

@@ +1 @@
+Профилирање и оптимизација на прашалници
+
+Во базите на податоци претставуваат критична компонента за успешното функционирање на апликациите. Како што расте волуменот на податоците и комплексноста на прашалницте, перформансите на базата стануваат тесно грло кое има влијание врз генералните перформанси на апликацијата, оперативната ефикасност и најважно, врз кориснчкото искуство.
+Еден од најчестите проблеми во администрацијата на базите се неефикасните 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`, што најчесто ќе биде случај бидејќи тоа се колони на надворешни клучеви и посекако се користат за спојувања во обични прашалници.