Forums PostgreSQL.fr

jhashe

Membre

Optimisation d'une requête TSearch

Bonjour,

Je vous sollicite beaucoup en ce moment, mais, en attendant d'avoir mis en place une solution de répartition de charge, je cherche à optimiser les requêtes les plus coûteuses de mon code actuel. La plupart sont généres par du code,d 'où leur aspect "systématique". J'ai bien avancé, avec parfois des évolutions spectaculaires. Il m'en reste cependant une, basée sur T-search, dont la durée d'exécution reste importante. Voici la requête:

SELECT ir_instrument_ead_noeud.id_ir_instrument, ir_instrument_ead_noeud.ir_inst_noeud_p_parent, ts_rank_cd(ir_inst_noeud_libelle_valeur_vecteur,r) AS score, ir_inst_noeud_valeur_brut FROM ir_instrument_ead_noeud, to_tsquery('etat & civil') AS r WHERE ir_instrument_ead_noeud.id_ir_instrument IN (1500,1499,610,606,582,605,587,586,589,590,579,580,608,588,585,591,607,581) AND ir_inst_noeud_libelle_valeur_vecteur @@ r

Un explain donne le résultat suivant:
"Nested Loop  (cost=290.22..11652.27 rows=765 width=155)"
"  ->  Function Scan on to_tsquery r  (cost=0.00..0.01 rows=1 width=32)"
"  ->  Bitmap Heap Scan on ir_instrument_ead_noeud  (cost=290.22..11611.75 rows=3088 width=123)"
"        Recheck Cond: (ir_instrument_ead_noeud.ir_inst_noeud_libelle_valeur_vecteur @@ r.r)"
"        Filter: (ir_instrument_ead_noeud.id_ir_instrument = ANY ('{1500,1499,610,606,582,605,587,586,589,590,579,580,608,588,585,591,607,581}'::integer[]))"
"        ->  Bitmap Index Scan on idx_lib_val_vecteur  (cost=0.00..290.02 rows=3088 width=0)"
"              Index Cond: (ir_instrument_ead_noeud.ir_inst_noeud_libelle_valeur_vecteur @@ r.r)"

Ce que je comprends, c'est que PostgreSQL utilise l'index placé sur ir_inst_noeud_libelle_valeur_vecteur, ce qui est logique car c'est la partie la plus lourde de la requête. Néanmoins, je pensais pouvoir l'optimiser en créant un nouvel index portant sur les colonnes id_ir_instrument (integer) et ir_inst_noeud_valeur_vecteur (tsvector), ce qui aurait énormément limité la liste des enregistrements à parcourir. Malheureusement cela semble impossible. PostgreSQL me retourne en effet systématiquement le message suivant:

ERREUR: la ligne index requiert 11312 octets, la taille maximum est 8191
État SQL :54000

Avez-vous une piste à me conseiller ?

Par avance, merci

PS: Je suis toujours en PostgreSQL 8.3.1

gleu

Administrateur

Re : Optimisation d'une requête TSearch

Deux pistes:

* mettre à jour en 8.3.8 (les index gist sont souvent améliorés/débuggés)
* créer un index sur id_ir_instrument seul (ça pourrait lui permettre de faire deux bitmap index scan et un bitmap and, ce qui pourrait accélérer les choses)

---

Guillaume.

jhashe

Membre

Re : Optimisation d'une requête TSearch

Merci pour votre réponse.

J'ai déjà un index sur id_ir_instrument, mais il n'est visiblement pas utilisé.
Quant à mettre à jour PostgreSQL, je le ferai sans doute, mais plutôt pour basculer en 8.4. Il faut cependant qu'au préalable je mette en place ma réplication pour pouvoir effectuer cette opération en limitant l'interruption de service.

gleu

Administrateur

Re : Optimisation d'une requête TSearch

Vous avez raison. Tant qu'à faire, autant profiter des bugs des anciennes versions

* 8.3.8: Fix poor choice of page split point in GiST R-tree operator classes (Teodor)

