Optimisation requête plpgsql

Je sollicite votre aide pour l'optimisation d'une requête plpgsql

Cette requête affiche les personnes d'une région avec leur nombre d'enfants.

C'est une requête dynamique et très longue je ne peux pas la mettre sur ce post et je m'en excuse.

Actuellement le temps d'exécution de cette requête est de 2,375 sec avec un affichage par page limité à 10 personnes .

J'ai trouvé ce temps très long. J'ai donc fait un EXPLAIN ANALYSE de cette fonction et voici les résultats.

Code:

---

Function Scan on fonc_membre_region (cost=0.00..12.50 rows=1000 width=532) (actual time=2378.003..2378.013 rows=10 loops=1)

---

Si je comprends bien cette analyse, la requête scan 1000 lignes ???

J'ai essayé de comprendre pour quoi et je me suis rendu compte que j'ai un critère sur le nombre d'enfants qui se présente comme ça :

Code:

---

WHERE membre_nombre_enfant >=0

---

la requête est plus rapide lorsqu'elle exécute :

Code:

---

WHERE membre_nombre_enfant =0

---

ou

Code:

---

WHERE membre_nombre_enfant =3

---

ou

Code:

---

WHERE membre_nombre_enfant >=3

---

Comment selon vous optimiser la requête quand on utilise un critère ">="

Code:

---

WHERE membre_nombre_enfant >=0

---

ou BETWEEN

Code:

---

WHERE membre_nombre_enfant BETWEEN 0 AND 4

---

C'est cette fourchette qui est consommatrice de ressource et de temps.

Merci à tous

Est-ce que les statistiques de ta table sont à jour (commande VACUUM) ?
Tu as des indexes sur ta table ?
Le temps d'exécution de la requête peut augmenter avec le nombre de lignes renvoyées (temps du fetch ou d'utilisation de l'index pour chaque ligne à retourner)

Oui j'ai des index sur la table, mais je me rends compte que je suis assez déçu des perfs de "postgres".

Quand les conditions dans les WHERE sont des egalités "=" ça fonctionne très rapidement, mais quand on utilise des between ou des "<" ">" alors là elle s'effondre très rapidement...

Et je n'ai que 8000 enregistrements et j'en retourne 10 à la fois...

maintenant que j'ai installé la même base en local je cherche ou optimiser...mais je suis "un peu" déçu...

J'avais l'habitude bosser avec mysql et question perfs ça n'a rien à voir...mais bon je veux réussir à l'optimiser cette base et il faut que j'y arrive...

Mais tu compares des requêtes qui ne renvoient pas le même nombre de lignes peut-être (combien de lignes tu as avec "where =" et combien avec "where beetween" ? Cela peut influencer, par exemple s'il doit utiliser un index pour ramener 1 ligne, ou bien plusieurs lignes
Tu as calculé les stats de ta table ?
C'est peut-être aussi le temp du fetch (affichage du résultat) qui est long ?
Tu as essayé de faire "select count(*)" au lieu de "select" pour ne pas avoir à tenir compte du temps d'affichage du résultat ?
Je ne connais pas bien Mysql mais c'est un peu rapide de faire des comparaisons de perfs et des conclusions sur un exemple peut-être optimisé d'un côté et pas de l'autre ...

Oui je suis d'accord, je ne jette pas la pierre sur postgres... c'était juste une première impression...

J'ai moins de lignes avec "Where =" que "Where between" c'est certain...puisque c'est une fourchette...mais avec 8000 enregistrements c long . Je crains quand il y en aura 100 000.

Comment obtient-on les stats ? Comment régler le fetch ?

je ne peux pas faire un select count(*) car il faudrait que je modifie ma requête car j'ai des message d'erreur à l'exécution...des champs qui doivent apparaitrent dans un GROUP BY

merci !

pour calculer les stats, regarde la commande VACUUM en SQL ou vacuumdb en Unix (ou DOS si c'est sur ta base migrée ;))
pour tes temps d'exécutions je disais juste que si tu lances les requêtes depuis l'utilitaire psql, une partie du temps peut être due à l'affichage sur l'écran et non pas au temps réel que postgresql met à exécuter la requête, c'est pour ça qu'avec un select count(*), ça t'aurait évité l'affichage du résultat

je lance les requêtes dans pgadmin et pas dans les pages web... Je peux pas faire un select count(*)...

Quand aux stats avec le VACUUM fait sur la base c'est une longue liste et je n'y comprends rien...

Autre question, selon toi il vaut mieux créer un index sur plusieurs colonnes ou un index pour chaque colonne ?

Bonjour,

as-tu placé des indexes sur les clés étrangères, pour accélerer les jointures ?

Sinon, pour le choix indexes multiples/index multicolonnes, cela dépend... La documentation expose les paramètres à prendre en considération pour le choix de l'un ou l'autre.

Oui c'est fait...mais c'est pas les jointures qui posent pb c'est les plages de recherche between ou > et <

Oui en fait dans la documentation sur les index de plusieurs colonnes on retient qu'il faut faire des tests et qu'il n'y a pas de règle.

Donc tout est empirique...

Citation:

---

Envoyé par viny

Donc tout est empirique...

---

C'est ce qui fait de l'optimisation un art... ;)

Plus sérieusement, l'optimiseur de Postgres emploie des algorithmes génétiques pour établir une planification optimale, qui dépendent eux-mêmes largement des statistiques disponibles sur les données, il est donc difficile d'établir à l'avance quelle sera la meilleure approche.