Consultas lentas en Discourse

Desarrollador
1

En el registro de la base de datos encontramos algunas consultas con un ORDER BY innecesario en una subconsulta generada por user.rb#L379. Una de ellas es la siguiente:

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

El ORDER BY en la subconsulta carece de sentido y podemos eliminarlo para acelerar la consulta. Según nuestras pruebas, esto puede mejorar el rendimiento de la consulta entre un 18% y un 90% (dependiendo del tiempo perdido en la ordenación dentro de la subconsulta).

2

¿Cuáles son los valores reales de referencia, en milisegundos?

3

Para este ejemplo, eliminar ORDER BY puede reducir el tiempo de consulta de 4711878 nanosegundos a 585849 nanosegundos.

4

Ejecutamos las pruebas en Discourse y, desde la base de datos, detectamos una consulta lenta (6480259 nanosegundos, en comparación con consultas similares) causada por un DISTINCT innecesario generado en site_settings_controller.rb#L141, como se muestra a continuación:

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
  )

Cuando se especifican valores para tags.id y notification_level, debido a la restricción UNIQUE (tag_id, user_id, notification_level) en tag_users y la PRIMARY KEY (id) en tags, tanto el CROSS JOIN como el LEFT JOIN no generan registros duplicados. Esto significa que podemos eliminar el DISTINCT para acelerar la consulta.
Esta consulta optimizada tarda 4538891 nanosegundos (una mejora del 30 %).

6

Del registro de la base de datos, encontramos una consulta lenta (6064379 nanosegundos) con muchas subconsultas y uniones generadas por group.rb#L112, como se muestra a continuación:

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, esto es equivalente a la siguiente consulta:

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

Esta consulta optimizada tarda 378062 nanosegundos (una mejora del 93%).

7

¿En qué es igual? Estás buscando grupos sin verificaciones de nivel de visibilidad.

8

Lo siento, la consulta reescrita correcta debería 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

La subconsulta con el predicado visibility_level = 0,1,2 puede reemplazarse por groups.visibility_level IN (0, 1, 2), y la subconsulta con el predicado visibility_level = 3,4 puede reemplazarse por or (groups.visibility_level IN (3,4) and gu.owner).

9

Desde la base de datos se detectó una consulta lenta (1141257 nanosegundos) con JOIN y DISTINCT innecesarios generados por directory_items_controller.rb, como se muestra a continuación:

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

Esta consulta no recuperó nada de user_stats y groups.id = 2898 puede reemplazarse por group_users.group_id = 2898, lo que significa que podemos eliminar las tablas groups y user_stats de la consulta. Al mismo tiempo, debido a UNIQUE (user_id, group_id) en group_users y PRIMARY KEY (id) en users, el JOIN no generará registros duplicados, por lo que también podemos eliminar DISTINCT de la consulta, como se muestra a continuación:

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

Esta consulta optimizada tarda 941700 nanosegundos (mejora del 17.49 %).

10

¿Estás ejecutando algún tipo de escáner de bases de datos en Discourse?

11

Ejecutamos las pruebas en Discourse y, desde la base de datos, detectamos una consulta lenta (331729 nanosegundos) en comparación con consultas similares, causada por un JOIN innecesario generado por ser_badge.rb#L18, como se muestra a continuación:

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

Esta subconsulta puede reemplazarse por un predicado simple: badges.enabled, como se muestra a continuación:

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

Esta consulta optimizada tarda 267383 nanosegundos (una mejora del 19%).