人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

データベースの質問です。
テーブル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つに分けて実行させた方が良いのでしたらその方法を教えて下さい。

●質問者: ktoshi
●カテゴリ:インターネット ウェブ制作
✍キーワード:ABC And A hoge PHP postgreSQL
○ 状態 :終了
└ 回答数 : 4/4件

▽最新の回答へ

1 ● iketerummo
●10ポイント

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';

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

◎質問者からの返答

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

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);

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


2 ● kurukuru-neko
●20ポイント

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

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

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により差はあ

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

◎質問者からの返答

ありがとうございます。

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

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

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レコードがヒットします。


3 ● kurukuru-neko
●20ポイント ベストアンサー

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

索引に間違いなので

以下で試してみると

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レコードがヒットします

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

◎質問者からの返答

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

試してみます。

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

なっています。


4 ● masal
●40ポイント

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

PostgreSQLのマニュアル

AllAboutの解説ページ

◎質問者からの返答

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

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

関連質問


●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