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

1. Погледи

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

Поглед 1: v_site_zapisnici

Брз преглед на сите записници по лице, без join-ови.
Се пребарува по: EMBG_Prekrsuvach (или EMBG_Policaec).

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

Целосни детали за конкретен записник: прекршител, возило, полицаец, прекршок, износ казна и статус уплата.
Се пребарува по: id_na_zapisnik / EMBG_Prekrsuvach.

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

Сите неплатени казни на даден граѓанин, со износ за наплата и колку денови се веќе неплатени.
Се пребарува по: EMBG_prekrsitel.

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) — колку пати, прв и последен пат, вкупен износ казни.
Се пребарува по: EMBG.

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

Статистики по полицаец: вкупно записници, прв/последен, активни месеци и просечен број записници по месец.
Се пребарува по: EMBG_P (дополнително по име на станица).

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

Целосен профил на граѓанин: вкупно прекршоци, платено, долг, прв/последен прекршок и број различни типови.
Се пребарува по: EMBG.

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;

2. Оптимизација

За секој поглед го измеривме почетното време преку EXPLAIN ANALYZE, ја идентификувавме најбавната операција и применивме оптимизација. Кај пребарувањата по клуч тоа е индекс, а кај аналитичките погледи — материјализација.

Поглед 1 — v_site_zapisnici

Најбавна операција: Seq Scan on zapisnik (се чита целата табела). Решение — индекс на колоната по која се филтрира:

CREATE INDEX idx_zapisnik_prekrsuvach ON Zapisnik(EMBG_Prekrsuvach);

Прашалник кој го тестираме:

-- testirame za prekrsuvac so EMBG = '1508004480145'
SELECT * FROM v_site_zapisnici WHERE embg_prekrsuvach = '1508004480145';

Без индекс
време на извршување: 6m4s

По додавање на индекс
време на извршување: ~133ms

Поглед 2 — v_zapisnici_detalno

Најбавна операција: Parallel Seq Scan on uplata (~2.000.000 редови). Решение — индекси на join-колоните:

CREATE INDEX idx_stavka_zapisnik_id_zapisnik ON Stavka_Zapisnik(id_na_zapisnik);
CREATE INDEX idx_uplata_id_zapisnik          ON Uplata(id_zapisnik);

Прашалник кој го тестираме:

-- testirame za prekrsitel so EMBG = '0412992470302'
SELECT * FROM v_zapisnici_detalno WHERE embg_prekrsitel = '0412992470302';

Без индекс
време на извршување: 5m59s

По додавање на индекс
време на извршување: ~154ms

Поглед 3 — v_neplateni_kazni

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

Прашалник кој го тестираме:

-- testirame za prekrsitel so EMBG = '0412992470302'
SELECT * FROM v_neplateni_kazni WHERE embg_prekrsitel = '0412992470302';

Без индекс
време на извршување: 6,922s

По додавање на индекс
време на извршување: ~121ms

Поглед 4 — v_povtoreni_prekrsoci_mv

Аналитички поглед — GROUP BY / HashAggregate над цела Zapisnik, па индекс не помага. Решение: материјализација (дефиницијата е во делот Погледи).

Прашалник кој го тестираме:

-- testirame za graganin so ime = 'Виолета'
SELECT * FROM v_povtoreni_prekrsoci_mv WHERE ime = 'Виолета';

Пред (обичен аналитички поглед)
време на извршување: 1m8s

Потоа (материјализиран поглед)
Со материјализиран поглед добиваме 388ms време на читање.
Освежувањето (REFRESH) трае 1m27s.

REFRESH MATERIALIZED VIEW v_povtoreni_prekrsoci_mv;

Поглед 5 — v_policaici_prosek_zapisnici_mv

Аналитички поглед — агрегација (COUNT, MIN, MAX) групирана по полицаец над цела Zapisnik, индекс не носи добивка. Решение: материјализација (дефиницијата е во делот Погледи).

Прашалник кој го тестираме:

-- testirame za policaec so ime = 'Никола'
SELECT * FROM v_policaici_prosek_zapisnici_mv WHERE policaec_ime = 'Никола';

Пред (обичен аналитички поглед)
време на извршување: 1,5s

Потоа (материјализиран поглед)
Со материјализиран поглед добиваме <1s време на читање.
Освежувањето (REFRESH) трае <2s.

REFRESH MATERIALIZED VIEW v_policaici_prosek_zapisnici_mv;

Поглед 6 — v_istorija_gragjanin_mv

Аналитички поглед — две одделни агрегации (над Zapisnik/Stavka_Zapisnik и над Uplata) па join, двете страни се скенираат во целост. Решение: материјализација (дефиницијата е во делот Погледи).

Прашалник кој го тестираме:

-- testirame za graganin so ime = 'Оливера'
SELECT * FROM v_istorija_gragjanin_mv WHERE ime = 'Оливера';

Пред (обичен аналитички поглед)
време на извршување: 1m33s

Потоа (материјализиран поглед)
Со материјализиран поглед добиваме <1s време на читање.
Освежувањето (REFRESH) трае 2m17s.

REFRESH MATERIALIZED VIEW v_istorija_gragjanin_mv;

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	материјализиран поглед