Documentation PostgreSQL 14.22 » Langage SQL » Recherche plein texte » Tables et index

Précédent | Niveau supérieur | Suivant
12.1. Introduction | Sommaire | 12.3. Contrôler la recherche plein texte

## 12.2. Tables et index

Les exemples de la section précédente illustrent la correspondance plein texte en utilisant des chaînes simples. Cette section montre comment rechercher les données de la table, parfois en utilisant des index.

### 12.2.1. Rechercher dans une table

Il est possible de faire des recherches plein texte sans index. Une requête qui ne fait qu'afficher le champ `title` de chaque ligne contenant le mot `friend` dans son champ `body` ressemble à ceci :

    SELECT title
    FROM pgweb
    WHERE to_tsvector('english', body) @@ to_tsquery('english', 'friend');

Cela trouve aussi les mots relatifs comme `friends` et `friendly`, car ils ont tous la même racine, le même lexème normalisé.

La requête ci-dessus spécifie que la configuration `english` doit être utilisée pour analyser et normaliser les chaînes. Nous pouvons aussi omettre les paramètres de configuration :

    SELECT title
    FROM pgweb
    WHERE to_tsvector(body) @@ to_tsquery('friend');

Cette requête utilisera l'ensemble de configuration indiqué par default_text_search_config.

Un exemple plus complexe est de sélectionner les dix documents les plus récents qui contiennent les mots `create` et `table` dans les champs `title` ou `body` :

    SELECT title
    FROM pgweb
    WHERE to_tsvector(title || ' ' || body) @@ to_tsquery('create & table')
    ORDER BY last_mod_date DESC LIMIT 10;

Pour plus de clarté, nous omettons les appels à la fonction `coalesce` qui est nécessaire pour rechercher les lignes contenant `NULL` dans un des deux champs.

Bien que ces requêtes fonctionnent sans index, la plupart des applications trouvent cette approche trop lente, sauf peut-être pour des recherches occasionnelles. Une utilisation pratique de la recherche plein texte réclame habituellement la création d'un index.

### 12.2.2. Créer des index

Nous pouvons créer un index GIN (Section 12.9) pour accélérer les recherches plein texte :

    CREATE INDEX pgweb_idx ON pgweb USING GIN(to_tsvector('english', body));

Notez que la version à deux arguments de `to_tsvector` est utilisée. Seules les fonctions de recherche plein texte qui spécifient un nom de configuration peuvent être utilisées dans les index sur des expressions (Section 11.7). Ceci est dû au fait que le contenu de l'index ne doit pas être affecté par default_text_search_config. Dans le cas contraire, le contenu de l'index peut devenir incohérent parce que différentes entrées pourraient contenir des `tsvector` créés avec différentes configurations de recherche plein texte et qu'il ne serait plus possible de deviner à quelle configuration fait référence une entrée. Il serait impossible de sauvegarder et restaurer correctement un tel index.

Comme la version à deux arguments de `to_tsvector` a été utilisée dans l'index ci-dessus, seule une référence de la requête qui utilise la version à deux arguments de `to_tsvector` avec le même nom de configuration utilise cet index. C'est-à-dire que `WHERE to_tsvector('english', body) @@ 'a & b'` peut utiliser l'index, mais `WHERE to_tsvector(body) @@ 'a & b'` ne le peut pas. Ceci nous assure qu'un index est seulement utilisé avec la même configuration que celle utilisée pour créer les entrées de l'index.

Il est possible de configurer des index avec des expressions plus complexes où le nom de la configuration est indiqué dans une autre colonne. Par exemple :

    CREATE INDEX pgweb_idx ON pgweb USING GIN(to_tsvector(config_name, body));

où `config_name` est une colonne de la table `pgweb`. Ceci permet l'utilisation de configurations mixtes dans le même index tout en enregistrant la configuration utilisée pour chaque entrée d'index. Ceci est utile dans le cas d'une bibliothèque de documents dans différentes langues. Encore une fois, les requêtes voulant utiliser l'index doivent être écrites pour correspondre à l'index, donc `WHERE to_tsvector(config_name, body) @@ 'a & b'`.

Les index peuvent même concaténer des colonnes :

    CREATE INDEX pgweb_idx ON pgweb USING GIN(to_tsvector('english', title || ' ' || body));

Une autre approche revient à créer une colonne `tsvector` séparée pour contenir le résultat de `to_tsvector`. Pour garder cette colonne automatiquement à jour par rapport à la source, on peut utiliser une colonne générée stockée. Cet exemple est une concaténation de `title` et `body`, en utilisant `coalesce` pour s'assurer qu'un champ est toujours indexé même si l'autre vaut `NULL` :

    ALTER TABLE pgweb
        ADD COLUMN textsearchable_index_col tsvector
                   GENERATED ALWAYS AS (to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))) STORED;

Puis nous créons un index GIN pour accélérer la recherche :

    CREATE INDEX textsearch_idx ON pgweb USING GIN(textsearchable_index_col);

Maintenant, nous sommes prêts pour des recherches plein texte rapides :

    SELECT title
    FROM pgweb
    WHERE textsearchable_index_col @@ to_tsquery('create & table')
    ORDER BY last_mod_date DESC
    LIMIT 10;

Un avantage de l'approche de la colonne séparée sur un index par expression est qu'il n'est pas nécessaire de spécifier explicitement la configuration de recherche plein texte dans les requêtes pour utiliser l'index. Comme indiqué dans l'exemple ci-dessus, la requête peut dépendre de `default_text_search_config`. Un autre avantage est que les recherches seront plus rapides, car il n'est plus nécessaire de refaire des appels à `to_tsvector` pour vérifier la correspondance de l'index. (Ceci est plus important lors de l'utilisation d'un index GiST par rapport à un index GIN ; voir la Section 12.9.) Néanmoins, l'approche de l'index par expression est plus simple à configurer et elle réclame moins d'espace disque, car la représentation `tsvector` n'est pas réellement stockée.