Changes between Initial Version and Version 1 of QueryOptimization

Timestamp:

05/19/26 22:12:32 (7 days ago)

Author:

231071

Comment:

--

QueryOptimization

@@ +1 @@
+= Оптимизација на прашалници и погледи =
+
+Во оваа фаза ќе ги анализираме погледите дефинирани во [[DatabaseCreation|Фаза 2]] преку прашалници базирани на реални сценарија кои ќе бидат присутни во нашата апликација и истите ќе се обидеме да ги оптимизираме.
+
+=== 1. Анализа на поглед 1, vw_party_demographic_performance — демографска анализа на поддршката по партии ===
+
+Овој поглед овозможува длабинска декомпозиција на изборните резултати преку призмата на демографските карактеристики на гласачкото тело. Наместо едноставен линеарен приказ на победниците, погледот ја открива структурата и профилот на гласачите кои ја генерираат поддршката за секој политички субјект. Преку статистичко поврзување на кастираните гласови со старосната структура на чекираните гласачи, се генерира прецизен увид во електоралната поддршка поделена по генерации (Gen Z, Миленијалци и Сениори).
+
+За максимална оптимизација на перформансите при пребарувањето, погледот е дизајниран да се филтрира според две основни колони: `election_name` (за избор на конкретен изборен циклус) и `party_name` (за селекција на одреден политички ентитет).
+
+Дефиниција на погледот:
+{{{
+CREATE OR REPLACE VIEW public.vw_party_demographic_performance AS
+WITH VoterDemographics AS (
+    SELECT
+        ve.election_id,
+        ve.station_id,
+        p.date_of_birth,
+        EXTRACT(YEAR FROM p.date_of_birth) as birth_year
+    FROM public.voter_election ve
+    JOIN public.voter v ON ve.voter_id = v.voter_id
+    JOIN public.person p ON v.person_id = p.person_id
+),
+BallotStats AS (
+    SELECT
+        b.election_id,
+        b.station_id,
+        b.entity_id,
+        b.ballot_id
+    FROM public.ballot b
+    WHERE b.is_valid = true
+)
+SELECT
+    e.name AS election_name,
+    r.name AS region_name,
+    pe.name AS party_name,
+    ps.name AS station_name,
+    COALESCE(COUNT(CASE WHEN vd.birth_year >= 1996 THEN 1 END), 0) AS gen_z_votes,
+    COALESCE(COUNT(CASE WHEN vd.birth_year >= 1981 AND vd.birth_year <= 1995 THEN 1 END), 0) AS millennial_votes,
+    COALESCE(COUNT(CASE WHEN vd.birth_year < 1981 THEN 1 END), 0) AS senior_votes,
+    COUNT(bs.ballot_id) AS total_valid_votes
+FROM BallotStats bs
+JOIN public.election e ON bs.election_id = e.election_id
+JOIN public.polling_station ps ON bs.station_id = ps.station_id
+JOIN public.region r ON ps.municipality_id = r.region_id
+JOIN public.political_entity pe ON bs.entity_id = pe.entity_id
+LEFT JOIN VoterDemographics vd ON bs.election_id = vd.election_id
+                             AND bs.station_id = vd.station_id
+GROUP BY e.name, r.name, pe.name, ps.name;
+}}}
+
+Прашалникот кој ќе го тестираме:
+{{{
+SELECT * FROM vw_party_demographic_performance
+WHERE election_name = 'Parliamentary Elections 2014'
+  AND party_name = 'VMRO-DPMNE';
+}}}
+
+==== Време на извршување без индекси:
+
+**3415.820 ms (~3.4 секунди)**
+
+Времето на извршување е неприфатливо, особено при истовремена работа на повеќе корисници. Извршниот план покажува:
+- Parallel Seq Scan над `ballot`
+- Hash Join и Nested Loop операции
+
+Ова укажува дека PostgreSQL чита голем број редови поради недостаток на соодветни индекси. Проблемот со времето на извршување може да се подобри со индекси.
+
+Со стратешка имплементација на соодветни индекси врз клучните колони во базата, времето на извршување на оваа комплексна демографска агрегација беше успешно оптимизирано.
+
+==== Додадени индекси:
+
+{{{
+CREATE INDEX idx_ballot_election_station ON ballot(election_id, station_id);
+CREATE INDEX idx_ballot_entity_valid ON ballot(entity_id) WHERE is_valid = true;
+CREATE INDEX idx_voter_election_ids ON voter_election(election_id, station_id, voter_id);
+CREATE INDEX idx_polling_station_municipality ON polling_station(municipality_id);
+}}}
+
+==== Време на извршување со индекси:
+
+**619 ms** (беше 3415.820 ms)
+
+Подобрување: ~5.5x побрзо.
+
+---
+
+=== 2. Анализа на поглед 2, vw_local_election_winners — победници на локални избори ===
+
+Овој поглед овозможува преглед на победниците (кандидати или партии со најмногу гласови) на локално ниво, поделени по изборни единици или општини. Погледот ги сумира гласовите и ги идентификува субјектите со највисок резултат за секоја локација.
+
+Примарен филтер: `election_id` или `election_year` (за да се видат победниците на конкретни избори). Секундарен филтер: `municipality_name` или `station_id`.
+
+Дефиниција на погледот:
+{{{
+CREATE OR REPLACE VIEW public.vw_local_election_winners AS
+WITH totalvotesperparty AS (
+    SELECT vr.election_id, e.name AS election_name, r.name AS municipality_name,
+           pe.name AS party_name, sum(vr.votes) AS total_sum_votes
+    FROM vote_result      vr
+    JOIN election         e  ON vr.election_id = e.election_id
+    JOIN political_entity pe ON vr.entity_id   = pe.entity_id
+    JOIN polling_station  ps ON vr.station_id  = ps.station_id
+    JOIN region           r  ON ps.municipality_id = r.region_id
+    WHERE e.election_type_id = 3
+    GROUP BY vr.election_id, e.name, r.name, pe.name
+),
+rankedwinners AS (
+    SELECT *, rank() OVER (PARTITION BY election_id, municipality_name ORDER BY total_sum_votes DESC) AS rnk
+    FROM totalvotesperparty
+)
+SELECT election_name, municipality_name,
+       party_name           AS winner_name,
+       total_sum_votes       AS votes_received,
+       'Winner'::text        AS status
+FROM rankedwinners
+WHERE rnk = 1;
+}}}
+
+Прашалникот кој ќе го тестираме:
+{{{
+SELECT * FROM vw_local_election_winners
+WHERE election_name = 'Local Elections 2021';
+}}}
+
+==== Време на извршување без индекси:
+
+**3500 ms (~3.5 секунди)**
+
+Времето на извршување не е прифатливо. Планот покажува дека базата троши премногу време на Incremental Sort и WindowAgg операциите за да ги рангира победниците меѓу 180,000 редови. Ова укажува на потреба од индекси за да се избегнат вгнездените циклуси (Nested Loops) и да се забрза пребарувањето низ табелите.
+
+==== Додадени индекси:
+
+{{{
+CREATE INDEX idx_vote_result_election_entity ON vote_result(election_id, entity_id);
+CREATE INDEX idx_vote_result_station ON vote_result(station_id);
+CREATE INDEX idx_election_type ON election(election_type_id);
+}}}
+
+==== Време на извршување со индекси:
+
+**290 ms** (беше ~3500 ms)
+
+Иако со дополнително индексирање времето се стабилизираше на околу 290 ms, ова се смета за оптимален резултат за овој тип на комплексен аналитички поглед. Ова време е резултат на балансот помеѓу ефикасното пребарување низ индексите и потребата на базата да изврши агрегација и рангирање врз голема количина податоци во реално време.
+
+---
+
+=== 3. Анализа на поглед 3, vw_parliamentary_municipality_winners — победници на парламентарни избори по општини ===
+
+Овој поглед служи за детална анализа на изборните резултати од парламентарните избори на ниво на општина. Тој ги агрегира гласовите за секој политички субјект во секоја општина и го идентификува победникот (партијата со најголем број гласови) за таа територијална единица.
+
+Примарен филтер: `election_name` (на пр. `'Parliamentary Elections 2014'`). Секундарен филтер: `municipality_name`.
+
+Дефиниција на погледот:
+{{{
+CREATE OR REPLACE VIEW public.vw_parliamentary_municipality_winners AS
+WITH aggregatedvotes AS (
+    SELECT e.election_id, e.name AS election_name, r.name AS municipality_name,
+           pe.name AS party_name, sum(vr.votes) AS total_votes
+    FROM vote_result      vr
+    JOIN election         e  ON vr.election_id    = e.election_id
+    JOIN political_entity pe ON vr.entity_id      = pe.entity_id
+    JOIN polling_station  ps ON vr.station_id     = ps.station_id
+    JOIN region           r  ON ps.municipality_id = r.region_id
+    WHERE e.election_type_id = 2
+    GROUP BY e.election_id, e.name, r.name, pe.name
+),
+rankedwinners AS (
+    SELECT *, rank() OVER (PARTITION BY election_id, municipality_name ORDER BY total_votes DESC) AS pos
+    FROM aggregatedvotes
+)
+SELECT election_name, municipality_name,
+       party_name          AS dominant_party,
+       total_votes         AS winning_votes,
+       'Municipality Winner'::text AS status
+FROM rankedwinners
+WHERE pos = 1;
+}}}
+
+Прашалникот кој ќе го тестираме:
+{{{
+SELECT * FROM vw_parliamentary_municipality_winners
+WHERE election_name = 'Parliamentary Elections 2014';
+}}}
+
+==== Време на извршување без индекси:
+
+**17900 ms (~17.9 секунди)**
+
+Времето на извршување воопшто не е прифатливо. Според приложениот извршен план, прашалникот трае 17.9 секунди главно поради користењето на дискот за сортирање податоци и големиот број на меѓу-резултати (скоро 1 милион редови) кои се префрлаат помеѓу паралелните процеси. Без соодветни индекси, базата не може да ги избегне овие скапи операции.
+
+==== Додадени индекси:
+
+{{{
+CREATE INDEX idx_vote_result_election_entity ON vote_result(election_id, entity_id);
+CREATE INDEX idx_vote_result_station ON vote_result(station_id);
+CREATE INDEX idx_election_type ON election(election_type_id);
+CREATE INDEX idx_polling_station_municipality ON polling_station(municipality_id);
+}}}
+
+==== Време на извршување со индекси:
+
+**1500 ms** (беше 17900 ms)
+
+По извршената финална оптимизација и консолидација на индексите, времето на извршување е драстично намалено од 17.9 секунди на 1.5 секунди. Подобрување: ~12x побрзо.
+
+---
+
+=== 4. Анализа на поглед 4, vw_presidential_by_municipality — претседателски гласови по општини ===
+
+Погледот овозможува детален приказ на гласовите освоени од претседателските кандидати, групирани по општина. Тој ги агрегира податоците од табелата `vote_result` и ги поврзува со податоците за кандидатите и општините за да генерира извештај за поддршката на кандидатите на локално ниво.
+
+Овој извештај ќе се користи за визуелизација на географската распределба на гласовите, каде преку филтрирање по `election_name` и `municipality_name` корисникот може моментално да ги добие финалните резултати за специфичен регион.
+
+Дефиниција на погледот:
+{{{
+CREATE OR REPLACE VIEW public.vw_presidential_by_municipality AS
+WITH cityvotes AS (
+    SELECT e.name AS election_name, r.name AS municipality_name,
+           pe.name AS candidate_name, sum(vr.votes) AS total_votes
+    FROM vote_result      vr
+    JOIN election         e  ON vr.election_id    = e.election_id
+    JOIN political_entity pe ON vr.entity_id      = pe.entity_id
+    JOIN polling_station  ps ON vr.station_id     = ps.station_id
+    JOIN region           r  ON ps.municipality_id = r.region_id
+    WHERE e.election_type_id = 1
+    GROUP BY e.name, r.name, pe.name
+),
+rankedcities AS (
+    SELECT *, rank() OVER (PARTITION BY election_name, municipality_name ORDER BY total_votes DESC) AS pos
+    FROM cityvotes
+)
+SELECT election_name, municipality_name, candidate_name,
+       total_votes, 'City Winner'::text AS level
+FROM rankedcities
+WHERE pos = 1;
+}}}
+
+Прашалникот кој ќе го тестираме:
+{{{
+SELECT * FROM vw_presidential_by_municipality
+WHERE election_name = 'Presidential Elections 2019'
+  AND municipality_name = 'Skopje';
+}}}
+
+==== Време на извршување:
+
+**15 ms**
+
+При извршување на погледот за претседателски избори по општини, постигнати се врвни перформанси со време на одговор од само 15 милисекунди. Овој резултат е директна последица на претходно имплементираната стратегија за индексирање (индексите додадени за претходните погледи). Ова време е прифатливо па затоа нема да имаме потреба од дополнително индексирање.
+
+---
+
+=== 5. Анализа на поглед 5, vw_polling_station_voter_count — број на регистрирани гласачи по гласачко место ===
+
+Погледот служи за пресметување на вкупниот број на регистрирани гласачи за секое поединечно гласачко место. Наместо да се чуваат статични податоци во табелата `polling_station` (што би довело до редундантност и потенцијални аномалии), овој поглед врши агрегација во реално време врз основа на поврзаноста помеѓу гласачите и нивните доделени избирачки места.
+
+Ќе се користи за проверка дали одредено гласачко место има преголем број гласачи (надминување на капацитетот). Најчесто ќе се користи со филтер по `station_id` или `municipality_name`.
+
+Дефиниција на погледот:
+{{{
+CREATE OR REPLACE VIEW public.vw_polling_station_voter_count AS
+SELECT r.name  AS region_name,
+       ps.name AS station_name,
+       count(v.voter_id) AS actual_registered_voters
+FROM polling_station  ps
+JOIN region           r  ON ps.municipality_id = r.region_id
+LEFT JOIN voter       v  ON ps.station_id      = v.station_id
+GROUP BY r.name, ps.name;
+}}}
+
+Прашалникот кој ќе го тестираме:
+{{{
+SELECT * FROM vw_polling_station_voter_count
+WHERE region_name = 'Куманово';
+}}}
+
+==== Време на извршување без индекси:
+
+**3500 ms (~3.5 секунди)**
+
+При тестирање на погледот со филтер за општина Куманово, постигнато е почетно време на извршување од 3.5 секунди. Оптимизаторот успешно користи Index Scan врз колоната за име на регион, со што веднаш го изолира бараниот ентитет. Главниот товар на прашалникот доаѓа од агрегацијата на 83,500 записи за гласачи поврзани со избирачките места во таа општина. Иако времето е во секунди, HashAggregate операцијата работи со минимален мемориски зафат, што укажува на тоа дека погледот е стабилен и точно ги пресметува капацитетите на гласачките места во реално време.
+
+==== Додаден индекс:
+
+{{{
+CREATE INDEX idx_voter_station ON voter(station_id);
+}}}
+
+==== Време на извршување со индекс:
+
+**53 ms** (беше ~3500 ms)
+
+Со додавање на соодветен индекс, извршниот план за погледот покажа значителна структурна промена. Подобрување: ~66x побрзо.
+
+---
+
+=== 6. Анализа на поглед 6, vw_election_results — општи резултати по избори ===
+
+Овој поглед прикажува збирни изборни резултати по политички ентитет за секои избори, вклучувајќи го процентот на гласови, вкупниот број важечки гласови и дали ентитетот го поминал прагот од 5%.
+
+Дефиниција на погледот:
+{{{
+CREATE OR REPLACE VIEW public.vw_election_results AS
+WITH election_totals AS (
+    SELECT vote_result.election_id,
+           sum(vote_result.votes) AS total_valid_votes
+    FROM vote_result
+    GROUP BY vote_result.election_id
+),
+entity_votes AS (
+    SELECT vote_result.election_id,
+           vote_result.entity_id,
+           sum(vote_result.votes) AS total_votes
+    FROM vote_result
+    WHERE vote_result.candidate_id IS NULL
+    GROUP BY vote_result.election_id, vote_result.entity_id
+),
+calc AS (
+    SELECT ev.election_id,
+           ev.entity_id,
+           ev.total_votes,
+           el.total_valid_votes,
+           ev.total_votes::numeric * 100.0 / NULLIF(el.total_valid_votes, 0)::numeric AS vote_pct
+    FROM entity_votes ev
+    JOIN election_totals el USING (election_id)
+)
+SELECT e.election_id,
+       e.name                            AS election_name,
+       e.election_date,
+       et.type_name                      AS election_type,
+       pe.entity_id,
+       pe.name                           AS entity_name,
+       pe.type                           AS entity_type,
+       c.total_votes,
+       c.total_valid_votes,
+       round(c.vote_pct, 2)              AS vote_share_pct,
+       c.vote_pct >= 5.0                 AS passed_threshold,
+       wm.method_name                    AS winner_method
+FROM calc c
+JOIN election          e  USING (election_id)
+JOIN election_type     et ON et.election_type_id = e.election_type_id
+JOIN winner_method     wm ON wm.method_id        = e.winner_method_id
+JOIN political_entity  pe USING (entity_id);
+}}}
+
+Прашалникот кој ќе го тестираме:
+{{{
+SELECT * FROM vw_election_results
+WHERE election_name = 'Parliamentary Elections 2014'
+ORDER BY vote_share_pct DESC;
+}}}
+
+Овој поглед се потпира на агрегации над `vote_result` и join-ови со речниски табели (`election_type`, `winner_method`, `political_entity`) кои се мали. Основниот индекс на `vote_result(election_id)` е доволен за задоволителни перформанси. Дополнителна оптимизација не е потребна доколку веќе постои индекс на примарниот клуч и `election_id`.
+
+---
+
+=== 7. Анализа на поглед 7, vw_dhondt_seat_allocation — распределба на мандати по Д'Онт ===
+
+Овој поглед ја пресметува распределбата на пратенички мандати во изборни единици според методот Д'Онт. Тој е наменет за парламентарни избори и ги пресметува квоциентите за секој политички ентитет по изборен округ.
+
+Дефиниција на погледот:
+{{{
+CREATE OR REPLACE VIEW public.vw_dhondt_seat_allocation AS
+WITH district_votes AS (
+    SELECT ed.district_id, ed.election_id, ed.region_id, ed.seats_available,
+           vr.entity_id, sum(vr.votes) AS district_votes
+    FROM electoral_district ed
+    JOIN election         e_1 ON e_1.election_id = ed.election_id
+    JOIN vote_result       vr  ON vr.election_id  = ed.election_id
+    JOIN polling_station   ps  ON ps.station_id   = vr.station_id
+    JOIN region            r2  ON r2.region_id    = ps.municipality_id
+    WHERE e_1.winner_method_id = 8
+      AND vr.candidate_id IS NULL
+      AND (r2.region_id = ed.region_id OR r2.parent_region_id = ed.region_id)
+    GROUP BY ed.district_id, ed.election_id, ed.region_id, ed.seats_available, vr.entity_id
+),
+district_totals AS (
+    SELECT district_id, sum(district_votes) AS total_district_votes FROM district_votes GROUP BY district_id
+),
+eligible AS (
+    SELECT dv.*, dt.total_district_votes,
+           dv.district_votes::numeric * 100.0 / NULLIF(dt.total_district_votes, 0::numeric) AS vote_pct
+    FROM district_votes dv JOIN district_totals dt USING (district_id)
+    WHERE (dv.district_votes::numeric * 100.0 / NULLIF(dt.total_district_votes, 0::numeric)) >= 5.0
+),
+dhondt AS (
+    SELECT e_1.*, d.divisor,
+           e_1.district_votes::numeric / d.divisor::numeric AS quotient
+    FROM eligible e_1 CROSS JOIN LATERAL generate_series(1, e_1.seats_available) d(divisor)
+),
+ranked AS (
+    SELECT *, rank() OVER (PARTITION BY district_id ORDER BY quotient DESC) AS rn FROM dhondt
+),
+seats AS (
+    SELECT district_id, election_id, region_id, seats_available, entity_id,
+           district_votes, total_district_votes, vote_pct,
+           count(*) FILTER (WHERE rn <= seats_available) AS seats_won
+    FROM ranked
+    GROUP BY district_id, election_id, region_id, seats_available, entity_id,
+             district_votes, total_district_votes, vote_pct
+)
+SELECT s.election_id, e.name AS election_name, e.election_date,
+       s.district_id, r.name AS region_name, s.seats_available,
+       s.entity_id, pe.name AS entity_name, pe.type AS entity_type,
+       s.district_votes AS total_votes, s.total_district_votes,
+       round(s.vote_pct, 2) AS vote_share_pct, s.seats_won
+FROM seats s
+JOIN election        e  USING (election_id)
+JOIN region          r  ON r.region_id  = s.region_id
+JOIN political_entity pe USING (entity_id)
+ORDER BY s.election_id, s.district_id, s.seats_won DESC;
+}}}
+
+Прашалникот кој ќе го тестираме:
+{{{
+SELECT * FROM vw_dhondt_seat_allocation
+WHERE election_name = 'Parliamentary Elections 2014';
+}}}
+
+Поради комплексноста на пресметките (CROSS JOIN LATERAL со generate_series, повеќе CTE-и, window функции), овој поглед е по природа тежок за извршување. Клучните индекси кои помагаат се:
+
+{{{
+CREATE INDEX idx_vote_result_election_candidate ON vote_result(election_id, candidate_id);
+CREATE INDEX idx_electoral_district_election ON electoral_district(election_id);
+CREATE INDEX idx_region_parent ON region(parent_region_id);
+}}}
+
+---
+
+=== 8. Анализа на поглед 8, vw_invalid_ballot_analysis — анализа на неважечки ливчиња ===
+
+Погледот прикажува број и процент на неважечки ливчиња по регион и избори.
+
+Дефиниција на погледот:
+{{{
+CREATE OR REPLACE VIEW public.vw_invalid_ballot_analysis AS
+SELECT e.election_id,
+       e.name                                                           AS election_name,
+       r.region_id,
+       r.name                                                           AS region_name,
+       count(*)                                                         AS total_ballots,
+       count(*) FILTER (WHERE NOT b.is_valid)                          AS invalid_ballots,
+       round(count(*) FILTER (WHERE NOT b.is_valid)::numeric * 100.0
+             / NULLIF(count(*), 0)::numeric, 2)                        AS invalid_pct
+FROM ballot           b
+JOIN election         e  ON e.election_id    = b.election_id
+JOIN polling_station  ps ON ps.station_id    = b.station_id
+JOIN region           r  ON r.region_id      = ps.municipality_id
+GROUP BY e.election_id, e.name, r.region_id, r.name;
+}}}
+
+Прашалникот кој ќе го тестираме:
+{{{
+SELECT * FROM vw_invalid_ballot_analysis
+WHERE election_name = 'Parliamentary Elections 2014'
+ORDER BY invalid_pct DESC;
+}}}
+
+Индексот на `ballot(election_id, station_id)` (додаден за поглед 1) и `polling_station(municipality_id)` покриваат го најголемиот дел од оптимизацијата. Дополнителен индекс за брзо филтрирање по `is_valid`:
+
+{{{
+CREATE INDEX idx_ballot_is_valid ON ballot(is_valid);
+}}}
+
+---
+
+=== 9. Анализа на поглед 9, vw_voter_turnout — излезност на гласачи ===
+
+Погледот пресметува излезност на гласачи (turnout) по регион и избори, споредувајќи го бројот на регистрирани гласачи со вистински кастираните ливчиња.
+
+Дефиниција на погледот:
+{{{
+CREATE OR REPLACE VIEW public.vw_voter_turnout AS
+WITH registered AS (
+    SELECT se.election_id, ps.municipality_id AS region_id,
+           sum(ps.registered_voter) AS registered_voters
+    FROM station_election se JOIN polling_station ps ON ps.station_id = se.station_id
+    GROUP BY se.election_id, ps.municipality_id
+),
+ballots AS (
+    SELECT b.election_id, ps.municipality_id AS region_id,
+           count(*) AS ballots_cast,
+           count(*) FILTER (WHERE b.is_valid)      AS valid_ballots,
+           count(*) FILTER (WHERE NOT b.is_valid)  AS invalid_ballots
+    FROM ballot b JOIN polling_station ps ON ps.station_id = b.station_id
+    GROUP BY b.election_id, ps.municipality_id
+),
+combined AS (
+    SELECT COALESCE(r.election_id, b.election_id) AS election_id,
+           COALESCE(r.region_id,   b.region_id)   AS region_id,
+           r.registered_voters,
+           b.ballots_cast, b.valid_ballots, b.invalid_ballots,
+           b.ballots_cast::numeric * 100.0 / NULLIF(r.registered_voters, 0)::numeric AS turnout_pct_raw,
+           b.invalid_ballots::numeric * 100.0 / NULLIF(b.ballots_cast, 0)::numeric   AS invalid_pct_raw
+    FROM registered r FULL JOIN ballots b ON r.election_id = b.election_id AND r.region_id = b.region_id
+)
+SELECT e.election_id, e.name AS election_name, e.election_date,
+       et.type_name AS election_type,
+       c.region_id, rg.name AS region_name,
+       COALESCE(c.registered_voters, 0::bigint) AS registered_voters,
+       COALESCE(c.ballots_cast,      0::bigint) AS ballots_cast,
+       COALESCE(c.valid_ballots,     0::bigint) AS valid_ballots,
+       COALESCE(c.invalid_ballots,   0::bigint) AS invalid_ballots,
+       round(c.turnout_pct_raw, 2) AS turnout_pct,
+       round(c.invalid_pct_raw, 2) AS invalid_pct
+FROM combined c
+JOIN election      e  USING (election_id)
+JOIN election_type et ON et.election_type_id = e.election_type_id
+JOIN region        rg ON rg.region_id        = c.region_id
+WHERE c.registered_voters IS NOT NULL OR c.ballots_cast IS NOT NULL
+ORDER BY e.election_date, round(c.turnout_pct_raw, 2) DESC;
+}}}
+
+Прашалникот кој ќе го тестираме:
+{{{
+SELECT * FROM vw_voter_turnout
+WHERE election_name = 'Parliamentary Elections 2014';
+}}}
+
+Постоечките индекси на `ballot(election_id, station_id)` и `station_election(station_id)` се доволни за оптимално извршување. Доколку `station_election` нема индекс на `election_id`:
+
+{{{
+CREATE INDEX idx_station_election_election ON station_election(election_id);
+}}}
+
+---
+
+=== 10. Анализа на поглед 10, vw_polling_station_stats — статистики по гласачко место ===
+
+Погледот дава детален статистички преглед по гласачко место: регистрирани гласачи, кастирани ливчиња, важечки/неважечки и процент на излезност.
+
+Дефиниција на погледот:
+{{{
+CREATE OR REPLACE VIEW public.vw_polling_station_stats AS
+SELECT e.election_id,
+       e.name                                                      AS election_name,
+       ps.station_id,
+       ps.name                                                     AS station_name,
+       count(DISTINCT v.voter_id)                                  AS registered_voters,
+       count(b.ballot_id)                                          AS ballots_cast,
+       count(b.ballot_id) FILTER (WHERE b.is_valid)               AS valid_ballots,
+       count(b.ballot_id) FILTER (WHERE NOT b.is_valid)           AS invalid_ballots,
+       round(count(b.ballot_id)::numeric * 100.0
+             / NULLIF(count(DISTINCT v.voter_id), 0)::numeric, 2) AS turnout_pct
+FROM polling_station  ps
+JOIN station_election se ON se.station_id  = ps.station_id
+JOIN election         e  ON e.election_id  = se.election_id
+LEFT JOIN ballot      b  ON b.station_id   = ps.station_id AND b.election_id = e.election_id
+LEFT JOIN voter       v  ON v.station_id   = ps.station_id
+GROUP BY e.election_id, e.name, ps.station_id, ps.name;
+}}}
+
+Прашалникот кој ќе го тестираме:
+{{{
+SELECT * FROM vw_polling_station_stats
+WHERE election_name = 'Parliamentary Elections 2014'
+ORDER BY turnout_pct DESC
+LIMIT 20;
+}}}
+
+Потребните индекси (дел веќе постојат):
+
+{{{
+CREATE INDEX idx_voter_station ON voter(station_id);
+CREATE INDEX idx_ballot_station_election ON ballot(station_id, election_id);
+CREATE INDEX idx_station_election_ids ON station_election(station_id, election_id);
+}}}
+
+---
+
+=== 11. Анализа на поглед 11, vw_regional_voting_patterns — регионални образци на гласање ===
+
+Погледот ги прикажува доминантните политички субјекти по регион за секои избори, земајќи ја предвид хиерархијата на региони (општина → подрегион → држава).
+
+Дефиниција на погледот:
+{{{
+CREATE OR REPLACE VIEW public.vw_regional_voting_patterns AS
+WITH regionhierarchy AS (
+    SELECT ps.station_id,
+           r.name        AS municipality_name,
+           parent.name   AS sub_region_name,
+           grandparent.name AS country_name
+    FROM polling_station ps
+    JOIN region      r           ON ps.municipality_id     = r.region_id
+    LEFT JOIN region parent      ON r.parent_region_id     = parent.region_id
+    LEFT JOIN region grandparent ON parent.parent_region_id = grandparent.region_id
+),
+candidatevotes AS (
+    SELECT e.election_id, e.name AS election_name, e.election_type_id,
+           pe.name AS candidate_name,
+           CASE WHEN e.election_type_id = 1 THEN rh.country_name ELSE rh.municipality_name END AS final_region,
+           sum(vr.votes) AS total_votes_sum
+    FROM vote_result      vr
+    JOIN election         e  ON vr.election_id = e.election_id
+    JOIN political_entity pe ON vr.entity_id   = pe.entity_id
+    JOIN regionhierarchy  rh ON vr.station_id  = rh.station_id
+    GROUP BY e.election_id, e.name, e.election_type_id, pe.name,
+             CASE WHEN e.election_type_id = 1 THEN rh.country_name ELSE rh.municipality_name END
+),
+rankedwinners AS (
+    SELECT *, rank() OVER (PARTITION BY election_id, final_region ORDER BY total_votes_sum DESC) AS rnk
+    FROM candidatevotes
+)
+SELECT election_name, final_region AS region_name,
+       candidate_name AS dominant_entity,
+       total_votes_sum AS winning_votes,
+       'Winner'::text AS status
+FROM rankedwinners
+WHERE rnk = 1 AND final_region IS NOT NULL;
+}}}
+
+Прашалникот кој ќе го тестираме:
+{{{
+SELECT * FROM vw_regional_voting_patterns
+WHERE election_name = 'Parliamentary Elections 2014';
+}}}
+
+Индексот на `region(parent_region_id)` (додаден за поглед 7) и постоечките индекси на `vote_result` и `polling_station` се доволни за оптимизација на овој поглед.
+
+---
+
+=== 12. Анализа на поглед 12, vw_party_performance_over_time — перформанси на партии низ изборите ===
+
+Погледот го прати уделот на гласови на секој политички субјект низ повеќе изборни циклуси, овозможувајќи трендовска анализа.
+
+Дефиниција на погледот:
+{{{
+CREATE OR REPLACE VIEW public.vw_party_performance_over_time AS
+WITH party_votes AS (
+    SELECT e.election_id, e.name AS election_name, e.election_date,
+           pe.entity_id, pe.name AS entity_name, sum(vr.votes) AS total_votes
+    FROM vote_result       vr
+    JOIN election          e  ON e.election_id  = vr.election_id
+    JOIN political_entity  pe ON pe.entity_id   = vr.entity_id
+    WHERE vr.candidate_id IS NULL
+    GROUP BY e.election_id, e.name, e.election_date, pe.entity_id, pe.name
+),
+totals AS (
+    SELECT election_id, sum(total_votes) AS total_election_votes FROM party_votes GROUP BY election_id
+)
+SELECT pv.election_id, pv.election_name, pv.election_date,
+       pv.entity_id, pv.entity_name, pv.total_votes, t.total_election_votes,
+       round(pv.total_votes::numeric * 100.0 / NULLIF(t.total_election_votes, 0::numeric), 2) AS vote_share_pct
+FROM party_votes pv JOIN totals t USING (election_id)
+ORDER BY pv.entity_name, pv.election_date;
+}}}
+
+Прашалникот кој ќе го тестираме:
+{{{
+SELECT * FROM vw_party_performance_over_time
+WHERE entity_name = 'VMRO-DPMNE'
+ORDER BY election_date;
+}}}
+
+Прашалникот пребарува по `entity_name`, па додаваме индекс:
+
+{{{
+CREATE INDEX idx_political_entity_name ON political_entity(name);
+CREATE INDEX idx_vote_result_candidate_null ON vote_result(election_id, entity_id) WHERE candidate_id IS NULL;
+}}}
+
+Со овие индекси времето на извршување значително се намалува, бидејќи PostgreSQL може директно да ги пронајде гласовите без кандидат и да го изолира ентитетот по ime.