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

[T.Suzuki]

鈴木＠KEGと申します。

プログラムで、カラムを羅列する方法では無くて、
集合問い合わせだけで実装する方法が知りたいという事でしょうか。

> select t1.p, t1.v, t2.v from t1, t2 where t1.p=t2.p and t2.v / t1.v > 2
t1 から t2 に倍を超える開きがあるレコードを、
1レコードで問い合わせたいと解釈しました。

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

pが全ての主キーで、tはトランザクションで増える可能性がある？
情報が少ないので、要求とは違ってる可能性大ですが、
（自分のリハビリに）ざっくりテーブルを作ってみました。

p_table = {p_id, p}
t_table = {t_id, t}
main_table = {p_id, t_id, v}

-----
create table p_table (
    p_id   INT,
    p      TEXT,
    constraint t_pk primary key(p_id)
);
create table t_table (
    t_id   INT,
    t      TEXT,
    constraint t_pk primary key(p_id)
);
create table main_table (
    p_id   INT,
    t_id   INT,
    v      real,
    constraint m_pk primary key(p_id, t_id),
    constraint p_id_fk foreign key (p_id)
        references p_table (p_id),
    constraint t_id_fk foreign key (t_id)
        references t_table (t_id),
);

例えば、t1〜t1400が範囲で指定されるならばbetweenでレコードを限定した
表とjoinすれば、検索条件に合うvが問い合わせ出来ます。

select pt.p, tt.t, mt.v
from main_table as mt
    inner join (select p_id, p from p_table where p_id between 1 and 21500) as pt using (p_id)
    inner join (select t_id, t from t_table where t_id between 1 and 1400) as tt using (t_id);

# 1 and 1400 がプログラムによって、10 and 30 とかになる。

v に計算が必要であれば、問い合わせ結果をPHPで処理するのも有りだと思います。
インデックスも使われるし、抽出レコードの少ないp_tableを先に結合すれば多少早くなるかと。

-----
SQLだけでやるとしたら…こんな感じでしょうか。

select * from main_table;

p_id|t_id|v
1|1|1
1|2|2
1|3|3
1|4|4
2|2|5
2|3|6
2|4|7
3|1|1
3|2|5
3|3|7
3|4|15

select pt.p, tt.t, mt.v, mm.v
from main_table as mt
    inner join (select p_id, p from p_table where p_id between 1 and 21500) as pt using (p_id)
    inner join (select t_id, t from t_table where t_id between 1 and 1400) as tt using (t_id)
    inner join main_table as mm on (mt.p_id = mm.p_id and mt.t_id = (mm.t_id +1));

p |t |t2.v|t1.v
p1|t2|2|1
p1|t3|3|2
p1|t4|4|3
p2|t3|6|5
p2|t4|7|6
p3|t2|5|1
p3|t3|7|5
p3|t4|14|7

select pt.p, tt.t, mt.v, mm.v
from main_table as mt
    inner join (select p_id, p from p_table where p_id between 1 and 21500) as pt using (p_id)
    inner join (select t_id, t from t_table where t_id between 1 and 1400) as tt using (t_id)
    inner join main_table as mm on (mt.p_id = mm.p_id and mt.t_id = (mm.t_id +1))
where mt.v / mm.v > 2;

p |t |t2.v|t1.v
p3|t2|5|1
p3|t4|15|7

# …結合多すぎて遅い？
 -----------------------------------------
      鈴木 徹 (SUZUKI Toru)
      Kenwood Engineering Corporation.
      E-mail:t_suzuki ＠ kenwood-eng.co.jp
 -----------------------------------------