Performance : lire EXPLAIN et choisir ses index

EXPLAIN ANALYZE : mesurer, ne pas deviner

Face à une requête lente, l'intuition ment (cours ML : « mesure, ne devine pas »). Le seul juge : le plan d'exécution réel.

EXPLAIN (ANALYZE, BUFFERS) SELECT … ;

Les nœuds à reconnaître, du pire au meilleur selon le contexte :

• Seq Scan : lecture de toute la table. Catastrophe sur 10 M de lignes filtrées, mais optimal si on lit 90 % de la table (l'index coûterait plus cher) ;
• Index Scan / Index Only Scan : l'index guide (l'Only ne touche même pas la table — index couvrant) ;
• Bitmap Heap Scan : entre les deux, pour une sélectivité moyenne ;
• Nested Loop / Hash Join / Merge Join : les trois stratégies de jointure — le planner choisit selon les volumes.

Les deux chiffres qui comptent : l'écart estimé vs réel (rows=100 estimé, actual rows=90000 → statistiques périmées, ANALYZE la table) et le temps par nœud (où part le budget). Un Seq Scan en haut d'une requête à 3 s sur une grosse table = ton coupable habituel.

Les familles d'index (chacune son usage)

Règle du composite : la colonne la plus sélective et filtrée par égalité en premier, celle qui ordonne ensuite ((tenant_id, created_at) sert WHERE tenant_id=… ORDER BY created_at).

Le coût caché des index

Un index accélère les lectures mais ralentit chaque écriture (il faut le maintenir) et occupe de l'espace. D'où la discipline : indexer les colonnes réellement filtrées/triées en prod (l'EXPLAIN le dit), pas « au cas où ». Et surveiller les index inutilisés (pg_stat_user_indexes) — un index jamais scanné est un coût d'écriture pur.