質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.51%
PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

Q&A

解決済

2回答

4782閲覧

PosgreSQLの評価順序が期待するように動かない場合変更する方法はありますか?

acre_maker

総合スコア145

PostgreSQL

PostgreSQLはオープンソースのオブジェクトリレーショナルデータベース管理システムです。 Oracle Databaseで使われるPL/SQLを参考に実装されたビルトイン言語で、Windows、 Mac、Linux、UNIX、MSなどいくつものプラットフォームに対応しています。

0グッド

0クリップ

投稿2017/08/29 04:30

編集2017/08/29 23:32

実行環境
PostgreSQL (version 9.5)
RDKit database cartridge (version 2017.03.1)

RDKit database cartridgeはPostgreSQLで
化学構造情報を取り扱うmol型や化学構造情報特有の検索を行うためのcartridgeです。


目的
現在、化学構造をレコードを保存しているcompoundsテーブルに対して部分構造検索と呼ばれる検索を行うSQL文を作成しています。

従来の方法では

  1. compoundsレコードのmolと呼ばれるカラムに対して部分構造検索を行う

のみが行う作業となり以下のようなSQL文になります。

sql

1SELECT compound_id FROM compounds WHERE mol @> BAR

@>はRDKit database cartridgeで拡張された機能である部分構造検索を行う部分です。

しかし、compoundsテーブルは約1200万のレコードが含まれているので、

  1. 別途用意したsubstructuresテーブルに対してquery_idと呼ばれるカラムで絞り込みを行いcompound_idを得る
  2. 得られたcompound_idを用いてNATURAL JOINでcompoundsテーブルを絞り込む
  3. 絞り込まれたレコードのmolと呼ばれるカラムに対して部分構造検索を行う

と言った作業を行うことで高速化を目指しています。

この1, 2, 3を順序通り行うために考えたSQL文が以下のようになります。

sql

1SELECT compound_id FROM 2compounds NATURAL JOIN (SELECT compound_id FROM substructures WHERE query_id = FOO) s 3WHERE mol @> BAR

しかしこれらの2通りの方法をEXPLAIN ANALYZEで調べたところ以下のような結果になりました。

改善案のSQL文では二度部分構造検索を行ってしまっており、むしろ計算コストが高くなってしまいました。

改善案のSQL文のEXPLAIN ANALYZE実行結果
改善案のSQL文のEXPLAIN ANALYZE実行結果

従来法のSQL文のEXPLAIN ANALYZE実行結果
従来法のSQL文のEXPLAIN ANALYZE実行結果

そこで、以下のようにWITHを用いたSQL文を使うと期待通り動くようになりました。

sql

1WITH s AS ( 2 SELECT * FROM compounds NATURAL JOIN (SELECT compound_id FROM substructures WHERE query_id = FOO) 3) 4SELECT COUNT(*) FROM s2 WHERE mol @> BAR

WITHを用いた改善案のSQL文のEXPLAIN ANALYZE実行結果
WITHを用いた改善案のSQL文のEXPLAIN ANALYZE実行結果

今後の勉強のためにWITHを用いないSQL文で期待どおりに動くようにするためにはどうすればよいかアドバイスいただけたら幸いです。

@>というcartridge特有の機能が一番初めに実行される仕様となっているのでしょうか?

また、インデックスを貼るなど以外のより高速にするためのアドバイスが有ればそちらも合わせていただけたら幸いです。

よろしくお願いします。


追記
ご助言いただいたようにquery_idにインデックスを追加し、NATURA JOINをINに変更したSQL文のEXPLAIN ANALYZE実行結果
改善案ver2SQL文のEXPLAIN ANALYZE実行結果

さらにご助言いただいたの結果はエラーとなりました。
イメージ説明

そこで、andでつないでみましたところ動きました。
イメージ説明](6991aa282d69b7bc8b7fa9fd6fb26204.png)

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答2

0

ベストアンサー

「改善案」とされる2番目のSQLの実行計画(画像では1番目)は単純に1番目のSQLの実行計画(画像では2番目)にsubstructuresテーブルのサブクエリの結果のJOINが結合されただけに見えますね。substructuresの検索コストが元のクエリのコストよりも大きいので、絞込に使う意味はないですし、そうすると無意味にコストを増やしているだけになっています。

二度部分構造検索を行ってしまっており

と書かれていますが、この点に違いはないように見えます。

3番目が意図通りと書かれていますが、コスト的には一番悪くなっています。

とりあえず大きな問題は、substructuresの検索が遅すぎることです。実行計画を見る限りではこれはおそらくquery_idにインデックスを張れば解決するとおもいます。

あとはサブクエリを結合してるのが何となくですが気持ち悪いです。結合ではなくINで検索するとか、サブクエリを使わずにJOINするとか、いろいろ試して実行計画確認してみてください

@>というcartridge特有の機能が一番初めに実行される仕様となっているのでしょうか?

