ANALYZE — Collecter les statistiques d'une base de données
ANALYZE [ (option
[, ...] ) ] [table_et_colonnes
[, ...] ] ANALYZE [ VERBOSE ] [table_et_colonnes
[, ...] ] oùoption
peut valoir : VERBOSE [booléen
] SKIP_LOCKED [booléen
] BUFFER_USAGE_LIMITtaille
ettable_et_colonnes
est :nom_table
[ (nom_colonne
[, ...] ) ]
ANALYZE
collecte des statistiques sur le contenu des
tables de la base de données et stocke les résultats dans le catalogue
système pg_statistic
.
L'optimiseur de requêtes les utilise pour déterminer les plans d'exécution
les plus efficaces.
Sans une liste de table_et_colonnes
, ANALYZE
examine chaque table et vue matérialisée de la base de données courante
lisible par l'utilisateur courant. Avec cette liste,
ANALYZE
n'examine que les tables de cette liste. Il est
également possible de donner une liste de noms de colonnes pour une table,
auquel cas seules les statistiques concernant ces colonnes sont collectées.
Quand la liste d'options est entourée de parenthèses, les options peuvent être écrites dans n'importe quel ordre. La syntaxe avec parenthèses a été introduite dans la version 11 de PostgreSQL ; la syntaxe sans parenthèses devient obsolète.
VERBOSE
L'affichage de messages de progression est activé.
SKIP_LOCKED
Précise qu'une commande ANALYZE
, quand elle commence à
travailler sur une relation, ne doit pas attendre la libération de verrous
en conflit :si une relation ne peut être verrouillée immédiatement et
sans attente, la relation est ignorée. Notez que même avec cette option,
ANALYZE
peut se retrouver bloqué en ouvrant les index
d'une relation, ou en récupérant des échantillons de lignes de partitions,
de tables héritant d'une autre, et de certains types de tables étrangères.
Notez aussi que ANALYZE
traite habituellement toutes
les partitions des tables partitionnées demandées, mais il ignorera toutes
les partitions s'il y a un verrou en conflit sur la table partitionnée.
BUFFER_USAGE_LIMIT
Précise la taille du cache
(Buffer Access Strategy)
utilisé par la commande ANALYZE
. Cette taille est
utilisée pour calculer le nombre de buffers du cache disque de PostgreSQL
qui seront utilisés dans le cadre de cette stratégie. 0
désactive l'utilisation d'une stratégie d'accès aux
buffers
. Quand cette option n'est pas indiquée,
ANALYZE
utilise la valeur provenant de
vacuum_buffer_usage_limit. Des configurations plus
hautes peuvent permettre d'exécuter ANALYZE
plus
rapidement mais une configuration trop élevée pourraient causer l'éviction
d'un trop grand nombre de blocs utiles. La valeur minimale est
128 ko
et la valeur maximale est
16 Go
.
booléen
Indique si l'option sélectionnée doit être activée ou désactivée. Vous
pouvez écrire TRUE
, ON
ou
1
pour activer l'option, et FALSE
,
OFF
ou 0
pour la désactiver. La
valeur booléen
peut aussi
être omise, auquel cas TRUE
est supposé.
taille
Indique une quantité de mémoire en ko. Les tailles peuvent être
indiquées sous la forme d'une chaîne de caractères contenant la taille
numérique suivie d'une des unités de mémoire suivantes :
B
(octets), kB
(kilo-octets),
MB
(méga-octets), GB
(giga-octets)
ou TB
(tera-octets).
nom_table
Le nom (éventuellement qualifié du nom du schéma) de la table à analyser. S'il n'est pas spécifié, toutes les tables standards, les tables partitionnées et les vues matérialisées dans la base de données courante sont analysées (mais pas les tables distantes). Si la table spécifiée est une table partitionnée, les statistiques héritées de la table partitionnée dans son ensemble ainsi que les statistiques des partitions individuelles sont mises à jour.
nom_colonne
Le nom d'une colonne à analyser. Par défaut, toutes les colonnes le sont.
Quand VERBOSE
est spécifié, ANALYZE
affiche des messages de progression pour indiquer la table en cours de
traitement. Diverses statistiques sur les tables sont aussi affichées.
Pour analyser une table, l'utilisateur doit être le propriétaire de la table
ou un superutilisateur. Néanmoins, les propriétaires des bases ont le droit
d'analyser toutes les tables situées dans leur bases, sauf les catalogues
partagés. (La restriction pour les catalogues partagées signifie qu'un
ANALYZE
sur une base complète peut seulement être réalisé
par un superutilisateur.) ANALYZE
ignorera toutes les
tables pour lesquelles l'utilisateur n'a pas le droit d'analyse.
Les tables distantes sont analysées seulement lorsqu'elles sont explicitement
ciblées. Certains wrappers de données distantes ne supportent pas encore
ANALYZE
. Si le wrapper de la table distante ne supporte
pas ANALYZE
, la commande affiche un message
d'avertissement et ne fait rien de plus.
Dans la configuration par défaut de PostgreSQL,
le démon autovacuum (voir Section 25.1.6) s'occupe de l'analyse
automatique des tables lorsqu'elles sont chargées pour la première fois avec
des données, puis à chaque fois qu'elles sont modifiées via les opérations
habituelles. Quand l'autovacuum est désactivé,
il est intéressant de lancer ANALYZE
périodiquement ou
juste après avoir effectué de grosses modifications sur le contenu d'une table.
Des statistiques à jour aident l'optimiseur à choisir le plan de
requête le plus approprié et améliorent ainsi la vitesse du traitement
des requêtes. Une stratégie habituelle pour les bases de données principalement
en lecture consiste à lancer VACUUM
et
ANALYZE
une fois par jour, au moment où le serveur est le
moins sollicité.
(Cela ne sera pas suffisant en cas de grosse activité en mise à jour.)
ANALYZE
ne requiert qu'un verrou en lecture sur la table
cible. Il peut donc être lancé en parallèle à d'autres activités sur la
table.
Les statistiques récupérées par ANALYZE
incluent
habituellement une liste des quelques valeurs les plus communes dans chaque
colonne et un histogramme affichant une distribution approximative des
données dans chaque colonne. L'un ou les deux peuvent être omis si
ANALYZE
les juge inintéressants (par exemple, dans une
colonne à clé unique, il n'y a pas de valeurs communes) ou si le type de
données de la colonne ne supporte pas les opérateurs appropriés. Il y a plus
d'informations sur les statistiques dans le Chapitre 25.
Pour les grosses tables, ANALYZE
prend aléatoirement
plusieurs lignes de la table, au hasard, plutôt que d'examiner chaque ligne.
Ceci permet à des tables très larges d'être examinées rapidement. Néanmoins,
les statistiques ne sont qu'approximatives et changent légèrement à chaque
fois qu'ANALYZE
est lancé, même si le contenu réel de la
table n'a pas changé. Cela peut résulter en de petites modifications dans les
coûts estimés par l'optimiseur affichés par EXPLAIN
. Dans de rares
situations, ce non-déterminisme entraîne le choix par l'optimiseur d'un plan
de requête différent entre deux lancements d'ANALYZE
. Afin
d'éviter cela, le nombre de statistiques récupérées par
ANALYZE
peut être augmenté, comme cela est décrit
ci-dessous.
L'étendue de l'analyse est contrôlée par l'ajustement de la variable de
configuration default_statistics_target ou colonne par
colonne en initialisant la cible des statistiques par colonne avec ALTER TABLE ... ALTER COLUMN ... SET
STATISTICS
. Cette valeur cible initialise le nombre maximum
d'entrées dans la liste des valeurs les plus communes et le nombre maximum de
points dans l'histogramme. La valeur cible par défaut est fixée à 100 mais
elle peut être ajustée vers le haut ou vers le bas afin d'obtenir un bon
compromis entre la précision des estimations de l'optimiseur, le temps pris
par ANALYZE
et l'espace total occupé dans
pg_statistic
. En particulier, initialiser la cible des
statistiques à zéro désactive la collecte de statistiques pour cette colonne.
Cela peut s'avérer utile pour les colonnes qui ne sont jamais utilisées dans
les clauses WHERE
, GROUP BY
ou
ORDER BY
des requêtes puisque l'optimiseur ne fait aucune
utilisation des statistiques de ces colonnes.
La plus grande cible de statistiques parmi les colonnes en cours d'analyse
détermine le nombre de lignes testées pour préparer les statistiques de la
table. Augmenter cette cible implique une augmentation proportionnelle du
temps et de l'espace nécessaires à l'exécution d'ANALYZE
.
Une des valeurs estimées par ANALYZE
est le nombre de
valeurs distinctes qui apparaissent dans chaque colonne. Comme seul un
sous-ensemble des lignes est examiné, cette estimation peut parfois être
assez inexacte, même avec la cible statistique la plus large possible. Si
cette inexactitude amène de mauvais plans de requêtes, une valeur plus
précise peut être déterminée manuellement, puis configurée avec ALTER TABLE ... ALTER COLUMN ... SET
(n_distinct = ...)
.
Si la table en cours d'analyse a des enfants en héritage,
ANALYZE
récupère deux ensembles de statistiques : un
sur les lignes de la table parent seule et un autre incluant les lignes du
parent et de tous les enfants. Le deuxième ensemble de statistiques est
nécessaire lors de l'optimisation de requêtes traitant l'arbre de tables
comme un ensemble. Les enfants eux-même ne sont pas analysés dans ce cas.
Néanmoins, le démon autovacuum ne prendra en
considération que les insertions et les mises à jour sur la table parent
elle-même pour décider de déclencher une analyse automatique sur cette table.
Si cette table a rarement des insertions ou des mises à jour, les
statistiques sur l'arbre d'héritage complet ne seront pas à jour tant que
vous n'exécuterez pas ANALYZE
manuellement.
Pour les tables partitionnées, ANALYZE
récupère les
statistiques en échantillonnant les lignes à partir de toutes les
partitions ; de plus, il va parcourir chaque partition récursivement et
mettre à jour ses statistiques. Chaque partition feuille est analysée
seulement une fois, y compris dans le cas d'un partitionnement à plusieurs
niveaux. Aucune statistique n'est récupérée pour la table parent seule (sans
les données de ces partitions), parce qu'avec le partitionnement, elle est
garantie d'être vide.
Le démon autovacuum ne traite pas les tables partitionnées, pas plus qu'il ne
traite les parents en héritage si seules les tables filles sont modifiées.
Il est généralement nécessaire d'exécuter périodiquement un
ANALYZE
manuel pour conserver des statistiques à jour sur
la hiérarchie de tables.
Si certaines tables filles ou partitions sont des tables externes dont les
wrappers de données externes ne supportent pas ANALYZE
,
ces tables sont ignorées lors de la récupération de statistiques pour
l'héritage.
Si la table en cours d'analyse est entièrement vide,
ANALYZE
n'enregistrera pas les nouvelles statistiques pour
cette table. Toutes les statistiques existantes seront conservées.
Chaque processus exécutant ANALYZE
indiquera sa
progression dans la vue pg_stat_progress_analyze
.
Voir Section 28.4.1 pour les détails.
Il n'existe pas d'instruction ANALYZE
dans le standard
SQL.