【SQL】あいまい検索機能で用いるSQL文がタイムアウトしてしまう

Question

あいまい検索機能で用いるSQL文のタイムアウトを防ぎたい(処理速度を上げたい)

Springbootフレームワークを用いて、
「テキストボックスに適当な値を入れると、その値を含む項目を表示する」という
あいまい検索機能を作成中です。

下記のようなSQL文を使用していますが、タイムアウトしてしまいます。
この書き方以外にタイムアウトしない書き方をご存じの方や
似た事象に遭って解決できた方がいらっしゃいましたら
どのように書いたらよいかご教示いただきたいです。

該当のソースコード

SELECT
  *
FROM(
  SELECT
    s.student_id
    , s.student_name
    , t.telephone_number
    , a.area
    , b.birth_month
  FROM
    student_table AS s
  LEFT JOIN
    telephone_number_table AS t
    ON s.telephone_number = t.telephone_number
  LEFT JOIN
    area_table AS a
    ON s.area_id= a.area_id
  LEFT JOIN
    birth_month_table AS b
    ON s.birth_month = b.birth_month
) AS target
WHERE(
  s.student_id IN (
    SELECT
      css_id
    FROM
      tb_trn_rn_css_order AS od
    LEFT JOIN
      telephone_number_table AS t
      ON s.telephone_number = t.telephone_number
    LEFT JOIN
      area_table AS a
      ON s.area_id= a.area_id
    LEFT JOIN
      birth_month_table AS b
      ON s.birth_month = b.birth_month
    WHERE
      CAST(s.student_id AS TEXT) LIKE '%${key}%'
      OR
      s.student_name LIKE '%${key}%'
      OR
      t.telephone_number LIKE '%${key}%'
      OR
      a.area LIKE '%${key}%'
      OR
      b.birth_month LIKE '%${key}%'
  )
)
ORDER BY
  s.student_id

試したこと

上記SQLのように、先にサブクエリでIDを絞り込んでいます。

補足情報（FW/ツールのバージョンなど）

postgreSQLを使用しています。
ビューは使用できません。

Comments

[sazi]

それぞれのテーブルのインデックス（プライマリーを含む）を追記して下さい。
また、そのSQLの実行計画も合わせて追記して下さい。

[m.ts10806]

実行計画の結果も追記してください。

Answer 1

SQLのチューニング以前にSQLの組み立て方に無駄（というか問題）があります。
Like条件部分は相関である必要は見当たりません。

以下の様にすると、CSS_IDで絞り込まれたものに対してlikeでのフィルターが掛かるようになると思いますので改善はされると思います。

SELECT  s.student_id
      , s.student_name
      , t.telephone_number
      , a.area
      , b.birth_month
FROM  student_table AS s
      LEFT JOIN telephone_number_table AS t
      ON    s.telephone_number = t.telephone_number
      LEFT JOIN area_table AS a
      ON    s.area_id= a.area_id
      LEFT JOIN   birth_month_table AS b
      ON    s.birth_month = b.birth_month
WHERE s.student_id IN (SELECT css_id FROM tb_trn_rn_css_order)
  and (
            CAST(s.student_id AS TEXT) LIKE '%${key}%'
        OR  s.student_name LIKE '%${key}%'
        OR  s.telephone_number LIKE '%${key}%'
        OR  a.area LIKE '%${key}%'
        OR  s.birth_month LIKE '%${key}%'
      )
ORDER BY s.student_id

likeでの部分一致はインデックスは使用されないので、likeじゃなきゃダメかどうかを見直しした方が良いと思います。
※birth_month が誕生月ならlikeじゃなくても良いのでは？

先にサブクエリでIDを絞り込んでいます。

厳密にこの方法でという事なら以下のような記述ですね。

SELECT  s.student_id
      , s.student_name
      , t.telephone_number
      , a.area
      , b.birth_month
FROM  student_table AS s
      LEFT JOIN telephone_number_table AS t
      ON    s.telephone_number = t.telephone_number
      LEFT JOIN area_table AS a
      ON    s.area_id= a.area_id
      LEFT JOIN   birth_month_table AS b
      ON    s.birth_month = b.birth_month
where s.student_id in (
        select  s.student_id
        from  student_table as s
              inner join tb_trn_rn_css_order o
              on    s.student_id=o.css_id
              left join area_table as a
              on    s.area_id= a.area_id
        where cast(s.student_id as text) like '%${key}%'
          or  s.student_name like '%${key}%'
          or  s.telephone_number like '%${key}%'
          or  a.area like '%${key}%'
          or  s.birth_month like '%${key}%'
      )
ORDER BY s.student_id