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 lequel utiliser.
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 ; par exemple, dans le plan de requête ci-dessous, la ligne Filter: indique que la sortie de l'index est de nouveau vérifiée :
EXPLAIN SELECT * FROM apod WHERE textsearch @@ to_tsquery('supernovae');
QUERY PLAN
-------------------------------------------------------------------------
Index Scan using textsearch_gidx on apod (cost=0.00..12.29 rows=2 width=1469)
Index Cond: (textsearch @@ '''supernova'''::tsquery)
Filter: (textsearch @@ '''supernova'''::tsquery)
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 mais leur performance dépendent logarithmiquement du nombre de mots uniques.
En fait, les index GIN stockent seulement les mots (lexemes) des valeurs tsvector, et non pas leur poids. Du coup, alors qu'un index GIN peut être considéré sans perte pour une requête qui ne précise pas de poids, il l'est pour les autres. Une deuxième vérification d'une ligne de table est nécessaire lors de l'utilisation d'une requête impliquant des poids. Malheureusement, dans la conception actuelle de PostgreSQL™, savoir si une nouvelle vérification est nécessaire est une propriété statique d'un opérateur particulier et n'est pas quelque chose qui peut être activé ou désactivé en ligne suivant les valeurs données à l'opérateur. Pour gérer cette situation sans imposer une surcharge dûe aux vérifications sur des requêtes qui n'en ont pas besoin, l'approche suivant a été adoptée :
L'opérateur standard de correspondance de texte @@ est marqué comme sans perte pour les index GIN.
Un opérateur de correspondance supplémentaire, @@@, est fourni et marqué comme à perte pour les index GIN. Sinon, cet opérateur se comporte exactement comme @@.
Quand une recherche par index GIN est lancée avec l'opérateur @@, le code de support d'index va renvoyer une erreur si la requête spécifie un poids. Ceci protège contre les mauvaises réponses dûes à un échec de la vérification des poids.
En bref, vous devez utiliser @@@ plutôt que @@ pour traiter les recherches par index GIN sur des requêtes qui impliquent des restrictions de poids. Pour les requêtes qui n'ont pas ces restrictions, les deux opérateurs fonctionneront mais @@ sera plus rapide. Cette bizarreté sera certainement corrigée dans une prochaine version de PostgreSQL™.
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 environ dix fois plus lents à mettre à jour que les index GiST.
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 et constraint_exclusion, 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.