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

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

新規登録して質問してみよう
ただいま回答率
85.50%
PostgreSQL

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

SQL

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

Q&A

解決済

3回答

6975閲覧

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

IgaDX

総合スコア7

PostgreSQL

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

SQL

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

0グッド

0クリップ

投稿2018/02/27 05:57

編集2018/02/27 10:03

前提・実現したいこと

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

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

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

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

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

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

該当のソースコード

言語:PostgreSQL

ソースコード

lang

1 2create or replace function insert_into_category( 3 Count INTEGER default null 4) 5RETURNS integer AS $$ 6DECLARE 7 --Forを回す回数. 8 loop_max integer; 9 --ForのLoop回数. 10 loop_count 11 INTEGER; 12 --カテゴリー名. 13 category_word character varying(255); 14 rec RECORD; 15 16BEGIN 17 --originalテーブルのカラムの大きさを調べる. 18 select into loop_max count(id) from original; 19 --loop_countの初期化 20 loop_count:= 0; 21 --Countを初期化 22 Count:=null; 23 24 FOR rec IN 0..loop_max LOOP 25 26 --category_wordに1つ目のカテゴリーを入れる。 27 select into category_word 28 (select split_part(category_name,E'/',1):: character varying(255) as category_name) 29 from original 30 where id =loop_count; 31 32 --1つ目のカテゴリーをinsert(同じカラムにすでに同じカテゴリーが存在する場合はinsertしない) 33 insert into 34 category (name) 35 select category_word 36 from original 37 where NOT EXISTS (SELECT name FROM Category WHERE name = category_word) LIMIT 1; 38 39 --categoryテーブルのname_allへoriginalテーブルのnameを上書き 40 update category 41 set name_all = (select name from original where id = loop_count) 42 where 43 EXISTS(select name from category where name = category_word) and name = category_word; 44 45 loop_count:=loop_count+1; 46 47 END LOOP; 48 return 0; 49END; 50$$ LANGUAGE plpgsql; 51 52select insert_into_category(); 53select * from category order by id asc;

試したこと

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

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

<環境>
PostgreSql9.6,pgAdmin3

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

lang

1 2--category 3create table category ( 4 name_all character varying(255) 5 , id serial not null 6 , parent integer 7 , name character varying(255) 8 , constraint category_PKC primary key (id) 9) ; 10 11create unique index category_pki 12 on category(id); 13 14create index parent_id_index 15 on category(parent); 16 17-- original 18create table original ( 19 id integer not null 20 , name character varying(255) 21 , condition_id integer 22 , category_name character varying(255) 23 , brand character varying(255) 24 , price double precision 25 , shipping integer 26 , description text 27 , constraint original_PKC primary key (id) 28) ; 29 30create index brand_index 31 on original(brand); 32 33create unique index original_pki 34 on original(id); 35 36comment on table category is 'category'; 37comment on column category.name_all is 'name_all'; 38comment on column category.id is 'id'; 39comment on column category.parent is 'parent'; 40comment on column category.name is 'name'; 41 42comment on table original is 'original'; 43comment on column original.id is 'id'; 44comment on column original.name is 'name'; 45comment on column original.condition_id is 'condition_id'; 46comment on column original.category_name is 'category_name'; 47comment on column original.brand is 'brand'; 48comment on column original.price is 'price'; 49comment on column original.shipping is 'shipping'; 50comment on column original.description is 'description';

気になる質問をクリップする

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

ベストアンサー

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

SQL

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

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

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

投稿2018/02/27 08:28

編集2018/02/27 08:40
sazi

総合スコア25138

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

IgaDX

2018/02/27 10:10

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

0

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

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

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

投稿2018/02/27 06:01

編集2018/02/27 06:27
退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

IgaDX

2018/02/27 06:26

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

2018/02/27 10:12

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

0

・テーブル1行ごとにinsertの処理をしてしまっているので

速度が遅くなってしまっています。

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

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

投稿2018/02/27 06:12

Orlofsky

総合スコア16415

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

IgaDX

2018/02/27 06:25

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

2018/02/27 08:00

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

2018/02/27 08:06

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

2018/02/27 10:01

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

2018/03/05 12:34

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

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問