【PostgreSQL】複合インデックスを利用したパフォーマンス改善事例

Last updated at 2026-05-01Posted at 2026-05-01

はじめに

PostgreSQLのプランナ（クエリの実行をどうやって効率的に行うかを決定する頭脳）が、単一インデックスでは効率的にSQLを実行できず、複合インデックスを用意してあげることにより、SQLの実行を効率的に実行できるようになった事例の紹介になります。

・本記事の各種クエリの実行は、PostgreSQLのバージョン15系を利用した内容です
・複合インデックスは「複数列インデックス」とも呼ばれますが、本記事では「複合インデックス」と表記します。

改善前の状況

あるプロダクトでは、以下のような単純なSELECT文の実行に約75秒かかっていました。
※キャッシュヒットによっては、9秒かかったり30秒かかったり...

SELECT
    *
FROM
    "sample_table"
WHERE
    "key_column" = 'T202602'
ORDER BY "id" ASC
LIMIT 1000
;

データ量的には、全体としては2300万レコードあり、key_columnで絞り込むと大体50万程のレコードになるような状況です。
上記SELECTはチャンクしつつの取得のため、2回目にLIMIT 1000 OFFSET 1000、3回目にLIMIT 1000 OFFSET 2000とLIMIT以降を変えて実行させています。そのため、key_columnの絞り込みが50万レコードあると、1回の実行に約75秒かかるようなSELECTが500回逐次実行されていました。当該テーブルは毎分ExclusiveLockをかけるようなバッチも動いており、SELECTが遅いことで、DBが高負荷になりアラートが頻繁に発生していました。

そして、以下の通り、key_column自体に単一インデックスはきちんと設定されていました。

インデックスを確認

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'sample_table' ;

インデックス確認結果

name	indexdef
sample_table_pkey	CREATE UNIQUE INDEX sample_table_pkey ON public.sample_table USING btree (id)
sample_table_key_column	CREATE INDEX sample_table_key_column ON public.sample_table USING btree (key_column)
...その他単一インデックスがあるのみで省略

一見、key_columnの単一インデックスがあるため、クエリの実行としては2000万近くあるレコード全体から、key_columnの単一インデックスを利用して範囲を限定した後、ORDER BY "id" ASCによるソートを行うだろうと想定していたため、数秒もかからず実行できそうに見えました。が、結果としては75秒近くかかっています。そして、なぜかソート指定をDESCに変えると1sかからず爆速になるという特徴もありました。

このようによくわからない状況になっている場合は、実行計画を確認することが大事です。

実行計画を確認する

と、いうわけで実行計画を確認してみることにしました。

実行計画を確認する

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
    *
FROM
    "sample_table"
WHERE
    "key_column" = 'T202602'
ORDER BY "id" ASC
LIMIT 1000
;

実行計画の取得結果（※一部マスキング済）

Limit  (cost=0.44..4452.07 rows=1000 width=844) (actual time=75557.531..75582.754 rows=1000 loops=1)			
	  Output: id, key_column, ...		
	Buffers: shared hit=3437153 read=1134446		
	I/O Timings: shared read=69075.243		
	->  Index Scan using sample_table_pkey on public.sample_table  (cost=0.44..1956348.93 rows=439468 width=844) (actual time=75557.530..75582.628 rows=1000 loops=1)		
		Output: id, key_column, ...	
		Filter: ((sample_table.key_column)::text = 'T202602'::text)	
		Rows Removed by Filter: 23019427	
		Buffers: shared hit=3437153 read=1134446	
		I/O Timings: shared read=69075.243	
Query Identifier: 663006251068243900			
Planning:			
Buffers: shared hit=164			
Planning Time: 0.731 ms			
Execution Time: 75582.854 ms

この実行計画の結果から読み取れる内容を見ていきます。

(1) Index Scan using ...

Index Scan using sample_table_pkeyと出ていますが、これはid(PK)のインデックスを利用して、上から順にレコードを探していることを示しています。※Index Scan Backwardだと下から走査していることを示します。

