PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 12.18 » Langage SQL » Recherche plein texte » Types d'index préférées pour la recherche plein texte

12.9. Types d'index préférées pour la recherche plein texte

Il existe deux types d'index qui peuvent être utilisés pour accélérer les recherches plein texte : GIN et GiST. Notez que les index ne sont pas obligatoires pour la recherche plein texte, mais, dans les cas où une colonne est utilisée fréquemment dans une recherche, un index sera suffisamment intéressant.

Pour créer un tel index, faites l'un des deux :

CREATE INDEX name ON table USING GIN(column);

Crée un index GIN (Generalized Inverted Index). La colonne doit être de type tsvector.

CREATE INDEX name ON table USING gist(colonne);

Crée un index GiST (Generalized Search Tree). La colonne peut être de type tsvector ou tsquery.

Les index GIN sont le type d'index préféré pour la recherche plein texte. En tant qu'index inversés, ils contiennent une entrée d'index pour chaque mot (lexème), avec une liste compressée des emplacements correspondants. Les recherches multimots peuvent trouver la première correspondance, puis utiliser l'index pour supprimer les lignes qui ne disposent pas des autres mots recherchés. Les index GIN stockent uniquement les mots (lexèmes) des valeurs de type tsvector, et non pas les labels de poids. De ce fait, une vérification de la ligne de table est nécessaire quand une recherche implique les poids.

Un index GiST est à perte, signifiant que l'index peut produire des faux positifs, et il est nécessaire de vérifier la ligne de la table pour les éliminer. PostgreSQL le fait automatiquement si nécessaire. Les index GiST sont à perte, car chaque document est représenté dans l'index par une signature à longueur fixe. La signature est générée par le hachage de chaque mot en un bit aléatoire dans une chaîne à n bits, tous ces bits étant assemblés dans une opération OR qui produit une signature du document sur n bits. Quand deux hachages de mots sont identiques, nous avons un faux positif. Si tous les mots de la requête ont une correspondance (vraie ou fausse), alors la ligne de la table doit être récupérée pour voir si la correspondance est correcte.

Un index GiST peut être couvrant, c'est-à-dire utiliser la clause INCLUDE. Les colonnes incluses peuvent avoir des types de données sans aucune classe d'opérateur GiST. Les attributs inclus seront stockés sans compression.

La perte implique une dégradation des performances à cause de récupérations inutiles d'enregistrements de la table qui s'avèrent être de fausses correspondances. Comme les accès aléatoires aux enregistrements de la table sont lents, ceci limite l'utilité des index GiST. La probabilité de faux positifs dépend de plusieurs facteurs, en particulier le nombre de mots uniques, donc l'utilisation de dictionnaires qui réduisent ce nombre est recommandée.

Notez que le temps de construction de l'index GIN peut souvent être amélioré en augmentant maintenance_work_mem alors qu'un index GiST n'est pas sensible à ce paramètre.

Le partitionnement de gros ensembles et l'utilisation intelligente des index GIN et GiST autorise l'implémentation de recherches très rapides avec une mise à jour en ligne. Le partitionnement peut se faire au niveau de la base en utilisant l'héritage, ou en distribuant les documents sur des serveurs et en récupérant les résultats de la recherche, par exemple via un accès Foreign Data. Ce dernier est possible, car les fonctions de score utilisent les informations locales.