パーティショニングを使わずに親テーブルのSELECTを子テーブルに飛ばす。

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

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

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

compoundsテーブルは約1200万のレコードが含まれており計算コストが非常に高いです。

そこで、以前コチラの質問をした時に「パーティショニングをしてみては？」とアドバイスをいただきましたので、
パーティショニングを以下のように行ってみました。

sql
1%%sql
2CREATE OR REPLACE FUNCTION compounds_insert_trigger()
3RETURNS TRIGGER AS $$
4BEGIN
5    IF (NEW.mol @> 'C1CNC1') THEN
6        INSERT INTO compounds_C1CNC1 VALUES (NEW.*);
7    ELSIF (NEW.mol @> 'c2ncc1NCNc1n2') THEN
8        INSERT INTO compounds_c2ncc1NCNc1n2 VALUES (NEW.*);
9    ELSIF (NEW.mol @> 'C1CCC1') THEN
10        INSERT INTO compounds_C1CCC1 VALUES (NEW.*);
11    ELSIF (NEW.mol @> 'c2ccc1CCCCc1c2') THEN
12        INSERT INTO compounds_c2ccc1CCCCc1c2 VALUES (NEW.*);
13    ELSIF (NEW.mol @> 'c2ccc(c1ccccc1)cc2') THEN
14        INSERT INTO compounds_c2ccc_c1ccccc1_cc2 VALUES (NEW.*);
15    ELSIF (NEW.mol @> 'c2ccc1NCCc1c2') THEN
16        INSERT INTO compounds_c2ccc1NCCc1c2 VALUES (NEW.*);
17    ELSIF (NEW.mol @> 'c2ccc1ccccc1c2') THEN
18        INSERT INTO compounds_c2ccc1ccccc1c2_ VALUES (NEW.*);
19    ELSIF (NEW.mol @> 'c2ccc1ncccc1c2') THEN
20        INSERT INTO compounds_c2ccc1ncccc1c2 VALUES (NEW.*);
21    ELSIF (NEW.mol @> 'C1CCCC1') THEN
22        INSERT INTO compounds_C1CCCC1 VALUES (NEW.*);
23    ELSIF (NEW.mol @> 'C1CCCCC1') THEN
24        INSERT INTO compounds_C1CCCCC1 VALUES (NEW.*);
25    ELSIF (NEW.mol @> 'c1c[nH]cn1') THEN
26        INSERT INTO compounds_c1c_nH_cn1 VALUES (NEW.*);
27    ELSIF (NEW.mol @> 'c1ccccc1.c1ccccc1.c1ccccc1') THEN
28        INSERT INTO compounds_c1ccccc1_c1ccccc1_c1ccccc1 VALUES (NEW.*);
29    ELSIF (NEW.mol @> 'C1CCNC1') THEN
30        INSERT INTO compounds_C1CCNC1 VALUES (NEW.*);
31    ELSIF (NEW.mol @> 'c1ccncc1') THEN
32        INSERT INTO compounds_c1ccncc1 VALUES (NEW.*);
33    ELSIF (NEW.mol @> 'CCCCCC') THEN
34        INSERT INTO compounds_CCCCCC VALUES (NEW.*);
35    ELSIF (NEW.mol @> 'c1ccccc1.c1ccccc1') THEN
36        INSERT INTO compounds_c1ccccc1_c1ccccc1 VALUES (NEW.*);
37    ELSIF (NEW.mol @> 'c1ccccc1') THEN
38        INSERT INTO compounds_c1ccccc1_ VALUES (NEW.*);
39    ELSE
40        INSERT INTO compounds_others VALUES (NEW.*);
41    END IF;
42    RETURN NULL;
43END;
44$$
45LANGUAGE plpgsql;
46
47CREATE TRIGGER insert_compounds_trigger
48    BEFORE INSERT ON compounds
49    FOR EACH ROW EXECUTE PROCEDURE compounds_insert_trigger();

しかし、EXPLAIN ANALYZEを調べてみたところ残念ながらCHECKの条件が簡単なものではないためconstraint_exclusionが働いていませんでした。
(@>はRDKit database cartridgeで拡張された機能である部分構造検索を行う部分です。右側に化学構造を含めます。)

ただ、別のテーブルに保存されるところはうまくいっているので

