PostgreSQLがインデックスを使ってくれなくてスロークエリが発生していた件〜 PostgreSQLのコスト計算ロジックを深掘りする〜

こんにちは。@mottei_0702と申します。ハローでは、世界中のレストランを予約できる予約代行サービスAutoReserveのバックエンド開発を行っています。

RDBMSを使って開発しているバックエンドエンジニアの方ならば、 EXPLAIN (ANALYZE) などを活用しながら、クエリのパフォーマンスを常に意識して開発しているのではないでしょうか。私達はPostgreSQLを利用していますが、PostgreSQLのクエリプランナは優秀なため特にこちらで設定を変えなくとも基本的には理想的に動作してくれます。

しかし最近、私は使ってほしいインデックスが利用されないケースに遭遇しました。PostgreSQLのソースコードまで踏み込んで調査してみたところ、PostgreSQLのコスト計算ロジックの一端を知ることができ大変興味深かったので、読者の皆さんに共有したいと思い記事にしました。

クエリプランナが想定と異なる挙動をした時にふとこの記事を思い出していただければ幸いです。これが皆さんのクエリ改善の助けになり、世界中で実行されるクエリがほんの少しだけ速くなる助けになればいいなと願っています。

経緯: 突然のスロークエリ

Cloud SQLにおけるパフォーマンス監視

我々ハローでは、PostgreSQLをGoogle CloudのCloud SQL上で運用しています。バックエンドエンジニアはサービス内でスロークエリが実行されていないかを定期的にチェックするようにしています。Query Insightsではパフォーマンスのネックとなっているクエリが一覧できるので、チェックの際には有効活用しています。

私が開発しているAutoReserveのデータベースの規模としては以下の通りとなっており(2026年2月時点)、サービスの成長に伴って規模は大きくなり続けています。データの増加による急激なパフォーマンスの悪化を検知するためにも定期的なパフォーマンスの確認が重要です。

総データ容量	約 2.5 TiB
ピーク時トランザクション数	約 30,000 / 秒
最大テーブル行数	約 400億レコード

問題のクエリ

ある日の調査で以下のようなクエリが問題になっているとわかりました。本記事ではこのクエリを深掘りしていき、これ以外のクエリは扱いません。restaurant_customers は restaurant_id と email をカラムに持つテーブルです。

SELECT 1 AS one 
FROM "restaurant_customers" 
WHERE LOWER("restaurant_customers"."email") = LOWER($1) 
AND "restaurant_customers"."restaurant_id" = $2    
LIMIT 1;

バックエンドのフレームワークはRuby on Railsを利用していますが、上のSQLはRailsの以下のようなコードから発生していました。

# レコードの作成・更新時に実行されるバリデーションロジック
# 同じrestaurant_idを持つレコードの中でemailが重複しているものがないかを確認する
# case_sensitive: falseで、大文字小文字が違うだけのものも同じemailだとみなしている
validates :email, uniqueness: { case_sensitive: false, scope: :restaurant_id }

スロークエリの原因

このテーブルにはrestaurant_idでのインデックスは貼ってありましたが、emailとの複合インデックスは未設定でした。問題となっているSQLに実際に値を入れてEXPLAINを実行してみると、インデックスが利用されておらずシーケンシャルスキャンが選択されていました。

 Limit  (cost=0.00..41.10 rows=1 width=4)
   ->  Seq Scan on restaurant_customers  (cost=0.00..103079.58 rows=2508 width=4)
         Filter: ((restaurant_id = 1) AND (lower((email)::text) = 'test@example.com'::text))

試しに EXPLAIN ANALYZE してみると、Query Insightsの統計通り時間がかかっていました。一回のバリデーションあたりで200msかかっているようでは話になりません。

 Planning Time: 0.181 ms
 Execution Time: 193.186 ms

解決方法は簡単で、restaurant_idでのインデックスとは別に複合インデックスを新たに貼れば終わりです。今回の場合はemailカラムにlower関数が利用されていたので、関数インデックスを使う必要があります*1。実際の対応としては、業務要件上不要なロジックだったので削除しました。これでめでたしめでたしとしてもいいのですが、調査中に一見不可解な事象に出会ったのでもう少し深掘りして調べてみました。

コスト計算ロジックを深掘りしてみる

結論から先に述べると、データの分布に偏りがありました。

restaurant_id = 1の場合: レコードは非常に多い(数万件-数十万件)
restaurant_id = 2の場合: レコードは少ない(1000件以下)

