La performance des requêtes peut être affectée par un grand nombre d'éléments. Certains peuvent être manipulés par l'utilisateur, d'autres sont fondamentaux au concept sous-jacent du système. Ce chapitre fournit des conseils sur la compréhension et sur la configuration fine des performances de PostgreSQL™.
PostgreSQL™ réalise un plan de requête pour chaque requête qu'il reçoit. Choisir le bon plan correspondant à la structure de la requête et aux propriétés des données est absolument critique pour de bonnes performances, donc le système inclut un planificateur complexe qui tente de sélectionner les bons plans. Vous pouvez utiliser la commande EXPLAIN pour voir quel plan de requête le planificateur crée pour une requête particulière. La lecture du plan est un art qui mérite un tutoriel complet, ce que vous n'aurez pas là ; ici ne se trouvent que des informations de base.
La structure d'un plan de requête est un arbre de nœuds de plan. Les nœuds de bas niveau sont les nœuds de parcours de tables : ils renvoient les lignes brutes d'une table. Il existe différents types de nœuds de parcours pour les différentes méthodes d'accès aux tables : parcours séquentiel, parcours d'index et parcours d'index bitmap. Si la requête requiert des jointures, agrégations, tris ou d'autres opérations sur les lignes brites, ce seront des nœuds supplémentaires « au-dessus » des nœuds de parcours qui réaliseront ces opérations. De nouveau, il y a habituellement plus d'une façon possible de réaliser ces opérations, donc différents types de nœuds peuvent aussi apparaître ici. La sortie d'EXPLAIN comprend une ligne pour chaque nœud dans l'arbre du plan, montrant le type de nœud basique avec les estimations de coût que le planificateur a fait pour l'exécution de ce nœud du plan. La première ligne (le nœud tout en haut) comprend le coût d'exécution total estimé pour le plan ; c'est ce nombre que le planificateur cherche à minimiser.
Voici un exemple trivial, juste pour montrer à quoi ressemble l'affichage. [7]
Verrouillage et index
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
Les nombres donnés par EXPLAIN sont :
Coût estimé du lancement (temps passé avant que l'affichage de la sortie ne commence, c'est-à-dire pour faire le tri dans un nœud de tri) ;
Coût total estimé (si toutes les lignes doivent être récupérées, ce qui pourrait ne pas être le cas : par exemple une requête avec une clause LIMIT ne paiera pas le coût total du nœud d'entrée du nœud du plan Limit) ;
Nombre de lignes estimé en sortie par ce nœud de plan (encore une fois, seulement si exécuté jusqu'au bout) ;
Largeur moyenne estimée (en octets) des lignes en sortie par ce nœud de plan.
Les coûts sont mesurés en unités arbitraires déterminées par les paramètres de coût du planificateur (voir Section 17.6.2, « Constantes de coût du planificateur »). La pratique habituelle est de mesurer les coûts en unité de récupération de pages disque ; autrement dit, seq_page_cost est initialisé à 1.0 par convention et les autres paramètres de coût sont relatifs à cette valeur. Les exemples de cette section sont exécutés avec les paramètres de coût par défaut.
Il est important de noter que le coût d'un nœud de haut niveau inclut le coût de tous les nœuds fils. Il est aussi important de réaliser que le coût reflète seulement les éléments d'importance pour le planificateur. En particulier, le coût ne considère pas le temps dépensé dans la transmission des lignes de résultat au client, ce qui pourrait être un facteur important dans le temps réellement passé ; mais le planificateur l'ignore parce qu'il ne peut pas le changer en modifiant le plan (chaque plan correct sortira le même ensemble de lignes).
La sortie des lignes est un peu difficile car il ne s'agit pas du nombre de lignes traitées ou parcourues par le plan de nœuds. C'est habituellement moins, reflétant la sélectivité estimée des conditions de la clause WHERE qui sont appliquées au nœud. Idéalement, les estimations des lignes de haut niveau sera une approximation des nombres de lignes déjà renvoyées, mises à jour, supprimées par la requête.
Pour revenir à notre exemple :
EXPLAIN SELECT * FROM tenk1;
QUERY PLAN
-------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..458.00 rows=10000 width=244)
C'est aussi direct que ce que nous obtenons. Si vous faîtes :
SELECT relpages, reltuples FROM pg_class WHERE relname = 'tenk1';
vous trouverez que tenk1 a 358 pages disque et 10000 lignes. Donc, le coût est estimé à 358 lectures de page, chacune coûtant seq_page_cost (1,0 par défaut), plus 10000 * cpu_tuple_cost (0,01 par défaut).
Maintenant, modifions la requête pour ajouter une condition WHERE :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 7000;
QUERY PLAN
------------------------------------------------------------
Seq Scan on tenk1 (cost=0.00..483.00 rows=7033 width=244)
Filter: (unique1 < 7000)
Notez que l'affichage d'EXPLAIN montre la clause WHERE appliquée comme une condition de « filtre » ; ceci signifie que le nœud de plan vérifie la condition pour chaque ligne qu'il parcourt et ne conserve que celles qui satisfont la condition. L'estimation des lignes en sortie a baissé à cause de la clause WHERE. Néanmoins, le parcours devra toujours visiter les 10000 lignes, donc le coût n'a pas baissé ; en fait, il a un peu augmenté pour refléter le temps CPU supplémentaire dépensé pour vérifier la condition WHERE.
Le nombre réel de lignes que cette requête sélectionnera est 7000 mais l'estimation est approximative. Si vous tentez de dupliquer cette expérience, vous obtiendrez probablement une estimation légèrement différente ; de plus, elle changera après chaque commande ANALYZE parce que les statistiques produites par ANALYZE sont prises à partir d'un extrait au hasard de la table.
Maintenant, rendons la condition plus restrictive :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100;
QUERY PLAN
------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
Ici, le planificateur a décidé d'utiliser un plan en deux étapes : le nœud en bas du plan visite un index pour trouver l'emplacement des lignes correspondant à la condition de l'index, puis le nœud du plan du dessus récupère réellement ces lignes de la table. Récupérer séparément les lignes est bien plus coûteux que de les lire séquentiellement mais comme toutes les pages de la table n'ont pas à être visitées, cela revient toujours moins cher qu'un parcours séquentiel (la raison de l'utilisation d'un plan à deux niveaux est que le nœud du plan du dessus trie les emplacements des lignes identifiés par l'index dans l'ordre physique avant de les lire pour minimiser les coûts des récupérations séparés. Le « bitmap » mentionné dans les noms de nœuds est le mécanisme qui s'occupe du tri).
Si la condition WHERE est assez sélective, le planificateur pourrait basculer vers un plan de parcours d'index « simple » :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3;
QUERY PLAN
------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.00 rows=2 width=244)
Index Cond: (unique1 < 3)
Dans ce cas, les lignes de la table sont récupérées dans l'ordre de l'index, ce qui les rend encore plus coûteuses à lire mais elles sont si peu nombreuses que le coût supplémentaire de triage des emplacements de lignes ne vaut pas le coup. Vous verrez plus fréquemment ce type de plan pour les requêtes qui récupèrent une seule ligne et pour les requêtes qui ont une condition ORDER BY correspondant à l'ordre de l'index.
Ajoutez une autre condition à la clause WHERE :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 3 AND stringu1 = 'xxx';
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using tenk1_unique1 on tenk1 (cost=0.00..10.01 rows=1 width=244)
Index Cond: (unique1 < 3)
Filter: (stringu1 = 'xxx'::name)
La condition ajoutée stringu1 = 'xxx' réduit l'estimation du nombre de lignes en sortie mais pas le coût car nous devons toujours visiter le même ensemble de lignes. Notez que la clause stringu1 ne peut pas être appliqué à une condition d'index (car cet index est seulement sur la colonne unique1). À la place, il est appliqué comme un filtre sur les lignes récupérées par l'index. Du coup, le coût a un peu augmenté pour refléter cette vérification supplémentaire.
S'il existe des index sur plusieurs colonnes utilisées dans la condition WHERE, le planificateur pourrait choisir d'utiliser une combinaison AND ou OR des index :
EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND unique2 > 9000;
QUERY PLAN
-------------------------------------------------------------------------------------
Bitmap Heap Scan on tenk1 (cost=11.27..49.11 rows=11 width=244)
Recheck Cond: ((unique1 < 100) AND (unique2 > 9000))
-> BitmapAnd (cost=11.27..11.27 rows=11 width=0)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique2 (cost=0.00..8.65 rows=1042 width=0)
Index Cond: (unique2 > 9000)
Mais ceci requiert de visiter plusieurs index, donc ce n'est pas nécessaire un gain comparé à l'utilisation d'un seul index et au traitement de l'autre condition par un filtre. Si vous variez les échelles de valeurs impliquées, vous vous apercevrez que le plan change en accord.
Maintenant, essayons de joindre deux tables, en utilisant les colonnes dont nous avons discuté :
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
--------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244)
Index Cond: ("outer".unique2 = t2.unique2)
Dans cette jointure en boucle imbriquée, le parcours externe utilise le même parcours de bitmap que celui vu précédemment et donc son coût et le nombre de lignes sont les mêmes parce que nous appliquons la clause WHERE unique1 < 100 à ce nœud. La clause t1.unique2 = t2.unique2 n'a pas encore d'intérêt donc elle n'affecte pas le nombre de lignes du parcours externe. Pour le parcours interne, la valeur unique2 de la ligne courante du parcours externe est connectée dans le parcours d'index interne pour produire une condition d'index identique à t2.unique2 = constante. Donc, nous obtenons le même plan de parcours interne et les coûts que nous obtenons de, disons, EXPLAIN SELECT * FROM tenk2 WHERE unique2 = 42. Les coûts du nœud correspondant à la boucle sont ensuite initialisés sur la base du coût du parcours externe, avec une répétition du parcours interne pour chaque ligne externe (ici, 106 * 3.01), plus un petit temps CPU pour traiter la jointure.
Dans cet exemple, le nombre de lignes en sortie de la jointure est identique aux nombres de lignes des deux parcours mais ce n'est pas vrai en règle générale car vous pouvez avoir des clauses WHERE mentionnant les deux tables et qui, donc, peuvent seulement être appliquées au point de jointure, et non pas aux parcours d'index. Par exemple, si nous avions ajouté WHERE ... AND t1.hundred < t2.hundred, cela aurait diminué le nombre de lignes en sortie du nœud de jointure mais cela n'aurait pas changé les parcours d'index.
Une façon de rechercher des plans différents est de forcer le planificateur à oublier certaines stratégies qu'il aurait donné vainqueur en utilisant les options d'activation (enable)/désactivation (disable) décrites dans la Section 17.6.1, « Configuration de la méthode du planificateur » (c'est un outil complexe mais utile ; voir aussi la Section 13.3, « Contrôler le planificateur avec des clauses JOIN explicites »).
SET enable_nestloop = off;
EXPLAIN SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
------------------------------------------------------------------------------------------
Hash Join (cost=232.61..741.67 rows=106 width=488)
Hash Cond: ("outer".unique2 = "inner".unique2)
-> Seq Scan on tenk2 t2 (cost=0.00..458.00 rows=10000 width=244)
-> Hash (cost=232.35..232.35 rows=106 width=244)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0)
Index Cond: (unique1 < 100)
Ce plan propose d'extraire les 100 lignes intéressantes de tenk1 en utilisant le même parcours d'index, de les placer dans une table de hachage en mémoire puis de faire un parcours séquentiel de tenk2, en cherchant dans la table de hachage des correspondances possibles de la ligne t1.unique2 = t2.unique2 pour chaque tenk2. Le coût pour lire tenk1 et pour initialiser la table de hachage correspond au coût de lancement complet pour la jointure hachée car nous n'obtiendrons pas de lignes jusqu'à avoir lu tenk2. Le temps total estimé pour la jointure inclut aussi une charge importante du temps CPU pour requêter la table de hachage 10000 fois. Néanmoins, notez que nous ne chargeons pas 10000 fois 232,35 ; la configuration de la table de hachage n'est exécutée qu'une fois dans ce type de plan.
Il est possible de vérifier la précision des coûts estimés par le planificateur en utilisant EXPLAIN ANALYZE. Cette commande exécute réellement la requête puis affiche le vrai temps d'exécution accumulé par chaque nœud du plan, avec les mêmes coûts estimés que ceux affichés par un simple EXPLAIN. Par exemple, nous pourrions obtenir un résultat comme celui-ci :
EXPLAIN ANALYZE SELECT * FROM tenk1 t1, tenk2 t2 WHERE t1.unique1 < 100 AND t1.unique2 = t2.unique2;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=2.37..553.11 rows=106 width=488) (actual time=1.392..12.700 rows=100 loops=1)
-> Bitmap Heap Scan on tenk1 t1 (cost=2.37..232.35 rows=106 width=244) (actual time=0.878..2.367 rows=100 loops=1)
Recheck Cond: (unique1 < 100)
-> Bitmap Index Scan on tenk1_unique1 (cost=0.00..2.37 rows=106 width=0) (actual time=0.546..0.546 rows=100 loops=1)
Index Cond: (unique1 < 100)
-> Index Scan using tenk2_unique2 on tenk2 t2 (cost=0.00..3.01 rows=1 width=244) (actual time=0.067..0.078 rows=1 loops=100)
Index Cond: ("outer".unique2 = t2.unique2)
Total runtime: 14.452 ms
Notez que les valeurs « temps réel » sont en millisecondes alors que les estimations de « coût » sont exprimées dans des unités arbitraires ; donc il y a peu de chances qu'elles correspondent. L'important est de vérifier si les ratios temps réel et coûts estimés correspondent.
Dans certains plans de requête, il est possible qu'un nœud de sous-plan soit exécuté plus d'une fois. Par exemple, le parcours d'index interne est exécuté une fois par ligne externe dans le plan de boucle imbriquée ci-dessus. Dans de tels cas, la valeur « loops » renvoie le nombre total d'exécution du nœud, et le temps réel et les valeurs des lignes affichées sont une moyenne par exécution. Ceci est fait pour que les nombres soient comparables avec la façon dont les estimations de coûts sont affichées. Multipliez par la valeur de « loops » pour obtenir le temps total réellement passé dans le nœud.
Le Total runtime (temps total d'exécution) affiché par EXPLAIN ANALYZE inclut les temps de lancement et d'arrêt de l'exécuteur ainsi que le temps passé lors du traitement des lignes de résultat. Il n'inclut pas le temps passé pour l'analyse, la réécriture ou la planification. Pour une requête SELECT, le temps total d'exécution sera juste un peu plus important que le temps total indiqué par le noœud du plan de haut niveau. Pour les commandes INSERT, UPDATE et DELETE, le temps total d'exécution pourrait être considérablement plus important parce qu'il inclut le temps passé au traitement des lignes de résultat. Dans ces commandes, le temps pour le nœud du plan principal est essentiellement le temps passé à calculer les nouvelles lignes et/ou l'emplacement des anciennes mais il n'inclut pas le temps passé à faire des modifications. Le temps passé à lancer les déclencheurs, s'il y en a, est aussi en dehors du nœud du plan principal et est affiché séparément pour chaque déclencheur.
Il est bon de noter que les résultats de EXPLAIN ne devraient pas être extrapolés pour des situations autres que celles de vos tests en cours ; par exemple, les résultats sur une petite table ne peuvent être appliqués à des tables bien plus importantes. Les estimations de coût du planificateur ne sont pas linéaires et, du coup, il pourrait bien choisir un plan différent pour une table plus petite ou plus grande. Un exemple extrême est celui d'une table occupant une page disque. Vous obtiendrez pratiquement toujours un parcours séquentiel que des index soient disponibles ou non. Le planificateur réalise que cela va nécessiter la lecture d'une seule page disque pour traiter la table dans ce cas, il n'y a donc pas d'intérêt à étendre des lectures de pages supplémentaires pour un index.
[7] Les exemples dans cette section sont récupérés de la base de données des tests de régression après avoir lancé un VACUUM ANALYZE, en utilisant les sources de la version 8.2. Vous devriez être capable d'obtenir des résultats similaires si vous essayez vous-même les exemples mais vos coûts estimés et les nombres de lignes varieront probablement légèrement car les statistiques d'ANALYZE se font à partir de valeurs prises au hasard.