Changes between Version 2 and Version 3 of OtherTopics

Timestamp:

09/24/26 20:30:42 (4 days ago)

Author:

233149

Comment:

--

OtherTopics

@@ -65 +65 @@
 ограничување.
 
-=== Резултати пред и по индексите
-
-|| '''Барање''' || '''Пред''' || '''По''' || '''Промена''' ||
-|| Извештај 1 - проодност по предмет || 146,0 ms || 156,0 ms || нема ||
-|| Извештај 2 - досие на сите студенти || 188,5 ms || 179,4 ms || нема ||
-|| Извештај 3 - најуспешен студент по програма || 107,3 ms || 94,0 ms || нема ||
-|| Извештај 4 - најоптоварен професор || 121,9 ms || 124,6 ms || нема ||
-|| Извештај 5 - промена на активноста || 124,0 ms || 138,1 ms || нема ||
-|| Досие на '''еден''' студент || 2,19 ms || '''1,07 ms''' || '''2,0 пати побрзо''' ||
-|| Дневник на професор за еден професор || 16,3 ms || '''7,6 ms''' || '''2,1 пати побрзо''' ||
-
-=== Зошто петте извештаи не станаа побрзи
-
-Ова не е неуспех на индексите, туку очекувано однесување. Петте извештаи од Фаза
-6 се агрегатни - тие читаат '''сè''' од semesters_subjects, passed_subjects и
+=== Кои прашалници се анализирани
+
+Анализирани се седум прашалници. Шест од нив се '''точно извештаите од
+Фаза 6''' (види [wiki:AdvancedReports AdvancedReports], скрипта [attachment:reports.sql]),
+а седмиот е секојдневно барање на апликацијата, додаден за споредба.
+
+|| '''#''' || '''Прашалник''' || '''Извор''' || '''Рутина во reports.sql''' ||
+|| 1 || Проодност по предмет и семестар || Фаза 6, Извештај 1 || {{{rep_subject_pass_rate()}}} ||
+|| 2 || Досие на сите студенти || Фаза 6, Извештај 2 || {{{rep_student_dossier()}}} ||
+|| 3 || Најуспешен студент по програма || Фаза 6, Извештај 3 || {{{rep_top_student_per_major()}}} ||
+|| 4 || Најоптоварен професор по семестар || Фаза 6, Извештај 4 || {{{rep_busiest_professor()}}} ||
+|| 5 || Промена на активноста меѓу семестри || Фаза 6, Извештај 5 || {{{rep_semester_growth()}}} ||
+|| 6 || Досие на '''еден''' студент || Фаза 6, Извештај 2 со аргумент || {{{rep_student_dossier(2500)}}} ||
+|| 7 || Дневник на професор || Апликација (не е од Фаза 6) || — ||
+
+Извештај 2 е мерен '''двапати''': еднаш без аргумент, кога враќа секој студент, и
+еднаш со p_user_id = 2500, кога враќа еден. Истата рутина со два различни
+аргумента покажува дека одлучувачки е '''селективноста''', а не самото барање.
+
+Точните наредби со кои се мерени се во [attachment:queries.sql].
+
+=== Методологија на мерењето
+
+За секој прашалник се снимени планови за извршување '''двапати''':
+
+ 1. врз шема без дополнителни индекси — датотеки {{{before_*.txt}}}
+ 2. по извршување на [attachment:indexes.sql] и {{{ANALYZE}}} — датотеки {{{after_*.txt}}}
+
+Сите четиринаесет излези се приложени кон оваа страна во целост. Подолу е
+даден извадок од секој план — точно оние јазли каде се гледа дали индексот
+се употребува или не.
+
+=== Збирен преглед
+
+|| '''#''' || '''Прашалник''' || '''Пред''' || '''По''' || '''Планот се промени?''' ||
+|| 1 || rep_subject_pass_rate || 146,0 ms || 156,0 ms || '''Не''' - идентичен план ||
+|| 2 || rep_student_dossier() || 188,5 ms || 179,4 ms || '''Не''' - идентичен план ||
+|| 3 || rep_top_student_per_major || 107,3 ms || 94,0 ms || '''Не''' - идентичен план ||
+|| 4 || rep_busiest_professor || 121,9 ms || 124,6 ms || '''Не''' - идентичен план ||
+|| 5 || rep_semester_growth || 124,0 ms || 138,1 ms || '''Не''' - идентичен план ||
+|| 6 || rep_student_dossier(2500) || 2,19 ms || '''1,07 ms''' || '''Да''' - Seq Scan ⇒ Index Scan ||
+|| 7 || Дневник на професор || 16,3 ms || '''7,6 ms''' || '''Да''' - Seq Scan ⇒ Bitmap Index Scan ||
+
+Последната колона е докажана подолу за секој прашалник посебно.
+
+=== Прашалник 1 - проодност по предмет (Фаза 6, Извештај 1)
+
+{{{#!sql
+EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_subject_pass_rate();
+}}}
+
+'''План ПРЕД индексите''' ([attachment:before_rep_subject_pass_rate.txt]):
+
+{{{
+->  Seq Scan on semesters_subjects ss  (rows=80000) (actual time=0.007..3.658 rows=80000.00)
+->  Seq Scan on passed_subjects ps     (rows=56000) (actual time=0.011..4.648 rows=56000.00)
+->  Seq Scan on enrolled_semesters es  (rows=16000) (actual time=0.011..2.191 rows=16000.00)
+Execution Time: 146.018 ms
+}}}
+
+'''План ПО индексите''' ([attachment:after_rep_subject_pass_rate.txt]):
+
+{{{
+->  Seq Scan on semesters_subjects ss  (rows=80000) (actual time=0.007..4.131 rows=80000.00)
+->  Seq Scan on passed_subjects ps     (rows=56000) (actual time=0.014..5.114 rows=56000.00)
+->  Seq Scan on enrolled_semesters es  (rows=16000) (actual time=0.011..2.333 rows=16000.00)
+Execution Time: 156.015 ms
+}}}
+
+'''Заклучок:''' планот е идентичен — во него '''не се појавува ниту еден''' од
+новите индекси. Барањето агрегира врз целите табели, па планерот точно
+заклучува дека секвенцијалното читање е поевтино.
+
+=== Прашалник 2 - досие на сите студенти (Фаза 6, Извештај 2)
+
+{{{#!sql
+EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_student_dossier();
+}}}
+
+'''План ПРЕД индексите''' ([attachment:before_rep_student_dossier.txt]):
+
+{{{
+->  Seq Scan on users u                (rows=4000)
+->  Seq Scan on semesters_subjects ss  (rows=80000) (actual time=0.032..5.444)
+->  Seq Scan on passed_subjects ps_1   (rows=56000) (actual time=0.042..6.293)
+->  Seq Scan on payment p              (rows=16000) (actual time=0.028..0.794)
+->  Seq Scan on user_documents ud      (rows=4000)
+Execution Time: 188.547 ms
+}}}
+
+'''План ПО индексите''' ([attachment:after_rep_student_dossier.txt]):
+
+{{{
+->  Seq Scan on users u                (rows=4000)
+->  Seq Scan on semesters_subjects ss  (rows=80000) (actual time=0.013..4.720)
+->  Seq Scan on passed_subjects ps_1   (rows=56000) (actual time=0.012..5.112)
+->  Seq Scan on payment p              (rows=16000) (actual time=0.028..0.788)
+->  Seq Scan on user_documents ud      (rows=4000)
+Execution Time: 179.370 ms
+}}}
+
+'''Заклучок:''' идентичен план. Извештајот бара досие за сите 4.000 студенти, па
+{{{ix_payment_enrollment}}} не помага — сите 16.000 плаќања и онака се потребни.
+Спореди со Прашалник 6, каде истата рутина го користи тој индекс.
+
+=== Прашалник 3 - најуспешен студент по програма (Фаза 6, Извештај 3)
+
+{{{#!sql
+EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_top_student_per_major();
+}}}
+
+'''План ПРЕД''' ([attachment:before_rep_top_student_per_major.txt]) и
+'''ПО''' ([attachment:after_rep_top_student_per_major.txt]) — идентични јазли:
+
+{{{
+->  Seq Scan on semesters_subjects ss       (rows=80000)
+->  Seq Scan on passed_subjects ps          (rows=56000)
+->  Seq Scan on enrolled_semesters es       (rows=16000)
+->  Index Scan using users_pkey on users u  (loops=6)
+
+Пред: Execution Time: 107.303 ms
+По:   Execution Time:  93.983 ms
+}}}
+
+'''Заклучок:''' единствениот Index Scan е по {{{users_pkey}}}, кој постоеше и пред
+измената. Ниту еден нов индекс не е употребен.
+
+=== Прашалник 4 - најоптоварен професор (Фаза 6, Извештај 4)
+
+{{{#!sql
+EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_busiest_professor();
+}}}
+
+'''План ПРЕД''' ([attachment:before_rep_busiest_professor.txt]) и
+'''ПО''' ([attachment:after_rep_busiest_professor.txt]):
+
+{{{
+->  Seq Scan on semesters_subjects ss  (rows=80000)    <- исто пред и по
+->  Seq Scan on passed_subjects ps     (rows=56000)
+->  Seq Scan on enrolled_semesters es  (rows=16000)
+
+Пред: Execution Time: 121.914 ms
+По:   Execution Time: 124.648 ms
+}}}
+
+'''Заклучок:''' иако постои {{{ix_semesters_subjects_professor}}}, планерот не го
+зема, бидејќи извештајот групира по '''сите''' професори, не бара еден. Истиот
+индекс е одлучувачки кај Прашалник 7, каде има услов за еден професор.
+
+=== Прашалник 5 - промена на активноста (Фаза 6, Извештај 5)
+
+{{{#!sql
+EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_semester_growth();
+}}}
+
+'''План ПРЕД''' ([attachment:before_rep_semester_growth.txt]) и
+'''ПО''' ([attachment:after_rep_semester_growth.txt]):
+
+{{{
+->  Seq Scan on semesters_subjects ss  (rows=80000)    <- исто пред и по
+->  Seq Scan on passed_subjects ps     (rows=56000)
+->  Seq Scan on enrolled_semesters es  (rows=16000)
+->  Seq Scan on active_semesters a     (rows=20)
+
+Пред: Execution Time: 124.040 ms
+По:   Execution Time: 138.077 ms
+}}}
+
+'''Заклучок:''' {{{ix_enrolled_semesters_semester}}} не е употребен, иако извештајот
+групира по semester_id — пак затоа што ги чита сите 20 семестри.
+
+=== Прашалник 6 - досие на еден студент (Фаза 6, Извештај 2 со аргумент)
+
+{{{#!sql
+EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM rep_student_dossier(2500);
+}}}
+
+'''План ПРЕД индексот''' ([attachment:before_dossier_one.txt]) — целата табела
+payment се чита:
+
+{{{
+->  Seq Scan on payment p  (cost=0.00..247.00 rows=16000 width=8)
+                           (actual time=0.011..0.624 rows=16000.00 loops=1)
+Execution Time: 2.190 ms
+}}}
+
+'''План ПО индексот''' ([attachment:after_dossier_one.txt]) — истата табела се чита
+преку индекс:
+
+{{{
+->  Index Scan using ix_payment_enrollment on payment p
+                           (cost=0.29..8.30 rows=1 width=8)
+                           (actual time=0.090..0.090 rows=1.00 loops=4)
+      Index Cond: (enrollment_id = es_3.id)
+Execution Time: 1.067 ms
+}}}
+
+'''Заклучок:''' индексот {{{ix_payment_enrollment}}} е '''докажано употребен''' —
+неговото име стои во јазлот. Наместо 16.000 реда се читаат 4 (по еден за секој
+семестар на студентот), а времето паѓа од 2,19 на 1,07 ms.
+
+Ова е најважната споредба во целата анализа: Прашалници 2 и 6 се '''истата
+рутина од Фаза 6'''. Разликата е само во аргументот, а тоа е доволно планот да се
+промени од Seq Scan во Index Scan.
+
+=== Прашалник 7 - дневник на професор (барање на апликацијата)
+
+{{{#!sql
+SELECT u.surname, u.name, s.name AS subject, ss.signature, ps.grade
+FROM semesters_subjects ss
+JOIN enrolled_semesters es ON es.id = ss.enrolled_semesters_id
+JOIN users u              ON u.id  = es.user_id
+JOIN subjects s           ON s.id  = ss.subjects_id
+LEFT JOIN passed_subjects ps ON ps.enrolled_id = ss.id
+WHERE ss.professor_id = 47
+ORDER BY s.name, u.surname;
+}}}
+
+'''План ПРЕД индексот''' ([attachment:before_gradebook.txt]):
+
+{{{
+->  Seq Scan on semesters_subjects ss  (cost=0.00..1510.00 rows=664 width=13)
+                                       (actual time=0.038..6.061 rows=684.00 loops=1)
+      Filter: (professor_id = 47)
+      Rows Removed by Filter: 79316
+Execution Time: 16.332 ms
+}}}
+
+'''План ПО индексот''' ([attachment:after_gradebook.txt]):
+
+{{{
+->  Bitmap Heap Scan on semesters_subjects ss  (cost=9.45..555.06 rows=666 width=13)
+                                               (actual time=0.108..1.480 rows=684.00 loops=1)
+      Recheck Cond: (professor_id = 47)
+      ->  Bitmap Index Scan on ix_semesters_subjects_professor
+                                               (cost=0.00..9.29 rows=666 width=0)
+                                               (actual time=0.070..0.070 rows=684.00 loops=1)
+            Index Cond: (professor_id = 47)
+Execution Time: 7.615 ms
+}}}
+
+'''Заклучок:''' индексот {{{ix_semesters_subjects_professor}}} е '''докажано
+употребен'''. Пред индексот базата читаше 80.000 реда и отфрлаше 79.316
+({{{Rows Removed by Filter}}}); по индексот оди директно до 684-те реда. Цената
+паѓа од 1510 на 555, а времето од 16,3 на 7,6 ms.
+
+=== Зошто петте агрегатни извештаи не станаа побрзи
+
+Ова не е неуспех на индексите, туку очекувано однесување. Прашалници 1-5 се
+агрегатни - тие читаат '''сѐ''' од semesters_subjects, passed_subjects и
 enrolled_semesters, за да пресметаат проодност, просек или ранг. Кога барањето и
 онака мора да ги прочита сите редови, секвенцијалното читање е побрзо од
