Discussion :

[ymoreau]

Bonjour,
J'ai créé un index plein-texte de type GIN sur une colonne text, lorsque je fais des requêtes sur ce champ l'index n'est pas utilisé :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
CREATE INDEX token_idx ON docs USING  gin(to_tsvector('english', text));
 
EXPLAIN select id_doc from docs where text @@ to_tsquery('english', 'anarchism');
                         QUERY PLAN                         
------------------------------------------------------------
 Seq Scan on docs  (cost=0.00..319429.04 rows=2666 width=4)
   Filter: (text @@ '''anarch'''::tsquery)

Je sais que lorsque la clause WHERE n'est pas assez restrictive il est préférable de faire un scan de la table, mais dans mon cas selon EXPLAIN seuls 2666 documents semblent concernés par la clause (sachant que la table en contient 2,7 millions).

Est-ce que j'ai loupé quelque chose dans la façon de créer l'index ou dans la requête qui devrait l'utiliser ?
Merci d'avance

[estofilo]

Pour être sûr, il faudrait faire ANALYZE de la table et aussi EXPLAIN ANALYZE au lieu de EXPLAIN.

[ymoreau]

Je n'ai pas pensé à lancer un ANALYSE mais j'ai lancé la requête sans EXPLAIN et comme prévu elle a fait un parcours complet de la table en 2h... Je vais quand même la relancer.

Edit : comme je le soupçonnais l'index n'est pas utilisé, la requête vient de m'afficher des messages comme quoi des mots trop longs ne peuvent pas être indexés. J'ai eu ce même message quand j'ai créé l'index, donc j'en déduis qu'il reparcoure tout le texte à la volée pour obtenir les lexèmes.

NOTICE: le mot est trop long pour être indexé
DÉTAIL : Les mots de plus de 2047 caractères sont ignorés.

[SQLpro]

Normal... Une indexation textuelle, comme son nom l'indique est fait pour indexer des textes constitués de phrases avec des mots... Pas pour des flux de chaines ou des chaines binaires !

A +

[ymoreau]

Mon champ text contient bien des mots, qui sont légèrement pollués par des erreurs de frappe et ou des concaténations suspectes, plus des termes techniques de mise en page non pertinents. Mais je n'ai eu ce warning de longueur que 3 ou 4 fois sur les 2,7 millions de documents (qui sont des articles wikipedia), pour le reste il s'agit de lexèmes valides et correctement "digérés" par les dictionnaires postgresql.

J'ai utilisé ts_debug() pour voir un peu ce que ça donnait. Ce qui m'interpelle c'est qu'il est écrit french_stem, or j'ai explicitement demandé un index en anglais à la création et dans la requête. Peut être qu'il faut être plus précis dans les options ? Il faudrait aussi que je désactive l'indexation des espaces et ponctuation, mais ça ne devrait pas perturber les requêtes à priori.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
select ts_debug(text) from docs where id_doc = 1;
 
                                      ts_debug
-------------------------------------------------------------------------------------
 (asciiword,"Word, all ASCII",Les,{french_stem},french_stem,{le})
 (blank,"Space symbols"," ",{},,)
 (asciiword,"Word, all ASCII",Rois,{french_stem},french_stem,{rois})
 (blank,"Space symbols"," ",{},,)
 (asciiword,"Word, all ASCII",mages,{french_stem},french_stem,{mag})
 (blank,"Space symbols"," ",{},,)
 (uint,"Unsigned integer",5203878,{simple},simple,{5203878})
 (blank,"Space symbols"," ",{},,)
 (uint,"Unsigned integer",217151308,{simple},simple,{217151308})
 (blank,"Space symbols"," ",{},,)
 (uint,"Unsigned integer",2008,{simple},simple,{2008})
 (int,"Signed integer",-06,{simple},simple,{-06})
 (int,"Signed integer",-04,{simple},simple,{-04})
 (numword,"Word, letters and digits",T20,{simple},simple,{t20})
 (blank,"Space symbols",:,{},,)
 (uint,"Unsigned integer",45,{simple},simple,{45})
 (blank,"Space symbols",:,{},,)
 (numword,"Word, letters and digits",24Z,{simple},simple,{24z})
 (blank,"Space symbols"," ",{},,)
 (asciiword,"Word, all ASCII",Ultimus,{french_stem},french_stem,{ultimus})
...
 (asciiword,"Word, all ASCII",Caspar,{french_stem},french_stem,{caspar})
 (blank,"Space symbols",". ",{},,)
 (asciiword,"Word, all ASCII",Synopsis,{french_stem},french_stem,{synops})
 (blank,"Space symbols"," ",{},,)
 (asciiword,"Word, all ASCII",The,{french_stem},french_stem,{the})
 (blank,"Space symbols"," ",{},,)
 (asciiword,"Word, all ASCII",Biblical,{french_stem},french_stem,{biblical})
 (blank,"Space symbols"," ",{},,)
 (asciiword,"Word, all ASCII",Magi,{french_stem},french_stem,{mag})
 (blank,"Space symbols"," ",{},,)
 (asciiword,"Word, all ASCII",find,{french_stem},french_stem,{find})
 (blank,"Space symbols"," ",{},,)
 (asciiword,"Word, all ASCII",themselves,{french_stem},french_stem,{themselv})
