Changes between Version 75 and Version 76 of QueryOptimization


Ignore:
Timestamp:
06/30/26 00:01:43 (6 days ago)
Author:
231027
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v75 v76  
    117117{{{
    118118
    119 ANALYZE;
     119ANALYZE "Venue";
     120ANALYZE "Section";
     121ANALYZE "Seat";
    120122
    121123}}}
    … …  
    124126
    125127По извршување на '''ANALYZE''', базата стекна целосен увид во дистрибуцијата на податоците, со што времето на '''INSERT''' се намали на 0.628 ms, што претставува забрзување од околу 30 пати. Операциите за '''SELECT''' и '''UPDATE''' ги задржаа своите врвни перформанси во под-милисекунден опсег.
     128
     129
     130
     131
     132
     133
     134
     135== Анализа и оптимизација на `User_Tickets`
     136
     137Овој поглед дава детален хронолошки преглед на сите купени поединечни билети по корисник, вклучувајќи ја точната платена цена, QR-кодот за влез и терминот на настанот. Преку релацијата со ставките за рефундација, погледот нуди и инстантна информација за тоа кои карти се откажани и кога се вратени парите.
     138
     139{{{
     140
     141CREATE OR REPLACE VIEW "User_Tickets" AS
     142SELECT u.user_id,
     143       u.username,
     144       toi.order_item_id,
     145       t.ticket_id,
     146       e.event_id,
     147       e.name AS event_name,
     148       eh.event_time,
     149       toi.qr_code,
     150       toi.item_price AS price_paid,
     151       tri.refund_item_id,
     152       tr.refund_time
     153FROM "User" u
     154JOIN "Regular_User" ru ON u.user_id = ru.user_id
     155JOIN "Ticket_Order" o ON ru.user_id = o.user_id
     156JOIN "Ticket_Order_Item" toi ON o.order_id = toi.order_id
     157JOIN "Ticket" t ON toi.ticket_id = t.ticket_id
     158JOIN "Event_Happening" eh ON t.event_happening_id = eh.event_happening_id
     159JOIN "Event" e ON eh.event_id = e.event_id
     160LEFT JOIN "Ticket_Refund_Item" tri ON toi.order_item_id = tri.order_item_id
     161LEFT JOIN "Ticket_Refund" tr ON tri.refund_id = tr.refund_id;
     162
     163}}}
     164
     165==== 1. Примарен филтер:
     166
     167Примарен филтер за овој поглед е `user_id` (ID на корисникот), бидејќи најчестото и критично пребарување е кога најавениот клиент ја отвора својата корисничка профилна страница за да ги види своите активни или минати билети.
     168
     169==== 2. Случај на употреба:
     170
     171Погледот претставува јадро на апликацијата во делот за дигитални билети (My Tickets). Секојпат кога корисникот сака да го прикаже својот QR-код на билетот за некој настан, системот го извршува овој прашалник. Доцнење на овој одзив предизвикува лошо корисничко искуство и застој на влезните капии.
     172
     173==== 3. Иницијално време:
     174
     175 * '''SELECT:''' 676.751 ms (Исклучително бавно поради комплексноста од 9 поврзани табели и секвенцијално скенирање на огромната табела за билети).
     176 * '''INSERT:''' 18.187 ms (Бавно извршување поради диск операции при евалуација на констреинтите).
     177 * '''UPDATE:''' 0.071 ms (Инстантна брзина благодарение на примарниот клуч).
     178
     179==== 4. Анализа на планот на извршување (без индекси):
     180
     181При селекција без индекси, базата е принудена да користи '''Gather''' операција со два дополнителни паралелни работници ('''Workers Planned: 2'''). Најголемиот проблем се јавува во тоа што се врши секвенцијално скенирање со проверка на релациите низ хард дискот, што генерира огромни 38,603 диск читања ('''shared read=38603'''). Кај '''INSERT''' операцијата, дури 17.637 ms се губат во тригерите за проверка на '''foreign key''' констреинтите бидејќи базата нема брза индексна патека до поврзаните записи.
     182
     183 * '''SELECT'''
     184
     185{{{
     186
     187EXPLAIN ANALYZE
     188SELECT * FROM "User_Tickets"
     189WHERE user_id = 5;
     190
     191}}}
     192
     193||= QUERY PLAN =||
     194||Gather  (cost\=66949.49..416072.10 rows\=4 width\=145) (actual time\=667.628..676.676 rows\=0.00 loops\=1)||
     195||  Workers Planned: 2||
     196||  Workers Launched: 2||
     197||  Buffers: shared read\=38603||
     198||  ->  Nested Loop Left Join  (cost\=65949.49..415071.70 rows\=2 width\=145) (actual time\=612.744..612.748 rows\=0.00 loops\=3)||
     199||        Buffers: shared read\=38603||
     200||        ->  Nested Loop Left Join  (cost\=65949.07..415070.67 rows\=2 width\=145) (actual time\=612.743..612.747 rows\=0.00 loops\=3)||
     201||              Buffers: shared read\=38603||
     202||              ->  Nested Loop  (cost\=65948.64..415069.63 rows\=2 width\=129) (actual time\=612.742..612.746 rows\=0.00 loops\=3)||
     203||                    Buffers: shared read\=38603||
     204||                    ->  Nested Loop  (cost\=65948.35..415068.90 rows\=2 width\=98) (actual time\=612.742..612.745 rows\=0.00 loops\=3)||
     205||                          Buffers: shared read\=38603||
     206||                          ->  Nested Loop  (cost\=65948.07..415068.27 rows\=2 width\=90) (actual time\=612.741..612.745 rows\=0.00 loops\=3)||
     207||                                Buffers: shared read\=38603||
     208||                                ->  Nested Loop  (cost\=65947.63..415065.48 rows\=2 width\=82) (actual time\=612.741..612.744 rows\=0.00 loops\=3)||
     209||                                      Buffers: shared read\=38603||
     210||                                      ->  Parallel Hash Join  (cost\=65946.77..415052.55 rows\=2 width\=62) (actual time\=612.740..612.743 rows\=0.00 loops\=3)||
     211||                                            Hash Cond: (toi.order_id \= o.order_id)||
     212||                                            Buffers: shared read\=38603||
     213||                                            ->  Parallel Seq Scan on ""Ticket_Order_Item"" toi  (cost\=0.00..326137.01 rows\=8750001 width\=62) (never executed)||
     214||                                            ->  Parallel Hash  (cost\=65946.76..65946.76 rows\=1 width\=16) (actual time\=612.667..612.667 rows\=0.00 loops\=3)||
     215||                                                  Buckets: 1024  Batches: 1  Memory Usage: 0kB||
     216||                                                  Buffers: shared read\=38603||
     217||                                                  ->  Parallel Seq Scan on ""Ticket_Order"" o  (cost\=0.00..65946.76 rows\=1 width\=16) (actual time\=612.428..612.428 rows\=0.00 loops\=3)||
     218||                                                        Filter: (user_id \= 5)||
     219||                                                        Rows Removed by Filter: 1750000||
     220||                                                        Buffers: shared read\=38603||
     221||                                      ->  Materialize  (cost\=0.86..12.91 rows\=1 width\=36) (never executed)||
     222||                                            ->  Nested Loop  (cost\=0.86..12.90 rows\=1 width\=36) (never executed)||
     223||                                                  ->  Index Scan using ""User_pkey"" on ""User"" u  (cost\=0.43..8.45 rows\=1 width\=28) (never executed)||
     224||                                                        Index Cond: (user_id \= 5)||
     225||                                                        Index Searches: 0||
     226||                                                  ->  Index Only Scan using ""Regular_User_pkey"" on ""Regular_User"" ru  (cost\=0.43..4.45 rows\=1 width\=8) (never executed)||
     227||                                                        Index Cond: (user_id \= 5)||
     228||                                                        Heap Fetches: 0||
     229||                                                        Index Searches: 0||
     230||                                ->  Index Scan using ""Ticket_pkey"" on ""Ticket"" t  (cost\=0.44..1.39 rows\=1 width\=16) (never executed)||
     231||                                      Index Cond: (ticket_id \= toi.ticket_id)||
     232||                                      Index Searches: 0||
     233||                          ->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening"" eh  (cost\=0.29..0.31 rows\=1 width\=24) (never executed)||
     234||                                Index Cond: (event_happening_id \= t.event_happening_id)||
     235||                                Index Searches: 0||
     236||                    ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..0.37 rows\=1 width\=39) (never executed)||
     237||                          Index Cond: (event_id \= eh.event_id)||
     238||                          Index Searches: 0||
     239||              ->  Index Scan using ""Ticket_Refund_Item_order_item_id_key"" on ""Ticket_Refund_Item"" tri  (cost\=0.43..0.52 rows\=1 width\=24) (never executed)||
     240||                    Index Cond: (order_item_id \= toi.order_item_id)||
     241||                    Index Searches: 0||
     242||        ->  Index Scan using ""Ticket_Refund_pkey"" on ""Ticket_Refund"" tr  (cost\=0.42..0.52 rows\=1 width\=16) (never executed)||
     243||              Index Cond: (refund_id \= tri.refund_id)||
     244||              Index Searches: 0||
     245||Planning:||
     246||  Buffers: shared hit\=38 read\=57 dirtied\=3||
     247||Planning Time: 29.035 ms||
     248||Execution Time: 676.751 ms||
     249
     250 * '''INSERT'''
     251
     252{{{
     253
     254EXPLAIN ANALYZE
     255INSERT INTO "Ticket_Order_Item" (order_item_id, order_id, ticket_id, item_price, qr_code)
     256VALUES (99999999, 1, 1, 1200.00, '3ebd5fa12ea8781d1e9ae4333484984a');
     257
     258}}}
     259
     260||= QUERY PLAN =||
     261||Insert on ""Ticket_Order_Item""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=17.637..17.638 rows\=0.00 loops\=1)||
     262||  Buffers: shared hit\=4 read\=3 dirtied\=1||
     263||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=556) (actual time\=0.001..0.002 rows\=1.00 loops\=1)||
     264||Planning Time: 0.029 ms||
     265||Trigger for constraint fk_item_order: time\=0.342 calls\=1||
     266||Trigger for constraint fk_item_ticket: time\=0.191 calls\=1||
     267||Execution Time: 18.187 ms||
     268
     269 * '''UPDATE'''
     270
     271{{{
     272
     273EXPLAIN ANALYZE
     274UPDATE "Ticket_Order_Item"
     275SET qr_code = '3ebd5fa12ec8781d1e9ae4333484984a'
     276WHERE order_item_id = 99999999;
     277
     278}}}
     279
     280||= QUERY PLAN =||
     281||Update on ""Ticket_Order_Item""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.051..0.052 rows\=0.00 loops\=1)||
     282||  Buffers: shared hit\=7||
     283||  ->  Index Scan using ""Ticket_Order_Item_pkey"" on ""Ticket_Order_Item""  (cost\=0.44..8.46 rows\=1 width\=522) (actual time\=0.023..0.024 rows\=1.00 loops\=1)||
     284||        Index Cond: (order_item_id \= 99999999)||
     285||        Index Searches: 1||
     286||        Buffers: shared hit\=4||
     287||Planning Time: 0.137 ms||
     288||Execution Time: 0.071 ms||
     289
     290==== 5. Оптимизација и индексирање:
     291
     292За драстично кратење на времето, воведуваме '''B-tree''' индекси врз надворешните клучеви кои ја контролираат хиерархијата на релациите од корисникот па се до ставката на нарачката. Дополнително, се извршува '''ANALYZE''' за стабилизација на статистиките.
     293
     294{{{
     295
     296CREATE INDEX CONCURRENTLY idx_ticket_order_user_id
     297ON "Ticket_Order" (user_id);
     298
     299CREATE INDEX CONCURRENTLY idx_toi_order_id
     300ON "Ticket_Order_Item" (order_id);
     301
     302CREATE INDEX CONCURRENTLY idx_toi_ticket_id
     303ON "Ticket_Order_Item" (ticket_id);
     304
     305
     306ANALYZE "Ticket_Order";
     307ANALYZE "Ticket_Order_Item";
     308
     309}}}
     310
     311==== 6. Резултат по оптимизација:
     312
     313По воведувањето на индексите и ажурирањето на статистиката, добиен е најголемиот перформансен бенефит во системот:
     314
     315 * Времето за '''SELECT''' падна на неверојатни 0.073 ms, што претставува забрзување од над 9.000 пати. Читањето од диск е целосно елиминирано ('''shared read=0'''), а базата сега извршува директен и молскавично брз '''Index Scan''' во RAM меморијата користејќи само 11 мемориски буфери.
     316 * Времето за '''INSERT''' падна на 0.655 ms (околу 27 пати побрзо), со оглед на тоа што проверката на '''foreign key''' констреинтите сега веднаш се резолвира преку новите индексни структури.
     317 * Операцијата '''UPDATE''' ги задржа своите стабилни и конзистентни под-милисекундни перформанси (0.135 ms).
     318
     319 * '''SELECT'''
     320
     321{{{
     322
     323EXPLAIN ANALYZE
     324SELECT * FROM "User_Tickets"
     325WHERE user_id = 99;
     326
     327}}}
     328
     329||= QUERY PLAN =||
     330||Nested Loop Left Join  (cost\=3.59..42.94 rows\=4 width\=145) (actual time\=0.070..0.073 rows\=0.00 loops\=1)||
     331||  Buffers: shared hit\=11||
     332||  ->  Nested Loop Left Join  (cost\=3.17..40.87 rows\=4 width\=145) (actual time\=0.070..0.072 rows\=0.00 loops\=1)||
     333||        Buffers: shared hit\=11||
     334||        ->  Nested Loop  (cost\=2.74..38.81 rows\=4 width\=129) (actual time\=0.070..0.071 rows\=0.00 loops\=1)||
     335||              Buffers: shared hit\=11||
     336||              ->  Nested Loop  (cost\=2.46..37.34 rows\=4 width\=98) (actual time\=0.069..0.071 rows\=0.00 loops\=1)||
     337||                    Buffers: shared hit\=11||
     338||                    ->  Nested Loop  (cost\=2.17..36.09 rows\=4 width\=90) (actual time\=0.069..0.071 rows\=0.00 loops\=1)||
     339||                          Buffers: shared hit\=11||
     340||                          ->  Nested Loop  (cost\=1.73..30.51 rows\=4 width\=82) (actual time\=0.069..0.070 rows\=0.00 loops\=1)||
     341||                                Buffers: shared hit\=11||
     342||                                ->  Nested Loop  (cost\=1.29..21.36 rows\=1 width\=36) (actual time\=0.069..0.069 rows\=0.00 loops\=1)||
     343||                                      Buffers: shared hit\=11||
     344||                                      ->  Nested Loop  (cost\=0.86..12.90 rows\=1 width\=36) (actual time\=0.039..0.041 rows\=1.00 loops\=1)||
     345||                                            Buffers: shared hit\=8||
     346||                                            ->  Index Scan using ""User_pkey"" on ""User"" u  (cost\=0.43..8.45 rows\=1 width\=28) (actual time\=0.017..0.018 rows\=1.00 loops\=1)||
     347||                                                  Index Cond: (user_id \= 99)||
     348||                                                  Index Searches: 1||
     349||                                                  Buffers: shared hit\=4||
     350||                                            ->  Index Only Scan using ""Regular_User_pkey"" on ""Regular_User"" ru  (cost\=0.43..4.45 rows\=1 width\=8) (actual time\=0.018..0.019 rows\=1.00 loops\=1)||
     351||                                                  Index Cond: (user_id \= 99)||
     352||                                                  Heap Fetches: 0||
     353||                                                  Index Searches: 1||
     354||                                                  Buffers: shared hit\=4||
     355||                                      ->  Index Scan using idx_ticket_order_user_id on ""Ticket_Order"" o  (cost\=0.43..8.44 rows\=1 width\=16) (actual time\=0.027..0.027 rows\=0.00 loops\=1)||
     356||                                            Index Cond: (user_id \= 99)||
     357||                                            Index Searches: 1||
     358||                                            Buffers: shared hit\=3||
     359||                                ->  Index Scan using idx_toi_order_id on ""Ticket_Order_Item"" toi  (cost\=0.44..8.89 rows\=26 width\=62) (never executed)||
     360||                                      Index Cond: (order_id \= o.order_id)||
     361||                                      Index Searches: 0||
     362||                          ->  Index Scan using ""Ticket_pkey"" on ""Ticket"" t  (cost\=0.44..1.39 rows\=1 width\=16) (never executed)||
     363||                                Index Cond: (ticket_id \= toi.ticket_id)||
     364||                                Index Searches: 0||
     365||                    ->  Index Scan using ""Event_Happening_pkey"" on ""Event_Happening"" eh  (cost\=0.29..0.31 rows\=1 width\=24) (never executed)||
     366||                          Index Cond: (event_happening_id \= t.event_happening_id)||
     367||                          Index Searches: 0||
     368||              ->  Index Scan using ""Event_pkey"" on ""Event"" e  (cost\=0.29..0.37 rows\=1 width\=39) (never executed)||
     369||                    Index Cond: (event_id \= eh.event_id)||
     370||                    Index Searches: 0||
     371||        ->  Index Scan using ""Ticket_Refund_Item_order_item_id_key"" on ""Ticket_Refund_Item"" tri  (cost\=0.43..0.52 rows\=1 width\=24) (never executed)||
     372||              Index Cond: (order_item_id \= toi.order_item_id)||
     373||              Index Searches: 0||
     374||  ->  Index Scan using ""Ticket_Refund_pkey"" on ""Ticket_Refund"" tr  (cost\=0.42..0.52 rows\=1 width\=16) (never executed)||
     375||        Index Cond: (refund_id \= tri.refund_id)||
     376||        Index Searches: 0||
     377||Planning:||
     378||  Buffers: shared hit\=295 read\=8||
     379||Planning Time: 74.096 ms||
     380||Execution Time: 0.196 ms||
     381
     382 * '''INSERT'''
     383
     384{{{
     385
     386EXPLAIN ANALYZE
     387INSERT INTO "Ticket_Order_Item" (order_item_id, order_id, ticket_id, item_price, qr_code)
     388VALUES (77777777, 1, 1, 1200.00, '3ebc5fd22ec8681d1e9ae4333484984a');
     389
     390}}}
     391
     392||= QUERY PLAN =||
     393||Insert on ""Ticket_Order_Item""  (cost\=0.00..0.01 rows\=0 width\=0) (actual time\=0.307..0.307 rows\=0.00 loops\=1)||
     394||  Buffers: shared hit\=12 dirtied\=3||
     395||  ->  Result  (cost\=0.00..0.01 rows\=1 width\=556) (actual time\=0.001..0.001 rows\=1.00 loops\=1)||
     396||Planning Time: 0.032 ms||
     397||Trigger for constraint fk_item_order: time\=0.200 calls\=1||
     398||Trigger for constraint fk_item_ticket: time\=0.132 calls\=1||
     399||Execution Time: 0.655 ms||
     400
     401 * '''UPDATE'''
     402
     403{{{
     404
     405EXPLAIN ANALYZE
     406UPDATE "Ticket_Order_Item"
     407SET qr_code = '3ebc5fd20ec8681d1e9ee5733484984a'
     408WHERE order_item_id = 77777777;
     409
     410}}}
     411
     412||= QUERY PLAN =||
     413||Update on ""Ticket_Order_Item""  (cost\=0.44..8.46 rows\=0 width\=0) (actual time\=0.091..0.091 rows\=0.00 loops\=1)||
     414||  Buffers: shared hit\=7||
     415||  ->  Index Scan using ""Ticket_Order_Item_pkey"" on ""Ticket_Order_Item""  (cost\=0.44..8.46 rows\=1 width\=522) (actual time\=0.022..0.023 rows\=1.00 loops\=1)||
     416||        Index Cond: (order_item_id \= 77777777)||
     417||        Index Searches: 1||
     418||        Buffers: shared hit\=4||
     419||Planning Time: 0.120 ms||
     420||Execution Time: 0.135 ms||