Consultas lentas no Discourse

Dev
1

No log do banco de dados, encontramos algumas consultas com ORDER BY desnecessário em subconsultas geradas por user.rb#L379. Uma delas é a seguinte:

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

O ORDER BY na subconsulta não tem sentido e pode ser removido para acelerar a consulta. De acordo com nossos testes, isso pode melhorar o desempenho da consulta em 18% a 90% (dependendo do tempo gasto na ordenação na subconsulta).

2

Quais são os benchmarks reais, em milissegundos?

3

Para este exemplo, remover a cláusula ORDER BY pode reduzir o tempo de consulta de 4711878 nanosegundos para 585849 nanosegundos.

4

Executamos testes no Discourse e, no banco de dados, identificamos uma consulta lenta (6.480.259 nanossegundos) em comparação com consultas semelhantes, devido ao DISTINCT desnecessário gerado por site_settings_controller.rb#L141, conforme abaixo:

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
  )

Quando os valores de tags.id e notification_level são especificados, devido à restrição UNIQUE (tag_id, user_id, notification_level) em tag_users e PRIMARY KEY(id) em tags, tanto o CROSS JOIN quanto o LEFT JOIN não geram registros duplicados. Isso significa que podemos remover o DISTINCT para acelerar a consulta.
Essa consulta otimizada leva 4.538.891 nanossegundos (melhoria de 30%).

6

No log do banco de dados, encontramos uma consulta lenta (6064379 nanossegundos) com várias subconsultas e uniões geradas por group.rb#L112, conforme abaixo:

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

Obviamente, isso é equivalente à consulta abaixo:

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

Essa consulta otimizada leva 378062 nanossegundos (melhoria de 93%).

7

Como é igual? Você está procurando grupos sem verificações de nível de visibilidade.

8

Desculpe, a consulta reescrita correta deve ser:

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

A subconsulta com o predicado visibility_level = 0,1,2 pode ser substituída por groups.visibility_level IN (0, 1, 2), e a subconsulta com o predicado visibility_level = 3,4 pode ser substituída por OR (groups.visibility_level IN (3,4) and gu.owner).

9

No banco de dados, identificamos uma consulta lenta (11.412.570 nanosegundos) com JOIN e DISTINCT desnecessários, gerados pelo directory_items_controller.rb, conforme abaixo:

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

Nenhuma informação foi recuperada de user_stats, e a condição groups.id = 2898 pode ser substituída por group_users.group_id = 2898. Isso significa que podemos remover as tabelas groups e user_stats da consulta. Além disso, devido à restrição UNIQUE (user_id, group_id) em group_users e PRIMARY KEY (id) em users, o JOIN não criará registros duplicados. Portanto, também podemos remover o DISTINCT da consulta, conforme mostrado abaixo:

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

Essa consulta otimizada leva 9.417.000 nanosegundos (melhoria de 17,49%).

10

Você está executando algum tipo de scanner de banco de dados no Discourse?

11

Executamos os testes no Discourse e, no banco de dados, identificamos uma consulta lenta (331.729 nanossegundos) em comparação com consultas similares, causada por um JOIN desnecessário gerado por ser_badge.rb#L18, conforme abaixo:

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

Essa subconsulta pode ser substituída por uma condição simples badges.enabled, como mostrado abaixo:

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

Essa consulta otimizada leva 267.383 nanossegundos (melhoria de 19%).