Changes between Version 10 and Version 11 of QueryOptimization
- Timestamp:
- 06/14/26 18:10:05 (5 days ago)
Legend:
- Unmodified
- Added
- Removed
- Modified
-
QueryOptimization
v10 v11 1 1 = Фаза 3: Индекси и оптимизација на прашалници = 2 3 4 5 2 6 3 [[BR]] … … 11 8 === Поглед 1: v_site_zapisnici === 12 9 '''Брз преглед на сите записници по лице, без join-ови.'''[[BR]] 10 Овој поглед овозможува брзо пронаоѓање на основните информации за издадените записници со цел оптимизација на почетните пребарувања. Наменет е за приказ на листи со записи во корисничкиот интерфејс каде што корисникот филтрира по прекршител или полицаец. Притоа, погледот извлекува податоци директно само од табелата `Zapisnik`, избегнувајќи сложени спојувања.[[BR]] 13 11 Се пребарува по: `EMBG_Prekrsuvach` (или `EMBG_Policaec`). 14 12 {{{#!sql 15 13 CREATE OR REPLACE VIEW v_site_zapisnici AS 16 14 SELECT id_na_zapisnik, datum, vreme, lokacija, Potpis, 17 15 id_slucaj, EMBG_Prekrsuvach, Vozilo_Broj_Sasija, EMBG_Policaec 18 16 FROM Zapisnik; 19 17 }}} 20 18 21 19 === Поглед 2: v_zapisnici_detalno === 22 '''Целосни детали за конкретен записник: прекршител, возило, полицаец, прекршок, износ казна и статус уплата.'''[[BR]] 20 '''Целосни детали за конкретен записник: прекршител, возило, полицаец, прекршок, износ на казната и статус на уплатата.'''[[BR]] 21 Погледот овозможува детален и сеопфатен приказ на сите информации поврзани со еден конкретен сообраќаен налог. Се користи во апликацијата кога операторот или граѓанинот сака да ги отвори сите детали за поединечен записник. За да се постигне ова, во view се спојува главната табела `Zapisnik` со табелите `Gragjanin` (за прекршителот и полицаецот), `Vozilo`, `Policaec`, `Stavka_Zapisnik`, `Prekrsok`, `Kazna` и `Uplata`.[[BR]] 23 22 Се пребарува по: `id_na_zapisnik` / `EMBG_Prekrsuvach`. 24 23 {{{#!sql 25 24 CREATE OR REPLACE VIEW v_zapisnici_detalno AS 26 25 SELECT z.id_na_zapisnik, z.datum, z.vreme, z.lokacija, z.id_slucaj, 27 28 29 30 26 g.EMBG AS embg_prekrsitel, g.ime AS prekrsitel_ime, g.prezime AS prekrsitel_prezime, 27 v.broj_na_sasija, v.model AS vozilo_model, 28 pol.EMBG_P AS embg_policaec, g_p.ime AS policaec_ime, g_p.prezime AS policaec_prezime, 29 p.ime AS prekrsok, k.iznos_kazna, u.iznos AS iznos_uplata, u.status AS status_uplata 31 30 FROM Zapisnik z 32 31 LEFT JOIN Gragjanin g ON z.EMBG_Prekrsuvach = g.EMBG … … 42 41 === Поглед 3: v_neplateni_kazni === 43 42 '''Сите неплатени казни на даден граѓанин, со износ за наплата и колку денови се веќе неплатени.'''[[BR]] 43 Овој поглед служи за брза финансиска проверка на прекршочните долгови кои сè уште не се подмирени од страна на граѓаните. Се користи при генерирање на налог за судска постапка, односно за неплатени обврски со пресметан рок на доцнење. Погледот се генерира преку филтрирано спојување на табелите `Uplata`, `Zapisnik`, `Gragjanin`, `Stavka_Zapisnik`, `Prekrsok` и `Kazna`.[[BR]] 44 44 Се пребарува по: `EMBG_prekrsitel`. 45 45 {{{#!sql 46 46 CREATE OR REPLACE VIEW v_neplateni_kazni AS 47 47 SELECT z.id_na_zapisnik, z.datum AS datum_prekrsok, z.lokacija, 48 49 50 48 g.EMBG AS embg_prekrsitel, g.ime AS ime_prekrsitel, g.prezime AS prezime_prekrsitel, 49 p.ime AS prekrsok, k.iznos_kazna, u.iznos AS iznos_za_naplata, 50 (CURRENT_DATE - z.datum) AS denovi_neplateno 51 51 FROM Uplata u 52 52 JOIN Zapisnik z ON u.id_zapisnik = z.id_na_zapisnik … … 59 59 60 60 === Поглед 4: v_povtoreni_prekrsoci_mv === 61 '''Граѓани што повторуваат ист тип прекршок (`COUNT > 1`) — колку пати, прв и последен пат, вкупен износ казни.'''[[BR]] 61 '''Граѓани што повторуваат ист тип прекршок (COUNT > 1)-колку пати, прв и последен пат, вкупен износ казни.'''[[BR]] 62 Ова е аналитички поглед наменет за следење и идентификување на прекршители, односно возачи кои постојано го повторуваат истиот прекршок. Се користи за статистички извештаи на МВР со цел таргетирање на критичните прекршители на патиштата. Погледот прави напредна агрегација и групирање со спојување на табелите `Zapisnik`, `Stavka_Zapisnik`, `Prekrsok`, `Kazna` и на крајот го врзува резултатот со табелата `Gragjanin`.[[BR]] 62 63 Се пребарува по: `EMBG`. 63 64 {{{#!sql 64 65 CREATE MATERIALIZED VIEW v_povtoreni_prekrsoci_mv AS 65 66 WITH Prekrsoci_Stats AS ( 66 67 COUNT(*) AS broj_povtori, MIN(z.datum) AS prv_pat, MAX(z.datum) AS posleden_pat,68 69 70 71 72 73 74 HAVING COUNT(*) > 167 SELECT z.EMBG_Prekrsuvach, sz.id_na_prekrsok, 68 COUNT() AS broj_povtori, MIN(z.datum) AS prv_pat, MAX(z.datum) AS posleden_pat, 69 SUM(k.iznos_kazna) AS vkupen_iznos_kazni 70 FROM Zapisnik z 71 JOIN Stavka_Zapisnik sz ON z.id_na_zapisnik = sz.id_na_zapisnik 72 JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok 73 JOIN Kazna k ON p.id_kazna = k.id_kazna 74 GROUP BY z.EMBG_Prekrsuvach, sz.id_na_prekrsok 75 HAVING COUNT() > 1 75 76 ) 76 77 SELECT g.EMBG, g.ime, g.prezime, p.id_prekrsok, p.ime AS prekrsok, 77 78 ps.broj_povtori, ps.prv_pat, ps.posleden_pat, ps.vkupen_iznos_kazni 78 79 FROM Prekrsoci_Stats ps 79 80 JOIN Gragjanin g ON ps.EMBG_Prekrsuvach = g.EMBG … … 83 84 === Поглед 5: v_policaici_prosek_zapisnici_mv === 84 85 '''Статистики по полицаец: вкупно записници, прв/последен, активни месеци и просечен број записници по месец.'''[[BR]] 85 Се пребарува по: `EMBG_P` (дополнително по име на станица). 86 Погледот овозможува евалуација на ефикасноста и активноста на полициските службеници на терен низ подолг временски период. Наменет е за внатрешна контрола и менаџмент на полициските станици за следење на просечната продуктивност по месец. Се потпира на статистичка агрегација над табелата `Zapisnik`, која потоа се спојува со табелите `Policaec` и `Gragjanin` за комплетирање на профилот.[[BR]] 87 Се пребарува по: EMBG_P. 86 88 {{{#!sql 87 89 CREATE MATERIALIZED VIEW v_policaici_prosek_zapisnici_mv AS 88 90 WITH Policaec_Stats AS ( 89 90 91 92 91 SELECT EMBG_Policaec, COUNT(id_na_zapisnik) AS vkupno_zapisnici, 92 MIN(datum) AS prv_zapisnik, MAX(datum) AS posleden_zapisnik 93 FROM Zapisnik 94 GROUP BY EMBG_Policaec 93 95 ) 94 96 SELECT pol.EMBG_P, pol.broj_na_znacka, g.ime AS policaec_ime, g.prezime AS policaec_prezime, 95 96 97 98 99 100 97 ps.vkupno_zapisnici, ps.prv_zapisnik, ps.posleden_zapisnik, 98 GREATEST(DATE_PART('year', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)) * 12 + 99 DATE_PART('month', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)), 1) AS aktivni_meseci, 100 ROUND(ps.vkupno_zapisnici::numeric / 101 GREATEST(DATE_PART('year', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)) * 12 + 102 DATE_PART('month', AGE(ps.posleden_zapisnik, ps.prv_zapisnik)), 1)::numeric, 2) AS prosek_zapisnici_po_mesec 101 103 FROM Policaec_Stats ps 102 104 JOIN Policaec pol ON ps.EMBG_Policaec = pol.EMBG_P … … 106 108 === Поглед 6: v_istorija_gragjanin_mv === 107 109 '''Целосен профил на граѓанин: вкупно прекршоци, платено, долг, прв/последен прекршок и број различни типови.'''[[BR]] 110 Овој поглед нуди целосно прекршочно и финансиско досие за секој граѓанин во системот на едно место. Се користи при пребарување на профил на граѓанин во апликацијата за веднаш да се прикаже неговата историја, вкупниот долг и направените уплати. Погледот комбинира и спојува две посебни агрегации од табелите `Zapisnik`, `Stavka_Zapisnik`, `Prekrsok` и `Kazna` од една страна, и табелата `Uplata` од друга страна, поврзувајќи ги на крај со табелата `Gragjanin`.[[BR]] 108 111 Се пребарува по: `EMBG`. 109 112 {{{#!sql 110 113 CREATE MATERIALIZED VIEW v_istorija_gragjanin_mv AS 111 114 WITH Zapisnik_Stats AS ( 112 113 114 115 116 117 118 119 120 121 122 115 SELECT EMBG_Prekrsuvach, 116 COUNT(DISTINCT z.id_na_zapisnik) AS vkupno_zapisnici, 117 MIN(z.datum) AS prv_prekrsok, MAX(z.datum) AS posleden_prekrsok, 118 COUNT(sz.id_stavka) AS vkupno_prekrshoci, 119 COUNT(DISTINCT sz.id_na_prekrsok) AS razlichni_tipovi, 120 COALESCE(SUM(k.iznos_kazna), 0) AS vkupen_iznos_kazni 121 FROM Zapisnik z 122 LEFT JOIN Stavka_Zapisnik sz ON z.id_na_zapisnik = sz.id_na_zapisnik 123 LEFT JOIN Prekrsok p ON sz.id_na_prekrsok = p.id_prekrsok 124 LEFT JOIN Kazna k ON p.id_kazna = k.id_kazna 125 GROUP BY EMBG_Prekrsuvach 123 126 ), 124 127 Uplata_Stats AS ( 125 126 127 128 129 130 131 128 SELECT z.EMBG_Prekrsuvach, 129 COALESCE(SUM(CASE WHEN u.status = 'Plateno' THEN u.iznos END), 0) AS vkupno_plateno, 130 COALESCE(SUM(CASE WHEN u.status = 'Neplateno' THEN u.iznos END), 0) AS vkupen_dolg, 131 COUNT(CASE WHEN u.status = 'Neplateno' THEN u.id_uplata END) AS broj_neplateni 132 FROM Uplata u 133 JOIN Zapisnik z ON u.id_zapisnik = z.id_na_zapisnik 134 GROUP BY z.EMBG_Prekrsuvach 132 135 ) 133 136 SELECT g.EMBG, g.ime, g.prezime, g.datum_ragjanje, 134 135 136 137 138 137 zs.vkupno_zapisnici, zs.vkupno_prekrshoci, zs.razlichni_tipovi, zs.vkupen_iznos_kazni, 138 COALESCE(us.vkupno_plateno, 0) AS vkupno_plateno, 139 COALESCE(us.vkupen_dolg, 0) AS vkupen_dolg, 140 COALESCE(us.broj_neplateni, 0) AS broj_neplateni, 141 zs.prv_prekrsok, zs.posleden_prekrsok 139 142 FROM Gragjanin g 140 143 JOIN Zapisnik_Stats zs ON g.EMBG = zs.EMBG_Prekrsuvach … … 142 145 }}} 143 146 144 145 147 [[BR]] 146 148 == 2. Оптимизација == 147 За секој поглед го измеривме почетното време преку `EXPLAIN ANALYZE`, ја идентификувавме најбавната операција и применивме оптимизација. Кај пребарувањата по клуч тоа е индекс, а кај аналитичките погледи —материјализација.148 149 === Поглед 1 —v_site_zapisnici ===150 Најбавна операција: `Seq Scan on zapisnik` (се чита целата табела). Решение —индекс на колоната по која се филтрира:149 За секој поглед го измеривме почетното време, ја идентификувавме најбавната операција и применивме оптимизација. Кај пребарувањата по клуч тоа е индекс, а кај аналитичките погледи-материјализација. 150 151 === Поглед 1: v_site_zapisnici === 152 Најбавна операција: `Seq Scan` on `Zapisnik` (се чита целата табела). Решение: индекс на колоната по која се филтрира: 151 153 {{{#!sql 152 154 CREATE INDEX idx_zapisnik_prekrsuvach ON Zapisnik(EMBG_Prekrsuvach); … … 167 169 [[Image(vew1-index.png, 700px)]] 168 170 169 === Поглед 2 —v_zapisnici_detalno ===170 Најбавна операција: `Parallel Seq Scan on uplata` (~2.000.000 редови). Решение —индекси на join-колоните:171 === Поглед 2: v_zapisnici_detalno === 172 Најбавна операција: Parallel Seq Scan on uplata (~2.000.000 редови). [[BR]] [[BR]]Решение: индекси на join-колоните: 171 173 {{{#!sql 172 174 CREATE INDEX idx_stavka_zapisnik_id_zapisnik ON Stavka_Zapisnik(id_na_zapisnik); … … 188 190 [[Image(view2-index.png, 700px)]] 189 191 190 === Поглед 3 —v_neplateni_kazni ===191 Најбавна операција: `Seq Scan on uplata`. Join-условот (`u.id_zapisnik = z.id_na_zapisnik`) е ист како кај Поглед 2, затоа не е потребен нов индекс — се реискористува постоечкиот `idx_uplata_id_zapisnik`.192 === Поглед 3: v_neplateni_kazni === 193 Најбавна операција: Seq Scan on uplata. Join-условот (u.id_zapisnik = z.id_na_zapisnik) е ист како кај Поглед 2, затоа не е потребен нов индекс, се реискористува постоечкиот idx_uplata_id_zapisnik. 192 194 193 195 '''Прашалник кој го тестираме:''' … … 205 207 [[Image(view3-index.png, 700px)]] 206 208 207 === Поглед 4 —v_povtoreni_prekrsoci_mv ===208 Аналитички поглед — `GROUP BY` / `HashAggregate` над цела `Zapisnik`, па индекс не помага. Решение: материјализација (дефиницијата е во делот Погледи).209 === Поглед 4: v_povtoreni_prekrsoci_mv === 210 Аналитички поглед-GROUP BY / HashAggregate над цела Zapisnik, па индекс не помага. Решение: материјализација. 209 211 210 212 '''Прашалник кој го тестираме:''' … … 219 221 220 222 '''Потоа''' (материјализиран поглед)[[BR]] 221 Со материјализиран поглед добиваме 388ms време на читање. 223 време на извршување: 388ms 222 224 [[Image(view4-materialized.png, 700px)]] [[BR]] 223 Освежувањето ( `REFRESH`) трае 1m27s.225 Освежувањето (REFRESH) трае 1m27s. 224 226 {{{#!sql 225 227 REFRESH MATERIALIZED VIEW v_povtoreni_prekrsoci_mv; 226 228 }}} 227 229 228 === Поглед 5 —v_policaici_prosek_zapisnici_mv ===229 Аналитички поглед — агрегација (`COUNT`, `MIN`, `MAX`) групирана по полицаец над цела `Zapisnik`, индекс не носи добивка. Решение: материјализација (дефиницијата е во делот Погледи).230 === Поглед 5: v_policaici_prosek_zapisnici_mv === 231 Аналитички поглед-агрегација (COUNT, MIN, MAX) групирана по полицаец над цела Zapisnik, индекс не носи добивка. Решение: материјализација. 230 232 231 233 '''Прашалник кој го тестираме:''' … … 240 242 241 243 '''Потоа''' (материјализиран поглед)[[BR]] 242 Со материјализиран поглед добиваме <1s време на читање. 244 време на извршување: <1s 243 245 [[Image(view5-materialized.png, 700px)]] [[BR]] 244 Освежувањето ( `REFRESH`) трае <2s.246 Освежувањето (REFRESH) трае <2s. 245 247 {{{#!sql 246 248 REFRESH MATERIALIZED VIEW v_policaici_prosek_zapisnici_mv; 247 249 }}} 248 250 249 === Поглед 6 —v_istorija_gragjanin_mv ===250 Аналитички поглед — две одделни агрегации (над `Zapisnik`/`Stavka_Zapisnik` и над `Uplata`) па join, двете страни се скенираат во целост. Решение: материјализација (дефиницијата е во делот Погледи).251 === Поглед 6: v_istorija_gragjanin_mv === 252 Аналитички поглед-две одделни агрегации (над Zapisnik/Stavka_Zapisnik и над Uplata) па join, двете страни се скенираат во целост. Решение: материјализација. 251 253 252 254 '''Прашалник кој го тестираме:''' … … 261 263 262 264 '''Потоа''' (материјализиран поглед)[[BR]] 263 Со материјализиран поглед добиваме <1s време на читање. 265 време на извршување: <1s 264 266 [[Image(view6-materialized.png, 700px)]] [[BR]] 265 Освежувањето ( `REFRESH`) трае 2m17s.267 Освежувањето (REFRESH) трае 2m17s. 266 268 {{{#!sql 267 269 REFRESH MATERIALIZED VIEW v_istorija_gragjanin_mv; 268 270 }}} 269 270 271 271 272 [[BR]]
