「インデックスを作ったのにSeq Scanが選ばれる」「ステージングでは速いのに本番だけ遅い」。この種の相談でpg_settingsを確認すると、effective_cache_sizeがデフォルトの4GBのままという環境が見つかります。搭載メモリ128GBのサーバでも、未設定ならPostgreSQLは「使えるキャッシュは4GB」という前提でインデックススキャンのコストを計算します。このパラメータはメモリを実際には確保しないため変更のリスクが小さく、再起動も要りません。ところが値を上げても実行計画が変わらない場合があり、そこで調査が止まりがちです。
この記事は要約版です。
sourceやsourcefileを使った上書き設定の特定手順、EXPLAIN (ANALYZE, BUFFERS)でコスト差を比較する判断基準、RDS/Auroraでの設定方法、パラメータ変更のチェックリストは、ブログの完全版にまとめています: effective_cache_sizeの適正値と確認手順
まずデフォルト放置を解消し、搭載メモリの50〜75%を目安に決める
最初の作業は現在値の確認です。settingが524288（unitが8kBなので4GB）でsourceがdefaultなら未設定です。PostgreSQL 9.4以降のデフォルトは4GBで、サーバの搭載メモリを考慮していません。
適正値の目安は、DB専用サーバなら搭載メモリの70〜75%、アプリやバッチが同居するなら50%です。コンテナやcgroupでメモリ制限をかけている場合は、ホストのメモリではなく制限値を基準にしてください。この値はshared_buffersとOSページキャッシュの合計を表す見積りなので、shared_buffersを含んだ数字を指定します。64GBのサーバでshared_buffers = 16GB、effective_cache_size = 48GBとしても二重計上ではありません。
値を上げても計画が変わらない理由は、プランナ内部での使われ方にあります。この値はMackert–Lohmanの近似式で物理読み込みページ数を推定する処理に入り、同じページへの再アクセスがキャッシュヒットする前提でコストを下げます。したがってNested Loopの内側のインデックススキャンのようにloopsが大きいほどコスト差が生じ、loops=1で数行取るだけの単発スキャンではほとんど変化しません。単発スキャンで選択を変えたい場合はrandom_page_costが対象になります。デフォルトの4.0は回転ディスク前提の値で、SSDやEBS gp3では過大です。推定行数と実測が10倍以上ずれているなら、パラメータより先にANALYZEと統計情報の粒度を点検します。
-- 1. 現在値と、その値がどこから来ているか
SELECT name, setting, unit, source, boot_val, context
FROM pg_settings WHERE name = 'effective_cache_size';
完全版で扱っている内容
- 原因の切り分け手順1として、
source・sourcefile・sourceline・pending_restartまで含めた確認クエリと、postgresql.auto.confやpg_db_role_settingによる上書きの特定方法 - 原因の切り分け手順2として、
enable_seqscan = offで比較したときのコスト差の読み方。1.5倍以内ならパラメータ調整、10倍以上なら統計情報という判断基準と、BUFFERSやtrack_io_timingの使い方 - 原因の切り分け手順3として、
free・/proc/meminfo・pg_buffercacheの実測から設定上限を算出する計算式 - 値を上げても変わらない場合の関連パラメータ。
random_page_costの調整手順、pg_statsのcorrelationと拡張統計（CREATE STATISTICS）、work_memや並列スキャンが結合方式を変える条件 - よくある質問として、
ALTER SYSTEMとDISCARD PLANSによる反映手順、RDS/Auroraのパラメータグループでの8kBブロック単位の指定とDBInstanceClassMemoryの式、変更を安全に回すための10項目のチェックリスト