Le type le plus simple de statistiques étendues trace les dépendances fonctionnelles , un concept utilisé dans les définitions des formes normales des bases de données. On dit qu'une colonne b est fonctionnellement dépendante d'une colonne a si la connaissance de la valeur de a est suffisante pour déterminer la valeur de b, et donc qu'il n'existe pas deux lignes ayant la même valeur de a avec des valeurs différentes de b. Dans une base de données complètement normalisée, les dépendances fonctionnelles ne devraient exister que sur la clé primaire et les superclés. Toutefois, dans la pratique, beaucoup d'ensembles de données ne sont pas totalement normalisés pour de nombreuses raisons ; une dénormalisation intentionnelle pour des raisons de performances est un exemple courant. Même dans une base de données totalement normalisée, il peut y avoir une corrélation partielle entre des colonnes, qui peuvent être exprimées comme une dépendance fonctionnelle partielle.

L'existence de dépendances fonctionnelles a un impact direct sur la précision de l'estimation pour certaines requêtes. Si une requête contient des conditions à la fois sur des colonnes indépendantes et sur des colonnes dépendantes, les conditions sur les colonnes dépendantes ne réduisent plus la taille du résultat ; mais sans la connaissance de cette dépendance fonctionnelle, l'optimiseur de requêtes supposera que les conditions sont indépendantes, avec pour résultat une taille de résultat sous-estimée.

Pour informer l'optimiseur des dépendances fonctionnelles, ANALYZE peut collecter des mesures sur des dépendances entre colonnes. Évaluer le degré de dépendance entre tous les ensembles de colonnes aurait un coût prohibitif, c'est pourquoi la collecte de données est limitée aux groupes de colonnes apparaissant ensemble dans un objet statistique défini avec l'option dependencies. Il est conseillé de ne créer des dépendences statistiques que pour des groupes de colonnes fortement corrélées, pour éviter un surcoût à la fois dans ANALYZE et plus tard lors de la planification de requête.

Voici un exemple de collecte de statistiques fonctionnellement dépendantes :

CREATE STATISTICS stts (dependencies) ON city, zip FROM zipcodes;

ANALYZE zipcodes;

SELECT stxname, stxkeys, stxddependencies
  FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
  WHERE stxname = 'stts';
 stxname | stxkeys |             stxddependencies
---------+---------+------------------------------------------
 stts    | 1 5     | {"1 => 5": 1.000000, "5 => 1": 0.423130}
(1 row)
     

On peut voir ici que la colonne 1 (zip code) détermine complètement la colonne 5 (city) et que donc le coefficient est 1.0, alors que la ville ne détermine le code postal qu'environ 42% du temps, ce qui veut dire qu'il y a beaucoup de villes (58%) qui sont représentées par plus d'un seul code postal.

Lors du calcul de la sélectivité d'une requête impliquant des colonnes fonctionnellement dépendantes, le planificateur ajoute l'estimation de sélectivité par condition en utilisant les coefficients de dépendance afin de ne pas produire de résultats sous-estimés.

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '94105';
      

SELECT * FROM zipcodes WHERE city = 'San Francisco' AND zip = '90210';
      

Les statistiques sur une seule colonne stockent le nombre de valeurs distinctes pour chaque colonne. Les estimations du nombre de valeurs distinctes combinant plus d'une colonne (par exemple, pour GROUP BY a, b) sont souvent fausses quand l'optimiseur ne dispose que de données statistiques par colonne, avec pour conséquence le choix de mauvais plans.

Afin d'améliorer de telles estimations, ANALYZE peut collecter des statistiques n-distinct pour des groupes de colonne. Comme précédemment, il n'est pas envisageable de le faire pour tous les regroupements possibles, ainsi les données ne sont collectées que pour ceux apparaissant ensemble dans un objet statistique défini avec l'option ndistinct. Des données seront collectées pour chaque combinaison possible de deux colonnes ou plus dans l'ensemble de colonnes listées.

En continuant avec l'exemple précédent, le nombre n-distinct dans une table de code postaux pourrait ressembler à ceci :

CREATE STATISTICS stts2 (ndistinct) ON city, state, zip FROM zipcodes;

