外部プログラムで特定したidに対するLEFT OUTER JOIN

Question

こんにちは

現在PostgreSQLとPythonを用いて以下のような流れの作業をしています。

1）Pythonを用いてある条件に合うテーブルX中のデータのidを取得する。

例）
id = 1, 3, 5, 6, 9, .....
使うデータで上の結果は変動します。

2）1のデータのidをinを用いてとりだす。

例)
SELECT * FROM X in (1, 3, 5, 6, 9, ...)

```ここの部分は実際にはPythonで書いています。

3）それに対して更に絞り込みを行う。。。。

といった流れです。


現在、この作業が非常に計算時間がかかるので高速化を考えています。

その際２のinを用いた検索に注目しました。

ネットや本などで勉強したところinやnot inを用いたSQL文は

検索に非常に時間がかかるので、LEFT OUTER JOINとNULLかどうかの

検索で時間を短縮できると書いていました。


しかし、ネットや本などでの例ではinの後ろ(上の例だと(1, 3, 5, 6, 9, ...))

は別のテーブルを用いている場合がほとんどです。

```ここに言語を入力
例)
SELECT * FROM X IN (SELECT ID FROM Y);

今回の私のように他のテーブルを用いない場合に、

同じようにLEFT OUTER JOINを用いたSQL文はかけますでしょうか？

LEFT OUTER JOINにこだわるつもりはありませんので

他の方法もございましたら

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

Comments

[A.Ichi]

だいたいのテーブルXの数とin()に使われる数は、お分かりになりますでしょうか？

[acre_maker]

テーブルXにはおよそ130,0000のレコードが入っています。
in()に使われる数は多くて1000です。ほとんどが10程度です。

Answer 1

今回の私のように他のテーブルを用いない場合に、

同じようにLEFT OUTER JOINを用いたSQL文はかけますでしょうか？

JOIN句はあくまでも別テーブルとの結合に利用するため、
そのままでは利用できません。

###それでもJOIN句を使ってみたい場合
そのままでは利用できませんと書きましたが、
サブクエリ等を利用して一時テーブルを作成すると、
一応LEFT JOINなどによるやり方が実現可能となります。
ただ元々のクエリはIN句で一致しているもののみを抽出しているので、
LEFT JOINよりはINNER JOINが正解となる気がします。

ちなみに以前当方もこちらのサイトで教わったのですが、
PostgreSQLでは以下のようにすると、配列から行展開が行えるようです。

SELECT
   UNNEST(ARRAY[1, 3, 5, 6, 9]) AS ID

結果
id
1
3
5
6
9

上記クエリで作成した一時テーブルを利用すると、
JOIN句を利用した記述方法は利用可能となると思われます。

###そもそもパフォーマンスが出ていない理由について
質問者さんはIN句の実行がボトルネックと考えているようですが、本当にそうでしょうか？

パフォーマンスの改善を行う場合はただやみ雲にやってもよい成果が上がらないので、
先ず本当にそこがボトルネックなっているのかを確認する必要があると思われます。

• SQLを直接実行した場合のPostgreSQL側の応答時間はどうか？
• id列にインデックスが張られている場合、そのインデックスが本当に利用されているかどうか？

1つ目については実はSQL単体で動作させると実はすぐに応答が返ってきたということになると、
それはSQLではなくそれ以外の箇所がボトルネックとなっていることになります。

１つ目で実際に応答が遅いことが確認でき、
かつid列にもインデックスが張ってある場合は2つ目を確認しましょう。
実行したSQLの実行計画を見るとヒントが得られることが多々あります。

実行計画を見て、インデックスが利用されていない場合は、
何らかの原因があるはずなので、
以降は更に調査進めるというのが一般的な流れとなります。

###その他確認ポイント

• A.Ichiさんのご回答にもあるように、

id列にインデックス(または主キー)が張られていますでしょうか？

id列にインデックスが張られておらず、
このテーブル自体の更新頻度があまり高くないのであれば、
id列にインデックスを張ることをおすすめします。

• id列の定義と検索時に指定した値の型は一致していますでしょうか？

検索時に指定している数字は数値型となりますが、
テーブル定義上のidも数値型になっていることを確認してください。

万一テーブル定義上では文字列型として定義されていた場合は、
DB側で暗黙の型変換が行われパフォーマンスが悪化します。

• IN句対象レコードを絞り更に絞り込みとありますが、

1つの処理(クエリ)にまとめることは出来ないでしょうか？

仮に一発の処理でまとめて記載できる場合は、
ある程度の速度改善が見込めるかもしれません。

Comments

[acre_maker]

細かくご指導ありがとうございました！UNNEST知らなかったので勉強になりました。
インデックスを含め色々試行錯誤した結果少しパフォーマンスが改善できました！

Answer 2

もしテーブルXのidにインデックスを貼るのが可能であれば、早くなるかと思われます。
もう貼られていれば、忘れてください。
また１３０万件に更新が沢山発生すると更新に時間が掛かるのでその場合は、お勧めしません。
さらにインデックスをお使いになるのであれば、REINDEXも速度低下を防ぐためには、検討された方がよろしいかと思います。

Comments

[acre_maker]

インデックスを貼ることで少しスピードアップしました！ありがとうございました！