17.6. Planification des requêtes
17.6.1. Configuration de la méthode du planificateur
Ces paramètres de configuration fournissent une méthode brutale
pour influencer les plans de requête choisis par l'optimiseur de
requêtes. Si le plan choisi par défaut par l'optimiseur pour une
requête particulière n'est pas optimal, une solution temporaire
peut provenir de l'utilisation de l'un de ces paramètres de
configuration pour forcer l'optimiseur à choisir un plan différent.
Désactiver un de ces paramètres de façon permanente n'est toutefois
pas une bonne idée. De meilleures façons d'améliorer la qualité des
plans choisis par l'optimiseur passent par l'ajustement de
Constantes de
coût du planificateur , le lancement plus fréquent de ANALYZE,
l'augmentation de la valeur du paramètre de configuration default_statistics_target
et l'augmentation du nombre de statistiques récupérées pour des
colonnes spécifiques en utilisant
ALTER TABLE SET STATISTICS
.
-
enable_bitmapscan (boolean)
-
Active ou désactive l'utilisation des plans de parcours de
bitmap par le planificateur de requêtes. Activé par défaut
(on).
-
enable_hashagg (boolean)
-
Active ou désactive l'utilisation des plan d'agrégation
hachée par le planificateur. Activé par défaut (on).
-
enable_hashjoin (boolean)
-
Active ou désactive l'utilisation des jointures de hachage
par le planificateur. Activé par défaut (on).
-
enable_indexscan (boolean)
-
Active ou désactive l'utilisation des parcours d'index par le
planificateur. Activé par défaut (on).
-
enable_mergejoin (boolean)
-
Active ou désactive l'utilisation des jointures de fusion par
le planificateur. Activé par défaut (on).
-
enable_nestloop (boolean)
-
Active ou désactive l'utilisation des jointures de boucles
imbriquées par le planificateur. Il n'est pas possible de
supprimer complètement les jointures de boucles imbriquées
mais la désactivation de cette variable décourage le
planificateur d'en utiliser une si d'autres méthodes sont
disponibles. Activé par défaut (on).
-
enable_seqscan (boolean)
-
Active ou désactive l'utilisation des parcours séquentiels
par le planificateur. Il n'est pas possible de supprimer
complètement les parcours séquentiels mais la désactivation
de cette variable décourage le planificateur d'n utiliser un
si d'autres méthodes sont disponibles. Activé par défaut
(on).
-
enable_sort (boolean)
-
Active ou désactive l'utilisation des étapes de tri explicite
par le planificateur. Il n'est pas possible de supprimer
complètement ces tris mais la désactivation de cette variable
décourage le planificateur d'en utiliser un si d'autres
méthodes sont disponibles. Activé par défaut (on).
-
enable_tidscan (boolean)
-
Active ou désactive l'utilisation des parcours de
TID par le planificateur.
Activé par défaut (on).
17.6.2. Constantes de coût du planificateur
Les variables de coût décrites dans
cette section sont mesurées sur une échelle arbitraire. Seules
leurs valeurs relatives ont un intérêt. De ce fait, augmenter ou
diminuer leurs valeurs d'un même facteur n'occasione aucun
changement dans les choix du planificateur. Traditionnellement, ces
variables sont indexées sur le coût de récupération séquentiel
d'une page ; c'est-à-dire que seq_page_cost est, par convention, positionné à
1.0 et les autres variables de coût sont
configurées relativement à cette référence. Il est toutefois
possible d'utiliser une autre échelle, comme les temps d'exécution
réels en millisecondes sur une machine particulière.
![[Note]](images/note.png)
Note
Il n'existe malheureuresement pas de méthode bien définie pour
déterminer les valeurs idéales des variables de coût. Il est
préférable de les considérer comme moyennes sur un jeu complet
de requêtes d'une installation particulière. Cela signifie que
modifier ces paramètres sur la seule base de quelques
expériences est très risqué.
-
seq_page_cost (floating
point)
-
Initialise l'estimation faite par le planificateur du coût de
récupération d'une page disque incluse dans une série de
récupérations séquentielles. La valeur par défaut est 1.0.
-
random_page_cost (floating
point)
-
Initialise l'estimation faite par le planificateur du coût de
récupération non-séquentielle d'une page disque. Mesurée
comme un multiple du coût de récupération d'une page
séquentielle, sa valeur par défaut est 4.0. Réduire cette
valeur par rapport à seq_page_cost
incite le système à privilégier les parcours d'index ;
l'augmenter donne l'impression de parcours d'index plus
coûteux. Les deux valeurs peuvent être augmentée ou diminuée
concomitament pour modifier l'importance des coûts
d'entrées/sorties disque par rapport aux coûts CPU, décrits
par les paramètres qui suivent.
![[Astuce]](images/tip.png)
Astuce
Bien que le système permette de configurer random_page_cost à une valeur inférieure à
celle de seq_page_cost, cela n'a
aucun intérêt. En revanche, les configurer à des valeurs
identiques prend tout son sens si la base tient
entièrement dans le cache en RAM. En effet, dans ce cas
il n'est pas pénalisant d'atteindre des pages qui ne se
suivent pas. De plus, dans une base presque entièrement
dans le cache, ces valeurs peuvent être abaisser
relativement aux paramètres CPU car le coût de
récupération d'une page déjà en RAM est bien moindre à
celui de sa récupération sur disque.
-
cpu_tuple_cost (floating
point)
-
Initialise l'estimation faite par le planificateur du coût de
traitement de chaque ligne lors d'une requête. La valeur par
défaut est 0.01.
-
cpu_index_tuple_cost (floating point)
-
Initialise l'estimation faite par le planificateur du coût de
traitement de chaque entrée de l'index lors d'un parcours
d'index. La valeur par défaut est 0.005.
-
cpu_operator_cost (floating
point)
-
Initialise l'estimation faite par le planificateur du coût de
traitement de chaque opérateur ou fonction exécutée dans une
requête. La valeur par défaut est 0.0025.
-
effective_cache_size (integer)
-
Initialise l'estimation faite par le planificateur de la
taille réelle du cache disque disponible pour une requête. Ce
paramètre est lié à l'estimation du coût d'utilisation d'un
index ; une valeur importante favorise les parcours d'index,
une valeur faible les parcours séquentiels. Pour configurer
ce parmètre, il est important de considérer à la fois les
tampons partagés de PostgreSQL™ et la portion de cache
disque du noyau utilisée pour les fichiers de données de
PostgreSQL™. Il faut
également tenir compte du nombre attendu de requêtes
concurrentes sur des tables différentes car elles partagent
l'espace disponible. Ce paramètre n'a pas d'inluence sur la
taille de la mémoire partagée allouée par PostgreSQL™, et ne réserve pas non
plus le cache disque du noyau ; il n'a qu'un rôle estimatif.
La valeur par défaut est de 128 Mo.
17.6.3. Optimiseur génétique de requêtes
-
geqo (boolean)
-
Active ou désactive l'optimisation génétique des requêtes,
algorithme de planification de requêtes sans recherche
exhaustive. Activé par défaut. La variable geqo_threshold fournit un moyen plus
granulaire de désactiver le GEQO pour certaines classes de
requêtes.
-
geqo_threshold (integer)
-
L'optimisation génétique des requêtes est utilisée pour
planifier les requêtes si au minimum ce nombre d'éléments est
impliqué dans la clause FROM (une
construction FULL OUTER JOIN ne
compte que pour un élément du FROM).
La valeur par défaut est 12. Il est généralement préférable
d'utiliser le planificateur déterministe, exhaustif, pour les
requêtes plus simples, mais pour les requêtes impliquant
autant de tables, celui-ci prend trop de temps.
-
geqo_effort (integer)
-
Contrôle le compromis entre le temps de planification et
l'efficacité du plan de requête dans GEQO. Cette variable est
un entier entre 1 et 10. La valeur par défaut est de cinq.
Des valeurs plus importantes augmentent le temps passé à la
planification de la requête mais aussi la probabilité qu'un
plan de requête efficace soit choisi.
geqo_effort n'a pas d'action directe
; il est simplement utilisé pour calculer les valeurs par
défaut des autres variables influençant le comportement de
GEQO (décrites ci-dessous). Il est également possible de les
configurer manuellement.
-
geqo_pool_size (integer)
-
Contrôle la taille de l'ensemble utilisé par GEQO.
C'est-à-dire le nombre d'individus au sein d'une population
génétique. Elle doit être au minimum égale à deux, les
valeurs utiles étant généralement comprises entre 100 et
1000. Si elle est configurée à zéro (valeur par défaut),
alors une valeur convenable est choisie en fonction de
geqo_effort et du nombre de tables
dans la requête.
-
geqo_generations (integer)
-
Contrôle le nombre de générations utilisées par GEQO.
C'est-à-dire le nombre d'itérations de l'algorithme. Il doit
être au minimum de un, les valeurs utiles se situent dans la
même plage que la taille de l'ensemble. S'il est configuré à
zéro (valeur par défaut), alors une valeur convenable est
choisie en fonction de geqo_pool_size.
-
geqo_selection_bias (floating
point)
-
Contrôle le biais de sélection utilisé par GEQO. C'est-à-dire
la pression de sélectivité au sein de la population. Les
valeurs s'étendent de 1.50 à 2.00 (valeur par défaut).
17.6.4. Autres options du planificateur
-
default_statistics_target
(integer)
-
Initialise la cible de statistiques par défaut pour les
colonnes de table qui n'ont pas une cible de colonne
spécifique configurée via
ALTER
TABLE SET STATISTICS
. Des valeurs élevées
accroissent le temps nécessaire à l'exécution d'
ANALYZE
mais peuvent
permettre d'améliorer la qualité des estimations du
planificateur. La valeur par défaut est 10. Pour plus
d'informations sur l'utilisation des statistiques par le
planificateur de requêtes, se référer à la Section 13.2,
« Statistiques utilisées par le
planificateur ».
-
constraint_exclusion (boolean)
-
Active ou désactive l'utilisation par le planificateur des
contraintes de table pour optimiser les requêtes. Désactivé
par défaut (off).
Si ce paramètre est activé (on), le
planificateur compare les conditions de la requête avec les
contraintes CHECK de la table et se
dispense de parcourir les tables dont les conditions sont en
contradiction avec les contraintes. Par exemple :
CREATE TABLE parent(clef integer, ...);
CREATE TABLE fils1000(check (clef between 1000 and 1999)) INHERITS(parent);
CREATE TABLE fils2000(check (clef between 2000 and 2999)) INHERITS(parent);
...
SELECT * FROM parent WHERE key = 2400;
Avec l'activation de l'exclusion de contrainte, ce
SELECT
ne
parcourt pas fils1000. Cela
peut améliorer les performances lorsque l'héritage est
utilisé pour construire des tables partitionnées.
Actuellement, constraint_exclusion
est désactivé par défaut (off) car
il existe un risque de résultats incorrects si les plans de
requêtes sont en cache -- si une contrainte de table est
modifiée ou supprimée, le plan précédemment engendré peut
s'évérer désormais faux ; il n'existe pas de mécanisme
intégré pour forcer une nouvelle planification (ce problème
sera probablement résolu dans une prochaine version de
PostgreSQL™). Une
autre raison de le conserver désactivé est le coût
relativement élevé des vérifications de contraintes, qui,
dans de nombreux cas, ne font pas gagner de temps. Il est
recommandé de ne l'activer que si des tables partitionées
conçues pour tirer parti de cette fonctionnalité sont
utilisées.
Se référer à la Section 5.9,
« Partitionnement » pour plus d'informations
sur l'utilisation des exclusion de contraintes et du
partitionement.
-
from_collapse_limit (integer)
-
Le planificateur assemble les sous-requêtes dans des requêtes
supérieures si la liste FROM
résultante contient au plus ce nombre d'éléments. Des valeurs
faibles réduisent le temps de planification mais conduisent à
des plans de requêtes inférieurs. La valeur par défaut est de
huit. Il est généralement conseillé de conserver cette valeur
inférieure à geqo_threshold.
Pour plus d'informations, voir
Section 13.3, « Contrôler le planificateur avec des
clauses JOIN explicites ».
-
join_collapse_limit (integer)
-
Le planificateur réécrit les constructions JOIN explicites (à l'exception de FULL JOIN) en une liste d'éléments FROM à chaque fois qu'il n'en résulte qu'une
liste ne contenant pas plus de ce nombre d'éléments. Des
valeurs faibles réduisent le temps de planification mais
conduisent à des plans de requêtes inférieurs.
Par défaut, cette variable a la même valeur que from_collapse_limit, valeur qui convient à la
plupart des utilisations. Configurer cette variable à 1
empêche le réordonnancement des JOINtures explicites. De ce fait, l'ordre des
jointures explicites indiqué dans la requête est l'ordre réel
dans lequel les relations sont jointes. Le planificateur de
la requête ne choisit pas toujours l'ordre de jointure
optimal ; les utilisateurs aguerris peuvent choisir
d'initialiser temporairement cette variable à 1 et d'indiquer
explicitement l'ordre de jointure souhaité. Pour plus
d'informations, voir
Section 13.3, « Contrôler le planificateur avec des
clauses JOIN explicites ».
|
Write Ahead Log
