Query Optimization

Оптимизација на погледи

Query Optimization Report​

all_recipes

1. Случај на употреба:

Овој поглед се користи за прикажување на сите рецепти во системот, најчесто без филтри или со pagination.

2. Бидејќи погледот не користи JOIN или WHERE услови, базата не користи дополнителни индекси при извршување.

3. Иницијално извршување на погледот.

4. Времето изминато во извршување на операциите insert и update изнесува:
   • Пред оптимизација: ~200-400 ms

5. Заклучок:

Времето на извршување е прифатливо и не е потребна дополнителна оптимизација.

recipe_details

1. Случај на употреба:

Овој поглед се користи кога корисникот сака да ги види целосните информации за еден рецепт. Перформансите се клучни бидејќи ова е една од најчесто користените страни.

2. Примарен филтер за овој поглед е recipe_id, додека исто така може да се пребарува и по cuisine_id.

3. Иницијално извршување на погледот.

4. Explain plan (пред оптимизација):
   • Seq 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
   • 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

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. Примарен филтер за погледот 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. Примарен филтер за погледот 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 операциите кај категориите на рецепти.