...

[estofilo]

Es-tu sûr de la technique d'indexation que tu utilises, c'est-à-dire d'indexer le résultat de to_tsvector(), au lieu d'indexer une colonne supplémentaire de type tsvector contenant ce résultat?

[ymoreau]

J'ai suivi les examples de la doc, ils proposent deux façons de faire, créer un index ou générer une colonne contenant un ts_vector, j'ai choisi la 1ère qui me parait plus simple étant donné que je n'ai qu'un champ à indexer et pas un asssemblage de colonnes (titre+auteur+article par exp).

Comme dit au début, je l'ai fait avec cette requête :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE INDEX token_idx ON docs USING  gin(to_tsvector('english', text));

Concernant mon message précédent, je n'avais pas compris que ts_debug retraite directement le texte et n'explore pas l'index, donc appelé avec la config par défaut il recherche des lexèmes français mais en l'appelant comme je l'ai fait pour mes autres requêtes c'est bien en anglais :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE INDEX token_idx ON docs USING  gin(to_tsvector('english', text));

Par contre, je n'ai pas trouvé de moyen de voir la config l'index plein-texte créé pour ma table, d'obtenir des infos. Il apparait dans la liste des index de la base mais je n'en sais pas plus sur son contenu.

[estofilo]

En admettant que tu ne veuilles pas de colonne supplémentaire, je pense que la requête devrait être:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
SELECT id_doc FROM docs WHERE to_tsvector('english',text) @@ to_tsquery('english', 'anarchism');

c'est-à-dire que la comparaison doit porter sur exactement la même expression que l'index.

[ymoreau]

J'avais lu dans la doc que la config devait être rigoureusement la même pour l'index et la requête, je pensais qu'en citant le champ seul il utiliserait l'index si le ts_query avait la même config. En explicitant comme tu le proposes ça fonctionne :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
explain select id_doc from docs where to_tsvector('english',text) @@ to_tsquery('english', 'anarchism');
                                       QUERY PLAN                                       
----------------------------------------------------------------------------------------
 Bitmap Heap Scan on docs  (cost=13177.79..57075.12 rows=13347 width=4)
   Recheck Cond: (to_tsvector('english'::regconfig, text) @@ '''anarch'''::tsquery)
   ->  Bitmap Index Scan on token_idx  (cost=0.00..13174.45 rows=13347 width=0)
         Index Cond: (to_tsvector('english'::regconfig, text) @@ '''anarch'''::tsquery)

Par contre si j'ajoute une condition LIMIT, j'ai à nouveau un scan complet. Je trouve ça étrange, mais de toute façon ça n'est pas ce que je veux faire exactement. La requête que je voulais faire à l'origine, récupère les 1000 documents les plus pertinents, et l'index est bien utilisé (alors qu'il y a également une clause LIMIT) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
explain analyse SELECT id_doc, rank FROM (SELECT id_doc, text, q, ts_rank(to_tsvector('english',text), q) AS rank FROM docs, to_tsquery('english', 'anarchism') q WHERE to_tsvector('english', text) @@ q ORDER BY rank DESC LIMIT 1000) AS foo;
                                                                     QUERY PLAN                                                                      
-----------------------------------------------------------------------------------------------------------------------------------------------------
 Subquery Scan foo  (cost=58007.14..58019.64 rows=1000 width=8) (actual time=38216.469..38217.103 rows=1000 loops=1)
   ->  Limit  (cost=58007.14..58009.64 rows=1000 width=782) (actual time=38216.467..38216.741 rows=1000 loops=1)
         ->  Sort  (cost=58007.14..58040.51 rows=13347 width=782) (actual time=38216.465..38216.580 rows=1000 loops=1)
               Sort Key: (ts_rank(to_tsvector('english'::regconfig, docs.text), q.q))
               Sort Method:  top-N heapsort  Memory: 405kB
               ->  Nested Loop  (cost=13177.79..57275.34 rows=13347 width=782) (actual time=19.387..38206.361 rows=3098 loops=1)
                     ->  Function Scan on q  (cost=0.00..0.01 rows=1 width=32) (actual time=0.004..0.005 rows=1 loops=1)
                     ->  Bitmap Heap Scan on docs  (cost=13177.79..57008.39 rows=13347 width=750) (actual time=1.936..39.972 rows=3098 loops=1)
                           Recheck Cond: (to_tsvector('english'::regconfig, docs.text) @@ q.q)
                           ->  Bitmap Index Scan on token_idx  (cost=0.00..13174.45 rows=13347 width=0) (actual time=1.159..1.159 rows=3098 loops=1)
                                 Index Cond: (to_tsvector('english'::regconfig, docs.text) @@ q.q)
 Total runtime: 38217.225 ms

Merci de votre aide !