PostgreSQLLa base de données la plus sophistiquée au monde.
Documentation PostgreSQL 16.2 » Référence » Commandes SQL » CREATE STATISTICS

CREATE STATISTICS

CREATE STATISTICS — définit des statistiques étendues

Synopsis

CREATE STATISTICS [ [ IF NOT EXISTS ] nom_statistique ]
    ON ( expression )
    FROM nom_table

CREATE STATISTICS [ [ IF NOT EXISTS ] nom_statistique ]
    [ ( type_statistique [, ... ] ) ]
    ON nom_colonne, nom_colonne [, ...]
    FROM nom_table
  

Description

CREATE STATISTICS créera un nouvel objet de suivi des statistiques étendues sur les données de la table, table distante ou vue matérialisée spécifiée. L'objet statistiques sera créé dans la base de données courante et son propriétaire sera l'utilisateur exécutant la commande.

La commande CREATE STATISTICS a deux formes basiques. La première forme autorise des statistiques à une variable pour une simple expression à récupérer, fournissant des bénéfices similaires à un index avec expression sans le surcoût de maintenance de l'index. Cette forme ne permet pas d'indiquer le type de statistiques car les différents types de statistiques sont uniquement pour les statistiques avec au moins deux variables. La seconde forme de la commande permet la récupération de statistiques sur plusieurs colonnes et/ou expressions, en indiquant optionnellement les types de statistiques à inclure. Cette forme causera aussi la récupération de statistiques à une variable sur toute expression incluse dans la liste.

Si un nom de schéma est donné (par exemple, CREATE STATISTICS monschema.mastat ...) alors l'objet statistiques est crée dans le schéma spécifié. Autrement, il sera crée dans le schéma courant. Si indiqué, le nom de l'objet statistiques doit être différent du nom de tous les autres objets statistiques dans le même schéma.

Paramètres

IF NOT EXISTS

Ne renvoie pas d'erreur si un objet statistiques de même nom existe déjà. Une note est affichée dans ce cas. Veuiller noter que seul le nom de l'objet statistiques est pris en compte ici, et non pas le détail de sa définition. Le nom de la statistique est requis quand IF NOT EXISTS est précisé.

nom_statistiques

Le nom (éventuellement qualifié du nom du schéma) de l'objet statistiques devant être créé. Si le nom est omis, PostgreSQL choisit un nom convenable basé sur le nom de la table parent et sur les noms des colonnes ou expressions.

type_statistique

Un type de statistique multivarié devant être calculé dans cet objet statistiques. Les types actuellement supportés sont ndistinct, qui active des statistiques n-distinct, dependencies qui active des statistiques de dépendances fonctionnelles, et mcv qui active les listes des valeurs les plus fréquentes. Si cette clause est omise, tous les types statistiques supportés sont inclus dans l'objet statistique. Les statistiques d'expression sur une variable sont construits automatiquement si la définition des statistiques inclue des expressions complexes plutôt que de simples références de colonnes. Pour plus d'informations, voir Section 14.2.2 et Section 76.2.

nom_colonne

Le nom d'une colonne de la table devant être couverte par les statistiques calculées. Ceci est seulement autorisé pour construire des statistiques sur plusieurs variables. Au moins deux noms de colonne ou expressions doivent être indiqués, mais leur ordre n'est pas significatif.

expression

Une expression couverte par les statistiques calculées. Ceci pourrait être utilisé pour construire des statistiques univariées sur une seule expression, ou faire partie d'une liste de plusieurs noms de colonnes et/ou expressions pour construire des statistiques multivariées. Dans ce dernier cas, les statistiques univariées séparées sont construites automatiquement pour chaque expression de la liste.

nom_table

Le nom (éventuellement qualifié du nom du schéma) de la table contenant le(s) colonne(s) sur lesquelles les statistiques sont calculées ; voir ANALYZE pour une explication de la gestion de l'héritage et des partitions.

Notes

Vous devez être le propriétaire de la table pour créer un objet statistiques lisant ses données. Une fois celui-ci créé, le propriétaire de l'objet statistiques est indépendant de la ou les tables sous-jacentes.

Les statistiques d'expression sont par expression et sont similaires à créer un index sur l'expression, sauf qu'elles évitent le surcoût de maintenance de l'index. Les statistiques d'expression sont construites automatiquement pour chaque expression dans la définition de l'objet statistique.

Exemples

Créer une table t1 avec deux colonnes fonctionnellement dépendantes, c'est-à-dire que la connaissance de la valeur de la première colonne est suffisante pour déterminer la valeur de l'autre colonne. Ensuite des statistiques de dépendances fonctionnelles sont construites sur ces colonnes :

CREATE TABLE t1 (
    a   int,
    b   int
);

INSERT INTO t1 SELECT i/100, i/500
                 FROM generate_series(1,1000000) s(i);

