La performance des requêtes peut être affectée par un grand nombre d'éléments. Certains peuvent être contrôlé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 choisir les bons plans. Vous pouvez utiliser la commande EXPLAIN(7) 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 pour réaliser ces opérations. Encore une fois, il existe plus d'une façon 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. [10]
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 (de gauche à droite) :
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 18.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éel 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 valeur rows 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. Le coût estimé est calculé avec (nombre de pages lues * seq_page_cost) + (lignes parcourues * cpu_tuple_cost). Par défaut, seq_page_cost vaut 1.0 et cpu_tuple_cost vaut 0.01. Donc le coût estimé est de (358 * 1.0) + (10000 * 0.01), soit 458.
Maintenant, modifions la requête originale 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é (par 10000 * cpu_operator_cost pour être exact) dans le but de 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 rows 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 référencé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: (t2.unique2 = t1.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 trouvé moins coûteuses en utilisant les options d'activation (enable)/désactivation (disable) décrites dans la Section 18.6.1, « Configuration de la méthode du planificateur » (c'est un outil complexe mais utile ; voir aussi la Section 14.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: (t2.unique2 = t1.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: (t2.unique2 = t1.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, mais pas le temps passé pour l'analyse, la réécriture ou la planification. Pour les commandes INSERT, UPDATE et DELETE, le temps passé à appliquer les modifications est comptabilisé sur le nœud de haut-niveau Insert, Update ou Delete. (Les nœuds du plan sous ce nœud représentent le travail de recherche des anciennes lignes et/ou de calcul des anciennes.) Le temps passé à exécuter les triggers BEFORE (s'ils sont présents) est comptabilisé dans le nœud relatif (Insert, Update ou Delete). Par contre, ce n'est pas le cas pour les triggers AFTER. Le temps passé dans chaque trigger (BEFORE ou AFTER) est aussi affiché séparément et est inclus dans le temps d'exécution total. Notez cependant que les triggers de contraintes différés ne seront pas exécutés avant la fin de la transaction. Du coup, ils ne sont pas affichés par EXPLAIN ANALYZE.
Il existe deux raisons importantes pour lesquelles les temps d'exécution mesurés par EXPLAIN ANALYZE peuvent dévier de l'exécution normale de la même requête. Tout d'abord, comme aucune ligne n'est réellement envoyée au client, les coûts de transmission réseau et les coûts de formatage des entrées/sorties ne sont pas inclus. Ensuite, la surcharge généré par la commande EXPLAIN ANALYZE peut être importante, tout spécialement sur les machines dont les appels à la fonction gettimeofday() sont particulièrement lents.
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.
[10] 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.