Documentation PostgreSQL 14.22 » Langage SQL » Conseils sur les performances » Statistiques utilisées par le planificateur

Précédent | Niveau supérieur | Suivant
14.1. Utiliser `EXPLAIN` | Sommaire | 14.3. Contrôler le planificateur avec des clauses `JOIN` explicites

## 14.2. Statistiques utilisées par le planificateur

### 14.2.1. Statistiques mono-colonne

Comme nous l'avons vu dans la section précédente, le planificateur de requêtes a besoin d'estimer le nombre de lignes récupérées par une requête pour faire les bons choix dans ses plans de requêtes. Cette section fournit un aperçu sur les statistiques que le système utilise pour ces estimations.

Un élément des statistiques est le nombre total d'entrées dans chaque table et index, ainsi que le nombre de blocs disque occupés par chaque table et index. Cette information est conservée dans la table `pg_class` sur les colonnes `reltuples` et `relpages`. Nous pouvons la regarder avec des requêtes comme celle-ci :

    SELECT relname, relkind, reltuples, relpages
    FROM pg_class
    WHERE relname LIKE 'tenk1%';

           relname        | relkind | reltuples | relpages
    ----------------------+---------+-----------+----------
     tenk1                | r       |     10000 |      358
     tenk1_hundred        | i       |     10000 |       30
     tenk1_thous_tenthous | i       |     10000 |       30
     tenk1_unique1        | i       |     10000 |       30
     tenk1_unique2        | i       |     10000 |       30
    (5 rows)