* 8.3.6: Empêche le parcours synchrone lors de la construction d'un index GIN (Tom). Comme GIN est optimisé pour insérer les lignes dans l'ordre croissant des TID, l'utilisation d'un parcours synchrone pourrait ralentir la construction par un facteur de trois, voire plus.

* 8.3.5: Correction d'une corruption d'index GiST. L'entrée d'index marquée comme « morte » après une suppression n'était pas la bonne (Teodor). Cela peut avoir comme conséquence des échecs dans les recherches par index pour des lignes qu'elles auraient dû trouver.

* 8.3.4: Correction d'un affichage dupliqué des lignes lors d'un parcours d'index GiST (Teodor)

* 8.3.2: Correction de la fonction de comparaison GiST pour tsquery (Teodor)

Je tiens à rappeler que le passage d'une version mineure à une autre version mineure (de la même branche) demande seulement de mettre à jour les exécutables et de redémarrer le serveur PostgreSQL.

Je comprends que passer de 8.3 à 8.4 demande du temps, mais appliquer une version corrective de PostgreSQL ne devrait même pas être discuté. Cela devrait être fait sans délai (surtout que la dernière version corrective date du 9 septembre... si un gros problème avait été découvert, il serait déjà connu).

Pour en revenir à votre problème, s'il n'utilise pas l'index sur id_ir_instrument, c'est qu'il ne doit pas être assez sélectif pour votre filtre. Que vaut votre paramètre random_page_cost ? et quelles sont les statistiques disponibles pour la colonne id_ir_instrument ?

---

Guillaume.

jhashe

Membre

Re : Optimisation d'une requête TSearch

C'est pas gentil de se moquer ;-)
Pour ceux qui, comme moi, n'osent pas toucher à un système en production, je me suis donc jeté à l'eau.

Mon SGBD est sur une distribution CentOS (un RedHat like). J'y ai installé un RPM mettant la liste des sources yum à jour (rpm -ivh pgdg-centos-8.3-6.noarch.rpm), puis j'ai juste fait un "yum update postgresql". Le système a automatiquement chargé les nouveaux binaires, résolu les dépendances, et redémarré PostgreSQL. Je n'ai même pas réussi à mesurer le temps d'indisponibilité (marginal dans mon cas). Et tout marche!!!

PS: En ce qui concerne la requête soumise en début de ce thread, le gain d'exécution est négligeable. Mais je ne regrette pas pour autant d'avoir effectué cette opération.

Encore merci pour tout,

Jérôme

gleu

Administrateur

Re : Optimisation d'une requête TSearch

C'est pas gentil de se moquer ;-)

Désolé... c'était pas méchant, c'était juste très (trop) tentant

En ce qui concerne la requête soumise en début de ce thread, le gain d'exécution est négligeable.

Dans ce cas, il faut continuer à chercher pourquoi. Donc j'en reviens à mes questions précédentes : [...] s'il n'utilise pas l'index sur id_ir_instrument, c'est qu'il ne doit pas être assez sélectif pour votre filtre. Que vaut votre paramètre random_page_cost ? et quelles sont les statistiques disponibles pour la colonne id_ir_instrument ?

---

Guillaume.

jhashe

Membre

Re : Optimisation d'une requête TSearch

La valeur du random_page_cost est celle définie par défaut (4.0 semblerait-il). En tous cas, je ne l'ai pas modifiée (la ligne est en commentaire dans le postgresql.conf)
En ce qui concerne les statistiques de la colonne id_ir_instrument, j'ai les valeurs suivantes (récupérées dans pgAdmin); je les donne mais je n'y comprends pas grand chose :-)

Division par zéro: 0
Largeur moyenne: 4
Valeurs distinctes: 106
Corrélations -0.499622

gleu

Administrateur

Re : Optimisation d'une requête TSearch

Essayer déjà de diminuer random_page_cost. 2.0 par exemple. Et tester de nouveau votre requête.

---

Guillaume.