Langsame Abfragen im Datenbank-Log gefunden

Beitragen Merkmal
1

Ich führe Tests in Discourse durch und stelle im Query-Log einige ineffiziente Abfragen fest, wie folgt:

  1. Unnötiges DISTINCT verlangsamt die Abfrage 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
  )

Wenn categories.id und notification_level konkrete Werte haben, erzeugen aufgrund der UNIQUE-Einschränkung (category_id, user_id) in category_users und des PRIMARY KEYs (id) in categories sowohl der CROSS JOIN als auch der LEFT JOIN keine duplizierten Datensätze. Das bedeutet, dass wir DISTINCT entfernen können, um die Abfrage zu beschleunigen, wie unten gezeigt:
Diese optimierte Abfrage benötigt 4.532.166 Nanosekunden (Verbesserung um 30 %).

  1. Unnötiges DISTINCT in einer Unterabfrage verlangsamt die Abfrage 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) ist überflüssig und kann entfernt werden, um die Abfrage zu beschleunigen, wie unten gezeigt:
Die Performance dieser Abfrage verbessert sich von 12.655.768 auf 5.005.154 Nanosekunden (Verbesserung um 60 %).

  1. Unnötiges DISTINCT in einer Unterabfrage verlangsamt die Abfrage [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

Ähnlich wie beim vorherigen Fall (andere Quellcodezeile) ist DISTINCT(tt.topic_id) überflüssig und kann entfernt werden, um die Abfrage zu beschleunigen:
Die Performance dieser Abfrage verbessert sich von 23.659.762 auf 21.030.593 Nanosekunden (Verbesserung um 10 %).