Changes between Version 2 and Version 3 of QueryOptimization

Timestamp:

05/20/26 12:11:21 (6 days ago)

Author:

231118

Comment:

--

QueryOptimization

@@ -4 +4 @@
 [attachment:QueryOptimization.pdf Query Optimization Report]
 
-
 ==== all_recipes ====
-Овој поглед се користи за прикажување на сите рецепти во системот, најчесто без филтри или со pagination. Бидејќи не користи JOIN или WHERE услови, не се применуваат индекси.
-
-Време на извршување:
+
+1. Случај на употреба:
+Овој поглед се користи за прикажување на сите рецепти во системот, најчесто без филтри или со pagination.
+
+2. Бидејќи погледот не користи JOIN или WHERE услови, базата не користи дополнителни индекси при извршување.
+
+3. Иницијално извршување на погледот.
+
+4. Времето изминато во извршување на операциите insert и update изнесува:
  * Пред оптимизација: ~200-400 ms
- * После оптимизација: ~200 ms
- * Заклучок: Прифатливо време, не е потребна оптимизација.
+
+5. Заклучок:
+Времето на извршување е прифатливо и не е потребна дополнителна оптимизација.
 
 
 ==== recipe_details ====
-Се користи за прикажување на детални информации за рецепт, заедно со кујната. Често се користи со филтри по recipe id или cuisine.
-
-Време на извршување:
- * Пред оптимизација: ~800-1500 ms
- * После оптимизација: ~50-120 ms
-
-Explain plan (пред):
+
+1. Случај на употреба:
+Овој поглед се користи кога корисникот сака да ги види целосните информации за еден рецепт. Перформансите се клучни бидејќи ова е една од најчесто користените страни.
+
+2. Примарен филтер за овој поглед е recipe_id, додека исто така може да се пребарува и по cuisine_id.
+
+3. Иницијално извршување на погледот.
+
+4. Explain plan (пред оптимизација):
  * Seq Scan на табелата recipe
