# Consultas lentas encontradas no Log do Banco de Dados

**URL:** https://meta.discourse.org/t/slow-queries-find-in-database-log/153023
**Category:** Feature
**Created:** [Maio 27, 2020, 8:43pm UTC](https://meta.discourse.org/t/slow-queries-find-in-database-log/153023 "2020-05-27T20:43:20Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![Peper](https://avatars.discourse-cdn.com/v4/letter/p/ecd19e/32.png) [@Peper](https://meta.discourse.org/u/Peper)
#### Post date: [Maio 27, 2020, 8:43pm UTC](https://meta.discourse.org/t/slow-queries-find-in-database-log/153023/1 "2020-05-27T20:43:20Z")

</div>

Executo specs no Discourse e encontro algumas consultas ineficientes no log de consultas, conforme mostrado a seguir:

1. DISTINCT desnecessário torna a consulta lenta [site\_settings\_controller.rb#L165](https://github.com/discourse/discourse/blob/61d8955fcb165d2a55760279928306b47fb98898/app/controllers/admin/site_settings_controller.rb#L165), como abaixo:

```plaintext
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
  )

```

Quando `categories.id` e `notification_level` têm valores específicos, devido à restrição UNIQUE (category\_id, user\_id) em `category_users` e PRIMARY KEY (id) em `categories`, tanto o `CROSS JOIN` quanto o `LEFT JOIN` não criam registros duplicados. Isso significa que podemos remover o `DISTINCT` para acelerar a consulta, conforme mostrado abaixo:

Esta consulta otimizada leva 4.532.166 nanossegundos (melhoria de 30%).

1. DISTINCT desnecessário em subconsulta torna a consulta lenta [search.rb#L523](https://github.com/discourse/discourse/blob/61d8955fcb165d2a55760279928306b47fb98898/lib/search.rb#L523):

```plaintext
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

```

O `DISTINCT(tt.topic_id)` é redundante e podemos removê-lo para acelerar a consulta, conforme mostrado abaixo:

Isso pode melhorar o desempenho desta consulta de 12.655.768 para 5.005.154 nanossegundos (melhoria de 60%).

1. DISTINCT desnecessário em subconsulta torna a consulta lenta [search.rb#L642]:

```plaintext
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

```

Semelhante ao caso anterior (código-fonte diferente), o `DISTINCT(tt.topic_id)` é redundante e podemos removê-lo para acelerar a consulta:

Isso pode melhorar o desempenho desta consulta de 23.659.762 para 21.030.593 nanossegundos (melhoria de 10%).