restaurant_idの値によってレコード数に極端な差があります。このようなデータの偏りは一般的に Data Skew と呼ばれます。

データ量に偏りがあればクエリの結果が変わってくるのもなんとなく理解できます。なんとなくの理解で終わらせるのもモヤモヤするので、PostgreSQLのクエリ実行計画の選択ロジックを深掘りしてみます。今回のケースにおいて、主に以下の3点が実行計画選択のポイントとなっています。

1. ランダムアクセスの高コスト性

仮に今回のケースでrestaurant_idでのindexが利用されたとして、今回のクエリではrestaurant_idで絞り込んだレコードをさらにemailの値で絞り込む処理が発生するため、結局ヒープ(テーブル本体)への読み込みが発生します。インデックス利用時のヒープへのアクセスはランダム(非シーケンシャル)アクセスとなり、シーケンシャルスキャンよりも高コストなスキャンとなります。PostgreSQLのデフォルトの設定では、ランダムアクセスのコストはシーケンシャルアクセスのコストの4倍となっています*2。

restaurant_idでのインデックスを利用したとしても、絞り込んだ対象のレコードが多いとランダムアクセスにコストがかかり結果コストが増大してしまうことが予想されます。ランダムアクセスの対象が少ないと問題とはならないので、これは明確にデータ量が多いことによる弊害といえます。

ここから得られる示唆としては、「たとえインデックスが利用されていたとしても、クエリが十分速いとは限らない」ということです。ヒープへのランダムアクセスを最小限にするようなインデックス設計を心がける必要があります。

インデックスを利用しない絞り込み条件がなければ、ヒープへの読み込みが必要なくなります。emailカラムを含めた複合インデックスを利用すれば、ヒープへのアクセスを伴わない Index Only Scan でクエリを実行できます。

-- 複合インデックスを作成
CREATE INDEX id_email_indexed ON restaurant_customers (restaurant_id, email_indexed);

-- 簡単のためLOWER関数は省略
EXPLAIN                     
SELECT 1 AS one 
FROM "restaurant_customers" 
WHERE "restaurant_customers"."email_indexed" = 'test@example.com'
AND "restaurant_customers"."restaurant_id" = 1
LIMIT 1;

 Limit  (cost=0.43..8.45 rows=1 width=4)
   ->  Index Only Scan using id_email_indexed on restaurant_customers  (cost=0.43..8.45 rows=1 width=4)
         Index Cond: ((restaurant_id = 1) AND (email_indexed = 'test@example.com'::text))

クエリの速度は劇的に改善しています。

Planning Time: 0.090 ms
Execution Time: 0.042 ms

実はIndex Only Scanが利用されるためには他に「どのトランザクションから見てもレコードが最新であること」が保証されている必要がありますが、こちらの調査・解説は本題とは逸れるので割愛しています。気になる方は Visibility Map で検索してみてください*3。Index Only Scanが選択されなくとも、複合インデックスになってランダムアクセスの対象が大幅に減ることによって実行速度は大幅に短縮されるので、今回のケースではほぼ変わらない程度の結果となっています。

「テーブル作成時は高速だったクエリが、レコード増加に伴い徐々に低速化し、やがて看過できない問題になる」このようなケースの多くは、このパターンで説明がつきます。ただインデックスを利用したプランのコストが高くなることがあるとはいえ、シーケンシャルスキャンのコストを超えることはあり得るのでしょうか？次節でより詳しく調査していきましょう。

2. LIMITの指定によるシーケンシャルスキャンコストの過小評価

問題となっているクエリの実行計画を詳しくみてみます。

 Limit  (cost=0.00..41.10 rows=1 width=4)
   ->  Seq Scan on restaurant_customers  (cost=0.00..103079.58 rows=2508 width=4)
         Filter: ((restaurant_id = 1) AND (lower((email)::text) = 'test@example.com'::text))

Seq Scanの行ではコストの最大値はトータルの実行コストは 103079.58 なっていますが、一つ上の行のLimit適用後のトータルのコストは 41.10 とかなり低く見積もっていることがわかります。つまり、Limitによってコストが大幅に短縮されているのがわかります。

PostgreSQLのソースコードを確認したところ、クエリに LIMIT が指定されている場合、独自の比例計算式でコストを算出します*4 *5。

$Cost_{limit} = StartupCost + (TotalCost - StartupCost) \times \frac{LimitRows}{EstimatedRows}$

