Discourse での遅いクエリ

開発
1

データベースログから、user.rb#L379 によって生成されたサブクエリに不要な ORDER BY が含まれているクエリがいくつか見つかりました。その一例は以下の通りです：

SELECT 
  "group_users"."group_id" 
FROM 
  "group_users" 
WHERE 
  "group_users"."group_id" IN (
    SELECT 
      "groups"."id" 
    FROM 
      "groups" 
    WHERE 
      (groups.id > 0) 
    ORDER BY 
      name ASC
  ) 
  AND "group_users"."user_id" = 762

サブクエリ内の ORDER BY は意味をなさないため、これを削除することでクエリを高速化できます。当社のテストによると、サブクエリでのソートに費やす時間に応じて、クエリのパフォーマンスを 18%〜90% 向上させることができます。

2

実際のベンチマーク値（ミリ秒単位）は何ですか？

3

この例では、ORDER BY を削除することで、クエリ実行時間を 4711878 ナノ秒から 585849 ナノ秒に短縮できます。

4

Discourse でスペックを実行したところ、データベースから不要な DISTINCT が生成された低速なクエリ（類似クエリと比較して 6480259 ナノ秒）が検出されました。これは以下の site_settings_controller.rb#L141 によって生成されています。

SELECT 
  DISTINCT users.id 
FROM 
  "users" CROSS 
  JOIN tags t 
  LEFT JOIN tag_users tu ON users.id = tu.user_id 
  AND t.id = tu.tag_id 
WHERE 
  (
    t.id IN (1825) 
    AND tu.notification_level IS NULL
  )

tag_users 表に UNIQUE (tag_id, user_id, notification_level) 制約があり、tags 表に PRIMARY KEY(id) 制約があるため、tags.id と notification_level が値を指定されている場合、クロスジョインも左ジョインも重複レコードを生成しません。つまり、DISTINCT を削除することでクエリを高速化できます。

この最適化されたクエリは 4538891 ナノ秒で実行され（約 30% の改善）ます。

6

データベースログから、group.rb#L112 によって生成された、多数のサブクエリと UNION を含む低速なクエリ（6064379 ナノ秒）が見つかりました。以下がその内容です：

SELECT 
  "groups"."id", 
  "groups"."name" 
FROM 
  "groups" 
  INNER JOIN "group_users" ON "groups"."id" = "group_users"."group_id" 
WHERE 
  "group_users"."user_id" = 296 
  AND (groups.id > 0) 
  AND (
    groups.id IN (
      SELECT 
        id 
      FROM 
        groups 
      WHERE 
        visibility_level = 0 
      UNION ALL 
      SELECT 
        id 
      FROM 
        groups 
      WHERE 
        visibility_level = 1 
        AND 296 IS NOT NULL 
      UNION ALL 
      SELECT 
        g.id 
      FROM 
        groups g 
        JOIN group_users gu ON gu.group_id = g.id 
        AND gu.user_id = 296 
      WHERE 
        g.visibility_level = 2 
      UNION ALL 
      SELECT 
        g.id 
      FROM 
        groups g 
        LEFT JOIN group_users gu ON gu.group_id = g.id 
        AND gu.user_id = 296 
        AND gu.owner 
      WHERE 
        g.visibility_level = 3 
        AND (
          gu.id IS NOT NULL 
          OR FALSE
        ) 
      UNION ALL 
      SELECT 
        g.id 
      FROM 
        groups g 
        JOIN group_users gu ON gu.group_id = g.id 
        AND gu.user_id = 296 
        AND gu.owner 
      WHERE 
        g.visibility_level = 4
    )
  ) 
ORDER BY 
  name ASC

これは明らかに、以下のクエリと同等です：

SELECT 
  "groups"."id", 
  "groups"."name" 
FROM 
  "groups" 
  INNER JOIN "group_users" ON "groups"."id" = "group_users"."group_id" 
WHERE 
  "group_users"."user_id" = 296 AND
  groups.visibility_level IN (0, 1, 2, 3, 4) 
