1. Comment fonctionne un index de base de données

Sans index, pour localiser un enregistrement précis dans une table de 10 millions de lignes, le moteur de base de données est contraint d’effectuer un Sequential Scan (Seq Scan), c’est-à-dire de lire l’intégralité des blocs du disque dur un à un (complexité temporelle O(N)). Un index est une structure de données auxiliaire optimisée permettant d’accéder directement aux tuples cibles en complexité O(log N).

2. La typologie des index dans PostgreSQL

  • B-Tree (Arbre équilibré) : L’index universel par défaut. Parfait pour les comparaisons d’égalité (=) et les recherches par intervalle (<, >, BETWEEN) ainsi que le tri (ORDER BY).
  • Hash Index : Dédié exclusivement aux comparaisons d’égalité stricte (=). Très compact mais ne supporte pas les intervalles ni les tris.
  • GIN (Generalized Inverted Index) : Index inversé conçu pour indexer des éléments composites contenant plusieurs valeurs par ligne : colonnes JSONB, tableaux PostgreSQL, et recherche plein texte (Full-Text Search).
  • GiST (Generalized Search Tree) : Index générique indispensable pour les données géospatiales (extensions PostGIS), les polygones géographiques et les plages de dates/horodatages (Range Types).

3. Index partiels et index couvrants (Covering Indexes)

-- Index partiel : indexer uniquement les comptes actifs (gain massif d'espace disque et de RAM)
CREATE INDEX idx_comptes_actifs ON comptes_bancaires(client_id) WHERE statut = 'ACTIF';

-- Index couvrant (INCLUDE) : stocker des colonnes supplémentaires dans les feuilles pour éviter la lecture de la table (Index Only Scan)
CREATE INDEX idx_transactions_compte_date ON transactions(compte_iban, date_transaction) INCLUDE (montant_chf);

Règle d’or : Chaque index accélère la lecture mais ralentit les écritures (INSERT, UPDATE, DELETE) car le SGBD doit maintenir l’arbre d’index à chaque modification.

PreviousNext