- * Nested Loop Join со cuisine
-
-Оптимизација:
-{{{
-CREATE INDEX idx_recipe_cuisine_id ON recipe(cuisine_id);
-}}}
-
-Explain plan (после):
- * Index Scan на recipe
+ * Nested Loop Left Join со cuisine
+ * Execution Time: ~173 ms
+
+5. Времето изминато во извршување на операциите insert и update изнесува:
+ * INSERT: ~351 ms
+ * UPDATE: ~500 ms
+
+6. Оптимизација:
+Со цел да се забрза спојувањето на табелите и филтрирањето по тип на кујна, се додава индекс на cuisine_id.
+
+{{{
+CREATE INDEX IF NOT EXISTS idx_recipe_cuisine_id
+ON recipe(cuisine_id);
+}}}
+
+7. Explain plan (после оптимизација):
+ * Bitmap Heap Scan
+ * Bitmap Index Scan using idx_recipe_cuisine_id
+ * Execution Time: ~19 ms
+
+8. Времето изминато во извршување на операциите insert и update по индексирање изнесува:
+ * INSERT: ~136 ms
+ * UPDATE: ~302 ms
+
+9. Заклучок:
+Со додавање индекс на cuisine_id значително се подобри времето на извршување и пребарувањето по кујна се извршува многу побрзо.
+
+==== recipe_ingredients_view ====
+
+1. Секогаш кога некој ќе отвори рецепт, базата мора да ги повлече сите негови состојки. Ова повлекува JOIN операции помеѓу табелите recipe, ingredient и recipe_ingredient.
+
+2. Иницијално извршување на погледот.
+
+3. Explain plan (пред оптимизација):
+ * Nested Loop Join
+ * Index Scan using recipe_ingredient_pkey
+ * Execution Time: ~0.125 ms
+
+4. Времето изминато во извршување на операциите insert и update изнесува:
+ * INSERT: ~112 ms
+ * UPDATE: ~131 ms
+
+5. Оптимизација:
+Овие погледи ги поврзуваат рецептите со нивните состојки. Користењето на индекси на надворешните клучеви во табелата recipe_ingredient ги оптимизира JOIN операциите.
+
+{{{
+CREATE INDEX IF NOT EXISTS idx_recipe_ingredient_recipe_id
+ON recipe_ingredient(recipe_id);
+
+CREATE INDEX IF NOT EXISTS idx_recipe_ingredient_ingredient_id
+ON recipe_ingredient(ingredient_id);
+}}}
+
+6. Explain plan (после оптимизација):
+ * Nested Loop Join
+ * Index Scan using recipe_ingredient_pkey
+ * Execution Time: ~0.571 ms
+
+7. Иако беа креирани дополнителни индекси над колоните recipe_id и ingredient_id, не се забележува значителна промена во execution plan-от. Причината е што базата веќе користи постоечки индекси при извршување на JOIN операциите.
+
+8. Времето изминато во извршување на операциите insert и update по индексирање изнесува:
+ * INSERT: ~210 ms
+ * UPDATE: ~85 ms
+
+9. Заклучок:
+Иако нема значително подобрување во execution plan-от, индексирањето на надворешните клучеви претставува добра практика за оптимизација на JOIN операции кај поголеми табели.
+
+==== recipe_reviews_view ====
+
+1. Примарен филтер:
+Примарен филтер за овој поглед е recipe_id (за прикажување на сите оцени за еден рецепт), а секундарен филтер е user_id (за преглед на сите рецензии оставени од еден корисник).
+
+2. Случај на употреба:
+Овој поглед е од витално значење за социјалниот аспект на системот. Корисниците се потпираат на оцените и искуствата на другите корисници пред да одлучат да подготват рецепт. Доколку вчитувањето на рецензиите е бавно, корисникот може да ја напушти страницата.
+
+3. Иницијално извршување на погледот.
+
+4. Explain plan (пред оптимизација):
+ * Nested Loop Join
+ * Seq Scan на recipe_review
+ * Execution Time: ~484 ms за recipe_id
+ * Execution Time: ~0.151 ms за user_id
+
+5. Времето изминато во извршување на операциите insert и update изнесува:
+ * INSERT: ~161 ms
+ * UPDATE по recipe_id: ~130 ms
+ * UPDATE по recipe_id и user_id: ~149 ms
+
+6. Оптимизација:
+Со цел да се забрза извршувањето се креираат индекси на recipe_id и user_id.
+
+{{{
+CREATE INDEX IF NOT EXISTS idx_recipe_review_recipe_id
+ON recipe_review(recipe_id);
+
+CREATE INDEX IF NOT EXISTS idx_recipe_review_user_id
+ON recipe_review(user_id);
+}}}
+
+7. Explain plan (после оптимизација):
+ * Bitmap Heap Scan
+ * Bitmap Index Scan using idx_recipe_review_recipe_id
+ * Index Scan using recipe_review_user_id_recipe_id_key
+ * Execution Time: ~6.487 ms
+
+8. Времето изминато во извршување на операциите insert и update по индексирање изнесува:
+ * INSERT: ~170 ms
+ * UPDATE: ~150 ms
+
+9. Заклучок:
+Со додавање индекси на recipe_id и user_id значително се подобри пребарувањето и вчитувањето на рецензиите.
+
+
+==== user_favorites_view ====
+
+1. Примарен филтер за овој поглед е user_id.
+
+2. Случај на употреба:
+Корисниците сакаат брзо да ги видат своите омилени рецепти и најчесто зачуваните рецепти во системот.
+
+3. Иницијално извршување на погледот.
+
+4. Explain plan (пред оптимизација):
+ * Nested Loop Join
+ * Seq Scan на recipe_favorite
  * Hash Join
