La recherche Full Text dans PostegreSQL : guide complet

Qu'est-ce que la recherche en plein texte ?

La recherche en plein texte dans PostgreSQL est une fonctionnalité permettant d'analyser et de rechercher des données textuelles à l'aide de types de données spécifiques comme tsvector (texte préparé pour la recherche) et tsquery (requête textuelle). Elle utilise des algorithmes avancés pour le traitement linguistique, tels que la tokenisation et le stemming, afin de comprendre les mots-clés et leur contexte dans le texte.

Installation et configuration

Avant de commencer, assurez-vous que votre instance PostgreSQL est configurée pour utiliser les fonctionnalités de recherche en plein texte. À partir de sa version 12, PostgreSQLMoteur de gestion de base de données libre de droit. inclut ces outils par défaut, mais vous pourriez avoir besoin de créer un environnement de test pour vos essais.

Dans la suite de cet article, nous travaillerons avec la table suivante dans nos exemples :

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    titre TEXT,
    contenu TEXT
);

INSERT INTO articles (titre, contenu)
VALUES ('Introduction à PostgreSQL', 'PostgreSQL est une base de données relationnelle.'),
       ('Recherche en plein texte', 'La recherche en plein texte permet des recherches avancées.'),
       ('PostgreSQL et Soundex', 'Soundex est utile pour les recherches phonétiques.');

Recherche en plein texte sur plusieurs colonnes

Pour effectuer une recherche en plein texte dans PostgreSQL, il est courant de combiner plusieurs colonnes pour générer un index de recherche. Voici comment procéder :

Création d'un index GIN :

Un index GIN (Generalized Inverted Index) est idéal pour accélérer les recherches en plein texte.

CREATE INDEX idx_articles_fts ON articles USING GIN (
    to_tsvector('french', titre || ' ' || contenu)
);

Recherche en plein texte :

Utilisez la fonction to_tsvector pour transformer le texte en format plein texte et to_tsquery pour formuler une requête.

SELECT * FROM articles

WHERE to_tsvector('french', titre || ' ' || contenu) @@ to_tsquery('texte & PostgreSQL');

Dans cet exemple :

• @@ est l'opérateur qui compare le texte et la requête.
• & est l'opérateur logique pour dire "et".

Pondération des termes

La pondération permet de donner plus d'importance à certains termes ou à certaines colonnes dans la recherche. Par exemple, un mot-clé dans le titre peut être plus important que dans le contenu.

Exemple :

1. Ajoutez des pondérations :

   SELECT *,
          ts_rank(
              setweight(to_tsvector('french', titre), 'A') ||
              setweight(to_tsvector('french', contenu), 'B'),
              to_tsquery('PostgreSQL')
          ) AS rank
   FROM articles
   WHERE to_tsvector('french', titre || ' ' || contenu) @@ to_tsquery('PostgreSQL')
   ORDER BY rank DESC;
   

2. Dans cet exemple :

   • setweight applique une pondération ('A' étant le poids le plus élevé, 'D' le plus faible).
   • Les résultats sont triés par pertinence.

Recherche phonétique avec Soundex

Soundex est une méthode permettant de chercher des mots qui "sonnent" de manière similaire, utile pour des noms mal orthographiés ou des recherches approximatives. Cela nécéssite d'importer la librairie fuzzystrmatch et de développer 2 méthodes pour pouvoir utiliser Soundex avec la recherche plein texte :

Exemple :

1. Activez l'extension fuzzystrmatch :

   CREATE EXTENSION fuzzystrmatch;
   

2. Création de nos méthodes d'intégration , Pour utiliser Soundex avec la recherche en plein texte, nous devons procéder aux opérations suivantes :

• Conversion de chaque mot en son code Soundex.
• Concaténation des codes Soundex en tsvector ou tsquery.

CREATE FUNCTION soundex_tsvector(v_name text) RETURNS tsvector

AS
$$
BEGIN
    RETURN to_tsvector('simple',
                       coalesce(string_agg(soundex(coalesce(n, '')), ' '), ''))
        FROM regexp_split_to_table(coalesce(v_name, ''), '\s+') AS n;
END
$$ LANGUAGE plpgsql IMMUTABLE;

CREATE FUNCTION soundex_tsquery(v_name text) RETURNS tsquery

AS
$$
BEGIN
    RETURN to_tsquery('simple',
                      coalesce(string_agg(soundex(coalesce(n, '')), ' | '), ''))
        FROM regexp_split_to_table(v_name, '\s+') AS n;
END
$$ LANGUAGE plpgsql IMMUTABLE;

3. Rechercher :

SELECT * FROM articles

WHERE soundex_tsvector(titre) @@ soundex_tsquery('PostgreSQL');

Optimisation des requêtes

Dans les exemples précédents, nous formons les tsvector directement dans les conditions de la requête. Pour des tables qui contiennent beaucoup d'éléments et/ou des requêtes qui peuvent être exécutées fréquemment, il est recommandé d'intégrer la valeur du tsvector directement dans une colonne de la table :

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    titre TEXT,
    contenu TEXT,

    vector                  tsvector GENERATED ALWAYS AS (
        to_tsvector('simple', titre) ||
        to_tsvector('simple', contenu)
        ) STORED,

    vector_weighted         tsvector GENERATED ALWAYS AS (
        setweight(to_tsvector('simple', titre), 'A') ||
        setweight(to_tsvector('simple', contenu), 'D')
        ) STORED
);

CREATE INDEX idx_articles_vector ON articles USING GIN (vector);
CREATE INDEX idx_articles_vector_weighted ON articles USING GIN (vector_weighted);

La recherche full texte, un outil puissant

PostgreSQL offre un éventail d'outils puissants pour la recherche en plein texte, permettant de créer des fonctionnalités de recherche avancées, rapides et pertinentes. Que ce soit pour la recherche sur plusieurs colonnes, la pondération des termes ou l'utilisation de Soundex, ces techniques peuvent améliorer significativement l'expérience utilisateur.

Expérimentez avec ces outils pour découvrir tout leur potentiel et optimiser vos applications. Pour un audit de base de donnée, n'hésitez pas à nous contacter !