effective_cache_sizeの適正値と確認手順

「インデックスを張ったのにSeq Scanが選ばれる」「ステージング環境では速いのに本番だけ遅い」。この種の相談でpg_settingsを覗くと、effective_cache_sizeがデフォルトの4GBのままというケースは珍しくありません。搭載メモリ128GBのサーバでも、設定しなければPostgreSQLは「使えるキャッシュは4GB」という前提でコストを計算します。

このパラメータはメモリを実際に確保しないため変更のリスクが小さく、再起動も不要です。ただし「上げたのに実行計画が変わらない」ことも多く、そこで手が止まりがちです。現在値の確認から適正値の決め方、変わらないときに次に見るべき箇所までを手順の形で整理します。

結論：まずは「デフォルト4GBのまま」かを確認し、搭載メモリの50〜75%を目安に見直す

最初にやることは2つだけです。

-- 1. 現在値と、その値がどこから来ているか
SELECT name, setting, unit, source, boot_val, context
FROM pg_settings WHERE name = 'effective_cache_size';

settingが524288（unitが8kBなので4GB）でsourceがdefaultなら、未設定の状態です。PostgreSQL 9.4以降のデフォルトは4GBで、サーバの搭載メモリを一切考慮していません。

目安は次のとおりです。

条件	設定値の目安
DB専用サーバ（他のプロセスがほぼいない）	搭載メモリの70〜75%
アプリやバッチが同居する	搭載メモリの50%
同一ホストに複数インスタンス	ホストメモリ ÷ インスタンス数 を基準に50〜75%
コンテナ/cgroupでメモリ制限あり	ホストのメモリではなく制限値を基準にする

# postgresql.conf（例：搭載メモリ 64GB、shared_buffers 16GB の専用サーバ）
shared_buffers = 16GB
effective_cache_size = 48GB

前提として、effective_cache_sizeは「1つのクエリが実行時に期待できるキャッシュの総量（shared_buffers + OSページキャッシュ）」の見積り値です。shared_buffersを含んだ数字を指定してください。上の例で16GB + 48GB = 64GBとなっても二重計上ではなく、意図した設定です。

設定変更はpg_reload_conf()で反映できます。再起動は不要です。

原因の切り分け手順（確認しやすい順）

手順1：現在値と設定の出どころ（source）を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の記述を上書きしている事故はよくあります。sessionやuser、databaseは、セッションやALTER ROLE/ALTER DATABASEでの上書きです。effective_cache_sizeのcontextはuserなので、セッション単位のSETもリロードでの反映も可能です。

ロール/データベース単位の上書きは次で確認します。

SELECT d.datname, r.rolname, s.setconfig
FROM pg_db_role_setting s
LEFT JOIN pg_database d ON d.oid = s.setdatabase
LEFT JOIN pg_roles r ON r.oid = s.setrole;

アプリ接続ユーザーだけ古い値が効いている、プール経由で別のセッション設定が残っている、といったケースはここで見つかります。

手順2：EXPLAIN (ANALYZE, BUFFERS) でIndex ScanとSeq Scanのコスト差を比べる

次に、プランナが「どれくらいの差で」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倍以上なら行数見積りや相関（correlation）の問題で、手順3より先に統計情報を疑います。Index Scanを強制しても実測のactual timeが改善しないなら、プランナの判断が正しいのでインデックス設計やクエリ側を直してください。

BUFFERSのshared hitとreadも確認します。readが多くても実際にはOSページキャッシュから返っている場合があるため、track_io_timing = onにしてI/O時間を見ると、本当にディスクに行っているかを判断しやすくなります。

セッション単位で値を変えて、計画が変わるかを安全に試せます。

SET effective_cache_size = '48GB';
EXPLAIN SELECT ... ;   -- ANALYZE 無しでコストだけ先に比較

推定行数と実測行数のズレの読み方はPostgreSQL EXPLAIN ANALYZEの見方に整理してあります。手順2で10倍以上の差が出たときは、そちらの観点で先に統計情報を点検してください。

手順3：shared_buffersとOSページキャッシュの実測値から設定できる上限を決める

目安の50〜75%で決め打ちしてよい場面も多いですが、同居プロセスがある環境では実測から上限を決めます。

# buff/cache の値が、おおよそOSページキャッシュとして使えている量
free -h
# 内訳を見る（Cached / Dirty など）
grep -E 'MemTotal|MemAvailable|^Cached|Dirty' /proc/meminfo

shared_buffersの実使用量はpg_buffercacheで確認できます。

CREATE EXTENSION IF NOT EXISTS pg_buffercache;
SELECT pg_size_pretty(count(*) * 8192) AS buffers_in_use
FROM pg_buffercache WHERE relfilenode IS NOT NULL;

「搭載メモリ − 他プロセスの常用量 −（work_mem × 想定同時実行数）−maintenance_work_mem」で残る量が、おおよそキャッシュに使える上限です。effective_cache_size自体はメモリを取らないので、この上限を少し超えても障害にはなりません。ただし実態より大きく申告するとプランナが楽観的になり、キャッシュに乗っていないインデックスを前提にしたプランを選びやすくなります。

なお、work_memを大きく見積もりすぎてキャッシュ用の余裕が無くなっている構成は、それ自体が遅さの原因です。VACUUMやロックまで含めた全体の切り分けはPostgreSQLが遅い・詰まる原因｜VACUUMと実行計画、ロックを参照してください。

なぜ値を変えると実行計画が変わるのか（プランナの内部挙動）

effective_cache_sizeはメモリを確保しない「見積り用」のパラメータ

shared_buffersやwork_memはメモリを実際に使いますが、effective_cache_sizeは確保しません。プランナがインデックススキャンのコストを見積もるときの前提値です。

内部的には、インデックススキャンで必要となる物理読み込みページ数を推定する処理（Mackert–Lohmanの近似式）で使われます。「このクエリが触るページ数に対して、利用可能なキャッシュがeffective_cache_sizeある」と仮定し、同じページへの再アクセスはキャッシュヒットする＝物理読み込みは発生しないものとしてコストを下げる仕組みです。

ここから挙動の特徴が2つ導けます。1つは、繰り返しアクセスがあるほど差が出る点です。Nested Loopの内側のインデックススキャン（loopsが大きい）や、相関サブクエリのように同じインデックスを何度もたどるケースで効きます。もう1つは、単発スキャンにはほとんど影響しない点です。loops=1でインデックスを1本たどって数行取るだけのプランでは推定ページ数自体が小さいため、値を4GBから48GBに変えてもコストはほとんど動きません。

「上げたのに変わらない」という現象の正体は、この2点です。値を上げても結果が間違うことはなく、変わるのはプラン選択だけなので、まず適正値にしてから次の手を打つ順番で問題ありません。

値を上げてもIndex Scanにならない場合に見直す関連パラメータ（random_page_cost・統計情報・work_mem）

単発のインデックススキャンで効くのはrandom_page_costです。デフォルトの4.0は、ランダムI/Oがシーケンシャルの4倍遅い回転ディスクを前提にしています。SSDやEBS gp3のような構成では過大なので、1.1〜2.0あたりを検討します。

-- セッションで試してから恒久化する
SET random_page_cost = 1.1;
EXPLAIN SELECT ... ;

seq_page_cost（デフォルト1.0）との比率が実質的な意味を持ちます。両方を同時に動かすと切り分けが難しくなるため、random_page_costだけを動かすのが無難です。

統計情報も確認します。推定行数が実測と10倍以上乖離している場合、パラメータをいくら触っても正しいプランにはなりません。

ANALYZE VERBOSE schema.table;

-- 列ごとの統計を確認（correlation が0に近いとインデックススキャンのコストが上がる）
SELECT attname, n_distinct, correlation, most_common_vals
FROM pg_stats WHERE tablename = 'orders';

-- 件数の多いテーブルは統計の粒度を上げる
ALTER TABLE orders ALTER COLUMN customer_id SET STATISTICS 500;
ANALYZE orders;

correlationは物理的な並び順と値の並び順の相関です。これが低いテーブルは、同じ件数を取るのにランダムI/Oが増えるため、プランナが正しくSeq Scanを選びます。その場合はCLUSTERや、必要列をインデックスに含めてIndex Only Scanにする方が筋の良い対処です。

複数列の条件で見積りが崩れている場合は拡張統計を使います。

CREATE STATISTICS stx_orders_dep (dependencies, ndistinct)
  ON prefecture, city FROM orders;
ANALYZE orders;

最後にwork_memと並列スキャンです。work_memが大きいとHash Joinのコストが下がり、「両テーブルをSeq Scanしてハッシュ結合」が選ばれやすくなります。Index Scan + Nested Loopを期待しているのに結合方式ごと別のプランになっている場合は、work_memやhash_mem_multiplierも比較対象に入れてください。max_parallel_workers_per_gatherやmin_parallel_table_scan_sizeの設定次第でParallel Seq Scanが勝つこともあります。EXPLAINの出力にGatherやParallel Seq Scanが出ているかで判別できます。

よくある質問

effective_cache_sizeの変更に再起動は必要？

不要です。pg_settings.contextがuserなので、以下のいずれでも反映できます。

-- セッション限定（検証向け）
SET effective_cache_size = '48GB';

-- 恒久化（postgresql.auto.conf に書かれる）
ALTER SYSTEM SET effective_cache_size = '48GB';
SELECT pg_reload_conf();

# postgresql.conf を直接編集した場合
pg_ctl reload -D /var/lib/pgsql/data
# または systemctl reload postgresql-16

注意点が1つあります。リロード後も、すでにキャッシュされた実行計画（PREPAREした文やPL/pgSQL関数内のクエリ）はすぐには再計画されません。効果を確認するときは接続を張り直すか、DISCARD PLANS;を実行してください。

shared_buffersとの合計が搭載メモリを超えても問題ない？

effective_cache_sizeはメモリを確保しないため、数値上は超えても動作に支障はありません。そもそもshared_buffersを含んだ総キャッシュ量を表す値なので、合計して搭載メモリと比較する考え方自体がずれています。

ただし、実態と乖離した大きな値はプランナを楽観的にします。1台に複数インスタンスを立てている場合や、コンテナでメモリ制限をかけている場合は、自インスタンスが使える量を基準にしてください。

RDS / Auroraでも変更できる？

できます。DBパラメータグループで変更し、effective_cache_sizeは適用タイプが動的（dynamic）なので再起動は不要です（shared_buffersは静的で再起動が必要）。

パラメータグループでの値の単位は8kBブロック数です。48GBにしたい場合は6291456のような指定になります。デフォルト値は{DBInstanceClassMemory/16384}のような式で定義されており、式のまま使えばインスタンスクラス変更に自動追従します。固定値に書き換えるかどうかは意識して決めてください。rds_superuserでもセッション単位のSETは可能なので、パラメータグループを触る前にSETでプランが変わるかを検証できます。

Auroraはshared_buffersのデフォルト比率がRDSと異なり、OSページキャッシュに回る量も変わります。Auroraではshared_buffersの実値を見てからeffective_cache_sizeを決める方が実態に合います。構成差の詳細はAurora/RDS PostgreSQLの違いと性能差の原因にまとめました。デフォルト式や制限値はエンジンバージョンによって変わるため、必ず公式ドキュメントで確認してください。

設定を大きくしても実行計画が変わらないのはなぜ？

チェックする順番は次のとおりです。

1. pg_settingsのsourceとcurrent_setting()を再確認する。postgresql.auto.confやALTER ROLEによる上書きがないか
2. loops=1の単発スキャンでは影響がほぼ出ない。random_page_costを検討する
3. EXPLAIN ANALYZEのrows推定と実測の比を見る。乖離が大きければANALYZEと統計の粒度が先
4. 実行計画がキャッシュされていないか。再接続またはDISCARD PLANS
5. 結合方式ごと別のプランになっていないか。work_memや並列実行の設定を疑う
6. テーブルの大半を読む条件や、相関が低いテーブルではSeq Scanが最適

再発防止：パラメータ変更を安全に回すためのチェックリスト

• 変更前にEXPLAIN (ANALYZE, BUFFERS)の出力をファイルに保存した（事後比較の唯一の根拠になる）
• 1回の変更で動かすパラメータは1つに絞った
• まずセッションのSETで検証し、効果を確認してから恒久化した
• postgresql.confとpostgresql.auto.confのどちらで管理するかを決め、チームで統一した
• 反映後にpg_settingsのsource・sourcefileで、意図した場所の値が効いていることを確認した
• pending_restart = trueのパラメータが残っていないか確認した
• pg_stat_statementsのmean_exec_time・callsを変更前後で比較し、他のクエリが悪化していないか見た
• log_min_duration_statementやauto_explainで、プラン変化による回帰を検知できる状態にした
• インスタンスタイプ変更・メモリ増設の手順書に、shared_buffersとeffective_cache_sizeの見直しを項目として入れた
• 巨大テーブルの自動ANALYZE設定と統計ターゲットを点検した（パラメータ調整より統計の方が効く場面が多い）

effective_cache_sizeは、プランナに正しい前提を与えるための基礎設定です。まずデフォルト放置を解消し、それでも計画が変わらないなら統計情報とrandom_page_costに進む。この順番で切り分けると、無駄なパラメータいじりを減らせます。

さらに学ぶには