PostgreSQLで1000万以上のレコードがあるテーブルを高速化したい

Question

やりたいこと

PostgreSQLで1000万以上のレコードがあるテーブルを検索する際に、4~5秒ほどかかっているのでそれを高速化したいです。

テーブル構成

テーブル名: streams

streamer_id | video_id | user_id | display_name | timestamp | comment 

streamer_id: 配信をしている人物のid
video_id: 配信が終了したときに生成されるアーカイブのid
user_id: コメントしたユーザーのid
display_name: コメントしたユーザーの別名
timestamp: コメントした時間
comment: コメント内容

使用されるクエリ

SELECT * FROM streams WHERE streamer_id='任意' LIMIT 500;
SELECT * FROM streams WHERE user_id='任意' LIMIT 500;
SELECT * FROM streams WHERE comment='任意' LIMIT 500;

もしくは上記の組み合わせ

クエリプランの結果

EXPLAIN ANALYZE SELECT COUNT(*) FROM streams;

                                                                  QUERY PLAN                                                                   
-----------------------------------------------------------------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=309837.86..309837.87 rows=1 width=8) (actual time=2502.391..2520.194 rows=1 loops=1)
   ->  Gather  (cost=309837.65..309837.86 rows=2 width=8) (actual time=2501.869..2520.135 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=308837.65..308837.66 rows=1 width=8) (actual time=2434.762..2434.763 rows=1 loops=3)
               ->  Parallel Seq Scan on streams  (cost=0.00..290917.12 rows=7168212 width=0) (actual time=1.051..1841.764 rows=5732370 loops=3)
 Planning Time: 1.070 ms
 JIT:
   Functions: 8
   Options: Inlining false, Optimization false, Expressions true, Deforming true
   Timing: Generation 1.713 ms, Inlining 0.000 ms, Optimization 1.527 ms, Emission 32.722 ms, Total 35.962 ms
 Execution Time: 2590.130 ms
(12 rows)

条件

user_idとstreamer_idは未知数です。
アーカイブによってはuser_idが複数回記録されたり、また同一のvideo_idを使ったレコード自体もユーザーのコメントに応じて無制限に増えます。

Comments

[maisumakun]

インデックスの貼り方はどんな感じでしょうか？

Answer 1

CREATE INDEX idx_streamer_id ON streams (streamer_id);
CREATE INDEX idx_user_id ON streams (user_id);
CREATE INDEX idx_comment ON streams (comment);
CREATE INDEX idx_streamer_user ON streams (streamer_id, user_id);

インデックスを見直したところ２０倍ほど早くなった

Answer 2

特定のIDで検索する部分はインデックスがきけば高速になります
コメント欄についてはどのくらい容量をとるかによりますがあまり効率よく検索はできないかも
場合によってはキーワードのみ抽出して別テーブルで管理するか、もしくは全文検索に対応させるか工夫次第ですかね・・・