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 : Sélectionner tout - Visualiser dans une fenêtre à part
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