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

[[BR]]
== 1. Погледи ==
Во оваа фаза дефиниравме 6 погледи кои покриваат реални сценарија за пребарување во апликацијата.
[attachment:views.sql views.sql]

=== Поглед 1: v_site_zapisnici ===
'''Брз преглед на сите записници по лице, без join-ови.'''[[BR]]
Овој поглед овозможува брзо пронаоѓање на основните информации за издадените записници со цел оптимизација на почетните пребарувања. Наменет е за приказ на листи со записи во корисничкиот интерфејс каде што корисникот филтрира по прекршител или полицаец. Притоа, погледот извлекува податоци директно само од табелата `Zapisnik`, избегнувајќи сложени спојувања.[[BR]]
Се пребарува по: `EMBG_Prekrsuvach` (или `EMBG_Policaec`).
{{{#!sql
CREATE OR REPLACE VIEW v_site_zapisnici AS
SELECT 
    id_na_zapisnik,
    datum,
    vreme,
    lokacija,
    Potpis,
    id_slucaj,
    EMBG_Prekrsuvach,
    Vozilo_Broj_Sasija,
    EMBG_Policaec
FROM Zapisnik;
}}}

=== Поглед 2: v_zapisnici_detalno ===
'''Целосни детали за конкретен записник: прекршител, возило, полицаец, прекршок, износ на казната и статус на уплатата.'''[[BR]]
Погледот овозможува детален и сеопфатен приказ на сите информации поврзани со еден конкретен сообраќаен налог. Се користи во апликацијата кога операторот или граѓанинот сака да ги отвори сите детали за поединечен записник. За да се постигне ова, во view се спојува главната табела `Zapisnik` со табелите `Gragjanin` (за прекршителот и полицаецот), `Vozilo`, `Policaec`, `Stavka_Zapisnik`, `Prekrsok`, `Kazna` и `Uplata`.[[BR]]
Се пребарува по: `id_na_zapisnik` / `EMBG_Prekrsuvach`.
{{{#!sql
CREATE OR REPLACE VIEW v_zapisnici_detalno AS
SELECT
    z.id_na_zapisnik,
    z.datum,
    z.vreme,
    z.lokacija,
    z.id_slucaj,
    g.EMBG AS embg_prekrsitel,
    g.ime AS prekrsitel_ime,
    g.prezime AS prekrsitel_prezime,
    v.broj_na_sasija,
    v.model AS vozilo_model,
    pol.EMBG_P AS embg_policaec,
    g_p.ime AS policaec_ime,
    g_p.prezime AS policaec_prezime,
    p.ime AS prekrsok,
    k.iznos_kazna,
    u.iznos AS iznos_uplata,
    u.status AS status_uplata
FROM Zapisnik z
LEFT JOIN Gragjanin g ON z.EMBG_Prekrsuvach = g.EMBG
LEFT JOIN Vozilo v ON z.Vozilo_Broj_Sasija = v.broj_na_sasija
LEFT JOIN Policaec pol ON z.EMBG_Policaec = pol.EMBG_P
LEFT JOIN Gragjanin g_p ON pol.EMBG_P = g_p.EMBG
LEFT JOIN Stavka_Zapisnik sz ON z.id_na_zapisnik = sz.id_na_zapisnik
LEFT JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
LEFT JOIN Kazna k ON p.id_kazna = k.id_kazna
LEFT JOIN Uplata u ON z.id_na_zapisnik = u.id_zapisnik;
}}}

=== Поглед 3: v_neplateni_kazni ===
'''Сите неплатени казни на даден граѓанин, со износ за наплата и колку денови се веќе неплатени.'''[[BR]]
Овој поглед служи за брза финансиска проверка на прекршочните долгови кои сè уште не се подмирени од страна на граѓаните. Се користи при генерирање на налог за судска постапка, односно за неплатени обврски со пресметан рок на доцнење. Погледот се генерира преку филтрирано спојување на табелите `Uplata`, `Zapisnik`, `Gragjanin`, `Stavka_Zapisnik`, `Prekrsok` и `Kazna`.[[BR]]
Се пребарува по: `EMBG_prekrsitel`.
{{{#!sql
CREATE OR REPLACE VIEW v_neplateni_kazni AS
SELECT
    z.id_na_zapisnik,
    z.datum AS datum_prekrsok,
    z.lokacija,
    g.EMBG AS embg_prekrsitel,
    g.ime AS ime_prekrsitel,
    g.prezime AS prezime_prekrsitel,
    p.ime AS prekrsok,
    k.iznos_kazna,
    u.iznos AS iznos_za_naplata,
    (CURRENT_DATE - z.datum) AS denovi_neplateno
FROM Uplata u
JOIN Zapisnik z ON u.id_zapisnik = z.id_na_zapisnik
JOIN Gragjanin g ON z.EMBG_Prekrsuvach = g.EMBG
JOIN Stavka_Zapisnik sz ON z.id_na_zapisnik = sz.id_na_zapisnik
JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
JOIN Kazna k ON p.id_kazna = k.id_kazna
WHERE u.status = 'Neplateno';
}}}

