Optimisation des requêtes PostgreSQL : guide pratique

1. Commencer par identifier les requêtes lentes

Activez pg_stat_statements — c’est l’extension la plus utile pour cet exercice, et elle est fournie avec PostgreSQL :

-- postgresql.conf
shared_preload_libraries = 'pg_stat_statements'

-- after restart
CREATE EXTENSION pg_stat_statements;

Elle regroupe chaque requête par forme normalisée et suit le temps d’exécution total et moyen, le nombre d’appels et le nombre de lignes renvoyées :

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 20;

Triez par total_exec_time, pas par mean_exec_time — une requête de 5 ms en moyenne mais appelée 50 000 fois par heure pose généralement plus de problèmes qu’une requête de 800 ms appelée deux fois par jour. Pour une analyse ponctuelle, log_min_duration_statement = 500 (en millisecondes) dans postgresql.conf journalise toute requête qui dépasse ce seuil, et le module auto_explain peut joindre automatiquement un plan d’exécution à ces entrées de journal.

2. Lire le plan d’exécution

Un simple EXPLAIN affiche l’estimation du planificateur ; EXPLAIN ANALYZE exécute réellement la requête et affiche les temps et nombres de lignes réels, ce qu’il vous faut pour diagnostiquer un problème en production :

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending';

Lisez-le de bas en haut, car les nœuds les plus internes s’exécutent en premier. Quelques points à vérifier :

• Seq Scan, Index Scan ou Index Only Scan. Un parcours séquentiel sur une grande table, là où vous attendiez un parcours d’index, signifie généralement que le planificateur n’a pas jugé l’index assez sélectif, ou qu’aucun index adapté n’existe. Un Index Only Scan (sans lecture de la table) est le plus rapide des trois.
• Lignes estimées et lignes réelles. EXPLAIN ANALYZE affiche les deux. Un écart important signifie que les statistiques du planificateur sont obsolètes (voir section 5) et qu’il risque, de ce fait, de choisir un mauvais plan.
• Sort Method: external merge Disk. Un tri a débordé sur le disque parce que work_mem était trop petit pour cette opération. Augmentez work_mem pour la session ou la requête, ou réduisez le volume de données à trier.
• Buffers : shared hit ou read. Avec l’option BUFFERS, un ratio élevé de read par rapport à hit signifie que la requête lit sur le disque au lieu de trouver les données déjà en cache dans shared_buffers.

3. Choisir le bon type d’index

PostgreSQL propose plusieurs types d’index, et choisir le mauvais est une raison fréquente pour laquelle un index « correct » ne sert à rien :

• B-tree (par défaut) — requêtes d’égalité et d’intervalle, tris. C’est ce qu’il vous faut dans la quasi-totalité des cas.
• GIN — valeurs composites : jsonb, tableaux, recherche plein texte (tsvector). Une requête qui filtre sur une clé JSONB sans index GIN parcourt toute la table, aussi bien écrite soit-elle.
• BRIN — très grandes tables dont la colonne suit l’ordre physique des lignes (par exemple un created_at en insertion seule). Minuscule par rapport à un B-tree, mais bien moins précis.
• Index partiels — n’indexez que les lignes réellement interrogées, par exemple CREATE INDEX ON orders (customer_id) WHERE status = 'pending', lorsque la plupart des lignes ne sont pas pending. Plus petits et plus rapides qu’un index sur toute la table.
• Index sur expression — si vous filtrez sur lower(email), indexez directement lower(email), et non email.

Un piège propre à PostgreSQL : il n’indexe pas automatiquement les colonnes de clé étrangère. Chaque colonne de clé étrangère utilisée dans une jointure ou un filtre doit avoir son propre index explicite.

4. Les écritures de requêtes qui neutralisent les index

• Index manquants sur les clés étrangères. Le constat le plus fréquent lors d’un premier audit — jointures et suppressions en cascade sur des clés étrangères non indexées.
• Fonctions appliquées à des colonnes indexées sans index sur expression correspondant. WHERE lower(email) = 'x' n’utilisera pas un index simple sur email.
• Conversions de type implicites. Comparer une colonne text à un littéral integer, ou à un paramètre non typé transmis par un ORM, peut empêcher silencieusement l’utilisation de l’index.
• Pagination avec un OFFSET élevé. Même problème que sur toute base relationnelle : PostgreSQL doit tout de même calculer puis écarter les lignes sautées. Utilisez la pagination par clé (WHERE id > :last_id ORDER BY id LIMIT 20) pour les pages lointaines.
• Requêtes N+1 générées par un ORM. Ce n’est pas un problème propre à PostgreSQL, mais pg_stat_statements le rend évident : une même forme de requête avec un nombre de calls démesuré par rapport au volume de requêtes HTTP.

5. Statistiques, VACUUM et fragmentation

Avec le modèle MVCC de PostgreSQL, un UPDATE ou un DELETE ne supprime pas immédiatement l’ancienne version de la ligne — il la marque comme morte et laisse le nettoyage à VACUUM. Si l’autovacuum prend du retard sur une table très sollicitée en écriture, les tuples morts s’accumulent, les tables et les index gonflent, et les statistiques du planificateur deviennent obsolètes, ce qui dégrade les plans d’exécution au fil du temps. Vérifiez le nombre de tuples morts :

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 10;

Si une table subit beaucoup de modifications, ajustez directement ses seuils d’autovacuum (autovacuum_vacuum_scale_factor) plutôt que de vous reposer sur la valeur par défaut de la base. Et lancez ANALYZE après les chargements massifs ou les migrations, afin que les estimations du planificateur reflètent immédiatement la réalité, sans attendre le prochain passage de l’autovacuum.

6. Surveiller et itérer

Intégrez pg_stat_statements à votre revue d’exploitation régulière, pas seulement à la gestion des incidents. Les volumes de données et les modes d’accès évoluent, et un plan satisfaisant le trimestre dernier peut basculer vers un parcours séquentiel dès qu’une table franchit un seuil de taille auquel le planificateur est sensible. Revérifiez les plans après les migrations de schéma et les montées de version majeure — le comportement du planificateur change d’une version majeure à l’autre.