Changes between Version 3 and Version 4 of QueryOptimization


Ignore:
Timestamp:
05/30/26 02:50:20 (12 days ago)
Author:
231018
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v3 v4  
    1 = Оптимизација на прашалници и погледи =
    2 
    3 Во оваа фаза ќе се изврши анализа и оптимизација на погледите дефинирани во претходната фаза преку прашалници базирани на реални сценарија кои би се користеле во рамки на системот '''Safe City Security'''.
    4 
    5 Целта е да се испитаат перформансите при пребарување и обработка на податоци поврзани со прекршоци, возила, казни, камери и корисници, како и да се идентификуваат потенцијални проблеми при извршување на прашалниците.
    6 
    7 Преку користење на ''EXPLAIN ANALYZE'' ќе се анализираат плановите на извршување, ќе се утврдат најбавните операции и ќе се изврши оптимизација со помош на индекси и подобрување на прашалниците, со цел системот да обезбеди поефикасно и побрзо извршување при работа со големи количини на податоци.
    8 
    9 
    10 == Анализа на View3: Сопственици на возила ==
    11 
    12 Прашалниците кои ќе ги тестираме се следните:
    13 
    14 {{{
    15 -- 3A: приказ на сите возила заедно со нивните сопственици
    16 SELECT *
    17 FROM vw_sopstvenici_na_vozila;
    18 
    19 -- 3B: анализа на планот на извршување
    20 EXPLAIN ANALYZE
    21 SELECT *
    22 FROM vw_sopstvenici_na_vozila;
    23 }}}
    24 
    25 1. Примарен филтер за погледот `vw_sopstvenici_na_vozila` ќе биде според `vozilo_id` или `registarska_oznaka`. Погледот ќе се користи за брз приказ на податоци за возило и негов сопственик.
    26 
    27 2. Примарен случај на употреба ќе биде пребарување и преглед на информации за возила и нивните сопственици. За овој поглед перформансите се важни, бидејќи табелите `Vozilo`, `Sopstvenik_Vozilo` и `Gragjanin` содржат голем број записи.
    28 
    29 
    30 '''Време на извршување без индекси:'''
    31 
    32 '''3A - 3 s'''
    33 
    34 Прашалникот успешно ги прикажува сите возила заедно со нивните сопственици преку поврзување на табелите `Vozilo`, `Sopstvenik_Vozilo` и `Gragjanin`.
    35 
    36 При извршување на прашалникот `SELECT * FROM vw_sopstvenici_na_vozila;`, DataGrip прикажува време на извршување од приближно 3 секунди. Ова време е прифатливо според поставениот критериум дека оптимизација и дополнително индексирање се врши само доколку времето е поголемо од 3 секунди.
    37 
    38 Бидејќи прашалникот враќа повеќе од 1.200.000 редови, очекувано е да постои поголемо време на извршување поради количината на податоци што треба да се обработат и прикажат.
    39 
    40 
    41 '''3B - 1904.498 ms'''
    42 
    43 {{{
    44 Gather  (cost=107243.47..264426.69 rows=1200350 width=66) (actual time=1347.925..1858.566 rows=1200350 loops=1)
    45   Workers Planned: 2
    46   Workers Launched: 2
    47   ->  Parallel Hash Join  (cost=106243.47..143391.69 rows=500146 width=66) (actual time=1328.027..1605.452 rows=400117 loops=3)
    48         Hash Cond: (sv.embg = g.embg)
    49         ->  Parallel Hash Join  (cost=30609.57..53750.91 rows=500146 width=40) (actual time=433.942..644.238 rows=400117 loops=3)
    50               Hash Cond: (sv.vozilo_id = v.vozilo_id)
    51               ->  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)
    52               ->  Parallel Hash  (cost=21223.03..21223.03 rows=485403 width=26) (actual time=267.048..267.049 rows=443210 loops=3)
    53                     Buckets: 131072  Batches: 16  Memory Usage: 6144kB
    54                     ->  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)
    55         ->  Parallel Hash  (cost=64512.62..64512.62 rows=499462 width=54) (actual time=505.717..505.718 rows=666570 loops=3)
    56               Buckets: 131072  Batches: 32  Memory Usage: 6592kB
    57               ->  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)
    58 Planning Time: 0.846 ms
     1== Анализа на View1: Прекршоци денес ==
     2
     3Прашалниците кои ќе ги тестираме се следните:
     4
     5{{{
     6-- 1A: приказ на сите прекршоци евидентирани на последниот датум
     7SELECT *
     8FROM vw_prekrsoci_denes
     9WHERE registarska_oznaka = 'SR-2625-AC';
     10
     11-- 1B: пребарување на прекршоци според регистарска ознака
     12EXPLAIN ANALYZE
     13SELECT *
     14FROM vw_prekrsoci_denes
     15WHERE registarska_oznaka = 'SR-2625-AC';
     16}}}
     17
     18'''Време на извршување без индекси:'''
     19
     20'''1A - 6259 ms'''
     21
     22При извршување на прашалникот:
     23
     24{{{
     25SELECT *
     26FROM vw_prekrsoci_denes
     27WHERE registarska_oznaka = 'SR-2625-AC';
     28}}}
     29
     30Прашалникот успешно ги прикажува прекршоците поврзани со возилото со регистарска ознака `SR-2625-AC`, заедно со информациите за зоната, дозволената брзина и сторителот на прекршокот.
     31
     32При извршување на прашалникот `SELECT * FROM vw_prekrsoci_denes WHERE registarska_oznaka = 'SR-2625-AC';`, DataGrip прикажува време на извршување од приближно 7 секунди.
     33
     34Иако се пребарува само едно возило, погледот поврзува повеќе табели (`Prekrsok`, `Kamera`, `Lokacija`, `Zona`, `Prekrsok_Vozilo`, `Vozilo`, `Prekrsok_Storitel` и `Gragjanin`), поради што PostgreSQL обработува поголем број записи пред да ги врати конечните резултати.
     35
     36
     37'''1B - 2565.134 ms'''
     38{{{
     39Nested Loop Left Join  (cost=81647.37..91337.78 rows=1 width=156) (actual time=133.585..2564.780 rows=1290 loops=1)
     40  InitPlan 1
     41    ->  Finalize Aggregate  (cost=80636.78..80636.79 rows=1 width=4) (actual time=127.885..128.003 rows=1 loops=1)
     42          ->  Gather  (cost=80636.37..80636.77 rows=4 width=4) (actual time=127.726..127.994 rows=5 loops=1)
     43                Workers Planned: 4
     44                Workers Launched: 4
     45                ->  Partial Aggregate  (cost=79636.37..79636.38 rows=1 width=4) (actual time=96.102..96.103 rows=1 loops=5)
     46                      ->  Parallel Seq Scan on prekrsok  (cost=0.00..78654.69 rows=392669 width=4) (actual time=0.025..69.617 rows=200002 loops=5)
     47  ->  Nested Loop Left Join  (cost=1010.15..10700.39 rows=1 width=142) (actual time=133.560..2548.091 rows=1290 loops=1)
     48        ->  Nested Loop Left Join  (cost=1009.72..10696.01 rows=1 width=128) (actual time=133.544..2543.391 rows=645 loops=1)
     49              ->  Nested Loop Left Join  (cost=1009.44..10695.62 rows=1 width=113) (actual time=133.533..2540.561 rows=645 loops=1)
     50                    ->  Nested Loop Left Join  (cost=1009.16..10695.28 rows=1 width=113) (actual time=133.521..2537.787 rows=645 loops=1)
     51                          ->  Nested Loop  (cost=1008.88..10694.83 rows=1 width=113) (actual time=133.506..2534.805 rows=645 loops=1)
     52                                ->  Gather  (cost=1008.46..10694.03 rows=1 width=14) (actual time=0.480..128.971 rows=1000010 loops=1)
     53                                      Workers Planned: 2
     54                                      Workers Launched: 2
     55                                      ->  Hash Join  (cost=8.46..9693.93 rows=1 width=14) (actual time=0.204..114.102 rows=333337 loops=3)
     56                                            Hash Cond: (pv.vozilo_id = v.vozilo_id)
     57                                            ->  Parallel Seq Scan on prekrsok_vozilo pv  (cost=0.00..8591.71 rows=416671 width=8) (actual time=0.029..39.040 rows=333337 loops=3)
     58                                            ->  Hash  (cost=8.45..8.45 rows=1 width=14) (actual time=0.082..0.083 rows=1 loops=3)
     59                                                  Buckets: 1024  Batches: 1  Memory Usage: 9kB
     60                                                  ->  Index Scan using uq_vozilo_registarska on vozilo v  (cost=0.43..8.45 rows=1 width=14) (actual time=0.074..0.075 rows=1 loops=3)
     61                                                        Index Cond: ((registarska_oznaka)::text = 'SR-2625-AC'::text)
     62                                ->  Index Scan using prekrsok_pkey on prekrsok p  (cost=0.43..0.80 rows=1 width=103) (actual time=0.002..0.002 rows=0 loops=1000010)
     63                                      Index Cond: (prekrsok_id = pv.prekrsok_id)
     64                                      Filter: (datum = (InitPlan 1).col1)
     65                                      Rows Removed by Filter: 1
     66                          ->  Index Scan using kamera_pkey on kamera k  (cost=0.28..0.44 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=645)
     67                                Index Cond: (kamera_id = p.kamera_id)
     68                    ->  Index Scan using lokacija_pkey on lokacija l  (cost=0.28..0.35 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=645)
     69                          Index Cond: (lokacija_id = k.lokacija_id)
     70              ->  Index Scan using zona_pkey on zona z  (cost=0.28..0.39 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=645)
     71                    Index Cond: (zona_id = l.zona_id)
     72        ->  Index Only Scan using prekrsok_storitel_pkey on prekrsok_storitel ps  (cost=0.43..4.36 rows=2 width=18) (actual time=0.005..0.006 rows=2 loops=645)
     73              Index Cond: (prekrsok_id = p.prekrsok_id)
     74              Heap Fetches: 0
     75  ->  Index Scan using gragjanin_pkey on gragjanin g  (cost=0.43..0.59 rows=1 width=42) (actual time=0.012..0.012 rows=1 loops=1290)
     76        Index Cond: (embg = (ps.storitel_embg)::bpchar)
     77Planning Time: 2.847 ms
     78Execution Time: 2565.134 ms
     79}}}
     80
     81== Анализа на View2: Неплатени казни во последните 2 недели ==
     82
     83Прашалниците кои ќе ги тестираме се следните:
     84
     85{{{
     86-- 2A: приказ на неплатена казна според идентификатор
     87SELECT *
     88FROM vw_neplateni_kazni_posledni_2_nedeli
     89WHERE kazna_id = 523;
     90
     91-- 2B: анализа на планот на извршување за неплатена казна според идентификатор
     92EXPLAIN ANALYZE
     93SELECT *
     94FROM vw_neplateni_kazni_posledni_2_nedeli
     95WHERE kazna_id = 523;
     96}}}
     97
     98'''Време на извршување без индекси:'''
     99
     100'''2A - 1246 ms'''
     101
     102При извршување на прашалникот:
     103
     104{{{
     105SELECT *
     106FROM vw_neplateni_kazni_posledni_2_nedeli
     107WHERE kazna_id = 523;
     108}}}
     109
     110Прашалникот се користи за пребарување на конкретна неплатена казна според нејзиниот идентификатор. Во овој случај, DataGrip не враќа редови за `kazna_id = 523`, бидејќи оваа казна не ги исполнува условите на погледот.
     111
     112При извршување на прашалникот, DataGrip прикажува време на извршување од 1246 ms, односно приближно 1.2 секунди.
     113
     114'''2B - 430.601 ms'''
     115
     116{{{
     117Nested Loop Left Join  (cost=192893.72..272550.73 rows=1 width=141) (actual time=419.459..428.078 rows=0 loops=1)
     118  InitPlan 1
     119    ->  Finalize Aggregate  (cost=95945.78..95945.79 rows=1 width=8) (actual time=397.322..405.935 rows=1 loops=1)
     120          ->  Gather  (cost=95945.36..95945.77 rows=4 width=4) (actual time=397.111..405.907 rows=5 loops=1)
     121                Workers Planned: 4
     122                Workers Launched: 4
     123                ->  Partial Aggregate  (cost=94945.36..94945.37 rows=1 width=4) (actual time=362.874..362.875 rows=1 loops=5)
     124                      ->  Parallel Seq Scan on kazna  (cost=0.00..88695.29 rows=2500029 width=4) (actual time=0.063..183.068 rows=2000000 loops=5)
     125  InitPlan 2
     126    ->  Finalize Aggregate  (cost=95945.78..95945.79 rows=1 width=4) (never executed)
     127          ->  Gather  (cost=95945.36..95945.77 rows=4 width=4) (never executed)
     128                Workers Planned: 4
     129                Workers Launched: 0
     130                ->  Partial Aggregate  (cost=94945.36..94945.37 rows=1 width=4) (never executed)
     131                      ->  Parallel Seq Scan on kazna kazna_1  (cost=0.00..88695.29 rows=2500029 width=4) (never executed)
     132  ->  Nested Loop Left Join  (cost=1001.71..80658.46 rows=1 width=135) (actual time=419.458..419.461 rows=0 loops=1)
     133        ->  Nested Loop Left Join  (cost=1001.29..80650.01 rows=1 width=131) (actual time=419.458..419.460 rows=0 loops=1)
     134              ->  Nested Loop Left Join  (cost=1000.86..80649.42 rows=1 width=117) (actual time=419.457..419.459 rows=0 loops=1)
     135                    ->  Nested Loop  (cost=1000.43..80644.94 rows=1 width=103) (actual time=419.457..419.458 rows=0 loops=1)
     136                          ->  Index Scan using kazna_pkey on kazna k  (cost=0.43..8.46 rows=1 width=16) (actual time=419.456..419.456 rows=0 loops=1)
     137                                Index Cond: (kazna_id = 523)
     138                                Filter: ((rok_na_plakanje >= (InitPlan 1).col1) AND (rok_na_plakanje <= (InitPlan 2).col1) AND (status = 0))
     139                                Rows Removed by Filter: 1
     140                          ->  Gather  (cost=1000.00..80636.47 rows=1 width=91) (never executed)
     141                                Workers Planned: 4
     142                                Workers Launched: 0
     143                                ->  Parallel Seq Scan on prekrsok p  (cost=0.00..79636.37 rows=1 width=91) (never executed)
     144                                      Filter: (kazna_id = 523)
     145                    ->  Index Only Scan using prekrsok_storitel_pkey on prekrsok_storitel ps  (cost=0.43..4.46 rows=2 width=18) (never executed)
     146                          Index Cond: (prekrsok_id = p.prekrsok_id)
     147                          Heap Fetches: 0
     148              ->  Index Scan using gragjanin_pkey on gragjanin g  (cost=0.43..0.59 rows=1 width=42) (never executed)
     149                    Index Cond: (embg = (ps.storitel_embg)::bpchar)
     150        ->  Index Only Scan using prekrsok_vozilo_pkey on prekrsok_vozilo pv  (cost=0.42..8.44 rows=1 width=8) (never executed)
     151              Index Cond: (prekrsok_id = p.prekrsok_id)
     152              Heap Fetches: 0
     153  ->  Index Scan using vozilo_pkey on vozilo v  (cost=0.43..0.68 rows=1 width=14) (never executed)
     154        Index Cond: (vozilo_id = pv.vozilo_id)
     155Planning Time: 1.388 ms
    59156JIT:
    60   Functions: 54
     157  Functions: 48
    61158"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
    62 "  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"
    63 Execution Time: 1904.498 ms
    64 
    65 }}}
    66 
    67 Од добиениот план на извршување може да се забележи дека PostgreSQL користи `Parallel Seq Scan` врз табелите `sopstvenik_vozilo`, `vozilo` и `gragjanin`. Тоа значи дека системот секвенцијално чита голем број записи од табелите со цел да ги поврзе потребните податоци.
    68 
    69 Дополнително, се користат `Parallel Hash Join` операции преку колоните `vozilo_id` и `embg`, што покажува дека PostgreSQL прави hash-based поврзување на големи множества податоци.
    70 
    71 Во планот на извршување може да се забележи дека:
    72 * табелата `sopstvenik_vozilo` враќа околу 400117 редови,
    73 * табелата `vozilo` враќа околу 443210 редови,
    74 * табелата `gragjanin` враќа околу 666570 редови.
    75 
    76 Поради големиот број записи и отсуството на дополнителни филтри (`WHERE` услови), PostgreSQL мора да обработи повеќе од 1.200.000 редови, што резултира со поголемо време на извршување.
    77 
    78 Во планот е прикажано `Execution Time: 1899.811 ms`, односно приближно 1.9 секунди. Разликата помеѓу ова време и времето прикажано во DataGrip (`3 s`) се должи на тоа што DataGrip дополнително го пресметува и времето потребно за прикажување на резултатите во табеларен приказ.
    79 
    80 Бидејќи времето на извршување е прифатливо и не го надминува поставениот праг од 3 секунди, за овој поглед нема потреба од дополнително индексирање.
    81 
    82 
    83 ''' Заклучок: '''
    84 
    85 Времето на извршување за овој поглед е прифатливо, па затоа не се поставуваат дополнителни индекси.
     159"  Timing: Generation 4.036 ms (Deform 1.421 ms), Inlining 0.000 ms, Optimization 2.244 ms, Emission 43.042 ms, Total 49.322 ms"
     160Execution Time: 430.601 ms
     161}}}
     162
     163
     164== Анализа на View3: Локации со најмногу прекршоци ==
     165
     166Прашалниците кои ќе ги тестираме се следните:
     167
     168{{{
     169-- 3A: приказ на локации со прекршоци во конкретна зона
     170SELECT *
     171FROM vw_lokacii_so_najmnogu_prekrsoci
     172WHERE zona = 'Centar';
     173
     174-- 3B: анализа на планот на извршување за локации со прекршоци во конкретна зона
     175EXPLAIN ANALYZE
     176SELECT *
     177FROM vw_lokacii_so_najmnogu_prekrsoci
     178WHERE zona = 'Centar';
     179}}}
     180
     181'''Време на извршување без индекси:'''
     182
     183'''3A - 109 ms'''
     184
     185Прашалникот се користи за приказ на локации во конкретна зона и бројот на прекршоци евидентирани на тие локации.
     186
     187Во овој случај, DataGrip не враќа редови за зоната `Centar`, бидејќи таква зона не постои во резултатите од погледот.
     188
     189При извршување на прашалникот, DataGrip прикажува време на извршување од 109 ms.
     190
     191'''3B - 0.430 ms'''
     192
     193{{{
     194GroupAggregate  (cost=96565.40..96574.57 rows=524 width=39) (actual time=0.357..0.360 rows=0 loops=1)
     195  Group Key: l.lokacija_id
     196  ->  Sort  (cost=96565.40..96566.71 rows=524 width=35) (actual time=0.355..0.358 rows=0 loops=1)
     197        Sort Key: l.lokacija_id
     198        Sort Method: quicksort  Memory: 25kB
     199        ->  Hash Right Join  (cost=211.68..96541.73 rows=524 width=35) (actual time=0.350..0.353 rows=0 loops=1)
     200              Hash Cond: (p.kamera_id = k.kamera_id)
     201              ->  Seq Scan on prekrsok p  (cost=0.00..90434.77 rows=1570677 width=8) (never executed)
     202              ->  Hash  (cost=211.67..211.67 rows=1 width=35) (actual time=0.344..0.346 rows=0 loops=1)
     203                    Buckets: 1024  Batches: 1  Memory Usage: 8kB
     204                    ->  Hash Right Join  (cost=150.41..211.67 rows=1 width=35) (actual time=0.343..0.345 rows=0 loops=1)
     205                          Hash Cond: (k.lokacija_id = l.lokacija_id)
     206                          ->  Seq Scan on kamera k  (cost=0.00..50.00 rows=3000 width=8) (never executed)
     207                          ->  Hash  (cost=150.40..150.40 rows=1 width=31) (actual time=0.339..0.341 rows=0 loops=1)
     208                                Buckets: 1024  Batches: 1  Memory Usage: 8kB
     209                                ->  Hash Join  (cost=86.51..150.40 rows=1 width=31) (actual time=0.339..0.340 rows=0 loops=1)
     210                                      Hash Cond: (l.zona_id = z.zona_id)
     211                                      ->  Seq Scan on lokacija l  (cost=0.00..56.00 rows=3000 width=20) (actual time=0.011..0.011 rows=1 loops=1)
     212                                      ->  Hash  (cost=86.50..86.50 rows=1 width=19) (actual time=0.322..0.323 rows=0 loops=1)
     213                                            Buckets: 1024  Batches: 1  Memory Usage: 8kB
     214                                            ->  Seq Scan on zona z  (cost=0.00..86.50 rows=1 width=19) (actual time=0.322..0.322 rows=0 loops=1)
     215                                                  Filter: ((ime)::text = 'Centar'::text)
     216                                                  Rows Removed by Filter: 3000
     217Planning Time: 0.611 ms
     218Execution Time: 0.430 ms
     219}}}
     220
     221
     222
     223
     224== Анализа на View4: Истечени регистрации ==
     225
     226Прашалниците кои ќе ги тестираме се следните:
     227
     228{{{
     229-- 4A: приказ на истечена регистрација според регистарска ознака
     230SELECT *
     231FROM vw_isteceni_registracii
     232WHERE registarska_oznaka = 'VE-135-AA';
     233
     234-- 4B: анализа на планот на извршување за истечена регистрација според регистарска ознака
     235EXPLAIN ANALYZE
     236SELECT *
     237FROM vw_isteceni_registracii
     238WHERE registarska_oznaka = 'VE-135-AA';
     239}}}
     240
     241'''Време на извршување без индекси:'''
     242
     243'''4A - 618 ms'''
     244
     245При извршување на прашалникот:
     246
     247{{{
     248SELECT *
     249FROM vw_isteceni_registracii
     250WHERE registarska_oznaka = 'VE-135-AA';
     251}}}
     252
     253Прашалникот успешно прикажува истечена регистрација за возилото со регистарска ознака `VE-135-AA`, заедно со податоците за регистрацијата, возилото и сопственикот.
     254
     255При извршување на прашалникот, DataGrip прикажува време на извршување од 618 ms.
     256
     257'''4B - 300.887 ms'''
     258
     259{{{
     260Gather  (cost=1008.88..74422.27 rows=2 width=102) (actual time=295.897..300.834 rows=1 loops=1)
     261  Workers Planned: 4
     262  Workers Launched: 4
     263  ->  Nested Loop Left Join  (cost=8.88..73422.07 rows=1 width=102) (actual time=277.098..288.605 rows=0 loops=5)
     264        ->  Hash Join  (cost=8.46..73421.51 rows=1 width=74) (actual time=277.068..288.574 rows=0 loops=5)
     265              Hash Cond: (r.vozilovozilo_id = v.vozilo_id)
     266              ->  Parallel Seq Scan on registracija r  (cost=0.00..71605.81 rows=688470 width=38) (actual time=10.031..215.222 rows=911932 loops=5)
     267                    Filter: (datum_istekuvanje < CURRENT_DATE)
     268                    Rows Removed by Filter: 250862
     269              ->  Hash  (cost=8.45..8.45 rows=1 width=40) (actual time=0.077..0.078 rows=1 loops=5)
     270                    Buckets: 1024  Batches: 1  Memory Usage: 9kB
     271                    ->  Index Scan using uq_vozilo_registarska on vozilo v  (cost=0.43..8.45 rows=1 width=40) (actual time=0.069..0.071 rows=1 loops=5)
     272                          Index Cond: ((registarska_oznaka)::text = 'VE-135-AA'::text)
     273        ->  Index Scan using gragjanin_pkey on gragjanin g  (cost=0.43..0.55 rows=1 width=42) (actual time=0.138..0.138 rows=1 loops=1)
     274              Index Cond: (embg = (r.sopstvenikembg)::bpchar)
     275Planning Time: 0.583 ms
     276Execution Time: 300.887 ms
     277}}}
     278
     279
     280== Анализа на View5: Граѓани со најмногу прекршоци ==
     281
     282Прашалниците кои ќе ги тестираме се следните:
     283
     284{{{
     285-- 5A: приказ на бројот на прекршоци за конкретен граѓанин
     286SELECT *
     287FROM vw_gragjani_so_najmnogu_prekrsoci
     288WHERE embg = '3006956460025';
     289
     290-- 5B: анализа на планот на извршување за конкретен граѓанин
     291EXPLAIN ANALYZE
     292SELECT *
     293FROM vw_gragjani_so_najmnogu_prekrsoci
     294WHERE embg = '3006956460025';
     295}}}
     296
     297'''Време на извршување без индекси:'''
     298
     299'''5A - 433 ms'''
     300
     301При извршување на прашалникот:
     302
     303{{{
     304SELECT *
     305FROM vw_gragjani_so_najmnogu_prekrsoci
     306WHERE embg = '3006956460025';
     307}}}
     308
     309Прашалникот успешно го прикажува бројот на прекршоци за граѓанинот со ЕМБГ `3006956460025`.
     310
     311При извршување на прашалникот, DataGrip прикажува време на извршување од 433 ms.
     312
     313'''5B - 143 ms'''
     314
     315{{{
     316GroupAggregate  (cost=1000.43..44123.49 rows=1 width=50) (actual time=73.505..78.493 rows=1 loops=1)
     317  ->  Nested Loop Left Join  (cost=1000.43..44123.48 rows=2 width=46) (actual time=10.174..78.482 rows=1 loops=1)
     318        ->  Index Scan using gragjanin_pkey on gragjanin g  (cost=0.43..8.45 rows=1 width=42) (actual time=0.027..0.033 rows=1 loops=1)
     319              Index Cond: (embg = '3006956460025'::bpchar)
     320        ->  Gather  (cost=1000.00..44115.01 rows=2 width=18) (actual time=10.143..78.443 rows=1 loops=1)
     321              Workers Planned: 4
     322              Workers Launched: 4
     323              ->  Parallel Seq Scan on prekrsok_storitel ps  (cost=0.00..43114.81 rows=1 width=18) (actual time=54.779..67.379 rows=0 loops=5)
     324                    Filter: ((storitel_embg)::bpchar = '3006956460025'::bpchar)
     325                    Rows Removed by Filter: 400004
     326Planning Time: 0.251 ms
     327Execution Time: 78.542 ms
     328}}}
     329
     330
     331
     332== Анализа на View6: Неактивни камери ==
     333
     334Прашалниците кои ќе ги тестираме се следните:
     335
     336{{{
     337-- 6A: приказ на неактивни камери во конкретна зона
     338SELECT *
     339FROM vw_neaktivni_kameri
     340WHERE zona = 'uchilishna zona';
     341
     342-- 6B: анализа на планот на извршување за неактивни камери во конкретна зона
     343EXPLAIN ANALYZE
     344SELECT *
     345FROM vw_neaktivni_kameri
     346WHERE zona = 'uchilishna zona';
     347}}}
     348
     3491. Примарен филтер за погледот `vw_neaktivni_kameri` ќе биде според `zona` или `tip_kamera`. Погледот ќе се користи за брз преглед на камери кои не се активни во одредена зона.
     350
     3512. Примарен случај на употреба ќе биде следење на неисправни, исклучени или неактивни камери. За овој поглед перформансите се важни бидејќи се поврзуваат табелите `Kamera`, `Lokacija`, `Zona` и `TipKamera`.
     352
     353'''Време на извршување без индекси:'''
     354
     355'''6A - 72 ms'''
     356
     357При извршување на прашалникот:
     358
     359{{{
     360SELECT *
     361FROM vw_neaktivni_kameri
     362WHERE zona = 'uchilishna zona';
     363}}}
     364
     365Прашалникот успешно прикажува неактивни камери во зоната `uchilishna zona`, заедно со податоци за улицата, статусот и типот на камерата.
     366
     367При извршување на прашалникот, DataGrip прикажува време на извршување од 72 ms.
     368
     369'''6B - 2.689 ms'''
     370
     371{{{
     372Nested Loop Left Join  (cost=158.66..246.66 rows=323 width=257) (actual time=1.282..2.613 rows=34 loops=1)
     373  ->  Hash Join  (cost=158.51..237.93 rows=323 width=43) (actual time=1.263..2.574 rows=34 loops=1)
     374        Hash Cond: (k.lokacija_id = l.lokacija_id)
     375        ->  Seq Scan on kamera k  (cost=0.00..65.00 rows=2985 width=20) (actual time=0.034..1.323 rows=309 loops=1)
     376              Filter: (lower((status)::text) <> 'aktivna'::text)
     377              Rows Removed by Filter: 2691
     378        ->  Hash  (cost=154.45..154.45 rows=325 width=31) (actual time=1.195..1.197 rows=325 loops=1)
     379              Buckets: 1024  Batches: 1  Memory Usage: 30kB
     380              ->  Hash Join  (cost=90.56..154.45 rows=325 width=31) (actual time=0.432..1.126 rows=325 loops=1)
     381                    Hash Cond: (l.zona_id = z.zona_id)
     382                    ->  Seq Scan on lokacija l  (cost=0.00..56.00 rows=3000 width=20) (actual time=0.007..0.231 rows=3000 loops=1)
     383                    ->  Hash  (cost=86.50..86.50 rows=325 width=19) (actual time=0.416..0.416 rows=325 loops=1)
     384                          Buckets: 1024  Batches: 1  Memory Usage: 25kB
     385                          ->  Seq Scan on zona z  (cost=0.00..86.50 rows=325 width=19) (actual time=0.008..0.357 rows=325 loops=1)
     386                                Filter: ((ime)::text = 'uchilishna zona'::text)
     387                                Rows Removed by Filter: 2675
     388  ->  Memoize  (cost=0.15..0.19 rows=1 width=222) (actual time=0.001..0.001 rows=1 loops=34)
     389        Cache Key: k.tip_kamera_id
     390        Cache Mode: logical
     391        Hits: 32  Misses: 2  Evictions: 0  Overflows: 0  Memory Usage: 1kB
     392        ->  Index Scan using tipkamera_pkey on tipkamera tk  (cost=0.14..0.18 rows=1 width=222) (actual time=0.007..0.007 rows=1 loops=2)
     393              Index Cond: (tip_kamera_id = k.tip_kamera_id)
     394Planning Time: 0.476 ms
     395Execution Time: 2.689 ms
     396}}}
     397
     398== Анализа на View7: Активни жалби ==
     399
     400Прашалниците кои ќе ги тестираме се следните:
     401
     402{{{
     403-- 7A: приказ на активни жалби според статус
     404SELECT *
     405FROM vw_aktivni_zalbi
     406WHERE status = 'podnesena';
     407
     408-- 7B: анализа на планот на извршување за активни жалби според статус
     409EXPLAIN ANALYZE
     410SELECT *
     411FROM vw_aktivni_zalbi
     412WHERE status = 'podnesena';
     413}}}
     414
     4151. Примарен филтер за погледот `vw_aktivni_zalbi` ќе биде според `status`. Погледот ќе се користи за брз преглед на жалби кои се во тек на обработка.
     416
     4172. Примарен случај на употреба ќе биде следење на активните жалби и нивната моментална состојба. За овој поглед перформансите се важни бидејќи се поврзуваат табелите `Zalba`, `Korisnik`, `Gragjanin` и `Prekrsok`.
     418
     419'''Време на извршување без индекси:'''
     420
     421'''7A - 34068 ms'''
     422
     423При извршување на прашалникот:
     424
     425{{{
     426SELECT *
     427FROM vw_aktivni_zalbi
     428WHERE status = 'podnesena';
     429}}}
     430
     431Прашалникот успешно ги прикажува сите активни жалби со статус `podnesena`, заедно со информациите за прекршокот и граѓанинот кој ја поднел жалбата.
     432
     433При извршување на прашалникот, DataGrip прикажува време на извршување од 34068 ms, односно приближно 34 секунди.
     434
     435Поради големиот број записи и поврзувањето на повеќе табели, времето на извршување е значително поголемо од поставениот праг.
     436
     437'''7B - 87924 ms'''
     438
     439{{{
     440Gather  (cost=1001.28..165958.95 rows=17682 width=178) (actual time=48.482..87720.979 rows=1751073 loops=1)
     441  Workers Planned: 4
     442  Workers Launched: 4
     443  ->  Nested Loop Left Join  (cost=1.28..163190.75 rows=4420 width=178) (actual time=51.788..87379.859 rows=350215 loops=5)
     444        ->  Nested Loop Left Join  (cost=0.85..134440.88 rows=4420 width=95) (actual time=51.697..85565.365 rows=350215 loops=5)
     445              ->  Nested Loop Left Join  (cost=0.43..131622.48 rows=4420 width=67) (actual time=41.937..54618.455 rows=350215 loops=5)
     446                    ->  Parallel Seq Scan on zalba z  (cost=0.00..112180.56 rows=4420 width=57) (actual time=15.090..524.515 rows=350215 loops=5)
     447"                          Filter: (((status)::text = 'podnesena'::text) AND (lower((status)::text) = ANY ('{podnesena,vo_postapka}'::text[])))"
     448                          Rows Removed by Filter: 1049785
     449                    ->  Index Scan using korisnik_pkey on korisnik k  (cost=0.43..4.40 rows=1 width=18) (actual time=0.154..0.154 rows=1 loops=1751073)
     450                          Index Cond: (korisnik_id = z.korisnik_id)
     451              ->  Index Scan using gragjanin_pkey on gragjanin g  (cost=0.43..0.64 rows=1 width=42) (actual time=0.088..0.088 rows=1 loops=1751073)
     452                    Index Cond: (embg = (k.embg)::bpchar)
     453        ->  Index Scan using prekrsok_pkey on prekrsok p  (cost=0.43..6.50 rows=1 width=87) (actual time=0.005..0.005 rows=1 loops=1751073)
     454              Index Cond: (prekrsok_id = z.prekrsok_id)
     455Planning Time: 0.833 ms
     456JIT:
     457  Functions: 90
     458"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
     459"  Timing: Generation 7.367 ms (Deform 3.709 ms), Inlining 0.000 ms, Optimization 3.573 ms, Emission 71.609 ms, Total 82.549 ms"
     460Execution Time: 87808.602 ms
     461}}}
     462
     463
     464== Анализа на View8: Зони со најмногу прекршоци ==
     465
     466Прашалниците кои ќе ги тестираме се следните:
     467
     468{{{
     469-- 8A: приказ на бројот на прекршоци за конкретна зона
     470SELECT *
     471FROM vw_zoni_so_najmnogu_prekrsoci
     472WHERE zona = 'bolnichka zona';
     473
     474-- 8B: анализа на планот на извршување за конкретна зона
     475EXPLAIN ANALYZE
     476SELECT *
     477FROM vw_zoni_so_najmnogu_prekrsoci
     478WHERE zona = 'bolnichka zona';
     479}}}
     480
     4811. Примарен филтер за погледот `vw_zoni_so_najmnogu_prekrsoci` ќе биде според `zona`. Погледот ќе се користи за приказ на бројот на прекршоци во конкретна зона.
     482
     4832. Примарен случај на употреба ќе биде анализа на зони со најголем број прекршоци. За овој поглед перформансите се важни бидејќи се поврзуваат табелите `Zona`, `Lokacija`, `Kamera` и `Prekrsok`, а потоа се врши групирање со `COUNT`.
     484
     485'''Време на извршување без индекси:'''
     486
     487'''8A - 584 ms'''
     488
     489При извршување на прашалникот:
     490
     491{{{
     492SELECT *
     493FROM vw_zoni_so_najmnogu_prekrsoci
     494WHERE zona = 'bolnichka zona';
     495}}}
     496
     497Прашалникот успешно го прикажува бројот на прекршоци за зоната `bolnichka zona`.
     498
     499При извршување на прашалникот, DataGrip прикажува време на извршување од 584 ms.
     500
     501'''8B - 566 ms'''
     502
     503{{{
     504HashAggregate  (cost=98630.18..98632.83 rows=265 width=27) (actual time=491.600..491.651 rows=265 loops=1)
     505  Group Key: z.zona_id
     506  Batches: 1  Memory Usage: 61kB
     507  ->  Hash Right Join  (cost=224.22..97936.46 rows=138743 width=23) (actual time=1.901..473.067 rows=88342 loops=1)
     508        Hash Cond: (p.kamera_id = k.kamera_id)
     509        ->  Seq Scan on prekrsok p  (cost=0.00..90434.77 rows=1570677 width=8) (actual time=0.012..301.843 rows=1000010 loops=1)
     510        ->  Hash  (cost=220.91..220.91 rows=265 width=23) (actual time=1.875..1.881 rows=265 loops=1)
     511              Buckets: 1024  Batches: 1  Memory Usage: 23kB
     512              ->  Hash Right Join  (cost=157.01..220.91 rows=265 width=23) (actual time=1.203..1.827 rows=265 loops=1)
     513                    Hash Cond: (k.lokacija_id = l.lokacija_id)
     514                    ->  Seq Scan on kamera k  (cost=0.00..50.00 rows=3000 width=8) (actual time=0.009..0.220 rows=3000 loops=1)
     515                    ->  Hash  (cost=153.70..153.70 rows=265 width=23) (actual time=1.185..1.189 rows=265 loops=1)
     516                          Buckets: 1024  Batches: 1  Memory Usage: 23kB
     517                          ->  Hash Right Join  (cost=89.81..153.70 rows=265 width=23) (actual time=0.477..1.133 rows=265 loops=1)
     518                                Hash Cond: (l.zona_id = z.zona_id)
     519                                ->  Seq Scan on lokacija l  (cost=0.00..56.00 rows=3000 width=8) (actual time=0.009..0.232 rows=3000 loops=1)
     520                                ->  Hash  (cost=86.50..86.50 rows=265 width=19) (actual time=0.441..0.442 rows=265 loops=1)
     521                                      Buckets: 1024  Batches: 1  Memory Usage: 22kB
     522                                      ->  Seq Scan on zona z  (cost=0.00..86.50 rows=265 width=19) (actual time=0.036..0.391 rows=265 loops=1)
     523                                            Filter: ((ime)::text = 'bolnichka zona'::text)
     524                                            Rows Removed by Filter: 2735
     525Planning Time: 0.561 ms
     526Execution Time: 491.742 ms
     527}}}
     528
     529
     530== Анализа на View9: Локации со најмногу жалби ==
     531
     532Прашалниците кои ќе ги тестираме се следните:
     533
     534{{{
     535-- 9A: приказ на бројот на жалби за конкретна зона
     536SELECT *
     537FROM vw_lokacii_so_najmnogu_zalbi
     538WHERE lokacija_id = 15;
     539
     540-- 9B: анализа на планот на извршување за конкретна зона
     541EXPLAIN ANALYZE
     542SELECT *
     543FROM vw_lokacii_so_najmnogu_zalbi
     544WHERE lokacija_id = 15;
     545}}}
     546
     5471. Примарен филтер за погледот `vw_lokacii_so_najmnogu_zalbi` ќе биде според `zona`. Погледот ќе се користи за приказ на локации со најголем број поднесени жалби во одредена зона.
     548
     5492. Примарен случај на употреба ќе биде анализа на локации каде што граѓаните најчесто поднесуваат жалби. За овој поглед перформансите се важни бидејќи се поврзуваат табелите `Lokacija`, `Zona`, `Kamera`, `Prekrsok` и `Zalba`, а потоа се врши групирање со `COUNT`.
     550
     551'''Време на извршување без индекси:'''
     552
     553'''9A - 1974 ms'''
     554
     555При извршување на прашалникот:
     556
     557{{{
     558SELECT *
     559FROM vw_lokacii_so_najmnogu_zalbi
     560WHERE lokacija_id = 15;
     561}}}
     562
     563Прашалникот успешно го прикажува бројот на жалби за локацијата со идентификатор `15`.
     564
     565При извршување на прашалникот, DataGrip прикажува време на извршување од 1974 ms, односно приближно 2 секунди.
     566
     567'''9B - 2474 ms'''
     568
     569{{{
     570GroupAggregate  (cost=272610.26..272627.87 rows=10 width=39) (actual time=2406.148..2406.157 rows=1 loops=1)
     571  Group Key: z.ime
     572  ->  Sort  (cost=272610.26..272616.10 rows=2335 width=35) (actual time=2405.597..2405.740 rows=2364 loops=1)
     573        Sort Key: z.ime
     574        Sort Method: quicksort  Memory: 226kB
     575        ->  Hash Right Join  (cost=94649.53..272479.63 rows=2335 width=35) (actual time=496.478..2403.630 rows=2364 loops=1)
     576              Hash Cond: (zl.prekrsok_id = p.prekrsok_id)
     577              ->  Seq Scan on zalba zl  (cost=0.00..151556.27 rows=7000127 width=8) (actual time=0.036..795.482 rows=7000000 loops=1)
     578              ->  Hash  (cost=94642.98..94642.98 rows=524 width=35) (actual time=496.302..496.309 rows=334 loops=1)
     579                    Buckets: 1024  Batches: 1  Memory Usage: 31kB
     580                    ->  Nested Loop Left Join  (cost=58.07..94642.98 rows=524 width=35) (actual time=26.370..496.059 rows=334 loops=1)
     581                          ->  Nested Loop Left Join  (cost=0.56..16.60 rows=1 width=31) (actual time=25.803..25.811 rows=1 loops=1)
     582                                ->  Index Scan using lokacija_pkey on lokacija l  (cost=0.28..8.30 rows=1 width=20) (actual time=0.015..0.021 rows=1 loops=1)
     583                                      Index Cond: (lokacija_id = 15)
     584                                ->  Index Scan using zona_pkey on zona z  (cost=0.28..8.30 rows=1 width=19) (actual time=0.023..0.024 rows=1 loops=1)
     585                                      Index Cond: (zona_id = l.zona_id)
     586                          ->  Hash Right Join  (cost=57.51..94621.14 rows=524 width=8) (actual time=0.560..470.129 rows=334 loops=1)
     587                                Hash Cond: (p.kamera_id = k.kamera_id)
     588                                ->  Seq Scan on prekrsok p  (cost=0.00..90434.77 rows=1570677 width=8) (actual time=0.011..303.832 rows=1000010 loops=1)
     589                                ->  Hash  (cost=57.50..57.50 rows=1 width=8) (actual time=0.277..0.279 rows=1 loops=1)
     590                                      Buckets: 1024  Batches: 1  Memory Usage: 9kB
     591                                      ->  Seq Scan on kamera k  (cost=0.00..57.50 rows=1 width=8) (actual time=0.026..0.269 rows=1 loops=1)
     592                                            Filter: (lokacija_id = 15)
     593                                            Rows Removed by Filter: 2999
     594Planning Time: 0.682 ms
     595JIT:
     596  Functions: 36
     597"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
     598"  Timing: Generation 2.019 ms (Deform 1.148 ms), Inlining 0.000 ms, Optimization 1.004 ms, Emission 24.890 ms, Total 27.912 ms"
     599Execution Time: 2408.367 ms
     600}}}
     601
     602