data / sql

Recherche plein texte avancée : pondération, trigrammes, autocomplétion

Leçon 221 exercice

Explication

Ce que vous allez apprendre

Pondérer le classement de pertinence avec setweight selon la provenance du texte
Comprendre pourquoi tsvector ne tolère aucune faute de frappe
Utiliser l'extension pg_trgm pour une recherche floue tolérante aux fautes
Créer un index GIN trigramme pour accélérer ILIKE '%motif%' et l'autocomplétion
Combiner recherche plein texte et filtres relationnels classiques dans une même requête

Dans quel contexte ?

Un moteur de recherche interne à un site d'actualités doit gérer trois attentes réalistes : classer les articles dont le mot-clé apparaît dans le TITRE avant ceux où il n'apparaît que dans le corps, tolérer qu'un utilisateur tape "Postgrs" au lieu de "Postgres", et proposer une autocomplétion instantanée pendant la frappe. Cette leçon construit ces trois briques une par une, au-dessus du tsvector/tsquery déjà vu.

D'abord, rappel du problème non résolu

La leçon sur les types avancés a introduit tsvector/tsquery pour chercher par sens plutôt que par caractères exacts. Mais un vrai moteur de recherche fait plus : il classe les meilleurs résultats en premier, tolère les fautes de frappe, et propose des suggestions pendant la saisie. Cette leçon répond à ces trois attentes, une par une.

Étape 1 : faire compter certains mots plus que d'autres

Toutes les correspondances ne se valent pas : trouver le mot cherché dans le TITRE d'un article compte généralement plus que le trouver noyé dans le corps du texte. setweight attribue des poids ('A', 'B', 'C'...) selon la provenance du texte, pour que ts_rank/ts_rank_cd classent les résultats de façon plus proche du jugement humain.

Il reste un problème : les fautes de frappe

tsvector reconnaît des formes de mots précises (les lexèmes) : il ne rapprochera jamais "Postgrs" de "Postgres" tout seul, faute de frappe non prévue. Il faut une autre technique pour ce cas.

Étape 2 : les trigrammes, une approche complémentaire

L'extension pg_trgm découpe chaque mot en séquences de trois caractères qui se chevauchent, puis compare la proportion de trigrammes communs entre deux chaînes. Plus deux mots partagent de trigrammes, plus ils sont jugés "similaires" — ce qui tolère naturellement une faute de frappe.

Technique	Résout	Ne résout pas
`tsvector`/`tsquery` + `setweight`	Recherche par sens, classement pondéré	Fautes de frappe
`pg_trgm` (`%`, `similarity`)	Tolérance aux fautes de frappe	Classement par pertinence sémantique
Index GIN trigramme	`ILIKE '%motif%'` rapide, autocomplétion	Compréhension du sens des mots

Un dernier problème : l'autocomplétion doit être rapide

Un ILIKE 'clav%' reste lent sans index adapté sur une grande table, et un ILIKE '%motif%' (recherche au milieu du mot) est encore pire, car un index B-Tree classique ne peut absolument pas l'accélérer.

Étape 3 : l'index GIN trigramme résout les deux cas

Un index GIN construit avec gin_trgm_ops accélère à la fois la recherche par préfixe et la recherche au milieu du mot — c'est la brique technique derrière la plupart des champs d'autocomplétion en production.

Piège et suite

Le piège classique est d'ajouter pg_trgm sans jamais créer l'index GIN correspondant : l'extension existe, mais les requêtes restent lentes. La prochaine leçon applique la même logique de "aller plus loin qu'une première solution simple" aux vues matérialisées, avec leur rafraîchissement automatisé.

Piège fréquent

Activer CREATE EXTENSION pg_trgm puis utiliser nom % 'terme' ou ILIKE '%motif%' sans avoir créé l'index USING GIN (nom gin_trgm_ops) ne provoque aucune erreur, mais chaque requête continue de scanner toute la table : l'extension seule n'apporte aucun gain de performance sans son index dédié.

Commandes & code

Recherche plein texte avancée

