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를 제거하면 쿼리 시간이 4,711,878나노초에서 585,849나노초로 줄어듭니다.

4

Discourse에서 스펙을 실행하는 중, Database에서 비슷한 쿼리와 비교했을 때 느린 쿼리(6480259 나노초)를 발견했습니다. 이 쿼리는 불필요한 DISTINCT를 생성하며, 원인은 다음과 같습니다:

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
  )

tags.id와 notification_level이 특정 값으로 지정된 경우, tag_users의 UNIQUE (tag_id, user_id, notification_level) 제약 조건과 tags의 PRIMARY KEY(id) 때문에 cross join과 left join 모두 중복 레코드를 생성하지 않습니다. 따라서 DISTINCT를 제거하여 쿼리를 가속화할 수 있습니다.
이 최적화 쿼리는 4538891 나노초가 소요되며(30% 개선)됩니다.

6

데이터베이스 로그에서 group.rb#L112에서 생성된 여러 서브쿼리와 유니언을 포함하는 느린 쿼리(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

Database에서 불필요한 JOIN과 DISTINCT로 인해 발생하는 느린 쿼리(1141257 나노초)를 발견했습니다. 해당 쿼리는 directory_items_controller.rb에서 생성되며 다음과 같습니다.

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

디스코스에서 어떤 종류의 데이터베이스 스캐너를 실행 중이신가요?

11

Discourse에서 스펙을 실행한 결과, Database에서 유사한 쿼리에 비해 느린 쿼리(331729나노초)가 발견되었습니다. 이 쿼리는 불필요한 JOIN을 포함하고 있으며, 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% 개선)됩니다.