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

[武田憲太郎]

武田と申します。

処理する行数の分だけ、insert/updateを発行する形でしょうか？
もしそうだとしたら、私ならそれを以下のように変更します。

-- 準備
create temp table 一時テーブル名 ..... ;
copy 一時テーブル名 from 入力元 ;

-- ここで必要に応じて一時テーブルにインデックスを張る。

begin ;

-- １．存在しない行へのinsert
insert into 処理テーブル名
 select (カラムリスト) from 一時テーブル名
  except
 select (カラムリスト) from 一時テーブル名
  where (pkey制約に引っかかる行を除外するための条件) -- ※下でもう一度使う
;

-- ２．存在する行のupdate
update 処理テーブル名 set カラムカウント=カラムカウント+count
 where
  t.カラム = 処理テーブル名.カラム
from
 (
  select count(*), カラム from 一時テーブル名
   where not ※ group by カラム
 ) as t
-- 「何某」の部分を、count(*)とgroup byで集計する。

commit;

drop table 一時テーブル名 ;

本来であればcreate temp table/drop tableもトランザクションの中に置いたほうが
良いと思うのですが、１と２で獲得されたロックをなるべく早めに開放してあげたい
ために外側に置いています。

insertに関してはSQL一回で済むのでcopyと同等のパフォーマンスを発揮できる(?)、
[pgsql-jp: 37704]の問題はexceptで回避する、updateに関してもSQLは一回だけ、一
旦countで集計してから一気に更新するので内部的な更新回数も最小限で済む、みた
いな考え方です。

細かい条件（元々の入力元に重複が存在するのか、pkeyの条件は何か）などが分らな
いので上の通りにはならないと思いますが、考え方的にいかがでしょうか？

既に試してたらごめんなさい。



-----Original Message-----
From: pgsql-jp-bounces ＠ ml.postgresql.jp
[mailto:pgsql-jp-bounces ＠ ml.postgresql.jp] On Behalf Of 河本陽一
Sent: Tuesday, November 21, 2006 5:03 PM
To: pgsql-jp ＠ ml.postgresql.jp
Subject: [pgsql-jp: 37702] INDEXを残したまま大量データを高速に挿入したい

こうもとです。

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

　データ挿入の処理速度を上げる方法を調べてみましたが、いずれも思うよ
うな動作となりませんでした。
・COPYを使用する
　→既存データがないので使用不可
・INDEXの削除
　→上記問題でSELECTの性能が出ないので見送り
・DBチューニング
　→7.4なので maintenance_work_mem は使用不可
　→checkpoint_segments を60にし、topでIO待ちが3%程度でも性能出ず
　→sort_mem を65536にしても性能でず

　挿入のトランザクションが終わるまではそのデータは見えないので、表示
にしか必要ないINDEXは更新しない方法がないか探してみましたが、見つか
りませんでした。

　INDEXの削除や追加をトランザクションの中に入れてみましたが、こうす
ると削除したINDEXを使用しているテーブルの問い合わせでロックがかかっ
てしまい、トランザクションが終わるまで戻ってきませんでした。

環境
  Redhat Linux ES4 (Kernel 2.6.9-42)
  PostgreSQL 7.4.13

======================================================================
河本陽一(こうもとよういち)
mailto:komoto.yoichi ＠ kcc.co.jp