Postgres : explain plan, ou comment optimiser ses requêtes sql

Lorsqu’on travaille sur des bases de données un peu grosses, que certaines requêtes commencent à être bien alambiquées, on peut être confronté à des problèmes de performance. Dans la plupart des SGDB, il existe des outils d’analyse de requête. Pour postgres, c’est la commande explain qui est utilisée pour ça.

Utilisation

Tiré de la documentation :

Cette commande affiche le plan d’exécution que l’optimiseur de PostgreSQL™ engendre pour l’instruction fournie. Le plan d’exécution décrit le parcours de la (des) table(s) utilisée(s) dans la requête — parcours séquentiel, parcours d’index, etc. — . Si plusieurs tables sont référencées, il présente également les algorithmes de jointures utilisés pour rassembler les lignes issues des différentes tables.

En fait, postgres analyse le contenu des tables afin d’estimer au mieux comment lancer les requêtes (le plan d’exécution). Cette opération d’étude est faite pendant la séquence analyze, il est donc important de lancer cette commande régulièrement (elle fait partie des opérations de maintenance de la base), surtout quand le contenu des tables a fortement évolué. Prenons l’exemple d’une table de 10 enregistrements, avec une clé indexée. Postgres estimera qu’il est plus rapide de faire un scan séquentiel qu’un scan d’index. Rajoutez 1 000 000 enregistrements à cette table. Tant que vous n’aurez pas fait un analyze, postgres continuera à utiliser un scan séquentiel… avec des conséquences importantes sur la réactivité de la base.

OK, mais concrètement, comment ça marche ?

C’est relativement simple à utiliser. Supposons que l’on veuille étudier la requête select * from disques, il faut lancer la requête :

explain select * from disques;

Le retour va ressembler à ça :

EXPLAIN SELECT * FROM disques;
QUERY PLAN

Seq Scan on disques (cost=0.00..458.00 rows=10000 width=244)

Voici ce que cela signifie

  • Seq Scan : la requête s’est fait de façon séquentielle sur la table
  • cost : il s’agit d’une estimation du coût de la requête. Dans notre cas, on commence à 0.00, on finit à 458.00, soit un coût de 458. On ne peut pas comparer ça à du temps machine, cela nous servira dans des analyses plus complètes à faire des comparaisons
  • rows : une estimation du nombre de lignes que va donner la requête

Les valeurs indiquées sont celles nécessaires à la requête en elle même, le temps d’envoi des données au client (psql, pgadmin3, php…) n’est pas pris en compte.
Bon, là, c’est sur, ça ne parle pas vraiment. On va passer à des exemples plus concret. Prenons une table evenements qui contient environ 2 millions d’enregistrements. Dans cette table, nous avons deux champs : type_evenement, qui est indexé (index idx_type_evenement), et descriptif_evenement, qui ne l’est pas


base_test=#
explain select * from evenements where type_evenement = 1;
QUERY PLAN
--------------------------------------------------------------------------
Index Scan using idx_type_evenement on evenements
(cost=0.00..2608.89 rows=1068 width=127)
Index Cond: (type_evenement = 1) (2 rows)
base_test=# explain select * from evenements where descriptif_evenement = 1;
QUERY PLAN
-------------------------------------------------
Seq Scan on evenements (cost=0.00..58581.22 rows=19 width=127)
Filter: (descriptif_evenement = '1'::text) (2 rows)

Là, on commence à y voir plus clair. Dans le premier cas, on fait un index scan : on utilise un index pour la recherche, pour un coût de 2344.62. Dans le second cas, on fait une recherche séquentielle (Seq Scan) pour un coût de 58581.22, ce qui signifie que la seconde requête sera environ 20 fois plus longue que la première !
On se doute donc que si la seconde requête est longue, c’est surement qu’il manque un index… De plus, cela peut poser problème quand on fait un update de cette table en fonction du descriptif_evenement, cela va locker la table, et d’autres requêtes peuvent s’accumuler derrière, et provoquer un écroulement du serveur.

