Changes between Version 74 and Version 75 of QueryOptimization


Ignore:
Timestamp:
06/29/26 23:45:04 (6 days ago)
Author:
231027
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v74 v75  
    22
    33
    4 == Анализа и оптимизација на `Performer_Events`
     4== Анализа и оптимизација на `Venue_Layout`
    55
    6 Овој поглед дава преглед на сите изведувачи и настаните на кои тие учествуваат, заедно со прецизниот термин на нивниот настап.
     6Овој поглед ја прикажува деталната физичка структура на секој објект (сала), поврзувајќи ги поединечните седишта со нивните сектори и самите локации. Патеката на релациите е поставена линеарно, овозможувајќи брза проверка на точната позиција на седиштето преку неговиот ред и број.
    77
    88{{{
    99
    10 CREATE VIEW "Performer_Events" AS
    11 SELECT p.performer_id,
    12        p.name AS performer_name,
    13        e.event_id,
    14        e.name AS event_name,
    15        eh.event_time
    16 FROM "Performer" p
    17 JOIN "Event_Happening_Performer" ehp ON p.performer_id = ehp.performer_id
    18 JOIN "Event_Happening" eh ON ehp.event_happening_id = eh.event_happening_id
    19 JOIN "Event" e ON eh.event_id = e.event_id;
     10CREATE OR REPLACE VIEW "Venue_Layout" AS
     11SELECT 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
     18FROM "Venue" v
     19JOIN "Section" s ON v.venue_id = s.venue_id
     20JOIN "Seat" st ON s.section_id = st.section_id;
    2021
    2122}}}
    … …  
    2324==== 1. Примарен филтер:
    2425
    25 Примарен филтер за овој поглед ќе биде според `performer_id` (ID на изведувач), бидејќи најчестото пребарување е за да се видат настапите на конкретен изведувач. Дополнително, ќе може да се пребарува и по `event_id`.
     26Примарен филтер за овој поглед е `venue_id` (ID на објектот), бидејќи најчестото пребарување е насочено кон визуелизација или вчитување на комплетната мапа на седишта за еден конкретен објект кога се купува билет.
    2627
    2728==== 2. Случај на употреба:
    2829
    29 Овој поглед е клучен за корисничкиот дел од апликацијата, каде што корисниците пребаруваат каде и кога настапува нивниот омилен изведувач. Бидејќи ова е акција што често се повторува од многу корисници истовремено, перформансите мора да бидат на високо ниво за да се обезбеди добро корисничко искуство.
     30Погледот се користи при интерактивниот приказ на салата/стадионот во корисничкиот интерфејс. Кога купувачот ќе избере настан, апликацијата мора веднаш да го исцрта распоредот на седишта по секции и редови за тој објект. Перформансите тука директно влијаат врз брзината на вчитување на корисничката страница за избор на седиште.
    3031
    3132==== 3. Иницијално време:
    3233
    33 Иницијалното време за извршување на погледот изнесува 3.249 s (3249 ms). Ова е исклучително бавно и неприфатливо за една веб-апликација, каде што секој одзив над 500ms се смета за критичен.
     34 * '''SELECT:''' 0.475 ms (Екстремно брзо поради постоечките уникатни констреинти `uq_section_venue_name` на табелата `Section` и `uq_seat_section_number` на табелата `Seat`).
     35 * '''INSERT:''' 19.912 ms (Релативно бавно, каде што најголемиот дел од времето паѓа на тригер проверката за '''foreign key''' констреинтот).
     36 * '''UPDATE:''' 0.137 ms (Инстантна брзина благодарение на примарниот клуч).
    3437
    3538==== 4. Анализа на планот на извршување (без индекси):
    3639
    37 Иако базата се обидува да користи постоечки '''Primary Key''' индекси, главниот проблем се јавува во спојувањето на четирите табели `Performer`, `Event_Happening_Performer`, `Event_Happening`, `Event`.
    38 
    39 Најбавниот дел е кај '''Nested Loop''' операциите и проверката на '''Foreign Keys''' при '''INSERT''' (1.424 s).
    40 
    41 Времето на планирање е исто така високо (1.8 s), што укажува на комплексност во резолвирањето на релациите без соодветни патеки.
     40При '''SELECT''' операцијата, PostgreSQL паметно ги користи веќе постоечките уникатни индекси генерирани од бизнис констреинтите, овозможувајќи брз '''Index Scan'''. Меѓутоа, при '''INSERT''' во табелата `Seat`, базата троши дури 19.752 ms само на тригерот за проверка на '''foreign key''' (`fk_seat_section`), бидејќи без ажурирана статистика, планерот мора рачно да ја проверува релацијата на диск.
    4241
    4342 * '''SELECT'''
    … …  
    4645
    4746EXPLAIN ANALYZE
    48     SELECT * FROM "Performer_Events" WHERE performer_id = 10;
     47SELECT * FROM "Venue_Layout"
     48WHERE venue_id = 1;
    4949
    5050}}}
    5151
    5252||= QUERY PLAN =||
    53 ||Nested Loop  (cost\=1.14..73.38 rows\=7 width\=68) (actual time\=1505.862..3249.242 rows\=4 loops\=1)||
    54 ||  ->  Nested Loop  (cost\=0.86..70.92 rows\=7 width\=37) (actual time\=1079.648..2259.465 rows\=4 loops\=1)||
    55 ||        ->  Index Scan using "Performer_pkey" on "Performer" p  (cost\=0.28..8.30 rows\=1 width\=21) (actual time\=701.003..701.005 rows\=1 loops\=1)||
    56 ||              Index Cond: (performer_id \= 10)||
    57 ||        ->  Nested Loop  (cost\=0.57..62.55 rows\=7 width\=24) (actual time\=378.637..1558.442 rows\=4 loops\=1)||
    58 ||              ->  Index Only Scan using uq_performer_at_time on "Event_Happening_Performer" ehp  (cost\=0.29..4.41 rows\=7 width\=16) (actual time\=90.817..90.838 rows\=4 loops\=1)||
    59 ||                    Index Cond: (performer_id \= 10)||
    60 ||                    Heap Fetches: 0||
    61 ||              ->  Index Scan using "Event_Happening_pkey" on "Event_Happening" eh  (cost\=0.29..8.30 rows\=1 width\=24) (actual time\=366.888..366.889 rows\=1 loops\=4)||
    62 ||                    Index Cond: (event_happening_id \= ehp.event_happening_id)||
    63 ||  ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=247.432..247.432 rows\=1 loops\=4)||
    64 ||        Index Cond: (event_id \= eh.event_id)||
    65 ||Planning Time: 1816.633 ms||
    66 ||Execution Time: 3249.314 ms||
     53||Nested Loop  (cost\=1.01..8110.27 rows\=1886 width\=54) (actual time\=0.152..0.475 rows\=775.00 loops\=1)||
     54||  Buffers: shared hit\=21 read\=13 dirtied\=1||
     55||  ->  Nested Loop  (cost\=0.57..23.73 rows\=5 width\=38) (actual time\=0.129..0.131 rows\=5.00 loops\=1)||
     56||        Buffers: shared read\=6||
     57||        ->  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)||
     58||              Index Cond: (venue_id \= 1)||
     59||              Index Searches: 1||
     60||              Buffers: shared read\=3||
     61||        ->  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)||
     62||              Index Cond: (venue_id \= 1)||
     63||              Index Searches: 1||
     64||              Buffers: shared read\=3||
     65||  ->  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)||
     66||        Index Cond: (section_id \= s.section_id)||
     67||        Index Searches: 5||
     68||        Buffers: shared hit\=21 read\=7 dirtied\=1||
     69||Planning:||
     70||  Buffers: shared hit\=4 read\=11||
     71||Planning Time: 0.631 ms||
     72||Execution Time: 0.577 ms||
    6773
    6874 * '''INSERT'''
    … …  
    7177
    7278EXPLAIN ANALYZE
    73     INSERT INTO "Event_Happening_Performer" (event_happening_id, performer_id)
    74     VALUES (1, 10);
     79INSERT INTO "Seat" (seat_id, section_id, row_number, seat_number)
     80VALUES (99999999, 1, 1, 99);
    7581
    7682}}}
    7783
    7884||= QUERY PLAN =||
    79 ||Insert on "Event_Happening_Performer"  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=558.243..558.244 rows\=0 loops\=1)||
    80 ||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=16) (actual time\=0.001..0.002 rows\=1 loops\=1)||
    81 ||Planning Time: 0.042 ms||
    82 ||Trigger for constraint fk_ehp_event_happening: time\=826.105 calls\=1||
    83 ||Trigger for constraint fk_ehp_performer: time\=40.075 calls\=1||
    84 ||Execution Time: 1424.451 ms||
     85||Insert on ""Seat""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.135..0.136 rows\=0.00 loops\=1)||
     86||  Buffers: shared hit\=5 read\=3 dirtied\=1||
     87||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=24) (actual time\=0.001..0.001 rows\=1.00 loops\=1)||
     88||Planning Time: 0.058 ms||
     89||Trigger for constraint fk_seat_section: time\=19.752 calls\=1||
     90||Execution Time: 19.912 ms||
    8591
    8692 * '''UPDATE'''
    … …  
    8995
    9096EXPLAIN ANALYZE
    91     UPDATE "Event_Happening_Performer"
    92     SET performer_id = 11
    93     WHERE event_happening_id = 1 AND performer_id = 10;
     97UPDATE "Seat"
     98SET seat_number = 100
     99WHERE seat_id = 99999999;
    94100
    95101}}}
    96102
    97103||= QUERY PLAN =||
    98 ||Update on "Event_Happening_Performer"  (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.219..0.219 rows\=0 loops\=1)||
    99 ||  ->  Index Scan using uq_performer_at_time on "Event_Happening_Performer"  (cost\=0.29..8.31 rows\=1 width\=14) (actual time\=0.120..0.121 rows\=1 loops\=1)||
    100 ||        Index Cond: ((performer_id \= 10) AND (event_happening_id \= 1))||
    101 ||Planning Time: 0.139 ms||
    102 ||Trigger for constraint fk_ehp_performer: time\=0.279 calls\=1||
    103 ||Execution Time: 23.621 ms||
     104||Update on ""Seat""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.106..0.106 rows\=0.00 loops\=1)||
     105||  Buffers: shared hit\=12||
     106||  ->  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)||
     107||        Index Cond: (seat_id \= 99999999)||
     108||        Index Searches: 1||
     109||        Buffers: shared hit\=4||
     110||Planning Time: 0.171 ms||
     111||Execution Time: 0.137 ms||
    104112
    105113==== 5. Оптимизација и индексирање:
    106114
    107 За да го решиме проблемот, воведуваме индекси на колоните кои служат за поврзување во '''JOIN''' условите и во '''WHERE''' филтрите:
    108 
    109  * `idx_ehp_performer_id` и `idx_ehp_happening_id` на табелата што ги поврзува изведувачите со термините.
    110 
    111  * `idx_event_happening_event_id` за побрзо поврзување на конкретниот термин со името на настанот.
     115Бидејќи постоечките уникатни констреинти веќе идеално ги покриваат '''JOIN''' релациите, креирањето на дополнителни индекси е непотребно и би довело до залудно трошење на мемориски ресурси. Наместо тоа, за да го решиме тесното грло при '''INSERT''' операциите, се извршува наредбата ANALYZE за табелите во релација со цел да се обноват статистиките на внатрешниот планер.
    112116
    113117{{{
    114118
    115 -- indexes for linking performers with specific event occurrences (M:N relationship)
    116 CREATE INDEX idx_ehp_performer_id ON "Event_Happening_Performer"(performer_id);
    117 CREATE INDEX idx_ehp_happening_id ON "Event_Happening_Performer"(event_happening_id);
    118 
    119 -- index for optimizing event lookups within scheduled event happenings
    120 CREATE INDEX idx_event_happening_event_id ON "Event_Happening"(event_id);
     119ANALYZE;
    121120
    122121}}}
    … …  
    124123==== 6. Резултат по оптимизација:
    125124
    126 По додавањето на индексите, времето на извршување на '''SELECT''' се намали на 0.533 ms.
    127 
    128 Подобрување: Ова е забрзување од над 6000 пати.
    129 
    130 Операциите за '''INSERT''' и '''UPDATE''' сега се извршуваат за помалку од 1ms, што значи дека индексите не го забавуваат системот, туку помагаат дури и кај тригерите за '''Foreign Keys'''.
    131 
    132  * '''SELECT'''
    133 
    134 {{{
    135 
    136 EXPLAIN ANALYZE
    137     SELECT * FROM "Performer_Events" WHERE performer_id = 10;
    138 
    139 }}}
    140 
    141 ||= QUERY PLAN =||
    142 ||Nested Loop  (cost\=1.14..77.38 rows\=7 width\=68) (actual time\=0.251..0.479 rows\=4 loops\=1)||
    143 ||  ->  Nested Loop  (cost\=0.86..74.92 rows\=7 width\=37) (actual time\=0.201..0.306 rows\=4 loops\=1)||
    144 ||        ->  Index Scan using "Performer_pkey" on "Performer" p  (cost\=0.28..8.30 rows\=1 width\=21) (actual time\=0.093..0.094 rows\=1 loops\=1)||
    145 ||              Index Cond: (performer_id \= 10)||
    146 ||        ->  Nested Loop  (cost\=0.57..66.55 rows\=7 width\=24) (actual time\=0.105..0.207 rows\=4 loops\=1)||
    147 ||              ->  Index Only Scan using uq_performer_at_time on "Event_Happening_Performer" ehp  (cost\=0.29..8.41 rows\=7 width\=16) (actual time\=0.062..0.065 rows\=4 loops\=1)||
    148 ||                    Index Cond: (performer_id \= 10)||
    149 ||                    Heap Fetches: 1||
    150 ||              ->  Index Scan using "Event_Happening_pkey" on "Event_Happening" eh  (cost\=0.29..8.30 rows\=1 width\=24) (actual time\=0.032..0.032 rows\=1 loops\=4)||
    151 ||                    Index Cond: (event_happening_id \= ehp.event_happening_id)||
    152 ||  ->  Index Scan using "Event_pkey" on "Event" e  (cost\=0.29..0.35 rows\=1 width\=39) (actual time\=0.041..0.041 rows\=1 loops\=4)||
    153 ||        Index Cond: (event_id \= eh.event_id)||
    154 ||Planning Time: 1.600 ms||
    155 ||Execution Time: 0.533 ms||
    156 
    157  * '''INSERT'''
    158 
    159 {{{
    160 
    161 EXPLAIN ANALYZE
    162     INSERT INTO "Event_Happening_Performer" (event_happening_id, performer_id)
    163     VALUES (2, 10);
    164 
    165 }}}
    166 
    167 ||= QUERY PLAN =||
    168 ||Insert on "Event_Happening_Performer"  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.307..0.308 rows\=0 loops\=1)||
    169 ||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=16) (actual time\=0.001..0.001 rows\=1 loops\=1)||
    170 ||Planning Time: 0.046 ms||
    171 ||Trigger for constraint fk_ehp_event_happening: time\=0.332 calls\=1||
    172 ||Trigger for constraint fk_ehp_performer: time\=0.169 calls\=1||
    173 ||Execution Time: 0.833 ms||
    174 
    175  * '''UPDATE'''
    176 
    177 {{{
    178 
    179 EXPLAIN ANALYZE
    180     UPDATE "Event_Happening_Performer"
    181     SET performer_id = 12
    182     WHERE event_happening_id = 2 AND performer_id = 10;
    183 
    184 }}}
    185 
    186 ||= QUERY PLAN =||
    187 ||Update on "Event_Happening_Performer"  (cost\=0.29..8.31 rows\=0 width\=0) (actual time\=0.437..0.438 rows\=0 loops\=1)||
    188 ||  ->  Index Scan using idx_ehp_happening_id on "Event_Happening_Performer"  (cost\=0.29..8.31 rows\=1 width\=14) (actual time\=0.174..0.175 rows\=1 loops\=1)||
    189 ||        Index Cond: (event_happening_id \= 2)||
    190 ||        Filter: (performer_id \= 10)||
    191 ||        Rows Removed by Filter: 1||
    192 ||Planning Time: 0.161 ms||
    193 ||Trigger for constraint fk_ehp_performer: time\=0.271 calls\=1||
    194 ||Execution Time: 0.763 ms||
     125По извршување на '''ANALYZE''', базата стекна целосен увид во дистрибуцијата на податоците, со што времето на '''INSERT''' се намали на 0.628 ms, што претставува забрзување од околу 30 пати. Операциите за '''SELECT''' и '''UPDATE''' ги задржаа своите врвни перформанси во под-милисекунден опсег.