Améliorer les performances d'une requête avec agrégats

Bonjour,

J'ai un soucis avec une requête qui prend trop de temps pour être utilisable. Il me semble que c'est un soucis classique, mais je n'ai pas trouvé de réponse claire, donc merci pour votre aide.

La requête en question ne concerne qu'une table :

Code:

---

1 2 3

SELECT trials, block, sum(omission), sum(comission), avg(response_time) FROM temp GROUP BY trials, block;

---

Elle consiste comme vous le voyez à deux some et une moyenne, groupés sur deux autre variables, et sans filtres. Les colonnes omission et comission sont du type integer, response_time float, trials et block de type varchar. Il y a 500 000 lignes dans la table, ce qui logiquement représente au pire la moitié de ce qu'elle contiendra pour finir.

Dans ces conditions la requête dure 24 secondes. Si je retire une des deux variables trials ou block de la requête, cela prend moins d'une seconde.

Est-il possible d'améliorer cela ? Je peux bien sûr calculer ces valeurs et les stocker dans une table séparée, mais j'aimerai vraiment savoir s'il est possible d'accélérer ces performances. Voici le plan de requête :

Code:

---

1 2 3 4 5 6 7 8

QUERY PLAN -------------------------------------------------------------------------------------------------------------------------------- GroupAggregate (cost=113849.63..121261.63 rows=40000 width=76) (actual time=19115.558..24052.907 rows=82803 loops=1) -> Sort (cost=113849.63..114968.30 rows=447467 width=76) (actual time=19115.515..23454.204 rows=447467 loops=1) Sort Key: trials_trial_psytool_reactionti_id, block -> Seq Scan on temp_palp (cost=0.00..32084.67 rows=447467 width=76) (actual time=0.049..461.138 rows=447467 loops=1) Total runtime: 24096.526 ms (5 lignes)

---

Merci

Combien vaut work_mem? Il faut l'augmenter si trop faible pour avoir un tri en mémoire au lieu d'un tri sur disque.

Citation:

---

Envoyé par estofilo

Combien vaut work_mem? Il faut l'augmenter si trop faible pour avoir un tri en mémoire au lieu d'un tri sur disque.

---

Merci pour cette remarque. La valeur de work_mem était celle par défaut, ie 1MB. J'ai changé la valeur pour 50MB, et effectivement la requête ne prend maintenant que 1,5 s. ( effectivement, j'avais déjà détecter qu'en utilisant un LIMIT, la requête était rapide jusqu'à un certain seuil de quantité de résultats )

Je suppose qu'il faut que je réfléchisse murement à la valeur à choisir. Le serveur a 16G de ram, est-ce que 50M parait une valeur plausible ? J'imagine que cela dépend de l'utilisation, mais la charge actuelle ne représente pas plus de 5 utilisateur simultanés, accédant aux données au travers d'une interface web, et donc n'effectuant probablement pas plus d'une action à la fois.
Existe-t-il des outils permettant de suivre la charge et détecter lorsque l'on atteint les limites d'utilisation et qu'il serait judicieux de modifier la configuration de postgresql ?

Merci encore pour la réponse.

Citation:

---

Le serveur a 16G de ram, est-ce que 50M parait une valeur plausible

---

Oui si c'est rapide avec 50M pourquoi se priver d'utiliser de la mémoire disponible? S'il n'y a pas beaucoup de sessions simultanées, ça ne posera jamais aucun problème.

Pour l'audit, il y a un paramètre log_temp_files à partir de 8.3 qui permet de logger l'usage de disque temporaire, ce qui peut aider à détecter que work_mem n'est pas suffisant.

Créez l'index suivant :

Code:

---

1 2	CREATE INDEX X_tmp_tribloomicomres ON temp (trials, block, omission, comission, response_time);

---

A +