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

Во оваа фаза ќе се изврши анализа и оптимизација на погледите дефинирани во претходната фаза преку прашалници базирани на реални сценарија кои би се користеле во рамки на системот 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 - 1904.498 ms

Gather  (cost=107243.47..264426.69 rows=1200350 width=66) (actual time=1347.925..1858.566 rows=1200350 loops=1)
  Workers Planned: 2
  Workers Launched: 2
  ->  Parallel Hash Join  (cost=106243.47..143391.69 rows=500146 width=66) (actual time=1328.027..1605.452 rows=400117 loops=3)
        Hash Cond: (sv.embg = g.embg)
        ->  Parallel Hash Join  (cost=30609.57..53750.91 rows=500146 width=40) (actual time=433.942..644.238 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.046..54.985 rows=400117 loops=3)
              ->  Parallel Hash  (cost=21223.03..21223.03 rows=485403 width=26) (actual time=267.048..267.049 rows=443210 loops=3)
                    Buckets: 131072  Batches: 16  Memory Usage: 6144kB
                    ->  Parallel Seq Scan on vozilo v  (cost=0.00..21223.03 rows=485403 width=26) (actual time=0.063..124.396 rows=443210 loops=3)
        ->  Parallel Hash  (cost=64512.62..64512.62 rows=499462 width=54) (actual time=505.717..505.718 rows=666570 loops=3)
              Buckets: 131072  Batches: 32  Memory Usage: 6592kB
              ->  Parallel Seq Scan on gragjanin g  (cost=0.00..64512.62 rows=499462 width=54) (actual time=14.767..217.461 rows=666570 loops=3)
Planning Time: 0.846 ms
JIT:
  Functions: 54
"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
"  Timing: Generation 4.132 ms (Deform 2.153 ms), Inlining 0.000 ms, Optimization 2.183 ms, Emission 42.395 ms, Total 48.710 ms"
Execution Time: 1904.498 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 секунди, за овој поглед нема потреба од дополнително индексирање.

Заклучок:

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