Même le meilleur matériel ne peut pas vous sauver d'un code SQL mal écrit.
PostgreSQL fournit des outils puissants pour analyser l'exécution des requêtes et optimiser les performances.
Dans cet article, nous allons explorer les principes fondamentaux de l'optimisation SQL afin que vous puissiez identifier les goulets d'étranglement et accélérer l'exécution de vos requêtes.
L'importance de l'optimisation de SQL
Un code SQL mal optimisé conduit à :
- Requêtes interminables qui frustrent les utilisateurs.
- Consommation inutile d'unité centrale et d'E/S.
- Verrous et contention ayant un impact sur d'autres sessions.
- Augmentation des coûts en cas d'évolution inutile du matériel.
L'optimisation de SQL est l'un des moyens les plus rentables d'améliorer les performances.
Étape 1 : Identifier les requêtes lentes
Vous pouvez suivre les requêtes lentes en utilisant :
- déclaration_min_durée_log - Enregistre les requêtes dépassant une durée déterminée.
- pg_stat_statements - Capture les statistiques d'exécution de toutes les requêtes.
- PEM SQL Profiler - Fournit un traçage et une analyse graphiques.
Exemple :
-- Log queries longer than 5 seconds
ALTER SYSTEM SET log_min_duration_statement = 5000;
SELECT pg_reload_conf();
Étape 2 : Examiner le plan d'exécution
Utiliser le EXPLIQUER pour voir comment PostgreSQL planifie l'exécution d'une requête :
EXPLAIN SELECT * FROM customers JOIN orders USING (customerid);
Ou courir EXPLIQUER ANALYSER pour exécuter la requête et comparer les estimations avec la durée d'exécution réelle :
EXPLAIN ANALYZE SELECT * FROM customers JOIN orders USING (customerid);
Principaux points à vérifier :
- Balayages séquentiels sur les grandes tables (peut indiquer des index manquants).
- Méthodes de jonction (jointure par hachage, boucle imbriquée, jointure par fusion).
- Coûts de tri et d'agrégation.
Étape 3 : Vérification des statistiques
PostgreSQL s'appuie sur les statistiques des tables et des colonnes pour la planification des requêtes.
Des statistiques obsolètes peuvent conduire à de mauvais plans d'exécution.
- Exécuter
ANALYSERpour actualiser les statistiques :
ANALYZE customers;
- Utilisation autovacuum pour que les statistiques soient automatiquement mises à jour.
- Pour les données asymétriques, ajuster les statistiques des colonnes :
ALTER TABLE orders ALTER COLUMN order_date SET STATISTICS 500;
Étape 4 : Restructurer les requêtes
Parfois, c'est la requête elle-même qui pose problème.
Meilleures pratiques :
- Éviter les conversions de type implicites.
- Utilisation équijoins plutôt que des fonctions dans les clauses WHERE.
- Remplacer les sous-requêtes corrélées par des jointures.
- Considérer vues matérialisées pour les rapports complexes.
- Utilisation requêtes parallèles dans la mesure du possible.
Étape 5 : Stratégies d'indexation
Les index sont l'épine dorsale de l'optimisation des performances.
Les options comprennent
- Arbre B (par défaut) - Pour les requêtes d'égalité et d'étendue.
- Hachures - Pour de simples recherches d'égalité.
- GIN - Pour les tableaux, JSONB et la recherche plein texte.
- BRIN - Pour les données séquentielles volumineuses telles que les séries temporelles.
- Index partiels - Indexer uniquement les lignes pertinentes.
- Index fonctionnels - Les expressions d'index comme
LOWER(email).
Exemple :
-- Partial index on active users only
CREATE INDEX users_active_idx ON users (last_login)
WHERE active = true;
Étape 6 : Examen du plan final
Après la restructuration et l'indexation, réexécutez EXPLIQUER ANALYSER pour confirmer les améliorations. Rechercher :
- Réduction du temps d'exécution.
- Diminution des coûts estimés et réels.
- Utilisation correcte de l'index.
Liste de contrôle des réglages rapides
- ✅ Activer la journalisation des requêtes pour les requêtes lentes.
- Utilisez EXPLAIN/EXPLAIN ANALYZE pour comprendre les plans d'exécution.
- ✅ Maintenir les statistiques à jour avec ANALYZE/autovacuum.
- ✅ Restructurer les requêtes inefficaces.
- ✅ Appliquer le bon type d'indice.
Réflexions finales
L'optimisation du langage SQL relève à la fois de la science et de l'art.
PostgreSQL vous donne les outils nécessaires pour identifier les problèmes de performance et les résoudre efficacement.
Commencez par l'enregistrement et les plans d'exécution, puis itérez avec des statistiques, des réécritures de requêtes et des stratégies d'indexation.
J'espère que cela vous a été utile.