ZrobDev/optimisation-sql

Optimisation SQL (PostgreSQL en priorité)

Mesurez avant toute modification. Un changement d'optimisation sans plan et mesures avant/après n'est qu'une supposition.

Procédure

1. Trouvez la cible : classez par coût total, pas par l'appel le plus lent individuel (voir la boucle de diagnostic de pg_stat_statements).
2. Capturez la référence : EXPLAIN (ANALYZE, BUFFERS) <requête> sur des données de taille production. De petites données de développement sélectionnent des plans différents.
3. Localisez le nœud où le temps ou les lignes divergent (règles de lecture ci-dessous). Corrigez uniquement ce nœud.
4. Ré-exécutez la même capture de plan. Ne conservez la modification que si elle améliore à la fois le temps et les buffers.
5. Enregistrez l'index ou la réécriture et la raison, à côté de la migration de schéma.

Encadrez EXPLAIN ANALYZE des INSERT/UPDATE/DELETE par BEGIN; ... ROLLBACK; car ANALYZE exécute réellement l'instruction.

Lecture d'un plan

• Lisez de bas en haut, nœuds internes en premier. Le actual time par nœud est par boucle ; multipliez par loops pour le coût réel.
• Comparez rows (estimation) à actual rows. Un écart de 10x ou plus est la cause racine bien plus souvent qu'un index manquant : exécutez ANALYZE <table>, augmentez la cible de statistiques de colonne, ou ajoutez des statistiques étendues (CREATE STATISTICS ... (dependencies, ndistinct)) pour les colonnes corrélées.
• Rows Removed by Filter beaucoup plus grand que le nombre de lignes retournées signifie que l'index (ou son absence) n'est pas sélectif pour ce prédicat.
• Buffers: shared read correspond au disque ou au cache OS ; shared hit correspond à shared_buffers. Un nombre élevé de lectures sur des exécutions répétées signifie que le jeu de travail ne tient pas en mémoire.
• Sort Method: external merge Disk: signifie que work_mem était trop petit pour ce tri. Augmentez-le par session ou par rôle, pas globalement, car il se multiplie par nœud de tri et par connexion.
• Nested Loop avec un grand nombre de loops interne et un côté interne sans index est la jointure lente classique. Hash Join avec débordement (Batches: >1) est la même histoire de work_mem.
• Un Seq Scan est correct lorsque la requête lit une grande fraction de la table. N'ajoutez pas d'index pour supprimer un Seq Scan qui retourne 20 % des lignes.
• Heap Fetches sur un Index Only Scan signifie que la visibility map est obsolète ; exécutez VACUUM sur la table.

Choix d'un index

Indexez uniquement le prédicat, la jointure ou le ORDER BY que vous avez mesuré. Chaque index coûte du temps d'écriture, du WAL et du travail de vacuum.

• btree : défaut pour l'égalité, les intervalles, le tri. Ordre des colonnes : colonnes d'égalité en premier, puis la colonne d'intervalle ou de tri. Le skip scan dans PG 18 aide dans certains cas de colonne de tête manquante, mais ne concevez pas autour de cela.
• Partiel (WHERE status = 'open') : lorsque les requêtes ciblent une tranche petite et stable (éléments ouverts, lignes non encore traitées). La clause WHERE de la requête doit impliquer le prédicat de l'index.
• Couvrant (INCLUDE (col)) : lorsqu'un index-only scan supprimerait les heap fetches pour une requête fréquente. Les colonnes incluses ne sont pas des clés de recherche.
• Expression (ON (lower(email)), ON ((payload->>'id'))) : lorsque la requête applique une fonction à la colonne. La requête doit utiliser l'expression identique.
• BRIN : pour les énormes tables append-only où la colonne est corrélée à l'ordre physique (timestamps, identifiants séquentiels). Index minuscule, élagage grossier. Vérifiez pg_stats.correlation proche de 1 ; s'il est faible, BRIN n'élaguera pas.
• GIN : jsonb containment, tableaux, texte intégral. GiST : intervalles, géométrie, contraintes d'exclusion.
• Créez sur les tables en production avec CREATE INDEX CONCURRENTLY ; il ne peut pas s'exécuter dans un bloc de transaction et un échec laisse un index INVALID à supprimer.
• Supprimez les index inutilisés seulement après avoir vérifié pg_stat_user_indexes.idx_scan sur un cycle métier complet (fin de mois pour les charges financières) et également sur les répliques.

Sargabilité

Un prédicat ne peut utiliser un index que si la colonne indexée est seule d'un côté.

• WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01' au lieu de WHERE extract(year from created_at) = 2026 ou date_trunc(...) = ....
• WHERE col = $1 avec des types correspondants ; les casts du style numeric_col = '5'::text du côté de la colonne bloquent l'index. Convertissez le paramètre, pas la colonne.
• Évitez le wildcard en tête LIKE '%x' ; utilisez pg_trgm GIN ou la recherche plein texte.
• OR entre différentes colonnes perd souvent l'index ; essayez UNION ALL de deux branches indexées.
• NOT IN (sous-requête) est hostile aux NULL et lent ; utilisez NOT EXISTS.
• Préférez EXISTS pour les semi-jointures plutôt que DISTINCT après une jointure, car il s'arrête à la première correspondance et ne duplique pas les lignes.

Fenêtres SQL vs auto-jointures

