데이터베이스 로그에서 느린 쿼리 찾기

기여 기능
1

discourse에서 스펙을 실행하여 쿼리 로그에서 다음과 같이 비효율적인 쿼리를 발견했습니다:

  1. 불필요한 DISTINCT가 쿼리를 느리게 만듭니다. site_settings_controller.rb#L165
SELECT 
  DISTINCT users.id 
FROM 
  "users" CROSS 
  JOIN categories c 
  LEFT JOIN category_users cu ON users.id = cu.user_id 
  AND c.id = cu.category_id 
WHERE 
  (
    c.id = '3613'
    AND cu.notification_level IS NULL
  )

categories.id와 notification_level이 특정 값으로 지정된 경우, category_users의 UNIQUE(category_id, user_id)와 categories의 PRIMARY KEY(id) 때문에 cross join과 left join 모두 중복된 레코드를 생성하지 않습니다. 이는 아래에 표시된 바와 같이 DISTINCT를 제거하여 쿼리를 가속화할 수 있음을 의미합니다:
이 최적화된 쿼리는 4,532,166나노초가 소요됩니다(30% 개선).

  1. 서브쿼리 내 불필요한 DISTINCT가 쿼리를 느리게 만듭니다. search.rb#L523
SELECT 
  "posts".* 
FROM 
  "posts" 
  JOIN (
    SELECT 
      *, 
      row_number() over() row_number 
    FROM 
      (
        SELECT 
          topics.id, 
          min(posts.post_number) post_number 
        FROM 
          "posts" 
          INNER JOIN "post_search_data" ON "post_search_data"."post_id" = "posts"."id" 
          INNER JOIN "topics" ON "topics"."id" = "posts"."topic_id" 
          AND ("topics"."deleted_at" IS NULL) 
          LEFT JOIN categories ON categories.id = topics.category_id 
        WHERE 
          ("posts"."deleted_at" IS NULL) 
          AND "posts"."post_type" IN (1, 2, 3) 
          AND (topics.visible) 
          AND (
            topics.archetype <> 'private_message'
          ) 
          AND (
            topics.id IN (
              SELECT 
                DISTINCT(tt.topic_id) 
              FROM 
                topic_tags tt 
              WHERE 
                tt.tag_id in (
                  SELECT 
                    tag_id 
                  FROM 
                    tag_group_memberships 
                  WHERE 
                    tag_group_id = 504
                )
            )
          ) 
          AND (
            categories.id NOT IN (
              SELECT 
                categories.id 
              WHERE 
                categories.search_priority = 1
            )
          ) 
          AND (
            (categories.id IS NULL) 
            OR (NOT categories.read_restricted)
          ) 
        GROUP BY 
          topics.id 
        ORDER BY 
          MAX(posts.created_at) DESC 
        LIMIT 
          6 OFFSET 0
      ) xxx
  ) x ON x.id = posts.topic_id 
  AND x.post_number = posts.post_number 
WHERE 
  ("posts"."deleted_at" IS NULL) 
ORDER BY 
  row_number

DISTINCT(tt.topic_id)는 중복되어 있으며, 아래에 표시된 바와 같이 이를 제거하여 쿼리를 가속화할 수 있습니다:
이 쿼리의 성능을 12,655,768에서 5,005,154로 개선할 수 있습니다(60% 개선).

  1. 서브쿼리 내 불필요한 DISTINCT가 쿼리를 느리게 만듭니다. [search.rb#L642]
SELECT 
  "posts".* 
FROM 
  "posts" 
  JOIN (
    SELECT 
      *, 
      row_number() over() row_number 
    FROM 
      (
        SELECT 
          topics.id, 
          posts.post_number 
        FROM 
          "posts" 
          INNER JOIN "post_search_data" ON "post_search_data"."post_id" = "posts"."id" 
          INNER JOIN "topics" ON "topics"."id" = "posts"."topic_id" 
          AND ("topics"."deleted_at" IS NULL) 
          LEFT JOIN categories ON categories.id = topics.category_id 
        WHERE 
          ("posts"."deleted_at" IS NULL) 
          AND "posts"."post_type" IN (1, 2, 3) 
          AND (topics.visible) 
          AND (
            topics.archetype <> 'private_message'
          ) 
          AND (
            topics.category_id IN (3715)
          ) 
          AND (
            topics.id IN (
              SELECT 
                DISTINCT(tt.topic_id) 
              FROM 
                topic_tags tt, 
                tags 
              WHERE 
                tt.tag_id = tags.id 
                AND lower(tags.name) IN ('lunch')
            )
          ) 
          AND (
            (categories.id IS NULL) 
            OR (NOT categories.read_restricted)
          ) 
        ORDER BY 
          posts.like_count DESC 
        LIMIT 
          6 OFFSET 0
      ) xxx
  ) x ON x.id = posts.topic_id 
  AND x.post_number = posts.post_number 
WHERE 
  ("posts"."deleted_at" IS NULL) 
ORDER BY 
  row_number

이전 항목과 유사하게(소스 코드는 다름) DISTINCT(tt.topic_id)는 중복되어 있으며, 이를 제거하여 쿼리를 가속화할 수 있습니다:
이 쿼리의 성능을 23,659,762에서 21,030,593으로 개선할 수 있습니다(10% 개선).