-
-Заклучок: Значително подобрување, времето е прифатливо.
-
-
-==== recipe_ingredients_view ====
-Се користи за прикажување на состојки за даден рецепт, најчесто со филтер по recipe_id.
-
-Време на извршување:
- * Пред оптимизација: 3-8 секунди
- * После оптимизација: 50-200 ms
-
-Explain plan (пред):
- * Seq Scan на recipe_ingredient (10M записи)
-
-Оптимизација:
-{{{
-CREATE INDEX idx_recipe_ingredient_recipe_id ON recipe_ingredient(recipe_id);
-CREATE INDEX idx_recipe_ingredient_ingredient_id ON recipe_ingredient(ingredient_id);
-}}}
-
-Explain plan (после):
- * Index Scan + Nested Loop
-
-Заклучок: Големо подобрување поради големината на табелата.
-
-
-==== recipe_reviews_view ====
-Се користи за приказ на оценки и коментари за рецепти, со филтри по recipe или user.
-
-Време на извршување:
- * Пред оптимизација: 2-5 секунди
- * После оптимизација: 50-150 ms
-
-Оптимизација:
-{{{
-CREATE INDEX idx_recipe_review_recipe_id ON recipe_review(recipe_id);
-CREATE INDEX idx_recipe_review_user_id ON recipe_review(user_id);
-}}}
-
-Заклучок: Значително намалување на времето.
-
-
-==== user_favorites_view ====
-Се користи за прикажување на омилени рецепти за даден корисник.
-
-Време на извршување:
- * Пред оптимизација: 1-3 секунди
- * После оптимизација: 30-100 ms
-
-Оптимизација:
-{{{
-CREATE INDEX idx_recipe_favorite_user_id ON recipe_favorite(user_id);
-CREATE INDEX idx_recipe_favorite_recipe_id ON recipe_favorite(recipe_id);
-}}}
-
-Заклучок: Прифатливо време по оптимизација.
+ * Execution Time: ~97 ms при пребарување по recipe_id
+ * Execution Time: ~0.199 ms при пребарување по user_id
+
+Базата користи recipe_favorite_pkey за основни пребарувања по корисник.
+
+5. Времето изминато во извршување на операциите insert и update изнесува:
+ * INSERT: ~170 ms
+ * UPDATE: ~224 ms
+
+6. Оптимизација:
+Со цел да се забрза извршувањето се креираат индекси на recipe_id и user_id.
+
+{{{
+CREATE INDEX idx_recipe_favorite_user_id
+ON recipe_favorite(user_id);
+
+CREATE INDEX idx_recipe_favorite_recipe_id
+ON recipe_favorite(recipe_id);
+}}}
+
+7. Explain plan (после оптимизација):
+ * Bitmap Heap Scan
+ * Bitmap Index Scan using idx_recipe_favorite_recipe_id
+ * Index Only Scan using recipe_favorite_pkey
+ * Execution Time: ~8.197 ms
+
+8. Времето изминато во извршување на операциите insert и update по индексирање изнесува:
+ * INSERT: ~79 ms
+ * UPDATE: ~97 ms
+
+9. Заклучок:
+Со индексирање на колоните recipe_id и user_id значително се подобри пребарувањето на омилени рецепти и се намали времето на извршување.
 
 
 ==== quick_recipes ====
-Се користи за прикажување на рецепти со време на подготовка <= 30 минути.
-
-Време на извршување:
- * Пред оптимизација: 500-1000 ms
- * После оптимизација: 20-80 ms
-
-Оптимизација:
-{{{
-CREATE INDEX idx_recipe_total_time ON recipe(total_time);
-}}}
-
-Заклучок: Филтрирањето значително се подобрува со индекс.
+
+1. Примарен филтер за овој поглед е total_time.
+
+2. Случај на употреба:
+Корисниците често пребаруваат "брзи рецепти" кои се готови за под 30 минути.
+
+3. Проблем:
+Се извршува Seq Scan на табелата recipe при пребарување и сортирање според total_time.
+
+4. Иницијално извршување на погледот.
+
+5. Explain plan (пред оптимизација):
+ * Seq Scan на recipe
+ * Sort operation по total_time
+ * Execution Time: ~79 ms
+
+6. Времето изминато во извршување на операциите insert и update изнесува:
+ * INSERT: ~242 ms
+ * UPDATE: ~1.188 s
+
+7. Оптимизација:
+За да може базата брзо да ги подреди и филтрира рецептите според времето на подготовка, се додава индекс на колоната total_time.
+
+{{{
+CREATE INDEX IF NOT EXISTS idx_recipe_total_time
+ON recipe(total_time);
+}}}
+
+8. Explain plan (после оптимизација):
+ * Bitmap Heap Scan
+ * Bitmap Index Scan using idx_recipe_total_time
+ * Execution Time: ~42 ms
+
+9. Времето изминато во извршување на операциите insert и update по индексирање изнесува:
+ * INSERT: ~148 ms
+ * UPDATE: ~2.989 s
+
+10. Заклучок:
+Со индексирање на total_time значително се подобри пребарувањето и сортирањето на брзи рецепти.
 
 
 ==== forum_posts_view ====
