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




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

=== Поглед 1: v_site_zapisnici ===
'''Брз преглед на сите записници по лице, без join-ови.'''[[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]]
Се пребарува по: `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]]
Се пребарува по: `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]]
Се пребарува по: `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_policaici_prosek_zapisnici_mv ===
'''Статистики по полицаец: вкупно записници, прв/последен, активни месеци и просечен број записници по месец.'''[[BR]]
Се пребарува по: `EMBG_P` (дополнително по име на станица).
{{{#!sql
CREATE MATERIALIZED VIEW v_policaici_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,
       ROUND(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, 2) 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]]
Се пребарува по: `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. Оптимизација ==
За секој поглед го измеривме почетното време преку `EXPLAIN ANALYZE`, ја идентификувавме најбавната операција и применивме оптимизација. Кај пребарувањата по клуч тоа е индекс, а кај аналитичките погледи — материјализација.

=== Поглед 1 — v_site_zapisnici ===
Најбавна операција: `Seq Scan on zapisnik` (се чита целата табела). Решение — индекс на колоната по која се филтрира:
{{{#!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 редови). Решение — индекси на 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 on 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`, па индекс не помага. Решение: материјализација (дефиницијата е во делот Погледи).

'''Прашалник кој го тестираме:'''
{{{#!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`, индекс не носи добивка. Решение: материјализација (дефиницијата е во делот Погледи).

'''Прашалник кој го тестираме:'''
{{{#!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, двете страни се скенираат во целост. Решение: материјализација (дефиницијата е во делот Погледи).

'''Прашалник кој го тестираме:'''
{{{#!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 || материјализиран поглед ||