Seq Scanが減らない時にまず見るpg_settingsとEXPLAIN

インデックスを作ったはずのテーブルでSeq Scanが選ばれ続ける。postgresql.confにeffective_cache_sizeを書いたのに実行計画が変わらない。こうした場面で値の妥当性を議論しても前に進みません。先に「いまどの設定が効いているのか」を確かめ、次に「プランナがどれくらいのコスト差でSeq Scanを選んだのか」を測ります。この順番を飛ばすと、統計情報の問題をパラメータ変更で補正しようとして調査が長引きます。

この記事は「effective_cache_sizeの適正値と確認手順」の一部を、話題を絞って書き直したものです。推奨値の決め方、プランナがキャッシュ量を前提にコストを計算する内部挙動、パラメータ変更を安全に進める手順は、ブログの完全版にまとめています: effective_cache_sizeの適正値と確認手順

手順1 現在値と設定の出どころをpg_settingsで読む

最初に確認するのは値そのものではなく、sourceです。設定ファイルに書いたつもりでも、別の経路の指定が優先されている場合があります。

SELECT name,
       setting, unit,
       current_setting(name) AS as_text,
       source, sourcefile, sourceline,
       boot_val, reset_val, context, pending_restart
FROM pg_settings
WHERE name IN ('effective_cache_size','shared_buffers',
               'random_page_cost','seq_page_cost','work_mem');

sourceがdefaultなら、そのパラメータは未設定です。この状態を直すところから始めてください。configuration fileであれば、sourcefileとsourcelineでどのファイルの何行目が有効なのか特定できます。ALTER SYSTEMが書き込むpostgresql.auto.confがpostgresql.confの記述を上書きしている例は珍しくありません。sourceがsession、user、databaseなら、セッションやALTER ROLE、ALTER DATABASEによる上書きです。

effective_cache_sizeのcontextはuserです。したがってセッション単位のSETでも、設定ファイル編集後のリロードでも反映できます。ロールやデータベース単位の上書きはpg_db_role_settingをpg_databaseとpg_rolesに結合して一覧できます。アプリ接続ユーザーだけ古い値が残っている、コネクションプール経由で別のセッション設定が残っている、といった状態はここで見つかります。

手順2 EXPLAIN (ANALYZE, BUFFERS) でコスト差の大きさを測る

設定が効いていると確認できたら、プランナがどの程度の差でSeq Scanを選んだのかを測ります。差が小さい場合はパラメータ調整で計画が変わりますが、桁違いの差なら原因は別にあります。

-- 現状のプラン
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;

-- Index Scan を強制したときのコストと実測値
SET enable_seqscan = off;
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;
RESET enable_seqscan;

enable_seqscan = offは比較用の計測手段です。本番の恒久対策には使いません。

判断の目安は次のとおりです。Index Scan側のcostがSeq Scan側の1.5倍以内なら、effective_cache_sizeやrandom_page_costの見直しで計画が変わる可能性が高いです。10倍以上の開きがあるときは行数見積りや相関の問題なので、メモリ量の調整より先に統計情報を点検してください。Index Scanを強制してもactual timeが改善しないなら、プランナの判断が正しかったことになります。その場合はインデックス設計かクエリ側を修正します。

BUFFERSのshared hitとreadも合わせて読みます。readが多くても、実際にはOSページキャッシュから返っていることがあります。track_io_timing = onにしてI/O時間を見ると、本当にディスクまで読みに行っているか判断しやすくなります。セッション単位でSET effective_cache_sizeを変え、ANALYZEなしのEXPLAINでコストだけ先に比較する方法も安全です。

手順3 設定できる上限を実測から決める

搭載メモリの50〜75%という目安で決めてよい場面も多い一方、同居プロセスがある環境では実測が必要になります。OSページキャッシュとして使えている量はfree -hのbuff/cacheや/proc/meminfoで確認し、shared_buffersの実使用量はpg_buffercache拡張で調べます。

上限の見当は「搭載メモリ − 他プロセスの常用量 −（work_mem × 想定同時実行数）− maintenance_work_mem」で残る量です。effective_cache_sizeはメモリを確保しないため、この上限を少し超えても障害は起きません。ただし実態より大きく申告すると、プランナがキャッシュに乗っていないインデックスを前提にしたプランを選びやすくなります。work_memを大きく取りすぎてキャッシュ用の余裕が無くなっている構成は、それ自体が遅さの原因です。

元記事で扱っている内容

• デフォルトの4GBのままかを確認し、搭載メモリの50〜75%を目安に見直す具体的な決め方
• 値を変えるとプランナのコスト計算と実行計画がどう変わるのか、内部挙動の解説
• effective_cache_sizeに関するよくある質問への回答
• パラメータ変更を安全に進めるためのチェックリスト
• 次に学ぶべきトピックと参考資料

effective_cache_sizeの適正値と確認手順