Techniques d’indexation

Module J4

Dalibo SCOP

25.06

juin 2025

Sur ce document

Formation Module J4
Titre Techniques d’indexation
Révision 25.06
PDF https://dali.bo/j4_pdf
EPUB https://dali.bo/j4_epub
HTML https://dali.bo/j4_html
Slides https://dali.bo/j4_slides
TP https://dali.bo/j4_tp
TP (solutions) https://dali.bo/j4_solutions

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

  • PaternitĂ©
  • Pas d’utilisation commerciale
  • Partage des conditions initiales Ă  l’identique

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 13 à 17.

Techniques d’indexation

Introduction

  • Qu’est-ce qu’un index ?
  • Comment indexer une base ?
  • Les index B-tree dans PostgreSQL

Objectifs

  • Comprendre ce qu’est un index
  • MaĂ®triser le processus de crĂ©ation d’index
  • ConnaĂ®tre les diffĂ©rents types d’index B-tree et leurs cas d’usages

Introduction aux index

  • Uniquement destinĂ©s Ă  l’optimisation
  • Ă€ gĂ©rer d’abord par le dĂ©veloppeur
    • Markus Winand : SQL Performance Explained

Utilités d’un index

  • Un index permet de :
    • trouver un enregistrement dans une table directement
    • rĂ©cupĂ©rer une sĂ©rie d’enregistrements dans une table
    • voire tout rĂ©cupĂ©rer dans l’index (Index Only Scan)
  • Un index facilite :
    • certains tris
    • certains agrĂ©gats
  • Obligatoires et automatique pour clĂ©s primaires & unicitĂ©
    • conseillĂ© pour clĂ©s Ă©trangères (FK)

Index et lectures

Un index améliore les SELECT

  • Sans index :
=# SELECT * FROM test WHERE id = 10000;
Temps : 1760,017 ms
  • Avec index :
=# CREATE INDEX idx_test_id ON test (id);

=# SELECT * FROM test WHERE id = 10000;
Temps : 27,711 ms

Index : inconvénients

  • L’index n’est pas gratuit !
  • Ralentit les Ă©critures
    • maintenance
  • Place disque
  • Compromis Ă  trouver

Index : contraintes pratiques à la création

  • Lourd…
-- bloque les écritures !
CREATE INDEX ON matable ( macolonne ) ;
-- ne bloque pas, peut échouer
CREATE INDEX CONCURRENTLY ON matable ( macolonne ) ;
  • Si fragmentation :
REINDEX INDEX nomindex ;
REINDEX TABLE CONCURRENTLY nomtable ;
  • Paramètres :
    • maintenance_work_mem (sinon : fichier temporaire !)
    • max_parallel_maintenance_workers

Types d’index dans PostgreSQL

  • DĂ©faut : B-tree classique (Ă©quilibrĂ©)
  • UNIQUE (prĂ©fĂ©rer la contrainte)
  • Mais aussi multicolonne, fonctionnel, partiel, couvrant
  • Index spĂ©cialisĂ©s : hash, GiST, GIN, BRIN, HNSW….

Fonctionnement d’un index