// travaux pratiques · Fondamentaux en informatique
TP05 - Index et plans d’exécution avec EXPLAIN
- TP 5 / 7
- 7 étapes
- ≈ 50 min
- Accès libre
Objectif : générer un volume réaliste de 300 000 interventions, lire un plan d’exécution avec EXPLAIN ANALYZE, mesurer le gain apporté par un index simple puis par un index composite, et vérifier l’utilisation et la taille des index.
Contexte
« L’application est lente » est l’un des tickets les plus fréquents qu’une équipe IT reçoit au sujet d’une base de données. Dans une grande majorité des cas, la cause n’est ni le serveur ni le réseau, mais une requête qui parcourt toute une table faute d’index adapté. Une base de quelques milliers de lignes masque le problème ; il apparaît quand l’historique grossit, souvent des mois après la mise en service. Dans une petite structure, l’administrateur doit savoir poser un diagnostic simple ; dans un grand groupe, il doit pouvoir le documenter précisément pour l’éditeur ou l’équipe de développement.
À la fin de ce TP, vous saurez lire les informations clés d’un plan (type de parcours, lignes estimées et réelles, temps), créer un index adapté à une requête et vérifier qu’il est réellement utilisé.
Prérequis : TP04 terminé. Connexion : psql -h localhost -U parc_admin -d parc. Prévoyez environ 100 Mo d’espace disque libre.
Durée estimée : 50 minutes.
Actions à réaliser
1. Générer un historique volumineux
La fonction generate_series produit une suite de nombres que l’on transforme en lignes : une intervention toutes les 5 minutes depuis le 1er janvier 2024, réparties sur les dix équipements et les quatre techniciens. ANALYZE met ensuite à jour les statistiques que le planificateur utilise pour choisir ses plans ; l’autovacuum le ferait seul, mais pas immédiatement.
\timing on
INSERT INTO interventions (equipement_id, date_intervention, technicien, description, duree_min)
SELECT 1 + (g % 10),
timestamptz '2024-01-01 00:00:00+01' + g * interval '5 minutes',
(ARRAY['amina','karim','lucas','sophie'])[1 + g % 4],
'Intervention générée ' || g,
5 + g % 120
FROM generate_series(1, 300000) AS g;
ANALYZE interventions;
Résultat attendu : INSERT 0 300000 en quelques secondes ; \timing on affiche désormais la durée de chaque commande. SELECT count(*) FROM interventions; renvoie 300 012.
2. Lire le plan d’une requête sans index
EXPLAIN montre le plan choisi par le planificateur ; avec ANALYZE, la requête est réellement exécutée et le plan est complété par les temps et les nombres de lignes réels. On cherche ici les interventions d’une journée.
EXPLAIN ANALYZE
SELECT id, equipement_id, technicien
FROM interventions
WHERE date_intervention >= '2025-06-01 00:00:00+02'
AND date_intervention < '2025-06-02 00:00:00+02';
Résultat attendu : un nœud Seq Scan on interventions (éventuellement sous un nœud Gather avec Parallel Seq Scan si le planificateur juge utile de paralléliser), une ligne Filter reprenant la condition, une ligne Rows Removed by Filter de l’ordre de 300 000 et environ 288 lignes réelles (actual … rows=288, une journée à raison d’une ligne toutes les 5 minutes). Notez la valeur de Execution Time en bas du plan.
3. Créer un index sur la date
Un index B-tree, type par défaut, permet de retrouver directement une plage de valeurs triées au lieu de lire toute la table. PostgreSQL nomme l’index automatiquement (interventions_date_intervention_idx) si on ne lui donne pas de nom.
CREATE INDEX ON interventions (date_intervention);
EXPLAIN ANALYZE
SELECT id, equipement_id, technicien
FROM interventions
WHERE date_intervention >= '2025-06-01 00:00:00+02'
AND date_intervention < '2025-06-02 00:00:00+02';
Résultat attendu : le plan utilise désormais interventions_date_intervention_idx, sous la forme d’un Index Scan ou d’un couple Bitmap Index Scan / Bitmap Heap Scan ; il n’y a plus de Rows Removed by Filter massif et le temps d’exécution chute nettement (souvent d’un ou deux ordres de grandeur).
4. Adapter l’index à une requête à deux critères
Les clés étrangères ne créent pas d’index sur la colonne qui référence : equipement_id n’en a pas. Pour la requête « historique d’un équipement sur une semaine », un index composite dont la première colonne est celle testée par égalité et la seconde celle testée par plage est le plus efficace.
CREATE INDEX ON interventions (equipement_id, date_intervention);
EXPLAIN ANALYZE
SELECT date_intervention, technicien, duree_min
FROM interventions
WHERE equipement_id = 3
AND date_intervention >= '2026-03-02 00:00:00+01'
AND date_intervention < '2026-03-09 00:00:00+01'
ORDER BY date_intervention;
Résultat attendu : le plan utilise interventions_equipement_id_date_intervention_idx, avec une condition d’index (Index Cond) portant sur les deux colonnes, et renvoie environ 200 lignes (une semaine représente 2 016 créneaux de 5 minutes, dont un sur dix concerne l’équipement 3). L’index étant déjà trié par date pour un équipement donné, le plan ne contient pas de nœud Sort séparé quand il s’agit d’un Index Scan.
5. Observer les accès mémoire et disque
Avec l’option BUFFERS, le plan indique combien de blocs de 8 Ko ont été trouvés dans le cache de PostgreSQL (shared hit) ou lus depuis le système (read). En version 17, il faut la demander explicitement ; depuis la version 18, elle est incluse par défaut avec ANALYZE.
EXPLAIN (ANALYZE, BUFFERS)
SELECT count(*) FROM interventions WHERE technicien = 'lucas';
Résultat attendu : un parcours séquentiel (aucun index ne porte sur technicien, et un quart de la table correspond de toute façon), le résultat 75 003, et une ligne Buffers: shared hit=… read=… dont le total correspond au nombre de blocs de la table. Relancez la requête : la part read diminue au profit de hit, car les blocs sont désormais en cache.
6. Vérifier l’usage et la taille des index
Un index accélère les lectures mais ralentit chaque écriture et occupe de l’espace. La vue statistique pg_stat_user_indexes compte le nombre de parcours de chaque index : un index jamais utilisé est un candidat à la suppression.
SELECT indexrelname, idx_scan, pg_size_pretty(pg_relation_size(indexrelid)) AS taille
FROM pg_stat_user_indexes
WHERE relname = 'interventions';
SELECT pg_size_pretty(pg_relation_size('interventions')) AS table_seule,
pg_size_pretty(pg_total_relation_size('interventions')) AS avec_index;
Résultat attendu : trois index (interventions_pkey et les deux créés dans ce TP), chacun de quelques mégaoctets, avec un compteur idx_scan non nul pour les index utilisés aux étapes 3 et 4. La taille totale dépasse sensiblement celle de la table seule.
7. Créer un index sans bloquer les écritures
Un CREATE INDEX classique bloque les écritures sur la table pendant toute sa construction. En production, on utilise CONCURRENTLY : plus lent, il laisse l’application écrire. Il ne peut pas s’exécuter dans un bloc de transaction et, s’il échoue, il laisse un index invalide qu’il faut supprimer.
CREATE INDEX CONCURRENTLY interventions_technicien_idx ON interventions (technicien);
\d interventions
DROP INDEX CONCURRENTLY interventions_technicien_idx;
Résultat attendu : \d interventions liste quatre index, sans la mention INVALID, puis l’index sur technicien est supprimé, car aucune requête utile ne le justifie.
Vérification
Le TP est réussi si vous avez relevé, pour la requête de l’étape 2, un temps d’exécution avec parcours séquentiel puis un temps nettement inférieur avec l’index ; si le plan de l’étape 4 montre une Index Cond sur equipement_id et date_intervention ; et si pg_stat_user_indexes confirme l’utilisation des deux index conservés.
Problèmes fréquents
- L’index n’est pas utilisé : statistiques périmées (relancez
ANALYZE interventions;), condition qui renvoie une grosse partie de la table (un parcours séquentiel est alors réellement plus rapide), ou fonction appliquée à la colonne (WHERE date(date_intervention) = …empêche l’utilisation d’un index sur la colonne brute). - « CREATE INDEX CONCURRENTLY cannot run inside a transaction block » : un
BEGINest resté ouvert (inviteparc=*>). Terminez la transaction, puis relancez. - « could not extend file … No space left on device » : le disque est plein. Libérez de l’espace et surveillez le volume de
/var/lib/postgresql; un serveur de bases de données ne doit jamais atteindre 100 %. - Les temps varient d’une exécution à l’autre : effet du cache. Comparez des exécutions répétées, pas une première exécution « à froid » avec une exécution « à chaud ».
Bonnes pratiques et sécurité
- Créez les index à partir de requêtes réelles et mesurées, pas « au cas où » : chaque index a un coût en écriture et en espace.
- Pensez à indexer les colonnes de clés étrangères fréquemment filtrées ou utilisées pour des suppressions en cascade.
- Pour repérer les requêtes lentes, activez
log_min_duration_statement(par exemple 500 ms) ou l’extensionpg_stat_statements, qui doit être chargée parshared_preload_librarieset demande donc un redémarrage. - Attention :
EXPLAIN ANALYZEexécute vraiment la requête. Sur unUPDATEou unDELETE, encadrez-le d’unBEGINet d’unROLLBACK.
Questions de contrôle
1. Quelle est la différence entre EXPLAIN et EXPLAIN ANALYZE ?
Voir la réponse
EXPLAIN affiche le plan estimé sans exécuter la requête ; EXPLAIN ANALYZE l’exécute et ajoute les temps et nombres de lignes réels, ce qui permet de comparer estimations et réalité.
2. Pourquoi placer equipement_id en première colonne de l’index composite ?
Voir la réponse
Parce que la requête le teste par égalité : l’index regroupe alors toutes les lignes d’un équipement, triées par date, et la plage de dates se lit d’un seul tenant.
3. Pourquoi utiliser CREATE INDEX CONCURRENTLY en production ?
Voir la réponse
Pour ne pas bloquer les écritures de l’application pendant la construction de l’index, au prix d’une création plus longue et de l’impossibilité de l’exécuter dans une transaction.
Pour aller plus loin
Pour suivre les performances dans la durée, raccordez l’instance à votre outil de supervision : le cours Supervision d’infrastructure avec Zabbix montre la démarche. Sur un grand parc d’instances, pg_stat_statements et la journalisation des requêtes lentes, centralisés, permettent de prioriser les optimisations. Conservez la base et ses index pour le TP06.
// formation sur mesure
Former votre équipe sur ce sujet ?
Azur Secure anime des sessions intra-entreprise, à distance ou dans vos locaux sur la Côte d'Azur, adaptées au niveau de vos équipes.