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

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

ただいまの
回答率

90.53%

  • PostgreSQL

    1310questions

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

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 973

acre_maker

score 133

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

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


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

従来の方法では

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

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

SELECT 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文が以下のようになります。

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


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

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

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

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

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

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

WITH s AS (
      SELECT * FROM compounds NATURAL JOIN (SELECT compound_id FROM substructures WHERE query_id = FOO)
)
SELECT 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)

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+2

「改善案」とされる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 17:49 編集

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

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

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

    ご助言いただいたとおり、query_idにインデックスを貼って、
    NATURAL JOIN をINに変えましたら大分早くなり、絞り込みをしたほうが速くなりました!
    画像を質問に追加で貼っておきます。
    INは遅くなることが多いのであまり使わないほうがよいと間違って理解していたようで
    敬遠していましたが、
    NATURAL JOINの方が遥かに遅いですね。勉強不足でした。


    結論としては、私が良かれと思って書いたSQL文の計算コストが大きすぎるから
    結局、2度部分構造検索を行ってでもそちらの実行計画の方が優先されたという感じでしょうかね。

    改めてありがとうございました!

    キャンセル

  • 2017/08/30 08:29

    追記でアドバイスありがとうございます。

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

    を試したところエラーが出たので、

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

    と、andでつないでみました。

    こちらの方の結果も質問で追加で貼っておきますね。

    重ね重ねありがとうございました!

    キャンセル

  • 2017/08/30 09:23

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

    キャンセル

  • 2017/08/30 09:52

    訂正ありがとうございました!

    ベストアンサーにさせていただきました。

    長々とありがとうございました!

    キャンセル

+1

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

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

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

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/08/29 17:56

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

    >molって必ずユニークになりますよね。
    >そしてインデックスも付いている(はず)。

    はい。おっしゃるとおりuniqueの制限をつけて、indexもgistで作成してます。
    私の下手なSQL文でつけた絞り込みよりもそちらの方が高速だと判断されてしまったのですね。

    >総件数とquery_idの値のバラつきによってインデックスが利用されるかされないか変わってきます。
    >入力される検索条件も視野に入れたほうがいいです。

    query_idで結構ばらつきが出るような結果になると思います。
    あるquery_idではまったく含まれない、一方で100万以上ヒットするquery_idもあると思います。

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

    パーティションは今まで考えたことがありませんでした。
    勉強してみますね!ありがとうございます。
    一番最後の文章どういう意味でしょうか?お暇な時に詳しくお話いただけると嬉しく思います。

    改めましてありがとうございました!

    キャンセル

  • 2017/08/29 19:00

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

    キャンセル

  • 2017/08/30 08:34

    さらにアドバイスありがとうございます。

    少し勉強してみました。
    検索が1つのテーブルに偏るようでしたらパーティショニングは効果的そうですね。
    化学構造を条件に組み込むことができたらおもしろそうです。

    さらに勉強してまた理解できない点がでたら
    おっしゃるように再度新規の質問をしてみたいと思います。

    改めましてアドバイスありがとうございました!

    キャンセル

同じタグがついた質問を見る

  • PostgreSQL

    1310questions

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