@@ -85 +320 @@
 скока по индексот и потоа по табелата.
 
-Дека разликите во табелата се шум, а не забавување, се гледа од повторените
+Дека разликите во времињата се шум, а не забавување, се гледа од повторените
 мерења на истото барање со исти индекси:
 
@@ -96 +331 @@
 
 Распонот од околу 28 ms меѓу четири последователни извршувања е поголем од сите
-разлики „пред/по“ во табелата. Значи индексите за овие барања не менуваат ништо -
-ниту на добро, ниту на лошо.
-
-=== Каде индексите навистина помогнаа
-
-Кај двете '''селективни''' барања - оние кои бараат податоци за еден студент или
-за еден професор - разликата е јасна и се гледа во самиот план.
-
-'''Досие на еден студент''' (2,19 ms → 1,07 ms). Планот пред индексите содржеше:
-
-{{{
-Seq Scan on payment
-}}}
-
-а по индексите:
-
-{{{
-Index Scan using ix_payment_enrollment
-}}}
-
-'''Дневник на професор''' (16,3 ms → 7,6 ms). Пред индексот:
-
-{{{
-Seq Scan on semesters_subjects   (80.000 реда прочитани, филтрирани на ~660)
-}}}
-
-По индексот:
-
-{{{
-Bitmap Heap Scan on semesters_subjects
-  -> Bitmap Index Scan using ix_semesters_subjects_professor
-}}}
-
-Наместо да ги прочита сите 80.000 реда и да ги отфрли 99%, базата сега оди
-директно до редовите на тој професор.
+разлики „пред/по“ во збирната табела. Затоа единствениот сигурен доказ дали
+индексот се употребува е '''самиот план''', а не измереното време — и токму
+затоа погоре е даден планот за секој прашалник посебно.
 
 === Дали индексите навистина се употребени
@@ -260 +463 @@
 отворањето е поскапо од вообичаеното.
 
-
 == Историјат
 
@@ -268 +470 @@
  документирање на безбедносните мерки во апликацијата и во базата.
 
+ '''Верзија 2''' - По забелешки: експлицитно е наведено дека шест од
+ седумте анализирани прашалници се извештаите од Фаза 6, со име на рутината
+ во reports.sql за секој. За секој прашалник е документиран планот за
+ извршување пред и по додавањето на индексите, со извадок од јазлите во
+ кои се гледа дали индексот се употребува. Додадена е скриптата queries.sql
+ со точните мерени наредби.
+
 == Статус