ORDER BY 
  name ASC

この最適化されたクエリは 378062 ナノ秒で実行され、93% の改善が見られました。

7

どうして等価なのでしょうか？あなたは可視レベルのチェックを行わないグループを探しています。

8

申し訳ありませんが、正しい書き換えられたクエリは以下の通りです。

SELECT 
  "groups"."id", 
  "groups"."name" 
FROM 
  "groups" 
  INNER JOIN "group_users" gu ON "groups"."id" = gu."group_id" 
WHERE 
  "group_users"."user_id" = 296 
   AND (groups.id > 0) 
   AND (
    groups.visibility_level IN (0, 1, 2) 
    OR (groups.visibility_level IN (3,4) and gu.owner)
   ) 
ORDER BY 
  name ASC

visibility_level = 0,1,2 という述語を持つサブクエリは groups.visibility_level IN (0, 1, 2) に置き換えられ、visibility_level = 3,4 という述語を持つサブクエリは or (groups.visibility_level IN (3,4) and gu.owner) に置き換えることができます。

9

データベースから、directory_items_controller.rb によって生成された不要な JOIN と DISTINCT を含む低速なクエリ（1141257 ナノ秒）が見つかりました。以下は該当するクエリです。

SELECT 
  COUNT(DISTINCT "directory_items"."id") 
FROM 
  "directory_items" 
  LEFT OUTER JOIN "users" ON "users"."id" = "directory_items"."user_id" 
  LEFT OUTER JOIN "group_users" ON "group_users"."user_id" = "users"."id" 
  LEFT OUTER JOIN "groups" ON "groups"."id" = "group_users"."group_id" 
  LEFT OUTER JOIN "user_stats" ON "user_stats"."user_id" = "directory_items"."user_id" 
WHERE 
  "directory_items"."period_type" = 1 
  AND "groups"."id" = 2898

このクエリは user_stats から何も取得しておらず、groups.id = 2898 は group_users.group_id = 2898 に置き換え可能です。つまり、クエリから groups テーブルと user_stats テーブルを削除できます。また、group_users には UNIQUE (user_id, group_id) が、users には PRIMARY KEY (id) が定義されているため、JOIN によって重複レコードは生成されません。したがって、以下の通り DISTINCT も削除できます。

SELECT 
  COUNT("directory_items"."id") 
FROM 
  "directory_items" 
  LEFT OUTER JOIN "users" ON "users"."id" = "directory_items"."user_id" 
  LEFT OUTER JOIN "group_users" ON "group_users"."user_id" = "users"."id" 
WHERE 
  "directory_items"."period_type" = 1 
  AND "group_users"."group_id"  = 2898

この最適化されたクエリの実行時間は 941700 ナノ秒で、17.49% の改善が見られます。

10

Discourse上で何らかのデータベーススキャナーを実行していますか？

11

Discourse でスペックを実行したところ、データベースから不要な JOIN が生成される遅いクエリ（類似のクエリと比較して 331729 ナノ秒）が検出されました。これは ser_badge.rb#L18 によって生成されています。

SELECT 
  COUNT(*) 
FROM 
  "badges" 
  INNER JOIN "user_badges" ON "badges"."id" = "user_badges"."badge_id" 
WHERE 
  "user_badges"."user_id" = 2112 
  AND (
    user_badges.badge_id IN (
      SELECT 
        id 
      FROM 
        badges 
      WHERE 
        enabled
    )
  ) 
  AND "badges"."id" = 1

このサブクエリは、以下に示すように単純な述語 badges.enabled に置き換えることができます。

SELECT 
  COUNT(*) 
FROM 
  "badges" 
  INNER JOIN "user_badges" ON "badges"."id" = "user_badges"."badge_id" 
WHERE 
  "user_badges"."user_id" = 2112 
  AND badges.enabled 
  AND "badges"."id" = 1

この最適化されたクエリは 267383 ナノ秒で実行され（19% の改善）