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

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

ただいまの
回答率

90.23%

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

解決済

回答 2

投稿

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

acre_maker

score 139

こんにちは

現在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)それに対して更に絞り込みを行う。。。。

といった流れです。

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

その際2の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にこだわるつもりはありませんので

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

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

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

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

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

    クリップを取り消します

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

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

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

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

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

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

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

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

    質問の評価を下げる

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

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

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

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

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

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

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

    詳細な説明はこちら

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

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

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

質問への追記・修正、ベストアンサー選択の依頼

  • A.Ichi

    2016/08/29 13:38

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

    キャンセル

  • acre_maker

    2016/08/29 13:42

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

    キャンセル

回答 2

checkベストアンサー

0

今回の私のように他のテーブルを用いない場合に、
同じように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ではなくそれ以外の箇所がボトルネックとなっていることになります。

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

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

その他確認ポイント

  • A.Ichiさんのご回答にもあるように、
    id列にインデックス(または主キー)が張られていますでしょうか?

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

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

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

  • IN句対象レコードを絞り更に絞り込みとありますが、
    1つの処理(クエリ)にまとめることは出来ないでしょうか?

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

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/05 15:20

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

    キャンセル

0

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

投稿

  • 回答の評価を上げる

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

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

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

  • 回答の評価を下げる

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

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

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

  • 2016/09/05 15:20

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

    キャンセル

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

  • ただいまの回答率 90.23%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる