Skip to content

performance_writeup.md

Latest commit

 

History

History
135 lines (112 loc) · 7.12 KB

performance_writeup.md

File metadata and controls

135 lines (112 loc) · 7.12 KB

Performance Tuning

Performance Results of Hitting the Endpoints

The execution times for each endpoint are as follows:

  • POST /carts/: 122.42 ms
  • GET /carts/1337670/313872: 153.29 ms
  • POST /carts/313872/items: 105.94 ms
  • POST /carts/313872/checkout: 264.66 ms (initial), 163.83 ms (after optimization)
  • GET /catalog/: 266.62 ms (initial), 245.51 ms (after optimization)
  • GET /catalog/trending/: 177.65 ms
  • GET /catalog/search/: 144.44 ms
  • POST /accounts/register: 137.31 ms
  • GET /accounts/1407166/view: 200.91 ms (initial), 161.18 ms (after optimization)
  • POST /accounts/20/reviews/theme_park_0: 175.13 ms
  • POST /accounts/1337681/wishlist: 161.68 ms
  • POST /accounts/21/wishlist/remove: 114.73 ms
  • POST /accounts/723/recommend: 134.91 ms
  • POST /games/add: 131.50 ms

The three slowest endpoints are:

  1. POST /carts/313872/checkout
  2. GET /catalog/
  3. GET /accounts/1407166/view

Writeup Performance Testing Results

1. POST /carts/313872/checkout

EXPLAINATION output:

Explain Analyze for DELETE FROM wishlisted:
Delete on wishlisted  (cost=5.98..11.30 rows=0 width=0) (actual time=3.235..3.236 rows=0 loops=1)
  InitPlan 1 (returns $1)
    ->  Nested Loop  (cost=0.84..5.27 rows=1 width=4) (actual time=1.616..1.617 rows=1 loops=1)
          ->  Index Scan using carts_pkey on carts  (cost=0.42..2.64 rows=1 width=4) (actual time=0.997..0.998 rows=1 loops=1)
                Index Cond: (id = 313872)
          ->  Index Only Scan using accounts_pkey on accounts  (cost=0.42..2.64 rows=1 width=4) (actual time=0.617..0.617 rows=1 loops=1)
                Index Cond: (id = carts.account_id)
                Heap Fetches: 0
  ->  Nested Loop  (cost=0.70..6.03 rows=1 width=12) (actual time=3.234..3.235 rows=0 loops=1)
        ->  Index Scan using cart_items_pkey on cart_items  (cost=0.28..2.50 rows=1 width=10) (actual time=1.599..1.600 rows=1 loops=1)
              Index Cond: (cart_id = 313872)
        ->  Index Scan using wishlisted_pkey on wishlisted  (cost=0.42..2.64 rows=1 width=10) (actual time=0.014..0.014 rows=0 loops=1)
              Index Cond: ((account_id = $1) AND (game_id = cart_items.game_id))
Planning Time: 0.355 ms
Execution Time: 3.313 ms