ANALYZE zipcodes;

SELECT stxkeys AS k, stxdndistinct AS nd
  FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid)
  WHERE stxname = 'stts2';
-[ RECORD 1 ]--------------------------------------------------------
k  | 1 2 5
nd | {"1, 2": 33178, "1, 5": 33178, "2, 5": 27435, "1, 2, 5": 33178}
(1 row)
     

Cela indique qu'il y a trois combinaisons de colonnes qui ont 33178 valeurs distinctes : le code postal et l'état; le code postal et la ville; et le code postal, la ville et l'état (le fait qu'ils soient tous égaux est attendu puisque le code postal seul est unique dans cette table). D'un autre côté, la combinaison de la ville et de l'état n'a que 27435 valeurs distinctes.

Il est conseillé de créer des objets statistiques ndistinct uniquement sur les combinaisons de colonnes réellement utilisées pour des regroupements, et pour lesquelles les mauvaises estimations du nombre de groupe a pour conséquence de mauvais plans. Sinon le temps consommé par ANALYZE serait gaspillé.

Un autre type de statistiques enregistrées pour chaque colonne est les listes des valeurs les plus communes. Ceci permet des estimations très précises pour les colonnes individuelles, mais pourrait résulter en des estimations significativement mauvaises pour les requêtes ayant des filtres sur plusieurs colonnes.

Pour améliorer ces estimations, ANALYZE peut récupérer des listes MCV sur des combinaisons de colonnes. De façon similaire aux dépendances fonctionnelles et coefficients de valeurs distinctes, il n'est pas possible de le faire pour chaque regroupement de colonnes. Ceci est encore plus vrai dans ce cas, car la liste MCV (contrairement aux dépendances fonctionnelles et coefficients de valeurs distinctes), enregistre les valeurs les plus communes. Donc les données ne sont récupérées que pour les groupes de colonnes apparaissant dans un objet statistique défini avec l'option mcv option.

En continuant sur l'exemple précédent, la liste MCV pour une table de codes ZIP pourrait ressembler à ce qui suit (contrairement aux types plus simples de statistiques, une fonction est requise pour inspecter le contenu du MCV) :

CREATE STATISTICS stts3 (mcv) ON city, state FROM zipcodes;

ANALYZE zipcodes;

SELECT m.* FROM pg_statistic_ext join pg_statistic_ext_data on (oid = stxoid),
                pg_mcv_list_items(stxdmcv) m WHERE stxname = 'stts3';

 index |         values         | nulls | frequency | base_frequency
-------+------------------------+-------+-----------+----------------
     0 | {Washington, DC}       | {f,f} |  0.003467 |        2.7e-05
     1 | {Apo, AE}              | {f,f} |  0.003067 |        1.9e-05
     2 | {Houston, TX}          | {f,f} |  0.002167 |       0.000133
     3 | {El Paso, TX}          | {f,f} |     0.002 |       0.000113
     4 | {New York, NY}         | {f,f} |  0.001967 |       0.000114
     5 | {Atlanta, GA}          | {f,f} |  0.001633 |        3.3e-05
     6 | {Sacramento, CA}       | {f,f} |  0.001433 |        7.8e-05
     7 | {Miami, FL}            | {f,f} |    0.0014 |          6e-05
     8 | {Dallas, TX}           | {f,f} |  0.001367 |        8.8e-05
     9 | {Chicago, IL}          | {f,f} |  0.001333 |        5.1e-05
   ...
(99 rows)
     

Ceci indique que la combinaison la plus commune des colonnes city et state est Washington DC, avec la fréquence réelle (dans cet exemple) de 0,35 %. La fréquence de base de la combinaison (telle qu'elle est calculée par les fréquences par mono colonne) est seulement de 0,0027 %, résultant en une sous-estimation très forte.

Il est préférable de créer des objets statistiques MCV uniquement sur les combinaisons de colonnes réellement utilisées ensemble dans des filtres et pour lesquelles la mauvaise estimation du nombre de groupes a pour conséquence de mauvais plans. Dans le cas contraire, le ANALYZE et le temps de planification sont juste gâchés.