DK Damir Khaibulin
🇷🇺 RU Russe 🇬🇧 EN Anglais 🇩🇪 DE Allemand 🇫🇷 FR Français 🇮🇹 IT Italien 🇪🇸 ES Espagnol
Parlons
← Retour aux articles
Performance et bases de données

Optimisation des requêtes : EXPLAIN et EXPLAIN ANALYZE dans PostgreSQL + autres outils d'analyse

Un guide pratique de l'analyse des requêtes PostgreSQL : EXPLAIN et EXPLAIN ANALYZE, plus pg_stat_statements, auto_explain, pgBadger, Laravel Telescope et Percona PMM avec des exemples réels.

Parlons-en ↗ Lire l'article
Langues disponibles
EN RU DE FR IT ES
✦
Article en vedette ↗

Optimisation des requêtes : EXPLAIN et EXPLAIN ANALYZE dans PostgreSQL + autres outils d'analyse

📊 Que sont EXPLAIN et EXPLAIN ANALYZE ?

EXPLAIN est une commande dans PostgreSQL (et d'autres bases de données SQL) qui affiche le plan d'exécution d'une requête sans réellement l'exécuter. Elle répond à la question : « Comment le SGBD prévoit-il d'exécuter cette requête ? »

EXPLAIN ANALYZE est une version plus puissante qui exécute réellement la requête et affiche des métriques d'exécution réelles : temps, nombre de lignes, index utilisés, etc.

🎯 Pourquoi en avons-nous besoin ?

À l'ère du big data et des applications à forte charge, comprendre les performances d'une base de données est une compétence critique. Ces outils aident à :

  • Identifier les goulots d'étranglement de performance
  • Optimiser les index et la structure de la base de données
  • Comprendre le comportement de l'optimiseur de requêtes
  • Réduire la charge sur le serveur de base de données

📈 Exemple d'utilisation dans Laravel avec PostgreSQL

// EXPLAIN simple $explain = DB::select('EXPLAIN SELECT * FROM users WHERE email = ?', ['user@example.com']); dd($explain); // EXPLAIN ANALYZE $explainAnalyze = DB::select('EXPLAIN ANALYZE SELECT * FROM users WHERE email = ?', ['user@example.com']); dd($explainAnalyze); // Utilisation de macros pour plus de commodité DB::macro('explain', function ($query, $bindings = []) { return DB::select('EXPLAIN ' . $query, $bindings); }); DB::macro('explainAnalyze', function ($query, $bindings = []) { return DB::select('EXPLAIN ANALYZE ' . $query, $bindings); }); // Exemple d'utilisation de macro $users = DB::explain('SELECT * FROM users WHERE active = true');

🔍 Analyse des résultats EXPLAIN

Une sortie typique inclut :

Seq Scan on users (cost=0.00..15.00 rows=500 width=44) Filter: (email = 'user@example.com'::text)
  • Seq Scan — scan séquentiel (peut être lent)
  • Index Scan — scan par index (plus rapide)
  • cost — coût estimé (plus bas est mieux)
  • rows — nombre de lignes attendu

🛠 Autres outils d'analyse pour PostgreSQL

1. pg_stat_statements — Surveillance de l'exécution des requêtes

-- Activer l'extension CREATE EXTENSION pg_stat_statements; -- Requêtes les plus coûteuses SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

2. auto_explain — Analyse automatique des requêtes lentes

-- Activer dans postgresql.conf shared_preload_libraries = 'auto_explain' auto_explain.log_min_duration = '100ms' -- journaliser les requêtes >100ms

3. pgBadger — Analyseur de logs PostgreSQL

# Installation et utilisation pgbadger /var/log/postgresql/postgresql-*.log -o report.html

4. Laravel Telescope — Diagnostics intégrés à Laravel

// Activer la surveillance des requêtes dans config/telescope.php 'watchers' => [ QueryWatcher::class => [ 'enabled' => env('TELESCOPE_QUERY_WATCHER', true), 'slow' => 100, // requêtes lentes >100ms ], ]

5. Percona Monitoring and Management (PMM) — Surveillance complète des bases de données

Plateforme open-source pour la surveillance et la gestion des performances de MySQL, MongoDB et PostgreSQL.

6. EXPLAIN (FORMAT JSON) — Analyse détaillée des requêtes

-- Obtenir un plan d'exécution détaillé au format JSON EXPLAIN (FORMAT JSON, ANALYZE) SELECT * FROM users;

🚀 Exemples pratiques d'optimisation dans Laravel

Optimisation du problème N+1

// MAUVAIS : N+1 requêtes $users = User::all(); foreach ($users as $user) { echo $user->posts->count(); // Requête séparée pour chaque utilisateur } // BON : chargement anticipé $users = User::with('posts')->get(); foreach ($users as $user) { echo $user->posts->count(); // Toutes les données déjà chargées }

Analyse de requêtes complexes avec utilisation d'index

// Créer un index Schema::table('orders', function (Blueprint $table) { $table->index(['user_id', 'created_at']); }); // Analyser la requête $analysis = DB::explainAnalyze(" SELECT users.name, COUNT(orders.id) as order_count FROM users JOIN orders ON users.id = orders.user_id WHERE orders.created_at > NOW() - INTERVAL '30 days' GROUP BY users.id HAVING COUNT(orders.id) > 5 ");

📊 Guide de comparaison des outils

Explorons les principales différences entre les outils d'analyse de performance de base de données :

EXPLAIN affiche le plan d'exécution théorique sans exécuter la requête. Il est parfait pour l'analyse préliminaire des requêtes lorsque vous voulez comprendre comment PostgreSQL prévoit d'exécuter votre instruction SQL. Utilisez-le pendant le développement des requêtes pour détecter les problèmes de performance potentiels tôt.

EXPLAIN ANALYZE va plus loin en exécutant réellement la requête et en fournissant des métriques de performance réelles. Cet outil révèle les temps d'exécution réels, les nombres de lignes et l'utilisation des ressources. Il est essentiel pour identifier les vrais goulots d'étranglement dans des environnements proches de la production, mais doit être utilisé avec prudence sur les systèmes de production en raison de l'exécution de la requête.

pg_stat_statements offre des statistiques complètes sur toutes les requêtes exécutées dans votre base de données. Il suit la fréquence d'exécution, le temps total et le temps d'exécution moyen de toutes les requêtes. C'est inestimable pour identifier les requêtes les plus coûteuses de votre application au fil du temps.

auto_explain journalise automatiquement les requêtes lentes selon des seuils configurables. Une fois configuré, il fonctionne silencieusement en arrière-plan, capturant les plans d'exécution des requêtes dépassant votre seuil de durée défini. Parfait pour la surveillance continue des performances sans intervention manuelle.

Laravel Telescope fournit une surveillance des requêtes au niveau de l'application dans l'écosystème Laravel. Il montre les requêtes dans le contexte de votre application, y compris les relations Eloquent, les requêtes HTTP et le traitement des jobs. Idéal pour les environnements de développement et de staging.

pgBadger analyse les fichiers de log PostgreSQL pour générer des rapports HTML détaillés sur les performances de la base de données. Il aide à identifier les patterns, les requêtes lentes et les problèmes de connexion sur de longues périodes.

Percona Monitoring and Management offre une surveillance de niveau entreprise avec des tableaux de bord de visualisation, des systèmes d'alerte et des analyses de requêtes sur plusieurs technologies de base de données.

💡 Conseils pratiques

  1. Commencez par EXPLAIN pour une analyse rapide
  2. Utilisez EXPLAIN ANALYZE pour des données précises
  3. Créez des index basés sur les résultats d'analyse
  4. Surveillez régulièrement — les performances changent avec la croissance des données
  5. Testez avec des volumes de données réalistes
  6. Combinez les outils pour une analyse complète
  7. Configurez des alertes pour la dégradation des performances

🔗 Conclusion

Comprendre les outils d'analyse de requêtes n'est pas un luxe mais une nécessité pour les développeurs modernes. Dans l'écosystème Laravel, nous avons des outils puissants à la fois au niveau de la base de données (PostgreSQL) et au niveau du framework (Telescope).

Points clés à retenir :

  • Analysez toujours les requêtes avant le déploiement
  • Les index résolvent 80 % des problèmes de performance
  • Une surveillance régulière prévient la dégradation des performances
  • Combinez plusieurs outils pour une visibilité complète
  • Testez les performances dans des conditions réalistes
Technologies et sujets

Tags de l'article

Aucun article ne correspond à ces filtres.

Vous avez un projet ou une idée à discuter ?

Parlons-en ↗