ANALYZE t1;

-- le nombre de lignes correspondantes sera drastiquement sous-estimé :
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);

CREATE STATISTICS s1 (dependencies) ON a, b FROM t1;

ANALYZE t1;

-- à présent le nombre de ligne estimé est plus précis :
EXPLAIN ANALYZE SELECT * FROM t1 WHERE (a = 1) AND (b = 0);
   

Sans statistiques fonctionnellement dépendantes, l'optimisateur supposera que les deux conditions WHERE sont indépendantes, et multiplierait leurs sélectivités pour arriver à une estimation du nombre de lignes bien trop basse. Avec de telles statistiques, l'optimiseur reconnaît que les conditions WHERE sont redondantes et ne sous-estime plus le nombre de lignes.

Créer une table t2 avec deux colonnes parfaitement corrélées (contenant des données identiques), et une liste MCV sur ces colonnes :

CREATE TABLE t2 (
    a   int,
    b   int
);

INSERT INTO t2 SELECT mod(i,100), mod(i,100)
                 FROM generate_series(1,1000000) s(i);

CREATE STATISTICS s2 (mcv) ON a, b FROM t2;

ANALYZE t2;

-- valid combination (found in MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 1);

-- invalid combination (not found in MCV)
EXPLAIN ANALYZE SELECT * FROM t2 WHERE (a = 1) AND (b = 2);
   

La liste MCV donne au planificateur des informations plus détaillées à propos des valeurs spécifiques qui apparaissent le plus fréquemment dans la table, de même qu'une borne supérieure sur les sélectivités des combinaisons de valeurs qui n'apparaissent pas dans la table, lui permettant de générer de meilleures estimations dans les deux cas.

Créer une table t3 avec une seule colonne de type timestamp, et exécuter des requêtes en utilisant des expressions sur cette colonne. Sans les statistiques étendues, le planificateur n'a pas d'information sur la distribution des données pour les expressions, et utilise les estimations par défaut. Le planificateyr ne réalise pas non plus que la valeur de la date tronquée au mois est complètement déterminée par la valeur de la date tronquée au jour. De ce fait, des statistiques sur l'expression et sur ndistinct sont construites sur ces deux expressions :

CREATE TABLE t3 (
    a   timestamp
);

INSERT INTO t3 SELECT i FROM generate_series('2020-01-01'::timestamp,
                                             '2020-12-31'::timestamp,
                                             '1 minute'::interval) s(i);

ANALYZE t3;

-- le nombre de lignes correspondantes va être fortement sous-estimé :
EXPLAIN ANALYZE SELECT * FROM t3
  WHERE date_trunc('month', a) = '2020-01-01'::timestamp;

EXPLAIN ANALYZE SELECT * FROM t3
  WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
                                 AND '2020-06-30'::timestamp;

EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
   FROM t3 GROUP BY 1, 2;

-- construction de statistiques ndistinct sur la paire d'expressions (des statistiques
-- par expression sont construites automatiquement)
CREATE STATISTICS s3 (ndistinct) ON date_trunc('month', a), date_trunc('day', a) FROM t3;

ANALYZE t3;

-- maintenant les estimations de nombre de lignes sont plus précises :
EXPLAIN ANALYZE SELECT * FROM t3
  WHERE date_trunc('month', a) = '2020-01-01'::timestamp;

EXPLAIN ANALYZE SELECT * FROM t3
  WHERE date_trunc('day', a) BETWEEN '2020-01-01'::timestamp
                                 AND '2020-06-30'::timestamp;

EXPLAIN ANALYZE SELECT date_trunc('month', a), date_trunc('day', a)
   FROM t3 GROUP BY 1, 2;

Sans statistiques sur l'expression et le ndistinct, le planificateur n'a pas d'informations sur le nombre de valeurs distinctes pour les expressions, et doit se baser sur les estimations par défaut. Les conditions d'égalité et d'intervalle sont supposées avoir une sélectivité de 0,5%, et le nombre de valeurs distinctes dans l'expression est supposé être le même que pour la colonne (i.e. unique). Ceci résulte en une sous-estimation significative du nombre de lignes pour les deux premières requêtes. De plus, le planificateur ne connaît pas la relation entre les expressions, donc il suppose que les deux conditions du WHERE et la condition du GROUP BY sont indépendentes, et multiplie leurs sélectivités ensemble pour arriver à une sous-estimation sévère du nombre de groupes dans la requête d'agrégat. Ceci est encore plus exacerbé par le manque de statistiques précises sur les expressions, forçant le planificateur à utiliser une estimation par défaut du ndistinct pour l'expression dérivée du ndistinct pour la colonne. Avec de telles statistiques, le planificateur reconnaît que les conditions sont corrélées, et arrive à des estimations bien plus précises.

Compatibilité

Il n'y a pas de commande CREATE STATISTICS dans le standard SQL.