Au-delà du tsvector/tsquery de base : pondération du classement, tolérance aux fautes de frappe et autocomplétion.

-- pg_trgm complète tsvector : indispensable pour la similarité et le ILIKE indexé
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- Pondération : le titre compte plus que le contenu dans le classement de pertinence
ALTER TABLE articles ADD COLUMN recherche tsvector
    GENERATED ALWAYS AS (
        setweight(to_tsvector('french', coalesce(titre, '')), 'A') ||
        setweight(to_tsvector('french', coalesce(contenu, '')), 'B')
    ) STORED;

CREATE INDEX idx_articles_recherche ON articles USING GIN (recherche);

-- ts_rank_cd tient compte de la densité et de la proximité des termes (plus fin que ts_rank)
SELECT titre, ts_rank_cd(recherche, requete) AS pertinence
FROM articles, plainto_tsquery('french', 'optimisation base de données') AS requete
WHERE recherche @@ requete
ORDER BY pertinence DESC
LIMIT 10;

-- Surligner les extraits correspondants, comme un moteur de recherche
SELECT titre, ts_headline('french', contenu, plainto_tsquery('french', 'index'),
       'StartSel=<mark>, StopSel=</mark>, MaxWords=25, MinWords=15') AS extrait
FROM articles
WHERE recherche @@ plainto_tsquery('french', 'index');

-- websearch_to_tsquery : syntaxe proche d'un moteur public ("exact", -exclusion, OR)
SELECT titre FROM articles
WHERE recherche @@ websearch_to_tsquery('french', '"clé étrangère" -obsolete');

-- Recherche floue tolérante aux fautes de frappe, via similarité trigramme
SELECT nom, similarity(nom, 'Postgrs') AS score
FROM produits
WHERE nom % 'Postgrs'              -- % = "suffisamment similaire" (seuil par défaut 0.3)
ORDER BY score DESC;

-- Index GIN trigramme : accélère aussi ILIKE '%motif%' (un B-Tree classique ne le peut pas)
CREATE INDEX idx_produits_nom_trgm ON produits USING GIN (nom gin_trgm_ops);

EXPLAIN ANALYZE SELECT * FROM produits WHERE nom ILIKE '%clavier%';
-- avec l'index trigramme : Bitmap Index Scan au lieu d'un Seq Scan complet

-- Autocomplétion par préfixe, également accélérée par l'index trigramme
SELECT nom FROM produits WHERE nom ILIKE 'clav%' ORDER BY nom LIMIT 10;

-- Combiner recherche plein texte et filtres relationnels classiques
SELECT a.titre, ts_rank(a.recherche, q) AS pertinence
FROM articles a, plainto_tsquery('french', 'sécurité') q
WHERE a.recherche @@ q AND a.publie = TRUE AND a.cree_le > CURRENT_DATE - INTERVAL '1 year'
ORDER BY pertinence DESC;

Résumé

setweight pondère titre/contenu dans le classement ; ts_rank_cd affine ce classement par densité des termes.
ts_headline génère des extraits surlignés directement en SQL, sans logique côté application.
pg_trgm (%, similarity) tolère les fautes de frappe, là où tsvector exige des mots exacts (au lexème près).
Un index GIN trigramme (gin_trgm_ops) accélère aussi ILIKE '%motif%', utile pour l'autocomplétion.

Exercices pratiques

1 disponible

Mission : réparer un autocomplétion de recherche toujours aussi lente

Objectif : Diagnostiquer pourquoi pg_trgm n'accélère rien sans son index dédié, puis construire une recherche pondérée titre/contenu et une recherche tolérante aux fautes de frappe.

Contexte

Le moteur de recherche interne du site d'actualités classe déjà bien les résultats grâce à setweight, mais deux tickets remontent en même temps : la barre d'autocomplétion reste lente sur ILIKE '%clav%' alors que CREATE EXTENSION pg_trgm; a bien été exécuté, et un utilisateur qui tape "Postgrs" au lieu de "Postgres" obtient zéro résultat. La table produits contient plusieurs centaines de milliers de lignes.

Résoudre l’exercice →