Le parcours de découverte de PostgreSQL

Postgres a plusieurs types d'index, et chaque nouvelle version semble en ajouter de nouveaux. Chaque type d'index est utile, mais le type à utiliser dépend (1) du type de données, parfois (2) des données sous-jacentes dans la table et (3) du type de recherche effectuée. Dans le contenu suivant, nous présenterons les types d'index que vous pouvez utiliser dans Postgres et quand vous devez utiliser quel type d'index. Avant de commencer, voici une liste de types d'index que nous allons vous guider :

B-Arbre
Indice inversé généralisé (GIN)
Arbre de recherche inversé généralisé (GiST)
Espace GiST partitionné (SP-GiST)
Indice de plage de blocs (BRIN)
Hachage
Commençons maintenant par l'indexation.

Dans Postgres, les index B-Tree sont les index les plus couramment utilisés

Si vous êtes diplômé en informatique, l'indexation B-Tree peut être le premier index que vous apprenez. Les index B-tree créent un arbre qui maintient toujours son équilibre. Lorsqu'il recherche quelque chose en fonction de l'index, il parcourt l'arborescence pour trouver la clé et renvoie les données que vous recherchez. L'utilisation d'un index est nettement plus rapide qu'une analyse séquentielle, car elle ne nécessite que la lecture de quelques pages (lorsque vous ne renvoyez que quelques enregistrements), par opposition à l'analyse séquentielle de milliers d'enregistrements.

Si vous exécutez une instruction CREATE INDEX standard, elle créera un index B-tree pour vous. Les index B-tree sont utiles pour la plupart des types de données, tels que le texte, les nombres et les horodatages. Si vous commencez tout juste à utiliser des index dans votre base de données et que vous n'utilisez pas trop de fonctionnalités avancées de Postgres sur votre base de données, l'utilisation d'un index B-Tree standard est probablement votre meilleure option.

Index GIN pour les colonnes à valeurs multiples

L'index inversé généralisé, généralement appelé GIN, convient principalement aux types de données lorsqu'une seule colonne contient plusieurs valeurs.

Selon la documentation Postgres :

"GIN est conçu pour gérer les situations dans lesquelles l'entrée en cours d'indexation est une valeur composée et la requête traitée par l'index doit rechercher une valeur qui apparaît dans l'entrée composée. Par exemple, cette entrée peut être un document et le La requête peut rechercher les caractères spécifiés contenus dans le document. »

Les types de données les plus courants inclus dans cette plage sont :

hStore
Tableau
Gamme
JSONB
L’un des aspects les plus satisfaisants des index GIN est leur capacité à comprendre les données stockées dans des valeurs composites. Toutefois, étant donné qu'un index GIN nécessite une connaissance spécifique de la structure des données pour chaque type individuel ajouté, tous les types de données ne sont pas pris en charge par l'index GIN.

Index GiST pour les lignes avec des valeurs qui se chevauchent

L'index GiST (Generalized Inverted Seach Tree) convient principalement lorsque vos données chevauchent d'autres lignes de données dans la même colonne. La meilleure utilisation des index GiST est si vous déclarez un type de données géométriques et que vous souhaitez savoir si deux polygones contiennent des points. Dans un cas, un point particulier peut être contenu dans une boîte, alors que dans le même temps, d'autres points n'existent que dans un polygone. Les types de données courants indexés à l'aide de GiST sont :

Type de géométrie
Types de texte nécessitant une recherche en texte intégral
Il existe de nombreuses limites fixes sur la taille des index GiST, sinon les index GiST peuvent devenir extrêmement volumineux. Au prix de cela, les index GiST sont avec perte (inexacts).

Selon la documentation officielle :

« Les index GiST entraînent des pertes, ce qui signifie que l'index peut produire de fausses correspondances, il est donc nécessaire de vérifier les vraies lignes du tableau pour éliminer les fausses correspondances (PostgreSQL effectuera automatiquement cette action si nécessaire) »

.

Cela ne signifie pas que vous obtiendrez un faux résultat, cela signifie simplement que Postgres effectue un petit travail supplémentaire pour filtrer ces faux résultats avant de vous renvoyer les données.

Remarque : les index GIN et GiST peuvent souvent être utilisés sur le même type de données. Généralement, on a de bonnes performances mais prend beaucoup d’espace disque, et vice versa. Lorsqu'il s'agit de GIN contre GiST, il n'existe pas de solution universelle qui fonctionnera dans toutes les situations, mais les règles ci-dessus devraient s'appliquer aux situations les plus courantes.

