== Индекси и оптимизација на прашалници == === Опис и методологија === Во оваа фаза се анализираат и оптимизираат 9 погледи (views) во системот. За секој поглед се мери времето на извршување, се чита планот на извршување, се додаваат индекси само таму каде што планот покажува конкретен `Seq Scan` (full scan) кој го оправдува, и потоа повторно се мери. Сите мерења се извршени со `EXPLAIN (ANALYZE, BUFFERS)`. Најголемите табели содржат по ~10.15 милиони редови (`sm_student_ocena_zapis`, `sm_student_upisan_kurs`, `fin_finansiska_obvrska`, `fin_naplati`) и ~9.14 милиони (`sm_student_ispit_prijava`). Времињата за `insert`/`update` се мерени врз 1000 редови, во трансакција со `ROLLBACK`. За секој поглед е дадено: 1. примарен филтер и случај на употреба 2. дефиниција на погледот 3. време и план пред индексирање (+ цена на `insert`/`update`) 4. оптимизација (индекси и/или преуредување на прашалникот) 5. време и план по индексирање (+ цена на `insert`/`update`) === Креирани индекси (преглед) === Вкупно се креирани '''10 индекси''' кои ги покриваат сите бавни погледи. Ниту еден индекс не е додаден без конкретен `Seq Scan` во планот кој го оправдува. {{{ -- Студентски синџир (View 1 и View 3) CREATE INDEX idx_studenti_indeks ON sm_studenti (indeks); CREATE INDEX idx_studenti_smerovi_student ON sm_studenti_smerovi (student_id); CREATE INDEX idx_semestar_upis_smer ON sm_student_semestar_upis (studenti_smerovi_id); CREATE INDEX idx_upisan_kurs_semestar_upis ON sm_student_upisan_kurs (student_semestar_upis_id); CREATE INDEX idx_ocena_zapis_upisan_kurs ON sm_student_ocena_zapis (student_upisan_kurs_id); CREATE INDEX idx_ispit_prijava_upisan_kurs ON sm_student_ispit_prijava (student_upisan_kurs_id); -- Финансии (View 4 и View 5) CREATE INDEX idx_korisnici_username ON korisnici (username); CREATE INDEX idx_fin_obvrska_user ON fin_finansiska_obvrska (user_id); CREATE INDEX idx_fin_naplati_user ON fin_naplati (user_id); -- Квалификации (View 9) CREATE INDEX idx_lice_kvalifikacija_lice ON vr_lice_kvalifikacija (lice_id); }}} Бидејќи поврзувањата меѓу табелите се прават преку колоните `*_id` (надворешни клучеви), индексите се поставуваат токму на тие колони, како и на колоните што најчесто се користат за филтрирање (`indeks`, `username`). ---- == View 1: vw_studentski_oceni (просек по студент) == '''Примарен филтер:''' `indeks` на студентот (се користи и според име и презиме). '''Случај на употреба:''' пресметка на кредитно-пондериран просек, освоени кредити и број на положени/паднати испити за еден студент, по систем на оценување. Ова е еден од најчесто користените прегледи (студентски сервиси, уверенија), па перформансите се критични. === Дефиниција === {{{ CREATE OR REPLACE VIEW public.vw_studentski_oceni AS SELECT s.id AS student_id, s.indeks, l.ime AS student_ime, l.prezime AS student_prezime, so.ime_sistem AS sistem_ocenuvanje, round(sum(sov.ocena_vrednost::numeric * ak.krediti::numeric) FILTER (WHERE sov.polozitelna) / NULLIF(sum(ak.krediti) FILTER (WHERE sov.polozitelna), 0)::numeric, 2) AS prosek, COALESCE(sum(ak.krediti) FILTER (WHERE sov.polozitelna), 0::bigint) AS osvoeni_krediti, count(*) FILTER (WHERE sov.polozitelna) AS polozeni, count(*) FILTER (WHERE NOT sov.polozitelna) AS padnati FROM sm_student_ocena_zapis soz JOIN sm_student_upisan_kurs suk ON soz.student_upisan_kurs_id = suk.id JOIN sm_student_semestar_upis ssu ON suk.student_semestar_upis_id = ssu.id JOIN sm_studenti_smerovi ss ON ssu.studenti_smerovi_id = ss.id JOIN sm_studenti s ON ss.student_id = s.id JOIN korisnici k ON s.user_id = k.id JOIN lice l ON k.lice_id = l.id JOIN sm_kurs_realizacija kr ON suk.kurs_realizacija_id = kr.id JOIN sm_akreditacija ak ON kr.akreditacija_id = ak.id JOIN sm_sistem_ocenuvanje_vrednost sov ON soz.sistem_ocenuvanje_vrednost_id = sov.id JOIN sm_sistem_ocenuvanje so ON sov.sistem_ocenuvanje_id = so.id GROUP BY s.id, s.indeks, l.ime, l.prezime, so.ime_sistem; }}} Тест прашалник: {{{ EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM vw_studentski_oceni WHERE indeks = 'S000001'; }}} === Пред индексирање — 67s 937ms === Ова не е прифатливо време за апликацијата, па затоа пристапуваме кон индексирање. {{{ GroupAggregate (cost=236482.53..236501.20 rows=110 width=599) (actual time=67897.311..67897.744 rows=1 loops=1) Group Key: s.id, l.ime, l.prezime, so.ime_sistem -> Gather Merge (cost=236482.53..236495.70 rows=110 width=552) (actual time=67897.107..67897.584 rows=110 loops=1) -> Sort (cost=235482.47..235482.54 rows=28 width=552) (actual time=67825.396..67825.414 rows=22 loops=5) Sort Key: s.id, l.ime, l.prezime, so.ime_sistem -> Hash Join (cost=115978.32..235481.80 rows=28 width=552) (actual time=32071.406..67825.151 rows=22 loops=5) Hash Cond: (sov.sistem_ocenuvanje_id = so.id) -> Nested Loop (cost=115965.17..235468.57 rows=28 width=40) (actual time=32017.993..67771.723 rows=22 loops=5) -> Nested Loop (cost=115965.03..235464.02 rows=28 width=35) (actual time=32017.828..67771.510 rows=22 loops=5) -> Nested Loop (cost=115964.75..235454.77 rows=28 width=35) (actual time=31959.675..67701.486 rows=22 loops=5) -> Parallel Hash Join (cost=115964.47..235446.51 rows=28 width=35) (actual time=31959.584..67701.266 rows=22 loops=5) Hash Cond: (soz.student_upisan_kurs_id = suk.id) -> Parallel Seq Scan on sm_student_ocena_zapis soz (cost=0.00..109965.70 rows=2537670 width=8) (actual time=39.116..61416.903 rows=2030116 loops=5) -> Parallel Hash (cost=115964.12..115964.12 rows=28 width=35) (actual time=6041.186..6041.197 rows=22 loops=5) -> Parallel Hash Join (cost=16417.06..115964.12 rows=28 width=35) (actual time=6021.721..6040.896 rows=22 loops=5) Hash Cond: (suk.student_semestar_upis_id = ssu.id) -> Parallel Seq Scan on sm_student_upisan_kurs suk (cost=0.00..90030.70 rows=2537670 width=12) (actual time=4.530..812.513 rows=2030116 loops=5) -> Parallel Hash (cost=16417.01..16417.01 rows=4 width=31) (actual time=5001.315..5001.323 rows=2 loops=5) -> Hash Join (cost=3503.21..16417.01 rows=4 width=31) (actual time=4878.870..5001.252 rows=2 loops=5) Hash Cond: (ssu.studenti_smerovi_id = ss.id) -> Parallel Seq Scan on sm_student_semestar_upis ssu (cost=0.00..11471.92 rows=384492 width=8) (actual time=45.903..3759.039 rows=184556 loops=5) -> Hash (cost=3503.19..3503.19 rows=1 width=31) (actual time=1221.057..1221.064 rows=1 loops=5) -> Nested Loop (cost=1742.07..3503.19 rows=1 width=31) (actual time=1001.650..1221.051 rows=1 loops=5) -> Nested Loop (cost=1741.78..3502.82 rows=1 width=20) (actual time=1001.604..1221.003 rows=1 loops=5) -> Hash Join (cost=1741.49..3494.51 rows=1 width=20) (actual time=1001.534..1220.931 rows=1 loops=5) Hash Cond: (ss.student_id = s.id) -> Seq Scan on sm_studenti_smerovi ss (cost=0.00..1510.78 rows=92278 width=8) (actual time=3.572..1200.401 rows=92278 loops=5) -> Hash (cost=1741.48..1741.48 rows=1 width=16) (actual time=9.823..9.824 rows=1 loops=5) -> Seq Scan on sm_studenti s (cost=0.00..1741.48 rows=1 width=16) (actual time=0.061..9.814 rows=1 loops=5) Filter: ((indeks)::text = 'S000001'::text) Rows Removed by Filter: 92277 -> Index Scan using korisnici_pkey on korisnici k (cost=0.29..8.31 rows=1 width=8) (actual time=0.047..0.048 rows=1 loops=5) Index Cond: (id = s.user_id) -> Index Scan using lice_pkey on lice l (cost=0.29..0.37 rows=1 width=19) (actual time=0.027..0.027 rows=1 loops=5) Index Cond: (id = k.lice_id) -> Index Scan using sm_kurs_realizacija_pkey on sm_kurs_realizacija kr (cost=0.28..0.30 rows=1 width=8) (actual time=0.008..0.008 rows=1 loops=110) Index Cond: (id = suk.kurs_realizacija_id) -> Index Scan using sm_akreditacija_pkey on sm_akreditacija ak (cost=0.28..0.33 rows=1 width=8) (actual time=3.181..3.181 rows=1 loops=110) Index Cond: (id = kr.akreditacija_id) -> Index Scan using sm_sistem_ocenuvanje_vrednost_pkey on sm_sistem_ocenuvanje_vrednost sov (cost=0.14..0.16 rows=1 width=13) (actual time=0.008..0.008 rows=1 loops=110) Index Cond: (id = soz.sistem_ocenuvanje_vrednost_id) -> Hash (cost=11.40..11.40 rows=140 width=520) (actual time=53.193..53.194 rows=7 loops=5) -> Seq Scan on sm_sistem_ocenuvanje so (cost=0.00..11.40 rows=140 width=520) (actual time=53.156..53.159 rows=7 loops=5) Execution Time: 67936.866 ms }}} '''Цена на insert/update пред индексирање''' (`sm_student_ocena_zapis`): insert '''1s 410ms''', update '''25ms'''. === Оптимизација === Најбавните операции се full scan на `sm_student_ocena_zapis` (10.15M редови), `sm_student_upisan_kurs`, `sm_student_semestar_upis` и `sm_studenti_smerovi`. Се додаваат индекси врз надворешните клучеви од студентскиот синџир: {{{ CREATE INDEX idx_studenti_indeks ON sm_studenti (indeks); CREATE INDEX idx_studenti_smerovi_student ON sm_studenti_smerovi (student_id); CREATE INDEX idx_semestar_upis_smer ON sm_student_semestar_upis (studenti_smerovi_id); CREATE INDEX idx_upisan_kurs_semestar_upis ON sm_student_upisan_kurs (student_semestar_upis_id); CREATE INDEX idx_ocena_zapis_upisan_kurs ON sm_student_ocena_zapis (student_upisan_kurs_id); }}} === По индексирање — 2.3ms === {{{ HashAggregate (cost=159.59..162.06 rows=110 width=137) (actual time=1.751..1.759 rows=1 loops=1) Group Key: s.id, l.ime, l.prezime, so.ime_sistem -> Hash Join (cost=133.05..156.56 rows=110 width=90) (actual time=1.457..1.631 rows=110 loops=1) Hash Cond: (sov.sistem_ocenuvanje_id = so.id) -> Hash Join (cost=131.89..154.94 rows=110 width=40) (actual time=1.412..1.559 rows=110 loops=1) Hash Cond: (soz.sistem_ocenuvanje_vrednost_id = sov.id) -> Hash Join (cost=129.95..152.67 rows=110 width=35) (actual time=1.343..1.460 rows=110 loops=1) Hash Cond: (ak.id = kr.akreditacija_id) -> Seq Scan on sm_akreditacija ak (cost=0.00..19.00 rows=700 width=8) (actual time=0.018..0.118 rows=700 loops=1) -> Hash (cost=128.57..128.57 rows=110 width=35) (actual time=1.205..1.208 rows=110 loops=1) -> Nested Loop (cost=2.74..128.57 rows=110 width=35) (actual time=0.346..1.149 rows=110 loops=1) -> Nested Loop (cost=2.46..96.12 rows=110 width=35) (actual time=0.321..0.850 rows=110 loops=1) -> Nested Loop (cost=2.03..40.37 rows=110 width=35) (actual time=0.265..0.362 rows=110 loops=1) -> Nested Loop (cost=1.59..26.39 rows=10 width=31) (actual time=0.205..0.216 rows=10 loops=1) -> Nested Loop (cost=1.17..25.31 rows=1 width=31) (actual time=0.151..0.155 rows=1 loops=1) -> Nested Loop (cost=0.88..24.94 rows=1 width=20) (actual time=0.127..0.130 rows=1 loops=1) -> Nested Loop (cost=0.58..16.63 rows=1 width=20) (actual time=0.081..0.083 rows=1 loops=1) -> Index Scan using idx_studenti_indeks on sm_studenti s (cost=0.29..8.31 rows=1 width=16) (actual time=0.049..0.049 rows=1 loops=1) Index Cond: ((indeks)::text = 'S000001'::text) -> Index Scan using idx_studenti_smerovi_student on sm_studenti_smerovi ss (cost=0.29..8.31 rows=1 width=8) (actual time=0.028..0.028 rows=1 loops=1) Index Cond: (student_id = s.id) -> Index Scan using korisnici_pkey on korisnici k (cost=0.29..8.31 rows=1 width=8) (actual time=0.045..0.045 rows=1 loops=1) Index Cond: (id = s.user_id) -> Index Scan using lice_pkey on lice l (cost=0.29..0.37 rows=1 width=19) (actual time=0.022..0.022 rows=1 loops=1) Index Cond: (id = k.lice_id) -> Index Scan using idx_semestar_upis_smer on sm_student_semestar_upis ssu (cost=0.42..0.98 rows=10 width=8) (actual time=0.052..0.057 rows=10 loops=1) Index Cond: (studenti_smerovi_id = ss.id) -> Index Scan using idx_upisan_kurs_semestar_upis on sm_student_upisan_kurs suk (cost=0.43..1.17 rows=23 width=12) (actual time=0.008..0.012 rows=11 loops=10) Index Cond: (student_semestar_upis_id = ssu.id) -> Index Scan using idx_ocena_zapis_upisan_kurs on sm_student_ocena_zapis soz (cost=0.43..0.50 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=110) Index Cond: (student_upisan_kurs_id = suk.id) -> Index Scan using sm_kurs_realizacija_pkey on sm_kurs_realizacija kr (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=110) Index Cond: (id = suk.kurs_realizacija_id) -> Hash (cost=1.42..1.42 rows=42 width=13) (actual time=0.029..0.030 rows=42 loops=1) -> Seq Scan on sm_sistem_ocenuvanje_vrednost sov (cost=0.00..1.42 rows=42 width=13) (actual time=0.007..0.014 rows=42 loops=1) -> Hash (cost=1.07..1.07 rows=7 width=58) (actual time=0.028..0.028 rows=7 loops=1) -> Seq Scan on sm_sistem_ocenuvanje so (cost=0.00..1.07 rows=7 width=58) (actual time=0.010..0.012 rows=7 loops=1) Execution Time: 2.272 ms }}} '''Цена на insert/update по индексирање:''' insert '''3s 411ms''', update '''31ms'''. Зголемувањето на insert е очекувана цена на одржување на индексот врз табела од 10M редови и е прифатливо, бидејќи оценките се запишуваат поединечно, а се читаат постојано. ---- == View 2: vw_aktivni_upisani_kursevi (број на запишани по курс) == '''Примарен филтер:''' `kurs_realizacija_id` (се користи и според академски семестар). '''Случај на употреба:''' планирање на капацитети — колку студенти се запишани на секоја реализација на курс и колку слободни места останале. === Дефиниција === {{{ CREATE OR REPLACE VIEW public.vw_aktivni_upisani_kursevi AS SELECT kr.id AS kurs_realizacija_id, p.ime_predmet, asem.name AS akademski_semestar, kr.kapacitet_studenti, count(suk.id) AS broj_upisani, kr.kapacitet_studenti - count(suk.id) AS slobodni_mesta FROM sm_student_upisan_kurs suk JOIN sm_kurs_realizacija kr ON suk.kurs_realizacija_id = kr.id JOIN sm_akreditacija ak ON kr.akreditacija_id = ak.id JOIN sm_predmet p ON ak.predmet_id = p.id JOIN sm_akademski_semestar asem ON kr.akademski_semestar_id = asem.id WHERE suk.status_slushanje::text = 'UPISAN'::text GROUP BY kr.id, p.ime_predmet, asem.name, kr.kapacitet_studenti; }}} Тест прашалник: `SELECT * FROM vw_aktivni_upisani_kursevi WHERE kurs_realizacija_id = 1;` === Време — 283ms (прифатливо, без индексирање) === {{{ GroupAggregate (cost=103956.30..103977.28 rows=600 width=57) (actual time=241.052..249.661 rows=0 loops=1) Group Key: p.ime_predmet, asem.name -> Sort (cost=103956.30..103959.67 rows=1348 width=45) (actual time=241.050..249.659 rows=0 loops=1) Sort Key: p.ime_predmet, asem.name -> Nested Loop (cost=1008.73..103886.22 rows=1348 width=45) (actual time=240.976..249.584 rows=0 loops=1) -> Nested Loop (cost=8.73..18.90 rows=1 width=41) (actual time=23.697..23.723 rows=1 loops=1) -> Nested Loop (cost=8.58..18.69 rows=1 width=26) (actual time=23.611..23.636 rows=1 loops=1) -> Hash Join (cost=8.31..10.38 rows=1 width=26) (actual time=23.563..23.587 rows=1 loops=1) Hash Cond: (asem.id = kr.akademski_semestar_id) -> Seq Scan on sm_akademski_semestar asem (cost=0.00..1.84 rows=84 width=18) (actual time=0.011..0.019 rows=84 loops=1) -> Hash (cost=8.29..8.29 rows=1 width=16) (actual time=23.506..23.507 rows=1 loops=1) -> Index Scan using sm_kurs_realizacija_pkey on sm_kurs_realizacija kr (cost=0.28..8.29 rows=1 width=16) (actual time=23.482..23.484 rows=1 loops=1) Index Cond: (id = 1) -> Index Scan using sm_akreditacija_pkey on sm_akreditacija ak (cost=0.28..8.29 rows=1 width=8) (actual time=0.030..0.030 rows=1 loops=1) Index Cond: (id = kr.akreditacija_id) -> Index Scan using sm_predmet_pkey on sm_predmet p (cost=0.15..0.20 rows=1 width=23) (actual time=0.065..0.065 rows=1 loops=1) Index Cond: (id = ak.predmet_id) -> Gather (cost=1000.00..103853.84 rows=1348 width=8) (actual time=217.255..225.857 rows=0 loops=1) -> Parallel Seq Scan on sm_student_upisan_kurs suk (cost=0.00..102719.04 rows=337 width=8) (actual time=186.026..186.026 rows=0 loops=5) Filter: ((kurs_realizacija_id = 1) AND ((status_slushanje)::text = 'UPISAN'::text)) Rows Removed by Filter: 2030116 Execution Time: 282.936 ms }}} Нема потреба од дополнително индексирање — агрегацијата по `kurs_realizacija_id` е добро покриена со постојниот план. Времето на `insert`/`update` останува како во View 1 (иста табела `sm_student_upisan_kurs`: insert '''41ms''', update '''41ms'''). ---- == View 3: vw_ispitni_prijavi (испитни пријави) == '''Примарен филтер:''' `indeks` на студент (се користи и според назив на испитна сесија). '''Случај на употреба:''' студент ги прегледува своите пријави или службата проверува пријави во сесија. Перформансите се критични во периодите на пријавување. === Дефиниција (по преуредување) === Дефиницијата ги подредува JOIN-овите така што студентскиот синџир е прв (види оптимизација подолу): {{{ CREATE OR REPLACE VIEW public.vw_ispitni_prijavi AS SELECT sip.id AS prijava_id, ise.naziv AS sesija_naziv, s.indeks, l.ime, l.prezime, p.ime_predmet, sip.datum_prijava, sip.status AS prijava_status FROM sm_studenti s JOIN sm_studenti_smerovi ss ON ss.student_id = s.id JOIN sm_student_semestar_upis ssu ON ssu.studenti_smerovi_id = ss.id JOIN sm_student_upisan_kurs suk ON suk.student_semestar_upis_id = ssu.id JOIN sm_student_ispit_prijava sip ON sip.student_upisan_kurs_id = suk.id JOIN sm_ispitna_sesija ise ON sip.ispitna_sesija_id = ise.id JOIN korisnici k ON s.user_id = k.id JOIN lice l ON k.lice_id = l.id JOIN sm_kurs_realizacija kr ON suk.kurs_realizacija_id = kr.id JOIN sm_akreditacija ak ON kr.akreditacija_id = ak.id JOIN sm_predmet p ON ak.predmet_id = p.id; }}} Тест прашалник: `SELECT * FROM vw_ispitni_prijavi WHERE indeks = 'S000001';` === Пред индексирање — 63s 288ms === Ова не е прифатливо време за апликацијата, па затоа пристапуваме кон индексирање. {{{ Gather (cost=157429.07..374892.89 rows=99 width=79) (actual time=56810.523..63252.822 rows=99 loops=1) -> Nested Loop (cost=156429.07..373882.99 rows=25 width=79) (actual time=54097.559..56810.753 rows=20 loops=5) -> Nested Loop (cost=156428.78..373873.64 rows=25 width=68) (actual time=54097.502..56810.398 rows=20 loops=5) -> Hash Join (cost=156428.47..373856.73 rows=25 width=68) (actual time=54097.407..56810.181 rows=20 loops=5) Hash Cond: (ss.student_id = s.id) -> Hash Join (cost=154686.99..366119.73 rows=2283898 width=60) (actual time=53247.869..56570.720 rows=1827104 loops=5) Hash Cond: (ssu.studenti_smerovi_id = ss.id) -> Hash Join (cost=152022.73..357459.97 rows=2283898 width=60) (actual time=53212.046..55859.387 rows=1827104 loops=5) Hash Cond: (ak.predmet_id = p.id) -> Hash Join (cost=152009.86..351379.27 rows=2283898 width=45) (actual time=53211.772..55457.368 rows=1827104 loops=5) Hash Cond: (kr.akreditacija_id = ak.id) -> Hash Join (cost=151982.11..345319.99 rows=2283898 width=45) (actual time=53153.718..54970.994 rows=1827104 loops=5) Hash Cond: (suk.kurs_realizacija_id = kr.id) -> Hash Join (cost=151931.31..339258.84 rows=2283898 width=45) (actual time=53152.825..54466.972 rows=1827104 loops=5) Hash Cond: (sip.ispitna_sesija_id = ise.id) -> Parallel Hash Join (cost=151922.64..333154.11 rows=2283898 width=32) (actual time=53152.630..54066.789 rows=1827104 loops=5) Hash Cond: (suk.student_semestar_upis_id = ssu.id) -> Parallel Hash Join (cost=134142.57..276650.79 rows=2283898 width=32) (actual time=51277.828..52301.181 rows=1827104 loops=5) Hash Cond: (sip.student_upisan_kurs_id = suk.id) -> Parallel Seq Scan on sm_student_ispit_prijava sip (cost=0.00..92895.99 rows=2283898 width=28) (actual time=21.258..49123.118 rows=1827104 loops=5) -> Parallel Hash (cost=90030.70..90030.70 rows=2537670 width=12) (actual time=916.648..916.649 rows=2030116 loops=5) -> Parallel Seq Scan on sm_student_upisan_kurs suk (cost=0.00..90030.70 rows=2537670 width=12) (actual time=0.085..325.262 rows=2030116 loops=5) -> Parallel Hash (cost=11471.92..11471.92 rows=384492 width=8) (actual time=82.353..82.354 rows=184556 loops=5) -> Parallel Seq Scan on sm_student_semestar_upis ssu (cost=0.00..11471.92 rows=384492 width=8) (actual time=0.024..32.022 rows=184556 loops=5) -> Hash (cost=5.52..5.52 rows=252 width=21) (actual time=0.164..0.165 rows=252 loops=5) -> Seq Scan on sm_ispitna_sesija ise (cost=0.00..5.52 rows=252 width=21) (actual time=0.044..0.083 rows=252 loops=5) -> Hash (cost=29.80..29.80 rows=1680 width=8) (actual time=0.855..0.855 rows=1680 loops=5) -> Seq Scan on sm_kurs_realizacija kr (cost=0.00..29.80 rows=1680 width=8) (actual time=0.049..0.464 rows=1680 loops=5) -> Hash (cost=19.00..19.00 rows=700 width=8) (actual time=58.026..58.027 rows=700 loops=5) -> Seq Scan on sm_akreditacija ak (cost=0.00..19.00 rows=700 width=8) (actual time=25.009..57.837 rows=700 loops=5) -> Hash (cost=8.50..8.50 rows=350 width=23) (actual time=0.245..0.246 rows=350 loops=5) -> Seq Scan on sm_predmet p (cost=0.00..8.50 rows=350 width=23) (actual time=0.064..0.141 rows=350 loops=5) -> Hash (cost=1510.78..1510.78 rows=92278 width=8) (actual time=34.949..34.949 rows=92278 loops=5) -> Seq Scan on sm_studenti_smerovi ss (cost=0.00..1510.78 rows=92278 width=8) (actual time=0.055..12.916 rows=92278 loops=5) -> Hash (cost=1741.48..1741.48 rows=1 width=16) (actual time=54.039..54.039 rows=1 loops=5) -> Seq Scan on sm_studenti s (cost=0.00..1741.48 rows=1 width=16) (actual time=44.536..54.023 rows=1 loops=5) Filter: ((indeks)::text = 'S000001'::text) Rows Removed by Filter: 92277 -> Index Scan using korisnici_pkey on korisnici k (cost=0.29..8.31 rows=1 width=8) (actual time=0.063..0.063 rows=1 loops=5) Index Cond: (id = s.user_id) -> Index Scan using lice_pkey on lice l (cost=0.29..0.37 rows=1 width=19) (actual time=0.013..0.013 rows=1 loops=99) Index Cond: (id = k.lice_id) Execution Time: 63288.171 ms }}} '''Цена на insert/update пред индексирање:''' insert '''64ms''', update '''26ms'''. === Оптимизација (индекс + преуредување на прашалникот) === Најбавните операции се full scan на `sm_student_ispit_prijava` (9.14M редови) и на студентскиот синџир. Покрај индексите од View 1, се додава: {{{ CREATE INDEX idx_ispit_prijava_upisan_kurs ON sm_student_ispit_prijava (student_upisan_kurs_id); }}} По само индексирањето времето изнесуваше '''7s 755ms''' — подобрено, но сè уште неприфатливо. Анализата покажа дека погледот има '''11 JOIN-а''', што го надминува `join_collapse_limit = 8` на PostgreSQL, па оптимизаторот не можеше да го разгледа редоследот кој тргнува од студентот. '''Затоа го преуредивме прашалникот''' — JOIN-овите во дефиницијата се подредени така што студентскиот синџир е прв (види дефиниција погоре). === По индексирање + преуредување — 2.2ms === {{{ Hash Join (cost=147.62..170.49 rows=99 width=79) (actual time=1.659..1.802 rows=99 loops=1) Hash Cond: (ak.predmet_id = p.id) -> Hash Join (cost=134.74..157.36 rows=99 width=64) (actual time=1.432..1.551 rows=99 loops=1) Hash Cond: (ak.id = kr.akreditacija_id) -> Seq Scan on sm_akreditacija ak (cost=0.00..19.00 rows=700 width=8) (actual time=0.012..0.126 rows=700 loops=1) -> Hash (cost=133.51..133.51 rows=99 width=64) (actual time=1.286..1.291 rows=99 loops=1) -> Nested Loop (cost=11.41..133.51 rows=99 width=64) (actual time=0.519..1.228 rows=99 loops=1) -> Hash Join (cost=11.13..104.30 rows=99 width=64) (actual time=0.482..0.938 rows=99 loops=1) Hash Cond: (sip.ispitna_sesija_id = ise.id) -> Nested Loop (cost=2.46..95.37 rows=99 width=51) (actual time=0.309..0.732 rows=99 loops=1) -> Nested Loop (cost=2.03..40.37 rows=110 width=31) (actual time=0.269..0.375 rows=110 loops=1) -> Nested Loop (cost=1.59..26.39 rows=10 width=27) (actual time=0.242..0.253 rows=10 loops=1) -> Nested Loop (cost=1.17..25.31 rows=1 width=27) (actual time=0.206..0.210 rows=1 loops=1) -> Nested Loop (cost=0.88..24.94 rows=1 width=16) (actual time=0.176..0.179 rows=1 loops=1) -> Nested Loop (cost=0.58..16.63 rows=1 width=16) (actual time=0.141..0.144 rows=1 loops=1) -> Index Scan using idx_studenti_indeks on sm_studenti s (cost=0.29..8.31 rows=1 width=16) (actual time=0.101..0.101 rows=1 loops=1) Index Cond: ((indeks)::text = 'S000001'::text) -> Index Scan using idx_studenti_smerovi_student on sm_studenti_smerovi ss (cost=0.29..8.31 rows=1 width=8) (actual time=0.035..0.036 rows=1 loops=1) Index Cond: (student_id = s.id) -> Index Scan using korisnici_pkey on korisnici k (cost=0.29..8.31 rows=1 width=8) (actual time=0.033..0.033 rows=1 loops=1) Index Cond: (id = s.user_id) -> Index Scan using lice_pkey on lice l (cost=0.29..0.37 rows=1 width=19) (actual time=0.029..0.029 rows=1 loops=1) Index Cond: (id = k.lice_id) -> Index Scan using idx_semestar_upis_smer on sm_student_semestar_upis ssu (cost=0.42..0.98 rows=10 width=8) (actual time=0.035..0.040 rows=10 loops=1) Index Cond: (studenti_smerovi_id = ss.id) -> Index Scan using idx_upisan_kurs_semestar_upis on sm_student_upisan_kurs suk (cost=0.43..1.17 rows=23 width=12) (actual time=0.005..0.010 rows=11 loops=10) Index Cond: (student_semestar_upis_id = ssu.id) -> Index Scan using idx_ispit_prijava_upisan_kurs on sm_student_ispit_prijava sip (cost=0.43..0.49 rows=1 width=28) (actual time=0.003..0.003 rows=1 loops=110) Index Cond: (student_upisan_kurs_id = suk.id) -> Hash (cost=5.52..5.52 rows=252 width=21) (actual time=0.135..0.136 rows=252 loops=1) -> Seq Scan on sm_ispitna_sesija ise (cost=0.00..5.52 rows=252 width=21) (actual time=0.017..0.058 rows=252 loops=1) -> Index Scan using sm_kurs_realizacija_pkey on sm_kurs_realizacija kr (cost=0.28..0.30 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=99) Index Cond: (id = suk.kurs_realizacija_id) -> Hash (cost=8.50..8.50 rows=350 width=23) (actual time=0.197..0.197 rows=350 loops=1) -> Seq Scan on sm_predmet p (cost=0.00..8.50 rows=350 width=23) (actual time=0.021..0.089 rows=350 loops=1) Execution Time: 2.223 ms }}} '''Цена на insert/update по индексирање:''' insert '''139ms''', update '''33ms'''. ---- == View 4: vw_otvoreni_finansiski_obvrski (отворен долг по корисник) == '''Примарен филтер:''' `username` на корисникот (се користи и според валута). '''Случај на употреба:''' наплата и опомени — вкупен отворен долг, број на обврски и најстаро доспевање по корисник и валута. === Дефиниција === {{{ CREATE OR REPLACE VIEW public.vw_otvoreni_finansiski_obvrski AS SELECT k.id AS user_id, k.username, l.ime, l.prezime, vv.iso_code AS valuta, sum(fo.otvorena_suma) AS vkupno_otvoreno, sum(fo.originalna_suma) AS vkupno_originalno, count(fo.id) AS broj_obvrski, min(fo.datum_dospevanje) AS najstaro_dospevanje FROM fin_finansiska_obvrska fo JOIN korisnici k ON fo.user_id = k.id JOIN lice l ON k.lice_id = l.id JOIN fin_vid_valuta vv ON fo.vid_valuta_id = vv.id WHERE fo.otvorena_suma > 0::numeric GROUP BY k.id, k.username, l.ime, l.prezime, vv.iso_code; }}} Тест прашалник: `SELECT * FROM vw_otvoreni_finansiski_obvrski WHERE username = 'korisnik_2_435';` === Пред индексирање — 104s 538ms === {{{ GroupAggregate (cost=26571.17..144178.69 rows=6 width=128) (actual time=104480.425..104486.536 rows=1 loops=1) Group Key: vv.iso_code, k.id, l.ime, l.prezime -> Incremental Sort (cost=26571.17..144178.48 rows=6 width=96) (actual time=104480.334..104486.447 rows=26 loops=1) Sort Key: vv.iso_code, k.id, l.ime, l.prezime Presorted Key: vv.iso_code -> Nested Loop (cost=3049.75..144178.21 rows=6 width=96) (actual time=104390.350..104486.351 rows=26 loops=1) Join Filter: (vv.id = fo.vid_valuta_id) -> Index Scan using fin_vid_valuta_iso_code_key on fin_vid_valuta vv (cost=0.14..49.19 rows=70 width=20) (actual time=7.023..7.029 rows=1 loops=1) -> Materialize (cost=3049.61..144122.74 rows=6 width=84) (actual time=104383.260..104479.245 rows=26 loops=1) -> Gather (cost=3049.61..144122.71 rows=6 width=84) (actual time=104383.249..104479.219 rows=26 loops=1) -> Nested Loop (cost=2049.61..143122.11 rows=2 width=84) (actual time=100742.280..104367.734 rows=5 loops=5) -> Parallel Hash Join (cost=2049.31..143109.60 rows=2 width=73) (actual time=100742.261..104367.709 rows=5 loops=5) Hash Cond: (fo.user_id = k.id) -> Parallel Seq Scan on fin_finansiska_obvrska fo (cost=0.00..140675.85 rows=146449 width=52) (actual time=17.366..104296.254 rows=406674 loops=5) Filter: (otvorena_suma > '0'::numeric) Rows Removed by Filter: 1623442 -> Parallel Hash (cost=2049.29..2049.29 rows=1 width=25) (actual time=27.980..27.981 rows=0 loops=5) -> Parallel Seq Scan on korisnici k (cost=0.00..2049.29 rows=1 width=25) (actual time=22.357..24.874 rows=0 loops=5) Filter: ((username)::text = 'korisnik_2_435'::text) Rows Removed by Filter: 20000 -> Memoize (cost=0.30..8.32 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=26) Cache Key: k.lice_id -> Index Scan using lice_pkey on lice l (cost=0.29..8.31 rows=1 width=19) (actual time=0.059..0.059 rows=1 loops=1) Index Cond: (id = k.lice_id) Execution Time: 104537.676 ms }}} '''Цена на insert/update пред индексирање:''' insert '''336ms''', update '''31ms'''. === Оптимизација === Најбавните операции се full scan на `fin_finansiska_obvrska` (10.15M редови) и на `korisnici`. Се додаваат индекси: {{{ CREATE INDEX idx_korisnici_username ON korisnici (username); CREATE INDEX idx_fin_obvrska_user ON fin_finansiska_obvrska (user_id); }}} === По индексирање — 100ms === {{{ GroupAggregate (cost=1168.78..1169.53 rows=20 width=116) (actual time=99.968..99.970 rows=1 loops=1) Group Key: k.id, l.ime, l.prezime, vv.iso_code -> Sort (cost=1168.78..1168.83 rows=20 width=56) (actual time=99.931..99.935 rows=26 loops=1) Sort Key: k.id, l.ime, l.prezime, vv.iso_code -> Nested Loop (cost=7.37..1168.35 rows=20 width=56) (actual time=99.796..99.845 rows=26 loops=1) Join Filter: (vv.id = fo.vid_valuta_id) -> Seq Scan on fin_vid_valuta vv (cost=0.00..1.01 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1) -> Nested Loop (cost=7.37..1167.09 rows=20 width=56) (actual time=99.777..99.821 rows=26 loops=1) -> Nested Loop (cost=0.71..16.75 rows=1 width=36) (actual time=99.691..99.694 rows=1 loops=1) -> Index Scan using idx_korisnici_username on korisnici k (cost=0.42..8.44 rows=1 width=25) (actual time=99.662..99.664 rows=1 loops=1) Index Cond: ((username)::text = 'korisnik_2_435'::text) -> Index Scan using lice_pkey on lice l (cost=0.29..8.31 rows=1 width=19) (actual time=0.024..0.024 rows=1 loops=1) Index Cond: (id = k.lice_id) -> Bitmap Heap Scan on fin_finansiska_obvrska fo (cost=6.66..1149.75 rows=59 width=24) (actual time=0.083..0.120 rows=26 loops=1) Recheck Cond: (user_id = k.id) Filter: (otvorena_suma > '0'::numeric) Rows Removed by Filter: 84 -> Bitmap Index Scan on idx_fin_obvrska_user (cost=0.00..6.65 rows=295 width=0) (actual time=0.028..0.028 rows=110 loops=1) Index Cond: (user_id = k.id) Execution Time: 100.194 ms }}} '''Цена на insert/update по индексирање:''' insert '''371ms''', update '''307ms'''. Update-от поскапе бидејќи тестот ја менува индексираната колона `user_id`; во реална употреба таа колона никогаш не се менува, па реалната цена е значително помала. ---- == View 5: vw_istorija_naplati (историја на наплати) == '''Примарен филтер:''' `username` на корисникот, со сортирање по датум опаѓачки (најнови наплати први). '''Случај на употреба:''' корисник или служба ја прегледува историјата на уплати. Перформансите се критични — ова е најчесто отворениот финансиски преглед. === Дефиниција === {{{ CREATE OR REPLACE VIEW public.vw_istorija_naplati AS SELECT n.id AS naplata_id, k.username, l.ime, l.prezime, n.naplatena_suma, vv.iso_code AS valuta, st.ime_status AS transakcija_status, n.nadvoresna_referenca, n.data AS datum_naplata, oe.ime AS organizaciona_edinica FROM fin_naplati n JOIN korisnici k ON n.user_id = k.id JOIN lice l ON k.lice_id = l.id JOIN fin_status_transakcija st ON n.status_transakcija_id = st.id JOIN fin_vid_valuta vv ON n.vid_valuta_id = vv.id JOIN or_organizaciona_edinica oe ON n.organizaciona_edinica_id = oe.id ORDER BY n.data DESC; }}} Тест прашалник: `SELECT * FROM vw_istorija_naplati WHERE username = 'korisnik_2_435';` === Пред индексирање — 237s 396ms === {{{ Gather Merge (cost=334530.98..334542.95 rows=100 width=1104) (actual time=237352.745..237359.519 rows=110 loops=1) -> Sort (cost=333530.92..333530.98 rows=25 width=1104) (actual time=237295.581..237295.594 rows=22 loops=5) Sort Key: n.data DESC -> Nested Loop (cost=2050.07..333530.34 rows=25 width=1104) (actual time=231938.852..237295.500 rows=22 loops=5) -> Nested Loop (cost=2049.92..333528.46 rows=25 width=592) (actual time=231917.043..237273.679 rows=22 loops=5) -> Nested Loop (cost=2049.76..333527.53 rows=25 width=580) (actual time=231917.020..237273.642 rows=22 loops=5) -> Nested Loop (cost=2049.61..333526.44 rows=25 width=68) (actual time=231890.375..237246.982 rows=22 loops=5) -> Parallel Hash Join (cost=2049.31..333509.53 rows=25 width=57) (actual time=231890.351..237246.942 rows=22 loops=5) Hash Cond: (n.user_id = k.id) -> Parallel Seq Scan on fin_naplati n (cost=0.00..324803.16 rows=2535916 width=40) (actual time=125850.817..236965.174 rows=2030116 loops=5) -> Parallel Hash (cost=2049.29..2049.29 rows=1 width=25) (actual time=41.257..41.259 rows=0 loops=5) -> Parallel Seq Scan on korisnici k (cost=0.00..2049.29 rows=1 width=25) (actual time=32.154..34.592 rows=0 loops=5) Filter: ((username)::text = 'korisnik_2_435'::text) Rows Removed by Filter: 20000 -> Memoize (cost=0.30..8.32 rows=1 width=19) (actual time=0.001..0.001 rows=1 loops=110) Cache Key: k.lice_id -> Index Scan using lice_pkey on lice l (cost=0.29..8.31 rows=1 width=19) (actual time=0.062..0.063 rows=1 loops=1) Index Cond: (id = k.lice_id) -> Memoize (cost=0.15..0.17 rows=1 width=520) (actual time=1.211..1.211 rows=1 loops=110) Cache Key: n.status_transakcija_id -> Index Scan using fin_status_transakcija_pkey on fin_status_transakcija st (cost=0.14..0.16 rows=1 width=520) (actual time=66.600..66.600 rows=1 loops=2) Index Cond: (id = n.status_transakcija_id) -> Memoize (cost=0.15..0.17 rows=1 width=20) (actual time=0.001..0.001 rows=1 loops=110) Cache Key: n.vid_valuta_id -> Index Scan using fin_vid_valuta_pkey on fin_vid_valuta vv (cost=0.14..0.16 rows=1 width=20) (actual time=0.078..0.078 rows=1 loops=1) Index Cond: (id = n.vid_valuta_id) -> Memoize (cost=0.15..0.17 rows=1 width=520) (actual time=0.991..0.991 rows=1 loops=110) Cache Key: n.organizaciona_edinica_id -> Index Scan using or_organizaciona_edinica_pkey on or_organizaciona_edinica oe (cost=0.14..0.16 rows=1 width=520) (actual time=109.017..109.017 rows=1 loops=1) Index Cond: (id = n.organizaciona_edinica_id) Execution Time: 237396.477 ms }}} '''Цена на insert/update пред индексирање:''' insert '''535ms''', update '''29ms'''. === Оптимизација === Најбавните операции се full scan на `fin_naplati` (10.15M редови) и сортирање на целата табела. Се подобруваат со: {{{ CREATE INDEX idx_fin_naplati_user ON fin_naplati (user_id); }}} (заедно со `idx_korisnici_username` од View 4). === По индексирање — 0.8ms === {{{ Sort (cost=2199.59..2199.85 rows=102 width=104) (actual time=0.570..0.579 rows=110 loops=1) Sort Key: n.data DESC -> Nested Loop (cost=9.42..2196.19 rows=102 width=104) (actual time=0.193..0.425 rows=110 loops=1) Join Filter: (oe.id = n.organizaciona_edinica_id) Rows Removed by Join Filter: 440 -> Nested Loop (cost=9.42..2179.46 rows=102 width=72) (actual time=0.174..0.309 rows=110 loops=1) Join Filter: (vv.id = n.vid_valuta_id) -> Seq Scan on fin_vid_valuta vv (cost=0.00..1.01 rows=1 width=8) (actual time=0.014..0.015 rows=1 loops=1) -> Nested Loop (cost=9.42..2177.17 rows=102 width=72) (actual time=0.157..0.273 rows=110 loops=1) Join Filter: (st.id = n.status_transakcija_id) Rows Removed by Join Filter: 89 -> Nested Loop (cost=9.42..2170.94 rows=102 width=68) (actual time=0.138..0.202 rows=110 loops=1) -> Nested Loop (cost=0.71..16.75 rows=1 width=36) (actual time=0.071..0.073 rows=1 loops=1) -> Index Scan using idx_korisnici_username on korisnici k (cost=0.42..8.44 rows=1 width=25) (actual time=0.048..0.050 rows=1 loops=1) Index Cond: ((username)::text = 'korisnik_2_435'::text) -> Index Scan using lice_pkey on lice l (cost=0.29..8.31 rows=1 width=19) (actual time=0.019..0.019 rows=1 loops=1) Index Cond: (id = k.lice_id) -> Bitmap Heap Scan on fin_naplati n (cost=8.71..2148.69 rows=551 width=40) (actual time=0.064..0.099 rows=110 loops=1) Recheck Cond: (user_id = k.id) -> Bitmap Index Scan on idx_fin_naplati_user (cost=0.00..8.57 rows=551 width=0) (actual time=0.026..0.026 rows=110 loops=1) Index Cond: (user_id = k.id) -> Materialize (cost=0.00..1.06 rows=4 width=12) (actual time=0.000..0.000 rows=2 loops=110) -> Seq Scan on fin_status_transakcija st (cost=0.00..1.04 rows=4 width=12) (actual time=0.008..0.008 rows=2 loops=1) -> Materialize (cost=0.00..1.17 rows=11 width=40) (actual time=0.000..0.000 rows=5 loops=110) -> Seq Scan on or_organizaciona_edinica oe (cost=0.00..1.11 rows=11 width=40) (actual time=0.009..0.010 rows=5 loops=1) Execution Time: 0.779 ms }}} '''Цена на insert/update по индексирање:''' insert '''723ms''', update '''92ms'''. ---- == View 6: vw_pregled_plati_period (трошок за плати по период) == '''Примарен филтер:''' период (`datum_od`/`datum_do`), се користи и според организациона единица. '''Случај на употреба:''' финансиската служба за месечни извештаи — вкупно бруто/нето/даноци, број на вработени и просечна нето плата по период, единица и валута. === Дефиниција === {{{ CREATE OR REPLACE VIEW public.vw_pregled_plati_period AS SELECT pp.id AS plata_period_id, pp.datum_od, pp.datum_do, oe.ime AS organizaciona_edinica, vv.iso_code AS valuta, sum(psv.bruto_vkupno) AS vkupno_bruto, sum(psv.neto_vkupno) AS vkupno_neto, sum(psv.danoci_vkupno) AS vkupno_danoci, count(psv.id) AS broj_vraboteni, round(avg(psv.neto_vkupno), 2) AS prosecna_neto_plata FROM fin_plata_stavka_vraboten psv JOIN fin_plata_period pp ON psv.plata_period_id = pp.id JOIN or_organizaciona_edinica oe ON pp.organizaciona_edinica_id = oe.id JOIN fin_vid_valuta vv ON psv.vid_valuta_id = vv.id GROUP BY pp.id, pp.datum_od, pp.datum_do, oe.ime, vv.iso_code; }}} Тест прашалник: `... WHERE datum_od >= '2010-01-01' AND datum_do <= '2010-03-31';` === Време — 150ms (прифатливо, без индексирање) === {{{ Finalize GroupAggregate (cost=15235.51..17119.24 rows=12142 width=680) (actual time=142.233..150.133 rows=33 loops=1) Group Key: pp.id, oe.ime, vv.iso_code -> Gather Merge (cost=15235.51..16593.09 rows=10118 width=680) (actual time=142.184..149.987 rows=34 loops=1) -> Partial GroupAggregate (cost=14235.49..14425.20 rows=5059 width=680) (actual time=136.817..136.892 rows=11 loops=3) Group Key: pp.id, oe.ime, vv.iso_code -> Sort (cost=14235.49..14248.14 rows=5059 width=570) (actual time=136.778..136.789 rows=101 loops=3) Sort Key: pp.id, oe.ime, vv.iso_code -> Hash Join (cost=82.30..13924.24 rows=5059 width=570) (actual time=73.119..136.604 rows=101 loops=3) Hash Cond: (psv.vid_valuta_id = vv.id) -> Hash Join (cost=70.73..13898.59 rows=5059 width=558) (actual time=73.028..136.486 rows=101 loops=3) Hash Cond: (pp.organizaciona_edinica_id = oe.id) -> Hash Join (cost=57.58..13871.76 rows=5059 width=46) (actual time=72.959..136.390 rows=101 loops=3) Hash Cond: (psv.plata_period_id = pp.id) -> Parallel Seq Scan on fin_plata_stavka_vraboten psv (cost=0.00..12842.22 rows=369622 width=34) (actual time=0.016..41.362 rows=295698 loops=3) -> Hash (cost=57.16..57.16 rows=33 width=16) (actual time=63.576..63.577 rows=33 loops=3) -> Seq Scan on fin_plata_period pp (cost=0.00..57.16 rows=33 width=16) (actual time=0.024..63.551 rows=33 loops=3) Filter: ((datum_od >= '2010-01-01'::date) AND (datum_do <= '2010-03-31'::date)) Rows Removed by Filter: 2378 -> Hash (cost=11.40..11.40 rows=140 width=520) (actual time=0.037..0.037 rows=11 loops=3) -> Seq Scan on or_organizaciona_edinica oe (cost=0.00..11.40 rows=140 width=520) (actual time=0.023..0.026 rows=11 loops=3) -> Hash (cost=10.70..10.70 rows=70 width=20) (actual time=0.046..0.047 rows=1 loops=3) -> Seq Scan on fin_vid_valuta vv (cost=0.00..10.70 rows=70 width=20) (actual time=0.037..0.038 rows=1 loops=3) Execution Time: 150.487 ms }}} Времето е задоволително, па нема потреба од индексирање ниту од преуредување на прашалникот. Времето на `insert`/`update` останува исто. ---- == View 7: vw_raspredelba_nastaven_kadar (оптовареност на наставен кадар) == '''Примарен филтер:''' име и презиме на вработен (се користи и според академски семестар). '''Случај на употреба:''' проверка на неделниот фонд часови по вработен (норматив), број на курсеви и паралелки. === Дефиниција === {{{ CREATE OR REPLACE VIEW public.vw_raspredelba_nastaven_kadar AS SELECT va.id AS vraboten_angazman_id, l.ime, l.prezime, asem.name AS akademski_semestar, sum(krnk.fond_casovi_nedelno) AS vkupno_casovi_nedelno, count(DISTINCT kr.id) AS broj_kursevi, count(DISTINCT krp.id) AS broj_paralelki FROM sm_kurs_realizacija_nastaven_kadar krnk JOIN vr_vraboten_angazman va ON krnk.vraboten_angazman_id = va.id JOIN lice l ON va.lice_id = l.id JOIN sm_paralelka_nastaven_kadar pnk ON krnk.id = pnk.kurs_realizacija_kadar_id JOIN sm_kurs_realizacija_paralelka krp ON pnk.kurs_realizacija_paralelka_id = krp.id JOIN sm_kurs_realizacija kr ON krp.kurs_realizacija_id = kr.id JOIN sm_akademski_semestar asem ON kr.akademski_semestar_id = asem.id WHERE krnk.aktiven GROUP BY va.id, l.ime, l.prezime, asem.name; }}} Тест прашалник: `... WHERE ime = 'Katerina' AND prezime = 'Ivanov';` === Време — 220ms (прифатливо, без индексирање) === Табелата `sm_kurs_realizacija_nastaven_kadar` има само 7.263 редови, па времето е прифатливо. {{{ GroupAggregate (cost=3891.72..3893.20 rows=54 width=81) (actual time=219.167..219.355 rows=101 loops=1) Group Key: va.id, asem.name -> Sort (cost=3891.72..3891.85 rows=54 width=46) (actual time=219.119..219.131 rows=108 loops=1) Sort Key: va.id, asem.name, kr.id -> Hash Join (cost=3662.39..3890.16 rows=54 width=46) (actual time=57.352..218.952 rows=108 loops=1) Hash Cond: (kr.akademski_semestar_id = asem.id) -> Nested Loop (cost=3659.50..3887.12 rows=54 width=36) (actual time=57.270..218.801 rows=108 loops=1) -> Nested Loop (cost=3659.22..3870.33 rows=54 width=32) (actual time=57.238..217.958 rows=108 loops=1) -> Hash Join (cost=3658.94..3853.08 rows=54 width=28) (actual time=35.105..121.720 rows=108 loops=1) Hash Cond: (pnk.kurs_realizacija_kadar_id = krnk.id) -> Seq Scan on sm_paralelka_nastaven_kadar pnk (cost=0.00..155.80 rows=10080 width=8) (actual time=12.748..98.114 rows=10080 loops=1) -> Hash (cost=3658.45..3658.45 rows=39 width=28) (actual time=22.313..22.316 rows=59 loops=1) -> Hash Join (cost=3504.20..3658.45 rows=39 width=28) (actual time=20.267..22.285 rows=59 loops=1) Hash Cond: (krnk.vraboten_angazman_id = va.id) -> Seq Scan on sm_kurs_realizacija_nastaven_kadar krnk (cost=0.00..126.63 rows=7263 width=13) (actual time=0.015..1.355 rows=7263 loops=1) Filter: aktiven -> Hash (cost=3502.75..3502.75 rows=116 width=19) (actual time=20.182..20.184 rows=177 loops=1) -> Hash Join (cost=2831.66..3502.75 rows=116 width=19) (actual time=14.625..20.102 rows=177 loops=1) Hash Cond: (va.lice_id = l.id) -> Seq Scan on vr_vraboten_angazman va (cost=0.00..613.89 rows=21789 width=8) (actual time=0.298..2.071 rows=21789 loops=1) -> Hash (cost=2825.00..2825.00 rows=533 width=19) (actual time=14.296..14.297 rows=668 loops=1) -> Seq Scan on lice l (cost=0.00..2825.00 rows=533 width=19) (actual time=0.071..14.004 rows=668 loops=1) Filter: (((ime)::text = 'Katerina'::text) AND ((prezime)::text = 'Ivanov'::text)) Rows Removed by Filter: 99332 -> Index Scan using sm_kurs_realizacija_paralelka_pkey on sm_kurs_realizacija_paralelka krp (cost=0.28..0.32 rows=1 width=8) (actual time=0.890..0.890 rows=1 loops=108) Index Cond: (id = pnk.kurs_realizacija_paralelka_id) -> Index Scan using sm_kurs_realizacija_pkey on sm_kurs_realizacija kr (cost=0.28..0.31 rows=1 width=8) (actual time=0.007..0.007 rows=1 loops=108) Index Cond: (id = krp.kurs_realizacija_id) -> Hash (cost=1.84..1.84 rows=84 width=18) (actual time=0.056..0.056 rows=84 loops=1) -> Seq Scan on sm_akademski_semestar asem (cost=0.00..1.84 rows=84 width=18) (actual time=0.014..0.026 rows=84 loops=1) Execution Time: 219.692 ms }}} Нема потреба од индексирање ниту од преуредување. Времето на `insert`/`update` останува исто. ---- == View 8: vw_vraboteni_karieren_pat (кариерен пат на вработени) == '''Примарен филтер:''' име на вработен. '''Случај на употреба:''' преглед на академско звање и моментална функција на вработен. === Дефиниција === {{{ CREATE OR REPLACE VIEW public.vw_vraboteni_karieren_pat AS SELECT (l.ime::text || ' '::text) || l.prezime::text AS vraboten, az.ime AS akademsko_zvanje, vaz.datum_od AS zvanje_od, COALESCE(tf.ime, 'Нема функција'::character varying) AS "моментална_функција", vf.datum_od AS "функција_од", vf.datum_do AS "мандат_до" FROM vr_vraboten_angazman va JOIN lice l ON va.lice_id = l.id LEFT JOIN vr_vraboten_akademsko_zvanje vaz ON vaz.vraboten_angazman_id = va.id AND vaz.aktivno = true LEFT JOIN vr_akademsko_zvanje az ON vaz.akademsko_zvanje_id = az.id LEFT JOIN vr_vraboten_funkcija vf ON vf.vraboten_angazman_id = va.id AND vf.aktivna = true LEFT JOIN vr_tip_funkcija tf ON vf.tip_funkcija_id = tf.id WHERE va.aktiven = true; }}} Тест прашалник: `... WHERE vraboten = 'Katerina Ivanov';` === Време — 431ms (прифатливо, без индексирање) === {{{ Nested Loop Left Join (cost=3642.93..4179.60 rows=36 width=592) (actual time=58.804..430.801 rows=59 loops=1) -> Hash Left Join (cost=3642.79..4171.24 rows=36 width=547) (actual time=58.783..402.398 rows=59 loops=1) Hash Cond: (va.id = vf.vraboten_angazman_id) -> Nested Loop Left Join (cost=3609.55..4134.66 rows=36 width=539) (actual time=58.757..402.352 rows=59 loops=1) -> Hash Right Join (cost=3609.40..4132.65 rows=36 width=27) (actual time=58.731..402.248 rows=59 loops=1) Hash Cond: (vaz.vraboten_angazman_id = va.id) -> Seq Scan on vr_vraboten_akademsko_zvanje vaz (cost=0.00..495.89 rows=7263 width=12) (actual time=0.254..343.104 rows=7263 loops=1) Filter: aktivno Rows Removed by Filter: 14526 -> Hash (cost=3608.95..3608.95 rows=36 width=19) (actual time=58.391..58.393 rows=59 loops=1) -> Merge Join (cost=1079.98..3608.95 rows=36 width=19) (actual time=7.535..58.350 rows=59 loops=1) Merge Cond: (l.id = va.lice_id) -> Index Scan using lice_pkey on lice l (cost=0.29..4683.82 rows=500 width=19) (actual time=0.120..51.340 rows=432 loops=1) Filter: ((((ime)::text || ' '::text) || (prezime)::text) = 'Katerina Ivanov'::text) Rows Removed by Filter: 53022 -> Sort (cost=1079.68..1097.84 rows=7263 width=8) (actual time=5.810..6.268 rows=7263 loops=1) Sort Key: va.lice_id -> Seq Scan on vr_vraboten_angazman va (cost=0.00..613.89 rows=7263 width=8) (actual time=0.254..3.736 rows=7263 loops=1) Filter: aktiven Rows Removed by Filter: 14526 -> Memoize (cost=0.15..0.18 rows=1 width=520) (actual time=0.001..0.001 rows=1 loops=59) Cache Key: vaz.akademsko_zvanje_id -> Index Scan using vr_akademsko_zvanje_pkey on vr_akademsko_zvanje az (cost=0.14..0.17 rows=1 width=520) (actual time=0.006..0.006 rows=1 loops=4) Index Cond: (id = vaz.akademsko_zvanje_id) -> Hash (cost=24.30..24.30 rows=715 width=16) (actual time=0.020..0.021 rows=11 loops=1) -> Seq Scan on vr_vraboten_funkcija vf (cost=0.00..24.30 rows=715 width=16) (actual time=0.015..0.017 rows=11 loops=1) Filter: aktivna -> Index Scan using vr_tip_funkcija_pkey on vr_tip_funkcija tf (cost=0.14..0.23 rows=1 width=520) (actual time=0.480..0.480 rows=0 loops=59) Index Cond: (id = vf.tip_funkcija_id) Execution Time: 431.103 ms }}} Нема потреба од индексирање ниту од преуредување. Времето на `insert`/`update` останува исто. ---- == View 9: vw_vraboteni_prekrsoci_kvalifikacii (прекршоци во квалификации) == '''Примарен филтер:''' нема — се користи како целосен извештај за сите активни вработени кои не ги исполнуваат предусловите за нивната позиција. '''Случај на употреба:''' HR при контрола на усогласеност. Се извршува периодично, но 76 секунди е неприфатливо и за извештај. === Дефиниција === {{{ CREATE OR REPLACE VIEW public.vw_vraboteni_prekrsoci_kvalifikacii AS WITH requirement_check AS ( SELECT va.id AS angazman_id, (l.ime::text || ' '::text) || l.prezime::text AS vraboten, rp.ime_pozicija, pg.logicki_operator, pp.id AS preduslov_id, (EXISTS ( SELECT 1 FROM vr_lice_kvalifikacija lk JOIN vr_vid_kvalifikacija vk ON lk.vid_kvalifikacija_id = vk.id WHERE lk.lice_id = va.lice_id AND vk.akademski_stepen_id >= pp.minimalen_akademski_stepen_id)) AS degree_met, (EXISTS ( SELECT 1 FROM vr_lice_kvalifikacija lk WHERE lk.lice_id = va.lice_id AND (pp.pole_kvalifikacija_id IS NULL OR lk.pole_kvalifikacija_id = pp.pole_kvalifikacija_id))) AS pole_met, (EXISTS ( SELECT 1 FROM vr_lice_kvalifikacija lk WHERE lk.lice_id = va.lice_id AND (pp.vid_kvalifikacija_id IS NULL OR lk.vid_kvalifikacija_id = pp.vid_kvalifikacija_id))) AS vid_met FROM vr_vraboten_angazman va JOIN lice l ON va.lice_id = l.id JOIN vr_rabota_pozicija rp ON va.rabota_pozicija_id = rp.id JOIN vr_pozicija_preduslov_grupa pg ON pg.rabota_pozicija_id = rp.id JOIN vr_pozicija_preduslov pp ON pp.pozicija_preduslov_grupa_id = pg.id WHERE va.aktiven = true ), evaluation AS ( SELECT angazman_id, vraboten, ime_pozicija, logicki_operator, degree_met AND pole_met AND vid_met AS condition_satisfied FROM requirement_check ) SELECT vraboten, ime_pozicija, 'NEVALIDEN'::text AS status_usoglasenost, CASE WHEN logicki_operator::text = 'AND'::text THEN 'Не исполнува еден или повеќе задолжителни услови'::text WHEN logicki_operator::text = 'OR'::text THEN 'Не исполнува ниту еден од алтернативните патишта'::text ELSE NULL::text END AS razlog FROM evaluation GROUP BY angazman_id, vraboten, ime_pozicija, logicki_operator HAVING logicki_operator::text = 'AND'::text AND NOT bool_and(condition_satisfied) OR logicki_operator::text = 'OR'::text AND NOT bool_or(condition_satisfied); }}} Тест прашалник: `SELECT * FROM vw_vraboteni_prekrsoci_kvalifikacii;` === Пред индексирање — 76s 68ms === {{{ Subquery Scan on vw_vraboteni_prekrsoci_kvalifikacii (cost=9.98..219984447.66 rows=145 width=612) (actual time=76026.008..76026.019 rows=0 loops=1) -> GroupAggregate (cost=9.98..219984446.21 rows=145 width=1132) (actual time=76026.007..76026.017 rows=0 loops=1) Group Key: va.id, ((((l.ime)::text || ' '::text) || (l.prezime)::text)), rp.ime_pozicija, pg.logicki_operator Filter: ((((pg.logicki_operator)::text = 'AND'::text) AND (NOT bool_and((EXISTS(SubPlan 1) AND EXISTS(SubPlan 2) AND EXISTS(SubPlan 3))))) OR (((pg.logicki_operator)::text = 'OR'::text) AND (NOT bool_or((EXISTS(SubPlan 4) AND EXISTS(SubPlan 5) AND EXISTS(SubPlan 6)))))) Rows Removed by Filter: 7263 -> Incremental Sort (cost=9.98..67483.64 rows=20336 width=1084) (actual time=1322.379..1431.824 rows=11183 loops=1) Sort Key: va.id, ((((l.ime)::text || ' '::text) || (l.prezime)::text)), rp.ime_pozicija, pg.logicki_operator Presorted Key: va.id -> Nested Loop (cost=0.74..66933.15 rows=20336 width=1084) (actual time=1321.801..1419.264 rows=11183 loops=1) Join Filter: (pg.id = pp.pozicija_preduslov_grupa_id) Rows Removed by Join Filter: 177655 -> Nested Loop (cost=0.74..24114.12 rows=20336 width=1059) (actual time=1321.755..1391.594 rows=7263 loops=1) Join Filter: (va.rabota_pozicija_id = pg.rabota_pozicija_id) Rows Removed by Join Filter: 145260 -> Nested Loop (cost=0.74..8850.07 rows=7263 width=547) (actual time=1321.709..1370.307 rows=7263 loops=1) -> Nested Loop (cost=0.44..2577.22 rows=7263 width=532) (actual time=1321.630..1334.642 rows=7263 loops=1) -> Index Scan using vr_vraboten_angazman_pkey on vr_vraboten_angazman va (cost=0.29..2395.12 rows=7263 width=12) (actual time=1321.507..1329.612 rows=7263 loops=1) Filter: aktiven Rows Removed by Filter: 14526 -> Memoize (cost=0.15..0.17 rows=1 width=520) (actual time=0.000..0.000 rows=1 loops=7263) Cache Key: va.rabota_pozicija_id -> Index Scan using vr_rabota_pozicija_pkey on vr_rabota_pozicija rp (cost=0.14..0.16 rows=1 width=520) (actual time=0.010..0.010 rows=1 loops=7) Index Cond: (id = va.rabota_pozicija_id) -> Memoize (cost=0.30..1.20 rows=1 width=19) (actual time=0.005..0.005 rows=1 loops=7263) Cache Key: va.lice_id -> Index Scan using lice_pkey on lice l (cost=0.29..1.19 rows=1 width=19) (actual time=0.003..0.003 rows=1 loops=7263) Index Cond: (id = va.lice_id) -> Materialize (cost=0.00..12.10 rows=140 width=524) (actual time=0.000..0.001 rows=21 loops=7263) -> Seq Scan on vr_pozicija_preduslov_grupa pg (cost=0.00..11.40 rows=140 width=524) (actual time=0.026..0.029 rows=21 loops=1) -> Materialize (cost=0.00..12.10 rows=140 width=16) (actual time=0.000..0.001 rows=26 loops=7263) -> Seq Scan on vr_pozicija_preduslov pp (cost=0.00..11.40 rows=140 width=16) (actual time=0.026..0.030 rows=26 loops=1) SubPlan 1 -> Nested Loop (cost=0.00..1721.08 rows=1 width=0) (actual time=1.727..1.727 rows=1 loops=11183) -> Seq Scan on vr_vid_kvalifikacija vk (cost=0.00..10.88 rows=23 width=4) ... -> Seq Scan on vr_lice_kvalifikacija lk (cost=0.00..1709.16 rows=3 width=4) (actual time=0.643..1.710 rows=2 loops=11183) Filter: (lice_id = va.lice_id) Rows Removed by Filter: 26433 SubPlan 2 / 3 / 4 / 5 / 6 -> Seq Scan on vr_lice_kvalifikacija (повторени full scan-ови по lice_id во секој EXISTS) Execution Time: 76068.442 ms }}} '''Цена на insert/update пред индексирање:''' insert '''106ms''', update '''30ms'''. === Оптимизација === Најбавните операции се шесткратниот full scan на `vr_lice_kvalifikacija` во `EXISTS` под-прашалниците. Се додава индекс: {{{ CREATE INDEX idx_lice_kvalifikacija_lice ON vr_lice_kvalifikacija (lice_id); }}} === По индексирање — 2s 179ms === За целосен извештај врз сите вработени ова е прифатливо време. По индексирањето, секој `EXISTS` користи `Bitmap Index Scan on idx_lice_kvalifikacija_lice` наместо full scan. {{{ Subquery Scan on vw_vraboteni_prekrsoci_kvalifikacii (cost=4177.58..882147.14 rows=4149 width=111) (actual time=2100.598..2100.612 rows=0 loops=1) -> GroupAggregate (cost=4177.58..882105.65 rows=4149 width=118) (actual time=2100.597..2100.610 rows=0 loops=1) Group Key: va.id, ((((l.ime)::text || ' '::text) || (l.prezime)::text)), rp.ime_pozicija, pg.logicki_operator Rows Removed by Filter: 7263 -> Sort (cost=4177.58..4200.06 rows=8992 width=70) (actual time=1370.665..1374.403 rows=11183 loops=1) -> Hash Join (cost=1084.67..3587.06 rows=8992 width=70) (actual time=1339.546..1364.286 rows=11183 loops=1) Hash Cond: (va.rabota_pozicija_id = rp.id) -> Merge Join (cost=1079.98..3420.26 rows=7263 width=27) (actual time=4.337..25.819 rows=7263 loops=1) Merge Cond: (l.id = va.lice_id) -> Index Scan using lice_pkey on lice l (cost=0.29..3933.61 rows=100000 width=19) (actual time=0.032..14.631 rows=53400 loops=1) -> Sort (cost=1079.68..1097.84 rows=7263 width=12) (actual time=4.269..4.806 rows=7263 loops=1) -> Seq Scan on vr_vraboten_angazman va (cost=0.00..613.89 rows=7263 width=12) (actual time=0.335..2.685 rows=7263 loops=1) Filter: aktiven SubPlan 1 -> Nested Loop (cost=4.31..17.05 rows=1 width=0) (actual time=0.016..0.016 rows=1 loops=11183) -> Bitmap Heap Scan on vr_lice_kvalifikacija lk (cost=4.31..15.87 rows=3 width=4) (actual time=0.006..0.007 rows=2 loops=11183) Recheck Cond: (lice_id = va.lice_id) -> Bitmap Index Scan on idx_lice_kvalifikacija_lice (cost=0.00..4.31 rows=3 width=0) (actual time=0.004..0.004 rows=3 loops=11183) Index Cond: (lice_id = va.lice_id) SubPlan 2..6 -> Bitmap Heap Scan on vr_lice_kvalifikacija using idx_lice_kvalifikacija_lice (за секој EXISTS) Execution Time: 2179.408 ms }}} '''Цена на insert/update по индексирање:''' insert '''93ms''', update '''38ms'''. ---- == Резиме == || '''Поглед''' || '''Пред''' || '''По''' || '''Индекси''' || || vw_studentski_oceni || 67.9 s || 2.3 ms || 5 (студентски синџир) || || vw_aktivni_upisani_kursevi || 283 ms || — || нема потреба || || vw_ispitni_prijavi || 63.3 s || 2.2 ms || 1 + преуреден прашалник || || vw_otvoreni_finansiski_obvrski || 104.5 s || 100 ms || 2 || || vw_istorija_naplati || 237.4 s || 0.8 ms || 1 || || vw_pregled_plati_period || 150 ms || — || нема потреба || || vw_raspredelba_nastaven_kadar || 220 ms || — || нема потреба || || vw_vraboteni_karieren_pat || 431 ms || — || нема потреба || || vw_vraboteni_prekrsoci_kvalifikacii || 76.1 s || 2.2 s || 1 || Вкупно '''10 индекси''' ги покриваат сите бавни погледи; ниту еден индекс не е додаден без конкретен full scan во планот на извршување кој го оправдува. Кај View 3 беше потребно и '''преуредување на прашалникот''' (студентскиот синџир прв), бидејќи 11-те JOIN-а го надминуваат `join_collapse_limit = 8`.