PostgreSQL全文検索: 正しくやれば速い（遅いという誤解を解く）

• PostgreSQLの標準Full-Text Search(FTS)は遅いという認識があるが、適切に最適化すれば非常に高速に動作する
• NeonのブログではRustベースのpg_search拡張と標準FTSを比較し、後者は遅いと主張している
• しかしこの比較は、PostgreSQL FTSに不可欠な基本的な最適化作業が欠けた状態で行われた可能性が高い
• 本記事では、標準FTS設定に簡単な最適化を施すだけで50倍の性能向上が可能であることを数値で示している

ベンチマーク設定の概要

• 1,000万件のログデータを持つテーブルをベースにテストを実施

  CREATE TABLE benchmark_logs (  
      id SERIAL PRIMARY KEY,  
      message TEXT,  
      country VARCHAR(255),  
      severity INTEGER,  
      timestamp TIMESTAMP,  
      metadata JSONB  
  );  
  

• 問題のクエリ構造:

  SELECT country, COUNT(*)  
  FROM benchmark_logs  
  WHERE to_tsvector('english', message) @@ to_tsquery('english', 'research')  
  GROUP BY country  
  ORDER BY country;  
  

  • to_tsvector()をクエリ内で実行 → 非常に非効率
  • GINインデックスがあっても適切に活用されない

テスト環境（標準設定を再現）

• EC2 i7ie.xlargeインスタンス、ローカルNVMe SSDを使用
• 4 vCPUs、PostgreSQL 16(Docker)を使用
• 主なPostgreSQL設定:

  -c shared_buffers=8GB  
  -c maintenance_work_mem=8GB  
  -c max_parallel_workers=4  
  -c max_worker_processes=4  
  

• 並列処理の制限: max_parallel_workers_per_gather = 2 (Neonは8を使用)

性能低下要因 1: リアルタイムのtsvector計算

• to_tsvector()をクエリ内で実行する場合:
• テキスト解析、形態素解析などを毎回実行
• インデックスをまったく活用できない

• 解決策: tsvectorカラムを事前生成してインデックス化

  • 1. tsvectorカラムを追加

  ALTER TABLE benchmark_logs ADD COLUMN message_tsvector tsvector;  
  

  • 2. データを投入

    UPDATE benchmark_logs SET message_tsvector = to_tsvector('english', message);  
    

  • 3. インデックスを作成 (fastupdateを無効化)

    CREATE INDEX idx_gin_logs_message_tsvector  
    ON benchmark_logs USING GIN (message_tsvector)  
    WITH (fastupdate = off);  
    

  • 4. クエリを修正

    SELECT country, COUNT(*)  
    FROM benchmark_logs  
    WHERE message_tsvector @@ to_tsquery('english', 'research')  
    GROUP BY country  
    ORDER BY country;  
    

性能低下要因 2: GINインデックスのfastupdate=on設定

• fastupdate=onは書き込み性能には有利だが、検索性能には悪影響
• 読み取り専用または検索中心のデータセットではfastupdate=offが必須
• インデックスがより小さく高速になり、pending listの処理も不要

• 最適化されたGINインデックスの作成方法

  CREATE INDEX idx_gin_logs_message_tsvector  
  ON benchmark_logs USING GIN (message_tsvector)  
  WITH (fastupdate = off);  
  

性能向上の数値: 50倍以上の改善

• 最適化前: 約41.3秒 (41,301 ms)
• 最適化後: 約0.88秒 (877 ms)
• 約50倍の性能向上を示した
• 並列処理数が少ない環境でもこの性能を達成可能

ts_rankの性能は実際に遅いことがある

• ts_rankまたはts_rank_cdはすべての結果を評価してから並べ替えるため、相対的に遅くなることがある
• 特に大量の結果を扱う場合はCPU/IO負荷が大きい

高度なランキング機能: VectorChord-BM25拡張

• 並べ替えの精度と速度が重要な場合は、専用拡張を使う方が効果的
• VectorChord-BM25はPostgreSQL向け拡張で、BM25アルゴリズムベースのランキング評価機能を提供
• Elasticsearchより3倍速いという報告もある

VectorChord-BM25の利点

• BM25アルゴリズム: TF-IDFより進化した検索ランキングアルゴリズム
• 専用インデックス形式: Block WeakAnd など、高速検索に最適化
• bm25vector型を提供: トークン化された表現を保存
• 検索精度と速度の両方を向上

結論: PostgreSQL標準FTSも十分に速い

• tsvectorカラムと適切なGINインデックス(fastupdate=off)を使えば、標準FTSでも非常に高速な検索が可能
• 性能比較は最適化された基準で行われるべき
• 高度なランキング機能が必要な場合は、VectorChord-BM25のような拡張ツールの活用を検討できる
• 核心メッセージ: ツールが遅いのではなく、設定が問題である可能性がある

[stadia]

おかげでクエリのチューニングをしました。

[pcj9024]

Hacker Newsの反応、怖いですね…「1000万件？ 冗談でしょ？」

[GN⁺]

Hacker News の意見

• pg_search のメンテナーとして言うと、Postgres のドキュメントによれば、Neon/ParadeDB の記事とここで使われている戦略はいずれも有効な代替案として提示されている

  • Postgres FTS の問題は、単一クエリを最適化することではなく、多様な実際のクエリに対して Elastic 並みの性能を提供することにある
  • pg_search は後者の問題を解決するために設計されており、ベンチマークもそれを反映している
  • Neon/ParadeDB のベンチマークは合計 12 個のクエリを含むが、現実的なユースケースとしては非現実的である
  • pg_search は、さまざまな「Elastic スタイル」のクエリと Postgres の型に対して、シンプルなインデックス定義だけで動作する

• tsvector をリアルタイムで計算するのは大きなミスだ

  • Postgres FTS を個人プロジェクトに実装したとき、ドキュメントを読み、ガイドラインに従った
  • ドキュメントは、基本的な非最適化ケースを作り、それを最適化する過程を明確に説明している
  • このミスをした人は、ドキュメントを読んでいないか、Postgres FTS を誤って表現しようという意図があるように思える

• 何でもかんでも Postgres に入れようとする傾向は理解できない

• Postgres ネイティブの全文検索実装をもっと多く見られるのはうれしい

  • 代替ソリューション（lucene/tantivy）は不変セグメント向けに設計されているため、Postgres のヒープテーブルと組み合わせると、かえって悪い解決策になる可能性がある

• 説明計画がないので、何が起きているのか理解しづらい

  • クエリがインデックスを使うなら、リアルタイム tsvector の再検査は一致項目にだけ適用され、ベンチマーククエリは LIMIT 10 なので再検査は少ない
  • クエリ条件が 2 つの gin インデックスに条件を持っているため、プランナーはまずすべての一致項目を再検査しているように見える

• 数年前、ネイティブ FTS を使いたかったが失敗した

  • 1 秒あたり数千件の挿入があるテーブルで、全体更新が遅くなり、トランザクションがタイムアウトした
  • インデックスを追加したが、2 つ目のインデックスが完了した時点でシステムでタイムアウトが発生した
  • インデックスを再び削除しなければならず、実際の FTS 性能をテストする機会すら得られなかった

• pg_search と vchord_bm25 拡張の RPM/DEB をパッケージした

  • 自分でベンチマークしたい人のためにリンクを提供する

• 多くのチームが Elasticsearch や Meilisearch にそのまま移行するのを見てきた

  • 適切に使えば、ネイティブ PG FTS から多くの性能を引き出せる
  • SQLite + FTS5 + Wasm を使って、ブラウザ内で同様の性能を得られるのか気になる

• 1,000 万件のレコードはおもちゃのデータセットだ

  • Wikipedia 全体や 2022 年以前の Reddit コメントのような大規模テキストデータセットの方が、ベンチマークには適している

• 2008 年ごろに初めて pg の全文検索を使った

  • Postgres 全文検索の問題は、遅すぎることではなく、柔軟性が足りなすぎることだ
  • 簡単な検索を追加するには良いが、検索をチューニングするには力不足だ
  • Solr や Elasticsearch では、複雑なインデックスや検索処理を設定できる
  • Postgres もこうした機能を採用できるはずだが、現時点では何も提供していない
  • Postgres は空白で分割し、手動でストップワードやステミングを使うことができる
  • フィールド重みに基づく検索スコアリングは不可能だ
  • 代替手段と比べると、おもちゃのようなシステムである