テスト其の参 〜 データの更新

テスト其の参 〜 データの更新

   まずデータの更新をテストしてみます。インデックスあり・なしそれぞれの状態で、10万件すべてをupdateする時間を比較してみます。一般的な アプリケーションから考えると、全件を一気に操作するといった操作の機会はそれほど多くはないと思われますが、特徴はむしろこういう単純な操作に出やすい ものです。

   テストでは社員表にある可変長文字列（その他）を更新します。この「その他」列には更新前の状態では70byteほどのダミー文字列が登録されています。これを列長は同じサイズのままで別の文字列へ書き換えます。
 

図1：10万件のデータを全件更新した場合の実行速度

   インデックスありのOracleの結果は、インデックスなしの場合の2割ほど余計に時間を要しました。 インデックスそのものはupdateされる「その他」列には作成しておらず、インデックスのデータ書き換えは起こらないのですが、若干のオーバヘッドを発 生させていると考えられます。処理時間の絶対値は小さいので、その比率に大きな意味は含まれないと考えて良いでしょう。

   対してPostgreSQLの場合は事情が異なります。update処理で行を書き換える場合、元のデータに削除フラグを付けて残したまま、データ を追記する処理構造になっていることは先に説明しました。そのため、updateはdelete＋insertのような処理を必要とします。この操作によ り行が置き換わり、TIDが変わります。インデックスの情報はTIDを指し示しているため、作成したインデックスが対象とした列ではない場合でも、イン デックスの変更が必要になってしまいます。

   パフォーマンスを考えてデータを設計する場合、書き換えの多い表へのインデックス作成は熟考が必要なポイントのひとつですが、PostgreSQL とOracleではその重みに違いがありそうです。ただしPostgreSQLでは、書き換えによる影響がインデックスに及ばないように条件指定で作成さ れる「部分インデックス」を作る事もできます。

   インデックスなしの場合では、このテストで使用した程度の件数ではOracle、PostgreSQLの実行時間はほぼ変わりませんでした。しか し、update対象の表に作成されたインデックスの数が多いほど、PostgreSQLでは極端にパフォーマンスが悪化する傾向があります。

   また、テストでは毎回表をdrop、createしてテストしましたが、試しに同じ表に対してのupdateを繰り返した場合、Oracleでは実 行速度に差は見られませんでしたが、PostgreSQLでは実行速度が徐々に遅くなっていきました。VACUUMを実行せずに処理を繰り返すことによ り、表が徐々に肥大化し、それにつれ実行速度が大きくなってしまいます。

条件付きの更新テスト

   さて、次に更新処理でもう少し現実的なテストを行ってみます。対象表は同じで元のデータ数も 10万件で変わりありませんが、updateする対象をランダムに作成した社員番号1万件による、条件付きのupdateを繰り返すテストです。具体的に は、下記のようなSQL文を1万回繰り返し処理します。
 

   条件部分の社員番号はランダムに生成し、1から10万までの範囲で作成しており、重複もありえます。

図2：10万件のデータから任意の1万件のデータを更新した場合の実行速度

   結果はインデックスの有無でまったく逆の結果となりました。まずインデックスありの場合では、実行時間 は「Oracle ＞ PostgreSQL」でPostgreSQLの倍程度の時間をOracleは必要としました。逆にインデックスがない場合の実行時間は「Oracle ＜ PostgreSQL」となり、PostgreSQLはOracleの倍以上の時間を必要とした結果となっています。

   グラフだけを見ると先ほどの結果と矛盾するように見えますが、実行時間の絶対値が大きく異なる事とインデックスの有無がもたらす影響が正反対である 点がポイントだと考えられます。全件をupdateする場合、インデックスは行を特定するためには使われず、PostgreSQLでは書き換えの対象を増 やすだけの存在でした。それが行指定でのupdateの場合は、インデックスが行を特定するために使用されます。

   ここでのインデックス有無の違いは、行を特定する際の処理速度の違いの方がより大きく現れていると考える事ができます。一行単位の更新処理における Oracleのオーバーヘッドの大きさや、PostgreSQLでのインデックスがない場合の処理負荷の大きさなど、複数の要因が複雑に絡んだ結果を示し ている訳です。PostgreSQLの素直な動作とOracleのソツのなさが見えて、なかなかおもしろい結果です。

   PostgreSQLでは、インデックスがない場合に行を特定する速度がOracleに比べ時間を要する一方、update時にはインデックスが新 たな処理負荷を生んでしまうという矛盾に頭を悩まされそうです。インデックスがあった場合の効果は明らかではありますが、不要なインデックス作成を避け て、必要なインデックスだけに絞る努力はOracle以上に必要と言えそうです。