ポリモーフィック関連を検索するSQLを速くしたい

SQLアンチパターンにも紹介されているポリモーフィック関連ですが、運用中のサービスで使われていると、そんなにすぐにスキーマ変更するわけにもいかず、うまく付き合っていく方法を探さないといけない場合もあります。

検索システムでこのポリモーフィック関連が使われていて、さらに複雑な検索条件が組み合わさると、検索結果が出るまで非常に時間がかかってしまうことがあります。ありました（笑）

この記事では、ポリモーフィック関連がつかわれているSQLのチューニングの方法をご紹介します。

環境

PostgreSQL 13

元のSQL

関連するテーブルはこんな感じです。

ER図

reports
- レポート
users
- ユーザー
departments
- 部署
user_departments
- ユーザーの所属部署
- ユーザーは複数の部署に所属できる

レポートを検索するSQLがこちら。あるユーザーAが承認したレポートを探すSQLです。

SELECT
  "reports".*
FROM
  "reports"
WHERE
  (
    "reports"."approved_by_type" = 'User'
    AND "reports"."approved_by_id" = ? -- user_id
  )
  OR (
    "reports"."approved_by_type" = 'Department'
    AND "reports"."approved_by_id" IN (
      SELECT
        "user_departments"."department_id"
      FROM
        "user_departments"
      WHERE
        "user_departments"."user_id" = ? -- user_id
    )
  );

ユーザーが承認する場合と、部署が承認する場合があるので、どちらが承認したかを表すapproved_byにポリモーフィック関連が使われています。

実際の検索条件は結構複雑で、他にも複数のテーブルを結合したり条件に加えるパターンがあるので、単純化したSQLになっています。

他の検索条件を考慮すると、このSQLは非常に遅くなります。これをチューニングする場合UNIONを使う方法があります。

UNIONで高速化

OR条件でサブクエリーを使うと、用意しておいたインデックスが使われず遅かったのでUNIONにすると速くなりました。

SELECT
  "reports".*
FROM
(
  (
    SELECT
      "reports".*
    FROM
      "reports"
    WHERE
      (
        "reports"."approved_by_type" = 'User'
        AND "reports"."approved_by_id" = ? -- user_id
      )
  )
  UNION
  (
    SELECT
      "reports".*
    FROM
      "reports"
    WHERE
      (
        "reports"."approved_by_type" = 'Department'
        AND "reports"."approved_by_id" IN (
          SELECT
            "user_departments"."department_id"
          FROM
            "user_departments"
          WHERE
            "user_departments"."user_id" = ? -- user_id
        )
      )
  )
) AS "reports";

しかし、この方法でもレコード数の増加に伴い、スロークエリーとなってしまったので、別の方法を考える必要がありました。事前にユーザーAの所属部署IDが分かればサブクエリーが一つ減るので、速くなりそうですね。

検索対象のIDを取得しておく

あらかじめユーザーAの所属部署IDを次のSQLで取得しておいて、approved_by_idの検索条件に使用します。

SELECT
  "user_departments"."department_id"
FROM
  "user_departments"
WHERE
  "user_departments"."user_id" = ?; -- user_id

SELECT
  "reports".*
FROM
  "reports"
WHERE
  (
    "reports"."approved_by_type" = 'User'
    AND "reports"."approved_by_id" = ? -- user_id
  )
  OR (
    "reports"."approved_by_type" = 'Department'
    AND "reports"."approved_by_id" IN (?) -- department_id
  );

PostgreSQLの場合だと、このSQLの実行計画ではBitmpaOrでインデックスが使われるので、UNIONを使った場合と比較してかなり高速化できました。

INにまとめる

IDにUUIDを使っていたおかげでさらなる高速化が可能でした。実はOR条件にする必要がなかったんですね。usersテーブルで使用されるIDは他のテーブルには存在せず、departmentsテーブルで使用されるIDはusersテーブルに存在しないので、approved_by_typeとapproved_by_idをそれぞれ一つの検索条件として取り扱うことが可能です。

ということで、最終的に運用しているSQLがこちらです。

SELECT
  "reports".*
FROM
  "reports"
WHERE
  "reports"."approved_by_type" IN ('User', 'Department')
  AND "reports"."approved_by_id" IN (?); -- user_idとdepartment_idのUUID

当初のSQLと比べてもかなりシンプルな形になりました。実行計画を見ても(approved_by_type, approved_by_id)のインデックスがちゃんと使われてますし、数十秒かかっていた検索が300ms程度になりました。

連番のIDを使っている場合、この方法は採用できないですが、UUIDを使っていて他のテーブルのIDと重複したIDにならない場合は有効な方法です。