Posted inDatabase

PostgreSQLの最適化：Partial IndexとCovering Indexで容量を90%削減し、クエリを高速化する

Database tutorial - IT technology blog
Database tutorial - IT technology blog

背景：従来のインデックスが負荷になる時

データベース管理において、私たちはしばしば逆説的な状況に直面します。クエリを高速化しようとすればするほど、多くのインデックスを作成したくなります。しかし、過剰なインデックスはINSERTやUPDATEの操作を遅くさせ、同時にストレージ容量を急激に増大させます。1億5,000万レコードを超えるordersテーブルを持つeコマースシステムを6か月以上運用した結果、本番環境での通常のB-Treeインデックスだけでは不十分であることに気づきました。

以前は、MySQLの習慣からすべてのカラムにインデックスを作成していました。しかし、PostgreSQLではインデックスのカスタマイズ機能がはるかに柔軟です。パフォーマンスの問題を解決してくれた2つの「黄金」のテクニックは、Partial Indexes（部分インデックス）とCovering Indexes（カバリングインデックス）です。

私が実際に直面した問題は以下の通りです：

  • リソースの無駄： 3年以上前のキャンセルされた注文など、めったにクエリされない古いデータまでインデックス化されていた。
  • I/Oボトルネック： データベースがインデックス内でキーを見つけても、データを取得するためにメインテーブル（Heap）にアクセスする追加のステップが必要になり、遅延が発生していた。

Partial Indexes：本当に必要なものだけをインデックスする

概念とメリット

Partial Indexを使用すると、WHERE句を使用してインデックスが必要なデータの範囲を限定できます。1億5,000万行すべてにインデックスを貼る代わりに、実際に「ホット」なデータの1〜2%だけに集中させることができます。

このテクニックは、主に次の2つのシナリオで非常に効果的です：

  1. データの偏り（Skewed Data）： 例えば、usersテーブルに1,000万行あり、そのうち1%だけがunverified（未認証）であるとします。リマインダーメールを送信するためにこのグループだけを検索する必要がある場合は、未認証のユーザーのみをインデックス化します。
  2. NULL値の除外： カラムの大部分が空データである場合、容量を大幅に節約できます。

実戦での導入

tasksテーブルを例に挙げます。現在処理中のタスク（processing）には関心がありますが、完了したタスク（finished）に触れることはほとんどありません。

-- メモリ節約のため、処理中のタスクのみをインデックスする
CREATE INDEX idx_tasks_processing 
ON tasks (created_at) 
WHERE status = 'processing';

クエリを実行すると、PostgreSQLは自動的にこのインデックスを使用します：

SELECT * FROM tasks 
WHERE status = 'processing' 
ORDER BY created_at DESC;

重要な注意点： SQLステートメントには必ずWHERE status = 'processing'という条件を含める必要があります。これがないと、Postgresのプランナーはインデックスを無視し、フルテーブルスキャン（Seq Scan）に切り替えてしまいます。

Covering Indexes：Index-Only Scanの境地に達する

INCLUDEキーワードの威力

通常、インデックスにはキーカラム（key columns）のみが保存されます。インデックスに含まれていないカラムをクエリすると、データベースは元のテーブルからデータを取得するために「Heap Fetch」を実行する必要があります。このステップはI/Oリソースを消費し、レスポンス速度を低下させます。

Covering Index（PostgreSQL 11以降でサポート）では、INCLUDEキーワードを使用してインデックスに追加データを付加できます。目標はIndex-Only Scanを実現することです。つまり、メインテーブルに触れることなく、インデックスからすべてを取得することです。

実例

私は以前、時間に基づいてuser_idとaction_codeを非常に高い頻度でクエリするログシステムを最適化しました。

-- INCLUDEを使用したCovering Index
CREATE INDEX idx_logs_time_covering 
ON logs (created_at) 
INCLUDE (user_id, action_code);

なぜこの方法が賢いのでしょうか？ idx_logs_time_coveringにおいて：

  • created_atはソート and 検索に使用されます（Search Key）。
  • user_idとaction_codeは単なる「付随データ」です（Payload）。

ペイロードはソートに使用されないため、3つのカラムすべてを主キーにするよりもインデックスがコンパクトになります。また、INSERTの速度も大幅に向上します。

両方の組み合わせ：容量を90%削減したケーススタディ

配送管理プロジェクトでは、配送中（shipping）の注文情報を取得するために、これら2つを組み合わせました：

CREATE INDEX idx_orders_shipping_fast_track
ON orders (customer_id)
INCLUDE (total_amount, shipping_address)
WHERE status = 'shipping';

結果は驚くべきものでした。インデックスのサイズは12GBから800MBに減少しました。クエリ速度は500msから10ms未満に急降下しました。これらは、6か月間の安定稼働後の本番環境で測定された実際の数値です。

効果の監視と測定

インデックスがすぐに機能すると過信しないでください。私は常にEXPLAIN ANALYZEを使用してプランナーの判断を確認しています。

EXPLAIN ANALYZE 
SELECT total_amount FROM orders 
WHERE status = 'shipping' AND customer_id = 12345;

Index Only Scanという行が表示されれば成功です。また、定期的にpg_stat_user_indexesビューを確認してください。1週間経過してもインデックスのidx_scanが0のままであれば、リソースを解放するために迷わずそのインデックスを削除しましょう。

SELECT relname, indexrelname, idx_scan 
FROM pg_stat_user_indexes 
WHERE schemaname = 'public';

データベースの最適化は継続的なプロセスです。Partial IndexとCovering Indexを深く理解することで、ハードウェアのアップグレード費用をかけずに、困難なパフォーマンスの問題を解決できるようになります。

Share:
Tags: