Il existe deux types d'index qui peuvent être utilisés pour accélérer les recherches plein texte. 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.
Tester et déboguer la
recherche plein texte
CREATE INDEX nom ON table USING gist(colonne);
Crée un index GiST (Generalized Search Tree). La colonne peut être de type tsvector ou tsquery.
CREATE INDEX nom ON table USING gin(colonne);
Crée un index GIN (Generalized Inverted Index). La colonne doit être de type tsvector.
Il y a des différences de performances substantielles entre les deux types d'index, donc il est important de comprendre leurs caractéristiques.
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écessire. 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 bit, 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.
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éatoire aux enregistrements de la table sont lents, ceci limite l'utilité des index GiST. La probabilité de faux positifs dépends de plusieurs facteurs, en particulier le nombre de mots uniques, donc l'utilisation de dictionnaires qui réduisent ce nombre est recommandée.
Les index GIN ne sont pas à perte pour les requêtes standards mais leur performance dépend de façon logarithmique au nombre de mots uniques. (Néanmoins, les index GIN enregistrent seulement les mots (lexemes) des valeurs de type tsvector, et non pas les labels de poids. Donc, la re-vérification d'une ligne de table est nécessaire quand vous utilisez une requête qui indique des poids.
Dans le choix du type d'index à utiliser, GiST ou GIN, pensez à ces différences de performances :
Les recherches par index GIN sont environ trois fois plus rapides que celles par index GiST.
Les index GIN prennent trois fois plus de temps à se contruire que les index GiST.
Les index GIN sont un peu plus lents à mettre à jour que les index GiST, mais dix fois plus lent si le support de la mise à jour rapide a été désactivé (voir Section 53.3.1, « Technique GIN de mise à jour rapide » pour les détails)
Les index GIN sont entre deux et trois fois plus gros que les index GiST.
En règle générale, les index GIN sont meilleurs pour des données statiques car les recherches sont plus rapides. Pour des données dynamiques, les index GiST sont plus rapides à mettre à jour. Autrement dit, les index GiST sont très bons pour les données dynamiques et rapides si le nombre de mots uniques (lexemes) est inférieur à 100000 alors que les index GIN gèreront plus de 100000 lexemes plus facilement mais sont plus lents à mettre à jour.
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 en utilisant le module contrib/dblink. Ce dernier est possible car les fonctions de score utilisent les informations locales.