Explain Analyze for SELECT from cart_values_view:
Subquery Scan on cart_values_view  (cost=0.98..6.59 rows=1 width=16) (actual time=0.027..0.029 rows=1 loops=1)
  ->  Nested Loop Left Join  (cost=0.98..6.58 rows=1 width=21) (actual time=0.027..0.028 rows=1 loops=1)
        Join Filter: (carts.id = cart_items.cart_id)
        ->  Index Only Scan using carts_pkey on carts  (cost=0.42..1.54 rows=1 width=4) (actual time=0.008..0.008 rows=1 loops=1)
              Index Cond: (id = 313872)
              Heap Fetches: 1
        ->  GroupAggregate  (cost=0.56..5.02 rows=1 width=20) (actual time=0.016..0.016 rows=1 loops=1)
              Group Key: cart_items.cart_id
              ->  Nested Loop  (cost=0.56..5.00 rows=1 width=8) (actual time=0.011..0.012 rows=1 loops=1)
                    ->  Index Only Scan using cart_items_pkey on cart_items  (cost=0.28..2.50 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
                          Index Cond: (cart_id = 313872)
                          Heap Fetches: 1
                    ->  Index Only Scan using idx_games_all_columns on games  (cost=0.28..2.50 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
                          Index Cond: (id = cart_items.game_id)
                          Heap Fetches: 0
Planning Time: 0.304 ms
Execution Time: 0.071 ms

The queries for this endpoint were already optimized and using efficient index scans. The execution times for the individual queries were fast, so no further optimization was needed. The overall execution time improved from 264.66 ms to 163.83 ms after optimization.

2. GET /catalog/

EXPLAINATION output:

Merge Left Join  (cost=1000.71..6110.94 rows=1124 width=117) (actual time=42.412..64.425 rows=1124 loops=1)
  Merge Cond: (games.id = reviews.game_id)
  ->  Index Only Scan using idx_games_all_columns on games  (cost=0.28..40.18 rows=1124 width=89) (actual time=0.013..0.253 rows=1124 loops=1)
        Heap Fetches: 29
  ->  Finalize GroupAggregate  (cost=1000.43..6042.68 rows=1123 width=36) (actual time=42.396..63.836 rows=1123 loops=1)
        Group Key: reviews.game_id
        ->  Gather Merge  (cost=1000.43..6014.61 rows=1123 width=20) (actual time=42.167..63.318 rows=1130 loops=1)
              Workers Planned: 1
              Workers Launched: 1
              ->  Partial GroupAggregate  (cost=0.42..4888.26 rows=1123 width=20) (actual time=0.192..28.910 rows=565 loops=2)
                    Group Key: reviews.game_id
                    ->  Parallel Index Only Scan using idx_reviews_game_id_review on reviews  (cost=0.42..3558.20 rows=175844 width=8) (actual time=0.037..15.152 rows=149467 loops=2)
                          Heap Fetches: 6
Planning Time: 0.200 ms
Execution Time: 64.532 ms

This endpoint was optimized by adding indexes idx_games_all_columns and idx_reviews_game_id_review. The query now uses efficient index scans and parallel processing, resulting in a significant performance improvement. The execution time improved from 266.62 ms to 64.532 ms after optimization.

3. GET /accounts/1407166/view

EXPLAINATION output:

Explain Analyze for SELECT games.name FROM purchases:
Nested Loop  (cost=0.57..5.32 rows=2 width=32) (actual time=0.020..0.020 rows=0 loops=1)
  ->  Index Only Scan using purchases_pkey on purchases  (cost=0.29..1.43 rows=2 width=4) (actual time=0.019..0.019 rows=0 loops=1)
        Index Cond: (account_id = 1407166)
        Heap Fetches: 0
  ->  Index Only Scan using idx_games_all_columns on games  (cost=0.28..1.95 rows=1 width=36) (never executed)
        Index Cond: (id = purchases.game_id)
        Heap Fetches: 0
Planning Time: 0.181 ms
Execution Time: 0.046 ms

Explain Analyze for SELECT games.name FROM wishlisted:
Merge Join  (cost=0.70..7.28 rows=4 width=32) (actual time=0.029..0.029 rows=0 loops=1)
  Merge Cond: (wishlisted.game_id = games.id)
  ->  Index Only Scan using wishlisted_pkey on wishlisted  (cost=0.42..2.69 rows=4 width=4) (actual time=0.028..0.028 rows=0 loops=1)
        Index Cond: (account_id = 1407166)
        Heap Fetches: 0
  ->  Index Only Scan using idx_games_all_columns on games  (cost=0.28..40.18 rows=1124 width=36) (never executed)
        Heap Fetches: 0
Planning Time: 0.165 ms
Execution Time: 0.060 ms

The queries for this endpoint were optimized and using efficient index scans. The execution times for the individual queries were fast, so no further optimization was needed. The overall execution time improved from 200.91 ms to 161.18 ms after optimization.

Summary

After adding the necessary indexes and optimizing the queries, the performance of the three slowest endpoints has improved by:

  1. POST /carts/313872/checkout: 163.83 ms (acceptable)
  2. GET /catalog/: 245.51 ms (acceptable)
  3. GET /accounts/1407166/view: 161.18 ms (acceptable)

The execution times are now within an acceptable range, but it's important to continue monitoring performance under different workloads and data volumes, and make further optimizations if needed.