=== Поглед 4: v_povtoreni_prekrsoci_mv ===
'''Граѓани што повторуваат ист тип прекршок (COUNT > 1)-колку пати, прв и последен пат, вкупен износ казни.'''[[BR]]
Ова е аналитички поглед наменет за следење и идентификување на прекршители, односно возачи кои постојано го повторуваат истиот прекршок. Се користи за статистички извештаи на МВР со цел таргетирање на критичните прекршители на патиштата. Погледот прави напредна агрегација и групирање со спојување на табелите `Zapisnik`, `Stavka_Zapisnik`, `Prekrsok`, `Kazna` и на крајот го врзува резултатот со табелата `Gragjanin`.[[BR]]
Се пребарува по: `EMBG`.
{{{#!sql
CREATE MATERIALIZED VIEW v_povtoreni_prekrsoci_mv AS
WITH Prekrsoci_Stats AS (
SELECT z.EMBG_Prekrsuvach, sz.id_na_prekrsok,
       COUNT(*) AS broj_povtori, MIN(z.datum) AS prv_pat, 
       MAX(z.datum) AS posleden_pat,
       SUM(k.iznos_kazna) AS vkupen_iznos_kazni
FROM Zapisnik z
JOIN Stavka_Zapisnik sz ON z.id_na_zapisnik = sz.id_na_zapisnik
JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok
JOIN Kazna k ON p.id_kazna = k.id_kazna
GROUP BY z.EMBG_Prekrsuvach, sz.id_na_prekrsok
HAVING COUNT(*) > 1
)
SELECT g.EMBG, g.ime, g.prezime, p.id_prekrsok, p.ime AS prekrsok,
       ps.broj_povtori, ps.prv_pat, ps.posleden_pat, ps.vkupen_iznos_kazni
FROM Prekrsoci_Stats ps
JOIN Gragjanin g ON ps.EMBG_Prekrsuvach = g.EMBG
JOIN Prekrsok p ON ps.id_na_prekrsok = p.id_prekrsok;
}}}

=== Поглед 5: v_policajci_prosek_zapisnici_mv ===
'''Статистики по полицаец: вкупно записници, прв/последен, активни месеци и просечен број записници по месец.'''[[BR]]
Погледот овозможува евалуација на ефикасноста и активноста на полициските службеници на терен низ подолг временски период. Наменет е за внатрешна контрола и менаџмент на полициските станици за следење на просечната продуктивност по месец. Се потпира на статистичка агрегација над табелата `Zapisnik`, која потоа се спојува со табелите `Policaec` и `Gragjanin` за комплетирање на профилот.[[BR]]
Се пребарува по: `EMBG_P`.
{{{#!sql
CREATE MATERIALIZED VIEW v_policajci_prosek_zapisnici_mv AS
WITH Policaec_Stats AS (
    SELECT EMBG_Policaec, COUNT(id_na_zapisnik) AS vkupno_zapisnici,
           MIN(datum) AS prv_zapisnik, MAX(datum) AS posleden_zapisnik
    FROM Zapisnik
    GROUP BY EMBG_Policaec
)
SELECT pol.EMBG_P, pol.broj_na_znacka, g.ime AS policaec_ime, g.prezime AS policaec_prezime,
       ps.vkupno_zapisnici, ps.prv_zapisnik, ps.posleden_zapisnik,
       GREATEST(DATE_PART('year', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)) * 12 +
                DATE_PART('month', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)), 1) AS aktivni_meseci,
       (ps.vkupno_zapisnici::numeric /
        GREATEST(DATE_PART('year', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)) * 12 +
            DATE_PART('month', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)),
            1)::numeric) AS prosek_zapisnici_po_mesec
FROM Policaec_Stats ps
JOIN Policaec pol ON ps.EMBG_Policaec = pol.EMBG_P
JOIN Gragjanin g  ON pol.EMBG_P = g.EMBG;
}}}