Ici, nous pouvons voir que `tenk1` contient 10000 lignes, comme pour ses index, mais que les index sont bien plus petits que la table (ce qui n'est pas surprenant).

Pour des raisons d'efficacité, les valeurs des colonnes `reltuples` et `relpages` ne sont pas mises à jour en temps réel, et contiennent alors souvent des valeurs un peu obsolètes. Elles sont mises à jour par les commandes `VACUUM`, `ANALYZE` et quelques commandes DDL comme `CREATE INDEX`. Une opération `VACUUM` ou `ANALYZE` qui ne parcourt pas la table entièrement (ce qui est le cas le plus fréquent) augmentera de façon incrémentale la valeur de `reltuples` sur la base de la partie de la table qu'elle a parcourue, résultant en une valeur approximative. Dans tous les cas, le planificateur mettra à l'échelle les valeurs qu'il aura trouvées dans `pg_class` pour correspondre à la taille physique de la table, obtenant ainsi une approximation plus proche de la réalité.

La plupart des requêtes ne récupèrent qu'une fraction des lignes dans une table à cause de clauses `WHERE` qui restreignent les lignes à examiner. Du coup, le planificateur a besoin d'une estimation de la sélectivité des clauses `WHERE`, c'est-à-dire la fraction des lignes qui correspondent à chaque condition de la clause `WHERE`. L'information utilisée pour cette tâche est stockée dans le catalogue système `pg_statistic`. Les entrées de `pg_statistic` sont mises à jour par les commandes `ANALYZE` et `VACUUM ANALYZE` et sont toujours approximatives même si elles ont été mises à jour récemment.

Plutôt que de regarder directement dans `pg_statistic`, il vaut mieux voir sa vue `pg_stats` lors d'un examen manuel des statistiques. La vue `pg_stats` est conçue pour être plus facilement lisible. De plus, `pg_stats` est lisible par tous alors que `pg_statistic` n'est lisible que par un super-utilisateur (ceci empêche les utilisateurs sans droits d'apprendre certaines choses sur le contenu des tables appartenant à d'autres personnes à partir des statistiques. La vue `pg_stats` est restreinte pour n'afficher que les lignes des tables lisibles par l'utilisateur courant). Par exemple, nous pourrions lancer :

    SELECT attname, inherited, n_distinct,
           array_to_string(most_common_vals, E'\n') as most_common_vals
    FROM pg_stats
    WHERE tablename = 'road';

     attname | inherited | n_distinct |          most_common_vals
    ---------+-----------+------------+------------------------------------
     name    | f         |  -0.363388 | I- 580                        Ramp+
             |           |            | I- 880                        Ramp+
             |           |            | Sp Railroad                       +
             |           |            | I- 580                            +
             |           |            | I- 680                        Ramp
     name    | t         |  -0.284859 | I- 880                        Ramp+
             |           |            | I- 580                        Ramp+
             |           |            | I- 680                        Ramp+
             |           |            | I- 580                            +
             |           |            | State Hwy 13                  Ramp
    (2 rows)

Notez que deux lignes sont affichées pour la même colonne, une correspondant à la hiérarchie d'héritage complète commençant à la table `road` (`inherited`=`t`), et une autre incluant seulement la table `road` elle-même (`inherited`=`f`).

Les informations stockées dans `pg_statistic` par `ANALYZE`, en particulier le nombre maximum d'éléments dans les tableaux `most_common_vals` et `histogram_bounds` pour chaque colonne, peuvent être définies colonne par colonne en utilisant la commande `ALTER TABLE SET STATISTICS` ou globalement en initialisant la variable de configuration default_statistics_target. La limite par défaut est actuellement de 100 entrées. Augmenter la limite pourrait résulter en des estimations plus précises du planificateur, en particulier pour les colonnes ayant des distributions de données irrégulières, au prix d'un plus grand espace consommé dans `pg_statistic` et d'un temps plus long pour calculer les estimations. En revanche, une limite plus basse pourrait être suffisante pour des colonnes avec des distributions de données simples.

Le Chapitre 72 donne plus de détails sur l'utilisation des statistiques par le planificateur.

### 14.2.2. Statistiques étendues

Il est habituel de voir des requêtes lentes tourner avec de mauvais plans d'exécution, car plusieurs colonnes utilisées dans les clauses de la requête sont corrélées. L'optimiseur part normalement du principe que toutes les conditions sont indépendantes les unes des autres, ce qui est faux quand les valeurs des colonnes sont corrélées. Les statistiques classiques, du fait qu'il s'agit par nature de statistiques sur une seule colonne, ne peuvent pas capturer d'information sur la corrélation entre colonnes. Toutefois, PostgreSQL a la possibilité de calculer des statistiques multivariées, qui peuvent capturer une telle information.

Comme le nombre de combinaisons de colonnes est très important, il n'est pas possible de calculer les statistiques multivariées automatiquement. À la place, des objets statistiques étendus, plus souvent appelés simplement objets statistiques, peuvent être créés pour indiquer au serveur qu'il faut obtenir des statistiques sur un ensemble intéressant de colonnes.

Les objets statistiques sont créés en utilisant la commande `CREATE STATISTICS`. La création de tels objets crée seulement une entrée dans le catalogue pour exprimer l'intérêt dans cette statistique. La vraie récupération de données est effectuée par `ANALYZE` (soit une commande manuelle, soit une analyse automatique en tâche de fond). Les valeurs collectées peuvent être examinées dans le catalogue `pg_statistic_ext_data`.

`ANALYZE` calcule des statistiques étendues basées sur le même ensemble de lignes de la table qu'il utilise pour calculer les statistiques standard sur une seule colonne. Puisque la taille d'échantillon peut être augmentée en augmentant la cible de statistiques de la table ou de n'importe laquelle de ses colonnes (comme décrit dans la section précédente), une plus grande cible de statistiques donnera normalement des statistiques étendues plus précises, mais nécessitera également plus de temps pour les calculer.

La section suivante décrit les types de statistiques étendues qui sont actuellement supportées.

#### 14.2.2.1. Dépendances fonctionnelles

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 cœfficients de dépendance afin de ne pas produire de résultats sous-estimés.

##### 14.2.2.1.1. Limites des dépendances fonctionnelles

Les dépendances fonctionnelles sont pour le moment uniquement appliquées pour les conditions sur une simple égalité entre une colonne et une valeur constante et des clauses `IN` contenant des valeurs constantes. Elles ne sont pas utilisées pour améliorer l'estimation sur les conditions d'égalité entre deux colonnes ou la comparaison d'une colonne avec une expression ni pour les clauses d'intervalle, `LIKE` ou tout autre type de condition.

Lors d'une estimation avec des dépendances fonctionnelles, l'optimiseur part du principe que les conditions sur les colonnes impliquées sont compatibles et donc redondantes. Si elles sont incompatibles, l'estimation correcte devrait être zéro ligne, mais cette possibilité n'est pas envisagée. Par exemple, dans une requête telle que

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

l'optimiseur négligera la clause `city` puisqu'elle ne changera pas la sélectivité, ce qui est correct. Par contre, il fera la même supposition pour

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

bien qu'il n'y ait en réalité aucune ligne satisfaisant cette requête. Toutefois, les statistiques de dépendances fonctionnelles ne fournissent pas suffisamment d'information pour en arriver à cette conclusion.

Pour beaucoup de situations pratiques, cette supposition est généralement correcte ; par exemple, l'application pourrait contenir une interface graphique qui n'autorise que la sélection de villes et codes postaux compatibles pour l'utilisation dans une requête. Mais si ce n'est pas le cas, les dépendances fonctionnelles pourraient ne pas être une solution viable.

#### 14.2.2.2. Nombre N-Distinct multivarié

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é.

#### 14.2.2.3. Listes multivariées MCV

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`.

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.