親テーブルをSELECT * FROM mol @> foo した時にトリガで特定のテーブルだけ検索するようにすれば計算コストが小さくなるのではないかと考えました。

現在さらに調べてトリガはSELECTに使うことができないのでRULEを使えばよいのかな？というところまで進みましたが

ドキュメントには

現時点では、ON SELECTルールは、条件を持たないINSTEADルールでなければなりません。

と書いているのでRULEでも難しいのかなとなってしまい、ここでどうすればよいかわからなくなってしまいました。

どのようにすれば目標を達成できるかアドバイスを頂けたら助かります。
パーティショニングを使う使わないにもこだわりはありません。

ご指導ご鞭撻よろしくお願いします。

行動規範の内容に同意します

回答1件

ベストアンサー

分割している内容を見ると、パーティション分割に適しているか疑問です。

要素を含むということで分割しているように見えますが、それだと同じ要素を含んでいるものがあると、複数のテーブルに存在することになるのではないでしょうか。
それだと、（どの程度かが重要ですが）トータルの件数は増加することになります。

またこの場合、最初にHITしたものだけ返却することになったりしないでしょうか
（同様なことについて経験は無いので推測にしか過ぎませんが）

現状Gist形式のインデックスとのことですが、別途Gin形式のインデックスが作成可能ならその方が高速なので、GIn形式で絞り込んでさらにGist形式で絞り込むという２段階方式もありかもしれません。
※mol型というのがどういう構造なのかまでは配慮していませんので、可能かどうかは分かりません。

因みに、@>はRDkitの拡張ではなくpostgresのテキスト検索演算子だと思われます。
なので、Gistインデックス作成時にmol型もtsquery型に変換しているのではないかと思われますが。

投稿2017/09/01 02:45

編集2017/09/01 06:24

sazi

総合スコア25173

acre_maker

2017/09/01 03:55

ご解答ありがとうございます。わかりにくい説明ですいません。＞要素を含むということで分割しているように見えますが、それだと同じ要素を含んでいるものがあると、複数のテーブルに存在することになるのではないでしょうか。＞それだと、（どの程度かが重要ですが）トータルの件数は増加することになります。おっしゃるとおり化学構造中に特定の構造があるかで絞り込んでいます。 ELSIFでつないでおりますので、上から順番に特定の構造を持つかで調べておりいずれかの構造を持っていたらそのテーブルに登録する。としているので、トータルの件数は増加しておりません。＞現状Gist形式のインデックスとのことですが、別途Gin形式のインデックスが作成可能ならその方が高速＞なので、GIn形式で絞り込んでさらにGist形式で絞り込むという２段階方式もありかもしれません。＞※mol型というのがどういう構造なのかまでは配慮していませんので、可能かどうかは分かりません。 mol型がおそらくGist形式しか対応してないと思います。 btree型で作ったことがありますが動いてなかったと思います。２段階方式は面白いアイディアだと思いますので、もう一度調べるなり作者に一度問い合わせてみます。ありがとうございました。＞因みに、@>はRDkitの拡張ではなくpostgresのテキスト検索演算子だと思われます。＞なので、Gistインデックス作成時にmol型もtsquery型に変換しているのではないかと思われますが。勉強不足ですいませんでした。 @>はmol型に対しては部分構造検索演算子として働くとお伝えするべきでしたね。部分構造検索は単純なテキスト検索ではないのでtsquery型に変換している可能性は低いと思います。 (簡単に説明しますと、特定の化学構造が別の化学構造の部分集合になっているかどうかを調べる検索です) 改めまして、ありがとうございました。

sazi

2017/09/01 05:27 編集

確かにelseifでしたね。では、A,B両方の要素を持つものをelseifで分割していき、Aの分割側に格納されたとすると、Bの要素で検索した場合、HITしないことにならないでしょうか。検索されたとしても、インデックスが使われるとは思われず、それだと目的から逸れてしまいますし。

acre_maker

2017/09/01 06:19

＞では、A,B両方の要素を持つものをelseifで分割していき、Aの分割側に格納されたとすると、Bの要素で検索した場合、HITしないことにならないでしょうか。確かにおっしゃるとおりその可能性はありますね。と、考えると別の条件で絞る必要がありますね。要素が重複している可能性があるものはCHECKに使えないですね。残念です。ご指摘、ありがとうございました！助かりました。

行動規範の内容に同意します