(2) Execution Time

Execution Time: 75582.854 msは全体の実行時間ですが、75秒近くかかっています。

(3) (actual time=XX...ZZ)

実行計画の取得結果の一行目にある(actual time=XX...ZZ)からは、XXの部分は、最初の一行を見つけて返すことが出来た時間で、ZZがすべて返し終わった時間を読み取れます。
今回は、actual time=75557.531..75582.754となっており、最初の一行を見つけて返し終わるまでに約75.557秒かかっており、75.582秒の時に全部（1000件）を返し終わっていることを示しています。つまり最初の一行を見つけるのに75.557秒かかっているが、その後の1000件分を返し終わるのに関しては25.098ms（75582.628 - 75557.530）つまり、約0.025秒くらいであることが読み取れます。
もっと噛み砕いて言うと、初回ヒットまで75秒、取得処理自体は0.025秒といった具合に、最初の1件目を見つけ出すのにものすごい時間がかかっているということです。

(4) Filter

Filter: ((sample_table.key_column)::text = 'T202602'::text)とありますが、idの単一インデックスにより見つかったレコードに対して、Filterの条件に合致するか評価を行っています。
つまり、key_columnの単一インデックスが使われておらず、id順に上から見ていってほぼ全ての行に対してkey_columnの条件に合致するかを見るという非常に非効率なことが行われています。

読み取れたことまとめ

本来意図していた、全体2300万近いレコードから効率的に対象データを見つけるには、key_columnの単一インデックスを利用して探索を行えば早いはずですが、なぜかPK（id）の単一インデックス（sample_table_pkey）を利用して上から順に対象レコードを検索して一行一行key_columnが合致するか見ていくような検索を行っています。その結果、目的のデータは後ろの方に固まっていたため、最初の1件を見つけ出すのに75秒近くかかっているということが分かりました。「なぜプランナはこのような検索方法を行う判断に至ったのか」と、「どう改善すれば良いのか」を、後述する実行速度を改善するには？で見ていきましょう。

DESCの場合の実行計画を確認する

その前に、DESCにした場合1秒かからないことが判明していましたが、DESCの場合の実行計画についても念の為確認してみましょう。対比することで何かヒントを得られるかもしれません。

DESCの場合の実行計画を確認する

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
    *
FROM
    "sample_table"
WHERE
    "key_column" = 'T202602'
ORDER BY "id" DESC
LIMIT 1000
;

実行計画の取得結果

Limit  (cost=0.44..4452.07 rows=1000 width=844) (actual time=114.373..117.777 rows=1000 loops=1)			
	Output: id, key_column, ...		
	Buffers: shared hit=26434 read=19		
	I/O Timings: shared read=2.886		
	->  Index Scan Backward using sample_table_pkey on public.sample_table  (cost=0.44..1956348.93 rows=439468 width=844) (actual time=114.371..117.698 rows=1000 loops=1)		
		Output: id, key_column, ...	
		Filter: ((sample_table.key_column)::text = 'T202602'::text)	
		Rows Removed by Filter: 453420	
		Buffers: shared hit=26434 read=19	
		I/O Timings: shared read=2.886	
Query Identifier: -6401126692934180982			
Planning Time: 0.169 ms			
Execution Time: 117.861 ms

この実行計画の結果から読み取れる内容を見ていきます。

(1) Index Scan Backward using ...

Index Scan Backward using sample_table_pkeyとありますが、75sかかっていた時と同じくidの単一インデックス（sample_table_pkey）を利用してインデックス検索を行っていることが分かります。今回はBackwardのため、下から順に探しているようです。

(2) Execution Time

Execution Time: 117.861 msは全体の実行時間でしたね、今回は0.117秒でありなんと1秒かかっていません。

(3) (actual time=XX...ZZ)

実行計画の取得結果一行目に出ているactual time=114.373..117.777から読み取れる通り、初回ヒットに0.114秒、その後全体を取得して返すのに0.003秒かかっていることが分かります。

