Recherche en texte intégral avec PostgreSQL

Introduction

Dans ce labo, vous apprendrez à implémenter la recherche en texte intégral dans PostgreSQL en utilisant TSVECTOR. Vous commencerez par créer une table articles et ajouter des exemples de données. Ensuite, vous ajouterez une colonne search_vector et créerez un index TSVECTOR sur celle-ci. Enfin, vous configurerez un déclencheur (trigger) pour mettre à jour automatiquement la colonne search_vector chaque fois que les colonnes title ou content sont modifiées. Cela vous permettra d'effectuer des recherches en texte intégral efficaces.

Skills Graph

Configuration de la table Articles

Dans cette étape, vous allez créer la table articles et insérer des exemples de données. Cette table sera utilisée pour démontrer les capacités de recherche en texte intégral dans PostgreSQL.

Tout d'abord, connectez-vous à la base de données PostgreSQL en tant qu'utilisateur postgres en utilisant la commande suivante dans votre terminal :

sudo -u postgres psql

Maintenant, créez la table articles avec la commande SQL suivante :

CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255),
    content TEXT
);

Cette commande crée une table nommée articles avec trois colonnes : id, title et content. La colonne id est une clé primaire à auto-incrémentation.

Ensuite, insérez des exemples de données dans la table articles :

INSERT INTO articles (title, content) VALUES
('PostgreSQL Tutorial', 'This is a comprehensive tutorial on PostgreSQL.'),
('Full Text Search in PostgreSQL', 'Learn how to implement full text search using TSVECTOR in PostgreSQL.'),
('PostgreSQL Performance Tuning', 'Tips and tricks to improve the performance of your PostgreSQL database.');

Cette commande insère trois lignes dans la table articles, chacune avec un titre et un contenu différents.

Vous pouvez vérifier que les données ont été insérées correctement en exécutant la requête suivante :

SELECT * FROM articles;

Vous devriez voir les trois lignes que vous venez d'insérer.

 id |             title              |                                       content
----+----------------------------------+-----------------------------------------------------------------------
  1 | PostgreSQL Tutorial            | This is a comprehensive tutorial on PostgreSQL.
  2 | Full Text Search in PostgreSQL | Learn how to implement full text search using TSVECTOR in PostgreSQL.
  3 | PostgreSQL Performance Tuning  | Tips and tricks to improve the performance of your PostgreSQL database.
(3 rows)

Ajout de la colonne search_vector et création d'un index GIN

Dans cette étape, vous allez ajouter la colonne search_vector à la table articles et créer un index GIN sur celle-ci. La colonne search_vector stockera la représentation TSVECTOR des colonnes title et content, et l'index GIN accélérera les recherches en texte intégral.

Tout d'abord, ajoutez la colonne search_vector à la table articles :

ALTER TABLE articles ADD COLUMN search_vector TSVECTOR;

Ensuite, mettez à jour la colonne search_vector avec la représentation TSVECTOR des colonnes title et content. Vous pouvez utiliser la fonction to_tsvector pour convertir du texte en TSVECTOR.

UPDATE articles SET search_vector = to_tsvector('english', title || ' ' || content);

Dans la commande ci-dessus, 'english' spécifie la configuration de langue à utiliser pour le traitement du texte. L'opérateur || concatène les colonnes title et content avec un espace entre les deux.

Maintenant, créez un index GIN sur la colonne search_vector :

CREATE INDEX articles_search_vector_idx ON articles USING GIN (search_vector);

GIN (Generalized Inverted Index - Index inversé généralisé) est un type d'index qui est bien adapté à l'indexation des colonnes TSVECTOR. Cet index permettra à PostgreSQL de trouver rapidement les documents qui correspondent à une requête de recherche donnée.

Vous pouvez vérifier que l'index a été créé en exécutant la commande suivante :

\d articles

Vous devriez voir l'index articles_search_vector_idx listé dans la sortie.

                                     Table "public.articles"
      Column      |          Type          | Collation | Nullable |                 Default
------------------+------------------------+-----------+----------+-----------------------------------------
 id               | integer                |           | not null | nextval('articles_id_seq'::regclass)
 title            | character varying(255) |           |          |
 content          | text                   |           |          |
 search_vector    | tsvector               |           |          |
Indexes:
    "articles_pkey" PRIMARY KEY, btree (id)
    "articles_search_vector_idx" gin (search_vector)

