Forums PostgreSQL.fr

pelé

Membre

Recherche plein texte - insenssible casse/accent/caractère spé./langue

Bonjour à tous !

  Passant de Mysql à Postgresql, pour information, je tente de reproduire la recherche plein texte "Match .. Against" de MySql en langage PostGresql.

J'ai utilisé alors cette démarche suivante :

ALTER TABLE etab ADD COLUMN texte_vectorise tsvector;
UPDATE etab SET texte_vectorise=to_tsvector(asciiname);
CREATE INDEX idx_vecteur_ascii ON contenu USING gin (texte_vectorise);

Mais je n'obtiens pas le résultat souhaité.

C'est à dire :

J'ai une colonne avec des noms propre de diverses langues : Français, arabe, allemand, chinois etc...

Je souhaite effectuer une recherche (style auto-complétion coté client) et donc retourner un résultat se rapprochant de ce quoi inscrit en interface.

Exemple :

"Pré-ilot" est le résultat souhaité (en autres)
En tapant au clavier : Pré | pré- | pre il | Pre il | etc etc etc ..
Je dois avoir en suggestion de résultat "Pre-ilot"

Idem pour les autres langues.

Avec MySql 5.5,
bdd : utf8_unicode_ci
mon_champs : utf8_unicode_ci
type innodb
Index : FullText

Cela marchais très bien.

Je souhaite le faire donc avec PostGreSql mais je ne vois comment y parvenir, d'autant que je ne sais du tout comment spécifié à ma base et champs que je veux un type de codage UTF8 et de collation Unicode (Que tout caractère sois reconnu). Car à ce que j'ai compris le type en défaut et utf8 et la collation en Locale (sois fr je pense..) donc tout caractères ascii n'étant dans la plage "FR" ne serai reconnu ? Ce qui n'est pas souhaité.

Ici ça pêche sur les accents en autres. "Pré" ne donnera en retour "Pre-ilot"

J'espère avoir bien détaillé et suis à votre écoute, étant bloqué.

Merci

Bidou

Membre

Re : Recherche plein texte - insenssible casse/accent/caractère spé./langue

Je suppose qu'il faudrait combiner ça avec l'extension PostgreSQL "unaccent" afin d'effectuer vos recherches sans les accents?

pelé

Membre

Re : Recherche plein texte - insenssible casse/accent/caractère spé./langue

Oui tout à fait. J'y avais pensé mais je n'avais pas réussis à l'utiliser. Je voyais bien le fichier unaccent.sql et unaccent.rules dans le dossier share de postgres. Je m'étais bien dis qu'il ne semblais installer et puis illumination... il fallait exécuter le fichier unaccent.sql pour installer la procédure. Je pensais que cela été accessible de base, fin bon...

Merci de cette réponse.

Alors j'ai utilisé cette fonction passant de

SELECT * FROM etab WHERE texte_vectorise @@ to_tsquery(('Pré'));

à

SELECT * FROM etab WHERE texte_vectorise @@ to_tsquery(unaccent('Pré'));

Le résultat y est mais -> C'est affreusement long en temps de requête !!!!

Comment cela ce fait-il ?

La fonction fait tout simplement enlever les accents puis la recherche plein text prend le relais et c'est tout.

Je passe de 83ms à 106.749ms , c'est étrange. La fonction n'est tout de même pas appelé X fois le nombre de lignes en table de données ??

2 - il semblerait que le fichier unaccent.rules ne prennent en comptes que les accents européens ? Je n'ai pas trouvé de fichier pour l'ensemble du panel ascii étendu http://docs.postgresqlfr.org/9.0/unaccent.html

C'est bien dommage tout ceci... dois-je donc effectuer le "codage" avant (en php dans mon cas) ou alors la requête n'est pas bien écrite ?

Dernière modification par pelé (21/06/2013 17:45:02)

gleu

Administrateur

Re : Recherche plein texte - insenssible casse/accent/caractère spé./langue

Vous aurez plus d'informations sur unaccent dans ce document : http://www.dalibo.org/glmf134_postgresq … nouveautes

Par contre, je ne suis pas sûr que la recherche plein texte de PostgreSQL soit le bon outil pour implémenter une auto-complétion du côté client.

---

Guillaume.

