質問編集履歴

3 さらにアドバイスいただいたSQL文を追記

acre_maker

acre_maker score 139

2017/08/30 08:32  投稿

PosgreSQLの評価順序が期待するように動かない場合変更する方法はありますか?
**実行環境**
PostgreSQL (version 9.5)
[RDKit database cartridge](http://www.rdkit.org/docs/Cartridge.html) (version 2017.03.1)
[RDKit database cartridge](http://www.rdkit.org/docs/Cartridge.html)はPostgreSQLで
化学構造情報を取り扱うmol型や化学構造情報特有の検索を行うためのcartridgeです。
---
**目的**
現在、化学構造をレコードを保存しているcompoundsテーブルに対して部分構造検索と呼ばれる検索を行うSQL文を作成しています。
従来の方法では
0. compoundsレコードのmolと呼ばれるカラムに対して部分構造検索を行う
のみが行う作業となり以下のようなSQL文になります。
```sql
SELECT compound_id FROM compounds WHERE mol @> BAR
```
@>はRDKit database cartridgeで拡張された機能である部分構造検索を行う部分です。
しかし、compoundsテーブルは約1200万のレコードが含まれているので、
0. 別途用意したsubstructuresテーブルに対してquery_idと呼ばれるカラムで絞り込みを行いcompound_idを得る
1. 得られたcompound_idを用いてNATURAL JOINでcompoundsテーブルを絞り込む
2. 絞り込まれたレコードのmolと呼ばれるカラムに対して部分構造検索を行う
と言った作業を行うことで高速化を目指しています。
この1, 2, 3を順序通り行うために考えたSQL文が以下のようになります。
```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実行結果](c604fd870fb5fa977ead84c876c338cf.png)
**従来法のSQL文のEXPLAIN ANALYZE実行結果**
![従来法のSQL文のEXPLAIN ANALYZE実行結果](868bf0b956d93f3f9aa5904016941faf.png)
---
そこで、以下のようにWITHを用いたSQL文を使うと期待通り動くようになりました。
```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実行結果](a4c743ff09469ce16f29d0ace72f3b77.png)
今後の勉強のためにWITHを用いないSQL文で期待どおりに動くようにするためにはどうすればよいかアドバイスいただけたら幸いです。
@>というcartridge特有の機能が一番初めに実行される仕様となっているのでしょうか?
また、インデックスを貼るなど以外のより高速にするためのアドバイスが有ればそちらも合わせていただけたら幸いです。
よろしくお願いします。
---
追記
ご助言いただいたようにquery_idにインデックスを追加し、NATURA JOINをINに変更したSQL文のEXPLAIN ANALYZE実行結果
![改善案ver2SQL文のEXPLAIN ANALYZE実行結果](dd78ede48ecfc5300fbe2865f9311acc.png)
![改善案ver2SQL文のEXPLAIN ANALYZE実行結果](dd78ede48ecfc5300fbe2865f9311acc.png)
さらにご助言いただいたの結果はエラーとなりました。
![イメージ説明](0c23d3ba793ea47ca8fe4812a10702a6.png)
そこで、andでつないでみましたところ動きました。
![イメージ説明](61bbaec09bde7d7e67480d094677d433.png)](6991aa282d69b7bc8b7fa9fd6fb26204.png)
  • PostgreSQL

    1604 questions

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

2 ご助言を参考に訂正したSQL文のEXPLAIN ANALYZEを追加

acre_maker

acre_maker score 139

2017/08/29 17:51  投稿

