Accéder au contenu principal

Index B-Tree de PostgreSQL

Les index dans PostgreSQL sont utilisés pour améliorer la performance des requêtes de base de données en permettant une récupération plus rapide des enregistrements. L'index B-Tree est le type d'index par défaut et le plus couramment utilisé, idéal pour les opérations de recherche de données impliquant des comparaisons.

Utilisation

Les index B-Tree sont utilisés lorsque vous avez besoin de rechercher, d'extraire et de trier efficacement des données pour des opérations utilisant des requêtes d'égalité ou de plage. Ils sont automatiquement créés sur les clés primaires mais peuvent être créés manuellement sur d'autres colonnes afin d'optimiser les performances des requêtes.

CREATE INDEX index_name ON table_name (column_name);

Dans cette syntaxe, CREATE INDEX crée un nouvel index avec un nom spécifique sur une colonne particulière d'un tableau, améliorant ainsi les vitesses de recherche et d'extraction. Les index B-Tree stockent également les valeurs NULL, ce qui peut être particulièrement utile lorsque les requêtes impliquent de vérifier la présence ou l'absence de telles valeurs.

Exemples

1. Création d'un index de base

CREATE INDEX idx_employee_name ON employees (name);

Cet exemple crée un index B-Tree sur la colonne name du tableau employees afin d'accélérer les requêtes filtrant ou triant les noms des employés.

SELECT * FROM employees WHERE name = 'John Doe';

Cette requête bénéficie de l'index, ce qui améliore la vitesse de recherche.

2. Index multi-colonnes

CREATE INDEX idx_order_date_customer ON orders (order_date, customer_id);

Cette syntaxe crée un index B-Tree sur les deux tableaux order_date et customer_id, ce qui est utile pour les requêtes portant sur les deux champs du tableau orders.

SELECT * FROM orders WHERE order_date = '2023-10-01' AND customer_id = 123;

Ici, l'index optimise la recherche par order_date et customer_id.

3. Index unique

CREATE UNIQUE INDEX idx_unique_email ON users (email);

Un index B-Tree unique est créé sur la colonne email, ce qui garantit que toutes les adresses électroniques du tableau users sont distinctes, tout en améliorant les performances de la requête.

SELECT * FROM users WHERE email = '[email protected]';

L'index unique accélère cette requête en localisant rapidement l'adresse électronique spécifique.

Conseils et bonnes pratiques

  1. Indexer de manière sélective. Ne créez des index que sur les colonnes fréquemment utilisées dans les conditions de recherche ou les opérations de jointure.
  2. Surveillez l'utilisation de l'index. Analysez régulièrement l'utilisation des index à l'aide d'outils PostgreSQL tels que pg_stat_user_indexes et EXPLAIN pour vous assurer qu'ils sont bénéfiques.
  3. Soyez prudent avec les mises à jour. N'oubliez pas que les index peuvent ralentir les opérations INSERT, UPDATE et DELETE, et qu'il convient donc de trouver un équilibre entre le besoin de rapidité et les frais généraux liés à la gestion des index.
  4. Tenez compte de la taille de l'index. Les index volumineux peuvent consommer beaucoup d'espace disque, il convient donc de les gérer judicieusement.
  5. Utilisez des index partiels. Créez des index partiels lorsque seul un sous-ensemble de données est interrogé, afin d'améliorer les performances et de réduire la taille de l'index. Par exemple : 
    CREATE INDEX idx_active_users ON users (status) WHERE status = 'active';

Maintenance de l'index

  • Utilisez REINDEX pour reconstruire périodiquement les index, ce qui permet d'améliorer les performances et de récupérer de l'espace de stockage lorsque les données changent au fil du temps. Par exemple : 
    REINDEX INDEX idx_employee_name;