[pgsql-jp: 28412] Re: updateの性能を向上

[Tamotsu Ebina]

海老名＠インフォテック です。

Iwao Watanabe wrote:
> こんにちは。
> 
> ----- Original Message ----- 
> From: <fukudami ＠ ntes.nec.co.jp>
> To: <pgsql-jp ＠ ml.postgresql.jp>
> Sent: Friday, December 20, 2002 8:34 PM
> Subject: [pgsql-jp: 28403] updateの性能を向上
> 
> 
> 
>>現状、普通にupdateを使用してDBを更新しているのですが、どうも
>>望んだ性能が出ません。現在約40万件レコード登録してあるテーブル
>>に対し、下記の様な方式でupdateを実行すると、約15分程度かかって
>>しまいます。更新対象のレコード数はそれほど多くは無い(と言っても
>>ばらつきはありますが)ので、可能ならば2〜3分程度の性能にしたいです。
>>更新対象のレコード数は、一度に500〜3000程度です。
> 
> 
> 人が見ないようなデータなら少々遅くても、
> コンピュータがせっせと動いてくれるので
> 文句をいう人は、まずいません。
> 40万件のデータ処理に15分が遅いですか？
> 充分早いではないですか。
> 人が同じことを処理して15分で終わるのでしょうか。
> 
> （最後はちょっと詭弁）
> 


最後は詭弁ですね。
たかだか40万件の処理に15分もかかるようならRDBMSを使う必要はありません。

私のテストプログラム(Java)で 1,000,000 件のインデックス付の
テーブルからランダムに約1000件抽出してUPDATEするのに要した時間を
参考までに示します。

PoatgreSQL7.2.3 ですが

DBサーバ
  CPU 2.4GHz Memory 1GB SCSI 10,000RPM x 3 Raid5
アプリケーションサーバ
  CPU 1.6GHz Memory 512MB IDE 5400RPM
のスペックで
JDBC 接続 100MB Switching HUB
の環境で実行した結果です。


[2002-12-21 17:06:28.990]  SelectUpdateLP01 Start.
pos
[2002-12-21 17:06:29.840]  Select Start
[2002-12-21 17:06:50.367]  SelectUpdateLP01 End.
count=970

20.527秒です。

1)レコード件数確認(プログラム実行前)
tghakodb=> select count(*) from customer_info;
  count
---------
 1000000
(1 row)

2)フラッグ付レコード件数確認(プログラム実行前)

tghakodb=> select count(*) from customer_info where delete_flg='D';
 count
-------
     0
(1 row)

3)フラッグ付レコード件数確認(プログラム実行プログラム実行後)

tghakodb=> select count(*) from customer_info where delete_flg='D';
 count
-------
   970
(1 row)

tghakodb=> select count(*) from customer_info where delete_flg!='D';
 count
--------
 999030
(1 row)

１千万件でも同じような結果だったと思います。

15分かかっているとなるとインデックスが働いてなく、全件検索になっている
可能性が高いですね。

vaccum --analyze --table <テーブル名> データベース名

でバキュームをかけ

explain <実行SQL文>

で確認すれば、索引が働いているか分かります。

キー付
kodb=> explain select * from customer_info where branch_cd='001' and
group_cd='001';
NOTICE:  QUERY PLAN:

Index Scan using pk_customer_info on customer_info  (cost=0.00..102.37
rows=25 width=1097)

EXPLAIN

キー無
kodb=> explain select * from customer_info where group_cd='001';
NOTICE:  QUERY PLAN:

Seq Scan on customer_info  (cost=0.00..61084.00 rows=5000 width=1097)

EXPLAIN

違いはキー部分がそちらはTEXTで私はCHARとNUMERIC
ですね。

参考までにこちらのテストテーブル

CREATE TABLE CUSTOMER_INFO (
 BRANCH_CD CHAR(3)   NOT NULL,
 GROUP_CD CHAR(3)    NOT NULL,
 CUSTOMER_CD CHAR(8) NOT NULL,
 SEQ_NUM NUMERIC(4)  NOT NULL,
 CUSTOMER_NAME_KNJ   VARCHAR(60),
 CUSTOMER_NAME       VARCHAR(30),
 CUSTOMER_TEL_CD     CHAR(1),
 CUSTOMER_TEL_NUM    VARCHAR(16),
 CUSTOMER_POST_NUM   CHAR(7),
 CUSTOMER_PREF_KNJ   VARCHAR(10),
 CUSTOMER_DIST_KNJ   VARCHAR(40),
 CUSTOMER_TOWN_KNJ   VARCHAR(40),
 CUSTOMER_ADDRESS_KNJ VARCHAR(40),
 CUSTOMER_APARTMENT_KNJ VARCHAR(40),
 CUSTOMER_PREF       VARCHAR(10),
 CUSTOMER_DIST       VARCHAR(20),
 CUSTOMER_TOWN       VARCHAR(20),
 CUSTOMER_ADDRESS    VARCHAR(20),
 CUSTOMER_APARTMENT  VARCHAR(20),
 MAIL                VARCHAR(30),
 DELETE_FLG          CHAR(1),
 UPDATE_DATE         CHAR(23),
 INSERT_DATE         CHAR(23)
);

ALTER TABLE CUSTOMER_INFO ADD CONSTRAINT PK_CUSTOMER_INFO
  PRIMARY KEY (BRANCH_CD,GROUP_CD,CUSTOMER_CD,SEQ_NUM);

ORACLEでは
where句を
  WHERE BRANCH_CD=001 AND GROUP_CD=001 AND CUSTOMER_CD=100010011
    AND SEQ_NUM=1;
と書くと、左辺に自動的にto_numeber関数が実行され、結果的に
索引が働かず、全件検索になった経験があります。

このときは、
  WHERE BRANCH_CD='001' AND GROUP_CD='001' AND CUSTOMER_CD='100010011'
    AND SEQ_NUM=1;
と書く亊によりインデクッスが働きました。

DB2では
SQL0401N  演算 "=" のオペランドのデータ・タイプが一致していません。
とエラーになります。

PostgreSQL7.2.3では検索結果は0件になります。

TEXTタイプのケースでは試したことはありません。

TEXTタイプは互換性から私はほとんど使いません。
商用データベースでは桁数が固定ならCHARを可変ならVARCHARを
使用するのが一般的だと思います。

以上参考になれば。