外部のライブラリによって提供される演算子については、ライブラリ側でコストが過小や過大に設定されているためにコスト計算がおかしくなり不効率な実行計画が選択される事例があるようです。が、実行計画見る限りではその例には当てはまらないような気がします。

--
追記されたSQLですが、二重になってるサブクエリのうち内側はともかく外側は意味不明です。

SELECT * FROM compounds WHERE compounds_id IN (SELECT ...) AND mol @> ...

でよいでしょう。

投稿2017/08/29 06:16

編集2017/08/30 00:23
suzukis

総合スコア1449

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

acre_maker

2017/08/29 09:00 編集

ご解答ありがとうございます。 >とりあえず大きな問題は、substructuresの検索が遅すぎることです。実行計画を見る限りではこれは>おそらくquery_idにインデックスを張れば解決するとおもいます。 >あとはサブクエリを結合してるのが何となくですが気持ち悪いです。結合ではなくINで検索するとか、>サブクエリを使わずにJOINするとか、いろいろ試して実行計画確認してみてください ご助言いただいたとおり、query_idにインデックスを貼って、 NATURAL JOIN をINに変えましたら大分早くなり、絞り込みをしたほうが速くなりました! 画像を質問に追加で貼っておきます。 INは遅くなることが多いのであまり使わないほうがよいと間違って理解していたようで 敬遠していましたが、 NATURAL JOINの方が遥かに遅いですね。勉強不足でした。 結論としては、私が良かれと思って書いたSQL文の計算コストが大きすぎるから 結局、2度部分構造検索を行ってでもそちらの実行計画の方が優先されたという感じでしょうかね。 改めてありがとうございました!
acre_maker

2017/08/29 23:29

追記でアドバイスありがとうございます。 SELECT * FROM compounds WHERE compounds_id IN (SELECT ...) WHERE mol @> ... を試したところエラーが出たので、 SELECT * FROM compounds WHERE compounds_id IN (SELECT ...) and mol @> ... と、andでつないでみました。 こちらの方の結果も質問で追加で貼っておきますね。 重ね重ねありがとうございました!
suzukis

2017/08/30 00:23

すいません書き間違いです
acre_maker

2017/08/30 00:52

訂正ありがとうございました! ベストアンサーにさせていただきました。 長々とありがとうございました!
guest

0

@>というcartridge特有の機能が一番初めに実行される仕様とな,,,

molって必ずユニークになりますよね。
そしてインデックスも付いている(はず)。
そうするとオプティマイザは、
そのカラムを使うのが最速と判断するので、
通常の仕様かと思います。

suzukisさんも言われていますが
提示されているwith句を使った3番目のSQLは
NestedLoopされており非常にパフォーマンスが悪いと考えられます。
substructuresのquery_idですが、
総件数とquery_idの値のバラつきによってインデックスが利用されるかされないか変わってきます。
入力される検索条件も視野に入れたほうがいいです。

他の案としては、
compoundsテーブルのレコード数が多少多いので
compoundsのcompound_id毎にパーティション切ったりすると、
多少早くなるかもしれません。
※可能であれば、substructuresのquery_idに紐づくcompound_id毎が最速になるかと。

投稿2017/08/29 07:20

szk.

総合スコア1400

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

acre_maker

2017/08/29 08:56

ご解答ありがとうございます。 >molって必ずユニークになりますよね。 >そしてインデックスも付いている(はず)。 はい。おっしゃるとおりuniqueの制限をつけて、indexもgistで作成してます。 私の下手なSQL文でつけた絞り込みよりもそちらの方が高速だと判断されてしまったのですね。 >総件数とquery_idの値のバラつきによってインデックスが利用されるかされないか変わってきます。 >入力される検索条件も視野に入れたほうがいいです。 query_idで結構ばらつきが出るような結果になると思います。 あるquery_idではまったく含まれない、一方で100万以上ヒットするquery_idもあると思います。 >他の案としては、 >compoundsテーブルのレコード数が多少多いので >compoundsのcompound_id毎にパーティション切ったりすると、 >多少早くなるかもしれません。 >※可能であれば、substructuresのquery_idに紐づくcompound_id毎が最速になるかと。 パーティションは今まで考えたことがありませんでした。 勉強してみますね!ありがとうございます。 一番最後の文章どういう意味でしょうか?お暇な時に詳しくお話いただけると嬉しく思います。 改めましてありがとうございました!
szk.

2017/08/29 10:00

パーティショニングで調べてもらうと分かりますが、 パーティションを区切るcompound_idの条件がいい感じに設定できれば、 といったところですが、、、 ちょっと調べて勉強していただいて、再度新規の質問されるのがいいと思います。 ボクより詳しい方もたくさんおられるので。
acre_maker

2017/08/29 23:34

さらにアドバイスありがとうございます。 少し勉強してみました。 検索が1つのテーブルに偏るようでしたらパーティショニングは効果的そうですね。 化学構造を条件に組み込むことができたらおもしろそうです。 さらに勉強してまた理解できない点がでたら おっしゃるように再度新規の質問をしてみたいと思います。 改めましてアドバイスありがとうございました!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.51%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問