Utilisez une fonction de fenêtre lorsque l'objectif est une valeur par ligne provenant de lignes apparentées du même ensemble : totaux cumulés, rang, lag/lead, "dernière ligne par clé" (row_number() OVER (PARTITION BY k ORDER BY ts DESC) = 1), car un scan et un tri remplacent une jointure de la table sur elle-même. Utilisez une auto-jointure ou LATERAL uniquement lorsque le second côté a besoin de son propre filtre ou d'une sonde d'index (top-N par clé depuis une grande table : JOIN LATERAL (SELECT ... ORDER BY ts DESC LIMIT 3) avec un index sur (k, ts DESC)). DISTINCT ON (k) ... ORDER BY k, ts DESC est la forme courte PostgreSQL pour "dernière par clé".

CTEs

Depuis PG 12, une CTE non récursive, sans effet de bord, référencée une fois est inlinée comme une sous-requête. Écrivez pour la lisibilité par défaut.

• Ajoutez AS MATERIALIZED lorsque la CTE est coûteuse et référencée plusieurs fois, ou lorsque vous devez empêcher le planificateur d'y pousser un mauvais prédicat.
• Ajoutez AS NOT MATERIALIZED pour forcer l'inlinage d'une CTE référencée plusieurs fois lorsque le filtre externe la rend peu coûteuse.
• Les CTEs modifiant des données s'exécutent toujours une fois ; elles ne sont pas optimisées.

Boucle de diagnostic pg_stat_statements

Nécessite pg_stat_statements dans shared_preload_libraries (redémarrage) et CREATE EXTENSION.

1. Triez par total_exec_time DESC (vérifiez aussi shared_blks_read, temp_blks_written, rows). Une requête de 5 ms appelée 40 millions de fois bat une requête nocturne de 30 s.
2. Prenez les 3 premières. Pour chacune, obtenez un ensemble de paramètres réels (les statistiques sont normalisées) et exécutez l'étape 2 de la procédure.
3. Corrigez, déployez, puis SELECT pg_stat_statements_reset(<userid>, <dbid>, <queryid>) pour cette requête uniquement, attendez une période représentative, comparez.
4. Répétez jusqu'à ce que l'entrée supérieure soit un coût justifié. Arrêtez-vous là. Associez auto_explain (log_min_duration, log_analyze avec parcimonie) pour capturer les plans de requêtes que vous ne pouvez pas reproduire.

PL/pgSQL seulement là où il a sa place

Utilisez une fonction ou une procédure lorsqu'elle supprime les allers-retours pour une logique ligne par ligne qui ne peut pas être ensembliste, applique une invariant de trigger, ou encapsule une transaction multi-instructions. Ne l'utilisez pas pour boucler sur des lignes en émettant une UPDATE chacune : réécrivez comme une seule UPDATE ... FROM ou INSERT ... SELECT. Marquez les fonctions pures IMMUTABLE/STABLE correctement (un mauvais label produit des résultats en cache erronés ou bloque l'inlinage). Préférez les fonctions SQL simples lorsque c'est possible afin que le planificateur puisse les inliner.

N+1 et pagination

• N+1 : une requête par ligne parente depuis le code applicatif ou ORM. Corrigez avec un seul WHERE id = ANY($1) par lot, une jointure, ou l'option d'eager loading de l'ORM. Détectez via pg_stat_statements : une instruction normalisée avec calls proche du nombre de lignes parentes.
• La pagination par OFFSET lit et jette toutes les lignes ignorées, donc la page N coûte O(N). Utilisez keyset : WHERE (created_at, id) < ($1, $2) ORDER BY created_at DESC, id DESC LIMIT 50 avec un index sur (created_at DESC, id DESC). La colonne de départage est obligatoire sinon des lignes sont sautées ou répétées. Keyset ne peut pas sauter à la page N ; utilisez OFFSET seulement pour de petits ensembles bornés.
• count(*) sur les grandes tables est un scan complet ; affichez des comptes approximatifs (reltuples) ou limitez avec SELECT count(*) FROM (SELECT 1 ... LIMIT 10001).

Notes ETL en masse

Chargez par lots avec COPY (ou INSERT ... SELECT) plutôt que des insertions ligne par ligne ; supprimez ou différez les index secondaires uniquement pour les grands chargements initiaux, puis exécutez ANALYZE après. Les grosses UPDATE gonflent les tables : traitez par lots avec une clé de plage, validez par lot, et laissez autovacuum suivre.

Autres dialectes (brève)

• BigQuery : le coût est basé sur les octets facturés, pas sur les lignes. Partitionnez sur une colonne date/timestamp et filtrez-la avec une constante ou un paramètre pour que l'élagage se produise ; clustérisez sur les colonnes les plus filtrées suivantes. SELECT * facture toutes les colonnes ; nommez les colonnes. Utilisez dry run pour voir les octets avant d'exécuter. LIMIT ne réduit pas les octets facturés.
• Snowflake : l'élagage utilise les métadonnées min/max des micro-partitions, donc filtrez sur les colonnes dont les valeurs sont naturellement ordonnées au chargement ; vérifiez "partitions scanned vs total" dans le profil de requête. Ajoutez une clé de clustering uniquement pour les très grandes tables avec un motif de filtre stable, car le reclustering coûte des crédits. Augmentez la taille de l'entrepôt pour le débordement (local/remote spill dans le profil), augmentez l'échelle (multi-cluster) pour les files d'attente/concurrence. Mettez en pause automatique rapidement.

Versions actuelles et liens de documentation : references/landscape-2026-11.md. Leçons issues d'utilisations réelles : references/gotchas.md.