wiki:QueryOptimization

Version 3 (modified by 231102, 15 hours ago) ( diff )

--

Индекси и оптимизација на прашалници

Опис и методологија

Во оваа фаза се анализираат и оптимизираат 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.

Note: See TracWiki for help on using the wiki.