質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

90.50%

  • SQL

    3017questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • PostgreSQL

    1358questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

【PostgreSQL】:大量のinsert処理を改善して、高速でテーブル間でデータを移したいです。

解決済

回答 3

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 4,589

IgaDX

score 1

 前提・実現したいこと

・originalテーブルにある150万件のデータを、categoryテーブルに
移そうとしているんですが、遅すぎるので改善したいです。

・現在、10件あたりの処理に5秒ほど掛かってしまうので改善したいです。

・PREPAREやCOPY等も調べましたが、重複処理をどうすればいいのか分からなかったです。

 発生している問題・エラーメッセージ

・テーブルからテーブルにデータを移す処理は正常に動いています。

・テーブル1行ごとにinsertの処理をしてしまっているので
速度が遅くなってしまっています。

 該当のソースコード

言語:PostgreSQL

ソースコード

create or replace function insert_into_category(
    Count INTEGER default null
)
RETURNS integer AS $$
DECLARE
    --Forを回す回数.
    loop_max integer;
    --ForのLoop回数.
    loop_count
     INTEGER;
    --カテゴリー名.
    category_word character varying(255);
    rec RECORD;

BEGIN
   --originalテーブルのカラムの大きさを調べる.
   select into loop_max count(id) from original;
   --loop_countの初期化
   loop_count:= 0;
   --Countを初期化  
   Count:=null;

   FOR rec IN 0..loop_max LOOP

   --category_wordに1つ目のカテゴリーを入れる。
   select into category_word
   (select split_part(category_name,E'/',1):: character varying(255) as category_name)
   from original
   where id =loop_count; 

   --1つ目のカテゴリーをinsert(同じカラムにすでに同じカテゴリーが存在する場合はinsertしない)
   insert into
   category (name)
   select category_word
   from original
   where NOT EXISTS (SELECT name FROM Category WHERE name = category_word) LIMIT 1;

   --categoryテーブルのname_allへoriginalテーブルのnameを上書き
   update category
   set name_all = (select name from original where id = loop_count)
   where
   EXISTS(select name from category where name = category_word) and name = category_word;

   loop_count:=loop_count+1;

   END LOOP;
   return 0;
END;
$$ LANGUAGE plpgsql;

select insert_into_category();
select * from category order by id asc;

 試したこと

COPY文を使って1度csvで出力して、csvで読み込もうとしました。
しかし、重複チェックをCOPY文でどうやればいいのか分からなくて止まっています。

 補足情報(FW/ツールのバージョンなど)

<環境>
PostgreSql9.6,pgAdmin3

テーブルのSQL文も記載します。

--category
create table category (
  name_all character varying(255)
  , id serial not null
  , parent integer
  , name character varying(255)
  , constraint category_PKC primary key (id)
) ;

create unique index category_pki
  on category(id);

create index parent_id_index
  on category(parent);

-- original
create table original (
  id integer not null
  , name character varying(255)
  , condition_id integer
  , category_name character varying(255)
  , brand character varying(255)
  , price double precision
  , shipping integer
  , description text
  , constraint original_PKC primary key (id)
) ;

create index brand_index
  on original(brand);

create unique index original_pki
  on original(id);

comment on table category is 'category';
comment on column category.name_all is 'name_all';
comment on column category.id is 'id';
comment on column category.parent is 'parent';
comment on column category.name is 'name';

comment on table original is 'original';
comment on column original.id is 'id';
comment on column original.name is 'name';
comment on column original.condition_id is 'condition_id';
comment on column original.category_name is 'category_name';
comment on column original.brand is 'brand';
comment on column original.price is 'price';
comment on column original.shipping is 'shipping';
comment on column original.description is 'description';
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

+2

「insert_into_category()」で行っている事は、端的には以下のINSERTで済みます。
(よく見るとid順で上書きだったので、順序を修正)

insert into category (name, name_all)
select split_part(category_name,'/',1), (array_agg(name order by id desc))[1]
from original
group by split_part(category_name,'/',1)


※だけど、Name_allという項目名からは、本当は列挙したいのじゃないかと思ったんですけどね。

上記で遅いなら件数の問題だと思いますので、select部分をcsv出力して取り込むとさらに改善されるかと。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/02/27 19:10

    回答ありがとうございます。
    上記のSQLを試したところ、insertの処理も1回で済み、SQLの動作が格段に早くなりました。
    また、array_agg()の関数を使うのは目から鱗でした。
    以上の理由からベストアンサーにさせていただきます。
    本当にありがとうございました。

    キャンセル

+2

実務でやったことがないのですが、
14.4. データベースへのデータ投入
大量のデータを高速に投入するには | Let's Postgres
は参考になりますでしょうか。

許されるなら、インデックス削除してからINSERT文などを実行し、
最後にインデックスを再生成。

GitHub - ossc-db/pg_bulkload: High speed data loading utility for PostgreSQL
pg_bulkloadってのも検討すると良いかも。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/02/27 15:26

    回答ありがとうございます。
    インデックス削除は試していなかったので試してみます。
    pg_bulkloadも知らなかったので教えていただきありがとうございます!

    キャンセル

  • 2018/02/27 19:12

    インデックスを削除して試したところ、動作が10倍ほど早くなりました。
    質問に回答していただき本当にありがとうございました。

    キャンセル

+1

・テーブル1行ごとにinsertの処理をしてしまっているので
速度が遅くなってしまっています。

その通りです。
通常、INSERT ... SELECT でまとめて処理します。

質問のプログラムコードは対応しているMarkdownの機能 の[コードを入力]で修正してください。
CREATE TABLE文も載せると適切なコメントが付き易いです。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2018/02/27 15:25

    回答ありがとうございます。
    INSERT ... SELECTを使って修正してみます。
    Markdownのことも教えていただきありがとうございました。

    キャンセル

  • 2018/02/27 17:00

    create table category に 同じ列 name_all が2つあります。

    キャンセル

  • 2018/02/27 17:06

    コードの見やすさの為に。
    > FOR rec IN 0..loop_max LOOP
    から
    > END LOOP;
    の間は字下げしましょう。

    キャンセル

  • 2018/02/27 19:01

    続けて回答ありがとうございます。
    tableの件、確認し修正しました。
    また、コードの見やすさの点についても意見していただき
    感謝しています。
    今後のコーディングにも活かさせていただきます。

    キャンセル

  • 2018/03/05 21:34

    掲示板を使うマナーとして、
    後でこのツリーを検索した人がわかり易いようにこの質問も直しましょう。

    キャンセル

同じタグがついた質問を見る

  • SQL

    3017questions

    SQL(Structured Query Language)は、リレーショナルデータベース管理システム (RDBMS)のデータベース言語です。大きく分けて、データ定義言語(DDL)、データ操作言語(DML)、データ制御言語(DCL)の3つで構成されており、プログラム上でSQL文を生成して、RDBMSに命令を出し、RDBに必要なデータを格納できます。また、格納したデータを引き出すことも可能です。

  • PostgreSQL

    1358questions

    PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。