データベースの質問です。

テーブルA,テーブルBがあります。以下の処理を実行させたいのですが、処理速度を早くする良い方法を教えて下さい。
(データベースはpostgreSQL,開発言語はPHP)

1.テーブルAからA.hogeにある値を問い合わせしてA.idを取得します。
2.テーブルBからB.hoge_idに1で取得したA.idを問い合わせしてB.idを取得します。

■テーブルA(レコード件数:約3万件)
列1:id
列2:hoge

■テーブルB(レコード件数:約20万件)
列1:id
列2:hoge_id

□試したこと
・select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;
・select B.id from B where B.hoge_id IN (select A.id from A where A.hoge='abc');
 上記2点は問い合わせが終了するまでに2秒以上かかります。
・select B.id from B where B.hoge_id IN ('123','456','789');
 A.idを自分で適当に選んで(例えば'123','456','789')実行した場合は、問い合わせが終了するまでに1秒以下でした。
 (1秒以下であれば問題ありません。)

できるだけ1つのSQL文で処理速度を上げたいのでその方法を教えて下さい。
もし、PHPを利用してSQL文を2つに分けて実行させた方が良いのでしたらその方法を教えて下さい。

回答の条件
  • 1人2回まで
  • 登録:2006/08/23 20:53:51
  • 終了:2006/08/24 11:45:06

ベストアンサー

id:kurukuru-neko No.3

kurukuru-neko回答回数1844ベストアンサー獲得回数1552006/08/24 02:09:37

ポイント20pt

前のデータは破棄してください[忘れてください]

索引に間違いなので

以下で試してみると

A.id : PRIMARY

A.hoge : UNIQUE

(B.id,B.hoge_id) : PRIMARY

で作成しみると。

create unique index a_idx_id on a using btree ( id );

create unique index a_idx_hoge on a using btree ( hoge );

create unique index b_idx_id_hoge_id on b using btree ( id,hoge_id );



select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

55 ms

select B.id from B where B.hoge_id IN (select A.id from A where A.hoge='abc');

55 ms

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

1735 ms

reindex table a;

reindex table b;

vacuum full a;

vacuum full b;

後 explan でみたとき

Seq Scan on b でrowsが全件になっていませんか?

なっている場合遅くて当然なので

indexの見直しが必要と思います。

以下の索引ためしにつけてみてください。

create index b_idx_hoge_id on b using btree ( hoge_id );

追加すると

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

0.15 ms

select B.id from B where B.hoge_id IN (select A.id from A where A.hoge='abc');

0.15 ms

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

1700 ms

※:select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;は1件の場合

===================================================

ためしに200000件のデータを一致させた場合

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

runtime: 1956ms

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

runtime: 1775ms

>約3000レコードがヒットします

このへんデータ構造が原因ですね。

id:ktoshi

回答ありがとうございます。

試してみます。

>Seq Scan on b でrowsが全件になっていませんか?

なっています。

2006/08/24 07:42:02

その他の回答(3件)

id:iketerummo No.1

iketerummo回答回数68ベストアンサー獲得回数42006/08/23 22:42:29

ポイント10pt

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

でどうでしょうか?

試したことの最初のSQL文が最速ではないかと思っていました。

推測でidが主キーとしてA.hogeに時間がかかっているとしたら

A.hogeに有効なインデックスを付けると改善されます。

select * from A where A.hoge='abc';

↑だけだとどれくらいの応答速度ですか?

id:ktoshi

回答ありがとうございます。

select * from A where A.hoge='abc';

は、10ミリ秒くらいです。

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

は、明日の朝に試してみます。

2006/08/24 00:19:05
id:kurukuru-neko No.2

kurukuru-neko回答回数1844ベストアンサー獲得回数1552006/08/24 00:12:34

ポイント20pt

テーブルに索引を以下のような索引を作成してみて

動作を確認してみてください。

create index a_idx_hoge on a using btree ( hoge );

create unique index b_idex_id on b using btree ( id );

使っている環境が違うので比較になるはどうかは

わかりませんが Indexを付けるだけで

1000倍以上の速度差があります。

適切な索引を指定していますか?

一意なキーにはUNIQUEを指定する。

テーブル作成時にCREATE TABLEでPRIMARY KEY/UNIQUE等の

制約を付ける方方法もある。

======= INDEXによる性能差 =============

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

0.12 ms  Index有

963 ms Index無

・select B.id from B where B.hoge_id IN (select A.id from A where A.hoge='abc');

0.15ms Index有

992 ms Index無

※:explain analyzeでの測定なのでCPU/RAMにより差はあ

  りますが相対速度はわかると思います。

id:ktoshi

ありがとうございます。

インデックスは作成しています。(が、念のため明日もう一度確認してみます。)

制約は以下のようになっています。

A.id : PRIMARY

A.hoge : UNIQUE

(B.id,B.hoge_id) : PRIMARY

ちなみに

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

とすると、約3000レコードがヒットします。

2006/08/24 00:45:04
id:kurukuru-neko No.3

kurukuru-neko回答回数1844ベストアンサー獲得回数1552006/08/24 02:09:37ここでベストアンサー

ポイント20pt

前のデータは破棄してください[忘れてください]

索引に間違いなので

以下で試してみると

A.id : PRIMARY

A.hoge : UNIQUE

(B.id,B.hoge_id) : PRIMARY

で作成しみると。

create unique index a_idx_id on a using btree ( id );

create unique index a_idx_hoge on a using btree ( hoge );

create unique index b_idx_id_hoge_id on b using btree ( id,hoge_id );



select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

55 ms

select B.id from B where B.hoge_id IN (select A.id from A where A.hoge='abc');

55 ms

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

1735 ms

reindex table a;

reindex table b;

vacuum full a;

vacuum full b;

後 explan でみたとき

Seq Scan on b でrowsが全件になっていませんか?

なっている場合遅くて当然なので

indexの見直しが必要と思います。

以下の索引ためしにつけてみてください。

create index b_idx_hoge_id on b using btree ( hoge_id );

追加すると

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

0.15 ms

select B.id from B where B.hoge_id IN (select A.id from A where A.hoge='abc');

0.15 ms

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

1700 ms

※:select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;は1件の場合

===================================================

ためしに200000件のデータを一致させた場合

select B.id from A,B where A.hoge='abc' and A.id=B.hoge_id;

runtime: 1956ms

select B.id from B where exists (select * from A where A.hoge='abc' and A.id=B.hoge_id);

runtime: 1775ms

>約3000レコードがヒットします

このへんデータ構造が原因ですね。

id:ktoshi

回答ありがとうございます。

試してみます。

>Seq Scan on b でrowsが全件になっていませんか?

なっています。

2006/08/24 07:42:02
id:masal No.4

masal回答回数43ベストアンサー獲得回数02006/08/24 10:09:16

ポイント40pt

あと、VACUUM FULLVACUUM ANALYZEを定期的に実行するのをお勧めします

PostgreSQLのマニュアル

AllAboutの解説ページ

id:ktoshi

回答ありがとうございます。

create index b_idx_hoge_id on b using btree ( hoge_id );

VACUUM FULL;

VACUUM ANALYZE;

を行ったところ、実行時間が40ミリ秒となりとても速くなりました。

また、explanでみたときSeq Scan on b ~ と表示されていた部分がなくなりました。

--------------------------------

回答してくださった皆さんありがとうございました。無事解決です。

補足:postgreSQLのバージョンは7.4.6

2006/08/24 11:35:58

この質問への反応(ブックマークコメント)

「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません