Forums PostgreSQL.fr

Reya

Membre

Full text search et index GIN non utilisé

Boujour a tous,

je commence à me documenter et faire des test sur FTS. J'ai suivi les recommandations sur le site de postgres. Tout fonctionne très bien mais je rencontre une chose étrange.

Je me suis rendu compte que certaines recherches prenaient énormément de temps et d'autre étaient presque instantanées.

En utilisant un explain sur la requête je vois que l'index n'est pas utilisé sur certains mots ( donc je comprends les temps très long).
Par exemple :

select iddocument from documenttext where test @@ 'maint:*'

me donne en explain

"Seq Scan on documenttext  (cost=0.00..36903.93 rows=23484 width=8)"
"  Output: iddocument"
"  Filter: (test @@ '''maint'':*'::tsquery)"

alors que la même recherche avec 'main:*' ou 'mainte:*' donne :

"Bitmap Heap Scan on documenttext  (cost=10.28..30.16 rows=5 width=8)"
"  Output: iddocument"
"  Recheck Cond: (test @@ '''mainte'':*'::tsquery)"
"  ->  Bitmap Index Scan on idx_gin  (cost=0.00..10.27 rows=5 width=0)"
"        Index Cond: (test @@ '''mainte'':*'::tsquery)"

Ce que ne ne comprends vraiment pas c'est pourquoi sur certains mots l'index n'est pas utilisé? Il y a d'autres mots comme 'patient', 'q',... qui n'utilise pas l'index!!

Quelqu'un a-t-il la réponse et ce phénomène??

gleu

Administrateur

Re : Full text search et index GIN non utilisé

Le EXPLAIN n'est pas suffisant. Il nous faudrait plutôt le résultat d'un EXPLAIN ANALYZE.

D'autre part, avez-vous essayé de désactiver les parcours séquentiel dans le cas de la première requête et d'effectuer un EXPLAIN ANALYZE après coup ?

---

Guillaume.

Reya

Membre

Re : Full text search et index GIN non utilisé

Voici l'explain analyse de la requête :

"Seq Scan on documenttext  (cost=0.00..36900.00 rows=23517 width=8) (actual time=75.995..51585.983 rows=38924 loops=1)"
"  Output: iddocument"
"  Filter: (test @@ '''maint'':*'::tsquery)"
"Total runtime: 51622.895 ms"

En désactivant les parcours séquentiel:

"Bitmap Heap Scan on documenttext  (cost=33132.49..66468.72 rows=23517 width=8) (actual time=27.244..1903.225 rows=38924 loops=1)"
"  Output: iddocument"
"  Recheck Cond: (test @@ '''maint'':*'::tsquery)"
"  ->  Bitmap Index Scan on idx_gin  (cost=0.00..33126.61 rows=23517 width=0) (actual time=24.038..24.038 rows=38924 loops=1)"
"        Index Cond: (test @@ '''maint'':*'::tsquery)"
"Total runtime: 1933.457 ms"

En modifiant cette variables va t-il y a voir des perturbation pour d'autre requêtes? si la seule modification me permet d'avoir tout le temps de bon résultat et sans perturbé le reste je dis oui!

Sinon aucune explication sur ce problème?

gleu

Administrateur

Re : Full text search et index GIN non utilisé

L'EXPLAIN ANALYZE est intéressant car il indique le nombre de lignes supposés (23517) et le nombre de lignes réels (38924). La différence est presque du simple au double, mais pourtant je ne pense pas que cela soit le problème.

Il n'est pas conseillé de désactiver l'un des enable_* en permanence. Cela permet juste de se faire une idée du pourquoi du problème. En l'occurence, je ne pense pas que le problème soit dû à de mauvaises statistiques.

Réactiver le parcours séquentiel et descendez la valeur du random_page_cost à 2 par exemple (s'il est bien à 4 actuellement comme je le suppose). Puis ré-exécutez votre requête. Ça devrait être bien meilleur.

---

Guillaume.

gleu

Administrateur

Re : Full text search et index GIN non utilisé

Ah oui, j'allais oublier l'explication sur la première question. La question était : pourquoi la différence de plan suivant le mot à rechercher.

Dans le premier cas (recherche du mot maint), il fait un parcours séquentiel car son estimation du nombre de lignes à récupérer est très grand (dans les 50000). Dans le second cas, il pense trouver le mot recherché que sur cinq lignes. Moins il y a de lignes, plus un parcours d'index est intéressant.

Autrement dit, je ne serais pas étonné que votre table fasse entre 50000 et 100000 lignes. Du coup, pas de parcours d'index quand le mot recherché se retrouve dans la majorité des documents.

---

Guillaume.

Reya

Membre

Re : Full text search et index GIN non utilisé

J'ai changer le paramètre à 2 et j'obtiens ceci :

"Seq Scan on documenttext  (cost=0.00..36900.00 rows=23517 width=8) (actual time=0.322..2692.242 rows=30226 loops=1)"
"  Output: iddocument"
"  Filter: (test @@ '''maint'''::tsquery)"
"Total runtime: 2717.811 ms"

avec le paramètre a 4:

"Seq Scan on documenttext  (cost=0.00..36900.00 rows=23517 width=8) (actual time=0.461..2729.602 rows=30226 loops=1)"
"  Output: iddocument"
"  Filter: (test @@ '''maint'''::tsquery)"
"Total runtime: 2755.302 ms"

je ne vois pas de différence! Je trouve quand même bizarre que sur ce mot ci, il n'utilise pas l'index pour effectuer la recherche!

gleu

Administrateur

Re : Full text search et index GIN non utilisé

Combien y a-t-il de lignes dans cette table ?

---

Guillaume.

Reya

Membre

Re : Full text search et index GIN non utilisé

Effectivement j'ai un total de 150000 lignes de texte ce qui représente environ 200 Mo de données.

Cependant lorsque je recherche un autre mot qui commence par 'mai' (forcement plus de résultat de maint) :

"Bitmap Heap Scan on documenttext  (cost=5.28..15.29 rows=5 width=8) (actual time=128.926..597.742 rows=50942 loops=1)"
"  Output: iddocument"
"  Recheck Cond: (test @@ '''mai'':*'::tsquery)"
"  ->  Bitmap Index Scan on idx_gin  (cost=0.00..5.28 rows=5 width=0) (actual time=125.037..125.037 rows=48397 loops=1)"
"        Index Cond: (test @@ '''mai'':*'::tsquery)"
"Total runtime: 634.291 ms"

Si j'ai bien compris, il pense qu'il y a 5 lignes alors qu'en réalité il y en 50942!

Comment je pourrait faire pour que la recherche se fasse plus rapidement. La taille des données est amené a évoluer fortement environ 200 documents texte par mois au minimun. Je ne peut me permettre d'avoir des temps qui vont sans cesse augmenter. Avez-vous des pistes ou des conseils pour par exemple 500 000 lignes et environ 1Go de données texte.

gleu

Administrateur

Re : Full text search et index GIN non utilisé

Juste une petite info rapide : test @@ 'maint:*' n'est pas une syntaxe correcte actuellement.

---

Guillaume.

Reya

Membre

Re : Full text search et index GIN non utilisé

Ah pourtant dans la doc on peut faire des recherche avec * pour faire des recherche sur un motif !!

Sinon quelle est la syntaxe correcte?

gleu

Administrateur

Re : Full text search et index GIN non utilisé

Dans quelle doc ? je connais un patch qui permettra de le faire dans la prochaine version, mais pas sur les versions stables actuelles.

---

Guillaume.

Reya

Membre

Re : Full text search et index GIN non utilisé

Documentation officielle de postgres 8.4 : http://www.postgresql.org/docs/8.4/inte … trols.html

...
Also, * can be attached to a lexeme to specify prefix matching:

SELECT to_tsquery('supern:*A & star:A*B');
        to_tsquery       
--------------------------
'supern':*A & 'star':*AB
Such a lexeme will match any word in a tsvector that begins with the given string.
...

gleu

Administrateur

Re : Full text search et index GIN non utilisé

Ah mince, j'avais loupé ça. Désolé. Bref.

Si j'ai bien compris, il pense qu'il y a 5 lignes alors qu'en réalité il y en 50942!

Oui, la statistique lui indique qu'il aura 5 lignes alors qu'il en aura 50942. Malgré ça, il fait un parcours d'index, ce qui est la bonne solution. Pour améliorer les statistiques, il faudra peut-être augmenter le default_statistics_target.

---

Guillaume.

Reya

Membre

Re : Full text search et index GIN non utilisé

J'ai fait la modification de la valeur mais j'ai exactement le même problème pour 'maint' ou 'patient', j'ai toujours des temps importants. Cela a quand même modifier certaines choses.
Maintenant pour 'mai' :

"Bitmap Heap Scan on documenttext  (cost=3791.57..10360.00 rows=4934 width=8) (actual time=2.149..44.116 rows=4934 loops=1)"
"  Output: iddocument"
"  Recheck Cond: (test @@ '''mai'''::tsquery)"
"  ->  Bitmap Index Scan on idx_gin  (cost=0.00..3790.34 rows=4934 width=0) (actual time=1.451..1.451 rows=4934 loops=1)"
"        Index Cond: (test @@ '''mai'''::tsquery)"
"Total runtime: 47.743 ms"

Il donne bien les bons résultats. Cependant pour 'main' :

"Bitmap Heap Scan on documenttext  (cost=3.34..5.35 rows=1 width=8) (actual time=0.476..7.855 rows=854 loops=1)"
"  Output: iddocument"
"  Recheck Cond: (test @@ '''main'''::tsquery)"
"  ->  Bitmap Index Scan on idx_gin  (cost=0.00..3.34 rows=1 width=0) (actual time=0.333..0.333 rows=854 loops=1)"
"        Index Cond: (test @@ '''main'''::tsquery)"
"Total runtime: 8.537 ms"

Il donne 1 lignes alors qu'il y en a 845!!!!!

Marc Cousin

Membre

Re : Full text search et index GIN non utilisé

Avez vous réexécuté la commande ANALYZE après avoir modifié le default_statistics_target ? Sinon la nouvelle valeur n'a pas encore été prise en compte, les statistiques n'ayant pas été recalculées.

---

Marc.

Reya

Membre

Re : Full text search et index GIN non utilisé

Oui oui j'ai refait une analyse après!

gleu

Administrateur

Re : Full text search et index GIN non utilisé

Faut bien comprendre que ce sont des statistiques. Les informations ne sont généralement pas exactes. Une estimation d'une ligne par rapport à 845, sur une table de 150000 lignes n'aura pas vraiment de conséquence sur le plan choisi.

---

Guillaume.

Reya

Membre

Re : Full text search et index GIN non utilisé

Je comprends bien mais je fais comment alors pour les mots qui sont présents dans beaucoup de lignes?

Reya

Membre

Re : Full text search et index GIN non utilisé

J'ai lu qu'il était possible d'utiliser l'index en modifiant une information sue la fonction : 'ts_match_vq'.

J'ai executé : alter function ts_match_vq(tsvector,tsquery) cost 1000 ;

avant :

explain analyse select iddocument from documenttext where ftsdocument @@ to_tsquery('french_simple','patient') ;
"Seq Scan on documenttext  (cost=0.00..36388.93 rows=85948 width=8) (actual time=137.234..6004.683 rows=85948 loops=1)"
"  Filter: (ftsdocument @@ '''patient'''::tsquery)"
"Total runtime: 6072.820 ms"

après :

alter function ts_match_vq(tsvector,tsquery) cost 1000 ;
explain analyse select iddocument from documenttext where ftsdocument @@ to_tsquery('french_simple','patient') ;
"Bitmap Heap Scan on documenttext  (cost=95322.41..345663.89 rows=85948 width=8) (actual time=24.316..135.363 rows=85948 loops=1)"
"  Recheck Cond: (ftsdocument @@ '''patient'''::tsquery)"
"  ->  Bitmap Index Scan on idx_gin  (cost=0.00..95300.92 rows=85948 width=0) (actual time=19.261..19.261 rows=85948 loops=1)"
"        Index Cond: (ftsdocument @@ '''patient'''::tsquery)"
"Total runtime: 193.150 ms"

L'index est utilisé et les temps sont plus que bon!!!!! 193 ms pour presque 90000 lignes

Cependant est-ce une bonne idée de modifié le cout de la fonction? Peut-il y a voir des disfonctionnement de Postgres?

gleu

Administrateur

Re : Full text search et index GIN non utilisé

Disfonctionnement, non. Mais une planification parfois étonnante, oui. Cependant, ça a eu l'air de bien améliorer la rapidité de vos requêtes, ce qui est très positif. Je me demande quelle était la valeur du coût par défaut.

---

Guillaume.

Reya

Membre

Re : Full text search et index GIN non utilisé

la valeur par defaut est 1!!!

J'ai lu dans ce post la petite subtilité : http://groups.google.com/group/pgsql.pe … 8c5f742a5b

Mais je n'ai pas trouvé plus d'explication !