On peut également faire des requêtes plus complexes :


base_test=#
explain select * from evenements where descriptif_evenement = 1 and type_evenement = 1;
QUERY PLAN
---------------------------------------------------------------------
Index Scan using idx_type_evenement on evenements (cost=0.00..2611.89 rows=1 width=127)
Index Cond: (type_evenement = 1)
Filter: (descriptif_evenement = '1'::text)

Dans ce cas, nous voyons que la base fait d’abord une recherche par index, puis elle filtre les lignes trouvées. Au final, ça ne prend presque pas plus de temps que si on ne voulait que les évènements de type_evenement = 1;

Requêtes avec jointures

Nous travaillons toujours sur notre table d’évènements, qui est maintenant liée à une table utilisateurs (users) par le champ evt_user_cod (indexé). Dans la table utilisateurs, il y a le code utilisateur (user_cod), clé primaire indexée, et le nom utilisateur (user_nom), valeur non indexées.
Nous voulons tous les évnènements liés à l’utilisateur ‘admin’ :


base_test=#
explain select evenements.* from evenements,user where user_nom = 'admin' and user_cod = evt_user_cod;
QUERY PLAN
------------------------------------------------------------------------
Nested Loop (cost=5.52..2094.74 rows=136 width=129)
-> Seq Scan on user (cost=0.00..1569.38 rows=1 width=4)
Filter: ((user_nom)::text = 'admin'::text)
-> Bitmap Heap Scan on evenements (cost=5.52..523.67 rows=136 width=129)
Recheck Cond: (evenements.evt_user_cod = user.user_cod)
-> Bitmap Index Scan on idx_evt_user_cod (cost=0.00..5.49 rows=136 width=0)
Index Cond: (evenements.evt_user_cod = user.user_cod)

Nous voyons donc ici un coût total de 2094. L’étape principale du coût est le seq scan sur le user_nom (qui représente les trois quarts de la requête avec son 1569). L’idéal serait bien sur de créer un index sur le user_nom, mais imaginons que l’on ne puisse pas le faire. Analysons la requête autrement :


base_test=#
explain select user_cod from user where user_nom = 'admin';
QUERY PLAN
--------------------------------------------------------
Seq Scan on user (cost=0.00..1569.38 rows=1 width=4)
Filter: ((user_nom)::text = 'admin'::text) (2 lignes)

Trouver le code utilisateur pour admin à un coût de 1569. Jusque là, pas de surprise, on retrouve la valeur précédente. Supposons que le code utilisateur est 1. Nous recherchons tous les évènements liés à l’utilisateur 1 :


base_test=#
explain select evenements.* from evenements where evt_user_cod = 1;
QUERY PLAN
---------------------------------------------------------------------------
Bitmap Heap Scan on evenements (cost=5.48..501.67 rows=130 width=129)
Recheck Cond: (evt_user_cod = 1)
-> Bitmap Index Scan on idx_evt_user_cod (cost=0.00..5.45 rows=130 width=0)
Index Cond: (evt_user_cod = 1) (4 lignes)

On arrive à un coût de 501. Si on l’ajoute au coût précédent de 1569, cela nous fait un total de 2070, soit légèrement moins que si on lançait la requête en une seule fois, mais pas suffisamment pour découper la requête en plusieurs parties.

Cas particulier des fonctions

Les fonctions intégrées ne sont pas analysées par explain. Si vous remarquez qu’une fonction prend trop de temps, il vous faut regarder la source de la fonction et analyser chaque étape une à une…

Conclusion

Ceci n’est qu’un aperçu de cette fonction. A vous d’apprendre à l’utiliser pour en tirer le plus de bénéfices possibles. L’analyse des requêtes prenant beaucoup de temps machine est essentielle pour le bon fonctionnement de vos bases de données, et apprendre à utiliser l’explain plan est essentiel pour tout dba.

Laisser un commentaire