Changes between Version 39 and Version 40 of QueryOptimization

Timestamp:

05/09/26 21:18:50 (2 weeks ago)

Author:

231027

Comment:

--

QueryOptimization

@@ -435 +435 @@
 }}}
 
+==== Без индекс:
+
+ * '''SELECT'''
+
+{{{
+
+EXPLAIN ANALYZE
+    SELECT * FROM "Event_Overall_Ratings" WHERE event_id = 1;
+
+}}}
+
+ * '''INSERT'''
+
+{{{
+
+EXPLAIN ANALYZE
+    INSERT INTO "Event_Happening_Rating" (rating_id, event_happening_id, user_id, rating, comment)
+    SELECT COALESCE(MAX(rating_id), 0) + 1, 1, 15, 9, 'Test rating'
+    FROM "Event_Happening_Rating";
+
+}}}
+
+ * '''UPDATE'''
+
+{{{
+
+EXPLAIN ANALYZE
+    UPDATE "Event_Happening_Rating"
+    SET rating = 8, comment = 'New test rating'
+    WHERE event_happening_id = 1 AND user_id = 15;
+
+}}}
+
+Пресметката на просечни оценки бара постојано агрегирање на податоци, што е бавно при секој нов приказ. Композитен индекс на (event_happening_id, rating) овозможува математичките операции да се вршат директно врз индексот, забрзувајќи го приказот на почетната страна.
+
 ==== Оптимизација:
 
@@ -441 +476 @@
 -- composite index to speed up grouping and aggregate calculations (AVG, COUNT)
 CREATE INDEX idx_ehr_happening_id_rating ON "Event_Happening_Rating"(event_happening_id, rating);
+
+}}}
+
+==== Со индекс:
+
+ * '''SELECT'''
+
+{{{
+
+EXPLAIN ANALYZE
+    SELECT * FROM "Event_Overall_Ratings" WHERE event_id = 1;
+
+}}}
+
+ * '''INSERT'''
+
+{{{
+
+EXPLAIN ANALYZE
+    INSERT INTO "Event_Happening_Rating" (rating_id, event_happening_id, user_id, rating, comment)
+    SELECT COALESCE(MAX(rating_id), 0) + 1, 1, 20, 7, 'Test rating'
+    FROM "Event_Happening_Rating";
+
+}}}
+
+ * '''UPDATE'''
+
+{{{
+
+EXPLAIN ANALYZE
+    UPDATE "Event_Happening_Rating"
+    SET rating = 8, comment = 'New test rating'
+    WHERE event_happening_id = 1 AND user_id = 15;
 
 }}}