Comment optimiser ma requête

Version imprimable

Bonjour,

La requête principale de mon site prend pas mal de temps, plus que ce que je voudrais en tout cas.
Il semble qu'un sort soit à l'origine de cette longueur... sort qui se fait pour moi lors du group by.

Voici un exemple de ma requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
SELECT DISTINCT id2, sclr1 
FROM 
(
SELECT COUNT(DISTINCT c0_.id) AS sclr0, 
  CASE WHEN COUNT(c0_.id) > 0 THEN MAX(c0_.created_at) ELSE i1_.updated_at END AS sclr1, 
  i1_.id AS id2, i1_.slug AS slug3, i1_.title AS title4, i1_.description AS description5, i1_.content AS content6, 
  i1_.is_published AS is_published7, i1_.is_removed AS is_removed8, i1_.created_at AS created_at9, i1_.updated_at AS updated_at10, 
  i1_.is_validated_by_admin AS is_validated_by_admin11, i1_.is_moderated AS is_moderated12, i1_.life AS life13, l2_.id AS id14, 
  l2_.nom AS nom15, l2_.min AS min16, l2_.max AS max17, l2_.niveau AS niveau18, l2_.population AS population19, 
  l2_.urlName AS urlname20, l2_.footer AS footer21, l2_.css AS css22, t3_.id AS id23, t3_.nom AS nom24, t3_.descriptif AS descriptif25, 
  t3_.is_moderated AS is_moderated26, t3_.ordre AS ordre27 
FROM idee i1_ 
INNER JOIN idee_localisation i4_ 
  ON i1_.id = i4_.idee_id 
INNER JOIN localisation l2_ 
  ON l2_.id = i4_.localisation_id 
INNER JOIN theme t3_ 
  ON i1_.theme_id = t3_.id 
LEFT JOIN commentaire c0_ 
  ON i1_.id = c0_.idee_id 
  AND (c0_.is_removed = false AND (c0_.life > 0 OR c0_.is_validated_by_admin = true)) 
WHERE 
  (i1_.life > 0 OR i1_.is_validated_by_admin = true)
  AND i1_.is_published = true 
  AND i1_.is_removed = false 
  AND l2_.min >= 0 
  AND l2_.max <= 36686 
GROUP BY i1_.id, i1_.slug, i1_.title, i1_.description, i1_.content, i1_.is_published, i1_.is_removed, i1_.created_at, i1_.updated_at, i1_.is_validated_by_admin, i1_.is_moderated, i1_.life, i1_.theme_id, i1_.user_id, t3_.id, t3_.nom, t3_.descriptif, t3_.is_moderated, t3_.ordre, l2_.id, l2_.nom, l2_.min, l2_.max, l2_.niveau, l2_.population, l2_.urlName, l2_.footer, l2_.css, l2_.parent_id 
ORDER BY sclr1 DESC
) dctrn_result 
ORDER BY sclr1 DESC 
LIMIT 12 
OFFSET 0

Mon explain analyze me donne ça :
http://explain.depesz.com/s/e9W1

Mais j'avoue que je sais pas trop comment l’interpréter, et surtout comment améliorer les choses.

Si vous avez une idée, je suis preneur.
Merci,
Pierre

Déjà commencez par supprimer tout ce qui ne vous sert à rien dans la requête.
J'ai fait quelques coupes franches, donc potentiellement le résultat n'est pas identique.

Essayez toutefois :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
    SELECT i1_.id AS id2
         , CASE WHEN COUNT(c0_.id) > 0 THEN MAX(c0_.created_at) ELSE i1_.updated_at END AS sclr1
      FROM idee              i1_ 
INNER JOIN idee_localisation i4_  ON i4_.idee_id = i1_.id
INNER JOIN localisation      l2_  ON l2_.id      = i4_.localisation_id 
 LEFT JOIN commentaire       c0_  ON c0_.idee_id = i1_.id
                                 AND NOT (c0_.is_removed)
                                 AND (c0_.life > 0 OR c0_.is_validated_by_admin)
     WHERE (i1_.life > 0 OR i1_.is_validated_by_admin)
       AND i1_.is_published
       AND NOT (i1_.is_removed)
       AND l2_.min >= 0 
       AND l2_.max <= 36686 
GROUP BY i1_.id, i1_.updated_at
ORDER BY sclr1 DESC 
   LIMIT 12 
  OFFSET 0;

16/02/2015, 13h04
pmithrandir

bonjour,

Je testerai pour voir ce que ça donne, mais je pense que ça sera pareil.
D'après ce que je lis, vous avez supprimé les éléments du select, ainsi que du group by.

