[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 メーリングリストの案内