Оптимизација на прашалници и погледи

Во оваа фаза ќе се изврши анализа и оптимизација на погледите дефинирани во претходната фаза преку прашалници базирани на реални сценарија кои би се користеле во рамки на системот Safe City Security.

Целта е да се испитаат перформансите при пребарување и обработка на податоци поврзани со прекршоци, возила, казни, камери и корисници, како и да се идентификуваат потенцијални проблеми при извршување на прашалниците.

Преку користење на EXPLAIN ANALYZE ќе се анализираат плановите на извршување, ќе се утврдат најбавните операции и ќе се изврши оптимизација со помош на индекси и подобрување на прашалниците, со цел системот да обезбеди поефикасно и побрзо извршување при работа со големи количини на податоци.

Анализа на View3: Сопственици на возила

Прашалникот кој ќе го тестираме е следниот:

-- 3A: приказ на сите возила заедно со нивните сопственици
SELECT *
FROM vw_sopstvenici_na_vozila;

-- 3B: анализа на планот на извршување
EXPLAIN ANALYZE
SELECT *
FROM vw_sopstvenici_na_vozila;

1. Примарен филтер за погледот vw_sopstvenici_na_vozila ќе биде според vozilo_id или registarska_oznaka. Погледот ќе се користи за брз приказ на податоци за возило и негов сопственик.

2. Примарен случај на употреба ќе биде пребарување и преглед на информации за возила и нивните сопственици. За овој поглед перформансите се важни, бидејќи табелите Vozilo, Sopstvenik_Vozilo и Gragjanin содржат голем број записи.

Време на извршување без индекси:

3A - 3 s

Прашалникот успешно ги прикажува сите возила заедно со нивните сопственици преку поврзување на табелите Vozilo, Sopstvenik_Vozilo и Gragjanin.

При извршување на прашалникот SELECT * FROM vw_sopstvenici_na_vozila;, DataGrip прикажува време на извршување од приближно 3 секунди. Ова време е прифатливо според поставениот критериум дека оптимизација и дополнително индексирање се врши само доколку времето е поголемо од 3 секунди.

Бидејќи прашалникот враќа повеќе од 1.200.000 редови, очекувано е да постои поголемо време на извршување поради количината на податоци што треба да се обработат и прикажат.

3B - Анализа на планот на извршување

Gather  (cost=102725.44..259522.66 rows=1200350 width=66)
(actual time=1342.828..1853.740 rows=1200350 loops=1)

Workers Planned: 2
Workers Launched: 2

-> Parallel Hash Join
(cost=101725.44..138487.66 rows=500146 width=66)
(actual time=1318.410..1603.201 rows=400117 loops=3)

Hash Cond: (sv.embg = g.embg)

-> Parallel Hash Join
(cost=26091.54..48846.88 rows=500146 width=40)
(actual time=410.265..621.322 rows=400117 loops=3)

Hash Cond: (sv.vozilo_id = v.vozilo_id)

-> Parallel Seq Scan on sopstvenik_vozilo sv
(cost=0.00..12647.46 rows=500146 width=18)
(actual time=0.048..55.599 rows=400117 loops=3)

-> Parallel Hash
(cost=17797.13..17797.13 rows=428913 width=26)
(actual time=243.205..243.206 rows=443210 loops=3)

-> Parallel Seq Scan on vozilo v
(cost=0.00..17797.13 rows=428913 width=26)
(actual time=0.051..101.953 rows=443210 loops=3)

-> Parallel Hash
(cost=64512.62..64512.62 rows=499462 width=54)
(actual time=521.096..521.097 rows=666570 loops=3)

-> Parallel Seq Scan on gragjanin g
(cost=0.00..64512.62 rows=499462 width=54)
(actual time=15.121..235.455 rows=666570 loops=3)

Planning Time: 0.835 ms

Execution Time: 1899.811 ms

Од добиениот план на извршување може да се забележи дека PostgreSQL користи Parallel Seq Scan врз табелите sopstvenik_vozilo, vozilo и gragjanin. Тоа значи дека системот секвенцијално чита голем број записи од табелите со цел да ги поврзе потребните податоци.

Дополнително, се користат Parallel Hash Join операции преку колоните vozilo_id и embg, што покажува дека PostgreSQL прави hash-based поврзување на големи множества податоци.

Во планот на извршување може да се забележи дека:

• табелата sopstvenik_vozilo враќа околу 400117 редови,
• табелата vozilo враќа околу 443210 редови,
• табелата gragjanin враќа околу 666570 редови.

Поради големиот број записи и отсуството на дополнителни филтри (WHERE услови), PostgreSQL мора да обработи повеќе од 1.200.000 редови, што резултира со поголемо време на извршување.

Во планот е прикажано Execution Time: 1899.811 ms, односно приближно 1.9 секунди. Разликата помеѓу ова време и времето прикажано во DataGrip (3 s) се должи на тоа што DataGrip дополнително го пресметува и времето потребно за прикажување на резултатите во табеларен приказ.

Бидејќи времето на извршување е прифатливо и не го надминува поставениот праг од 3 секунди, за овој поглед нема потреба од дополнително индексирање.

Анализа на View1: Неплатени казни во последните 2 недели

-- 1A: сите прекршоци регистрирани од конкретна камера
SELECT *
FROM prekrsoci_po_kamera
WHERE kamera_id = 5;

-- 1B: последни прекршоци регистрирани од камерите
SELECT *
FROM prekrsoci_po_kamera
ORDER BY datum DESC
LIMIT 10;

1. Примарен филтер за погледот vw_neplateni_kazni_posledni_2_nedeli ќе биде според датумот на казната, односно ќе се прикажуваат само казните креирани во последните 14 дена. Дополнително, погледот може да се користи и за пребарување според сторител, возило или регистарска ознака.

2. Примарен случај на употреба ќе биде преглед на неплатени казни од последните две недели, со цел администрацијата да има увид кои казни сè уште немаат извршено плаќање. За овој поглед перформансите се важни, бидејќи се користат повеќе поврзани табели како Kazna, Prekrsok, Gragjanin, Vozilo и Plakanje.

3. Иницијалното време за извршување на погледот е ms / s.