Recherche FullText

Voir la vidéo

Découverte de PostgreSQL

Recherche FullText 12 min
Junior
Télécharger la vidéo
Description Sommaire

Lorsqu'il s'agit de rechercher un texte dans une base de données, le premier réflexe est souvent d'utiliser des requêtes LIKE.
Si, par exemple, on cherche tous les produits dont le titre contient le mot-clé "lapin", on peut écrire :

SELECT * FROM products WHERE title LIKE '%lapin%';

Cependant, cette approche a deux inconvénients majeurs :

  1. La recherche sur plusieurs champs devient compliquée avec des OR successifs.
  2. On ne peut pas classer les résultats par pertinence.

Un élément avec le titre "Lapin" devrait apparaître avant un autre qui ne contiendrait le mot que dans sa description. C'est là qu'intervient la recherche Full Text de PostgreSQL.

Indexer du texte avec to_tsvector

La recherche Full Text repose sur un format de stockage optimisé : le tsvector. Ce type de donnée convertit un champ texte en un vecteur contenant les mots-clés et leur position.

SELECT to_tsvector('french', title) FROM products;

Retourne :

'petit':1 'lapin':2 'blanc':3

Il est possible de fusionner plusieurs champs avec || :

SELECT to_tsvector('french', title) || to_tsvector('french', description) FROM products;

Pondérer les champs avec setweight

Tous les mots n'ont pas la même importance. Un mot dans le titre est plus pertinent que dans la description. Pour cela, on peut associer un poid aux vecteurs via setweight :

SELECT setweight(to_tsvector('french', title), 'A') ||
       setweight(to_tsvector('french', description), 'B')
FROM products;
  • A : poids le plus fort.
  • B, C, D : poids décroissants.

Le vecteur ressemblera à ça

'petit':1 'lapin':2A,4B 'blanc':3

Stocker les vecteurs pour améliorer les performances

Calculer to_tsvector à chaque requête est coûteux. Il vaut mieux sauvegarder le vecteur directement dans la base de données avec une colonne générée :

ALTER TABLE products ADD COLUMN search_field tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('french', title), 'A') ||
        setweight(to_tsvector('french', description), 'B')
    ) STORED;

-- On crée aussi un index pour améliorer les performances
CREATE INDEX idx_search_field ON products USING GIN (search_field);

Ce champ est automatiquement mis à jour lors d'une insertion ou modification d'un enregistrement.

Effectuer une recherche Full Text

On utilise @@ avec to_tsquery pour rechercher un mot :

SELECT * FROM products WHERE search_field @@ to_tsquery('french', 'lapin');

Pour une syntaxe plus naturelle, websearch_to_tsquery permet d'interpréter les requêtes comme un moteur de recherche :

SELECT * FROM products WHERE search_field @@ websearch_to_tsquery('french', 'lapin pruneau');
  • texte sans guillemets : le texte sans guillemets sera converti en termes séparés par des opérateurs &, comme s'il était traité par plainto_tsquery.
  • "texte entre guillemets" : le texte entre guillemets sera converti en termes séparés par des opérateurs <->, comme s'il était traité par phraseto_tsquery.
  • OR : le mot "or" sera converti en opérateur |.
  • - : un tiret sera converti en opérateur !.

Ordonner par pertinence

Pour ordonner les résultats par pertinence, on peut utiliser la fonction ts_rank ou ts_rank_cd :

SELECT *, ts_rank(search_field, to_tsquery('french', 'lapin')) AS rank
FROM products
WHERE search_field @@ to_tsquery('french', 'lapin')
ORDER BY rank DESC;

ts_rank_cd permet de prendre en compte la proximité des mots recherché dans le calcul du score.

Afficher un extrait avec ts_headline

Quand on crée un moteur de recherche on veut souvent proposer à l'utilisateur un extrait du contenu où apparait les mots clefs recherchés.

SELECT ts_headline('french', description, to_tsquery('french', 'lapin'))
FROM products;

ts_headline renvoie automatiquement un extrait en mettant en évidence les termes trouvés.

Limitations et alternatives

Malgré ses avantages, la recherche Full Text de PostgreSQL a quelques limites :

  • Le système de stockage utilise le disque, ce qui peut ralentir l'indexation et nécessiter de la place supplémentaire.
  • Elle ne gère pas les fautes de frappe (ex : "Lapni" au lieu de "Lapin").

Pour une meilleure tolérance aux fautes et une recherche en mémoire, des solutions comme Typesense ou Meilisearch seront plus adaptées.

Publié
Technologies utilisées
PostgreSQL
Auteur :
Grafikart
Partager