実行環境
PostgreSQL (version 9.5)
RDKit database cartridge (version 2017.03.1)
RDKit database cartridgeはPostgreSQLで
化学構造情報を取り扱うmol型や化学構造情報特有の検索を行うためのcartridgeです。
目的
現在、化学構造をレコードを保存しているcompoundsテーブルに対して部分構造検索と呼ばれる検索を行うSQL文を作成しています。
従来の方法では
- compoundsレコードのmolと呼ばれるカラムに対して部分構造検索を行う
のみが行う作業となり以下のようなSQL文になります。
sql
1SELECT compound_id FROM compounds WHERE mol @> BAR
@>はRDKit database cartridgeで拡張された機能である部分構造検索を行う部分です。
しかし、compoundsテーブルは約1200万のレコードが含まれているので、
- 別途用意したsubstructuresテーブルに対してquery_idと呼ばれるカラムで絞り込みを行いcompound_idを得る
- 得られたcompound_idを用いてNATURAL JOINでcompoundsテーブルを絞り込む
- 絞り込まれたレコードの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実行結果
そこで、以下のように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文で期待どおりに動くようにするためにはどうすればよいかアドバイスいただけたら幸いです。
@>というcartridge特有の機能が一番初めに実行される仕様となっているのでしょうか?
また、インデックスを貼るなど以外のより高速にするためのアドバイスが有ればそちらも合わせていただけたら幸いです。
よろしくお願いします。
追記
ご助言いただいたようにquery_idにインデックスを追加し、NATURA JOINをINに変更したSQL文のEXPLAIN ANALYZE実行結果
そこで、andでつないでみましたところ動きました。
](6991aa282d69b7bc8b7fa9fd6fb26204.png)
回答2件
あなたの回答
tips
プレビュー
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/08/29 09:00 編集
2017/08/29 23:29
2017/08/30 00:23
2017/08/30 00:52