[pgsql-jp: 37724] Re: INDEXを残したまま大量データを高速に挿入したい

[TANIDA Yutaka]

谷田です。

On Tue, 21 Nov 2006 17:02:38 +0900
河本陽一 <komoto.yoichi ＠ kcc.co.jp> wrote:

> 　毎日1回、大量のデータ(1000万件)をひとつのトランザクションで投入し
> ようとしています。PostgreSQLは、7.4を使用しています。
> 　投入速度が遅いため、投入時は最低限のINDEX以外を削除することで性能
> が出るようになりました。
> 　しかし、データ投入の間(時間にして約8時間20分)は、INDEXがないために
> SELECTの性能が出なくなってしまいます。
> 　これでは使用する上で困るので、回避方法を探しています。

現実的な方法はいろいろ出ているようですので、別の怪しい方法をば。基本
的に危険な方法なのでお勧めはしません。まあこういう変わった方法もあるとい
うことで、読み物程度に楽しんでください。

begin;
update pg_class set relhasindex='f' where relname=(該当テーブル名);
データ投入更新
update pg_class set relhasindex='t' where relname=(該当テーブル名);
reindex table (該当テーブル名);
commit;

この方法は、一時的にインデックスを無効にすることで、データ投入速度を高速
化するものです。当然、投入するデータがデータ全体に占める割合が多ければ多
いほど高い効果があります。

こうすると、reindexする間だけAccessExclusiveLockのためアクセスできなくな
りますが、データ更新は高速になります。ただし、以下の点に留意してくださ
い。

-pg_classテーブルをいじりますので、relname以外に条件が必要なことがありま
す。この説明はここでは省略します。
-データ投入更新時に、インデックスを無効にしたテーブルでのインデックスが
必要な場合、他の接続からselect すれば、そちらではインデックスが使えま
す。同じ接続からselectしても、インデックスは使われません。
-更新後の後処理は絶対に忘れないでください。特にreindexをしないと、イン
デックスの整合性に問題が出ます。

ちなみに、Slony-Iが、subscribe setでデータをコピーする時にこの方法を使っ
て高速化を実現してます。


--
TANIDA Yutaka <tanida ＠ sraoss.co.jp>