20万行の表から1人分の注文を取り出しました。索引がなければ毎回20万行を見に行きます。
索引があれば10.0行、時間は203分の1です。その代わり書く側は索引8個で20.4倍になりました。
見に行く行の数が2万分の1になります。時間の比はそれより小さく、203倍でした。
PostgreSQLで索引がない場合の動きは、ドキュメントに明記されています。
あらかじめの準備がなければ、系は該当するすべての項目を見つけるために、表の全体を1行ずつ走査しなければならないという記述です。
どれだけの差になるかを、20万行の表をその場で作って測りました。
顧客2万人・注文20万行です。ある顧客の注文をすべて取り出す問い合わせを繰り返します。
注文の表 200,000行・顧客 20,000人 ある顧客の注文をすべて取り出す問い合わせを 2000回 やり方 1回あたり見に行った行 合計の時間 1回あたりの時間 全部の行を見る 200000.0行 249ms 0.124ms 索引をたどる 10.0行 1ms 0.001ms
見に行く行は20万行から10.0行になりました。2万分の1です。
時間の比は203倍です。行数の比ほどには開きません。
全部を見る場合、順番に並んだものを端から読むだけです。1行あたりの手間は小さくなります。
索引をたどる場合は、飛び飛びの位置を読みに行きます。1行あたりの手間はこちらのほうが大きくなります。
だから該当する行が多い問い合わせでは、索引を使わないほうが速いことがあります。この計測の10行は少ないほうの例です。
見る行は2万分の1、時間は203分の1。比が違う。
With no advance preparation, the system would have to scan the entire test1 table, row by row, to find all matching entries.原文PostgreSQL 公式ドキュメント「Indexes — Introduction」 この内容の有効期限2027-02-18
読みの速さは索引の数では増えません。書きの重さは索引の数だけ増えます。
PostgreSQLの索引には維持の手間がかかります。ドキュメントは索引が作られたあと、系はそれを表と同期した状態に保たなければならないと述べています。
続けてこれはデータを操作する処理に負荷を加えるとも書いています。その負荷を測りました。
行を 200,000件 足す。索引の数を変えて、かかる時間を見る 索引は「値ごとに行番号を並べたもの」として作っている 索引の数 足すのにかかった時間 1件あたり 索引なしとの比 0個 5ms 0.02µs 1.0倍 1個 13ms 0.06µs 2.7倍 2個 18ms 0.09µs 3.9倍 4個 45ms 0.23µs 9.4倍 8個 97ms 0.49µs 20.4倍
1個で2.7倍です。索引を1つ足しただけで、書きは3倍近くになります。
8個では20.4倍でした。索引の数にほぼ比例して増えます。
読みの側は、索引を増やしても速くなりません。使われるのは条件に合った1つだけです。
つまり使われていない索引は、書きを重くするだけです。読みへの寄与がありません。
見つけ方は単純で、どの索引が使われたかを記録しておいて、期間中に一度も出てこないものを外します。求めた権限のうち使われないものを外す考え方と同じで、その計測はOAuth 2.0の記事にあります。
この計測の8個という数は多すぎに見えますが、外部キーと一意性の制約が自動で索引を作るため、意識せずここまで増えることがあります。
After an index is created, the system has to keep it synchronized with the table.原文PostgreSQL 公式ドキュメント「Indexes — Introduction」 この内容の有効期限2027-02-18
対象を見つける部分は索引で速くなります。同じ行を待つ部分は、担当を増やしても縮みません。
PostgreSQLの索引は、読み取り以外にも効きます。ドキュメントは索引は、検索条件を伴う更新や削除の命令にも役立ちうると述べています。
ただし書き換えには、対象を見つける時間とは別に、順番待ちの時間があります。
処理4000件のうち、一定の割合が同じ行を取り合うとしました。
処理 4000件。1件 2ms。同じ行を取り合う分は順番待ちになる 取り合う割合を変えて、担当を増やしたときの短縮を見る 取り合う割合 1人 2人 4人 8人 16人 16人での短縮 0% 8.00秒 4.00秒 2.00秒 1.00秒 0.50秒 16.0倍 5% 8.00秒 4.20秒 2.30秒 1.35秒 0.88秒 9.1倍 20% 8.00秒 4.80秒 3.20秒 2.40秒 2.00秒 4.0倍 50% 8.00秒 6.00秒 5.00秒 4.50秒 4.25秒 1.9倍
取り合いがなければ16人で16.0倍です。ここは素直に効きます。
5%取り合うだけで9.1倍に落ちます。20%なら4.0倍です。
1番目がよく起きます。合計を持つ行が1つなら、そこは順番にしか進みません。
解き方は分けることです。合計を複数行に分けて持ち、読むときに足し合わせます。取り合う割合そのものを下げる形になります。
実在のデータベースは使わず、行の並びと索引をその場で実装して測っています。実際の系では、値が手元にあるかどうかや、書き込みを記録に残す処理が加わるため、絶対の時間は違います。取り合いの計算も、待ちを式で置いたものです。ここで見せているのは、読みと書きで索引の効く向きが逆になること、取り合いは担当を増やしても消えないという2つの関係です。
Indexes can also benefit UPDATE and DELETE commands with search conditions.原文PostgreSQL 公式ドキュメント「Indexes — Introduction」 この内容の有効期限2027-02-18
同じ課題を持つ会社にとって、動いている設定は「作る時間」を買えるということです。ServiceDockは自作のワークフローやテンプレートを出品できるマーケットプレイスです。手数料や出品の流れは出品者向けページにまとまっています。
出品の仕組みを見る