表の一部だけを索引へ入れる
部分索引は、述語を満たす行だけを索引へ登録します。この実験ではstatusがopenの行だけを対象にし、closedの大部分を索引へ入れません。索引が小さくなる可能性はありますが、全ての問い合わせへ使える索引ではありません。問い合わせが返し得る行を索引が漏れなく含むと、計画時に判断できる必要があります。以下はPostgreSQL18で調べるための手順です。実測結果を示したものではなく、独立した使い捨てローカルDBで実行し、得られた計画を条件と一緒に記録します。
使える条件と有利な条件は違う
statusがopenであることを明示した問い合わせは、索引の述語を満たす候補になります。closedを求める問い合わせは、その索引には目的の行がないため、同じ意味では使えません。一方、openの問い合わせでも、対象行が多い、表が小さい、他の索引が有利といった理由で別の計画を選ぶ場合があります。索引を使える論理的な条件と、費用見積もりで選ばれる条件を分けます。「使われなかった」という一つの現象だけで、述語の書き方に誤りがあると即断しないようにします。
同じ表で三つの計画を比べる
一時表に10万行を作り、100行に一行だけopenとします。統計を取った後、openの定数条件、closedの定数条件、パラメータを使う問い合わせの計画を比べます。パラメータ版では比較を明確にするため、セッション内だけforce_generic_planを指定します。これは本番で推奨する設定ではなく、全てのパラメータ値に使える計画という条件を作る実験操作です。実験の最後にPREPAREした文を破棄し、変更した設定を戻します。EXPLAINだけの箇所では問い合わせ自体を実行していない点にも注意してください。
-- PostgreSQL 18, disposable local database, one session.
CREATE TEMP TABLE lab_partial(id integer, status text, amount integer);
INSERT INTO lab_partial
SELECT i, CASE WHEN i%100=0 THEN 'open' ELSE 'closed' END, i%50
FROM generate_series(1, 100000) AS i;
CREATE INDEX lab_partial_open ON lab_partial(id) WHERE status='open';
ANALYZE lab_partial;
EXPLAIN SELECT id FROM lab_partial WHERE status='open' AND id=50000;
EXPLAIN SELECT id FROM lab_partial WHERE status='closed' AND id=50001;
SET plan_cache_mode = force_generic_plan;
PREPARE lab_partial_query(text, integer) AS
SELECT id FROM lab_partial WHERE status=$1 AND id=$2;
EXPLAIN EXECUTE lab_partial_query('open', 50000);
DEALLOCATE lab_partial_query;
RESET plan_cache_mode;
パラメータがopenでも同じとは限らない
実行時にopenを渡すことを利用者が知っていても、汎用計画を作る時点では$1がopenとは限りません。そのため、全ての$1へ成り立たない述語を、その計画の前提にはできません。実行ごとの値で作るカスタム計画とは事情が異なります。パラメータ化そのものを危険として文字列連結へ戻すのではなく、固定された業務条件をSQLに明示できるか、別の索引が適するか、実際の計画選択が何かを調べます。安全な値の渡し方と、部分索引が使える条件を混同しないことが大切です。
書き方の違いを無制限に推論しない
人間に同じ条件に見えても、計画器が任意の論理式の同値性を証明するわけではありません。簡単な不等式の含意などに対応していても、関数、型変換、複雑なORを含む式へ一般化しないようにします。まず索引の定義を取得し、問い合わせの実際の述語、型、パラメータ化の有無を並べます。そのうえで計画を取り、対象の行集合が一致するかも確認します。計画が変わるよう式を変形するときは、NULLや三値論理の意味を変えてしまわないかを別途検査します。
特殊な索引を維持する費用も確認する
openの行が増えたり、頻繁にopenとclosedを往復したりすると、索引の大きさや更新の費用が変わります。最初の一回の計画だけで恒久的な選択を決めず、データ分布と運用する問い合わせを見直します。この実験では割合を固定していますが、それは挙動を理解するための条件です。全ての表で一パーセントなら同じ計画になるという境界ではありません。比較後は一時表のセッションを閉じ、索引を選ぶ理由、対象外の問い合わせ、設定変更の有無を記録として残します。