Changes between Initial Version and Version 1 of QueryOptimization

Timestamp:

06/12/26 19:54:29 (9 days ago)

Author:

231025

Comment:

--

QueryOptimization

@@ +1 @@
+= Фаза 3: Индекси и оптимизација на прашалници =
+
+''' Членови на тимот '''
+* Матеа Ѓоргоска 231069 (координатор)
+* Ана Марија Кукулева 231025
+
+
+[[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. Оптимизација ==
+За секој поглед го измеривме почетното време преку `EXPLAIN ANALYZE`, ја идентификувавме најбавната операција и применивме оптимизација. Кај пребарувањата по клуч тоа е индекс, а кај аналитичките погледи — материјализација.
+
+=== Поглед 1 — v_site_zapisnici ===
+Најбавна операција: `Seq Scan on zapisnik` (се чита целата табела). Решение — индекс на колоната по која се филтрира:
+{{{#!sql
+CREATE INDEX idx_zapisnik_prekrsuvach ON Zapisnik(EMBG_Prekrsuvach);
+}}}
+
+'''Explain план (пред / по индекс):'''
+[[Image(view1.png, 700px)]]
+[[Image(vew1-index.png, 700px)]]
+
+'''Времиња на извршување:'''
+* SELECT: 6m4s → '''~133ms'''
+* INSERT: 1,699s → '''0,066s'''
+* UPDATE: 0,081s → '''0,055s'''
+
+=== Поглед 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);
+}}}
+
+'''Explain план (пред / по индекс):'''
+[[Image(view2.png, 700px)]]
+[[Image(view2-index.png, 700px)]]
+
+'''Времиња на извршување:'''
+* SELECT: 5m59s → '''~154ms'''
+* INSERT: 3,962s → '''0,230s'''
+* UPDATE: 3,950s → '''0,053s'''
+
+=== Поглед 3 — v_neplateni_kazni ===
+Најбавна операција: `Seq Scan on uplata`. Join-условот (`u.id_zapisnik = z.id_na_zapisnik`) е ист како кај Поглед 2, '''па не е потребен нов индекс''' — се реискористува постоечкиот `idx_uplata_id_zapisnik`.
+
+'''Explain план (пред / по индекс):'''
+[[Image(view3.png, 700px)]]
+[[Image(view3-index.png, 700px)]]
+
+'''Времиња на извршување:'''
+* SELECT: 6,922s → '''~121ms'''
+* INSERT: 0,153s → '''0,134s'''
+* UPDATE: 1,990s → '''0,031s'''
+
+=== Поглед 4 — v_povtoreni_prekrsoci ===
+Аналитички поглед. План: `GROUP BY` / `HashAggregate` над цела `Zapisnik` — агрегацијата ги поминува сите редови, индекс не помага. Решение — '''материјализиран поглед'''; INSERT/UPDATE на основните табели остануваат исти.
+
+'''Explain план:'''
+[[Image(view4.png, 700px)]]
+
+'''Време на извршување:''' ~4,917s
+
+=== Поглед 5 — v_policaici_prosek_zapisnici ===
+Аналитички поглед. План: агрегација (`COUNT`, `MIN`, `MAX`) групирана по полицаец над цела `Zapisnik` — индекс не носи добивка. Решение — '''материјализиран поглед''' со периодично освежување.
+
+'''Explain план:'''
+[[Image(view5.png, 700px)]]
+
+'''Време на извршување:''' ~3,234s
+
+=== Поглед 6 — v_istorija_gragjanin ===
+Аналитички поглед. План: две одделни агрегации (над `Zapisnik`/`Stavka_Zapisnik` и над `Uplata`) па join — двете страни се скенираат во целост. Решение — '''материјализиран поглед''' со периодично освежување.
+
+'''Explain план:'''
+[[Image(view6.png, 700px)]]
+
+'''Време на извршување:''' ~9,076s
+
+
+[[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 || — || материјализација ||