= Фаза 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 ===
'''Граѓани што повторуваат ист тип прекршок (`COUNT > 1`) — колку пати, прв и последен пат, вкупен износ казни.'''[[BR]]
Се пребарува по: `EMBG`.
{{{#!sql
CREATE OR REPLACE VIEW v_povtoreni_prekrsoci 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 ===
'''Статистики по полицаец: вкупно записници, прв/последен, активни месеци и просечен број записници по месец.'''[[BR]]
Се пребарува по: `EMBG_P` (дополнително по име на станица).
{{{#!sql
CREATE OR REPLACE VIEW v_policaici_prosek_zapisnici 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 ===
'''Целосен профил на граѓанин: вкупно прекршоци, платено, долг, прв/последен прекршок и број различни типови.'''[[BR]]
Се пребарува по: `EMBG`.
{{{#!sql
CREATE OR REPLACE VIEW v_istorija_gragjanin 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` (се чита целата табела). Решение — индекс на колоната по која се филтрира:
{{{#!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 ===
Аналитички поглед. План: `GROUP BY` / `HashAggregate` над цела `Zapisnik` — агрегацијата ги поминува сите редови, индекс не помага. Затоа го материјализираме (`_mv`); резултатот се пресметува еднаш и се чита готов:
{{{#!sql
DROP VIEW v_povtoreni_prekrsoci;

CREATE MATERIALIZED VIEW v_povtoreni_prekrsoci_mv AS
-- ista definicija kako vo del 1
...;
}}}

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

[[Image(view4.png, 700px)]]

'''Време на извршување:''' градењето (`REFRESH`) трае ~4,917s; потоа читањето по `EMBG` е речиси моментално.

=== Поглед 5 — v_policaici_prosek_zapisnici ===
Аналитички поглед. План: агрегација (`COUNT`, `MIN`, `MAX`) групирана по полицаец над цела `Zapisnik` — индекс не носи добивка. Затоа го материјализираме (`_mv`):
{{{#!sql
DROP VIEW v_policaici_prosek_zapisnici;

CREATE MATERIALIZED VIEW v_policaici_prosek_zapisnici_mv AS
-- ista definicija kako vo del 1
...;
}}}

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

[[Image(view5.png, 700px)]]

'''Време на извршување:''' градењето (`REFRESH`) трае ~3,234s; потоа читањето по `EMBG_P` е речиси моментално.

=== Поглед 6 — v_istorija_gragjanin ===
Аналитички поглед. План: две одделни агрегации (над `Zapisnik`/`Stavka_Zapisnik` и над `Uplata`) па join — двете страни се скенираат во целост. Затоа го материјализираме (`_mv`):
{{{#!sql
DROP VIEW v_istorija_gragjanin;

CREATE MATERIALIZED VIEW v_istorija_gragjanin_mv AS
-- ista definicija kako vo del 1
...;
}}}

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

[[Image(view6.png, 700px)]]

'''Време на извршување:''' градењето (`REFRESH`) трае ~9,076s; потоа читањето по `EMBG` е речиси моментално.


[[BR]]
== 3. Резиме ==
||='''Поглед'''=||='''Пред'''=||='''По'''=||='''Решение'''=||
|| v_site_zapisnici || 6m4s || ~133ms || индекс ||
|| v_zapisnici_detalno || 5m59s || ~154ms || индекс ||
|| v_neplateni_kazni || 6,922s || ~121ms || постоечки индекс ||
|| v_povtoreni_prekrsoci || ~4,917s || брзо* || материјализиран поглед ||
|| v_policaici_prosek_zapisnici || ~3,234s || брзо* || материјализиран поглед ||
|| v_istorija_gragjanin || ~9,076s || брзо* || материјализиран поглед ||

'''*''' читањето од материјализиран поглед е речиси моментално; наведеното време е еднократната цена на градењето/освежувањето (`REFRESH`).