pelé

Membre

Re : Recherche plein texte - insenssible casse/accent/caractère spé./langue

(je ré-écris tout, le message n'as été mémorisé après l’identification...).

Alors :

J'ai parcouru le lien et compris un truc en plus en effet, associé une fonction à une recherche de texte.
J'ai fait comme suit :

CREATE TEXT SEARCH CONFIGURATION fr (COPY = french);
ALTER TEXT SEARCH CONFIGURATION fr ALTER MAPPING FOR hword, hword_part, word WITH unaccent, french_stem;

A vu de nez, ça ne semble suffire déjà. testons.

SELECT * FROM etab WHERE texte_vectorise @@ to_tsquery('Pré');

Aucun résultat trouvé

SELECT * FROM etab WHERE texte_vectorise @@ to_tsquery('Pré%');

Aucun résultat trouvé

SELECT * FROM etab WHERE texte_vectorise @@ to_tsquery('fr','Pré%');

Aucun résultat trouvé

SELECT * FROM etab WHERE texte_vectorise @@ to_tsquery('fr', 'Prés%');

Trouve : Prés-rené (ok)

Conclusion :
La fonction unaccent ne fonctionne que si l'on spécifie un dictionnaire. Ok, j'avais déjà compris.
Le résultat est correct que si le mot "entier" est écris.

C'est donc très insuffisant :
- Ceci ne résous l'idée d'auto-complétion.
- Préciser à chaque fois un dictionnaire n'étais pas mon intention.

J'ai des millions de lignes en diverses langues et il n'y a aucune association de ces lignes à une langue précises.. Inconnu.
Je ne peut chercher un caractère chinois dans un dictionnaire japonais, par exemple.

Alors :
- Récupérer tout dictionnaire de langue du monde
- Associer la fonction "unaccent" à chaque dictionnaire (qui d'ailleurs n'est valable qu'en europe)
- Détecter la langue écrite dan l'auto-compléte pour la préciser en paramètre de la requête sql

C'est très conséquent et fastidieux, je m'attendais à quelque chose de plus simple...

D'autant plus que ceci ne résoudra l'idée d'auto-complétion comme vous l'aviez bien prédis.

Que me suggériez-vous ?

Ps : quelques autres tests :

Index sur "asciiname" (btree)

Majuscule - pas d'accent - un mot : like

SELECT * FROM etab WHERE asciiname like (Pre%');

Ok : (600ms)

Majuscule - accent - un mot : like

SELECT * FROM etab WHERE asciiname like (Pré%');

BAD : 0 ligne (22ms)

Majuscule - pas d'accent - un mot : like

SELECT * FROM etab WHERE asciiname like any (array['Pre%']);

OK : (5745 ms)

Majuscule - pas d'accent - deux mots : like any

SELECT * FROM etab WHERE asciiname like any (array['Pre%','sta%']);

OK : (6292 ms) : renvoie aussi mot commençant par 'stat', non forcément désiré.

Majuscule - accent - deux mots : like

SELECT * FROM etab WHERE asciiname like any (array['Pré%','sta%']);

BAD : (6300 ms) : renvoie simplement mot començant par "sta".

Minuscule - pas d'accent - deux mots : like

SELECT * FROM etab WHERE asciiname like any (array['pre%','sta%']);

BAD : (6300 ms) : renvoie simplement mot començant par "sta".

Majuscule - pas d'accent - un mot : ~* %

SELECT * FROM etab WHERE asciiname ~* 'Pre%';

BAD : 0 lignes (23 Secondes)

Majuscule - pas d'accent - un mot : ~*

SELECT * FROM etab WHERE asciiname ~* 'Pre';

Ok :  (23 Secondes) : Qui contient le sub/mot Pre

Majuscule - accent - un mot : ~*

SELECT * FROM etab WHERE asciiname ~* 'Pré';

BAD : 0 ligne  (23 Secondes)

Majuscule - pas d'accent - deux mots : ~*

SELECT * FROM etab WHERE asciiname ~* 'Pre|sta';

BAD : Trop de ligne (29 Secondes) : Renvoie tout ce qui contient sta, hors : "Pre" suivis de "sta" désiré uniquement. (quoique)

Minuscule - pas d'accent - un mot: ~*

SELECT * FROM etab WHERE asciiname ~* 'pre';

BAD : Trop de ligne (22 Secondes) : Renvoie tout ce qui contient "pre", hors : Commençant par "pre", désiré, et non, syllabe présente dans un mot (exemple : "sapre");.

Minuscule - pas d'accent - un mot: similar to

SELECT * FROM etab WHERE asciiname similar to 'pre';

BAD : résultat insufissant (20ms)

Minuscule - pas d'accent - un mot: similar to %

SELECT * FROM etab WHERE asciiname similar to 'pre%';

BAD : 0 ligne (371ms)

Majuscule - pas d'accent - un mot: similar to %

SELECT * FROM etab WHERE asciiname similar to 'Pre%';

Ok : (34ms)

Majuscule - pas d'accent - deux mots: similar to % |

SELECT * FROM etab WHERE asciiname similar to 'Pre%|sta%';

Bad : (14 Secondes) Renvoie tout ce qui commence par "Pre" même si "sta" non présent.

Majuscule - pas d'accent - deux mots: similar to %

SELECT * FROM etab WHERE asciiname similar to 'Pre%sta%';

OK : (19 ms) Renvoie ce qui commence par "Pre" suivis d'un mot commençant par "sta".
BAD : Ne renvoie pas un mot composé de trois mots : Prés-saint-stain

Majuscule - pas d'accent - deux mots: similar to %%

SELECT * FROM etab WHERE asciiname similar to '%Pre%%sta%';

OK : (32 ms) Renvoie ce qui contient "Pre" et "sta"

Minuscule - pas d'accent - deux mots: similar to %%

SELECT * FROM etab WHERE asciiname similar to '%Pre%%sta%';

BAD : 0 ligne (34 ms)

...

Conclusion :

On a vu
- like avec accent et fonction "unaccent" trop long
- like minuscule : pas de résultat
- like avec accent sans fonction "unaccent" : pas de résultat.

- like any, beaucoup trop long
- like any avec plusieurs mots renvois une recherche dans tout ordre (à méditer)
- like anye avec accent, résultat insuffisant.

- ~* ok, mais trop long
- ~* avec accent : inccorect
- ~* | : tout ordre de recherche, pas forcément le but recherché
- ~* avec minuscule : renvoi tout mot contenant ou entier, contenant la chaine de caractère donnée

- similar to : du pareil aux mêmes que précédement.
- plus rapide en recherche
- N'arrive pas non plus les recherches composés "Pre%sta%" ne renvoie pas "Prés-saint-stain"
- Prends pas en compte minuscule et accent.

Pas forcément concluant tout ceci. Je voulais simplement faire une recherche texte insensible à la casse, accents, et gérables dans toutes langues. Ce sont tous des noms propre donc un ordre précis. (Ex : "prés rené" doit donner "Prés saint rené" et non pas "Arene des pres", ça n'aurai aucun sens).

pelé

Membre

Re : Recherche plein texte - insenssible casse/accent/caractère spé./langue

J'ai essayé en utilisant le dictionnaire "simple". à ce que j'ai compris il rend minuscule tout caractère (et il semble "gérer" les accents "tout seul"), suite à un test :

SELECT * FROM etab WHERE texte_vectorise @@ to_tsquery('simple','pré%&-&sta%')

OU

SELECT * FROM etab WHERE texte_vectorise @@ to_tsquery('simple','pré%&sta%')

Résultat : Pre-station (23ms)(4résultats)(tout est là)

Il y a eus association de "p" et "P"
Le "-" a été inhibé
Recherche du mot composé

C'est un début qui semble correct.

Je n'ai pas encore trouvé l’information qui précisé qu'il pouvait "convertir" tout les accents (pas que européens).

Cette démarche signifie que je dois gérer la requête selon le/les mot(s) saisient en autocomplétion donc.

i.e :
1 ) Saisie : 'pre'

SELECT * FROM etab WHERE texte_vectorise @@ to_tsquery('simple','pre%')

2 ) Saisie : 'pre-st'

SELECT * FROM etab WHERE texte_vectorise @@ to_tsquery('simple','pré%&st%')

3 ) Car le test :

SELECT * FROM etab WHERE texte_vectorise @@ to_tsquery('simple','pre-st%')

renvoie aucun résultat, alors que le test "2" en renvoie.

4 )  Saisie 'pre st'

Syntax error.

Très embêtant. Il faut donc remplacer les "espaces" par des "&" avant de le placer en paramètre de requêtes SQL.

Je creuse encore.

gleu

Administrateur

Re : Recherche plein texte - insenssible casse/accent/caractère spé./langue

Attention, il ne faut pas mettre de % dans to_tsquery. Ce n'est pas un LIKE.

La fonction unaccent ne fonctionne que si l'on spécifie un dictionnaire.

Pas besoin s'il s'agit de votre dictionnaire par défaut (paramètre default_text_search_config). Si je prends votre exemple :

pele=# SELECT to_tsquery('Pré');
 to_tsquery 
------------
 'pré'
(1 row)

pele=# SELECT to_tsquery('fr', 'Pré');
 to_tsquery 
------------
 'pre'
(1 row)

pele=# set default_text_search_config to fr;
SET
pele=# SELECT to_tsquery('Pré');
 to_tsquery 
------------
 'pre'
(1 row)

Le résultat est correct que si le mot "entier" est écris.

Absolument pas, sauf peut-être dans ce cas-là.

J'ai des millions de lignes en diverses langues et il n'y a aucune association de ces lignes à une langue précises.

La recherche plein texte de PostgreSQL ne permet pas ce genre de chose. La recherche plein texte permet de rechercher un texte parmi d'autres parmi la langue d'un utilisateur. Il faut donc spécifier un dictionnaire qui correspond à cette langue. Si votre table contient des textes de différentes langues, cela ne fonctionnera pas.

Que me suggériez-vous ?

Ne pas utiliser la recherche plein texte de PostgreSQL. Vous aurez beau essayer de la faire tenir pour votre cas d'utilisation, cela ne fonctionnera pas.

Résultat : Pre-station (23ms)(4résultats)(tout est là)

Vu la recherche, il vous aurait aussi trouvé des chaînes du type pre%sta et sta%pre. Le tiret est viré, ce n'est pas un mot, et l'ordre dans pre & sta n'est pas voulu. La preuve :

pele=# select to_tsquery('simple','pré%&-&sta%')
pele-# ;
  to_tsquery   
---------------
 'pré' & 'sta'
(1 row)

Il cherche simplement tous les documents qui contiennent des mots dont la racine est pré et et des mots dont la racine est sta.

Je n'ai pas encore trouvé l’information qui précisé qu'il pouvait "convertir" tout les accents (pas que européens).

Il ne les convertit pas, comme indiqué dans mon exemple ci-dessus. Je ne sais pas quel modif vous avez fait sur votre système pour qu'il le fasse mais ça ne se passe pas ainsi par défaut.

Pour l'exemple suivant, c'est normal :

pele=# select to_tsquery('simple','pre-st%');
       to_tsquery        
-------------------------
 'pre-st' & 'pre' & 'st'
(1 row)

Il ne cherche pas ce que vous attendez.

Très embêtant. Il faut donc remplacer les "espaces" par des "&" avant de le placer en paramètre de requêtes SQL.

Oui, c'est ainsi que ça fonctionne, rien de bien étonnant. C'est indiqué dans la documentation.

Je creuse encore.

Ne connaissant pas le "Match .. Against" de MySQL, je ne saurais pas vous dire quel est son équivalent, à considérer qu'il existe. Par contre, je pense clairement que la recherche plein texte de PostgreSQL ne correspond pas aux recherches que vous voulez faire (en tout cas, celles montrées ici).

---

Guillaume.

pelé

Membre

Re : Recherche plein texte - insenssible casse/accent/caractère spé./langue

"Rien ne va plus", me dirai ce croupier après mise ! Mais pourtant, toutes les personnes ayant posé jetons y croient dur comme fer.

Une miséricorde, plein de test sans bonne figure.

_________

Attention, il ne faut pas mettre de % dans to_tsquery. Ce n'est pas un LIKE.

Oui, je m'étais rendu compte, je voulais juste émettre mon cheminement.

Pas besoin s'il s'agit de votre dictionnaire par défaut (paramètre default_text_search_config)

ça ne doit-être mon dictionnaire par défaut alors, j'obtiens :

SELECT to_tsquery('Pré');
to_tsquery
------------
'pré'

J'utilise :
- pgAdmin3
- postgresql 9.0

Dans fts dictionnaries j'ai : unaccent
Dans fts configurations j'ai : simple / fr

Je reste un peut embrouillé de tout ceci

Premièrement, "simple" et "fr" devrai plutôt être des dictionnaires et "unaccent" une configuration. Après, tout dépend de l'idée qu'il y a derrière...

Il y a beaucoup d’ambiguïté sur ce terme "dictionnaire". Implicitement c'est l'ensemble des mots d'une langue. Après il est spécifié plusieurs type de dictionnaire simple/synonymes/thesaurus/ispell/snowball

Simple : associé un mot à un dictionnaire. Si c'est un mot courant, il est ignoré. S'il est trouvé et non courant, il est normalisé, c'est à dire, associé aux dictionnaire. S'il n'est pas trouvé, il est recherché dans le prochain dictionnaire.
Synonymes : Évite de supprimer/réduire des mots à un seul sens. Offre plusieurs sens à un mot.
Thesaurus : associé un mot à une phrase/mots et vice versa
Ispell : Réduire un ensemble de mots de même langue à une même racine. Utilise donc un seul lexeme pour un ensemble de jeton (lexeme "bank" pour les jetons "banked-banking-banks")
Hunspell : Pareil, mais gére aussi les mots composés.
Snowball : pareil de Ispell et accepte tout. (??)

Voila ce que j'ai compris. Me voila bien.

Les dictionnaires sont utilisés pour éliminer des mots qui ne devraient pas être considérés dans une recherche (termes courants), et pour normaliser des mots pour que des formes dérivées de ce même mot établissent une correspondance.

Je ne veux rien "épargner" lors de ma recherche. Normaliser ne me servira pas à grand chose. Dans mon cas.

La recherche plein texte permet de rechercher un texte parmi d'autres parmi la langue d'un utilisateur

C'est noté.

Il cherche simplement tous les documents qui contiennent des mots dont la racine est pré et et des mots dont la racine est sta.

Aucune possibilité d’ordonnancement ?

Il ne les convertit pas

Je voulais dire par là, faire une association. "à" correspond à "a".

J'ai essayé d'autres tests alors, mais peut concluant :

Avec citext, c'était peut suffisant. Déjà la recherche était très longue, puis ne gère pas les accents.

J'ai effectué un index avec lower alors :

Create index geo_idx on etab (lower(asciiname))

Puis avec la requête

select * from etab where lower(asciiname) like lower('le_puy_en%');

Pour trouver "Le Puy-en-Velay". 12 secondes... beaucoup trop long (je trouve cela étrange).

Pour trouver "Rio de Janeiro" avec " Rio_de%" (1800 résultats, 5 secondes).

est "Pré" ... 0 résultats...

Ne connaissant pas le "Match .. Against" de MySQL

Je ne connais pas forcément mieux, mais c'est la "même idée" que "ts_vector @@ to_query". Là aussi on ne peut utiliser le terme "%", c'est un mot rechercher et non une partie de mot. Rechercher des mots dans des lignes donc.
Alors j'avais aussi ce pseudo problème d'alternances, comme dans l'exemple "Pre | sta", mais qui était fortement compensé par le fait que les accents et caractères spéciaux étaient "gérés" tout "seul". En spécifiant mon champs en "utf8_unicode_ci", i.e, collation unicode (style utf8, donc Tout, si j'ai bien compris et "ci", insensible à la casse).
Quand à savoir la réel fiabilité de ceci, avec mon clavier d'européen, cela fonctionnais, avec un clavier arabe, à voir ! (surtout les glyphes asiatiques).

