SQL : comprendre et optimiser ses index

Un index manquant, c’est une requête qui parcourt des millions de lignes pour retourner dix résultats. Un index inutile, c’est de l’espace disque gaspillé et des insertions ralenties. Comprendre comment PostgreSQL utilise ses index change radicalement les performances.

Comment fonctionne un index B-tree

L’index par défaut de PostgreSQL est un B-tree (arbre équilibré). Il trie les valeurs et permet de localiser une valeur en O(log n) au lieu de O(n) pour un scan séquentiel.

-- Sans index : PostgreSQL parcourt toutes les lignes
EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- Seq Scan on orders (cost=0.00..45231.00 rows=1 width=156)
--   Filter: (user_id = 42)

-- Avec index : saut direct aux bonnes lignes
CREATE INDEX idx_orders_user_id ON orders(user_id);

EXPLAIN SELECT * FROM orders WHERE user_id = 42;
-- Index Scan using idx_orders_user_id on orders (cost=0.43..8.45 rows=1 width=156)
--   Index Cond: (user_id = 42)

EXPLAIN ANALYZE : lire le plan d’exécution

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT o.id, o.total, u.email
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status = 'pending'
  AND o.created_at > NOW() - INTERVAL '24 hours'
ORDER BY o.created_at DESC
LIMIT 50;

Points clés à lire dans le plan :

• Seq Scan → souvent signe d’index manquant ou non utilisé
• cost=X..Y → X = coût de démarrage, Y = coût total (estimé)
• rows=N → estimation du nombre de lignes. Si très différent de la réalité → ANALYZE requis
• Buffers: hit=N → lignes lues depuis le cache (bon). read=N → lues depuis le disque (coûteux)

Index composites : l’ordre des colonnes compte

-- Requête typique : filtrer sur status ET trier par created_at
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY created_at DESC;

-- Index composite — status en premier (filtrage), created_at en second (tri)
CREATE INDEX idx_orders_status_created
ON orders(status, created_at DESC);

-- ⚠️ Cet index NE sert PAS pour une requête filtrée uniquement sur created_at
-- L'index composite est utilisé de gauche à droite
SELECT * FROM orders WHERE created_at > '2026-01-01';
-- → cet index n'est PAS utilisé pour cette requête seule

Index partiels : indexer seulement ce qui est pertinent

-- Sur 10 millions de commandes, seulement 1000 sont 'pending'
-- Indexer toutes les commandes pour filtrer sur 'pending' est inefficace

-- Index partiel : indexe uniquement les lignes où status = 'pending'
CREATE INDEX idx_orders_pending
ON orders(created_at DESC)
WHERE status = 'pending';

-- PostgreSQL utilise cet index pour :
SELECT * FROM orders WHERE status = 'pending' ORDER BY created_at DESC;

-- Avantage : index beaucoup plus petit = plus rapide + moins de RAM

Index sur expressions

-- Recherche insensible à la casse — sans index : Seq Scan
SELECT * FROM users WHERE lower(email) = lower('Alice@Example.com');

-- Index fonctionnel sur l'expression
CREATE INDEX idx_users_email_lower ON users(lower(email));

-- Maintenant la requête utilise l'index
EXPLAIN SELECT * FROM users WHERE lower(email) = 'alice@example.com';
-- Index Scan using idx_users_email_lower

pg_stat_user_indexes : identifier les index inutiles

-- Index qui n'ont jamais été utilisés depuis le dernier restart
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexname NOT LIKE 'pk_%'   -- exclure les primary keys
ORDER BY pg_relation_size(indexrelid) DESC;

-- Index les plus utilisés (pour prioriser le cache)
SELECT indexrelname, idx_scan, idx_tup_read, idx_tup_fetch,
       pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC
LIMIT 20;

Vacuum et statistiques : maintenir les index en forme

-- Mettre à jour les statistiques (important après un chargement massif)
ANALYZE orders;

-- Reconstruire un index fragmenté (bloquant — utiliser REINDEX CONCURRENTLY en prod)
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

-- Voir le taux de fragmentation des index
SELECT relname, pg_size_pretty(pg_relation_size(oid)) as size,
       round(100 * (1 - avg_leaf_density/90.0)) as fragmentation_pct
FROM pg_class
JOIN pg_stat_user_tables ON relname = relname
WHERE relkind = 'i'
ORDER BY fragmentation_pct DESC;

Notre formation SQL couvre l’optimisation des requêtes et la gestion des index PostgreSQL avec des cas pratiques sur des bases réelles.