Query lente in Discourse

Sviluppatore
1

Dai log del database rileviamo alcune query con un ORDER BY non necessario in una subquery generata da user.rb#L379. Una di esse è la seguente:

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

L’uso di ORDER BY nella subquery è privo di significato e può essere rimosso per accelerare la query. Secondo i nostri test, ciò può migliorare le prestazioni della query del 18%-90% (a seconda del tempo speso nell’ordinamento nella subquery).

2

Quali sono i benchmark reali, in millisecondi?

3

Per questo esempio, rimuovere ORDER BY può ridurre il tempo di query da 4711878 nanosecondi a 585849 nanosecondi.

4

Eseguiamo gli spec in Discourse e dal database rileviamo una query lenta (6480259 nanosecondi, rispetto a una query simile) con un DISTINCT non necessario generato da
site_settings_controller.rb#L141, come segue:

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 tags.id e notification_level hanno valori specificati, grazie all’indice UNIQUE (tag_id, user_id, notification_level) su tag_users e alla PRIMARY KEY(id) su tags, né il CROSS JOIN né il LEFT JOIN generano record duplicati. Ciò significa che possiamo rimuovere DISTINCT per accelerare la query.
Questa query ottimizzata impiega 4538891 nanosecondi (miglioramento del 30%).

6

Dal Database Log rileviamo una query lenta (6064379 nanosecondi) con molte subquery e union generate da group.rb#L112, come segue:

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

Ovviamente è equivalente alla query riportata di seguito:

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

Questa query ottimizzata impiega 378062 nanosecondi (miglioramento del 93%).

7

Come fa ad essere equivalente? Stai cercando gruppi senza controlli sul livello di visibilità.

8

Mi scuso, la query riscritta corretta dovrebbe essere

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 sottoquery con il predicato visibility_level = 0,1,2 può essere sostituita da groups.visibility_level IN (0, 1, 2), e la sottoquery con il predicato visibility_level = 3,4 può essere sostituita da OR (groups.visibility_level IN (3,4) and gu.owner).

9

Dal database rileviamo una query lenta (1141257 nanosecondi) con JOIN e DISTINCT non necessari, generata da directory_items_controller.rb, come segue:

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

Non ha recuperato dati da user_stats e groups.id = 2898 può essere sostituito da group_users.group_id = 2898, il che significa che possiamo rimuovere le tabelle groups e user_stats dalla query. Allo stesso tempo, grazie all’indice UNIQUE (user_id, group_id) in group_users e alla PRIMARY KEY (id) in users, il JOIN non crea record duplicati, quindi possiamo anche rimuovere DISTINCT dalla query, come mostrato di seguito:

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

Questa query ottimizzata impiega 941700 nanosecondi (miglioramento del 17,49%).

10

Stai eseguendo qualche tipo di scanner di database su Discourse?

11

Eseguiamo i test in Discourse e dal Database rileviamo una query lenta (331729 nanosecondi, rispetto a una query simile) con un JOIN non necessario generato da
ser_badge.rb#L18, come segue:

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

Questa subquery può essere sostituita da un semplice predicato badges.enabled, come mostrato di seguito:

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

Questa query ottimizzata impiega 267383 nanosecondi (miglioramento del 19%).