Performances des requêtes

Introduction

La performance d’une requête SQL est un élément important de la mise au point d’une application : en effet, obtenir un résultat correct est une condition nécessaire, mais pas suffisante pour qu’une application soit considérée comme livrable : le temps et les ressources consommées sont des données critiques qu’il faut maîtriser du mieux possible.

Pour tenter de maîtriser ces deux notions, il faut comprendre comment PostgreSQL reçoit les ordres SQL et les interprète pour renvoyer les résultats : une requête SQL est transformée en un arbre d’exécution, où chaque nœud de l’arbre correspond à une étape. La nature de chaque nœud est choisie par PostgreSQL en fonction de ce qui semble le moins coûteux à l’interpréteur au moment du choix. Ce choix se base sur des estimations des ordres de grandeur des données présentes dans la base de données, pondérées par des constantes de coûts, elles-mêmes modifiables par l’intermédiaire de paramètres de configuration.

Il existe de nombreuses raisons qui font que les choix semblent ne pas être les meilleurs possibles, comme par exemple, le fait que les estimations utilisées ne représentent pas correctement la réalité...

La commande EXPLAIN

La commande EXPLAIN a pour but de montrer le plan d’exécution choisi par l’instance PostgreSQL pour exécuter une requête SQL. Pour chaque étape, ou nœud, les différentes possibilités sont évaluées et la moins coûteuse est sélectionnée. Les options qui ne sont pas retenues ne sont pas montrées.

La commande a comme principal argument la requête SQL que l’on souhaite analyser, très souvent une requête SELECT. Différentes options permettent de modifier les données affichées dans l’arbre d’exécution. Les principales options sont les suivantes :

ANALYZE : en exécutant effectivement la requête, cette option permet l’affichage des mesures chronométriques de chacune des étapes de la requête ainsi que du nombre de tuples résultant de chaque étape. Cette option est désactivée par défaut.
VERBOSE : affiche des informations supplémentaires pour chacune des étapes, comme la liste des colonnes retournées, ou le nom qualifié des tables et des fonctions. Cette option est désactivée par défaut.
COSTS : affiche les informations sur les coûts des étapes : coût initial et coût final, estimation du nombre de lignes et de la taille des lignes. Cette option est activée par défaut.
BUFFERS : pour chaque étape de la requête, affiche les informations sur les blocs de données lus depuis la mémoire (hit) ou depuis les fichiers (read), ainsi que sur les blocs modifiés (dirtied) par l’étape, et ceux retirés de la mémoire (written) à cette étape. Les nombres de blocs indiqués incluent les blocs des étapes antérieures de la requête. Cette option est désactivée par défaut et ne peut être utilisée que si l’option ANALYZE est activée.
TIMING : lorsque l’option ANALYZE est activée, cette option est elle-même activée par défaut, chronométrant les différentes étapes de la requête. Cette option permet en fait de désactiver les mesures chronométriques avec l’option ANALYZE.
SUMMARY : affiche le temps total...

Affichage d’un plan d’exécution

Même si le mode texte est lisible et qu’il est possible de suivre le chemin des étapes, lorsque la complexité de l’arbre augmente, il devient difficile d’exploiter correctement les données présentes et ainsi de comprendre le plan d’exécution. L’aide d’un outil mettant en forme le plan d’exécution et extrayant les informations pertinentes est essentielle pour être en mesure de proposer des améliorations.

Les outils graphiques comme Dbeaver, PgAdmin ou OmniDB proposent des affichages graphiques des plans d’exécution, qui peuvent aider à comprendre ces plans. Différents outils en ligne sont spécialisés dans l’affichage de ces plans, et parmi eux, le plus efficace et le plus utilisé est disponible à l’adresse : https://explain.depesz.com/

La zone de texte permet de copier le plan d’exécution dans l’outil, il est possible de rajouter un titre au plan, de ne pas faire apparaître ce plan dans l’historique et d’obfusquer les noms des objets. Une fois les données envoyées, un tableau est affiché contenant une étape par ligne et les informations dans les colonnes, comme le montre la copie d’écran suivante :

Les colonnes du tableau sont les suivantes :

exclusive : ce temps...

Interpréter un plan d’exécution

Les différentes étapes d’un plan d’exécution correspondent à des types de traitements des données spécifiques. Les types de traitements sont présents dans PostgreSQL, et correspondent à des algorithmes bien précis. La liste suivante résume les principaux types de traitements utilisés :

SeqScan : il s’agit de lire l’ensemble des blocs de données d’une table, et éventuellement d’y appliquer un filtre en fonction des clauses WHERE. Les blocs de données sont lues depuis la mémoire partagée, ou depuis le système de fichiers, selon ce qui est indiqué par l’option BUFFERS.
Index Scan : il s’agit de lire une partie des données d’une table, en passant par un index. En fonction d’une sélection des données à lire, généralement de par la clause WHERE d’une requête SELECT, l’arbre de l’index est parcouru pour identifier les blocs de données de la table où sont présentes les données. Ce type de traitement lit donc les données de l’index et de la table. Il existe des parcours inverses dits Index Scan Backward.
Index Only Scan : à la différence du précédent type, il s’agit de lire les données seulement dans l’index, et pas dans...

Création d’index

La création d’index est une étape importante de la mise au point d’une base de données : le but souhaité lors de cette création est d’améliorer l’efficacité d’une requête SQL, en temps d’exécution et en quantité de données lues.

En simplifiant autant que possible, lors de la lecture d’une table, les blocs de données sont parcourus et les lignes comprises dans les blocs sont lues pour trouver les données recherchées par une requête SELECT. Quels que soient les filtres de recherche, lorsque la quantité de données réellement trouvées est très petite par rapport au volume de la table, cette façon de faire est particulièrement inefficace.

Un index est une structure de données physique qui permet d’accéder plus rapidement aux données : afin de ne pas parcourir l’ensemble des blocs, les données sont triées de telle sorte que la recherche d’une valeur n’entraîne que quelques parcours à faire dans la structure de données, et donc dans un nombre limité de blocs de données. Ce parcours d’index indique alors dans quels blocs de données de la table sont situées les lignes effectivement recherchées, ce qui est beaucoup plus efficace.

La principale difficulté que PostgreSQL doit résoudre consiste à déterminer la part des données de la table : plus la part est importante, moins l’utilisation d’un index est effectivement pertinente.

S’il existe différentes structures d’index, la plus courante est l’arbre équilibré, ou B-tree : les données sont placées dans un arbre, de telle sorte que chaque avancée dans l’arbre élimine la moitié des données de l’arbre : les parcours sont alors très courts, et sont rapides et efficaces. D’autres types de structures physiques existent, qui concernent principalement des données non-scalaires comme des plages ou des tableaux de données.

Dans tous les cas, les index sont mis à jour en même temps que les données des tables, dans les transactions, et la lecture d’un index reflète toujours...