Indice SP-GiST pour des données plus volumineuses

L'index GiST spatialement partitionné (SP-GiST) adopte l'arbre spatialement partitionné de Purdue Research. Les index SP-GiST sont souvent utilisés lorsque vos données ont un facteur de regroupement naturel et ne constituent pas un arbre équilibré. Les numéros de téléphone en sont un excellent exemple (au moins les numéros de téléphone américains le sont). Ils ont le format suivant :

Indicatif régional à 3 chiffres
Numéro de préfixe à 3 chiffres (lié aux anciens centraux téléphoniques)
Numéro de ligne à 4 chiffres
Cela signifie qu'il existe un facteur de regroupement naturel au niveau des trois premiers chiffres du premier ensemble, suivi du deuxième ensemble de trois chiffres, puis que les nombres sont répartis uniformément. Cependant, dans certains indicatifs régionaux de numéros de téléphone, la saturation est plus élevée que dans d’autres. Le résultat peut être un arbre très déséquilibré. Étant donné qu’il existe dès le départ un facteur d’agrégation naturel et que les données ne sont pas réparties de manière égale, des données telles que les numéros de téléphone pourraient constituer un bon argument pour SP-GiST.

Indice BRIN pour des données plus volumineuses

Les index de plage de blocs (BRIN) se concentrent sur certaines situations comme SP-GiST, ils sont mieux utilisés lorsque les données ont un ordre naturel et que la taille des données est souvent importante. Si vous avez un milliard d'enregistrements par ordre chronologique, BRIN peut s'avérer utile. Si vous interrogez un grand ensemble de données naturellement regroupées, telles que plusieurs codes postaux, BRIN peut vous aider à garantir que des codes postaux similaires sont stockés à des emplacements proches sur le disque.

Lorsque vous disposez d'une très grande base de données triée par date ou code postal, l'index BRIN vous permet d'ignorer ou d'exclure très rapidement certaines données inutiles. De plus, les index BRIN sont relativement petits par rapport à la taille globale des données. Ainsi, lorsque vous disposez d'un ensemble de données volumineux, les index BRIN peuvent fonctionner mieux.

Hash index, enfin plus peur du crash

Les index de hachage existent dans Postgres depuis des années, cependant, jusqu'à la sortie de Postgres 10, il y avait une énorme mise en garde concernant leur utilisation : ils n'étaient pas enregistrés dans les WAL. Cela signifie que si votre serveur tombe en panne et que vous ne pouvez pas basculer vers un serveur de secours ou restaurer à partir d'une archive en utilisant par exemple wal-g, vous perdrez cet index jusqu'à ce que vous le reconstruisiez. Avec la sortie de Postgres 10, ils sont désormais enregistrés dans WAL, vous pouvez donc envisager de les utiliser à nouveau, mais la vraie question est : devriez-vous le faire ?

Les index de hachage fournissent parfois des recherches plus rapides que les index B-Tree et sont également rapides à créer. Le plus gros problème est qu'ils sont limités aux seules opérations de comparaison « d'égalité », vous ne pouvez donc les utiliser que pour des recherches de correspondances exactes. Cela rend les index de hachage beaucoup moins flexibles que les index B-Tree couramment utilisés, et vous ne devriez pas les considérer comme un remplacement, mais comme un index pour des cas particuliers.

Lequel devriez-vous utiliser ? Nous venons d'en présenter beaucoup. Si vous avez un peu peur, c'est normal. Si vous le savez déjà, CREATE INDEX créera toujours un index pour vous à l'aide d'un B-Tree, et la bonne nouvelle est que les performances de Postgres sont bonnes ou très bonnes pour la plupart des bases de données. :) Si vous envisagez d'utiliser davantage de fonctionnalités de Postgres, voici un aide-mémoire pour l'utilisation d'autres types d'index Postgres :

B-Tree - adapté à la plupart des types de données et requêtes

GIN - pour JSONB/hstore/arrays
GiST - pour la recherche en texte intégral et les types de données géométriques
SP-GiST - adapté aux grands ensembles de données avec des facteurs d'agrégation naturels mais une distribution inégale
BRIN - adapté aux très grands ensembles de données avec ordre séquentiel
Hash - bon pour les opérations d'égalité, mais généralement un index B-Tree suffit toujours.
Si vous avez des questions ou des commentaires sur cet article, n'hésitez pas à rejoindre notre chaîne Slack.

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!