Changes between Version 4 and Version 5 of otherdevelopment

Timestamp:

02/03/26 12:44:15 (3 days ago)

Author:

231175

Comment:

--

otherdevelopment

@@ -4 +4 @@
 
 === Извештај за најпопуларни курсеви според оценка ===
+
+Анализата на перформанси се врши врз основа на моменталната состојба во базата, која има податоци кои служат само за тестирање. Базата содржи: 1000 курсеви, 2000 преводи (по 2 преводи по курс), 1000 верзии на курсот (по 1 верзија по курс), 5000 корисници, 200 експерти, 10000 enrollments, 7000 reviews
 
 '''SQL:'''
@@ -34 +36 @@
 Користење: JOIN на `ct.course_id` + WHERE филтер на `ct.language`  
 
-Подобрување: Index Scan наместо Seq Scan (**~60x побрзо**)
+Подобрување: Index Scan наместо Seq Scan
 
 ---
@@ -45 +47 @@
 Користење: JOIN на `cv.course_id`  
 
-Подобрување: Директен lookup (**~45x побрзо**)
+Подобрување: Директен lookup
 
 ---
@@ -56 +58 @@
 Користење: JOIN на `e.course_version_id`  
 
-Подобрување: Избегнува full table scan (**~60x побрзо**)
+Подобрување: Избегнува full table scan
 
 ---
@@ -67 +69 @@
 Користење: JOIN на `r.enrollment_id`  
 
-Подобрување: Unique scan (**~35x побрзо**)
+Подобрување: Unique scan
 
 ---
@@ -81 +83 @@
 ---
 
+{{{
+CREATE OR REPLACE FUNCTION test_popular_courses()
+RETURNS void AS $$
+DECLARE
+  start_time timestamptz;
+  end_time timestamptz;
+  duration int;
+BEGIN
+  start_time := clock_timestamp();
+  
+  PERFORM 
+    c.id AS course_id,
+    ct.title_short AS course_title,
+    cv.version_number AS course_version,
+    cv.active AS is_active,
+    COUNT(DISTINCT e.id) AS total_enrollments,
+    SUM(r.rating)::numeric / COUNT(r.id) AS average_rating,
+    COUNT(r.id) AS total_reviews
+  FROM course c
+  JOIN course_translate ct ON c.id = ct.course_id
+  JOIN course_version cv ON c.id = cv.course_id
+  JOIN enrollment e ON cv.id = e.course_version_id
+  JOIN review r ON e.id = r.enrollment_id
+  WHERE ct.language = 'en'
+  GROUP BY c.id, ct.id, cv.id
+  ORDER BY SUM(r.rating)::numeric / COUNT(r.id) DESC
+  LIMIT 20;
+  
+  end_time := clock_timestamp();
+  duration := round(1000 * (extract(epoch FROM end_time) - extract(epoch FROM start_time)));
+  
+  RAISE NOTICE 'Query executed in: % ms', duration;
+END;
+$$ LANGUAGE plpgsql;
+
+DROP INDEX IF EXISTS uk_course_translate_course_language;
+DROP INDEX IF EXISTS idx_course_version_course_id;
+DROP INDEX IF EXISTS idx_enrollment_course_version_id;
+DROP INDEX IF EXISTS uk_review_enrollment;
+
+-- run 1: no indexes
+
+SELECT test_popular_courses();
+
+-- run 2: index uk_course_translate_course_language
+
+CREATE UNIQUE INDEX uk_course_translate_course_language 
+ON course_translate(course_id, language);
+ANALYZE course_translate;
+
+SELECT test_popular_courses();
+
+-- run 3: index uk_course_translate_course_language + idx_course_version_course_id
+
+CREATE INDEX idx_course_version_course_id 
+ON course_version(course_id);
+ANALYZE course_version;
+
+SELECT test_popular_courses();
+
+-- run 4: index uk_course_translate_course_language + idx_course_version_course_id + idx_enrollment_course_version_id
+
+CREATE INDEX idx_enrollment_course_version_id 
+ON enrollment(course_version_id);
+ANALYZE enrollment;
+
+SELECT test_popular_courses();
+
+-- run 5: all indexes -> uk_course_translate_course_language + idx_course_version_course_id + idx_enrollment_course_version_id + uk_review_enrollment
+
+CREATE UNIQUE INDEX IF NOT EXISTS uk_review_enrollment 
+ON review(enrollment_id);
+ANALYZE review;
+
+SELECT test_popular_courses();
+
+DROP FUNCTION test_popular_courses();
+}}}
+
 **Сумарно:**
 - **Без индекси:** Seq Scan на сите табели (бавно)
 - **Со индекси:** Index Scan на сите JOIN-ови и WHERE (брзо)
-- **Подобрување:** **~30x побрзо** 
+- **Перформанси:**
+ - Без индекси: 51ms
+ - Со индекси: 23ms
+- **Забрзување: 2.2 пати**