PosgreSQLの評価順序が期待するように動かない場合変更する方法はありますか?
**実行環境**
PostgreSQL (version 9.5)
[RDKit database cartridge](http://www.rdkit.org/docs/Cartridge.html) (version 2017.03.1)
[RDKit database cartridge](http://www.rdkit.org/docs/Cartridge.html)はPostgreSQLで
化学構造情報を取り扱うmol型や化学構造情報特有の検索を行うためのcartridgeです。
---
**目的**
現在、化学構造をレコードを保存しているcompoundsテーブルに対して部分構造検索と呼ばれる検索を行うSQL文を作成しています。
従来の方法では
0. compoundsレコードのmolと呼ばれるカラムに対して部分構造検索を行う
のみが行う作業となり以下のようなSQL文になります。
```sql
SELECT compound_id FROM compounds WHERE mol @> BAR
```
@>はRDKit database cartridgeで拡張された機能である部分構造検索を行う部分です。
しかし、compoundsテーブルは約1200万のレコードが含まれているので、
0. 別途用意したsubstructuresテーブルに対してquery_idと呼ばれるカラムで絞り込みを行いcompound_idを得る
1. 得られたcompound_idを用いてNATURAL JOINでcompoundsテーブルを絞り込む
2. 絞り込まれたレコードのmolと呼ばれるカラムに対して部分構造検索を行う
と言った作業を行うことで高速化を目指しています。
この1, 2, 3を順序通り行うために考えたSQL文が以下のようになります。
```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実行結果](c604fd870fb5fa977ead84c876c338cf.png)
**従来法のSQL文のEXPLAIN ANALYZE実行結果**
![従来法のSQL文のEXPLAIN ANALYZE実行結果](868bf0b956d93f3f9aa5904016941faf.png)
---
そこで、以下のようにWITHを用いたSQL文を使うと期待通り動くようになりました。
```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実行結果](a4c743ff09469ce16f29d0ace72f3b77.png)
今後の勉強のためにWITHを用いないSQL文で期待どおりに動くようにするためにはどうすればよいかアドバイスいただけたら幸いです。
@>というcartridge特有の機能が一番初めに実行される仕様となっているのでしょうか?
また、インデックスを貼るなど以外のより高速にするためのアドバイスが有ればそちらも合わせていただけたら幸いです。
よろしくお願いします。
よろしくお願いします。
---
追記
ご助言いただいたようにquery_idにインデックスを追加し、NATURA JOINをINに変更したSQL文のEXPLAIN ANALYZE実行結果
![改善案ver2SQL文のEXPLAIN ANALYZE実行結果](dd78ede48ecfc5300fbe2865f9311acc.png)
  • PostgreSQL

    1604 questions

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

1 誤字があったので修正

acre_maker

acre_maker score 139

2017/08/29 13:31  投稿

PosgreSQLの評価順序が期待するように動かない場合変更する方法はありますか?
**実行環境**
PostgreSQL (version 9.5)
[RDKit database cartridge](http://www.rdkit.org/docs/Cartridge.html) (version 2017.03.1)
[RDKit database cartridge](http://www.rdkit.org/docs/Cartridge.html)はPostgreSQLで
化学構造情報を取り扱うmol型や化学構造情報特有の検索を行うためのcartridgeです。
---
**目的**
現在、化学構造をレコードを保存しているcompoundsテーブルに対して部分構造検索と呼ばれる検索を行うSQL文を作成しています。
従来の方法では
0. compoundsレコードのmolと呼ばれるカラムに対して部分構造検索を行う
のみが行う作業となり以下のようなSQL文になります。
```sql
SELECT compound_id FROM compounds WHERE mol @> BAR
```
@>はRDKit database cartridgeで拡張された機能である部分構造検索を行う部分です。
しかし、compoundsテーブルは約1200万のレコードが含まれているので、
0. 別途用意したsubstructuresテーブルに対してquery_idと呼ばれるカラムで絞り込みを行いcompound_idを得る
1. 得られたcompound_idを用いてNATURAL JOINでcompoundsテーブルを絞り込む
2. 絞り込まれたレコードのmolと呼ばれるカラムに対して部分構造検索を行う
と言った作業を行うことで高速化を目指しています。
この1, 2, 3を順序通り行うために考えたSQL文が以下のようになります。
```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実行結果](c604fd870fb5fa977ead84c876c338cf.png)
**従来法のSQL文のEXPLAIN ANALYZE実行結果**
![従来法のSQL文のEXPLAIN ANALYZE実行結果](868bf0b956d93f3f9aa5904016941faf.png)
---
そこで、以下のようにWITHを用いたSQL文を使うと期待通り動くようになりました。
```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実行結果](a4c743ff09469ce16f29d0ace72f3b77.png)
**WITHを用いた改善案のSQL文のEXPLAIN ANALYZE実行結果**
![WITHを用いた改善案のSQL文のEXPLAIN ANALYZE実行結果](a4c743ff09469ce16f29d0ace72f3b77.png)
今後の勉強のためにWITHを用いないSQL文で期待どおりに動くようにするためにはどうすればよいかアドバイスいただけたら幸いです。
@>というcartridge特有の機能が一番初めに実行される仕様となっているのでしょうか?
また、インデックスを貼るなど以外のより高速にするためのアドバイスが有ればそちらも合わせていただけたら幸いです。
よろしくお願いします。
  • PostgreSQL

    1604 questions

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

思考するエンジニアのためのQ&Aサイト「teratail」について詳しく知る