Changes between Version 1 and Version 2 of QueryOptimization


Ignore:
Timestamp:
05/19/26 22:51:52 (7 days ago)
Author:
231071
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v1 v2  
    33Во оваа фаза ќе ги анализираме погледите дефинирани во [[DatabaseCreation|Фаза 2]] преку прашалници базирани на реални сценарија кои ќе бидат присутни во нашата апликација и истите ќе се обидеме да ги оптимизираме.
    44
    5 === 1. Анализа на поглед 1, vw_party_demographic_performance — демографска анализа на поддршката по партии ===
     5=== View 1. vw_election_results ===
    66
    7 Овој поглед овозможува длабинска декомпозиција на изборните резултати преку призмата на демографските карактеристики на гласачкото тело. Наместо едноставен линеарен приказ на победниците, погледот ја открива структурата и профилот на гласачите кои ја генерираат поддршката за секој политички субјект. Преку статистичко поврзување на кастираните гласови со старосната структура на чекираните гласачи, се генерира прецизен увид во електоралната поддршка поделена по генерации (Gen Z, Миленијалци и Сениори).
    87
    9 За максимална оптимизација на перформансите при пребарувањето, погледот е дизајниран да се филтрира според две основни колони: `election_name` (за избор на конкретен изборен циклус) и `party_name` (за селекција на одреден политички ентитет).
    10 
    11 Дефиниција на погледот:
    12 {{{
    13 CREATE OR REPLACE VIEW public.vw_party_demographic_performance AS
    14 WITH VoterDemographics AS (
    15     SELECT
    16         ve.election_id,
    17         ve.station_id,
    18         p.date_of_birth,
    19         EXTRACT(YEAR FROM p.date_of_birth) as birth_year
    20     FROM public.voter_election ve
    21     JOIN public.voter v ON ve.voter_id = v.voter_id
    22     JOIN public.person p ON v.person_id = p.person_id
    23 ),
    24 BallotStats AS (
    25     SELECT
    26         b.election_id,
    27         b.station_id,
    28         b.entity_id,
    29         b.ballot_id
    30     FROM public.ballot b
    31     WHERE b.is_valid = true
    32 )
    33 SELECT
    34     e.name AS election_name,
    35     r.name AS region_name,
    36     pe.name AS party_name,
    37     ps.name AS station_name,
    38     COALESCE(COUNT(CASE WHEN vd.birth_year >= 1996 THEN 1 END), 0) AS gen_z_votes,
    39     COALESCE(COUNT(CASE WHEN vd.birth_year >= 1981 AND vd.birth_year <= 1995 THEN 1 END), 0) AS millennial_votes,
    40     COALESCE(COUNT(CASE WHEN vd.birth_year < 1981 THEN 1 END), 0) AS senior_votes,
    41     COUNT(bs.ballot_id) AS total_valid_votes
    42 FROM BallotStats bs
    43 JOIN public.election e ON bs.election_id = e.election_id
    44 JOIN public.polling_station ps ON bs.station_id = ps.station_id
    45 JOIN public.region r ON ps.municipality_id = r.region_id
    46 JOIN public.political_entity pe ON bs.entity_id = pe.entity_id
    47 LEFT JOIN VoterDemographics vd ON bs.election_id = vd.election_id
    48                              AND bs.station_id = vd.station_id
    49 GROUP BY e.name, r.name, pe.name, ps.name;
    50 }}}
    51 
    52 Прашалникот кој ќе го тестираме:
    53 {{{
    54 SELECT * FROM vw_party_demographic_performance
    55 WHERE election_name = 'Parliamentary Elections 2014'
    56   AND party_name = 'VMRO-DPMNE';
    57 }}}
    58 
    59 ==== Време на извршување без индекси:
    60 
    61 **3415.820 ms (~3.4 секунди)**
    62 
    63 Времето на извршување е неприфатливо, особено при истовремена работа на повеќе корисници. Извршниот план покажува:
    64 - Parallel Seq Scan над `ballot`
    65 - Hash Join и Nested Loop операции
    66 
    67 Ова укажува дека PostgreSQL чита голем број редови поради недостаток на соодветни индекси. Проблемот со времето на извршување може да се подобри со индекси.
    68 
    69 Со стратешка имплементација на соодветни индекси врз клучните колони во базата, времето на извршување на оваа комплексна демографска агрегација беше успешно оптимизирано.
    70 
    71 ==== Додадени индекси:
    72 
    73 {{{
    74 CREATE INDEX idx_ballot_election_station ON ballot(election_id, station_id);
    75 CREATE INDEX idx_ballot_entity_valid ON ballot(entity_id) WHERE is_valid = true;
    76 CREATE INDEX idx_voter_election_ids ON voter_election(election_id, station_id, voter_id);
    77 CREATE INDEX idx_polling_station_municipality ON polling_station(municipality_id);
    78 }}}
    79 
    80 ==== Време на извршување со индекси:
    81 
    82 **619 ms** (беше 3415.820 ms)
    83 
    84 Подобрување: ~5.5x побрзо.
    858
    869---
    8710
    88 === 2. Анализа на поглед 2, vw_local_election_winners — победници на локални избори ===
     11=== View 2. vw_dhondt_seat_allocation ===
    8912
    90 Овој поглед овозможува преглед на победниците (кандидати или партии со најмногу гласови) на локално ниво, поделени по изборни единици или општини. Погледот ги сумира гласовите и ги идентификува субјектите со највисок резултат за секоја локација.
    91 
    92 Примарен филтер: `election_id` или `election_year` (за да се видат победниците на конкретни избори). Секундарен филтер: `municipality_name` или `station_id`.
    93 
    94 Дефиниција на погледот:
    95 {{{
    96 CREATE OR REPLACE VIEW public.vw_local_election_winners AS
    97 WITH totalvotesperparty AS (
    98     SELECT vr.election_id, e.name AS election_name, r.name AS municipality_name,
    99            pe.name AS party_name, sum(vr.votes) AS total_sum_votes
    100     FROM vote_result      vr
    101     JOIN election         e  ON vr.election_id = e.election_id
    102     JOIN political_entity pe ON vr.entity_id   = pe.entity_id
    103     JOIN polling_station  ps ON vr.station_id  = ps.station_id
    104     JOIN region           r  ON ps.municipality_id = r.region_id
    105     WHERE e.election_type_id = 3
    106     GROUP BY vr.election_id, e.name, r.name, pe.name
    107 ),
    108 rankedwinners AS (
    109     SELECT *, rank() OVER (PARTITION BY election_id, municipality_name ORDER BY total_sum_votes DESC) AS rnk
    110     FROM totalvotesperparty
    111 )
    112 SELECT election_name, municipality_name,
    113        party_name           AS winner_name,
    114        total_sum_votes       AS votes_received,
    115        'Winner'::text        AS status
    116 FROM rankedwinners
    117 WHERE rnk = 1;
    118 }}}
    119 
    120 Прашалникот кој ќе го тестираме:
    121 {{{
    122 SELECT * FROM vw_local_election_winners
    123 WHERE election_name = 'Local Elections 2021';
    124 }}}
    125 
    126 ==== Време на извршување без индекси:
    127 
    128 **3500 ms (~3.5 секунди)**
    129 
    130 Времето на извршување не е прифатливо. Планот покажува дека базата троши премногу време на Incremental Sort и WindowAgg операциите за да ги рангира победниците меѓу 180,000 редови. Ова укажува на потреба од индекси за да се избегнат вгнездените циклуси (Nested Loops) и да се забрза пребарувањето низ табелите.
    131 
    132 ==== Додадени индекси:
    133 
    134 {{{
    135 CREATE INDEX idx_vote_result_election_entity ON vote_result(election_id, entity_id);
    136 CREATE INDEX idx_vote_result_station ON vote_result(station_id);
    137 CREATE INDEX idx_election_type ON election(election_type_id);
    138 }}}
    139 
    140 ==== Време на извршување со индекси:
    141 
    142 **290 ms** (беше ~3500 ms)
    143 
    144 Иако со дополнително индексирање времето се стабилизираше на околу 290 ms, ова се смета за оптимален резултат за овој тип на комплексен аналитички поглед. Ова време е резултат на балансот помеѓу ефикасното пребарување низ индексите и потребата на базата да изврши агрегација и рангирање врз голема количина податоци во реално време.
    14513
    14614---
    14715
    148 === 3. Анализа на поглед 3, vw_parliamentary_municipality_winners — победници на парламентарни избори по општини ===
    149 
    150 Овој поглед служи за детална анализа на изборните резултати од парламентарните избори на ниво на општина. Тој ги агрегира гласовите за секој политички субјект во секоја општина и го идентификува победникот (партијата со најголем број гласови) за таа територијална единица.
    151 
    152 Примарен филтер: `election_name` (на пр. `'Parliamentary Elections 2014'`). Секундарен филтер: `municipality_name`.
    153 
    154 Дефиниција на погледот:
    155 {{{
    156 CREATE OR REPLACE VIEW public.vw_parliamentary_municipality_winners AS
    157 WITH aggregatedvotes AS (
    158     SELECT e.election_id, e.name AS election_name, r.name AS municipality_name,
    159            pe.name AS party_name, sum(vr.votes) AS total_votes
    160     FROM vote_result      vr
    161     JOIN election         e  ON vr.election_id    = e.election_id
    162     JOIN political_entity pe ON vr.entity_id      = pe.entity_id
    163     JOIN polling_station  ps ON vr.station_id     = ps.station_id
    164     JOIN region           r  ON ps.municipality_id = r.region_id
    165     WHERE e.election_type_id = 2
    166     GROUP BY e.election_id, e.name, r.name, pe.name
    167 ),
    168 rankedwinners AS (
    169     SELECT *, rank() OVER (PARTITION BY election_id, municipality_name ORDER BY total_votes DESC) AS pos
    170     FROM aggregatedvotes
    171 )
    172 SELECT election_name, municipality_name,
    173        party_name          AS dominant_party,
    174        total_votes         AS winning_votes,
    175        'Municipality Winner'::text AS status
    176 FROM rankedwinners
    177 WHERE pos = 1;
    178 }}}
    179 
    180 Прашалникот кој ќе го тестираме:
    181 {{{
    182 SELECT * FROM vw_parliamentary_municipality_winners
    183 WHERE election_name = 'Parliamentary Elections 2014';
    184 }}}
    185 
    186 ==== Време на извршување без индекси:
    187 
    188 **17900 ms (~17.9 секунди)**
    189 
    190 Времето на извршување воопшто не е прифатливо. Според приложениот извршен план, прашалникот трае 17.9 секунди главно поради користењето на дискот за сортирање податоци и големиот број на меѓу-резултати (скоро 1 милион редови) кои се префрлаат помеѓу паралелните процеси. Без соодветни индекси, базата не може да ги избегне овие скапи операции.
    191 
    192 ==== Додадени индекси:
    193 
    194 {{{
    195 CREATE INDEX idx_vote_result_election_entity ON vote_result(election_id, entity_id);
    196 CREATE INDEX idx_vote_result_station ON vote_result(station_id);
    197 CREATE INDEX idx_election_type ON election(election_type_id);
    198 CREATE INDEX idx_polling_station_municipality ON polling_station(municipality_id);
    199 }}}
    200 
    201 ==== Време на извршување со индекси:
    202 
    203 **1500 ms** (беше 17900 ms)
    204 
    205 По извршената финална оптимизација и консолидација на индексите, времето на извршување е драстично намалено од 17.9 секунди на 1.5 секунди. Подобрување: ~12x побрзо.
     16=== View 3. vw_invalid_ballot_analysis ===
    20617
    20718---
    20819
    209 === 4. Анализа на поглед 4, vw_presidential_by_municipality — претседателски гласови по општини ===
     20=== View 4. vw_local_election_winners ===
    21021
    211 Погледот овозможува детален приказ на гласовите освоени од претседателските кандидати, групирани по општина. Тој ги агрегира податоците од табелата `vote_result` и ги поврзува со податоците за кандидатите и општините за да генерира извештај за поддршката на кандидатите на локално ниво.
     221.      Овој поглед  овозможува преглед на победниците (кандидати или партии со најмногу гласови) на локално ниво, поделени по изборни единици или општини. Погледот ги сумира гласовите од табелата ballot и ги идентификува субјектите со највисок резултат за секоја локација.
     232.      Примарен филтер: election_id или election_year (за да се видат победниците на конкретни избори). Секундарен филтер: municipality_name или station_id (доколку корисникот сака да види кој победил во точно одредена општина или место).
     243.      Иницијално време на извршување на погледот е 3.5 секунди , што не е прифатливо.
    21225
    213 Овој извештај ќе се користи за визуелизација на географската распределба на гласовите, каде преку филтрирање по `election_name` и `municipality_name` корисникот може моментално да ги добие финалните резултати за специфичен регион.
     26[[File:4.png]]
    21427
    215 Дефиниција на погледот:
    216 {{{
    217 CREATE OR REPLACE VIEW public.vw_presidential_by_municipality AS
    218 WITH cityvotes AS (
    219     SELECT e.name AS election_name, r.name AS municipality_name,
    220            pe.name AS candidate_name, sum(vr.votes) AS total_votes
    221     FROM vote_result      vr
    222     JOIN election         e  ON vr.election_id    = e.election_id
    223     JOIN political_entity pe ON vr.entity_id      = pe.entity_id
    224     JOIN polling_station  ps ON vr.station_id     = ps.station_id
    225     JOIN region           r  ON ps.municipality_id = r.region_id
    226     WHERE e.election_type_id = 1
    227     GROUP BY e.name, r.name, pe.name
    228 ),
    229 rankedcities AS (
    230     SELECT *, rank() OVER (PARTITION BY election_name, municipality_name ORDER BY total_votes DESC) AS pos
    231     FROM cityvotes
    232 )
    233 SELECT election_name, municipality_name, candidate_name,
    234        total_votes, 'City Winner'::text AS level
    235 FROM rankedcities
    236 WHERE pos = 1;
    237 }}}
     284.      Планот покажува дека базата троши премногу време на Incremental Sort и WindowAgg операциите за да ги рангира победниците меѓу 180,000 редови. Ова укажува на потреба од индекси за да се избегнат вгнездените циклуси (Nested Loops) и да се забрза пребарувањето низ табелите.
    23829
    239 Прашалникот кој ќе го тестираме:
    240 {{{
    241 SELECT * FROM vw_presidential_by_municipality
    242 WHERE election_name = 'Presidential Elections 2019'
    243   AND municipality_name = 'Skopje';
    244 }}}
     305.      Проблемот може да се поправи со додавање на соодветни индекси.
    24531
    246 ==== Време на извршување:
     32[[File:5.png]]
    24733
    248 **15 ms**
     346.      Иако со дополнително индексирање времето се стабилизираше на околу 290 ms, ова се смета за оптимален резултат за овој тип на комплексен аналитички поглед. Ова време е резултат на балансот помеѓу ефикасното пребарување низ индексите и потребата на базата да изврши агрегација и рангирање врз голема количина податоци во реално време.
    24935
    250 При извршување на погледот за претседателски избори по општини, постигнати се врвни перформанси со време на одговор од само 15 милисекунди. Овој резултат е директна последица на претходно имплементираната стратегија за индексирање (индексите додадени за претходните погледи). Ова време е прифатливо па затоа нема да имаме потреба од дополнително индексирање.
     36[[File:6.png]]
     37[[File:7.png]]
    25138
    25239---
    25340
    254 === 5. Анализа на поглед 5, vw_polling_station_voter_count — број на регистрирани гласачи по гласачко место ===
     41=== View 5. vw_parliamentary_municipality_winners ===
    25542
    256 Погледот служи за пресметување на вкупниот број на регистрирани гласачи за секое поединечно гласачко место. Наместо да се чуваат статични податоци во табелата `polling_station` (што би довело до редундантност и потенцијални аномалии), овој поглед врши агрегација во реално време врз основа на поврзаноста помеѓу гласачите и нивните доделени избирачки места.
     431.      Овој поглед служи за детална анализа на изборните резултати од парламентарните избори на ниво на општина. Тој ги агрегира гласовите за секој политички субјект во секоја општина и го идентификува победникот (партијата со најголем број гласови) за таа територијална единица.
     442.      Примарен филтер: election_name (на пр. 'Parliamentary Elections 2014'). Секундарен филтер: municipality_name (доколку сакаме да видиме кој победил во одредена општина на државно ниво).
     453.      Иницијално време на извршување на погледот е  скоро 17 секунди  , што воопшто не е прифатливо.
    25746
    258 Ќе се користи за проверка дали одредено гласачко место има преголем број гласачи (надминување на капацитетот). Најчесто ќе се користи со филтер по `station_id` или `municipality_name`.
     47[[File:8.png]]
    25948
    260 Дефиниција на погледот:
    261 {{{
    262 CREATE OR REPLACE VIEW public.vw_polling_station_voter_count AS
    263 SELECT r.name  AS region_name,
    264        ps.name AS station_name,
    265        count(v.voter_id) AS actual_registered_voters
    266 FROM polling_station  ps
    267 JOIN region           r  ON ps.municipality_id = r.region_id
    268 LEFT JOIN voter       v  ON ps.station_id      = v.station_id
    269 GROUP BY r.name, ps.name;
    270 }}}
    27149
    272 Прашалникот кој ќе го тестираме:
    273 {{{
    274 SELECT * FROM vw_polling_station_voter_count
    275 WHERE region_name = 'Куманово';
    276 }}}
     504.      Според приложениот извршен план, кверито трае 17.9 секунди главно поради користењето на дискот за сортирање податоци и големиот број на меѓу-резултати (скоро 1 милион редови) кои се префрлаат помеѓу паралелните процеси. Без соодветни индекси, базата не може да ги избегне овие скапи операции.
    27751
    278 ==== Време на извршување без индекси:
     52[[File:9.png]]
    27953
    280 **3500 ms (~3.5 секунди)**
     545.      По извршената финална оптимизација и консолидација на индексите, времето на извршување на комплексниот поглед за парламентарни победи е драстично намалено од 17.9 секунди на 1.5 секунди.
    28155
    282 При тестирање на погледот со филтер за општина Куманово, постигнато е почетно време на извршување од 3.5 секунди. Оптимизаторот успешно користи Index Scan врз колоната за име на регион, со што веднаш го изолира бараниот ентитет. Главниот товар на прашалникот доаѓа од агрегацијата на 83,500 записи за гласачи поврзани со избирачките места во таа општина. Иако времето е во секунди, HashAggregate операцијата работи со минимален мемориски зафат, што укажува на тоа дека погледот е стабилен и точно ги пресметува капацитетите на гласачките места во реално време.
     56[[File:10.png]]
    28357
    284 ==== Додаден индекс:
    285 
    286 {{{
    287 CREATE INDEX idx_voter_station ON voter(station_id);
    288 }}}
    289 
    290 ==== Време на извршување со индекс:
    291 
    292 **53 ms** (беше ~3500 ms)
    293 
    294 Со додавање на соодветен индекс, извршниот план за погледот покажа значителна структурна промена. Подобрување: ~66x побрзо.
     58[[File:11.png]]
    29559
    29660---
    29761
    298 === 6. Анализа на поглед 6, vw_election_results — општи резултати по избори ===
     62=== View 6.  vw_party_demographic_performance ===
    29963
    300 Овој поглед прикажува збирни изборни резултати по политички ентитет за секои избори, вклучувајќи го процентот на гласови, вкупниот број важечки гласови и дали ентитетот го поминал прагот од 5%.
    301 
    302 Дефиниција на погледот:
    303 {{{
    304 CREATE OR REPLACE VIEW public.vw_election_results AS
    305 WITH election_totals AS (
    306     SELECT vote_result.election_id,
    307            sum(vote_result.votes) AS total_valid_votes
    308     FROM vote_result
    309     GROUP BY vote_result.election_id
    310 ),
    311 entity_votes AS (
    312     SELECT vote_result.election_id,
    313            vote_result.entity_id,
    314            sum(vote_result.votes) AS total_votes
    315     FROM vote_result
    316     WHERE vote_result.candidate_id IS NULL
    317     GROUP BY vote_result.election_id, vote_result.entity_id
    318 ),
    319 calc AS (
    320     SELECT ev.election_id,
    321            ev.entity_id,
    322            ev.total_votes,
    323            el.total_valid_votes,
    324            ev.total_votes::numeric * 100.0 / NULLIF(el.total_valid_votes, 0)::numeric AS vote_pct
    325     FROM entity_votes ev
    326     JOIN election_totals el USING (election_id)
    327 )
    328 SELECT e.election_id,
    329        e.name                            AS election_name,
    330        e.election_date,
    331        et.type_name                      AS election_type,
    332        pe.entity_id,
    333        pe.name                           AS entity_name,
    334        pe.type                           AS entity_type,
    335        c.total_votes,
    336        c.total_valid_votes,
    337        round(c.vote_pct, 2)              AS vote_share_pct,
    338        c.vote_pct >= 5.0                 AS passed_threshold,
    339        wm.method_name                    AS winner_method
    340 FROM calc c
    341 JOIN election          e  USING (election_id)
    342 JOIN election_type     et ON et.election_type_id = e.election_type_id
    343 JOIN winner_method     wm ON wm.method_id        = e.winner_method_id
    344 JOIN political_entity  pe USING (entity_id);
    345 }}}
    346 
    347 Прашалникот кој ќе го тестираме:
    348 {{{
    349 SELECT * FROM vw_election_results
    350 WHERE election_name = 'Parliamentary Elections 2014'
    351 ORDER BY vote_share_pct DESC;
    352 }}}
    353 
    354 Овој поглед се потпира на агрегации над `vote_result` и join-ови со речниски табели (`election_type`, `winner_method`, `political_entity`) кои се мали. Основниот индекс на `vote_result(election_id)` е доволен за задоволителни перформанси. Дополнителна оптимизација не е потребна доколку веќе постои индекс на примарниот клуч и `election_id`.
     641.      Овој поглед овозможува длабинска декомпозиција на изборните резултати преку призмата на демографските карактеристики на гласачкото тело. Наместо едноставен линеарен приказ на победниците, погледот ја открива структурата и профилот на гласачите кои ја генерираат поддршката за секој политички субјект. Преку статистичко поврзување на кастираните гласови со старосната структура на чекираните гласачи, се генерира прецизен увид во електоралната поддршка поделена по генерации (Gen Z, Миленијалци и Сениори).
     652.      За максимална оптимизација на перформансите при пребарувањето, погледот е дизајниран да се филтрира според две основни колони: election_name (за избор на конкретен изборен циклус) и party_name (за селекција на одреден политички ентитет).
     663.      Мерење на време пред оптимизација
     67[[File:1.png]]
     68Иницијално време: 3415.820 ms (~3.4 секунди). Времето на извршување е неприфатливо, особено при истовремена работа на    повеќе корисници.
     694.     
     70Parallel Seq Scan над ballot
     71Hash Join и Nested Loop операции
     72Ова укажува дека PostgreSQL чита голем број редови поради недостаток на соодветни индекси.
     735.      Проблемот со време на извршување може да се подобри со индекси.
     746.      Со стратешка имплементација на соодветни индекси врз клучните колони во базата, времето на извршување на оваа комплексна демографска агрегација беше успешно оптимизирано на само 619 милисекунди.
     75[[File:2.png]]
     76[[File:3.png]]
    35577
    35678---
    35779
    358 === 7. Анализа на поглед 7, vw_dhondt_seat_allocation — распределба на мандати по Д'Онт ===
     80=== View 7. vw_party_performance_over_time ===
    35981
    360 Овој поглед ја пресметува распределбата на пратенички мандати во изборни единици според методот Д'Онт. Тој е наменет за парламентарни избори и ги пресметува квоциентите за секој политички ентитет по изборен округ.
    361 
    362 Дефиниција на погледот:
    363 {{{
    364 CREATE OR REPLACE VIEW public.vw_dhondt_seat_allocation AS
    365 WITH district_votes AS (
    366     SELECT ed.district_id, ed.election_id, ed.region_id, ed.seats_available,
    367            vr.entity_id, sum(vr.votes) AS district_votes
    368     FROM electoral_district ed
    369     JOIN election         e_1 ON e_1.election_id = ed.election_id
    370     JOIN vote_result       vr  ON vr.election_id  = ed.election_id
    371     JOIN polling_station   ps  ON ps.station_id   = vr.station_id
    372     JOIN region            r2  ON r2.region_id    = ps.municipality_id
    373     WHERE e_1.winner_method_id = 8
    374       AND vr.candidate_id IS NULL
    375       AND (r2.region_id = ed.region_id OR r2.parent_region_id = ed.region_id)
    376     GROUP BY ed.district_id, ed.election_id, ed.region_id, ed.seats_available, vr.entity_id
    377 ),
    378 district_totals AS (
    379     SELECT district_id, sum(district_votes) AS total_district_votes FROM district_votes GROUP BY district_id
    380 ),
    381 eligible AS (
    382     SELECT dv.*, dt.total_district_votes,
    383            dv.district_votes::numeric * 100.0 / NULLIF(dt.total_district_votes, 0::numeric) AS vote_pct
    384     FROM district_votes dv JOIN district_totals dt USING (district_id)
    385     WHERE (dv.district_votes::numeric * 100.0 / NULLIF(dt.total_district_votes, 0::numeric)) >= 5.0
    386 ),
    387 dhondt AS (
    388     SELECT e_1.*, d.divisor,
    389            e_1.district_votes::numeric / d.divisor::numeric AS quotient
    390     FROM eligible e_1 CROSS JOIN LATERAL generate_series(1, e_1.seats_available) d(divisor)
    391 ),
    392 ranked AS (
    393     SELECT *, rank() OVER (PARTITION BY district_id ORDER BY quotient DESC) AS rn FROM dhondt
    394 ),
    395 seats AS (
    396     SELECT district_id, election_id, region_id, seats_available, entity_id,
    397            district_votes, total_district_votes, vote_pct,
    398            count(*) FILTER (WHERE rn <= seats_available) AS seats_won
    399     FROM ranked
    400     GROUP BY district_id, election_id, region_id, seats_available, entity_id,
    401              district_votes, total_district_votes, vote_pct
    402 )
    403 SELECT s.election_id, e.name AS election_name, e.election_date,
    404        s.district_id, r.name AS region_name, s.seats_available,
    405        s.entity_id, pe.name AS entity_name, pe.type AS entity_type,
    406        s.district_votes AS total_votes, s.total_district_votes,
    407        round(s.vote_pct, 2) AS vote_share_pct, s.seats_won
    408 FROM seats s
    409 JOIN election        e  USING (election_id)
    410 JOIN region          r  ON r.region_id  = s.region_id
    411 JOIN political_entity pe USING (entity_id)
    412 ORDER BY s.election_id, s.district_id, s.seats_won DESC;
    413 }}}
    414 
    415 Прашалникот кој ќе го тестираме:
    416 {{{
    417 SELECT * FROM vw_dhondt_seat_allocation
    418 WHERE election_name = 'Parliamentary Elections 2014';
    419 }}}
    420 
    421 Поради комплексноста на пресметките (CROSS JOIN LATERAL со generate_series, повеќе CTE-и, window функции), овој поглед е по природа тежок за извршување. Клучните индекси кои помагаат се:
    422 
    423 {{{
    424 CREATE INDEX idx_vote_result_election_candidate ON vote_result(election_id, candidate_id);
    425 CREATE INDEX idx_electoral_district_election ON electoral_district(election_id);
    426 CREATE INDEX idx_region_parent ON region(parent_region_id);
    427 }}}
    42882
    42983---
    43084
    431 === 8. Анализа на поглед 8, vw_invalid_ballot_analysis — анализа на неважечки ливчиња ===
     85=== View 8. vw_polling_station_stats ===
    43286
    433 Погледот прикажува број и процент на неважечки ливчиња по регион и избори.
    434 
    435 Дефиниција на погледот:
    436 {{{
    437 CREATE OR REPLACE VIEW public.vw_invalid_ballot_analysis AS
    438 SELECT e.election_id,
    439        e.name                                                           AS election_name,
    440        r.region_id,
    441        r.name                                                           AS region_name,
    442        count(*)                                                         AS total_ballots,
    443        count(*) FILTER (WHERE NOT b.is_valid)                          AS invalid_ballots,
    444        round(count(*) FILTER (WHERE NOT b.is_valid)::numeric * 100.0
    445              / NULLIF(count(*), 0)::numeric, 2)                        AS invalid_pct
    446 FROM ballot           b
    447 JOIN election         e  ON e.election_id    = b.election_id
    448 JOIN polling_station  ps ON ps.station_id    = b.station_id
    449 JOIN region           r  ON r.region_id      = ps.municipality_id
    450 GROUP BY e.election_id, e.name, r.region_id, r.name;
    451 }}}
    452 
    453 Прашалникот кој ќе го тестираме:
    454 {{{
    455 SELECT * FROM vw_invalid_ballot_analysis
    456 WHERE election_name = 'Parliamentary Elections 2014'
    457 ORDER BY invalid_pct DESC;
    458 }}}
    459 
    460 Индексот на `ballot(election_id, station_id)` (додаден за поглед 1) и `polling_station(municipality_id)` покриваат го најголемиот дел од оптимизацијата. Дополнителен индекс за брзо филтрирање по `is_valid`:
    461 
    462 {{{
    463 CREATE INDEX idx_ballot_is_valid ON ballot(is_valid);
    464 }}}
    46587
    46688---
    46789
    468 === 9. Анализа на поглед 9, vw_voter_turnout — излезност на гласачи ===
     90=== View 9. vw_polling_station_voter_count ===
    46991
    470 Погледот пресметува излезност на гласачи (turnout) по регион и избори, споредувајќи го бројот на регистрирани гласачи со вистински кастираните ливчиња.
     921.      Погледот  служи за пресметување на вкупниот број на регистрирани гласачи за секое поединечно гласачко место. Наместо да се чуваат статични податоци во табелата polling_station (што би довело до редундантност и потенцијални аномалии), овој поглед врши агрегација во реално време врз основа на поврзаноста помеѓу гласачите и нивните доделени избирачки места.
     932.      Ќе се користи за проверка дали одредено гласачко место има преголем број гласачи (надминување на капацитетот). Најчесто ќе се користи со филтер по station_id или municipality_name за да се види густината на гласачкото тело во одредена општина. Обезбедува секогаш точни и ажурни податоци, бидејќи бројот на гласачи се пресметува директно од изворната табела voter.
     943.      При тестирање на погледот со филтер за општина Куманово, постигнато е време на извршување од 3.5 секунди. Оптимизаторот успешно користи Index Scan врз колоната за име на регион, со што веднаш го изолира бараниот ентитет. Главниот товар на кверито доаѓа од агрегацијата на 83,500 записи за гласачи поврзани со избирачките места во таа општина. Иако времето е во секунди, HashAggregate операцијата работи со минимален мемориски зафат, што укажува на тоа дека погледот е стабилен и точно ги пресметува капацитетите на гласачките места во реално време без користење на статични (кеширани) колони.
    47195
    472 Дефиниција на погледот:
    473 {{{
    474 CREATE OR REPLACE VIEW public.vw_voter_turnout AS
    475 WITH registered AS (
    476     SELECT se.election_id, ps.municipality_id AS region_id,
    477            sum(ps.registered_voter) AS registered_voters
    478     FROM station_election se JOIN polling_station ps ON ps.station_id = se.station_id
    479     GROUP BY se.election_id, ps.municipality_id
    480 ),
    481 ballots AS (
    482     SELECT b.election_id, ps.municipality_id AS region_id,
    483            count(*) AS ballots_cast,
    484            count(*) FILTER (WHERE b.is_valid)      AS valid_ballots,
    485            count(*) FILTER (WHERE NOT b.is_valid)  AS invalid_ballots
    486     FROM ballot b JOIN polling_station ps ON ps.station_id = b.station_id
    487     GROUP BY b.election_id, ps.municipality_id
    488 ),
    489 combined AS (
    490     SELECT COALESCE(r.election_id, b.election_id) AS election_id,
    491            COALESCE(r.region_id,   b.region_id)   AS region_id,
    492            r.registered_voters,
    493            b.ballots_cast, b.valid_ballots, b.invalid_ballots,
    494            b.ballots_cast::numeric * 100.0 / NULLIF(r.registered_voters, 0)::numeric AS turnout_pct_raw,
    495            b.invalid_ballots::numeric * 100.0 / NULLIF(b.ballots_cast, 0)::numeric   AS invalid_pct_raw
    496     FROM registered r FULL JOIN ballots b ON r.election_id = b.election_id AND r.region_id = b.region_id
    497 )
    498 SELECT e.election_id, e.name AS election_name, e.election_date,
    499        et.type_name AS election_type,
    500        c.region_id, rg.name AS region_name,
    501        COALESCE(c.registered_voters, 0::bigint) AS registered_voters,
    502        COALESCE(c.ballots_cast,      0::bigint) AS ballots_cast,
    503        COALESCE(c.valid_ballots,     0::bigint) AS valid_ballots,
    504        COALESCE(c.invalid_ballots,   0::bigint) AS invalid_ballots,
    505        round(c.turnout_pct_raw, 2) AS turnout_pct,
    506        round(c.invalid_pct_raw, 2) AS invalid_pct
    507 FROM combined c
    508 JOIN election      e  USING (election_id)
    509 JOIN election_type et ON et.election_type_id = e.election_type_id
    510 JOIN region        rg ON rg.region_id        = c.region_id
    511 WHERE c.registered_voters IS NOT NULL OR c.ballots_cast IS NOT NULL
    512 ORDER BY e.election_date, round(c.turnout_pct_raw, 2) DESC;
    513 }}}
     96[[File:14.png]]
    51497
    515 Прашалникот кој ќе го тестираме:
    516 {{{
    517 SELECT * FROM vw_voter_turnout
    518 WHERE election_name = 'Parliamentary Elections 2014';
    519 }}}
     98[[File:15.png]]
    52099
    521 Постоечките индекси на `ballot(election_id, station_id)` и `station_election(station_id)` се доволни за оптимално извршување. Доколку `station_election` нема индекс на `election_id`:
    522100
    523 {{{
    524 CREATE INDEX idx_station_election_election ON station_election(election_id);
    525 }}}
     1014. Со додавање на соодветен индекс , извршниот план за погледот  покажа значителна структурна промена. Сега постигнато е време на извршување од 53 ms.
     102 
     103[[File:16.png]]
     104
     105[[File:17.png]]
    526106
    527107---
    528108
    529 === 10. Анализа на поглед 10, vw_polling_station_stats — статистики по гласачко место ===
     109=== View 10. vw_presidential_by_municipality ===
    530110
    531 Погледот дава детален статистички преглед по гласачко место: регистрирани гласачи, кастирани ливчиња, важечки/неважечки и процент на излезност.
     1111.      Погледот овозможува детален приказ на гласовите освоени од претседателските кандидати, групирани по општина. Тој ги агрегира податоците од табелата Vote Result и ги поврзува со податоците за кандидатите и општините за да генерира извештај за поддршката на кандидатите на локално ниво.
     1122.      Овој извештај ќе се користи за визуелизација на географската распределба на гласовите, каде преку филтрирање по election_name и municipality_name корисникот може моментално да ги добие финалните резултати за специфичен регион.
     1133.      При извршување на погледот за претседателски избори по општини, постигнати се врвни перформанси со време на одговор од само 15 милисекунди. Овој резултат е директна последица на претходно имплементираната стратегија за индексирање.
     1144.      Ова време е прифатливо па затоа нема да имаме потреба од индексирање.
    532115
    533 Дефиниција на погледот:
    534 {{{
    535 CREATE OR REPLACE VIEW public.vw_polling_station_stats AS
    536 SELECT e.election_id,
    537        e.name                                                      AS election_name,
    538        ps.station_id,
    539        ps.name                                                     AS station_name,
    540        count(DISTINCT v.voter_id)                                  AS registered_voters,
    541        count(b.ballot_id)                                          AS ballots_cast,
    542        count(b.ballot_id) FILTER (WHERE b.is_valid)               AS valid_ballots,
    543        count(b.ballot_id) FILTER (WHERE NOT b.is_valid)           AS invalid_ballots,
    544        round(count(b.ballot_id)::numeric * 100.0
    545              / NULLIF(count(DISTINCT v.voter_id), 0)::numeric, 2) AS turnout_pct
    546 FROM polling_station  ps
    547 JOIN station_election se ON se.station_id  = ps.station_id
    548 JOIN election         e  ON e.election_id  = se.election_id
    549 LEFT JOIN ballot      b  ON b.station_id   = ps.station_id AND b.election_id = e.election_id
    550 LEFT JOIN voter       v  ON v.station_id   = ps.station_id
    551 GROUP BY e.election_id, e.name, ps.station_id, ps.name;
    552 }}}
     116[[File:12.png]]
    553117
    554 Прашалникот кој ќе го тестираме:
    555 {{{
    556 SELECT * FROM vw_polling_station_stats
    557 WHERE election_name = 'Parliamentary Elections 2014'
    558 ORDER BY turnout_pct DESC
    559 LIMIT 20;
    560 }}}
    561 
    562 Потребните индекси (дел веќе постојат):
    563 
    564 {{{
    565 CREATE INDEX idx_voter_station ON voter(station_id);
    566 CREATE INDEX idx_ballot_station_election ON ballot(station_id, election_id);
    567 CREATE INDEX idx_station_election_ids ON station_election(station_id, election_id);
    568 }}}
     118[[File:13.png]]
    569119
    570120---
    571121
    572 === 11. Анализа на поглед 11, vw_regional_voting_patterns — регионални образци на гласање ===
     122=== View 11. vw_regional_voting_patterns ===
    573123
    574 Погледот ги прикажува доминантните политички субјекти по регион за секои избори, земајќи ја предвид хиерархијата на региони (општина → подрегион → држава).
    575124
    576 Дефиниција на погледот:
    577 {{{
    578 CREATE OR REPLACE VIEW public.vw_regional_voting_patterns AS
    579 WITH regionhierarchy AS (
    580     SELECT ps.station_id,
    581            r.name        AS municipality_name,
    582            parent.name   AS sub_region_name,
    583            grandparent.name AS country_name
    584     FROM polling_station ps
    585     JOIN region      r           ON ps.municipality_id     = r.region_id
    586     LEFT JOIN region parent      ON r.parent_region_id     = parent.region_id
    587     LEFT JOIN region grandparent ON parent.parent_region_id = grandparent.region_id
    588 ),
    589 candidatevotes AS (
    590     SELECT e.election_id, e.name AS election_name, e.election_type_id,
    591            pe.name AS candidate_name,
    592            CASE WHEN e.election_type_id = 1 THEN rh.country_name ELSE rh.municipality_name END AS final_region,
    593            sum(vr.votes) AS total_votes_sum
    594     FROM vote_result      vr
    595     JOIN election         e  ON vr.election_id = e.election_id
    596     JOIN political_entity pe ON vr.entity_id   = pe.entity_id
    597     JOIN regionhierarchy  rh ON vr.station_id  = rh.station_id
    598     GROUP BY e.election_id, e.name, e.election_type_id, pe.name,
    599              CASE WHEN e.election_type_id = 1 THEN rh.country_name ELSE rh.municipality_name END
    600 ),
    601 rankedwinners AS (
    602     SELECT *, rank() OVER (PARTITION BY election_id, final_region ORDER BY total_votes_sum DESC) AS rnk
    603     FROM candidatevotes
    604 )
    605 SELECT election_name, final_region AS region_name,
    606        candidate_name AS dominant_entity,
    607        total_votes_sum AS winning_votes,
    608        'Winner'::text AS status
    609 FROM rankedwinners
    610 WHERE rnk = 1 AND final_region IS NOT NULL;
    611 }}}
    612 
    613 Прашалникот кој ќе го тестираме:
    614 {{{
    615 SELECT * FROM vw_regional_voting_patterns
    616 WHERE election_name = 'Parliamentary Elections 2014';
    617 }}}
    618 
    619 Индексот на `region(parent_region_id)` (додаден за поглед 7) и постоечките индекси на `vote_result` и `polling_station` се доволни за оптимизација на овој поглед.
    620125
    621126---
    622127
    623 === 12. Анализа на поглед 12, vw_party_performance_over_time — перформанси на партии низ изборите ===
     128=== View 12. vw_voter_turnout ===
    624129
    625 Погледот го прати уделот на гласови на секој политички субјект низ повеќе изборни циклуси, овозможувајќи трендовска анализа.
    626 
    627 Дефиниција на погледот:
    628 {{{
    629 CREATE OR REPLACE VIEW public.vw_party_performance_over_time AS
    630 WITH party_votes AS (
    631     SELECT e.election_id, e.name AS election_name, e.election_date,
    632            pe.entity_id, pe.name AS entity_name, sum(vr.votes) AS total_votes
    633     FROM vote_result       vr
    634     JOIN election          e  ON e.election_id  = vr.election_id
    635     JOIN political_entity  pe ON pe.entity_id   = vr.entity_id
    636     WHERE vr.candidate_id IS NULL
    637     GROUP BY e.election_id, e.name, e.election_date, pe.entity_id, pe.name
    638 ),
    639 totals AS (
    640     SELECT election_id, sum(total_votes) AS total_election_votes FROM party_votes GROUP BY election_id
    641 )
    642 SELECT pv.election_id, pv.election_name, pv.election_date,
    643        pv.entity_id, pv.entity_name, pv.total_votes, t.total_election_votes,
    644        round(pv.total_votes::numeric * 100.0 / NULLIF(t.total_election_votes, 0::numeric), 2) AS vote_share_pct
    645 FROM party_votes pv JOIN totals t USING (election_id)
    646 ORDER BY pv.entity_name, pv.election_date;
    647 }}}
    648 
    649 Прашалникот кој ќе го тестираме:
    650 {{{
    651 SELECT * FROM vw_party_performance_over_time
    652 WHERE entity_name = 'VMRO-DPMNE'
    653 ORDER BY election_date;
    654 }}}
    655 
    656 Прашалникот пребарува по `entity_name`, па додаваме индекс:
    657 
    658 {{{
    659 CREATE INDEX idx_political_entity_name ON political_entity(name);
    660 CREATE INDEX idx_vote_result_candidate_null ON vote_result(election_id, entity_id) WHERE candidate_id IS NULL;
    661 }}}
    662 
    663 Со овие индекси времето на извршување значително се намалува, бидејќи PostgreSQL може директно да ги пронајде гласовите без кандидат и да го изолира ентитетот по ime.