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

1. Погледи

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

Поглед 1: v_site_zapisnici

Брз преглед на сите записници по лице, без join-ови.
Овој поглед овозможува брзо пронаоѓање на основните информации за издадените записници со цел оптимизација на почетните пребарувања. Наменет е за приказ на листи со записи во корисничкиот интерфејс каде што корисникот филтрира по прекршител или полицаец. Притоа, погледот извлекува податоци директно само од табелата Zapisnik, избегнувајќи сложени спојувања.
Се пребарува по: 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

Целосни детали за конкретен записник: прекршител, возило, полицаец, прекршок, износ на казната и статус на уплатата.
Погледот овозможува детален и сеопфатен приказ на сите информации поврзани со еден конкретен сообраќаен налог. Се користи во апликацијата кога операторот или граѓанинот сака да ги отвори сите детали за поединечен записник. За да се постигне ова, во view се спојува главната табела Zapisnik со табелите Gragjanin (за прекршителот и полицаецот), Vozilo, Policaec, Stavka_Zapisnik, Prekrsok, Kazna и Uplata.
Се пребарува по: 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

Сите неплатени казни на даден граѓанин, со износ за наплата и колку денови се веќе неплатени.
Овој поглед служи за брза финансиска проверка на прекршочните долгови кои сè уште не се подмирени од страна на граѓаните. Се користи при генерирање на налог за судска постапка, односно за неплатени обврски со пресметан рок на доцнење. Погледот се генерира преку филтрирано спојување на табелите Uplata, Zapisnik, Gragjanin, Stavka_Zapisnik, Prekrsok и Kazna.
Се пребарува по: 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)-колку пати, прв и последен пат, вкупен износ казни.
Ова е аналитички поглед наменет за следење и идентификување на прекршители, односно возачи кои постојано го повторуваат истиот прекршок. Се користи за статистички извештаи на МВР со цел таргетирање на критичните прекршители на патиштата. Погледот прави напредна агрегација и групирање со спојување на табелите Zapisnik, Stavka_Zapisnik, Prekrsok, Kazna и на крајот го врзува резултатот со табелата Gragjanin.
Се пребарува по: 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_policajci_prosek_zapisnici_mv

Статистики по полицаец: вкупно записници, прв/последен, активни месеци и просечен број записници по месец.
Погледот овозможува евалуација на ефикасноста и активноста на полициските службеници на терен низ подолг временски период. Наменет е за внатрешна контрола и менаџмент на полициските станици за следење на просечната продуктивност по месец. Се потпира на статистичка агрегација над табелата Zapisnik, која потоа се спојува со табелите Policaec и Gragjanin за комплетирање на профилот.
Се пребарува по: EMBG_P.

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

Целосен профил на граѓанин: вкупно прекршоци, платено, долг, прв/последен прекршок и број различни типови.
Овој поглед нуди целосно прекршочно и финансиско досие за секој граѓанин во системот на едно место. Се користи при пребарување на профил на граѓанин во апликацијата за веднаш да се прикаже неговата историја, вкупниот долг и направените уплати. Погледот комбинира и спојува две посебни агрегации од табелите Zapisnik, Stavka_Zapisnik, Prekrsok и Kazna од една страна, и табелата Uplata од друга страна, поврзувајќи ги на крај со табелата Gragjanin.
Се пребарува по: 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. Оптимизација

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

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