[pgsql-jp: 35687] たくさんの表を連結するときの速いSQL文を教えてください

"鈴木孝征(Suzuki Takamasa)" takamasa @ thaliana.myhome.cx
2005年 7月 7日 (木) 12:57:47 JST


はじめまして、鈴木孝征と申します。よろしくお願いいたします。

検索のスピードをあげるためのSQL文の工夫について教えてください。

環境
PC: DELL Dimension 4500c
CPU: Pentium4 1.8 GHz
メモリ: 768 MB

OS: Vine Linux 3.1
kernel 2.4.27
Apache 1.3.33
PHP 4.3.11
PostgreSQL 7.4.8
全てVineのパッケージからインストール

ウェブアプリケーションの開発をしていてSQLの構築とユーザーの入力した値の
チェックなどはPHPで行っています。SQLの実行はpg_query関数を使っています。

ユーザーはテーブル(t1からt1400まで)と検索値(p1からp21500まで)を入力します。

テーブル(t1)は二つのカラム(pとv)を持ちレコード数は21500あります。pは
text、vはdouble型の値を持ち、pはuniqueです。同じ設計のテーブルが1400(t1
からt1400)あり今後増える予定です。pの値はp1からp21500までで、全てのテー
ブルで同じです。vの値は0から10万ぐらいの数値です。

create index t1_index on t1 (p);として全てのテーブルのp列のインデックス
を作成しました。

ユーザーにtとpを入力させ、対応するvを抽出できるようにしたいと考えていま
す。tはt1からt1400まであり、最大200個ぐらいを同時に選択します。pはp1から
p21500まであり、最大100個ぐらいを同時に選択します。

この目的を達成するために以下のようなSQLをPHPで構築し、実行させました。
SQL文中の...は省略記号です。

select t1.v as t1, t2.v as t2, ... t100.v as t100
from t1, t2, ... t100
where (t2.p = t1.p and  ... and  t100.p = t1.p) and
      (t1.p = p1 or t1.p = p2 or ... or t1.p = p100)

私の環境ではtが60ぐらいでpを20個選択したときは30秒ほどで終了しましたが、
tを90ぐらいに増やすと4分ぐらいかかり、200を超えると待ちきれなくなりまし
た(かかった時間については記憶があいまいですがtの増加に対し比例以上の時間
がかかったと覚えています)。

そこでselectのスピードを上げるために下記のようにselectを分割しました。

SQL1:	select t1.p as p, t1.v as t1, t2.v as t2, ... , t10.v as t10
	into s1
	from t1, t2, ... , t10
	where (t2.p = t1.p and ... and t10.p = t1.p) and
	      (t1.p = p1 or t1.p = p2 or ... or t1.p = p100)

SQL2:	select t11.p as p, t11.v as t11, t12.v as t12, ... , t20.v as t20
	into s2
	from t11, t12, ... , t20
	where (t12.p = t11.p and ... and t20.p = t11.p) and
	      (t11.p = p1 or t11.p = p2 or ... or t11.p = p100)

SQL3 からSQL10までを順次構築、実行後、次のSQLを実行

select t1, t2, t3 , ... t100
from s1, s2, ... , s10
where s2.p = s1.p and s3.p = s1.p and ... and s10.p = s1.p

すると劇的にスピードがあがりました。そこでtの数によっていくつぐらいに分
割したらいいのかを調べてみました。以下に結果を示します。

tの数	SQLの数	1回のSQLで選択するテーブル数	所要時間(秒)
12	1	12	 1.7
	2	6	 3.8
	3	4	 2.9
	4	3	 3.7
26	3	9	15.0
	6	5	 6.5
	9	3	12.4
52	4	13	 8.0
	5	11	 7.2
	6	9	33
	7	8	19.7
94	5	19	17.0
	6	17	17.8
	7	14	21.7
	8	12	22.0
	9	11	22.0

tの数によりSQLをいくつに分割するのが効果的なのか、わかるかた教えてください。

またもっと効率のいいselect文の使い方があれば教えてください。



pgsql-jp メーリングリストの案内