PostgreSQL, le système de gestion de bases de données relationnelles open source populaire, propose différents types d'index pour optimiser les performances des requêtes et améliorer l'efficacité de la récupération des données. Dans cet article, nous apprendrons comment créer différents types d'index dans PostgreSQL. Dans la mesure du possible, les index seront appliqués à l'exemple de base de données gratuit "dvdrental" sample database à l'aide des instructions DML ainsi que de a class="default-links" href="/download/navicat
1. Index B-Tree :
L’index B-Tree est le type d'index par défaut dans PostgreSQL, adapté à différents types de données, notamment le texte, les numériques et l'horodatage. Il organise les données dans une structure arborescente équilibrée, facilitant les requêtes de plage et les recherches d'égalité efficaces. Créons un index B-Tree sur la colonne "customer_id" de la table "payment" :
CREATE INDEX btree_customer_id_idx ON payment(customer_id);
Dans Navicat, vous trouverez des index dans l'onglet "Index" du Concepteur de tables. Pour créer l'index ci-dessus, nous saississon « btree_customer_id_idx » dans le champ Nom, choisissons « customer_id » pour les « Champs » et sélectionnons « B-Tree » dans la liste déroulante Méthode d'index :
Voici l'index btree_customer_id_idx avec tous les champs ci-dessus renseignés :
Cliquez sur le bouton Enregistrer puis créez l'index.
2. Hash Index:
Les index de hachage sont optimaux pour les contrôles d'égalité mais moins efficaces pour les requêtes de plage. Ils utilisent des fonctions de hachage pour mapper les clés aux entrées d'index. Voici comment créer un index Hash sur la colonne "film_id" de la table "inventaire", en utilisant d'abord une instruction DML :
CREATE INDEX hash_film_id_idx ON inventory USING HASH(film_id);
Et maintenant avec Navicat :
3. GiST Index:
Les index GiST (Generalized Search Tree) prennent en charge divers types de données et requêtes complexes, ce qui les rend polyvalents pour des applications telles que la recherche en texte intégral et les types de données géométriques.
Voici un exemple de création d'un index GiST sur une colonne géométrique :
CREATE INDEX index_geometry ON table_name USING GIST (geometry_column);
4. SP-GiST Index:
Les index SP-GiST (Space-Partitioned Generalized Search Tree) conviennent aux types de données avec des structures multidimensionnelles ou hiérarchiques. Ils indexent efficacement les arbres non équilibrés.
Voici un exemple de création d'un index SP-GiST sur une colonne tsvector :
CREATE INDEX index_text_search ON table_name USING SPGIST (tsvector_column);
5. GIN Index:
L'index inversé généralisé (GIN) est idéal pour les cas tels que la recherche en texte intégral, les types de tableaux et les types de données composites. Ils sont efficaces pour les types de données comportant plusieurs clés ou composants. Créons un index GIN sur la colonne "titre" de la table "film" pour la recherche en texte intégral :
CREATE INDEX gin_title_idx ON film USING gin(to_tsvector('english', title));
Voici l'onglet Index de la table "film" dans Navicat avec l'index gin_title_idx ajouté :
6. BRIN Index:
Block Range Index (BRIN) convient aux grandes tables avec des données triées, car il indexe des plages de blocs de données plutôt que des lignes individuelles. Il est efficace pour les colonnes avec corrélation entre des valeurs adjacentes. Voici comment créer un index BRIN sur la colonne "rental_date" de la table "rental" :
CREATE INDEX brin_rental_date_idx ON rental USING brin(rental_date);
Voici l'index brin_rental_date_idx dans Navicat :
Conclusion
PostgreSQL propose une gamme de types d'index répondant à divers types de données et exigences de requêtes. Comprendre les caractéristiques de chaque type d'index aide les administrateurs et les développeurs de bases de données à prendre des décisions éclairées lors de l'optimisation des performances des bases de données. Parallèlement, l'utilisation d'un outil tel Navicat for PostgreSQL 16 qui facilite grandement l'utilisation des index.