Aller au contenu
S'identifier S’abonner
Back — Postgresql — SQL — Tools

Décryptez et optimisez vos requêtes SQL PostgreSQL avec EXPLAIN

L'optimisation des requêtes SQL améliore les performances des bases de données, surtout avec de grands ensembles de données. PostgreSQL propose des outils comme EXPLAIN ANALYZE pour analyser et optimiser vos requêtes. Utilisez-les efficacement pour des performances optimales.

,   and  Pierre-Alexandre MANÇAUX
7 lecture min
Photo by DALL-E

L'optimisation des requêtes SQL est essentielle pour améliorer les performances des bases de données, en particulier lorsque vous travaillez avec des ensembles de données volumineux. PostgreSQL, un système de gestion de bases de données relationnelles open-source, offre des outils puissants pour analyser et optimiser les requêtes SQL. Parmi ces outils, EXPLAIN ANALYZE est l'un des plus utiles pour comprendre comment PostgreSQL exécute une requête et identifier les goulots d'étranglement potentiels.

Introduction à EXPLAIN ANALYZE

EXPLAIN ANALYZE est une commande PostgreSQL qui fournit un plan d'exécution détaillé pour une requête SQL. Ce plan montre comment PostgreSQL prévoit d'exécuter la requête, y compris les opérations effectuées, les index utilisés et les coûts estimés. En utilisant EXPLAIN ANALYZE, vous pouvez identifier les parties de la requête qui prennent le plus de temps et ajuster votre SQL ou vos index en conséquence.

Voici un exemple complexe de ce que peut donner EXPLAIN dans PostgreSQL :

Sort  (cost=235.34..236.84 rows=600 width=32) (actual time=8.239..8.242 rows=12 loops=1)
  Sort Key: (avg(e.salary)) DESC
  Sort Method: quicksort  Memory: 25kB
  ->  HashAggregate  (cost=198.50..208.50 rows=600 width=32) (actual time=8.155..8.167 rows=12 loops=1)
        Group Key: d.department_name
        Filter: (count(e.employee_id) > 5)
        ->  Hash Join  (cost=34.00..175.50 rows=1840 width=16) (actual time=0.456..7.123 rows=1750 loops=1)
              Hash Cond: (e.department_id = d.department_id)
              ->  Seq Scan on employees e  (cost=0.00..121.40 rows=1840 width=16) (actual time=0.012..4.567 rows=1750 loops=1)
                    Filter: (hire_date > '2020-01-01'::date)
              ->  Hash  (cost=22.00..22.00 rows=960 width=8) (actual time=0.235..0.235 rows=950 loops=1)
                    Buckets: 1024  Memory Usage: 45kB
                    ->  Seq Scan on departments d  (cost=0.00..22.00 rows=960 width=8) (actual time=0.008..0.156 rows=950 loops=1)
Planning Time: 0.345 ms
Execution Time: 8.456 ms

Mais alors, comment décrypter tout ce charabia de PostgreSQL ? Laissez-moi être votre Sherlock Holmes des plans d'exécution, votre Indiana Jones des performances de requêtes, bref, votre guide éclairé dans cette passionnante aventure ! 

Anatomie d'un plan d'exécution PostgreSQL : comprendre les types d'opérations

Voici un aperçu des types d'opérations que vous pouvez rencontrer dans les plans d'exécution de PostgreSQL, ainsi que des conseils sur ceux à éviter et les solutions possibles pour améliorer les performances :

Seq Scan (Scan séquentiel)

  • Description : parcourt chaque ligne de la table.
  • À éviter : oui, pour les grandes tables, car cela peut être très coûteux en termes de temps d'exécution.
  • Solution : ajouter des index appropriés sur les colonnes utilisées dans les clauses WHERE pour permettre des scans d'index au lieu de scans séquentiels.

Index Scan

  • Description : utilise un index pour trouver les lignes correspondantes.
  • À éviter : non, c'est généralement plus rapide que le scan séquentiel. Assurez-vous que les index sont bien maintenus et que les statistiques sont à jour.

Index Only Scan

  • Description : utilise uniquement l'index pour récupérer les données, sans accéder à la table.
  • À éviter : non, c'est très efficace si toutes les colonnes nécessaires sont dans l'index. Attention à ne pas inclure toute la base de données dans les index.