Malheureusement, je ne suis pas en mesure de faire de même sur mon code (c'est une requête de pagination générée par un ORM qui prend la requête de base, et ajoute select distinct id dessus). À savoir, sur la page, j’exécute aussi cette même requête sans le select distinct, avec les mêmes problèmes de lenteur.

La seule chose que je peux faire, c'est de modifier l'esprit de ma requête (par exemple, le case va disparaître au profit d'une variable, je pense, à un moment).
Je peux aussi bien sûr mettre des index.

Là, mon gros problème, c'est que je ne comprends pas vraiment le explain de postgres. Il y a sûrement une logique, mais je ne pige pas... donc impossible de voir ce que je peux changer du coup (sous mysql, j'aurais eu 5 ou 6 lignes avec des index utilisés et le nombre d’éléments dans cet index ... facile à comprendre, ici, je ne sais pas).

Ok, on va sortir l'agrégat global et le faire de façon unitaire sur commentaire :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
with cte_commentaire as
(
  select idee_id
       , MAX(created_at)    as created_at_max
       , count(*)           as nb_commentaires
       , COUNT(DISTINCT id) as sclr0
    from commentaire
   where NOT (is_removed)
     AND (life > 0 OR is_validated_by_admin)
group by idee_id
)
    SELECT c0_.sclr0
         , CASE WHEN c0_.nb_commentaires > 0 THEN c0_.created_at_max ELSE i1_.updated_at END AS sclr1
         , i1_.id AS id2, i1_.slug AS slug3, i1_.title AS title4, i1_.description AS description5, i1_.content AS content6
         , i1_.is_published AS is_published7, i1_.is_removed AS is_removed8, i1_.created_at AS created_at9, i1_.updated_at AS updated_at10
         , i1_.is_validated_by_admin AS is_validated_by_admin11, i1_.is_moderated AS is_moderated12, i1_.life AS life13, l2_.id AS id14
         , l2_.nom AS nom15, l2_.min AS min16, l2_.max AS max17, l2_.niveau AS niveau18, l2_.population AS population19
         , l2_.urlName AS urlname20, l2_.footer AS footer21, l2_.css AS css22, t3_.id AS id23, t3_.nom AS nom24, t3_.descriptif AS descriptif25
         , t3_.is_moderated AS is_moderated26, t3_.ordre AS ordre27 
      FROM idee              i1_ 
INNER JOIN idee_localisation i4_  ON i4_.idee_id = i1_.id
INNER JOIN localisation      l2_  ON l2_.id      = i4_.localisation_id 
 LEFT JOIN cte_commentaire   c0_  ON c0_.idee_id = i1_.id
     WHERE (i1_.life > 0 OR i1_.is_validated_by_admin)
       AND i1_.is_published
       AND NOT (i1_.is_removed)
       AND l2_.min >= 0 
       AND l2_.max <= 36686 
  ORDER BY sclr1 DESC 
     LIMIT 12 
    OFFSET 0;

16/02/2015, 14h11
pmithrandir

Bonjour,

Je ne peux pas faire de sous-requêtes... l'ORM ne permet pas cela (en gros, je peux faire des join, group by, having, c'est à peu près tout).

De plus, les commentaires n'ont pas l'air de poser de problèmes dans la requête non ?
http://explain.depesz.com/s/e9W1

Il y a un problème ligne 6 et 7, mais je n'arrive pas à comprendre exactement ce que ça dit, de façon à trouver ce qui lui manque pour aller plus vite.

Il faut vraiment juste que je comprenne ce que posgtres m'affiche à ces lignes pour décider de la réponse appropriée. En gros, il me faut un traducteur de "postgres" à humain...

Pierre
16/02/2015, 14h22
Waldar

Citation:

Envoyé par pmithrandir

Mais j'avoue que je sais pas trop comment [...] améliorer les choses.

Citation:

Envoyé par pmithrandir

Je ne peux pas faire de sous requêtes... l'ORM ne permet pas cela. (en gros, je peux faire des join, group by, having, c'est a peu près tout)

Vous n'avez pas un problème de SQL en fait, vous avez un problème classique d'ORM : vous en utilisez un pour faire autre chose que select * from.
Vous pouvez le contourner en créant une vue avec le code SQL qui va bien, et mapper votre vue dans l'ORM.

Citation:

Envoyé par pmithrandir

De plus, les commentaires n'ont pas l'air de poser de problèmes dans la requête non ?

Pas directement, mais tous les agrégats portent sur des colonnes de la table commentaire.
Donc plutôt que de faire les jointures, récupérer les colonnes et faire un "gros" group by, je fais l'inverse : un "petit" group by sur les colonne nécessaires puis une simple jointure.

Citation:

Envoyé par pmithrandir

Il y a un problème ligne 6 et 7, mais je n'arrive pas à comprendre exactement ce que ca dit, de façon à trouver ce qui lui manque pour aller plus vite.

Simplement que le group by prend quinze secondes, ce qui est vraiment beaucoup vu qu'il n'y a que 90 lignes en entrée (même sur une machine du début du siècle, ça ne devrait pas dépasser le dixième de seconde).
16/02/2015, 14h34
pmithrandir

Une idée de comment améliorer ce group by ?
J'ai 3 VCPU + 4 GB de ram qui s'ennuient, et ça reste ultra lent...

Et puis 90 lignes, c'est vraiment rien... c'est ça qui m’énerve le plus. Je ne comprends pas comment ça peut être lent.

Après, c'est 15 millisecondes... pas 15 secondes... Mais en prod, ça me donne quand même 80% de mon temps total.
16/02/2015, 14h51
Waldar

Citation:

Envoyé par pmithrandir

Une idée de comment améliorer ce group by ?

Oui, ne pas le faire cf. ce que je vous ai proposé ci-dessus.

Ok pour les millisecondes, c'est plus convenable !
16/02/2015, 15h05
pmithrandir

Bonjour,

Je vais voir ce que je peux faire. La solution de la vue semble séduisante, mais elle me prendra un poil de temps...

Surtout, je peux la remplacer avantageusement par une colonne dans la table idée qui contient la date de dernière modification. Cette date étant calculée, je n'aurai plus à faire ma jointure sur la table commentaire, donc plus de group by non plus. Une simple requête count sur la table commentaire avec les 10 idées sélectionnées fera l'affaire.
Le seul problème, c'est que cette requête peut également être triée par nombre de commentaires... et là ça se complique si je les sors de là... mais je vais encore y réfléchir.
Au pire, il y a peut-être d'autres choses à changer avant cela.

Si quelqu'un a d'autres suggestions, je suis preneur.

Merci,
Pierre