Changes between Version 15 and Version 16 of QueryOptimization

Timestamp:

05/26/26 23:26:53 (15 hours ago)

Author:

231088

Comment:

--

QueryOptimization

@@ -228 +228 @@
 
 == 3. Анализа и оптимизација на vw_public_artist_profiles ==
-
 Погледот {{{vw_public_artist_profiles}}} се користи за прикажување на јавните профили на артистите и бендовите, нивниот град, жанр, просечен рејтинг и бројот на reviews.
 
 Прашалникот кој беше тестиран е следниот:
-
 {{{
 SELECT *
@@ -240 +238 @@
 
 === Време на извршување без индекси ===
-
 '''3.1 - 15423.388 ms'''
-
 {{{
 HashAggregate  (cost=761290.87..761380.87 rows=6000 width=497) (actual time=15419.314..15419.430 rows=213 loops=1)
@@ -261 +257 @@
 
 За оптимизација беа додадени следните индекси:
-
 {{{
 CREATE INDEX idx_bookable_location
@@ -283 +278 @@
 
 === Време на извршување со индекси ===
-
 '''3.1 - 15251.957 ms'''
-
 {{{
 HashAggregate  (cost=761290.87..761380.87 rows=6000 width=497) (actual time=15247.945..15248.063 rows=213 loops=1)
@@ -300 +293 @@
 
 По оптимизацијата беше забележано умерено подобрување:
-
  * од ~15.4 s
  * на ~15.2 s
@@ -306 +298 @@
 И покрај додадените индекси, PostgreSQL продолжи да користи {{{Sequential Scan}}} бидејќи query-от обработува огромен број редици и користи сложени aggregation операции како {{{AVG}}}, {{{COUNT}}} и {{{GROUP BY}}}.
 
-Во вакви ситуации planner-от проценува дека sequential processing е поефикасен од index traversal, па индексите имаат ограничено влијание врз вкупното време на извршување.
+Во вакви ситуации planner-от проценува дека sequential processing е поефикасен од index traversal, па индексите имаат ограничено влијание врз вкупното време на извршување. Поради ова, беше одлучено да се примени дополнителна оптимизација преку Материјализиран Поглед.
+
+=== Оптимизација со Материјализиран Поглед ===
+Бидејќи индексите не можеа значително да го подобрат времето на извршување поради природата на query-от (full-table aggregation со {{{GROUP BY}}} и повеќе {{{Hash Join}}} операции), беше креиран Материјализиран Поглед ({{{MATERIALIZED VIEW}}}) кој ја зачувува пресметаната агрегација и овозможува директно пребарување врз резултатот.
+
+За разлика од обичниот поглед кој го извршува query-от секој пат, Материјализираниот Поглед ги зачувува резултатите физички на диск, со што филтерот {{{WHERE city = 'Skopje'}}} работи врз само неколку стотици редици наместо врз милиони.
+{{{
+CREATE MATERIALIZED VIEW mv_public_artist_profiles AS
+SELECT
+    b.bookable_id,
+    b.display_name,
+    b.bookable_type,
+    l.city,
+    g.genre_name,
+    ROUND(AVG(r.rating), 2) AS average_rating,
+    COUNT(r.review_id)      AS total_reviews
+FROM Bookable b
+LEFT JOIN Location l       ON b.location_id = l.location_id
+LEFT JOIN BookableGenre bg ON b.bookable_id = bg.bookable_id
+LEFT JOIN Genre g          ON bg.genre_id = g.genre_id
+LEFT JOIN Offer o          ON b.bookable_id = o.bookable_id
+LEFT JOIN Booking bk       ON bk.offer_id = o.offer_id
+LEFT JOIN Review r         ON r.booking_id = bk.booking_id
+GROUP BY b.bookable_id, b.display_name, b.bookable_type, l.city, g.genre_name;
+
+CREATE UNIQUE INDEX idx_mv_profiles_bookable_id
+ON mv_public_artist_profiles(bookable_id);
+
+CREATE INDEX idx_mv_profiles_city
+ON mv_public_artist_profiles(city);
+
+CREATE INDEX idx_mv_profiles_rating
+ON mv_public_artist_profiles(average_rating DESC);
+}}}
+
+=== Време на извршување со Материјализиран Поглед ===
+'''3.1 - 0.147 ms'''
+{{{
+Bitmap Heap Scan on mv_public_artist_profiles  (cost=4.35..24.46 rows=9 width=856) (actual time=0.040..0.114 rows=213 loops=1)
+  Recheck Cond: ((city)::text = 'Skopje'::text)
+  Heap Blocks: exact=16
+  ->  Bitmap Index Scan on idx_mv_profiles_city  (cost=0.00..4.34 rows=9 width=0) (actual time=0.022..0.022 rows=213 loops=1)
+        Index Cond: ((city)::text = 'Skopje'::text)
+Planning Time: 0.275 ms
+Execution Time: 0.147 ms
+}}}
+
+По креирањето на Материјализираниот Поглед беше забележано драстично подобрување:
+ * од ~15.2 s (со индекси)
+ * на ~0.147 ms (со Материјализиран Поглед)
+
+Наместо да ги скенира милионите редици од табелите {{{Offer}}}, {{{Booking}}} и {{{Review}}}, PostgreSQL сега користи {{{Bitmap Index Scan}}} директно врз зачуваниот резултат преку индексот на {{{city}}}. Ова претставува подобрување од околу '''103,000 пати''' во споредба со оригиналниот query со индекси.
+
+За да останат податоците ажурирани, Материјализираниот Поглед се освежува по секоја промена на релевантните табели:
+{{{
+REFRESH MATERIALIZED VIEW CONCURRENTLY mv_public_artist_profiles;
+}}}