Create indexes on the ranking tables

いくつかのテーブルでインデックスを張るようにしました。

submissionsテーブルの重複しているインデックスを削除しました(2023/01/13追加)

ユーザー名の大文字小文字を区別するSQLを見つけたので、区別しないようついでに修正しました。

背景

ブラウザの開発者ツールを眺めていて、ランキング関連のエンドポイントからの応答が遅い事に気が付きました。

Rated Point Sumのランキング（/v3/user/rated_point_sum_rank）が500ms前後、言語別AC数のランキング（/v3/language_ranking）は1.5秒～3秒の時間がかかっており、開発者ツール曰く、遅いとされる部類のようです。

database-definition.sqlを確認したところ、インデックスが張られていないことに気が付きました。ランキングのテーブルは頻繁に参照されるので、インデックスを張った方が良いと思いました。

Before After

-- ユーザーのRated Point Sumを取得する
EXPLAIN ANALYSE
SELECT
    point_sum
FROM
    rated_point_sum
WHERE
    LOWER(user_id) = 'hotate29';

張る前

Gather  (cost=1000.00..4870.15 rows=1264 width=17) (actual time=39.381..40.907 rows=1 loops=1)
  Workers Planned: 1
  Workers Launched: 1
  ->  Parallel Seq Scan on rated_point_sum  (cost=0.00..3743.75 rows=744 width=17) (actual time=28.255..38.239 rows=0 loops=2)
        Filter: (lower((user_id)::text) = 'hotate29'::text)
        Rows Removed by Filter: 126352
Planning Time: 0.213 ms
Execution Time: 40.920 ms

張った後

CREATE INDEX ON rated_point_sum (LOWER(user_id));

Bitmap Heap Scan on rated_point_sum  (cost=30.22..1563.15 rows=1264 width=17) (actual time=0.021..0.021 rows=1 loops=1)
  Recheck Cond: (lower((user_id)::text) = 'hotate29'::text)
  Heap Blocks: exact=1
  ->  Bitmap Index Scan on rated_point_sum_lower_idx  (cost=0.00..29.90 rows=1264 width=0) (actual time=0.009..0.009 rows=1 loops=1)
        Index Cond: (lower((user_id)::text) = 'hotate29'::text)
Planning Time: 0.252 ms
Execution Time: 0.029 ms

-- C++でのAC数上位3人を取得
EXPLAIN ANALYSE
SELECT
    user_id, problem_count
FROM
    language_count
WHERE
    simplified_language = 'C++'
ORDER BY
    problem_count DESC, user_id ASC
OFFSET 0 LIMIT 3;

張る前

Limit  (cost=8187.44..8187.79 rows=3 width=13) (actual time=33.363..33.364 rows=3 loops=1)
  ->  Gather Merge  (cost=8187.44..22404.89 rows=123630 width=13) (actual time=33.362..35.349 rows=3 loops=1)
        Workers Planned: 1
        Workers Launched: 1
        ->  Sort  (cost=7187.43..7496.51 rows=123630 width=13) (actual time=32.402..32.403 rows=2 loops=2)
              Sort Key: problem_count DESC, user_id
              Sort Method: top-N heapsort  Memory: 25kB
              Worker 0:  Sort Method: top-N heapsort  Memory: 25kB
              ->  Parallel Seq Scan on language_count  (cost=0.00..5589.54 rows=123630 width=13) (actual time=0.006..21.847 rows=104200 loops=2)
                    Filter: ((simplified_language)::text = 'C++'::text)
                    Rows Removed by Filter: 103780
Planning Time: 0.070 ms
Execution Time: 35.371 ms

張った後

CREATE INDEX ON language_count (simplified_language, problem_count DESC, user_id);

Limit  (cost=0.42..0.67 rows=3 width=13) (actual time=0.031..0.033 rows=3 loops=1)
  ->  Index Only Scan using language_count_simplified_language_problem_count_user_id_idx on language_count  (cost=0.42..17073.12 rows=210171 width=13) (actual time=0.030..0.033 rows=3 loops=1)
        Index Cond: (simplified_language = 'C++'::text)
        Heap Fetches: 3
Planning Time: 0.072 ms
Execution Time: 0.042 ms

kenkoooo / AtCoderProblems

Create indexes on the ranking tables #1337

背景

Before After