二重否定するSQL – NOT EXISTSのNOT EXISTSが遅い

今日はSQLの話です。

二重否定の検索をするSQLがありまして、数十秒かかるスロークエリーになっていたんですね。調べてみると、他にもEXISTSを使っている条件があったりして、結構なレコード数でループする実行計画になっててそりゃ遅いな、、と思ったわけです。

さて、チューニングしようとしたら意外といいクエリーが思い浮かばなくて困りました。

環境

PostgreSQL 13

元のSQL

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

reports
- レポート
report_items
- reports – itemsの中間テーブル
items
- レポートに記入する項目 (report has many report_images)
item_images
- 項目に追加する画像 (report_item has many item_images)
confirmed_images
- 画像を確認済みにしたときに作られるレコード (report_item has many confirmed_images)

これらのテーブルを検索するSQLがこちら。

SELECT
  "reports".*
FROM
  "reports"
  INNER JOIN "users" ON "users"."id" = "reports"."user_id"
WHERE
  "users"."company_id" = 'company uuid'
  AND EXISTS (
    -- itemsの検索条件が入る
    SELECT
    FROM
      "items"
      INNER JOIN "report_items" ON "report_items"."item_id" = "items"."id"
    WHERE
      "reports"."id" = "report_items"."report_id"
      AND "items"."created_at" >= '2021-07-01 00:00:00'
  )
  AND NOT EXISTS (
    -- 画像確認済みになっていない項目が存在しないようなレポートを検索する
    -- -> すべての項目が画像確認済みになっているレポートを検索する
    SELECT
    FROM
      "items" -- item_imagesが存在すること
      INNER JOIN "report_items" ON "report_items"."item_id" = "items"."id"
      INNER JOIN "item_images" ON "item_images"."item_id" = "items"."id"
    WHERE
      "reports"."id" = "report_items"."report_id"
      AND NOT EXISTS (
        SELECT
        FROM
          "confirmed_images" -- 画像確認済みの項目
        WHERE
          "confirmed_images"."item_id" = "items"."id"
      )
  );

項目を検索する条件と、「すべての項目が画像確認済みになっている」条件は互いに独立した条件にしないといけません。これが結構やっかいなところで、実行計画を見ると100万件 x 200万件のループになっていました。

最終的に、12,000msから10msになったSQLがこちら。NOT EXISTSのテーブルをitemsからreport_itemsに変更しただけです。

SELECT
  "reports".*
FROM
  "reports"
  INNER JOIN "users" ON "users"."id" = "reports"."user_id"
WHERE
  "users"."company_id" = 'company uuid'
  AND EXISTS (
    SELECT
    FROM
      "items"
      INNER JOIN "report_items" ON "report_items"."item_id" = "items"."id"
    WHERE
      "reports"."id" = "report_items"."report_id"
      AND "items"."created_at" >= '2021-07-01 00:00:00'
  )
  AND NOT EXISTS (
    SELECT
    FROM
      "report_items"
      INNER JOIN "item_images" ON "item_images"."item_id" = "report_items"."item_id"
    WHERE
      "reports"."id" = "report_items"."report_id"
      AND NOT EXISTS (
        SELECT
        FROM
          "confirmed_images"
        WHERE
          "confirmed_images"."item_id" = "report_items"."item_id"
      )
  );

とりあえずこの変更で実行時間が激減したものの、理由がよく分かってないのが気持ち悪いですね。

ほかにも、同程度の実行時間になったSQLもあります。こちらは、検索条件でreportsを絞り込んでからNOT EXISTSの条件をCOUNTに変換して検索するSQLになります。

SELECT
  "reports".*,
FROM
  (
    SELECT
      "reports".*,
      (
        -- レポートに含まれる画像付き項目の数
        SELECT
          COUNT(*) AS "count1"
        FROM
          "items"
          INNER JOIN "report_items" ON "report_items"."item_id" = "items"."id"
          INNER JOIN "item_images" ON "item_images"."item_id" = "report_items"."item_id"
        WHERE
          "reports"."id" = "report_items"."report_id"
      ) AS "t1",
      (
        -- レポートに含まれる確認済み画像付き項目の数
        SELECT
          COUNT(*) AS "count2"
        FROM
          "items"
          INNER JOIN "report_items" ON "report_items"."item_id" = "items"."id"
          INNER JOIN "item_images" ON "item_images"."item_id" = "report_items"."item_id"
          INNER JOIN "confirmed_images" ON "confirmed_images"."item_id" = "report_items"."item_id"
        WHERE
          "reports"."id" = "report_items"."report_id"
      ) AS "t2"
    FROM
      "reports"
      INNER JOIN "users" ON "users"."id" = "reports"."user_id"
    WHERE
      "users"."company_id" = 'company uuid'
      AND EXISTS (
        SELECT
        FROM
          "items"
          INNER JOIN "report_items" ON "report_items"."item_id" = "items"."id"
        WHERE
          "reports"."id" = "report_items"."report_id"
          AND "items"."created_at" >= '2021-07-01 00:00:00'
      )
    GROUP BY
      "reports"."id"
  ) AS "reports"
WHERE
  "reports"."count1" = "reports"."count2";