(4) Filter

Filter: ((sample_table.key_column)::text = 'T202602'::text)とありますが、75sの時と同じくidの単一インデックスにより見つかったレコードに対して、Filterの条件に合致するか評価を行っています。

読み取れたことまとめ

DESC指定に変えてIndex Scan Backwardが行われ、見つけたいデータがたまたま後ろの方に固まっていたため、最初の1行を見つけ出すのに0.114秒で済み、1秒かからずに目的のデータを取得できたことが分かりました。これは、検索方法がDESCによって大きく変わったということではなく、目的のデータが後ろの方にあったため、Index Scan Backwardの通り後ろから走査するとたまたま見つけるのが早かっただけということが分かります。そしてこれは根本的な解決策がDESCを利用することではないということを示唆しています。

実行速度を改善するには？

では、実行速度を改善させるにはどうしたら良いのでしょうか。
本来の理想としては、key_columnのインデックスを利用して、50万レコードを絞り込んでから、idの昇順にすれば速度的には75秒もかからないはずです。そのため、PostgreSQLのプランナが、key_columnのインデックスを利用してスキャンさせることができればクエリの速度は劇的に改善する見込みが立てられます。

しかしながら、実際はidのインデックスが使われています。こちらのドキュメントでは以下のようなことが記載されています。

インデックスは、その行を指定した順番で取り出すことができます。これにより、別途ソート処理を行うことなく、問い合わせのORDER BY指定に従うことが可能です。...（一部省略）
プランナは、ORDER BY指定を満足させるために、指定に一致し利用可能なインデックスでスキャンするか、または、テーブルを物理的な順番でスキャンし明示的なソートを行うかを考慮します。テーブルの大部分のスキャンが必要な問い合わせでは、後に発生するシーケンシャルなアクセスパターンのために要求されるディスクI/Oが少ないため、インデックスを使用するよりも、明示的なソートの方が高速です。数行を取り出す必要がある場合のみ、インデックスの方が有用になります。
ORDER BYとLIMIT nが組み合わされた場合が、重要かつ特別です。先頭のn行を識別するために、明示的なソートを全データに対して行う必要があります。しかし、もしORDER BYに合うインデックスが存在すれば、残りの部分をスキャンすることなく、先頭のn行の取り出しを直接行うことができます。

とあるように、プランナは

ORDER BY指定を満足させるために、指定に一致し利用可能なインデックスでスキャンする

テーブルを物理的な順番でスキャンし明示的なソートを行う

を判断するようです。
そして、重要な情報は以下になります。

しかし、もしORDER BYに合うインデックスが存在すれば、残りの部分をスキャンすることなく、先頭のn行の取り出しを直接行うことができます。

このことから、key_columnのインデックスで絞り込むよりも、ORDER BY句で指定されているidのインデックスをそのまま使えば、ソート不要でそのまま先頭を見つけて取り出せるプランが良いと判断したように見えます。
結果的に、先頭行を見つけるのにかなり後ろの方まで探索しにいかなければならず、idの単一インデックスによる検索のため、ソートは不要になったが先頭行を見つけ出すためにフルスキャンちっくなことをしなければならず処理に時間がかかってしまっていたということになります。

以上を踏まえると、プランナとして魅力的な選択肢は以下になります。
・ORDER BY句で指定しているidのインデックスを使うとソート不要になるためプランナとしては積極的に使いたい
・WHERE句で指定されているkey_columnによる絞り込みもインデックスがあれば使って絞り込みたい

上記を満たすためには何が必要でしょうか。そうです、複合インデックスの出番です。
例えば、key_columnとidの複合インデックスがあるとどうでしょう。key_columnによりスキャンする範囲を高速に絞り込みつつ、その範囲の中でid順が保証されるため、ソートも不要という魅力的な選択肢をプランナに提供できます。

ただし、複合インデックスを作成する際は少し留意する点がありそうでした。こちらに記載されている情報を抜粋します。

