Changes between Version 83 and Version 84 of QueryOptimization


Ignore:
Timestamp:
06/30/26 22:30:58 (5 days ago)
Author:
231027
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v83 v84  
    44== Анализа и оптимизација на `Venue_Layout`
    55
    6 Овој поглед ја прикажува деталната физичка структура на секој објект (сала), поврзувајќи ги поединечните седишта со нивните сектори и самите локации. Патеката на релациите е поставена линеарно, овозможувајќи брза проверка на точната позиција на седиштето преку неговиот ред и број.
     6Овој поглед ја прикажува деталната физичка структура на секој објект, декомпонирајќи го просторот од ниво на локација до конкретно седиште. Со линеарно поврзување на хиерархијата `Venue -> Section -> Seat`, погледот овозможува брза верификација на капацитетот и распоредот, што е неопходно за логистичко планирање на настаните.
    77
    88{{{
    99
    1010CREATE OR REPLACE VIEW "Venue_Layout" AS
    11 SELECT v.venue_id,
    12        v.name AS venue_name,
    13        s.section_id,
    14        s.name AS section_name,
    15        st.seat_id,
    16        st.row_number,
    17        st.seat_number
     11SELECT v.venue_id, v.name AS venue_name, s.section_id, s.name AS section_name, st.seat_id, st.row_number, st.seat_number
    1812FROM "Venue" v
    19 JOIN "Section" s ON v.venue_id = s.venue_id
    20 JOIN "Seat" st ON s.section_id = st.section_id;
    21 
    22 }}}
    23 
    24 Погледот е веќе најоптимално напишан и не треба да се преуредува.
     13    JOIN "Section" s ON v.venue_id = s.venue_id
     14    JOIN "Seat" st ON s.section_id = st.section_id;
     15
     16}}}
     17
     18Погледот е веќе најоптимално напишан со користење на постоечките уникатни констреинти и не бара дополнителни модификации.
    2519
    2620==== 1. Примарен филтер:
    … …  
    3428==== 3. Иницијално време:
    3529
    36  * '''SELECT:''' 0.475 ms (Екстремно брзо поради постоечките уникатни констреинти `uq_section_venue_name` на табелата `Section` и `uq_seat_section_number` на табелата `Seat`).
    37  * '''INSERT:''' 19.912 ms (Релативно бавно, каде што најголемиот дел од времето паѓа на тригер проверката за '''foreign key''' констреинтот).
    38  * '''UPDATE:''' 0.137 ms (Инстантна брзина благодарение на примарниот клуч).
     30 * '''SELECT:''' 2.190 ms
     31 * '''INSERT:''' 4.752 ms
     32 * '''UPDATE:''' 0.133 ms
    3933
    4034==== 4. Анализа на планот на извршување (без индекси):
    4135
    42 При '''SELECT''' операцијата, PostgreSQL паметно ги користи веќе постоечките уникатни индекси генерирани од бизнис констреинтите, овозможувајќи брз '''Index Scan'''. Меѓутоа, при '''INSERT''' во табелата `Seat`, базата троши дури 19.752 ms само на тригерот за проверка на '''foreign key''' (`fk_seat_section`), бидејќи без ажурирана статистика, планерот мора рачно да ја проверува релацијата на диск.
     36При '''SELECT''' операцијата, '''PostgreSQL''' паметно ги користи веќе постоечките уникатни индекси генерирани од бизнис констреинтите, овозможувајќи брз '''Index Scan'''. Планерот успешно ги мапира релациите без потреба од дополнителни оптимизации.
    4337
    4438 * '''SELECT'''
    … …  
    4741
    4842EXPLAIN ANALYZE
    49 SELECT * FROM "Venue_Layout"
     43SELECT *
     44FROM "Venue_Layout"
    5045WHERE venue_id = 1;
    5146
    5247}}}
    5348
    54 ||= QUERY PLAN =||
    55 ||Nested Loop  (cost\=1.01..8110.27 rows\=1886 width\=54) (actual time\=0.152..0.475 rows\=775.00 loops\=1)||
    56 ||  Buffers: shared hit\=21 read\=13 dirtied\=1||
    57 ||  ->  Nested Loop  (cost\=0.57..23.73 rows\=5 width\=38) (actual time\=0.129..0.131 rows\=5.00 loops\=1)||
    58 ||        Buffers: shared read\=6||
    59 ||        ->  Index Scan using ""Venue_pkey"" on ""Venue"" v  (cost\=0.29..8.30 rows\=1 width\=28) (actual time\=0.061..0.061 rows\=1.00 loops\=1)||
    60 ||              Index Cond: (venue_id \= 1)||
    61 ||              Index Searches: 1||
    62 ||              Buffers: shared read\=3||
    63 ||        ->  Index Scan using uq_section_venue_name on ""Section"" s  (cost\=0.29..15.38 rows\=5 width\=18) (actual time\=0.051..0.052 rows\=5.00 loops\=1)||
    64 ||              Index Cond: (venue_id \= 1)||
    65 ||              Index Searches: 1||
    66 ||              Buffers: shared read\=3||
    67 ||  ->  Index Scan using uq_seat_section_number on ""Seat"" st  (cost\=0.44..1608.23 rows\=908 width\=24) (actual time\=0.009..0.050 rows\=155.00 loops\=5)||
    68 ||        Index Cond: (section_id \= s.section_id)||
    69 ||        Index Searches: 5||
    70 ||        Buffers: shared hit\=21 read\=7 dirtied\=1||
    71 ||Planning:||
    72 ||  Buffers: shared hit\=4 read\=11||
    73 ||Planning Time: 0.631 ms||
    74 ||Execution Time: 0.577 ms||
     49||= QUERY PLAN =||=  =||
     50||Nested Loop  (cost\=1.01..9625.30 rows\=2264 width\=54) (actual time\=0.868..2.190 rows\=775.00 loops\=1)||||
     51||Buffers||shared hit\=21 read\=13 dirtied\=1||
     52||->  Nested Loop  (cost\=0.57..25.51 rows\=6 width\=38) (actual time\=0.622..0.627 rows\=5.00 loops\=1)||||
     53||Buffers||shared read\=6||
     54||        ->  Index Scan using ""Venue_pkey"" on ""Venue"" v  (cost\=0.29..8.30 rows\=1 width\=28) (actual time\=0.071..0.072 rows\=1.00 loops\=1)||||
     55||Index Cond||(venue_id \= 1)||
     56||Index Searches||1||
     57||Buffers||shared read\=3||
     58||        ->  Index Scan using uq_section_venue_name on ""Section"" s  (cost\=0.29..17.14 rows\=6 width\=18) (actual time\=0.548..0.550 rows\=5.00 loops\=1)||||
     59||Index Cond||(venue_id \= 1)||
     60||Index Searches||1||
     61||Buffers||shared read\=3||
     62||  ->  Index Scan using uq_seat_section_number on ""Seat"" st  (cost\=0.44..1590.95 rows\=901 width\=24) (actual time\=0.056..0.282 rows\=155.00 loops\=5)||||
     63||Index Cond||(section_id \= s.section_id)||
     64||Index Searches||5||
     65||Buffers||shared hit\=21 read\=7 dirtied\=1||
     66||Planning||||
     67||Buffers||shared hit\=14 read\=22||
     68||Planning Time||5.752 ms||
     69||Execution Time||2.271 ms||
    7570
    7671 * '''INSERT'''
    … …  
    8479}}}
    8580
    86 ||= QUERY PLAN =||
    87 ||Insert on ""Seat""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.135..0.136 rows\=0.00 loops\=1)||
    88 ||  Buffers: shared hit\=5 read\=3 dirtied\=1||
    89 ||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=24) (actual time\=0.001..0.001 rows\=1.00 loops\=1)||
    90 ||Planning Time: 0.058 ms||
    91 ||Trigger for constraint fk_seat_section: time\=19.752 calls\=1||
    92 ||Execution Time: 19.912 ms||
     81||= QUERY PLAN =||=  =||
     82||Insert on ""Seat""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=4.492..4.493 rows\=0.00 loops\=1)||||
     83||Buffers||shared hit\=5 read\=6 dirtied\=3||
     84||->  Result  (cost\=0.00..0.01 rows\=1 width\=24) (actual time\=0.001..0.001 rows\=1.00 loops\=1)||||
     85||Planning Time||0.033 ms||
     86||Trigger for constraint fk_seat_section||time\=0.243 calls\=1||
     87||Execution Time||4.752 ms||
    9388
    9489 * '''UPDATE'''
    … …  
    9792
    9893EXPLAIN ANALYZE
    99 UPDATE "Seat"
    100 SET seat_number = 100
    101 WHERE seat_id = 99999999;
    102 
    103 }}}
    104 
    105 ||= QUERY PLAN =||
    106 ||Update on ""Seat""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.106..0.106 rows\=0.00 loops\=1)||
    107 ||  Buffers: shared hit\=12||
    108 ||  ->  Index Scan using ""Seat_pkey"" on ""Seat""  (cost\=0.44..8.46 rows\=1 width\=10) (actual time\=0.048..0.049 rows\=1.00 loops\=1)||
    109 ||        Index Cond: (seat_id \= 99999999)||
    110 ||        Index Searches: 1||
    111 ||        Buffers: shared hit\=4||
    112 ||Planning Time: 0.171 ms||
    113 ||Execution Time: 0.137 ms||
     94UPDATE "Seat" SET seat_number = 100 WHERE seat_id = 99999999;
     95
     96}}}
     97
     98||= QUERY PLAN =||=  =||
     99||Update on ""Seat""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.101..0.102 rows\=0.00 loops\=1)||||
     100||Buffers||shared hit\=12||
     101||  ->  Index Scan using ""Seat_pkey"" on ""Seat""  (cost\=0.44..8.46 rows\=1 width\=10) (actual time\=0.031..0.032 rows\=1.00 loops\=1)||||
     102||Index Cond||(seat_id \= 99999999)||
     103||Index Searches||1||
     104||Buffers||shared hit\=4||
     105||Planning Time||0.125 ms||
     106||Execution Time||0.133 ms||
    114107
    115108==== 5. Оптимизација и индексирање:
    116109
    117 Бидејќи постоечките уникатни констреинти веќе идеално ги покриваат '''JOIN''' релациите, креирањето на дополнителни индекси е непотребно и би довело до залудно трошење на мемориски ресурси. Наместо тоа, за да го решиме тесното грло при '''INSERT''' операциите, се извршува наредбата ANALYZE за табелите во релација со цел да се обноват статистиките на внатрешниот планер.
     110Бидејќи постоечките уникатни констреинти веќе идеално ги покриваат '''JOIN''' релациите, креирањето на дополнителни индекси е непотребно и би довело до залудно трошење на мемориски ресурси. Погледот е веќе оптимизиран. Доволно е само да се изврши:
    118111
    119112{{{
    … …  
    124117
    125118}}}
    126 
    127 ==== 6. Резултат по оптимизација:
    128 
    129 По извршување на '''ANALYZE''', базата стекна целосен увид во дистрибуцијата на податоците, со што времето на '''INSERT''' се намали на 0.628 ms, што претставува забрзување од околу 30 пати. Операциите за '''SELECT''' и '''UPDATE''' ги задржаа своите врвни перформанси во под-милисекунден опсег.
    130119
    131120