Comment créer des index dans PostgreSQL

Les indices sont des tables de recherche spécialisées utilisées par les moteurs de recherche de banque de données pour accélérer les résultats des requêtes. Un index est une référence aux informations d'un tableau. Par exemple, si les noms d'un carnet de contacts ne sont pas classés par ordre alphabétique, vous devrez descendre chaque ligne et rechercher dans chaque nom avant d'atteindre le numéro de téléphone spécifique que vous recherchez. Un index accélère les commandes SELECT et les phrases WHERE, en effectuant la saisie de données dans les commandes UPDATE et INSERT. Que les index soient insérés ou supprimés, il n'y a aucun impact sur les informations contenues dans la table. Les index peuvent être spéciaux de la même manière que la limitation UNIQUE permet d'éviter les enregistrements de réplique dans le champ ou l'ensemble de champs pour lesquels l'index existe.

Syntaxe générale

La syntaxe générale suivante est utilisée pour créer des index.

>> CREATE INDEX nom_index ON nom_table (nom_colonne);

Pour commencer à travailler sur les index, ouvrez le pgAdmin de Postgresql depuis la barre d'application. Vous trouverez l'option 'Serveurs' affichée ci-dessous. Faites un clic droit sur cette option et connectez-la à la base de données.

Comme vous pouvez le voir, la base de données 'Test' est répertoriée dans l'option 'Bases de données'. Si vous n'en avez pas, faites un clic droit sur "Bases de données", accédez à l'option "Créer" et nommez la base de données selon vos préférences.

Développez l'option "Schémas" et vous y trouverez l'option "Tables". Si vous n'en avez pas, cliquez dessus avec le bouton droit de la souris, accédez à « Créer » et cliquez sur l'option « Tableau » pour créer un nouveau tableau. Puisque nous avons déjà créé la table 'em' vous pouvez la voir dans la liste.

Essayez la requête SELECT dans l'éditeur de requête pour récupérer les enregistrements de la table 'emp', comme indiqué ci-dessous.

>> SÉLECTIONNER * DU public.emp ORDER BY « id » ASC ;

Les données suivantes seront dans la table 'emp'.

Créer des index à une seule colonne

Développez le tableau "emp" pour trouver différentes catégories, e.g., Colonnes, contraintes, index, etc. Cliquez avec le bouton droit sur « Index », accédez à l'option « Créer » et cliquez sur « Indexer » pour créer un nouvel index.

Construire un index pour la table "emp" donnée, ou l'affichage final, à l'aide de la fenêtre de dialogue Index. Ici, il y a deux onglets : 'Général' & 'Définition.' Dans l'onglet 'Général', insérez un titre spécifique pour le nouvel index dans le champ 'Nom'. Choisissez le « tablespace » sous lequel le nouvel index sera stocké à l'aide de la liste déroulante à côté de « Tablespace.'  Comme dans la zone « Commentaire », faites des commentaires d'index ici. Pour commencer ce processus, accédez à l'onglet « Définition ».

Ici, spécifiez la 'Méthode d'accès' en sélectionnant le type d'index. Après cela, pour créer votre index comme « Unique », plusieurs autres options y sont répertoriées. Dans la zone 'Colonnes', appuyez sur le signe '+' et ajoutez les noms de colonnes à utiliser pour l'indexation. Comme vous pouvez le voir, nous avons appliqué l'indexation uniquement à la colonne "Téléphone". Pour commencer, sélectionnez la section SQL.

L'onglet SQL affiche la commande SQL qui a été créée par vos entrées tout au long de la boîte de dialogue Index. Cliquez sur le bouton 'Enregistrer' pour créer l'index.

Encore une fois, allez à l'option 'Tables' et accédez à la table 'emp'. Actualisez l'option 'Index' et vous y trouverez l'index 'index_on_phone' nouvellement créé.

Maintenant, nous allons exécuter la commande EXPLAIN SELECT pour vérifier les résultats des index avec la clause WHERE. Cela se traduira par la sortie suivante, qui dit, 'Seq Scan on emp.' Vous pouvez vous demander pourquoi cela s'est produit lorsque vous utilisez des index.

Raison : Le planificateur Postgres peut décider de ne pas avoir d'index pour diverses raisons. Le stratège prend les meilleures décisions la plupart du temps, même si les raisons ne sont pas toujours claires. C'est bien si une recherche d'index est utilisée dans certaines requêtes, mais pas dans toutes. Les entrées renvoyées par l'une ou l'autre table peuvent varier en fonction des valeurs fixes renvoyées par la requête. Comme cela se produit, une analyse de séquence est presque toujours plus rapide qu'une analyse d'index, ce qui indique que le planificateur de requêtes avait peut-être raison de déterminer que le coût d'exécution de la requête de cette manière est réduit.

Créer des index de plusieurs colonnes

Pour créer des index à plusieurs colonnes, ouvrez le shell de ligne de commande et considérez le tableau suivant « étudiant » pour commencer à travailler sur des index avec plusieurs colonnes.

>> SÉLECTIONNER * DE L'Étudiant ;

Écrivez-y la requête CREATE INDEX suivante. Cette requête va créer un index nommé 'new_index' dans les colonnes 'sname' et 'age' de la table 'student'.

>> CRÉER UN INDEX new_index ON Étudiant (nom, âge);

Maintenant, nous allons lister les propriétés et les attributs de l'index 'new_index' nouvellement créé à l'aide de la commande '\d'. Comme vous pouvez le voir sur l'image, il s'agit d'un index de type btree qui a été appliqué aux colonnes 'sname' et 'age'.

>> \d nouvel_index;

Créer un index UNIQUE

Pour construire un index unique, supposons la table 'em' suivante.

>> SELECT * FROM emp;

Exécutez la requête CREATE UNIQUE INDEX dans le shell, suivie du nom d'index 'empind' dans la colonne 'name' de la table 'emp'. Dans la sortie, vous pouvez voir que l'index unique ne peut pas être appliqué à une colonne avec des valeurs "nom" en double.

>> CRÉER UN INDEX unique empind ON emp (nom);

Assurez-vous d'appliquer l'index unique uniquement aux colonnes qui ne contiennent aucun doublon. Pour la table 'emp', vous pouvez supposer que seule la colonne 'id' contient des valeurs uniques. Nous allons donc lui appliquer un index unique.

>> CRÉER UN INDEX unique empind ON emp (id);

Voici les attributs de l'index unique.

>> \d empid;

Indice de chute

L'instruction DROP est utilisée pour supprimer un index d'une table.

>> DROP INDEX empind;

Conclusion

Bien que les index soient conçus pour améliorer l'efficacité des bases de données, dans certains cas, il n'est pas possible d'utiliser un index. Lors de l'utilisation d'un index, les règles suivantes doivent être prises en compte :

• Les index ne doivent pas être supprimés pour les petites tables.
• Tables avec de nombreuses opérations de mise à niveau/mise à jour ou d'ajout/insertion par lots à grande échelle.
• Pour les colonnes avec un pourcentage substantiel de valeurs NULL, les index ne peuvent pas être mélangés-
• vendre.
• L'indexation doit être évitée avec des colonnes régulièrement manipulées.