Changes between Version 14 and Version 15 of QueryOptimization


Ignore:
Timestamp:
05/26/26 23:19:43 (6 hours ago)
Author:
231088
Comment:

--

Legend:

Unmodified
Added
Removed
Modified

  • QueryOptimization

    v14 v15  
    310310
    311311== 4. Анализа и оптимизација на vw_trending_bookables ==
    312 
    313312Погледот {{{vw_trending_bookables}}} се користи за прикажување на најпопуларните артисти и бендови според бројот на bookings и просечниот рејтинг. Овој поглед се користи за homepage recommendations и analytics.
    314313
    315314Прашалникот кој беше тестиран е следниот:
    316 
    317315{{{
    318316SELECT *
    … …  
    323321
    324322=== Време на извршување без индекси ===
    325 
    326 '''4.1 - 22675.664 ms'''
    327 
    328 {{{
    329 Limit  (cost=1149893.25..1149893.28 rows=10 width=61) (actual time=22673.555..22673.564 rows=10 loops=1)
    330   ->  Sort
     323'''4.1 - 23693.914 ms'''
     324{{{
     325Limit  (cost=1149893.25..1149893.28 rows=10 width=61) (actual time=23691.798..23691.806 rows=10 loops=1)
     326  ->  Sort  (cost=1149893.25..1149894.75 rows=600 width=61) (actual time=23476.040..23476.046 rows=10 loops=1)
    331327        Sort Key: (count(bk.booking_id)) DESC
    332         ->  HashAggregate
     328        Sort Method: top-N heapsort  Memory: 25kB
     329        ->  HashAggregate  (cost=1149871.28..1149880.28 rows=600 width=61) (actual time=23475.685..23475.836 rows=600 loops=1)
    333330              Group Key: b.bookable_id
    334               ->  Hash Right Join
    335                     ->  Hash Left Join
    336                           ->  Seq Scan on offer o
    337                           ->  Seq Scan on booking bk
    338                           ->  Seq Scan on review r
    339 Planning Time: 1.190 ms
    340 Execution Time: 22675.664 ms
     331              Batches: 1  Memory Usage: 169kB
     332              ->  Hash Right Join  (cost=251298.36..999871.28 rows=20000000 width=29) (actual time=4249.553..20639.674 rows=20000595 loops=1)
     333                    Hash Cond: (o.bookable_id = b.bookable_id)
     334                    ->  Hash Left Join  (cost=251269.86..946971.95 rows=20000000 width=12) (actual time=4249.291..17174.982 rows=20000000 loops=1)
     335                          Hash Cond: (o.offer_id = bk.offer_id)
     336                          ->  Seq Scan on offer o  (cost=0.00..329899.00 rows=20000000 width=8) (actual time=0.037..2604.142 rows=20000000 loops=1)
     337                          ->  Hash  (cost=181713.23..181713.23 rows=4001410 width=12) (actual time=4247.656..4247.659 rows=4001410 loops=1)
     338                                ->  Hash Left Join  (cost=40503.35..181713.23 rows=4001410 width=12) (actual time=401.092..2863.158 rows=4001410 loops=1)
     339                                      Hash Cond: (bk.booking_id = r.booking_id)
     340                                      ->  Seq Scan on booking bk  (cost=0.00..78258.10 rows=4001410 width=8) (actual time=0.043..582.369 rows=4001410 loops=1)
     341                                      ->  Hash  (cost=20819.49..20819.49 rows=1199749 width=8) (actual time=397.674..397.675 rows=1199749 loops=1)
     342                                            ->  Seq Scan on review r  (cost=0.00..20819.49 rows=1199749 width=8) (actual time=0.022..133.592 rows=1199749 loops=1)
     343                    ->  Hash  (cost=21.00..21.00 rows=600 width=21) (actual time=0.235..0.236 rows=600 loops=1)
     344                          ->  Seq Scan on bookable b  (cost=0.00..21.00 rows=600 width=21) (actual time=0.029..0.119 rows=600 loops=1)
     345Planning Time: 84.544 ms
     346Execution Time: 23693.914 ms
    341347}}}
    342348
    … …  
    346352
    347353За оптимизација беа додадени следните индекси:
    348 
    349354{{{
    350355CREATE INDEX idx_offer_bookable
    … …  
    365370
    366371=== Време на извршување со индекси ===
    367 
    368 '''4.1 - 22582.888 ms'''
    369 
    370 {{{
    371 Limit  (cost=1149893.25..1149893.28 rows=10 width=61) (actual time=22580.768..22580.777 rows=10 loops=1)
    372   ->  Sort
     372'''4.1 - 26028.630 ms'''
     373{{{
     374Limit  (cost=1149893.25..1149893.28 rows=10 width=61) (actual time=26026.371..26026.380 rows=10 loops=1)
     375  ->  Sort  (cost=1149893.25..1149894.75 rows=600 width=61) (actual time=25818.135..25818.141 rows=10 loops=1)
    373376        Sort Key: (count(bk.booking_id)) DESC
    374         ->  HashAggregate
     377        Sort Method: top-N heapsort  Memory: 25kB
     378        ->  HashAggregate  (cost=1149871.28..1149880.28 rows=600 width=61) (actual time=25817.826..25817.963 rows=600 loops=1)
    375379              Group Key: b.bookable_id
    376               ->  Hash Right Join
    377                     ->  Hash Left Join
    378                           ->  Seq Scan on offer o
    379                           ->  Seq Scan on booking bk
    380                           ->  Seq Scan on review r
    381 Planning Time: 1.633 ms
    382 Execution Time: 22582.888 ms
     380              Batches: 1  Memory Usage: 169kB
     381              ->  Hash Right Join  (cost=251298.36..999871.28 rows=20000000 width=29) (actual time=3790.821..22958.347 rows=20000595 loops=1)
     382                    Hash Cond: (o.bookable_id = b.bookable_id)
     383                    ->  Hash Left Join  (cost=251269.86..946971.95 rows=20000000 width=12) (actual time=3790.550..19482.658 rows=20000000 loops=1)
     384                          Hash Cond: (o.offer_id = bk.offer_id)
     385                          ->  Seq Scan on offer o  (cost=0.00..329899.00 rows=20000000 width=8) (actual time=0.048..2551.431 rows=20000000 loops=1)
     386                          ->  Hash  (cost=181713.23..181713.23 rows=4001410 width=12) (actual time=3789.013..3789.016 rows=4001410 loops=1)
     387                                ->  Hash Left Join  (cost=40503.35..181713.23 rows=4001410 width=12) (actual time=393.530..2817.254 rows=4001410 loops=1)
     388                                      Hash Cond: (bk.booking_id = r.booking_id)
     389                                      ->  Seq Scan on booking bk  (cost=0.00..78258.10 rows=4001410 width=8) (actual time=0.038..571.295 rows=4001410 loops=1)
     390                                      ->  Hash  (cost=20819.49..20819.49 rows=1199749 width=8) (actual time=390.800..390.801 rows=1199749 loops=1)
     391                                            ->  Seq Scan on review r  (cost=0.00..20819.49 rows=1199749 width=8) (actual time=0.021..128.328 rows=1199749 loops=1)
     392                    ->  Hash  (cost=21.00..21.00 rows=600 width=21) (actual time=0.244..0.245 rows=600 loops=1)
     393                          ->  Seq Scan on bookable b  (cost=0.00..21.00 rows=600 width=21) (actual time=0.035..0.128 rows=600 loops=1)
     394Planning Time: 1.646 ms
     395Execution Time: 26028.630 ms
    383396}}}
    384397
    385398По оптимизацијата беше забележано мало подобрување кај query-от што ги прикажува најпопуларните артисти:
    386 
    387  * од ~22.6 s
    388  * на ~22.5 s
     399 * од ~23.7 s
     400 * на ~26.0 s
    389401
    390402И покрај додадените индекси, PostgreSQL продолжи да користи {{{Sequential Scan}}} бидејќи query-от обработува огромен број редици и користи сложени aggregation операции како {{{COUNT}}}, {{{AVG}}}, {{{GROUP BY}}} и {{{ORDER BY}}}.
    391403
    392 Во вакви ситуации planner-от проценува дека sequential processing е поефикасен од index traversal, па индексите имаат ограничено влијание врз вкупното време на извршување.
     404Во вакви ситуации planner-от проценува дека sequential processing е поефикасен од index traversal, па индексите имаат ограничено влијание врз вкупното време на извршување. Поради ова, беше одлучено да се примени дополнителна оптимизација преку Материјализиран Поглед.
     405
     406=== Оптимизација со Материјализиран Поглед ===
     407Бидејќи индексите не можеа значително да го подобрат времето на извршување поради природата на query-от (full-table aggregation со {{{GROUP BY}}} и {{{ORDER BY}}}), беше креиран Материјализиран Поглед ({{{MATERIALIZED VIEW}}}) кој ја зачувува пресметаната агрегација и овозможува директно пребарување врз резултатот.
     408
     409За разлика од обичниот поглед кој го извршува query-от секој пат, Материјализираниот Поглед ги зачувува резултатите физички на диск, со што сортирањето и броењето работат врз само 600 редици наместо врз 20 милиони.
     410{{{
     411CREATE MATERIALIZED VIEW mv_trending_bookables AS
     412SELECT
     413    b.bookable_id,
     414    b.display_name,
     415    b.bookable_type,
     416    COUNT(bk.booking_id)    AS total_bookings,
     417    ROUND(AVG(r.rating), 2) AS average_rating
     418FROM Bookable b
     419LEFT JOIN Offer o    ON b.bookable_id = o.bookable_id
     420LEFT JOIN Booking bk ON bk.offer_id = o.offer_id
     421LEFT JOIN Review r   ON r.booking_id = bk.booking_id
     422GROUP BY b.bookable_id, b.display_name, b.bookable_type;
     423
     424CREATE UNIQUE INDEX idx_mv_trending_bookable_id
     425ON mv_trending_bookables(bookable_id);
     426
     427CREATE INDEX idx_mv_trending_total_bookings
     428ON mv_trending_bookables(total_bookings DESC);
     429
     430CREATE INDEX idx_mv_trending_avg_rating
     431ON mv_trending_bookables(average_rating DESC);
     432}}}
     433
     434=== Време на извршување со Материјализиран Поглед ===
     435'''4.1 - 0.035 ms'''
     436{{{
     437Limit  (cost=0.15..0.97 rows=10 width=420) (actual time=0.012..0.017 rows=10 loops=1)
     438  ->  Index Scan using idx_mv_trending_total_bookings on mv_trending_bookables  (cost=0.15..49.15 rows=600 width=420) (actual time=0.011..0.015 rows=10 loops=1)
     439Planning Time: 0.339 ms
     440Execution Time: 0.035 ms
     441}}}
     442
     443По креирањето на Материјализираниот Поглед беше забележано драстично подобрување:
     444 * од ~26.0 s (со индекси)
     445 * на ~0.035 ms (со Материјализиран Поглед)
     446
     447Наместо да ги скенира 20 милиони редици од табелите {{{Offer}}}, {{{Booking}}} и {{{Review}}}, PostgreSQL сега користи {{{Index Scan}}} директно врз зачуваниот резултат од само 600 редици. Ова претставува подобрување од околу '''742,000 пати''' во споредба со оригиналниот query со индекси.
     448
     449За да останат податоците ажурирани, Материјализираниот Поглед се освежува по секоја промена на релевантните табели:
     450{{{
     451REFRESH MATERIALIZED VIEW CONCURRENTLY mv_trending_bookables;
     452}}}
    393453
    394454