 |
|
| 徹底比較!! Oracle & PostgreSQL
|
第７回：PostgreSQLの適用範囲を考える
〜 更新・削除のパフォーマンス
著者：ミラクル・リナックス 高橋 強 2005/6/20
|
|
|
| 前のページ 1 2 3 次のページ
|
|
| テスト其の参 〜 データの更新
|
まずデータの更新をテストしてみます。インデックスあり・なしそれぞれの状態で、10万件すべてをupdateする時間を比較してみます。一般的なアプリケーションから考えると、全件を一気に操作するといった操作の機会はそれほど多くはないと思われますが、特徴はむしろこういう単純な操作に出やすいものです。
テストでは社員表にある可変長文字列（その他）を更新します。この「その他」列には更新前の状態では70byteほどのダミー文字列が登録されています。これを列長は同じサイズのままで別の文字列へ書き換えます。
|
update 社員表 set その他="ダミーの文字列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万回繰り返し処理します。
|
update 社員表 set その他="ダミーの文字列70byte" where 社員番号=[任意の番号];
|
条件部分の社員番号はランダムに生成し、1から10万までの範囲で作成しており、重複もありえます。
|
図2：10万件のデータから任意の1万件のデータを更新した場合の実行速度
|
結果はインデックスの有無でまったく逆の結果となりました。まずインデックスありの場合では、実行時間は「Oracle ＞ PostgreSQL」でPostgreSQLの倍程度の時間をOracleは必要としました。逆にインデックスがない場合の実行時間は「Oracle ＜ PostgreSQL」となり、PostgreSQLはOracleの倍以上の時間を必要とした結果となっています。
グラフだけを見ると先ほどの結果と矛盾するように見えますが、実行時間の絶対値が大きく異なる事とインデックスの有無がもたらす影響が正反対である点がポイントだと考えられます。全件をupdateする場合、インデックスは行を特定するためには使われず、PostgreSQLでは書き換えの対象を増やすだけの存在でした。それが行指定でのupdateの場合は、インデックスが行を特定するために使用されます。
ここでのインデックス有無の違いは、行を特定する際の処理速度の違いの方がより大きく現れていると考える事ができます。一行単位の更新処理におけるOracleのオーバーヘッドの大きさや、PostgreSQLでのインデックスがない場合の処理負荷の大きさなど、複数の要因が複雑に絡んだ結果を示している訳です。PostgreSQLの素直な動作とOracleのソツのなさが見えて、なかなかおもしろい結果です。
PostgreSQLでは、インデックスがない場合に行を特定する速度がOracleに比べ時間を要する一方、update時にはインデックスが新たな処理負荷を生んでしまうという矛盾に頭を悩まされそうです。インデックスがあった場合の効果は明らかではありますが、不要なインデックス作成を避けて、必要なインデックスだけに絞る努力はOracle以上に必要と言えそうです。
|
前のページ 1 2 3 次のページ
|
|
|
|
|
著者プロフィール
ミラクル・リナックス株式会社 高橋 強
1993年某SIベンダへ入社後、Oracle、DB関連のコンサルティング、チューニング、社内案件の技術支援などを10年ほど担当。2004年ミラクル・リナックス株式会社へ入社。
|
|
|
|
|
|
