Changes between Version 4 and Version 5 of QueryOptimization


Ignore:
Timestamp:
05/30/26 17:23:12 (12 days ago)
Author:
231018
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v4 v5  
    1616}}}
    1717
    18 '''Време на извршување без индекси:'''
     18==== Време на извршување без индекси ====
    1919
    2020'''1A - 6259 ms'''
    … …  
    7979}}}
    8080
     81==== Време на извршување со индекси ====
     82
     83'''1A - 3896 ms (беше 6259 ms)'''
     84
     85'''1B - 834 ms (беше 2565.134 ms)'''
     86
     87{{{
     88Nested Loop Left Join  (cost=12.68..7792.73 rows=2 width=156) (actual time=0.351..376.504 rows=1290 loops=1)
     89  InitPlan 2
     90    ->  Result  (cost=0.45..0.46 rows=1 width=4) (actual time=0.018..0.020 rows=1 loops=1)
     91          InitPlan 1
     92            ->  Limit  (cost=0.42..0.45 rows=1 width=4) (actual time=0.016..0.017 rows=1 loops=1)
     93                  ->  Index Only Scan Backward using idx_prekrsok_datum on prekrsok  (cost=0.42..21048.60 rows=1000010 width=4) (actual time=0.015..0.016 rows=1 loops=1)
     94                        Index Cond: (datum IS NOT NULL)
     95                        Heap Fetches: 0
     96  ->  Nested Loop Left Join  (cost=11.80..7791.09 rows=2 width=142) (actual time=0.324..349.304 rows=1290 loops=1)
     97        ->  Nested Loop Left Join  (cost=11.37..7786.67 rows=1 width=128) (actual time=0.297..340.552 rows=645 loops=1)
     98              ->  Nested Loop Left Join  (cost=11.09..7786.29 rows=1 width=113) (actual time=0.253..338.210 rows=645 loops=1)
     99                    ->  Nested Loop Left Join  (cost=10.81..7785.94 rows=1 width=113) (actual time=0.244..335.905 rows=645 loops=1)
     100                          ->  Nested Loop  (cost=10.53..7785.41 rows=1 width=113) (actual time=0.236..333.094 rows=645 loops=1)
     101                                Join Filter: (pv.vozilo_id = v.vozilo_id)
     102                                ->  Index Scan using uq_vozilo_registarska on vozilo v  (cost=0.43..8.45 rows=1 width=14) (actual time=0.024..0.026 rows=1 loops=1)
     103                                      Index Cond: ((registarska_oznaka)::text = 'SR-2625-AC'::text)
     104                                ->  Nested Loop  (cost=10.10..7768.49 rows=678 width=107) (actual time=0.188..332.815 rows=645 loops=1)
     105                                      ->  Bitmap Heap Scan on prekrsok p  (cost=9.68..2525.70 rows=678 width=103) (actual time=0.174..4.152 rows=645 loops=1)
     106                                            Recheck Cond: (datum = (InitPlan 2).col1)
     107                                            Heap Blocks: exact=634
     108                                            ->  Bitmap Index Scan on idx_prekrsok_datum  (cost=0.00..9.51 rows=678 width=0) (actual time=0.075..0.075 rows=645 loops=1)
     109                                                  Index Cond: (datum = (InitPlan 2).col1)
     110                                      ->  Index Only Scan using prekrsok_vozilo_pkey on prekrsok_vozilo pv  (cost=0.42..7.72 rows=1 width=8) (actual time=0.508..0.508 rows=1 loops=645)
     111                                            Index Cond: (prekrsok_id = p.prekrsok_id)
     112                                            Heap Fetches: 645
     113                          ->  Index Scan using kamera_pkey on kamera k  (cost=0.28..0.53 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=645)
     114                                Index Cond: (kamera_id = p.kamera_id)
     115                    ->  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)
     116                          Index Cond: (lokacija_id = k.lokacija_id)
     117              ->  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)
     118                    Index Cond: (zona_id = l.zona_id)
     119        ->  Index Only Scan using prekrsok_storitel_pkey on prekrsok_storitel ps  (cost=0.43..4.40 rows=2 width=18) (actual time=0.011..0.013 rows=2 loops=645)
     120              Index Cond: (prekrsok_id = p.prekrsok_id)
     121              Heap Fetches: 0
     122  ->  Index Scan using gragjanin_pkey on gragjanin g  (cost=0.43..0.59 rows=1 width=42) (actual time=0.020..0.020 rows=1 loops=1290)
     123        Index Cond: (embg = (ps.storitel_embg)::bpchar)
     124Planning Time: 2.778 ms
     125Execution Time: 376.778 ms
     126}}}
     127
     128По креирањето на индексот `idx_prekrsok_datum`, повторно беше извршен истиот прашалник врз погледот `vw_prekrsoci_denes`.
     129
     130Времето на извршување на обичниот `SELECT` се намали од 6259 ms на 3896 ms, додека времето на `EXPLAIN ANALYZE` се намали од 2565.134 ms на 834 ms. Ова покажува дека индексот има позитивен ефект врз извршувањето на прашалникот.
     131
     132Иако прашалникот сè уште поврзува повеќе табели, по оптимизацијата времето е значително подобрено и е поблиску до прифатливиот праг.
     133
    81134== Анализа на View2: Неплатени казни во последните 2 недели ==
    82135
    … …  
    96149}}}
    97150
    98 '''Време на извршување без индекси:'''
     151==== Време на извршување без индекси ====
    99152
    100153'''2A - 1246 ms'''
    … …  
    161214}}}
    162215
     216==== Време на извршување со индекси ====
     217
     218'''2A - 1822 ms (беше 1246 ms)'''
     219
     220При повторно извршување на прашалникот по креирање на индексот, DataGrip прикажува време на извршување од 1822 ms. Прашалникот не враќа редови, бидејќи казната со `kazna_id = 523` не ги исполнува условите на погледот.
     221
     222'''2B - 950.735 (беше 430.601 ms)'''
     223
     224{{{
     225Gather  (cost=192893.00..270766.77 rows=2 width=141) (actual time=936.691..948.469 rows=0 loops=1)
     226  Workers Planned: 4
     227  Workers Launched: 4
     228  InitPlan 1
     229    ->  Finalize Aggregate  (cost=95945.42..95945.43 rows=1 width=8) (actual time=400.346..400.476 rows=1 loops=1)
     230          ->  Gather  (cost=95945.00..95945.41 rows=4 width=4) (actual time=399.914..400.436 rows=5 loops=1)
     231                Workers Planned: 4
     232                Workers Launched: 4
     233                ->  Partial Aggregate  (cost=94945.00..94945.01 rows=1 width=4) (actual time=365.516..365.518 rows=1 loops=5)
     234                      ->  Parallel Seq Scan on kazna  (cost=0.00..88695.00 rows=2500000 width=4) (actual time=0.056..180.906 rows=2000000 loops=5)
     235  InitPlan 2
     236    ->  Finalize Aggregate  (cost=95945.42..95945.43 rows=1 width=4) (actual time=393.098..393.178 rows=1 loops=1)
     237          ->  Gather  (cost=95945.00..95945.41 rows=4 width=4) (actual time=392.880..393.151 rows=5 loops=1)
     238                Workers Planned: 4
     239                Workers Launched: 4
     240                ->  Partial Aggregate  (cost=94945.00..94945.01 rows=1 width=4) (actual time=360.995..360.996 rows=1 loops=5)
     241                      ->  Parallel Seq Scan on kazna kazna_1  (cost=0.00..88695.00 rows=2500000 width=4) (actual time=0.060..180.915 rows=2000000 loops=5)
     242  ->  Nested Loop Left Join  (cost=2.14..77875.71 rows=1 width=141) (actual time=107.787..107.791 rows=0 loops=5)
     243        ->  Nested Loop Left Join  (cost=1.72..77875.03 rows=1 width=135) (actual time=107.787..107.790 rows=0 loops=5)
     244              ->  Nested Loop Left Join  (cost=1.29..77866.58 rows=1 width=131) (actual time=107.786..107.789 rows=0 loops=5)
     245                    ->  Nested Loop Left Join  (cost=0.86..77865.98 rows=1 width=117) (actual time=107.786..107.788 rows=0 loops=5)
     246                          ->  Nested Loop  (cost=0.43..77861.50 rows=1 width=103) (actual time=107.785..107.787 rows=0 loops=5)
     247                                ->  Parallel Seq Scan on prekrsok p  (cost=0.00..77853.03 rows=1 width=91) (actual time=101.813..107.757 rows=0 loops=5)
     248                                      Filter: (kazna_id = 523)
     249                                      Rows Removed by Filter: 200002
     250                                ->  Index Scan using kazna_pkey on kazna k  (cost=0.43..8.46 rows=1 width=16) (actual time=0.135..0.135 rows=0 loops=1)
     251                                      Index Cond: (kazna_id = 523)
     252                                      Filter: ((rok_na_plakanje >= (InitPlan 1).col1) AND (rok_na_plakanje <= (InitPlan 2).col1) AND (status = 0))
     253                                      Rows Removed by Filter: 1
     254                          ->  Index Only Scan using prekrsok_storitel_pkey on prekrsok_storitel ps  (cost=0.43..4.46 rows=2 width=18) (never executed)
     255                                Index Cond: (prekrsok_id = p.prekrsok_id)
     256                                Heap Fetches: 0
     257                    ->  Index Scan using gragjanin_pkey on gragjanin g  (cost=0.43..0.59 rows=1 width=42) (never executed)
     258                          Index Cond: (embg = (ps.storitel_embg)::bpchar)
     259              ->  Index Only Scan using prekrsok_vozilo_pkey on prekrsok_vozilo pv  (cost=0.42..8.44 rows=1 width=8) (never executed)
     260                    Index Cond: (prekrsok_id = p.prekrsok_id)
     261                    Heap Fetches: 0
     262        ->  Index Scan using vozilo_pkey on vozilo v  (cost=0.43..0.68 rows=1 width=14) (never executed)
     263              Index Cond: (vozilo_id = pv.vozilo_id)
     264Planning Time: 1.577 ms
     265JIT:
     266  Functions: 159
     267"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
     268"  Timing: Generation 13.514 ms (Deform 5.247 ms), Inlining 0.000 ms, Optimization 6.953 ms, Emission 136.174 ms, Total 156.641 ms"
     269Execution Time: 950.735 ms
     270}}}
     271
     272За овој поглед беше креиран индексот `idx_kazna_status`, бидејќи погледот филтрира казни според нивниот статус.
     273
     274Во овој случај, по креирањето на индексот не се добива подобрување на времето на извршување. Причината е што прашалникот дополнително филтрира според `kazna_id = 523`, а `kazna_id` е примарен клуч и PostgreSQL веќе користи индекс преку `kazna_pkey`.
     275
     276Затоа новиот индекс `idx_kazna_status` не е пресуден за овој конкретен прашалник. Бидејќи времето на извршување е околу 1.2 секунди, прашалникот сè уште се смета за прифатлив и не е потребна дополнителна оптимизација.
    163277
    164278== Анализа на View3: Локации со најмногу прекршоци ==
    … …  
    179293}}}
    180294
    181 '''Време на извршување без индекси:'''
     295==== Време на извршување без индекси ====
    182296
    183297'''3A - 109 ms'''
    … …  
    239353}}}
    240354
    241 '''Време на извршување без индекси:'''
     355==== Време на извршување без индекси ====
    242356
    243357'''4A - 618 ms'''
    … …  
    295409}}}
    296410
    297 '''Време на извршување без индекси:'''
     411==== Време на извршување без индекси ====
    298412
    299413'''5A - 433 ms'''
    … …  
    328442}}}
    329443
    330 
     444==== Време на извршување со индекси: ====
     445
     446'''5A - 898 ms (беше 433 ms)'''
     447
     448При повторно извршување на прашалникот по креирање на индексот, DataGrip прикажува време на извршување од 898 ms. Во овој случај времето не е намалено кај обичниот SELECT, но прашалникот и понатаму се извршува во прифатливо време.
     449
     450'''5B - 107.553 ms (беше 78.542 ms)'''
     451
     452{{{
     453GroupAggregate  (cost=1000.43..44134.74 rows=1 width=50) (actual time=99.588..107.507 rows=1 loops=1)
     454  ->  Nested Loop Left Join  (cost=1000.43..44134.73 rows=2 width=46) (actual time=19.063..107.495 rows=1 loops=1)
     455        ->  Index Scan using gragjanin_pkey on gragjanin g  (cost=0.43..8.45 rows=1 width=42) (actual time=0.026..0.032 rows=1 loops=1)
     456              Index Cond: (embg = '3006956460025'::bpchar)
     457        ->  Gather  (cost=1000.00..44126.26 rows=2 width=18) (actual time=19.033..107.457 rows=1 loops=1)
     458              Workers Planned: 4
     459              Workers Launched: 4
     460              ->  Parallel Seq Scan on prekrsok_storitel ps  (cost=0.00..43126.06 rows=1 width=18) (actual time=51.393..67.470 rows=0 loops=5)
     461                    Filter: ((storitel_embg)::bpchar = '3006956460025'::bpchar)
     462                    Rows Removed by Filter: 400004
     463Planning Time: 0.258 ms
     464Execution Time: 107.553 ms
     465}}}
     466
     467Иако беше креиран индексот idx_prekrsok_storitel_embg, PostgreSQL продолжува да користи Parallel Seq Scan врз табелата Prekrsok_Storitel. Поради тоа не се забележува значително подобрување на перформансите. Сепак, времето на извршување останува многу мало и прифатливо.
    331468
    332469== Анализа на View6: Неактивни камери ==
    … …  
    3514882. Примарен случај на употреба ќе биде следење на неисправни, исклучени или неактивни камери. За овој поглед перформансите се важни бидејќи се поврзуваат табелите `Kamera`, `Lokacija`, `Zona` и `TipKamera`.
    352489
    353 '''Време на извршување без индекси:'''
     490==== Време на извршување без индекси ====
    354491
    355492'''6A - 72 ms'''
    … …  
    4175542. Примарен случај на употреба ќе биде следење на активните жалби и нивната моментална состојба. За овој поглед перформансите се важни бидејќи се поврзуваат табелите `Zalba`, `Korisnik`, `Gragjanin` и `Prekrsok`.
    418555
    419 '''Време на извршување без индекси:'''
     556==== Време на извршување без индекси ====
    420557
    421558'''7A - 34068 ms'''
    … …  
    461598}}}
    462599
     600'''Креирани индекси за оптимизација:'''
     601
     602{{{
     603CREATE INDEX idx_zalba_status
     604ON Zalba(status);
     605
     606CREATE INDEX idx_zalba_korisnik_id
     607ON Zalba(korisnik_id);
     608
     609CREATE INDEX idx_zalba_prekrsok_id
     610ON Zalba(prekrsok_id);
     611}}}
     612
     613Индексот `idx_zalba_status` се користи за побрзо филтрирање на жалбите според статус. Индексите `idx_zalba_korisnik_id` и `idx_zalba_prekrsok_id` се додаваат бидејќи табелата `Zalba` се поврзува со `Korisnik` и `Prekrsok` преку овие колони.
     614
     615==== Време на извршување со индекси ====
     616
     617'''7A - 2044 ms (беше 34068 ms)'''
     618
     619При повторно извршување на прашалникот по креирање на индексите, времето на извршување се намали од 34 секунди на околу 2 секунди.
     620
     621'''7B - 9958.522 ms (беше 87808.602 ms)'''
     622
     623{{{
     624Gather  (cost=1001.28..165947.35 rows=17682 width=178) (actual time=13.649..9870.867 rows=1751073 loops=1)
     625  Workers Planned: 4
     626  Workers Launched: 4
     627  ->  Nested Loop Left Join  (cost=1.28..163179.15 rows=4420 width=178) (actual time=15.231..9086.458 rows=350215 loops=5)
     628        ->  Nested Loop Left Join  (cost=0.85..134440.33 rows=4420 width=95) (actual time=15.136..7178.456 rows=350215 loops=5)
     629              ->  Nested Loop Left Join  (cost=0.43..131621.92 rows=4420 width=67) (actual time=15.025..2527.731 rows=350215 loops=5)
     630                    ->  Parallel Seq Scan on zalba z  (cost=0.00..112180.00 rows=4420 width=57) (actual time=14.877..530.185 rows=350215 loops=5)
     631"                          Filter: (((status)::text = 'podnesena'::text) AND (lower((status)::text) = ANY ('{podnesena,vo_postapka}'::text[])))"
     632                          Rows Removed by Filter: 1049785
     633                    ->  Index Scan using korisnik_pkey on korisnik k  (cost=0.43..4.40 rows=1 width=18) (actual time=0.005..0.005 rows=1 loops=1751073)
     634                          Index Cond: (korisnik_id = z.korisnik_id)
     635              ->  Index Scan using gragjanin_pkey on gragjanin g  (cost=0.43..0.64 rows=1 width=42) (actual time=0.013..0.013 rows=1 loops=1751073)
     636                    Index Cond: (embg = (k.embg)::bpchar)
     637        ->  Index Scan using prekrsok_pkey on prekrsok p  (cost=0.42..6.50 rows=1 width=87) (actual time=0.005..0.005 rows=1 loops=1751073)
     638              Index Cond: (prekrsok_id = z.prekrsok_id)
     639Planning Time: 1.002 ms
     640JIT:
     641  Functions: 90
     642"  Options: Inlining false, Optimization false, Expressions true, Deforming true"
     643"  Timing: Generation 7.452 ms (Deform 3.796 ms), Inlining 0.000 ms, Optimization 3.529 ms, Emission 70.615 ms, Total 81.595 ms"
     644Execution Time: 9958.522 ms
     645
     646}}}
     647
     648По креирањето на индексите `idx_zalba_status`, `idx_zalba_korisnik_id` и `idx_zalba_prekrsok_id`, PostgreSQL може поефикасно да ги пронајде записите во табелата `Zalba` и да ги изврши спојувањата со табелите `Korisnik` и `Prekrsok`.
     649
     650Времето на извршување на прашалникот се намали од 87808.602 ms на 9958.522 ms, што претставува подобрување од приближно 10 пати.
     651
    463652
    464653== Анализа на View8: Зони со најмногу прекршоци ==
    … …  
    4836722. Примарен случај на употреба ќе биде анализа на зони со најголем број прекршоци. За овој поглед перформансите се важни бидејќи се поврзуваат табелите `Zona`, `Lokacija`, `Kamera` и `Prekrsok`, а потоа се врши групирање со `COUNT`.
    484673
    485 '''Време на извршување без индекси:'''
     674==== Време на извршување без индекси ====
    486675
    487676'''8A - 584 ms'''
    … …  
    5497382. Примарен случај на употреба ќе биде анализа на локации каде што граѓаните најчесто поднесуваат жалби. За овој поглед перформансите се важни бидејќи се поврзуваат табелите `Lokacija`, `Zona`, `Kamera`, `Prekrsok` и `Zalba`, а потоа се врши групирање со `COUNT`.
    550739
    551 '''Време на извршување без индекси:'''
     740==== Време на извршување без индекси ====
    552741
    553742'''9A - 1974 ms'''