C'est pour cela que j'ai ma base en utf8 et souhaiterai une collation "universel", car je ne veux pas du tout que ce champs "asciiname" soit associé à une collation linguistique, catalogué, car aucune collation linguistique ne reconnait tout caractère de toute langue, à part celle prétendue de mysql "unicode", mais je n'ai pas trouvé cela chez postgresql.

J'ai vu qu'en fait le charactère "_" dans une recherche like correspond à tout caractère ? Exemple

select * from etab where lower(asciiname) like lower('puy_en%');

Réponse : Puyrenier
Le "_" à était remplacé par "r" ici. Ce qui doit bourriner plus la recherche en lenteur donc.

En fait, il y a quelque chose que je ne comprends pas. Un caractère est unique et peut avoir comme association Minuscule/Majuscule et accent/sans accent. Comment peut faire  une personne qui veut l'ensemble des caractère linguistiques, tout bêtement, et que c'est association soit implicite (entre langue).

Si je comprend bien, lors d'une recherche, si je cherche "B" on recherche le codage 0042 mais il faut aussi rechercher 'b' et si on écris "b avec accent" il doit rechercher "B" et "b".
En grec " Έ " devra être vu comme " E "

Ce n'est pas évident c'est sur. Mais par exemple, gmap, en tapant un nom de ville avec/sans accent, minuscule/majuscule, n'importe qu'elle langue, on à une concordance. C'est le but recherché ici aussi.