-Се користи за прикажување на форум постови заедно со корисник.
-
-Време на извршување:
- * Пред оптимизација: 1-2 секунди
- * После оптимизација: 50-150 ms
-
-Оптимизација:
-{{{
-CREATE INDEX idx_forumpost_user_id ON forumpost(user_id);
-CREATE INDEX idx_forumpost_created_at ON forumpost(created_at);
-}}}
-
-Заклучок: Подобрени перформанси при прикажување и сортирање.
+
+1. Примарен филтер за погледот forum_posts_view е user_id.
+
+2. Случај на употреба:
+Погледот се користи за прикажување форум објави и пребарување на објави според корисник и датум.
+
+3. Иницијално извршување на погледот.
+
+4. Explain plan (пред оптимизација):
+ * Gather operation
+ * Parallel Seq Scan на forumpost
+ * Nested Loop Left Join
+ * Execution Time: ~3752 ms за user_id
+ * Execution Time: ~4291 ms за user_id и created_at
+
+5. Времето изминато во извршување на операциите insert и update изнесува:
+ * INSERT: ~288 ms
+ * UPDATE по id: ~64 ms
+ * Масовен UPDATE: ~14.10 s
+
+6. Оптимизација:
+Со цел да се забрза извршувањето се креираат индекси на user_id и created_at.
+
+{{{
+CREATE INDEX idx_forumpost_user_id
+ON forumpost(user_id);
+
+CREATE INDEX idx_forumpost_created_at
+ON forumpost(created_at);
+}}}
+
+7. Explain plan (после оптимизација):
+ * Bitmap Heap Scan
+ * Bitmap Index Scan using idx_forumpost_user_id
+ * Bitmap Index Scan using idx_forumpost_created_at
+ * Execution Time: ~0.211 ms
+ * Execution Time: ~0.415 ms
+
+8. Времето изминато во извршување на операциите insert и update по индексирање изнесува:
+ * INSERT: ~96 ms
+ * UPDATE: ~160 ms
+
+9. Заклучок:
+Со индексирање на user_id и created_at значително се подобри пребарувањето и филтрирањето на форум објавите.
 
 
 ==== recipe_categories_view ====
-Се користи за прикажување категории на рецепти.
-
-Време на извршување:
- * Пред оптимизација: 1-3 секунди
- * После оптимизација: 50-150 ms
-
-Оптимизација:
-{{{
-CREATE INDEX idx_recipe_category_recipe_id ON recipe_category(recipe_id);
-CREATE INDEX idx_recipe_category_category_id ON recipe_category(category_id);
-}}}
-
-Заклучок: Значително подобрување поради join операциите.
-
-
-=== Заклучок ===
-
-Оптимизацијата на погледите со користење на индекси доведе до значително подобрување на перформансите, особено кај табели со голем број записи (10M+). Времето на извршување се намали од неколку секунди на милисекунди, што е прифатливо за продукциски систем. За погледи без филтри и join операции, индексите немаат значително влијание.
+
+1. Примарен филтер за погледот recipe_categories_view е според recipe_id и category_id.
+
+2. Случај на употреба:
+Погледот се користи за прикажување категории на рецепти и пребарување рецепти според категорија.
+
+3. Иницијално извршување на погледот.
+
+4. Explain plan (пред оптимизација):
+ * Nested Loop Join
+ * Parallel Seq Scan на recipe_category
+ * Hash Join
+ * Execution Time: ~61 ms за пребарување по category_id
+ * Execution Time: ~0.277 ms за пребарување по recipe_id
+
+5. Времето изминато во извршување на операциите insert и update изнесува:
+ * INSERT: ~160 ms
+ * UPDATE: ~302 ms
+
+6. Оптимизација:
+Со цел да се забрза извршувањето се креираат индекси на recipe_id и category_id.
+
+{{{
+CREATE INDEX idx_recipe_category_recipe_id
+ON recipe_category(recipe_id);
+
+CREATE INDEX idx_recipe_category_category_id
+ON recipe_category(category_id);
+}}}
+
+7. Explain plan (после оптимизација):
+ * Bitmap Heap Scan
+ * Bitmap Index Scan using idx_recipe_category_category_id
+ * Index Only Scan using recipe_category_pkey
+ * Execution Time: ~225 ms за category_id
+ * Execution Time: ~0.341 ms за recipe_id
+
+8. Времето изминато во извршување на операциите insert и update по индексирање изнесува:
+ * INSERT: ~117 ms
+ * UPDATE: ~159 ms
+
+9. Заклучок:
+Со индексирање на recipe_id и category_id се подобри пребарувањето и JOIN операциите кај категориите на рецепти.