しかし、先頭側の（左側）列に制約がある場合に、このインデックスはもっとも効率的になります。正確な規則は、先頭側の列への等価制約、および、等価制約を持たない先頭列への不等号制約がスキャン対象のインデックス範囲を制限するために使用されます。

とあるように、まずは制約（絞り込みたい範囲）をばしっと決められるものを左側にまず書きましょうということです。今回の事例でいうと、複合インデックスを作成する際に以下のように、まずは範囲がばしっと決まる条件に利用したいカラム（key_column）を左側に書きます。

複合インデックスの作成

CREATE INDEX sample_table_key_column_id
    ON sample_table (key_column, id);

このように作成することで、この複合インデックスが利用される際にkey_columnにより探索する範囲が一気に絞り込まれ、加えてid順が保証されておりソート不要という、プランナが真っ先に採用してくれそうな複合インデックスを準備することができました。

改善結果

実際に複合インデックスの作成後、改善されたのかを見ていきましょう。
まずは、作成した複合インデックスを確認します。

インデックスを確認

SELECT indexname, indexdef FROM pg_indexes WHERE tablename = 'sample_table' ;

インデックス確認結果

name	indexdef
sample_table_pkey	CREATE UNIQUE INDEX sample_table_pkey ON public.sample_table USING btree (id)
sample_table_key_column	CREATE INDEX sample_table_key_column ON public.sample_table USING btree (key_column)
...省略
-- ↓↓↓ 作成された複合インデックス ↓↓↓
sample_table_key_column_id	CREATE INDEX sample_table_key_column_id ON public.sample_table USING btree (key_column, id)

複合インデックスが無事作成されているため、次に実行計画を出してみましょう。

実行計画の取得

EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT
    *
FROM
    "sample_table"
WHERE
    "key_column" = 'T202602'
ORDER BY "id" ASC
LIMIT 1000
;

実行計画の取得結果

Limit  (cost=0.56..1626.03 rows=1000 width=843) (actual time=0.056..1.325 rows=1000 loops=1)
"  Output: id, key_column, ..."
  Buffers: shared hit=1729
  ->  Index Scan using sample_table_key_column_id on public.sample_table  (cost=0.56..683788.62 rows=420671 width=843) (actual time=0.054..1.239 rows=1000 loops=1)
"        Output: id, key_column, ..."
        Index Cond: ((sample_table.key_column)::text = 'T202602'::text)
        Buffers: shared hit=1729
Query Identifier: 663006251068243900
Planning:
  Buffers: shared hit=205
Planning Time: 0.672 ms
Execution Time: 1.425 ms

Execution Time: 1.425 msの通り、全体の実行時間は約0.0014秒と爆速になりました。
また、Index Scan using sample_table_key_column_idの通り、作成された複合インデックスをプランナが利用してくれているのが分かります。
そして、Index Cond: ((sample_table.key_column)::text = 'T202602'::text)の部分ですが、FilterではなくIndex Condの場合、高速なインデックススキャンがkey_columnを利用して行われていることを指し示しています。パフォーマンス改善は無事できたようです。

参考（もっと実行速度を早くさせるには）

本記事で遅かったSELECT文は、複合インデックスの追加により劇的に早くなりました。
しかし、以下前記した通りOFFSETを利用しているのが現状です。

2回目にLIMIT 1000 OFFSET 1000、3回目にLIMIT 1000 OFFSET 2000とLIMIT以降を変えて実行

こちらが非常に参考になるのですが、キーセットページネーションというアプローチに変更することでさらにパフォーマンス改善ができそうなため、参考情報として記載いたします。

最後に

今回得られた教訓は、実行計画をちゃんと見ることの大切さを学びました。仮に実行計画の内容がよくわからない！となったとしても、生成AIを利用して「この結果のこの部分ってどうゆうこと？」「改善したいけど何をすべき？」など聞いてみると良いかもしれません。いずれにしても、実行計画をまずは確認してどのようにSQLが実行されているかを確認することが大事です。最後まで見ていただきありがとうございます。

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up