=== Поглед 6: v_istorija_gragjanin_mv ===
'''Целосен профил на граѓанин: вкупно прекршоци, платено, долг, прв/последен прекршок и број различни типови.'''[[BR]]
Овој поглед нуди целосно прекршочно и финансиско досие за секој граѓанин во системот на едно место. Се користи при пребарување на профил на граѓанин во апликацијата за веднаш да се прикаже неговата историја, вкупниот долг и направените уплати. Погледот комбинира и спојува две посебни агрегации од табелите `Zapisnik`, `Stavka_Zapisnik`, `Prekrsok` и `Kazna` од една страна, и табелата `Uplata` од друга страна, поврзувајќи ги на крај со табелата `Gragjanin`.[[BR]]
Се пребарува по: `EMBG`.
{{{#!sql
CREATE MATERIALIZED VIEW v_istorija_gragjanin_mv AS
WITH Zapisnik_Stats AS (
SELECT EMBG_Prekrsuvach,
COUNT(DISTINCT z.id_na_zapisnik) AS vkupno_zapisnici,
MIN(z.datum) AS prv_prekrsok, MAX(z.datum) AS posleden_prekrsok,
COUNT(sz.id_stavka) AS vkupno_prekrshoci,
COUNT(DISTINCT sz.id_na_prekrsok) AS razlichni_tipovi,
COALESCE(SUM(k.iznos_kazna), 0) AS vkupen_iznos_kazni
FROM Zapisnik z
LEFT JOIN Stavka_Zapisnik sz ON z.id_na_zapisnik = sz.id_na_zapisnik
LEFT JOIN Prekrsok p         ON sz.id_na_prekrsok = p.id_prekrsok
LEFT JOIN Kazna k            ON p.id_kazna = k.id_kazna
GROUP BY EMBG_Prekrsuvach
),
Uplata_Stats AS (
SELECT z.EMBG_Prekrsuvach,
COALESCE(SUM(CASE WHEN u.status = 'Plateno'  THEN u.iznos END), 0) AS vkupno_plateno,
COALESCE(SUM(CASE WHEN u.status = 'Neplateno' THEN u.iznos END), 0) AS vkupen_dolg,
COUNT(CASE WHEN u.status = 'Neplateno' THEN u.id_uplata END) AS broj_neplateni
FROM Uplata u
JOIN Zapisnik z ON u.id_zapisnik = z.id_na_zapisnik
GROUP BY z.EMBG_Prekrsuvach
)
SELECT g.EMBG, g.ime, g.prezime, g.datum_ragjanje,
zs.vkupno_zapisnici, zs.vkupno_prekrshoci, zs.razlichni_tipovi, zs.vkupen_iznos_kazni,
COALESCE(us.vkupno_plateno, 0) AS vkupno_plateno,
COALESCE(us.vkupen_dolg, 0)    AS vkupen_dolg,
COALESCE(us.broj_neplateni, 0) AS broj_neplateni,
zs.prv_prekrsok, zs.posleden_prekrsok
FROM Gragjanin g
JOIN Zapisnik_Stats zs ON g.EMBG = zs.EMBG_Prekrsuvach
LEFT JOIN Uplata_Stats us ON g.EMBG = us.EMBG_Prekrsuvach;
}}}

[[BR]]
== 2. Оптимизација ==
За секој поглед го измеривме почетното време, ја идентификувавме најбавната операција и применивме оптимизација. Кај пребарувањата по клуч тоа е индекс, а кај аналитичките погледи-материјализација.

=== Поглед 1: v_site_zapisnici ===
Најбавна операција: `Seq Scan` on `Zapisnik` (се чита целата табела).
[[BR]] Решение:  индекс на колоната по која се филтрира:
{{{#!sql
CREATE INDEX idx_zapisnik_prekrsuvach ON Zapisnik(EMBG_Prekrsuvach);
}}}

'''Прашалник кој го тестираме:'''
{{{#!sql
-- testirame za prekrsuvac so EMBG = '1508004480145'
SELECT * FROM v_site_zapisnici WHERE embg_prekrsuvach = '1508004480145';
}}}

'''Без индекс'''[[BR]]
време на извршување: 6m4s
[[Image(view1.png, 700px)]]

'''По додавање на индекс'''[[BR]]
време на извршување: ~133ms
[[Image(vew1-index.png, 700px)]]

=== Поглед 2: v_zapisnici_detalno ===
Најбавна операција: `Parallel Seq Scan` on `Uplata` (~2.000.000 редови). 
[[BR]] Решение: индекси на join-колоните:
{{{#!sql
CREATE INDEX idx_stavka_zapisnik_id_zapisnik ON Stavka_Zapisnik(id_na_zapisnik);
CREATE INDEX idx_uplata_id_zapisnik          ON Uplata(id_zapisnik);
}}}

'''Прашалник кој го тестираме:'''
{{{#!sql
-- testirame za prekrsitel so EMBG = '0412992470302'
SELECT * FROM v_zapisnici_detalno WHERE embg_prekrsitel = '0412992470302';
}}}

