PostgreSQL : diagnostiquer et corriger les requêtes lentes

Les problèmes de performance PostgreSQL ont presque toujours une cause identifiable — et souvent une correction simple. Voici la méthode pour aller du symptôme à la solution.

Identifier les requêtes lentes

-- Activer pg_stat_statements (dans postgresql.conf)
-- shared_preload_libraries = 'pg_stat_statements'

SELECT
    query,
    calls,
    total_exec_time / calls AS avg_time_ms,
    rows / calls AS avg_rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

pg_stat_statements est le point de départ : il identifie les requêtes qui consomment le plus de temps cumulé.

EXPLAIN ANALYZE : lire le plan d’exécution

EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT u.*, o.* 
FROM users u
JOIN orders o ON o.user_id = u.id
WHERE u.country = 'FR'
  AND o.created_at > '2026-01-01';

Points d’attention dans le plan :

Ce que vous voyez	Ce que ça signifie
`Seq Scan` sur grande table	Absence d’index utile
`Hash Join` vs `Index Join`	Normal pour de grands jeux de données
`cost=0..99999` élevé	Estimation du planificateur — vérifiez les stats
`actual rows` très différent de `rows`	Statistiques obsolètes, lancer `ANALYZE`

Index : les types et quand les utiliser

-- Index standard sur colonne discriminante
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- Index partiel : seulement les commandes récentes
CREATE INDEX idx_orders_recent ON orders(created_at)
WHERE created_at > '2025-01-01';

-- Index composite : respectez l'ordre des colonnes
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
-- Utile pour : WHERE user_id = X et WHERE user_id = X AND created_at > Y
-- Inutile pour : WHERE created_at > Y seul

Les jointures et le N+1 problem

-- N+1 : une requête par user pour récupérer ses commandes
SELECT * FROM users WHERE country = 'FR';
-- Pour chaque user : SELECT * FROM orders WHERE user_id = ?

-- Correct : une seule requête
SELECT u.id, u.name, count(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
WHERE u.country = 'FR'
GROUP BY u.id, u.name;

Vacuum et statistiques : maintenance souvent négligée

PostgreSQL utilise MVCC (Multi-Version Concurrency Control) : les lignes supprimées ou mises à jour ne sont pas immédiatement libérées. Sans VACUUM régulier, la table gonfle et les perfs se dégradent.

-- Voir l'état du vacuum
SELECT schemaname, tablename, last_vacuum, last_autovacuum, n_dead_tup
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

-- Forcer un vacuum + analyze
VACUUM ANALYZE orders;

L’autovacuum gère ça automatiquement dans la plupart des cas — mais vérifiez qu’il est bien configuré pour les tables à forte activité.

Notre formation SQL & PostgreSQL aborde le tuning des performances avec des cas pratiques réels.