12.2. Tables et index

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');
    

Ceci trouve aussi les mots relatifs comme friends et friendly car ils ont tous la même racine, le même lexeme 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 clareté, 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.

Nous pouvons créer un index GIN (Section 12.9, « Types d'index GiST et GIN ») 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, « Index d'expressions »). 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 configuration mixe 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. 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;
UPDATE pgweb SET textsearchable_index_col =
     to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));
    

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êt 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;
    

Lors de l'utilisation d'une colonne séparée pour stocker la représentation tsvector, il est nécessaire d'ajouter un trigger pour obtenir une colonne tsvector à jour à tout moment suivant les modifications de title et body. La Section 12.4.3, « Triggers pour les mises à jour automatiques » explique comment le faire.

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, « Types d'index GiST et GIN ».) 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.