PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 15.6 » Langage SQL » Recherche plein texte » Tables et index

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

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.