Le full text correspond à la moitié du besoin, manque l’ordonnancement, "mot" suivis du "mot", la concordance des accents, les partis de mots pour l’auto complétion. En fait beaucoup !!

Une fusion de like et full-text. (Déjà, gérer la concordance de tout accents linguistique serai bien !!! )

Je n'ai pas encore trouvé.

Dernière modification par pelé (27/06/2013 12:08:08)

gleu

Administrateur

Re : Recherche plein texte - insenssible casse/accent/caractère spé./langue

ça ne doit-être mon dictionnaire par défaut

Un seul moyen de le savoir : SHOW default_text_search_config;

Premièrement, "simple" et "fr" devrai plutôt être des dictionnaires et "unaccent" une configuration. Après, tout dépend de l'idée qu'il y a derrière...

Le vocabulaire de la recherche plein texte est tout autre. fr est une configuration utilisateur (utilisateur dans le sens où vous l'avez créé). Cette configuration fait appel à différents dictionnaires dont unaccent. Ces dictionnaires dépendent du type d'informations traitées (token) :

pele=# \dF+ fr
 Text search configuration "public.fr"
Parser: "pg_catalog.default"
      Token      |     Dictionaries     
-----------------+----------------------
 asciihword      | french_stem
 asciiword       | french_stem
 email           | simple
 file            | simple
 float           | simple
 host            | simple
 hword           | unaccent,french_stem
 hword_asciipart | french_stem
 hword_numpart   | simple
 hword_part      | unaccent,french_stem
 int             | simple
 numhword        | simple
 numword         | simple
 sfloat          | simple
 uint            | simple
 url             | simple
 url_path        | simple
 version         | simple
 word            | unaccent,french_stem

Je ne veux rien "épargner" lors de ma recherche. Normaliser ne me servira pas à grand chose. Dans mon cas.

On est bien d'accord que normaliser ne vous sert à rien dans votre cas. D'où le fait que la recherche plein texte n'est pas la bonne solution à votre problème/besoin.

Aucune possibilité d’ordonnancement ?

Non, pas directement avec la recherche plein texte. Cependant, vous pouvez contourner le problème ainsi :

SELECT * FROM etab
WHERE texte_vectorise @@ to_tsquery('simple','pre & st')
  AND texte LIKE 'pre%st%';

La recherche plein texte permettra de trouver rapidement un sur-ensemble de ce que vous recherchez réellement et le LIKE verrouille uniquement ce qui vous intéresse parmi ce sous-ensemble.

Avec citext, c'était peut suffisant. Déjà la recherche était très longue, puis ne gère pas les accents.

Normal, ce n'est pas le but de citext. citext est un type text insensible à la casse, mais pas aux accents.

Pour trouver "Le Puy-en-Velay". 12 secondes... beaucoup trop long (je trouve cela étrange).

Difficile à dire sans plus de détails. Rien ne dit qu'il a utilisé l'index par exemple.

J'ai vu qu'en fait le charactère "_" dans une recherche like correspond à tout caractère ?

Oui, c'est le standard SQL qui le demande ainsi.

---

Guillaume.

pelé

Membre

Re : Recherche plein texte - insenssible casse/accent/caractère spé./langue

Merci pour toutes ces précisions.

Je ne suis pas encore très familiarisé avec tout ceci c'est sur.

Juste pour info alors, par défaut : pg_catalog.english

Que cela voudrait-il donc dire si je ne spécifie le catalog français ?

J'ai testé vite fait donc.

SELECT * FROM etab
WHERE texte_vectorise @@ to_tsquery('pré & st')
  AND lower(asciiname) LIKE lower(unaccent('pré%st%'));

Résultat : 0 rows

SELECT * FROM etab
WHERE texte_vectorise @@ to_tsquery('fr', 'pré & st')
  AND lower(asciiname) LIKE lower(unaccent('pré%st%'));

Résultat : 4 rows (résultats attendu).

Donc, comme en effet, le dictionnaire "unaccent" était ajouté à la configuration "FR" et non "English", la recherche plein-texte par défaut (sois le catalog english) ne convertit pas chaîne avec accent -> chaîne sans accents et ne reçoit dès lors aucun résultat dès le début.
Rappel : texte_vectorise et asciiname sont des chaînes de caractères en ascii (selon moi étendu)(en effet je reprends une base existante), mais à partir du moment où l'on utilise un alphabet arabe, chinois etc, ce n'est plus de l'ascii mais de l'unicode.

Ps : utiliser le dictionnaire unaccent dans une configuration 'english' ne servirait à rien si je comprends bien, étant donné qu'un dictionnaire (au sens littéraire) anglais n'as d'accent ? Où cela n'as rien à voir. "Unaccent" est juste une fonction utilisé dans la configuration "English". Configuration qui spécifie juste un nom auxquels on associe divers dictionnaire fonction/stem , où stem est une liste de mots courant devant être "zappé" lors d'une recherche. Ce qui dans mon cas est pénalisant alors, exemple : recherche "la madone", on doit attendre d'écrire "la m" au minimum pour susciter l'idée de voir apparaitre la suggestion "la madone".

SELECT * FROM etab
WHERE texte_vectorise @@ to_tsquery('simple','pre & st')
  AND texte LIKE 'pre%st%';

C'est très bien vu. A force de vouloir faire des requêtes simples j'en oublie d'aller plus loin.
Je l'ai un peut modifié. "Asciiname" comportant majuscule/minsucule et aucun accents mais il peut avoir des caractères " - ", " ' ", et les mots utilisateurs pouvant contenir "n’importe" quoi, devant être réduis à minuscule et sans accents.

Difficile à dire sans plus de détails. Rien ne dit qu'il a utilisé l'index par exemple.

Oui c'est vrai, j'ai remarqué que ce n'était toujours le cas.

SELECT * FROM etab
WHERE texte_vectorise @@ to_tsquery('fr', 'pré & st')
  AND lower(asciiname) LIKE lower(unaccent('pré%st%'));

Cette dernière requête à répondu sous "529"ms. (certes je suis en local et surement on peut faire des modifications sur la configuration postgresql pour améliorer un peut, mais bon, je m'attendais à mieux).

J'ai effectué un explain

"Bitmap Heap Scan on etab  (cost=5.38..9.41 rows=1 width=603)"
"  Recheck Cond: (texte_vectorise @@ '''pre'' & ''st'''::tsquery)"
"  Filter: (lower((asciiname)::text) ~~ lower(unaccent('pré%st%'::text)))"
"  ->  Bitmap Index Scan on idx_vecteur_ascii  (cost=0.00..5.38 rows=1 width=0)"
"        Index Cond: (texte_vectorise @@ '''pre'' & ''st'''::tsquery)"

On utilise bien l'index de recherche texte-plein, puis ensuite aucun index sur "asciiname" car c'est celui d'une liste retourné par "recherche plein texte" et non sur la colonne "asciiname" (si je suis bien la démarche).

J'ai donc tenté cette requête :

SELECT * FROM etab
WHERE lower(asciiname) LIKE lower(unaccent('pré%st%'));

Résultats : 4 rows (c'est toujours bon).
En revanche : 30 secondes de temps d'exécution.

Explain :

"Seq Scan on etab  (cost=0.00..326566.80 rows=38023 width=603)"
"  Filter: (lower((asciiname)::text) ~~ lower(unaccent('pré%st%'::text)))"

Aucun index

J'ai deux index sur "asciiname"

CREATE INDEX low_ascciname_idx
  ON etab
  USING btree
  (lower(asciiname::text));

CREATE INDEX asciiname_idx
  ON etab
  USING btree
  (asciiname);

Ce que je comprends, c'est qu'aucun index est utilisé car j'ajoute la fonction "unaccent" lors de ma requête qui n'est associé à aucun index sur cette colonne.

J'ai donc tenté le coup, ne sachant pas...

CREATE INDEX low_unac_ascciname_idx
  ON etab
  USING btree
  (lower(unaccent(asciiname::text)));

Impossible, râlant l'idée que la fonction unaccent est IMMUTABLE. (ce qui pour moi veut dire que "rien ne dis que cette fonction voir le fichier rules associé, ne sera inchangé ensuite). Vu comme ça on peut douter de tout.

Bon, tant pis alors.

Je retourne sur "Like"

explain SELECT * FROM all_geonames
WHERE lower(asciiname) LIKE lower('los% ang%');

Explain :

"Bitmap Heap Scan on etab  (cost=1005.92..93125.81 rows=760 width=603)"
"  Filter: (lower((asciiname)::text) ~~ 'los% ang%'::text)"
"  ->  Bitmap Index Scan on low_ascciname_idx  (cost=0.00..1005.73 rows=38023 width=0)"
"        Index Cond: ((lower((asciiname)::text) >= 'los'::text) AND (lower((asciiname)::text) < 'lot'::text))"

L'index : "low_ascciname_idx" est bien utilisé.
Rapide (26ms)

NB : 'los%ang%' ne donnera le même résultat que 'los% ang%' . Le premier étant vu comme "lo" suivis de "%ang%". Il suffit d'une erreur est...

Mais encore voila :

SELECT * FROM etab
WHERE lower(asciiname) LIKE lower('pré% st%');

Résultats : 0 rows.

Conclusion :
- Recherche like, plus rapide que recherche plein texte suivis de l'affinement like.
- Recherche like ne gère pas l'insensibilité à la casse des accents. (ce qui est fort dommage...).

Donc à ce que je me dirai. Postgresql ne s'aurai faire un like qui réponds rapidement par défaut en gérant en même temps cette insensibilité aux accents.

-> Effectuer donc cette conversion au niveau javascript lors de l'autocomplétion n'ayant l’impression de pouvoir faire autrement.

SAS

Membre

Re : Recherche plein texte - insenssible casse/accent/caractère spé./langue

(Juste un petite remarque, en passant.)

Vous pouvez aussi utiliser l'opérateur ilike, qui lui est insensible à la casse.

---

Stéphane Schildknecht
Conseil, formations et support PostgreSQL
http://www.loxodata.com

pelé

Membre

Re : Recherche plein texte - insenssible casse/accent/caractère spé./langue

Oui tout à fait, je ne l'avais pas précisé dans ma démarche c'est vrai.
Le problème c'est que cet opérateur n'utilise pas d'index :

explain select * from etab where asciiname ilike 'pre%';

"Seq Scan on etab  (cost=0.00..269532.00 rows=12167 width=603)"
"  Filter: ((asciiname)::text ~~* 'pre%'::text)"

Je suis donc partis sur un index lower(asciiname)

En revanche, je devrai peut-être mieux mettre ma colonne en "citext" et non pas "varying". Il me semblait que c'était plus long pourtant, à re-tester. Je préfère "varying" que "text", n'enregistrant de texte en base, c'est dommage.

De plus, ce module ne semble utiliser d'index ?

CREATE INDEX ascii_idx
  ON etab
  USING btree
  (asciiname);

CREATE INDEX low_ascii_idx
  ON etab
  USING btree
  (lower(asciiname::text));

asciiname citext NOT NULL,

explain select * from etab where asciiname like 'pre%';

"Seq Scan on etab  (cost=0.00..160255.38 rows=37105 width=50)"
"  Filter: (asciiname ~~ 'pre%'::citext)"

Dernière modification par pelé (04/07/2013 16:54:04)

gleu

Administrateur

Re : Recherche plein texte - insenssible casse/accent/caractère spé./langue

ILIKE ne peut pas utiliser d'index. Par contre, citext doit pouvoir utiliser un index.

---

Guillaume.