'''Без индекс'''[[BR]]
време на извршување: 5m59s
[[Image(view2.png, 700px)]]

'''По додавање на индекс'''[[BR]]
време на извршување: ~154ms
[[Image(view2-index.png, 700px)]]

=== Поглед 3: v_neplateni_kazni ===
Најбавна операција: `Seq Scan` на `Uplata`. Join-условот (u.id_zapisnik = z.id_na_zapisnik) е ист како кај Поглед 2, затоа не е потребен нов индекс, се реискористува постоечкиот idx_uplata_id_zapisnik.

'''Прашалник кој го тестираме:'''
{{{#!sql
-- testirame za prekrsitel so EMBG = '0412992470302'
SELECT * FROM v_neplateni_kazni WHERE embg_prekrsitel = '0412992470302';
}}}

'''Без индекс'''[[BR]]
време на извршување: 6,922s
[[Image(view3.png, 700px)]]

'''По додавање на индекс'''[[BR]]
време на извршување: ~121ms
[[Image(view3-index.png, 700px)]]

=== Поглед 4: v_povtoreni_prekrsoci_mv ===
Аналитички поглед-`GROUP BY` /`HashAggregate` над цела `Zapisnik`, па индекс не помага. 
[[BR]]Решение: материјализација.

'''Прашалник кој го тестираме:'''
{{{#!sql
-- testirame za graganin so ime = 'Виолета'
SELECT * FROM v_povtoreni_prekrsoci_mv WHERE ime = 'Виолета';
}}}

'''Пред''' (обичен аналитички поглед)[[BR]]
време на извршување: 1m8s
[[Image(view4.png, 700px)]]

'''Потоа''' (материјализиран поглед)[[BR]]
време на извршување: 388ms
[[Image(view4-materialized.png, 700px)]] [[BR]]
Освежувањето (REFRESH) трае 1m27s.
{{{#!sql
REFRESH MATERIALIZED VIEW v_povtoreni_prekrsoci_mv;
}}}

=== Поглед 5: v_policaici_prosek_zapisnici_mv ===
Аналитички поглед-агрегација (`COUNT`, `MIN`, `MAX`) групирана по полицаец над цела `Zapisnik`, индекс не носи добивка. 
[[BR]]Решение: материјализација.

'''Прашалник кој го тестираме:'''
{{{#!sql
-- testirame za policaec so ime = 'Никола'
SELECT * FROM v_policaici_prosek_zapisnici_mv WHERE policaec_ime = 'Никола';
}}}

'''Пред''' (обичен аналитички поглед)[[BR]]
време на извршување: 1,5s
[[Image(view5.png, 700px)]]

'''Потоа''' (материјализиран поглед)[[BR]]
време на извршување: <1s
[[Image(view5-materialized.png, 700px)]] [[BR]]
Освежувањето (REFRESH) трае <2s.
{{{#!sql
REFRESH MATERIALIZED VIEW v_policaici_prosek_zapisnici_mv;
}}}

=== Поглед 6: v_istorija_gragjanin_mv ===
Аналитички поглед-две одделни агрегации (над `Zapisnik`/`Stavka_Zapisnik` и над `Uplata`) па join, двете страни се скенираат во целост. 
[[BR]]Решение: материјализација.

'''Прашалник кој го тестираме:'''
{{{#!sql
-- testirame za graganin so ime = 'Оливера'
SELECT * FROM v_istorija_gragjanin_mv WHERE ime = 'Оливера';
}}}

'''Пред''' (обичен аналитички поглед)[[BR]]
време на извршување: 1m33s
[[Image(view6.png, 700px)]]

'''Потоа''' (материјализиран поглед)[[BR]]
време на извршување: <1s
[[Image(view6-materialized.png, 700px)]] [[BR]]
Освежувањето (REFRESH) трае 2m17s.
{{{#!sql
REFRESH MATERIALIZED VIEW v_istorija_gragjanin_mv;
}}}

[[BR]]
== 3. Резиме ==
||='''Поглед'''=||='''Пред'''=||='''По'''=||='''Решение'''=||
|| v_site_zapisnici || 6m4s || ~133ms || индекс ||
|| v_zapisnici_detalno || 5m59s || ~154ms || индекс ||
|| v_neplateni_kazni || 6,922s || ~121ms || постоечки индекс ||
|| v_povtoreni_prekrsoci_mv || 1m8s || 388ms || материјализиран поглед ||
|| v_policaici_prosek_zapisnici_mv || 1,5s || <1s || материјализиран поглед ||
|| v_istorija_gragjanin_mv || 1m33s || <1s || материјализиран поглед ||