Un index, c’est une table des matières

Ce que vous saurez faire

Formuler une hypothèse d’indexation, lire un plan et comparer avant/après sans confondre estimation et mesure. Faites cet atelier dans une base jetable dédiée aux essais.

1. Partir d’une question précise

Notre écran affiche les dix derniers cours d’un auteur. Les conditions et l’ordre demandé orientent le choix de l’index, pas seulement le nom des colonnes.

CREATE TABLE index_lab (
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  author_id integer NOT NULL,
  published_at timestamptz NOT NULL
);
INSERT INTO index_lab (author_id, published_at)
SELECT n % 100, now() - n * interval '1 minute'
FROM generate_series(1, 10000) AS n;
ANALYZE index_lab;

EXPLAIN (ANALYZE, BUFFERS)
SELECT id, published_at FROM index_lab
WHERE author_id = 7
ORDER BY published_at DESC
LIMIT 10;

Le plan décrit une suite d’opérations. Repérez le parcours de table ou d’index, un éventuel tri et les nombres de lignes estimés puis réellement obtenus. Les coûts ne sont pas des millisecondes. ANALYZE dans EXPLAIN exécute réellement la requête : ne l’appliquez pas distraitement à une écriture en production.

Référence : lire EXPLAIN.

2. Tester un index cohérent avec cet accès

CREATE INDEX index_lab_author_date_idx
ON index_lab (author_id, published_at DESC);
ANALYZE index_lab;

Relancez exactement la même requête avec EXPLAIN (ANALYZE, BUFFERS). Dans cet index B-tree, l’auteur est la première clé ; la date ordonne les entrées à l’intérieur de chaque auteur. Cela correspond au filtre d’égalité suivi de l’ordre demandé.

Ce n’est pas une promesse de résultat universel. Pour rechercher les cours récents de tous les auteurs, ce même index ne correspond plus aussi directement au besoin. La taille de la table et la distribution des valeurs influencent également le choix du planificateur.

Référence : index multicolonnes.

3. Comparer équitablement

Conservez les mêmes données et requêtes. Répétez les essais : un deuxième passage peut profiter de pages déjà en mémoire. Mesurez aussi le coût des écritures et l’espace occupé avant de multiplier les index.

À vous de jouer

Sur une minuscule table, PostgreSQL garde un parcours séquentiel après création de l’index. Cela signifie-t-il que l’index est cassé ?

Correction commentée

Non. Lire quelques pages peut coûter moins cher qu’emprunter l’index puis accéder aux lignes. Vérifiez les statistiques et testez sur un volume représentatif. Le but est d’améliorer le temps et les ressources consommées par une requête réelle, pas de forcer l’apparition du mot « Index » dans le plan.