Changes between Version 2 and Version 3 of QueryOptimization


Ignore:
Timestamp:
05/26/26 22:08:42 (4 hours ago)
Author:
231088
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v2 v3  
    1212
    1313{{{
    14 -- 1.1
    15 SELECT *
    16 FROM vw_available_bookables
    17 WHERE city = 'Skopje'
    18 AND status = 'AVAILABLE';
    19 
    2014-- 1.2
    2115SELECT *
    … …  
    2822FROM vw_available_bookables
    2923WHERE city = 'Ohrid'
    30 AND genre_name = 'Rock'
    31 AND status = 'AVAILABLE';
     24AND genre_name = 'Rock';
    3225}}}
    3326
    3427=== Време на извршување без индекси ===
    35 
    36 '''1.1 - 55.127 ms'''
    37 
    38 {{{
    39 Gather  (cost=1037.34..13879.22 rows=5448 width=481) (actual time=8.888..52.020 rows=71472 loops=1)
    40   Workers Planned: 3
    41   Workers Launched: 3
    42   ->  Hash Join  (cost=37.34..12334.42 rows=1757 width=481) (actual time=2.712..34.856 rows=17868 loops=4)
    43         Hash Cond: (av.bookable_id = b.bookable_id)
    44         ->  Parallel Seq Scan on availabilityslot av
    45               Filter: ((status)::text = 'AVAILABLE'::text)
    46 Planning Time: 0.836 ms
    47 Execution Time: 55.127 ms
    48 }}}
    4928
    5029'''1.2 - 13.717 ms'''
    … …  
    5231{{{
    5332Gather  (cost=1042.46..13517.18 rows=1873 width=481) (actual time=9.626..13.638 rows=0 loops=1)
    54   ->  Hash Join  (cost=42.46..12329.88 rows=604 width=481)
     33  Workers Planned: 3
     34  Workers Launched: 3
     35  ->  Hash Join
    5536        ->  Parallel Seq Scan on availabilityslot av
    5637              Filter: ((status)::text = 'AVAILABLE'::text)
    … …  
    6344{{{
    6445Gather  (cost=1037.17..13320.92 rows=17 width=481) (actual time=9.876..13.817 rows=0 loops=1)
    65   ->  Hash Join  (cost=37.17..12319.22 rows=5 width=481)
     46  Workers Planned: 3
     47  Workers Launched: 3
     48  ->  Hash Join
    6649        ->  Parallel Seq Scan on availabilityslot av
    6750              Filter: ((status)::text = 'AVAILABLE'::text)
    … …  
    7053}}}
    7154
    72 При почетната анализа беше забележано дека PostgreSQL користи Parallel Sequential Scan врз табелата {{{AvailabilitySlot}}}, како и Sequential Scan врз {{{Bookable}}} и {{{Location}}}. Ова значеше дека системот обработува голем број редици за да ги пронајде потребните достапни артисти и бендови.
     55Во почетната анализа со {{{EXPLAIN ANALYZE}}} беше забележано дека PostgreSQL користи {{{Parallel Sequential Scan}}} врз табелата {{{AvailabilitySlot}}}, како и {{{Sequential Scan}}} врз {{{Bookable}}} и {{{Location}}}. Ова значеше дека системот обработува голем број редици за да ги пронајде потребните достапни артисти и бендови.
    7356
    7457За оптимизација беа додадени следните индекси:
    … …  
    9578CREATE INDEX idx_genre_name
    9679ON Genre(genre_name);
     80
     81CREATE INDEX idx_bookable_active
     82ON Bookable(is_active);
    9783}}}
    9884
    9985=== Време на извршување со индекси ===
    100 
    101 '''1.1 - 77.491 ms'''
    102 
    103 {{{
    104 Hash Left Join  (cost=58.20..2541.02 rows=23970 width=481) (actual time=0.959..74.797 rows=71472 loops=1)
    105   ->  Nested Loop
    106         ->  Index Scan using idx_availability_bookable on availabilityslot av
    107 Planning Time: 1.861 ms
    108 Execution Time: 77.491 ms
    109 }}}
    11086
    11187'''1.2 - 0.426 ms'''
    … …  
    134110 * од ~13 ms на ~0.7 ms
    135111
    136 Првиот query и понатаму имаше поголемо време на извршување бидејќи враќа многу голем број резултати (~71,000 редици), но planner-от започна да користи индексно пребарување наместо Parallel Sequential Scan.
    137 
    138 == 2. Анализа и оптимизација на vw_client_booking_history ==
    139 
    140 Погледот {{{vw_client_booking_history}}} се користи за прикажување на историјата на booking-ите на клиентите, нивниот статус и информациите за плаќање.
    141 
    142 Прашалниците кои беа тестирани се следните:
    143 
    144 {{{
    145 -- 2.1
    146 SELECT *
    147 FROM vw_client_booking_history
    148 WHERE client_id = 1500;
    149 
    150 -- 2.2
    151 SELECT *
    152 FROM vw_client_booking_history
    153 WHERE payment_status = 'PAID';
    154 }}}
    155 
    156 === Време на извршување без индекси ===
    157 
    158 '''2.1 - 478.399 ms'''
    159 
    160 {{{
    161 Nested Loop  (cost=394513.00..438192.78 rows=3976 width=58) (actual time=434.407..443.008 rows=0 loops=1)
    162   ->  Gather
    163         ->  Parallel Hash Right Join
    164               ->  Parallel Seq Scan on payment p
    165               ->  Parallel Seq Scan on bookingrequest br
    166 Planning Time: 44.765 ms
    167 Execution Time: 478.399 ms
    168 }}}
    169 
    170 '''2.2 - 159.853 ms'''
    171 
    172 {{{
    173 Nested Loop  (cost=1001.86..42632.01 rows=1 width=58) (actual time=142.971..159.779 rows=0 loops=1)
    174   ->  Gather
    175         ->  Parallel Seq Scan on payment p
    176               Filter: ((payment_status)::text = 'PAID'::text)
    177 Planning Time: 1.571 ms
    178 Execution Time: 159.853 ms
    179 }}}
    180 
    181 При почетната анализа беше забележано дека PostgreSQL користи Parallel Sequential Scan врз табелите {{{BookingRequest}}}, {{{Booking}}} и {{{Payment}}}. Ова предизвикуваше значително време на извршување, особено кај query-ите што пребаруваат според клиент или статус на плаќање.
    182 
    183 За оптимизација беа додадени следните индекси:
    184 
    185 {{{
    186 CREATE INDEX idx_bookingrequest_client
    187 ON BookingRequest(client_id);
    188 
    189 CREATE INDEX idx_offer_request
    190 ON Offer(request_id);
    191 
    192 CREATE INDEX idx_offer_bookable
    193 ON Offer(bookable_id);
    194 
    195 CREATE INDEX idx_booking_offer
    196 ON Booking(offer_id);
    197 
    198 CREATE INDEX idx_booking_status
    199 ON Booking(booking_status);
    200 
    201 CREATE INDEX idx_payment_booking
    202 ON Payment(booking_id);
    203 
    204 CREATE INDEX idx_payment_status
    205 ON Payment(payment_status);
    206 
    207 CREATE INDEX idx_bookable_id
    208 ON Bookable(bookable_id);
    209 }}}
    210 
    211 === Време на извршување со индекси ===
    212 
    213 '''2.1 - 44.997 ms'''
    214 
    215 {{{
    216 Nested Loop  (cost=1115.31..63410.15 rows=3975 width=58) (actual time=38.480..44.907 rows=0 loops=1)
    217   ->  Parallel Bitmap Heap Scan on bookingrequest br
    218         ->  Bitmap Index Scan on idx_bookingrequest_client
    219   ->  Index Scan using idx_offer_request on offer o
    220   ->  Index Scan using idx_booking_offer on booking bk
    221 Planning Time: 2.529 ms
    222 Execution Time: 44.997 ms
    223 }}}
    224 
    225 '''2.2 - 0.114 ms'''
    226 
    227 {{{
    228 Nested Loop  (cost=2.29..14.64 rows=1 width=58) (actual time=0.047..0.049 rows=0 loops=1)
    229   ->  Index Scan using idx_payment_status on payment p
    230 Planning Time: 2.188 ms
    231 Execution Time: 0.114 ms
    232 }}}
    233 
    234 По оптимизацијата PostgreSQL започна да користи:
    235 
    236  * {{{Bitmap Index Scan}}}
    237  * {{{Index Scan}}}
    238  * {{{Index Only Scan}}}
    239 
    240 Најголемо подобрување беше забележано кај query-от што пребарува според {{{payment_status}}}, каде времето на извршување се намали:
    241 
    242  * од ~159 ms
    243  * на ~0.1 ms
    244 
    245 Исто така, query-от што пребарува според {{{client_id}}} се подобри:
    246 
    247  * од ~478 ms
    248  * на ~44 ms
     112Со додавањето на индексите planner-от започна да користи индексно пребарување наместо {{{Parallel Sequential Scan}}}, што овозможи побрзо извршување на query-ите и подобар execution plan.