Bitmap Heap Scan

  • Description : utilise un bitmap pour marquer les lignes à lire, puis accède à la table.
  • À éviter : non, c'est souvent utilisé pour optimiser les requêtes qui retournent beaucoup de lignes. Utiliser des index appropriés et maintenir les statistiques à jour.

Bitmap Index Scan

  • Description : Utilise un index pour créer un bitmap des lignes à lire.
  • À éviter : Non, c'est souvent utilisé en combinaison avec un bitmap heap scan. Assurez-vous que les index sont bien définis et que les statistiques sont à jour.

Nested Loop

  • Description : pour chaque ligne de la table externe, parcourt la table interne.
  • À éviter : oui, pour les grandes tables, car cela peut être très coûteux.
  • Solution : utiliser des index sur les colonnes de jointure, ou envisager d'autres types de jointures comme les hash joins ou merge joins.

Hash Join

  • Description : utilise une table de hachage pour joindre les tables.
  • À éviter : non, c'est généralement efficace pour les grandes tables. Assurez-vous que les statistiques sont à jour et que les paramètres de mémoire (comme work_mem) sont suffisamment élevés pour permettre des opérations de hachage efficaces.

Merge Join

  • Description : trie les deux tables puis les joint.
  • À éviter : non, c'est efficace pour les tables déjà triées. Utiliser des index sur les colonnes de jointure et assurer que les statistiques sont à jour.

Hash Aggregate

  • Description : utilise une table de hachage pour agréger les résultats.
  • À éviter : non, c'est efficace pour les agrégations. Assurez-vous que les paramètres de mémoire (comme work_mem) sont suffisamment élevés pour permettre des agrégations efficaces.

Sort

  • Description : trie les résultats.
  • À éviter : oui, si possible, car cela peut être coûteux en termes de mémoire et de temps.
  • Solution : utiliser des index pour éviter les tris explicites et ajuster les paramètres de mémoire (comme work_mem) pour améliorer les performances de tri.

Explication du coût

Le coût dans un plan d'exécution PostgreSQL est une estimation de la quantité de ressources nécessaires pour exécuter une opération. Il est exprimé en unités arbitraires et comprend deux valeurs principales :

  • Start-up Cost : Le coût initial pour commencer l'opération.
  • Total Cost : Le coût total pour compléter l'opération.

Le coût est influencé par plusieurs facteurs, notamment la taille des données, la présence d'index, et les statistiques de la table. PostgreSQL utilise ces coûts pour choisir le plan d'exécution le plus efficace.

Importance des statistiques et de VACUUM

Pour que PostgreSQL puisse estimer correctement les coûts, il est crucial d'avoir des statistiques à jour sur vos tables et index. Les statistiques sont utilisées par le planificateur de requêtes pour évaluer le nombre de lignes que chaque opération va traiter et pour choisir le plan d'exécution le plus efficace.

L'utilisation de la commande VACUUM (et plus spécifiquement VACUUM ANALYZE) permet de mettre à jour ces statistiques. VACUUM nettoie les tables en supprimant les lignes obsolètes, tandis que ANALYZE collecte des statistiques sur la distribution des données. Il est recommandé d'exécuter régulièrement VACUUM ANALYZE pour maintenir des performances optimales.

Option BUFFERS : comprendre la mémoire de PostgreSQL en action

L'option BUFFERS dans EXPLAIN ANALYZE fournit des informations supplémentaires sur l'utilisation des tampons (buffers) lors de l'exécution de la requête. Les tampons sont des blocs de mémoire utilisés pour stocker temporairement les données lues depuis le disque. Cette option est utile pour comprendre l'impact des opérations de lecture et d'écriture sur la mémoire.

