Lire EXPLAIN dans PostgreSQL avant d’ajouter un index

Commencer par les estimations

Préférez EXPLAIN simple si l’exécution peut coûter cher. Les coûts du plan ne sont pas des millisecondes. Un parcours séquentiel peut être pertinent si une grande partie d’une petite table doit être lue.

EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

Mesurer dans un environnement adapté

Sur un SELECT sans risque, examinez lignes, loops et buffers. Un fort écart d’estimation invite à vérifier les statistiques et la distribution. ANALYZE exécute aussi les écritures : ne l’utilisez pas sans précaution pour celles-ci.

EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM orders WHERE customer_id = 42;

Lire une ligne avant tout l’arbre

Le fragment ci-dessous est illustratif, pas une mesure sur une base réelle. cost=0.43..8.61 indique les coûts estimés de démarrage et d’ensemble en unités du planificateur. rows=10 est le nombre de sorties attendu et width=16 la taille moyenne estimée d’une ligne en octets.

La partie mesurée indiquerait 10 000 lignes au lieu de 10, pour une exécution. actual time=0.05..12.00 correspond au temps jusqu’à la première ligne et à la fin, en millisecondes. Un tel écart justifie d’examiner l’estimation avant de conclure qu’un nouvel index suffit.

Index Scan using orders_customer_idx on orders
  (cost=0.43..8.61 rows=10 width=16)
  (actual time=0.05..12.00 rows=10000 loops=1)

Partir des feuilles et repérer le travail répété

L’indentation indique quels nœuds alimentent leurs parents. Une lecture peut fournir les lignes à une jointure, un tri ou un agrégat. Cherchez où le travail augmente : beaucoup de lignes rejetées, un gros tri ou une opération interne répétée.

Pour un nœud ordinaire, actual rows et les temps sont des moyennes par exécution. Avec actual rows=5 et loops=1000, environ 5 000 lignes sont produites au total. Le temps peut inclure celui des enfants : additionner tous les nœuds compte plusieurs fois une partie du travail. Les plans parallèles demandent plus de prudence ; Execution Time donne une référence globale.

Affiner avec les buffers et les statistiques

BUFFERS complète la durée par les accès aux données. shared hit désigne un bloc trouvé dans le cache partagé PostgreSQL ; shared read un bloc lu dans ce cache, éventuellement depuis le cache du système. Ces compteurs ne représentent pas forcément des blocs distincts ou des lectures physiques du disque.

Des statistiques anciennes ou une distribution déséquilibrée peuvent expliquer les mauvaises estimations. ANALYZE orders collecte les statistiques de la table, contrairement à EXPLAIN ANALYZE qui exécute la requête. Après une modification massive, examinez les statistiques. Un tri débordant sur disque ne demande pas la même correction qu’une jointure mal estimée.

ANALYZE orders;

Modifier un facteur et comparer équitablement

Formulez une hypothèse : trop de lignes inutiles lues, recherches répétées ou gros résultat intermédiaire trié. Un filtre sélectif peut profiter d’un index adapté. Une lecture séquentielle reste raisonnable pour une petite table ou une requête retournant une grande partie des données.

Comparez paramètres et données équivalents, avec plusieurs mesures : cache et charge concurrente influencent la durée. Une seule exécution rapide en cache ne prouve pas un gain général. Comptez aussi stockage et coût d’écriture de l’index. Commencez par un SELECT approprié : EXPLAIN ANALYZE exécute également les écritures et peut avoir des effets de bord.