[pgsql-jp: 41208] Re: INSERT INTO XXX SELECTでout of memoryが発生

[y hori]

花田様

返信ありがとうございます。

> INSERT 先のテーブルですが、AFTER INSERT トリガーや参照整合性制約はついて
> いないでしょうか？これらがあると、INSERT した行数分だけトリガイベントを
> 保存していくので、バックエンドのメモリ使用量が増えてしまうようです。
Insert先のテーブルですが、参照整合性制約、トリガー等はついていないです。
テーブルには格納データの範囲指定のためのCHECK制約が1つ
btree INDEXが18個存在しています。

花田さんのアドバイスを元に下記の実験を行いました。

・テーブルAのデータだけを別のテーブルにコピーして主キー、INDEXがない状態で問題のクエリを試してみました。

以前はクエリ実行後すぐにout of memoryになった処理が、エラーにならずに終了しました。
単純に問題のクエリのSelect部分だけをおこkなった時のメモリ使用量と同程度のメモリを使用して終了しました。

・テーブルAと同様の主キーを追加して実験
こちらもエラーにならずに終了しました。メモリ増加も同程度でした。

・主キーがある状態で、CHECK制約を追加して実験
こちらもエラーにならずに終了しました。メモリ増加も同程度でした。

もうすこし徐々に使用しているINDEXを増やしながら実験してみます。

しかし、なぜINDEXが影響するのか分かりません。
単純なINSERTではこのような事がなかった気がします。

どなたかご存知であれば教えていただきたいのですが、
INSERT先テーブルにSELECTしているために起きている現象なのでしょうか？

以上です。宜しくお願い致します。

2012年9月3日 9:51 花田 茂 <hanada ＠ metrosystems.co.jp>:
> 花田です。
>
> (2012/09/03 8:27), y hori wrote:
>> テーブルA : 約2000万件 (5.6GB程)
>> テーブルB : 約200万件  (569MB程)
>>
>> 上記テーブルAにはPrimary key (pkey1,pkey2)がついています。
>> テーブルBは一時的なものなので主キーはつけていませんが、
>> テーブルA同様、データもpkey1,pkey2で一意です。
>
> INSERT 先のテーブルですが、AFTER INSERT トリガーや参照整合性制約はついて
> いないでしょうか？これらがあると、INSERT した行数分だけトリガイベントを
> 保存していくので、バックエンドのメモリ使用量が増えてしまうようです。
>
> 8.3 環境が手元になく 9.1.2 環境での追試しかできませんでしたが、FK がある
> 場合だけバックエンドプロセスのサイズが増える現象が再現できました。
>
> 回避策は「トリガーや制約を外した状態で INSERT する」です。カーソルを使っ
> て一行ずつ INSERT することでも回避できるかも知れません(とても遅くなるか
> もしれませんが)。
>
> --
> 株式会社メトロシステムズ
>   花田 茂
> Mail : hanada ＠ metrosystems.co.jp
>  Tel : 03-5951-1219
>  Fax : 03-5951-2929