速くしたい問い合わせを、複数並べる
注文一覧で使う問い合わせを二つ固定します。Q1は顧客42の最新5件、Q2は顧客42の未完了の最新5件です。さらに状態を更新する仕事もあります。一つのSELECTだけを見て索引を選ぶと、別の問い合わせや書込みの費用を見落とします。既存の索引と実行計画の導入を前提に、どの列の順序がどの要求を助けるかを、同じデータの二つの候補で比較します。
今回の表はid、customer、created、status、payloadを持つ10,000行です。createdは説明用の整数順序で、実時刻のタイムゾーンや同時刻の並べ替えは扱いません。実製品で安定したページ分割が必要なら、同時刻の順序を決める列も設計します。ワークロードの問い合わせ条件、並び、返す列、頻度、更新する列を先に一覧へ書き、計画の一部が速く見えることだけを全体の採用理由にしません。
先頭の等価条件と、その後の順序を追う
候補Aは(customer, created DESC)です。Q1はcustomerを一つに固定した後、createdの順に必要な先頭だけを取りやすくなります。Q2では同じ顧客の行を新しい順へ進みながら、statusの条件へ合わない行を飛ばす場合があります。未完了が少なければ、返す5件より多くの行を見る可能性があります。索引が使われたかだけでなく、どこに条件が適用され何行が除かれたかを読みます。
候補Bは(customer, status, created DESC)です。Q2は最初の二列を等価条件で固定でき、残るcreatedの順を利用しやすい候補です。一方Q1はstatusを固定しないため、各status内のcreated順がそのまま顧客全体のcreated順にはなりません。別途Sortが選ばれる場合があります。「列を一つ足したから全てが良くなる」とは言えず、問い合わせで固定する接頭辞と要求する順序を対応させます。
述語、被覆、部分索引は別の判断
今回の二候補は通常の複合索引ですが、未完了だけを対象にする部分索引も別の候補になります。問い合わせの条件がその述語を満たすと判断できる必要があり、全状態を取るQ1へ同じように効くとは限りません。返す列を含める被覆の工夫も、表へのアクセスを常にゼロにする保証ではありません。可視性の確認など、対象DBの実装条件が関係します。既存の部分索引・Index-only scanの実験へ戻って比較します。
索引を全部作れば選べるから最善、ともしません。保存領域、挿入と更新の仕事、統計と計画の候補が増えます。似た索引を二つ保持する価値があるかは、Q1とQ2の頻度や期限、更新量から判断します。ここでは候補Aを落としてBを作ることで、二候補が互いに選択へ混ざらない比較にします。運用で既存索引を削除する判断は、今回の一時表のDROP INDEXをそのまま本番へ流す操作とは別です。
同じ表で計画と更新を観察する
SQLを使い捨てPostgreSQLのpsqlで実行します。BEGINの中で一時表を作り、一定の規則で10,000行を生成しANALYZEします。まず索引追加前、次にA、最後にBでQ1とQ2のEXPLAIN ANALYZEをJSON形式で記録します。BUFFERSも付け、返した行数と走査の範囲を確認します。ANALYZEの統計や環境で選ばれる計画は変わり得るため、特定のノード名だけを全環境へ固定の合格条件としません。
候補ごとにサイズとstatus更新の計画も出します。Aの後の更新はSAVEPOINTへ戻し、論理データを元へ戻してからBを作ります。ただしロールバックで全ての物理的なページ状態やキャッシュが初期化されるわけではありません。この順序で得た時間を、厳密に独立したベンチマークの順位として扱わないでください。最後にROLLBACKして一時表を含む変更を残しません。既存の本番接続情報は使わない構成です。
\timing on
BEGIN;
CREATE TEMP TABLE orders_lab(id int PRIMARY KEY,customer int,created int,status text,payload text);
INSERT INTO orders_lab SELECT i,i%100,i,CASE WHEN (i/100)%10=0 THEN 'open' ELSE 'done' END,repeat('x',100) FROM generate_series(1,10000) i;
ANALYZE orders_lab;
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) SELECT id FROM orders_lab WHERE customer=42 ORDER BY created DESC LIMIT 5;
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) SELECT id FROM orders_lab WHERE customer=42 AND status='open' ORDER BY created DESC LIMIT 5;
CREATE INDEX candidate_a ON orders_lab(customer,created DESC);
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) SELECT id FROM orders_lab WHERE customer=42 ORDER BY created DESC LIMIT 5;
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) SELECT id FROM orders_lab WHERE customer=42 AND status='open' ORDER BY created DESC LIMIT 5;
SELECT pg_relation_size('candidate_a') AS a_bytes;
SAVEPOINT before_update_a;
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) UPDATE orders_lab SET status='done' WHERE id<=1000;
ROLLBACK TO before_update_a;
DROP INDEX candidate_a;
CREATE INDEX candidate_b ON orders_lab(customer,status,created DESC);
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) SELECT id FROM orders_lab WHERE customer=42 ORDER BY created DESC LIMIT 5;
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) SELECT id FROM orders_lab WHERE customer=42 AND status='open' ORDER BY created DESC LIMIT 5;
SELECT pg_relation_size('candidate_b') AS b_bytes;
EXPLAIN(ANALYZE,BUFFERS,FORMAT JSON) UPDATE orders_lab SET status='done' WHERE id<=1000;
ROLLBACK;
実行記録から比較できたこと
今回のPostgreSQL 18.6では、追加前に二問い合わせとも順次走査とSortを使い、AではIndex Scanが選ばれました。BではQ2がIndex Scanとなる一方、Q1にはBitmapの走査とSortが現れました。先頭のcustomerとstatusをどう固定するかという予測と対応します。この結果はそのデータと環境で選ばれた計画で、全ての顧客や分布の保証ではありません。
SQL全体を五回実行した全出力を保存しています。単発の実行時間へ過剰な意味を持たせず、走査と絞込み、Sort、サイズ、更新のどの部分が変わったかを先に読みます。行数が少ないと索引を使わない方が合理的な場合もあります。計画へ索引を強制した結果だけを成功と呼ばず、最適化器が選んだ計画と、その前提となるデータ量を確認します。性能の改善が必要なら、実際のワークロードに近い入力と独立した反復測定を追加します。
| 条件 | Q1:顧客+新しい順 | Q2:顧客+状態+新しい順 |
|---|---|---|
| 索引なし | Seq Scan+Sort | Seq Scan+Sort |
| 候補A | Index Scan | Index Scan、状態はFilter |
| 候補B | Bitmap系+Sort | Index Scan |
書込みで何が増えるかを見る
statusを索引のキーへ含めれば、statusを変える更新では索引側の状態も考える必要があります。更新後の値が同じ場合やページの空きなど、対象DBが選ぶ経路にも条件があります。「索引一個だから更新費用が一倍増える」と単純に計算しません。EXPLAIN ANALYZEで実行されるUPDATEは実際に表を変更するため、一時表とトランザクションの範囲へ閉じ込めます。読むだけのEXPLAINと混同しないでください。
保存サイズも、行数だけでなくキーの型、分布、空き、構築後の更新に左右されます。今回のpg_relation_sizeは、その時点の候補索引の観測値です。索引ファイルの量と、テーブル全体、キャッシュの常駐量、バックアップの量は同じではありません。運用の容量へつなぐ際には、保持する他の索引、書込みログ、更新後の増加も別に見積もります。一つの読み取りの速さと、長く維持する費用を同じ比較表へ並べます。
頻度と期限を変えて採用を決める
Q1が一時間に一回、Q2が毎秒多数というサービスと、Q1が大半を占めるサービスでは、候補の価値が違います。各問い合わせの回数へ一回の費用を掛ける考え方は比較に役立ちますが、競合やキャッシュが変わると単純な足し算だけでは予測し切れません。まず読む仕事と書く仕事の割合、返す件数、必要な期限を表にし、重要な要求がどの計画へなるかを確認します。
監視では索引が使われた回数だけで削除の可否を決めず、観測期間や重要な低頻度の仕事も考えます。月末の処理や障害復旧時の照会が短い期間へ入っていない可能性があります。今回の実験は二つのSELECTと一つのUPDATEに範囲を絞った設計演習です。製品へ採用するときは、欠けた問い合わせと制約を確認し、変更前後で結果が一致し、遅い方へ変わる仕事も把握できる検証を行います。
読取りの予算に更新も含める
注文履歴の画面で一日に千回の読取りと十回の更新がある場合と、毎秒大量の状態更新がある場合では、同じ索引でも選択が変わります。検索の一回分を速くしても、更新ごとに索引の保守が増えれば、書込みの余裕を使います。頻度と一回あたりの費用を掛け合わせる考え方は出発点ですが、同時実行の競合や待ち時間の分布まで一つの平均値に畳まないようにします。
今回の一万行は列の分布と問合せを固定した比較です。実際の判断では、最近の注文だけが多い顧客、状態が偏る期間、データ量が増えた後も調べます。計画が変わった場合には、索引名の好みで結果を戻すのではなく、推定行数と実行行数、並べ替え、読み出した範囲を見直します。候補を選んだ後の削除判断にも、利用状況と更新負担の両方の記録が必要です。
どちらへSortが残るか答える課題
課題は索引(customer,status,created DESC)で、customerだけを固定してcreatedの降順を求めたとき、索引内の順序だけで必ず要求を満たせるかです。解答は、statusのグループをまたいだcreated順がそのまま保証されないので、必ずとは言えません。statusも等価条件で固定するQ2とは前提が違います。索引名ではなく、固定する接頭辞と、残る順序を説明してください。
追加課題では未完了だけの部分索引を作り、全状態を求めるQ1にも同じように使えるかを問います。解答は、索引にない行も必要なので、その部分索引だけで同じ問い合わせを満たせるとは言えません。最後に、更新のEXPLAIN ANALYZEを本番へそのまま実行してよいかも考えます。この手順は一時表を対象にした実書込みを含みます。結果、計画、容量、更新、実行範囲を一緒に判断できれば到達点です。