Startup Cost: 0.00 最初の1行を返すまでにかかるコスト(インデックスの起動コストなど)
TotalCost: 103079.58 全件スキャンするのにかかるコスト
LimitRows: 1 LIMITに指定した値
EstimatedRows: 2508 絞り込み対象のレコードがテーブル内に何件あるかという推定値

このロジックの意図としては「全件走査すると10万以上のコストがかかるが、条件に合致するレコード（EstimatedRows）はテーブル内に2508件もあるのだから、全体の 1/2508 をスキャンすれば必要な行（LimitRows）が見つかるだろう」ということです。

テーブル内に絞り込み条件に該当する2508件のレコードが均等に分散していると仮定した場合、テーブル全てをスキャンしなくとも1/2508の範囲を探索すれば一件ヒットすることが期待できます。つまり、スキャンにかかるコストも1/2508となるということです。コストも 103079.58 / 2508 = 41.10 となっており、計算式と一致します。

約2500件に1件の割合でrestaurant_idとemailが同等のレコードがあるということは、1億件のレコードがあるテーブルだったとしてもrestaurant_idとemailが同じレコードが40000件あるということになり、相当のデータの偏りがあることになります。restaurant_idの偏りはともかく、emailの値がここまで偏ることはさすがに統計情報がおかしいとしか思えません。この謎については次節で解き明かします。

3. lower関数が統計情報を狂わせる

カラムに対してlowerのような関数を噛ませるとクエリの実行速度が遅くなる場合があるということをご存知の方は多いと思いますが、実行速度が遅くなるだけでなく実行計画の選択にも影響を与えるということはご存知でしたでしょうか。

PostgreSQLはテーブル内のレコードの値の分布を統計情報として保存しています*6。クエリプランナは、テーブルごとに作成された統計データをもとにクエリコストを計算します。前の節でクエリプランナが絞り込み対象のレコードを2508件と判断しているのは、テーブルの中身を実際に見ているのではなくこの統計情報を参考にしています。

本来ならば、restaurant_idとemailのカラムの統計情報からレコード数を推定します。ただ今回はemailカラムにlower関数が使われているので、emailカラムについての統計情報を使えません。小文字変換した値の統計情報を持っていないので当然です。この時にPostgreSQLは定数を利用してレコード数を計算します。等号演算では 0.005 という定数を使うので、「0.005の割合つまり200行に1行は lower((email)::text) = 'test@example.com'::text) を満たすレコードがある」と概算します*7 *8。今回はrestaurant_id=1のレコードが約50万件ある環境で検証しているため 500,000 * 0.005 = 2500 となり、レコード数の推定値2508とほぼ一致します。

絞り込み対象のレコードがどれほど多かろうと少なかろうとクエリプランナが提示するコストは同じになるため、多くの場合は実態とかけ離れたものになることが予想できます。

まとめ

インデックスを利用した実行計画であっても十分速くならないことがある。データ量が大きいテーブルに対するクエリでは、ヒープへのアクセスが最小限になっていることが重要。
PostgreSQLはLIMIT句が指定された時にシーケンシャルスキャンのコストを低く見積もるロジックがある。
カラムに対して関数を利用したクエリでは、統計情報を利用できないため、クエリ実行計画におけるレコード数の概算が実態とかけ離れることがある。

教訓

最後まで読んでいただきありがとうございます。

RDBMSを利用している中でもそう頻繁に起きないようなケースの紹介でしたが、クエリプランナの計算ロジックの紹介では普段の開発でも参考になる部分はあるのではないでしょうか。特にData Skewが原因となるパフォーマンス劣化は、マルチテナントで運営しているSaasでかつ、利用する企業(組織)の規模にばらつきがあるようなサービスでは起こりえます。特にスタートアップでは、「最初は小規模の企業にサービスを提供していたが、サービスの成長に伴ってより大きい企業も導入するようになり、データ量が途端に増えた」というケースは対岸の火事ではありません。データベースはサービスのアキレス腱です。サービスが走れなくなる前にしっかりメンテナンスすることが重要です。

PostgreSQLのクエリプランナには日頃お世話になっていましたが、ソースコードを読んでその実態を調査することは初めてで、とても学びになる点が多く純粋に楽しかったです。今回の調査では問題に関連する部分をつまみ食いしたに過ぎませんが、なんとなくの理解で終わらせず引き続き理解を深めていきたいと思いました。