Changes between Version 13 and Version 14 of QueryOptimization

Timestamp:

05/26/26 23:11:22 (6 hours ago)

Author:

231088

Comment:

--

QueryOptimization

@@ -395 +395 @@
 
 == 5. Анализа и оптимизација на vw_artist_dashboard ==
-
 Погледот {{{vw_artist_dashboard}}} се користи за прикажување на статистики за артистите и бендовите, вклучувајќи број на bookings, вкупна заработка и просечен рејтинг. Овој поглед се користи во artist dashboard делот од апликацијата.
 
 Прашалникот кој беше тестиран е следниот:
-
 {{{
 SELECT *
@@ -407 +405 @@
 
 === Време на извршување без индекси ===
-
-'''5.1 - 32835.819 ms'''
-
+'''5.1 - 66721.664 ms'''
 {{{
 HashAggregate  (cost=1485307.72..1485320.22 rows=200 width=87) (actual time=66672.114..66672.131 rows=0 loops=1)
-Group Key: b.bookable_id
+  Group Key: b.bookable_id
   Filter: (round(avg(r.rating), 2) >= 4.5)
   ->  Hash Right Join
@@ -429 +425 @@
 
 За оптимизација беа додадени следните индекси:
-
 {{{
 CREATE INDEX idx_offer_bookable
@@ -451 +446 @@
 
 === Време на извршување со индекси ===
-
-'''5.1 - 30002.268 ms'''
-
+'''5.1 - 31275.268 ms'''
 {{{
 HashAggregate  (cost=1485307.72..1485320.22 rows=200 width=87) (actual time=29999.572..29999.580 rows=0 loops=1)
@@ -465 +458 @@
               ->  Seq Scan on review r
 Planning Time: 1.395 ms
-Execution Time: 30002.268 ms
+Execution Time: 31275.268 ms
 }}}
 
 По оптимизацијата беше забележано подобрување кај query-от што филтрира според просечен рејтинг:
-
- * од ~32.8 s
- * на ~30.0 s
+ * од ~66.7 s
+ * на ~31.3 s
 
 И покрај тоа што PostgreSQL продолжи да користи {{{Sequential Scan}}}, индексите помогнаа при join операциите и hash processing, што резултираше со побрзо извршување на query-от.
 
-Бидејќи погледот користи сложени aggregation операции како {{{SUM}}}, {{{AVG}}}, {{{COUNT}}} и {{{GROUP BY}}}, planner-от проценува дека sequential processing е поефикасен од index traversal при обработка на голем број редици.
+Бидејќи погледот користи сложени aggregation операции како {{{SUM}}}, {{{AVG}}}, {{{COUNT}}} и {{{GROUP BY}}}, planner-от проценува дека sequential processing е поефикасен од index traversal при обработка на голем број редици. Поради ова, беше одлучено да се примени дополнителна оптимизација преку Materijализиран Поглед.
+
+=== Оптимизација со Материјализиран Поглед ===
+Бидејќи индексите не можеа значително да го подобрат времето на извршување поради природата на query-от (full-table aggregation со {{{GROUP BY}}}), беше креиран Материјализиран Поглед ({{{MATERIALIZED VIEW}}}) кој ја зачувува пресметаната агрегација и овозможува директно пребарување врз резултатот.
+
+За разлика од обичниот поглед кој го извршува query-от секој пат, Материјализираниот Поглед ги зачувува резултатите физички на диск, со што филтерот {{{WHERE average_rating >= 4.5}}} работи врз само 600 редици наместо врз милиони.
+{{{
+CREATE MATERIALIZED VIEW mv_artist_dashboard AS
+SELECT
+    b.bookable_id,
+    b.display_name,
+    COUNT(bk.booking_id) AS total_bookings,
+    SUM(
+        CASE
+            WHEN p.payment_status = 'PAID' THEN p.amount
+            ELSE 0
+        END
+    ) AS total_earnings,
+    ROUND(AVG(r.rating), 2) AS average_rating
+FROM Bookable b
+LEFT JOIN Offer o    ON b.bookable_id = o.bookable_id
+LEFT JOIN Booking bk ON bk.offer_id = o.offer_id
+LEFT JOIN Payment p  ON p.booking_id = bk.booking_id
+LEFT JOIN Review r   ON r.booking_id = bk.booking_id
+GROUP BY b.bookable_id, b.display_name;
+
+CREATE UNIQUE INDEX idx_mv_dashboard_bookable_id
+ON mv_artist_dashboard(bookable_id);
+
+CREATE INDEX idx_mv_dashboard_rating
+ON mv_artist_dashboard(average_rating);
+}}}
+
+=== Време на извршување со Материјализиран Поглед ===
+'''5.1 - 0.111 ms'''
+{{{
+Seq Scan on mv_artist_dashboard  (cost=0.00..15.50 rows=200 width=394) (actual time=0.090..0.090 rows=0 loops=1)
+  Filter: (average_rating >= 4.5)
+  Rows Removed by Filter: 600
+Planning Time: 0.321 ms
+Execution Time: 0.111 ms
+}}}
+
+По креирањето на Материјализираниот Поглед беше забележано драстично подобрување:
+ * од ~31.3 s (со индекси)
+ * на ~0.1 ms (со Материјализиран Поглед)
+
+Наместо да ги скенира милионите редици од табелите {{{Offer}}}, {{{Booking}}}, {{{Payment}}} и {{{Review}}}, PostgreSQL сега скенира само 600 редици од зачуваниот резултат. Ова претставува подобрување од околу '''281,000 пати''' во споредба со оригиналниот query со индекси.
+
+За да останат податоците ажурирани, Материјализираниот Поглед се освежува по секоја промена на релевантните табели:
+{{{
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_artist_dashboard;
+}}}
+