Forums PostgreSQL.fr

Pascal P

Membre

Optimisation de recherche textuelle

Bonjour, travaillant actuellement sur de l'indexation textuelle sous PostgreSQLje souhaitais vous faire part de plusieurs questions pour optimiser les performances. Mais avant, une petite présentation de là où j'en suis.

La table est organisée ainsi :

nom       |  type
----------+-----------
id        |  bigserial
title     |  character varying (100)
plainText |  character varying (50000)
lexemes   |  tsvector

La table a été remplie à partir de documents textes : le titre est stocké dans le champ "title" tandis que le contenu du fichier est stocké dans le champ "plainText". Pour remplir la colonne "lexemes", j'ai fait ceci :

UPDATE "Base" SET "lexemes" = setweight(to_tsvector('french', coalesce(title,'')), 'A') || setweight(to_tsvector('french', coalesce("plainText",'')), 'B');

Un index GIN a été placé sur la colonne "lexemes". Pourquoi GIN ? Dans le site en développement, l'important est d'avoir des résultats rapidement, GIN semblait donc mieux adapté à GiST.

La table a été remplie progressivement (100'000, 200'000... puis 1'000'000 maintenant). Je m'intéresse principalement à la récupération du nombre de résultats (donc un COUNT), et à la récupération des 50 lignes les mieux classées (utilisation de ts_rank_cd). Je travaille actuellement en local, avec PostgreSQL installé sur mon poste (donc utilisé également pour d'autre tâches) :
Windows 7
Intel i5 760 (2.80GHz)
4Go RAM
PostgreSQL 9.0.3

J'ai modifié le fichier postgresql.conf de la manière suivante :
shared_buffers = 512MB
work_mem = 20MB
effective_cache_size = 2GB
J'avais entendu parler d'un gin_fuzzy_search_limit, mais apparemment ce paramètre n'existe plus en version 9.0 ?

Voilà en ce qui concerne la présentation. Les problèmes sont les suivants :

Question 1 : Lorsque je recherche un terme pour la première fois, la requête prend beaucoup de temps (plusieurs dizaines de secondes), alors que les fois suivantes elle est bien plus courte (100ms à 1s pour les tests effectués). Je me doute bien qu'il y a une histoire de cache là-dessous, mais il n'y a pas moyen d'accélérer la première exécution ?

SELECT id, ts_rank_cd(lexemes, query, 32) AS rank FROM "Base", plainto_tsquery('french', 'mot') query
WHERE lexemes @@ query ORDER BY rank DESC LIMIT 50

1ère exécution : http://explain.depesz.com/s/czT
2ème exécution : http://explain.depesz.com/s/Swn

Question 2 : L'autre problème c'est que j'ai certains termes dont la recherche met plusieurs minutes (2min20 dans mon test) à retourner des résultats, et ce peut importe le nombre de fois que la requête est exécutée. Bon, la recherche retourne beaucoup de résultats, le cas ne se produira probablement pas en production, mais si l'étude de ce cas peut permettre d'augmenter la rapidité de toute les recherches c'est déjà ça de pris !

Voici le EXPLAIN ANALYZE de la requête en question : http://explain.depesz.com/s/41X

Voilà j'espère avoir été clair et avoir fourni les bonnes informations. J'espère que vous pourrez m'aider à optimiser ma base.

Je vous remercie d'avance.

Marc Cousin

Membre

Re : Optimisation de recherche textuelle

Question 1 : le problème, c'est que pour calculer le 'rank', postgresql est obligé d'aller chercher tous les enregistrements de la table correspondant à la clause where, puis appliquer rank dessus, trier, et vous ramener les 50 enregistrements que vous voulez. D'où les problèmes de performance. Il va y avoir une nouveauté de ce point de vue dans la version 9.1, si vous utilisez GIST et non GIN.

Même problème pour la question 2 en fait. La recherche plein texte dans postgres, pour le moment, ne marche vraiment bien que si la clause where est assez restrictive. L'index n'arrive pas à ramener les données triées par rank directement.

---

Marc.

Pascal P

Membre

Re : Optimisation de recherche textuelle

Le problème est récupérer tous les résultats sans tri et sans rank, puis faire des calculs par derrière (Java), ça va également prendre du temps, je ne suis pas sûr d'y gagner par rapport à la méthode utilisée actuellement.
De plus, dans tous les cas (avec ou sans tri, avec ou sans rank), le temps de première exécution d'une requête est trop important. N'y a-t'il pas d'autres réglages qui pourraient améliorer ça ? Ce genre de requête va être un élément central du site, et les mots clés que les utilisateurs vont entrer vont être très variés, donc c'est vraiment la première exécution qui doit être rapide (le reste, c'est du bonus).

Je suis déjà en train de voir la personnalisation des dictionnaires pour réduire le nombre de lexemes, mais je ne pense pas que ça soit suffisant.

gleu

Administrateur

Re : Optimisation de recherche textuelle

Difficile à dire sans en savoir plus. Toutes les techniques d'améliorations des performances des requêtes sont utilisables. Ça peut passer par exemple par un cache plus grand, plus de mémoire, etc. La question est trop vaste pour pouvoir y répondre correctement.

---

Guillaume.