Types de buffers

  • shared hit: Nombre de tampons partagés trouvés dans le cache.
  • shared read: Nombre de tampons partagés lus depuis le disque.
  • shared dirtied: Nombre de tampons partagés modifiés dans le cache.
  • shared written: Nombre de tampons partagés écrits sur le disque.
  • local hit: Nombre de tampons locaux trouvés dans le cache (utilisé dans les transactions).
  • local read: Nombre de tampons locaux lus depuis le disque.
  • local dirtied: Nombre de tampons locaux modifiés dans le cache.
  • local written: Nombre de tampons locaux écrits sur le disque.
  • temp read: Nombre de tampons temporaires lus depuis le disque.
  • temp written: Nombre de tampons temporaires écrits sur le disque.

Paramètres PostgreSQL influant sur les buffers

Pour optimiser l'utilisation des tampons, vous pouvez ajuster plusieurs paramètres PostgreSQL :

  • shared_buffers: Définit la quantité de mémoire allouée aux tampons partagés. Augmenter cette valeur peut améliorer les performances en réduisant les lectures disque.
  • work_mem: Définit la quantité de mémoire allouée aux opérations de tri et de hachage. Augmenter cette valeur peut améliorer les performances des opérations de tri et d'agrégation.
  • maintenance_work_mem: Utilisé pour les opérations de maintenance comme VACUUM et CREATE INDEX. Augmenter cette valeur peut accélérer ces opérations.
❗
Augmenter ces paramètres peut améliorer les performances, mais des valeurs trop élevées peuvent être contre-productives. Il est essentiel de les ajuster correctement en fonction de la configuration et de l'utilisation spécifiques de votre système.

Exemple d'utilisation

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM employees WHERE department_id = 1;

Résultat :

Seq Scan on employees  (cost=0.00..18.10 rows=2 width=37) (actual time=0.012..0.014 rows=2 loops=1)
  Buffers: shared hit=4
  Filter: (department_id = 1)
  Rows Removed by Filter: 2
Planning Time: 0.080 ms
Execution Time: 0.032 ms

Dans cet exemple, l'option BUFFERS montre que 4 tampons partagés ont été trouvés dans le cache (shared hit=4). Ces informations peuvent aider à diagnostiquer les problèmes de performance liés à l'utilisation de la mémoire.

Exemple de requêtes et plans d'exécution

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INT
);

INSERT INTO employees (name, department_id)
VALUES
('Alice', 1),
('Bob', 2),
('Charlie', 1),
('David', 3);

Requête: SELECT * FROM employees WHERE department_id = 1;

Explain: EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 1;

Résultat

Seq Scan on employees  (cost=0.00..18.10 rows=2 width=37) (actual time=0.012..0.014 rows=2 loops=1)
  Filter: (department_id = 1)
  Rows Removed by Filter: 2
Planning Time: 0.080 ms
Execution Time: 0.032 ms

Dans ce plan d'exécution, PostgreSQL utilise un Seq Scan (scan séquentiel) pour parcourir la table employees. La requête est simple et rapide, mais pour des tables plus grandes, un scan séquentiel peut devenir coûteux.

Requête avec index

Pour améliorer les performances, nous pouvons ajouter un index sur la colonne department_id.

CREATE INDEX idx_department_id ON employees(department_id);

Requête: SELECT * FROM employees WHERE department_id = 1;

Explain: EXPLAIN ANALYZE SELECT * FROM employees WHERE department_id = 1;

Résultat

Index Scan using idx_department_id on employees  (cost=0.14..8.16 rows=2 width=37) (actual time=0.010..0.012 rows=2 loops=1)
  Index Cond: (department_id = 1)
Planning Time: 0.123 ms
Execution Time: 0.027 ms

Avec l'index, PostgreSQL utilise un Index Scan au lieu d'un Seq Scan, ce qui réduit considérablement le coût et le temps d’exécution.

Conclusion

L'optimisation des requêtes SQL est cruciale pour maintenir des performances élevées dans PostgreSQL.
En utilisant EXPLAIN ANALYZE, vous pouvez obtenir des informations détaillées sur les plans d'exécution de vos requêtes et les performances globales de votre base de données.

Que vous travailliez avec des requêtes simples ou complexes, l'analyse des plans d'exécution et des logs vous aidera à identifier les goulots d'étranglement et à optimiser vos requêtes pour des performances optimales.

Pour approfondir ce sujet, un prochain article détaillera l'utilisation d'outils d'analyse avancés comme pgBadger et le site Depesz.

Lié

Dernier