Effectuer des recherches en texte intégral avec TSQUERY

Maintenant que vous avez configuré la colonne search_vector et l'index GIN, vous pouvez effectuer des recherches en texte intégral à l'aide des opérateurs TSQUERY.

PostgreSQL fournit la fonction to_tsquery pour convertir une chaîne de texte en un objet TSQUERY. Vous pouvez ensuite utiliser l'opérateur @@ pour faire correspondre le TSQUERY avec la colonne search_vector.

Par exemple, pour rechercher les articles qui contiennent à la fois "PostgreSQL" et "tutorial" (tutoriel), vous pouvez utiliser la requête suivante :

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & tutorial');

L'opérateur & dans la fonction to_tsquery spécifie que les deux termes doivent être présents dans le search_vector.

Vous pouvez également utiliser l'opérateur | pour rechercher les articles qui contiennent soit "PostgreSQL", soit "tutorial" (tutoriel) :

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'PostgreSQL | tutorial');

Pour rechercher les articles qui contiennent l'expression "full text search" (recherche en texte intégral), vous pouvez utiliser la requête suivante :

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', '''full text search''');

Le fait d'encadrer l'expression entre guillemets simples indique à to_tsquery de la traiter comme une seule expression.

Essayez d'expérimenter avec différentes requêtes de recherche pour voir comment fonctionnent les opérateurs TSQUERY.

Classer les résultats par pertinence

PostgreSQL fournit la fonction ts_rank pour classer les résultats de recherche par pertinence. La fonction ts_rank prend le search_vector et le TSQUERY en entrée et renvoie une valeur de classement (rank).

Pour classer les résultats de recherche par pertinence, vous pouvez utiliser la requête suivante :

SELECT title, ts_rank(search_vector, to_tsquery('english', 'PostgreSQL')) AS rank
FROM articles
WHERE search_vector @@ to_tsquery('english', 'PostgreSQL')
ORDER BY rank DESC;

Cette requête recherche les articles qui contiennent "PostgreSQL", calcule le classement de chaque article à l'aide de la fonction ts_rank, puis trie les résultats par classement dans l'ordre décroissant.

La colonne rank dans la sortie affiche le score de pertinence pour chaque article. Les articles avec des scores de pertinence plus élevés sont listés en premier.

Maintenir la colonne search_vector à jour avec un Trigger (déclencheur)

Pour vous assurer que la colonne search_vector est toujours à jour, vous pouvez créer un trigger (déclencheur) qui met automatiquement à jour la colonne chaque fois que les colonnes title ou content sont modifiées.

Tout d'abord, créez une fonction de trigger (déclencheur) qui met à jour la colonne search_vector :

CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
  NEW.search_vector := to_tsvector('english', NEW.title || ' ' || NEW.content);
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Cette fonction prend les nouvelles valeurs des colonnes title et content et met à jour la colonne search_vector avec la représentation TSVECTOR des valeurs concaténées.

Ensuite, créez un trigger (déclencheur) qui appelle la fonction update_search_vector chaque fois qu'une ligne est insérée ou mise à jour dans la table articles :

CREATE TRIGGER articles_update_search_vector
BEFORE INSERT OR UPDATE ON articles
FOR EACH ROW
EXECUTE FUNCTION update_search_vector();

Ce trigger (déclencheur) mettra automatiquement à jour la colonne search_vector chaque fois que les colonnes title ou content sont modifiées.

Pour tester le trigger (déclencheur), mettez à jour le contenu d'un article existant :

UPDATE articles SET content = 'This is an updated comprehensive tutorial on PostgreSQL with advanced features.' WHERE id = 1;

Maintenant, recherchez les articles qui contiennent le mot "features" :

SELECT * FROM articles WHERE search_vector @@ to_tsquery('english', 'features');

Vous devriez voir l'article mis à jour dans les résultats de recherche.

Résumé

Dans ce labo, vous avez appris à implémenter la recherche en texte intégral (full-text search) dans PostgreSQL en utilisant TSVECTOR. Vous avez créé une table articles, ajouté une colonne search_vector, créé un index GIN sur la colonne search_vector, et configuré un trigger (déclencheur) pour mettre automatiquement à jour la colonne search_vector chaque fois que les colonnes title ou content sont modifiées. Vous avez également appris à effectuer des recherches en texte intégral en utilisant les opérateurs TSQUERY et à classer les résultats de recherche par pertinence.