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.