Maîtriser PostgreSQL EXPLAIN ANALYZE : Des Scans Séquentiels aux Index Optimaux

1. Décoder EXPLAIN (ANALYZE, BUFFERS)

EXPLAIN seul ne montre que ce que le planificateur compte faire, sur la base des statistiques collectées par les passages ANALYZE d’autovacuum. C’est une estimation de coût, pas une mesure : les unités de coût sont arbitraires, elles représentent des accès disque et des cycles CPU relatifs, pas des millisecondes. Dès que vous ajoutez ANALYZE, PostgreSQL exécute réellement la requête (avec les effets de bord des écritures éventuelles, d’où l’intérêt d’encapsuler le profilage de DML dans une transaction annulée ensuite), et chaque nœud du plan affiche alors actual time, actual rows et loops.

BUFFERS est l’option que tout le monde néglige, à tort. Elle indique shared_hit (pages trouvées dans shared_buffers), shared_read (pages lues depuis l’OS ou le disque), et pour les nœuds d’écriture, shared_dirtied et shared_written. Une requête qui semble rapide en temps réel mais affiche un shared_read élevé sur un cache froid est trompeuse : la première exécution charge les pages en cache, et les suivantes paraissent artificiellement rapides. Exécutez toujours EXPLAIN (ANALYZE, BUFFERS) au moins deux fois pour comparer, sinon vous optimisez pour un scénario cache-chaud qui n’arrive jamais en production sous pression d’éviction réelle.

Le chiffre le plus utile de toute la sortie est l’écart entre les lignes estimées par le planificateur et les lignes réellement retournées par un nœud. Quand cet écart dépasse un ordre de grandeur, le planificateur travaille sur des statistiques obsolètes ou trop grossières, et toutes les décisions basées sur le coût en aval de ce nœud — ordre des jointures, algorithme de jointure, utilisation ou non d’un index — reposent sur une hypothèse fausse. Corriger les statistiques (ANALYZE, augmenter default_statistics_target, ou des statistiques étendues pour des colonnes corrélées) a souvent plus d’impact que d’ajouter un index.

Lisez le plan depuis le nœud le plus imbriqué vers l’extérieur — c’est l’ordre d’exécution, pas l’ordre d’affichage de haut en bas. Le actual time de chaque nœud est cumulatif, incluant ses enfants : un Nested Loop affichant 800ms n’est pas forcément un opérateur de jointure coûteux, il peut simplement additionner le temps d’un scan enfant onéreux. Soustrayez manuellement les temps des enfants avant de conclure quel nœud est réellement le goulot d’étranglement.

2. Identifier les goulots d’étranglement (Seq Scan)

Un Seq Scan n’est pas un bug en soi. Sur une table de quelques milliers de lignes, ou pour une requête qui a réellement besoin de la majorité des lignes, un scan séquentiel bat un scan d’index car il lit les pages dans l’ordre physique avec read-ahead, alors qu’un scan d’index fait des accès aléatoires par ligne trouvée si la table n’est pas bien regroupée. Le problème survient quand le planificateur choisit un Seq Scan sur une grande table pour un prédicat sélectif : c’est le signal qu’il manque un index, qu’une fonction enrobe la colonne filtrée et annule un index existant, ou que les statistiques sous-estiment la sélectivité.