4 Statistiques pour le planificateur

La première illustration de ce chapitre est la Figure 4-1.

4/ Statistiques pour le planificateur

4.1 INTRODUCTION

• Indispensables!
• Statistiques sur les données pour le planificateur
  • pour le choix du parcours
  • comme le choix des jointures
• Les statistiques indiquent :
  • la cardinalité d’un filtre → stratégie d’accès
  • la cardinalité d’une jointure → algorithme de jointure
  • la cardinalité d’un regroupement → algorithme de regroupement
• Sans bonnes statistiques, pas de bons plans !

Connaître le coût unitaire de traitement d’une ligne est utile, mais sans savoir combien de lignes sont à traiter, il est impossible de calculer le coût total d’une requête. PostgreSQL s’appuie donc principalement sur les statistiques, qui sont générées sur la base d’un échantillonnage des données d’une table, pour guider le planificateur. Ces statistiques permettent de déterminer la répartition et la qualité des valeurs dans une colonne. Elles sont essentielles pour évaluer la sélectivité des filtres (clauses WHERE, jointures) et un choix de stratégie optimale: utiliser un index, organiser les jointures ou choisir un algorithme de regroupement. Sans statistiques précises et à jour, le planificateur pourrait générer des plans d’exécution inefficaces, impactant les performances des requêtes. Ces décisions s’appuient sur les informations contenues dans les statistiques ainsi que sur certains paramètres de configuration, qui permettent d’orienter le planificateur. Ces statistiques sont collectées automatiquement, mais peuvent parfois être obsolètes si les tables évoluent rapidement. Il est donc essentiel de maintenir les statistiques à jour avec la commande ANALYZE pour garantir des plans de requête efficaces.

Les statistiques sur les données utilisées par le planificateur ne doivent pas être confondues avec les statistiques d’activité recensées dans les vues pg_stat_* et pg_statio_* (chargement système, connexions, etc.) qui sont distinctes et ne seront pas évoquées ici.

Voir au besoin les modules de formation H1 - Supervision de PostgreSQL 1 ou H2 - Analyses et diagnostics 2.

1 https://dali.bo/h1_html 2 https://dali.bo/h2_html

4.2 AU MENU

• Statistiques de volumétrie
• Statistiques sur les données
  • principe
  • statistiques étendues
• ANALYZE et autovacuum

Le présent module nous permettra de comprendre sur quelles informations l’optimiseur se base pour calculer ses coûts.

4.3 STATISTIQUES DE VOLUMÉTRIE

4.3.1 pg_class

SELECT relpages, reltuples FROM pg_class WHERE relname = 'employes';

• pour tables et index
• relpages : taille en blocs
  • dont relallvisible
• reltuples : lignes
• Mise à jour : VACUUM / ANALYZE

L’optimiseur a besoin de deux données statistiques pour une table ou un index: sa taille physique et le nombre de lignes portées par l’objet. Ces deux données statistiques sont stockées dans la table pg_class.

• La taille physique sur le disque de la table ou de l’index est exprimée en nombre de blocs de 8 ko, et stockée dans la colonne relpages .
• relallvisible est le nombre de blocs dont toutes les lignes sont visibles (« vivantes »).
• La cardinalité de la table ou de l’index, c’est-à-dire le nombre de lignes, est stockée dans la colonne reltuples . Il s’agit des lignes vivantes (visibles par une nouvelle session). Dans pg_stat_user_tables se trouvent aussi la quantité de lignes totale et le nombre de lignes

mortes (modifiées, effacées, éventuellement encore visibles de certaines sessions). Attention: ces valeurs sont des estimations, pas forcément à jour. La mise à jour a lieu essentiellement lors d’un VACUUM ou ANALYZE récent. Ces chiffres permettent au planificateur de calculer une densité de lignes dans les blocs à récupérer. Quand il aura estimé un nombre de lignes à lire dans une table ou un index, il pourra ainsi connaître le nombre de blocs à lire.

4.4 Statistiques sur les données

4.4.1 Tables de statistiques

• Échantillonner pour mieux planifier
• Estimer la sélectivité d’une clause WHERE ou JOIN
• Tables pg_statistic (par colonne), pg_statistic_ext…
• Vues pg_stats (par colonne), pg_stats_ext et pg_stats_ext_exprs

Les statistiques sur les données permettent de déterminer la sélectivité d’un filtre (prédicat d’une clause WHERE ou une condition de jointure) et donc quelle sera la quantité de données récupérées par la lecture d’une table.

Prenons un exemple avec la base employes (le SQL pour l’alimenter est sur https://dali.bo/tp_employes_services). Avec un filtre peu sélectif, date_embauche = '2006-09-01', la requête suivante ramène pratiquement l’intégralité de la table. PostgreSQL choisit donc une lecture séquentielle de la table, ou Seq Scan :

EXPLAIN (ANALYZE, TIMING OFF)
SELECT *
FROM employes_big
WHERE date_embauche='2006-09-01';

                           QUERY PLAN
-----------------------------------------------------------------
 Seq Scan on employes_big
   (cost=0.00..10901.69 rows=498998 width=40)
                                        (actual rows=499004 loops=1)
   Filter: (date_embauche = '2006-09-01'::date)
   Rows Removed by Filter: 11
 Planning Time: 0.027 ms
 Execution Time: 42.624 ms

La partie cost montre que l’optimiseur estime que la lecture ramène 498 998 lignes. Comme on peut le voir, ce n’est pas exact : elle en récupère 499 004. Ce n’est qu’une estimation basée sur des statistiques selon la répartition des données, et ces estimations sont la plupart du temps un peu erronées. L’important est de savoir si l’erreur est négligeable ou importante. Dans notre cas, elle est négligeable. On lit aussi que 11 lignes ont été filtrées pendant le parcours (et 499 004 + 11 correspond bien aux 499 015 lignes de la table).

Avec un filtre sur une valeur beaucoup plus sélective, la requête ne ramène que 2 lignes. L’optimiseur préfère donc passer par l’index présent sur la colonne :

EXPLAIN (ANALYZE, TIMING OFF)
SELECT *
FROM employes_big
WHERE date_embauche='2006-01-01';

                             QUERY PLAN
--------------------------------------------------------------------
 Index Scan using employes_big_date_embauche_idx on employes_big
   (cost=0.42..4.44 rows=1 width=41) (actual rows=2 loops=1)
   Index Cond: (date_embauche = '2006-01-01'::date)
 Planning Time: 0.213 ms
 Execution Time: 0.090 ms

Dans ce deuxième essai, l’optimiseur estime ramener 1 ligne. En réalité, il en ramène 2. L’estimation reste relativement précise étant donné le volume de données.

Dans le premier cas, l’optimiseur prévoit de sélectionner l’essentiel de la table et estime qu’il est moins coûteux de passer par une lecture séquentielle plutôt que par une lecture d’index. Dans le second cas, où le filtre est très sélectif, une lecture par index est plus appropriée.

Pour comprendre les choix de l’optimiseur, il est possible de consulter les statistiques dont il dispose. La vue pg_stats contient les statistiques de chaque colonne et des index sur expression. Cette vue a été créée pour faciliter la compréhension des statistiques stockées dans la table système pg_statistic.

Les vues pg_stats_ext et pg_stats_ext_exprs sont basées sur les tables systèmes pg_statistic_ext et pg_statistic_ext_data, qui concernent les statistiques étendues. Celles-ci peuvent porter sur plus d’une colonne et doivent être créées par les utilisateurs avec CREATE STATISTICS. Ces tables sont alimentées par ANALYZE.

4.5 Statistiques monovariées

4.5.1 Statistiques monovariées

• Pour chaque colonne de chaque table
• Adaptées à la majorité des cas
  • forte ou faible contrainte de type
  • forte ou faible variabilité des données
• Calculées automatiquement par ANALYZE

Par défaut, ANALYZE échantillonne chaque colonne de chaque table et calcule différentes statistiques en fonction des contraintes associées à leur type (nombres, chaînes, binaires) et de la variabilité des valeurs observées.

4.5.2 Valeurs distinctes (N-Distinct)

-[ RECORD 1 ]----------+-------------------------------------
schemaname             | public
tablename              | employes
attname                | date_embauche
n_distinct             | -0.5

• Estimation basée sur l’échantillon
• Si > 0 : nombre de valeurs distinctes dans la colonne
  • valeur constante indépendante de la volumétrie
• Si < 0 : proportion de lignes distinctes
  • valeur proportionnelle à la volumétrie
• -1 : toutes les valeurs diffèrent (par exemple une clé primaire)
• La valeur peut être forcée

Les statistiques d’une colonne d’une table se récupèrent ainsi :

SELECT * FROM pg_stats
WHERE tablename = 'employes' AND attname = 'date_embauche' ;

On ne s’intéresse pour le moment qu’au champ n_distinct. Si une colonne contient moins de 10 % de valeurs distinctes par rapport au nombre total de lignes (par exemple la colonne departement dans une table adresses), PostgreSQL considère que ce nombre reste fixe. La valeur de n_distinct dans pg_stats est alors positive et correspond exactement au compte de valeurs uniques.

Si le nombre de valeurs distinctes dépasse 10 % (par exemple le champ auteur dans une table commentaires), l’optimiseur estime que ce nombre augmentera proportionnellement avec la taille de la table. Dans ce cas, n_distinct devient négatif pour indiquer un ratio d’accroissement. Une valeur de -0.15 signifie que l’optimiseur prévoit que 15 % des lignes seront des valeurs distinctes : si la table passe à 20 000 lignes, il estimera environ 3 000 valeurs distinctes.

Cette colonne peut être NULL si le type de données n’a pas d’opérateur =. Par exemple, les types point ou json ne le supportent pas, alors que int, text et jsonb le supportent.

Il est possible de forcer cette colonne à une valeur constante :

ALTER TABLE nom_table
ALTER COLUMN nom_colonne SET (n_distinct = <valeur>);

On utilise n_distinct_inherited pour une table comprenant des partitions. Pour les grosses tables contenant des valeurs distinctes, indiquer une grande valeur ou la valeur -1 permet de favoriser les parcours d’index à la place des parcours bitmap. C’est aussi utile pour des tables où les données ne sont pas réparties de façon homogène et où la collecte de cette statistique est alors faussée.

4.5.3 Most Common Values (MCV)

-[ RECORD 1 ]-----+----------------------------------------------------
schemaname        | public
tablename         | employes
attname           | date_embauche
most_common_vals  | {2006-03-01,2006-09-01,2000-06-01,2005-03-06,2006-01-01}
most_common_freqs | {0.214286,0.214286,0.142857,0.142857,0.142857}

• Liste des valeurs les plus fréquentes
• Liste des fréquences de ces valeurs
• Variable en fonction de l’échantillonnage

Les informations relatives aux valeurs les plus fréquentes sont visibles dans les colonnes most_common_vals et most_common_freqs de la vue pg_stats.

La colonne most_common_vals contient une liste triée des valeurs les plus communes. Elle peut être NULL si les valeurs semblent toujours aussi communes ou si le type de données n’a pas d’opérateur =.

La colonne most_common_freqs contient une liste triée des fréquences pour ces valeurs. Cette fréquence est le nombre d’occurrences de la valeur divisé par le nombre de lignes. Elle est NULL si most_common_vals est NULL.

La vue pg_stats comporte aussi les colonnes most_common_elems et most_common_elem_freqs, qui sont les équivalents pour les données de type tableau. Considérons un tableau de mots-clés associés à chaque article :

SELECT * FROM articles;

 id |            tags
----+----------------------------
 33 | {stylo,bleu,bille}
 34 | {cahier,spirale,A4}
 35 | {agrafeuse,métal,compacte}
 36 | {stylo,noir,gel}
 37 | {cahier,A4,quadrillé}
 38 | {cahier,spirale,A4}
 39 | {stylo,bleu,bille}
 40 | {cahier,A4,spirale}
 41 | {stylo,bleu,roller}
 42 | {stylo,noir,gel}
 (10 lignes)

Dans ce genre de cas, les champs most_common_elems et most_common_elem_freqs de pg_stats sont renseignés. On peut y retrouver les étiquettes les plus présentes, et leur fréquence :

SELECT
    unnest(most_common_elems::text::text[]) AS tag,
    unnest(most_common_elem_freqs) AS frequency
FROM pg_stats
WHERE tablename = 'articles' AND attname = 'tags';

    tag     | frequency
-----------+-----------
 A4         |       0.4
 agrafeuse  |        0.1
 bille      |        0.2
 bleu       |        0.3
 cahier     |        0.4
 compacte   |        0.1
 gel        |        0.2
 métal      |        0.1
 noir       |        0.2
 quadrillé  |        0.1
 roller     |        0.1
 spirale    |        0.3
 stylo      |        0.5
            |        0.1 -- fréquence minimale observée
            |        0.5 -- fréquence maximale observée
            |          0 -- fréquence des éléments NULL
(16 lignes)

Par exemple, l’étiquette « cahier » est présente dans les étiquettes de 4 des 10 lignes de la table. Sa fréquence est donc de 40 % (0,4). Les trois dernières valeurs contenues dans most_common_elem_freqs permettent à l’optimiseur de connaître directement les fréquences minimale, maximale et associée aux valeurs NULL de l’ensemble.

4.5.4 Histogramme

-[ RECORD 1 ]-------+------------------------------------------------------
schemaname          | public
tablename           | produits
attname             | prix
histogram_bounds    | {4.00,6.12,9.14,23.55,99.57}

• Décrit la répartition des données (hors MCV et NULL)
• Nombre de lignes sensiblement égal entre chaque borne de l’histogramme
• N’est pas calculé par ANALYZE si toutes les valeurs sont dans les MCV

Pour chaque colonne, les valeurs récupérées par l’échantillonnage sont triées. Ces données triées sont partagées en N intervalles de valeurs, d’amplitudes différentes mais de même nombre d’éléments. N dépend de la valeur du paramètre default_statistics_target, par défaut 100, et de la configuration spécifique de la colonne. PostgreSQL construit ensuite un tableau dont chaque valeur correspond au début d’une tranche.

L’histogramme illustré ci-dessus a été construit ainsi :

-- Création de la table
CREATE TABLE produits (
    id SERIAL PRIMARY KEY,
    nom TEXT,
    prix NUMERIC(10,2)
);

-- Ajustement de l'échantillonnage pour l'exemple
ALTER TABLE produits ALTER COLUMN prix SET STATISTICS 4;

-- Insertion de données
INSERT INTO produits (nom, prix)
SELECT
    'Produit ' || g,
    CASE floor(random() * 4)
        WHEN 0 THEN ROUND((4 + random() * 2)::numeric, 2)  -- 25 % [4-6)
        WHEN 1 THEN ROUND((6 + random() * 2)::numeric, 2)  -- 25 % [6-8)
        WHEN 2 THEN ROUND((8 + random() * 9)::numeric, 2)  -- 25 % [8-17)
        ELSE ROUND((17 + random() * 83)::numeric, 2)       -- 25 % [17-100)
    END
FROM generate_series(1, 45000) g;

-- Calcul des statistiques
ANALYZE produits;

-- Affichage de l'histogramme
SELECT schemaname, tablename, attname, histogram_bounds
FROM pg_stats
WHERE tablename = 'produits'
AND attname = 'prix' \gx;

-[ RECORD 1 ]-------+------------------------------------------------------
schemaname          | public
tablename           | produits
attname             | prix
histogram_bounds    | {4.00,6.12,9.14,23.55,99.57}

Ces valeurs peuvent légèrement varier entre deux appels différents d’ANALYZE, car l’échantillonnage change à chaque fois.

La requête suivante permet de vérifier que les 5 bornes de l’histogramme permettent bien une répartition équilibrée des lignes :

WITH bounds AS (
    -- Extraction des bornes de l'histogramme
    SELECT unnest(histogram_bounds::text::numeric[]) AS bound
    FROM pg_stats
    WHERE tablename = 'produits' AND attname = 'prix'
),
intervals AS (
    -- Génération des paires de bornes (borne_inf, borne_sup)
    SELECT
        bound AS borne_inf,
        lead(bound) OVER (ORDER BY bound) AS borne_sup
    FROM bounds
),
counts AS (
    -- Comptage des lignes de produits dans chaque intervalle
    SELECT
        borne_inf,
        borne_sup,
        COUNT(*) AS nombre_de_produits
    FROM intervals
    JOIN produits
      ON produits.prix >= borne_inf
     AND (borne_sup IS NULL OR produits.prix < borne_sup)
    GROUP BY borne_inf, borne_sup
)
SELECT
    borne_inf,
    borne_sup,
    nombre_de_produits,
    -- Calcul du pourcentage arrondi à 2 décimales
    ROUND(nombre_de_produits * 100.0
          / SUM(nombre_de_produits) OVER (), 2) AS pourcentage
FROM counts
ORDER BY borne_inf;

 borne_inf | borne_sup | nombre_de_produits | pourcentage
-----------+-----------+--------------------+-------------
      4.01 |        5.97 |              11218 |       24.94
      5.97 |        8.06 |              11534 |       25.64
      8.06 |       20.48 |              11481 |       25.52
     20.48 |       99.07 |              10636 |       23.65
     99.07 |             |                111 |        0.25
(5 lignes)

L’échantillonnage a permis d’avoir quatre lots répartis de manière équilibrée, mais pas parfaite. L’information est de qualité suffisante pour l’optimiseur, surtout lorsque default_statistics_target est suffisamment élevé.

Comme pour most_common_elems et most_common_elems_freqs, la colonne elem_count_histogram de la vue pg_stats permet de calculer des histogrammes pour les colonnes de type tableau.

4.5.5 Valeurs nulles

-[ RECORD 1 ]----------+-------------------------------------
schemaname             | public
tablename              | clients
attname                | telephone_fixe
null_frac              | 0.98

• Estimation du nombre de valeurs nulles dans la colonne

Cette statistique, la colonne null_frac de la vue pg_stats, correspond au pourcentage de valeurs NULL dans l’échantillon considéré par ANALYZE. Elle est toujours calculée.

4.5.6 Corrélation physique

-[ RECORD 1 ]----------+-------------------------------------
schemaname             | public
tablename              | clients
attname                | date_inscription
correlation            | 1

• Varie de -1 à 1
• Indique si les données sont ordonnées dans les fichiers
• Arbitre la pertinence de l’usage d’un index et son type de parcours en estimant le nombre de blocs de la table à lire :
  • forte corrélation = données regroupées > parcours d’index efficace
  • faible corrélation = données dispersées

La colonne correlation de la vue pg_stats indique la corrélation entre l’ordre physique et l’ordre logique des valeurs de la colonne. Dans notre exemple, les données sont parfaitement triées sur le disque selon le champ date_inscription, la corrélation est donc de 1. Si les données sont physiquement triées, il y aura moins de blocs à récupérer sur le disque pour des valeurs proches ou identiques que si elles étaient totalement dispersées dans la table.

Dans l’exemple ci-dessous, on considère la table produits avec un champ stock. Les produits les plus anciens ont tendance à avoir davantage de stock que ceux récemment entrés en catalogue, la corrélation est donc proche de -1 :

SELECT schemaname, tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'produits'
AND attname = 'stock' \gx

-[ RECORD 1 ]-----------
schemaname | public
tablename  | produits
attname    | stock
correlation | -0.9380958

Si la corrélation physique est proche de 0, les lignes sont dispersées dans la table sans lien avec la valeur du champ. Passer par un index est alors moins intéressant en termes de nombre de blocs à lire par ligne ramenée. Un parcours séquentiel de la table ou un bitmap heap scan ont alors plus de chances d’être choisis :

SELECT schemaname, tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'commandes'
AND attname = 'date_achat' \gx

-[ RECORD 1 ]-------------
schemaname  | public
tablename   | commandes
attname     | date_achat
correlation | -0.001096768

Si cette colonne date_achat est critique pour le métier et que beaucoup de requêtes y font référence pour un tri, on peut trier explicitement la table selon ce champ avec CLUSTER :

-- Création d'un index sur date_achat
CREATE INDEX idx_commandes_date ON commandes(date_achat);

-- CLUSTER
CLUSTER commandes USING idx_commandes_date;

-- ANALYZE
ANALYZE commandes;

-- Résultat
SELECT schemaname, tablename, attname, correlation
FROM pg_stats
WHERE tablename = 'commandes'
AND attname = 'date_achat' \gx

-[ RECORD 1 ]-----------
schemaname | public
tablename  | commandes
attname    | date_achat
correlation | 1

Comparons les plans d’exécution avant et après CLUSTER :

-- Requête
EXPLAIN ANALYZE
SELECT * FROM commandes
WHERE date_achat BETWEEN '2023-06-01' AND '2023-06-30';

-- Plan avant CLUSTER (simplifié)

                         QUERY PLAN
----------------------------------------------------------------
 Bitmap Heap Scan on commandes
   (actual time=1.621..10.927 rows=8192)
   Heap Blocks: exact=541
   Buffers: shared hit=541 read=9
   -> Bitmap Index Scan on idx_commandes_date
        (actual time=1.475 rows=8192)
        Buffers: shared read=9
 Planning Time: 1.003 ms
 Execution Time: 11.662 ms -- TOTAL 12.665 ms

-- Plan après CLUSTER (simplifié)

                         QUERY PLAN
----------------------------------------------------------------
 Index Scan using idx_commandes_date on commandes
   (actual time=0.084..2.150 rows=8192)
   Index Cond: (date_achat BETWEEN '2023-06-01' AND '2023-06-30')
   Buffers: shared hit=55
 Planning Buffers: shared hit=9
 Planning Time: 0.454 ms
 Execution Time: 2.812 ms -- TOTAL 3.266 ms

Dans le premier cas, l’index est bien utilisé mais via un Bitmap Index Scan, qui référence les blocs et non directement chacune des 8 136 lignes. Dans ce cas, 550 blocs ont dû être lus. Après CLUSTER, les données sont physiquement triées sur disque selon le champ date_achat. L’Index Scan classique est alors choisi et le temps d’exécution est réduit de 74 %. Dix fois moins de blocs ont été lus.

CLUSTER réécrit, défragmente et réindexe la table, et l’opération est totalement bloquante. Cette commande est donc très lourde. Elle est surtout utile en fin d’un batch ou d’un chargement de données.

La corrélation physique imposée par CLUSTER n’est pas forcément conservée par PostgreSQL. Au fur et à mesure que les lignes sont modifiées, elles peuvent se retrouver dans n’importe quelle partie vide de la table.

La corrélation peut être à NULL si le type de données de la colonne ne supporte pas l’opérateur de comparaison <.

4.5.7 Largeur moyenne des données

-[ RECORD 1 ]----------+-------------------------------------
schemaname             | public
tablename              | employes
attname                | date_embauche
avg_width              | 4

• Calculé pour les champs non NULL de taille variable (text, json, jsonb, binaires, etc.)
• Constant pour les autres types (integer, boolean, char, etc.)

La largeur moyenne en octets des éléments d’une colonne est indiquée dans le champ avg_width de la vue pg_stats. Elle est constante pour les colonnes dont le type est à taille fixe (integer, boolean, etc.). Elle est variable pour les autres, principalement text, varchar, bytea et jsonb.

Dans le cas d’un champ avec des valeurs assez grandes (plusieurs kilooctets), avg_width est faussé, car le mécanisme TOAST se déclenche. Les champs sont alors compressés, voire déportés dans une table annexe. avg_width calcule alors la longueur physique moyenne du champ dans la table, compressé ou pas, parfois réduite à un pointeur vers la table TOAST associée.

4.6 Échantillonnage

4.6.1 Taille de l’échantillon

• Échantillon de 30 000 lignes par défaut
• default_statistics_target = 100
• × 300 lignes

Par défaut, un ANALYZE récupère 30 000 lignes d’une table. Cette valeur se calcule à partir du paramètre default_statistics_target (100 par défaut) : la taille de l’échantillon est de 300 fois default_statistics_target.

Les statistiques générées à partir de cet échantillon sont bonnes si la table ne contient pas des millions de lignes. Au-delà, il arrive très souvent que la répartition des données dans une colonne soit régulière, et l’échantillon reste assez représentatif pour donner de bonnes estimations.

4.6.2 Modifier l’échantillon

-- Configurable pour chaque colonne
ALTER TABLE t ALTER COLUMN c SET STATISTICS 300 ;
-- Configurable pour chaque statistique étendue
ALTER STATISTICS nom SET STATISTICS valeur ;

• Échantillon plus important
  • plus précis dans certains cas
  • temps de planification plus long
• ANALYZE pour rafraîchir les statistiques

Si l’échantillon n’est plus assez représentatif, les estimations qui en découlent peuvent mener à de mauvais plans d’exécution. Un échantillon plus gros peut corriger ce souci. Pour cela, il faut augmenter la valeur du paramètre default_statistics_target.

Il est possible de configurer ce paramétrage colonne par colonne :

ALTER TABLE nom_table ALTER nom_colonne SET STATISTICS <valeur> ;
ANALYZE nom_table ;

Les statistiques seront alors plus précises, mais elles seront plus longues à calculer par ANALYZE, prendront plus de place sur le disque et en RAM, et demanderont plus de travail au planificateur pour générer le plan optimal. Augmenter cette valeur n’a donc pas que des avantages : on évitera de dépasser 1000. Généralement, l’échantillonnage ne sera augmenté que pour certaines colonnes posant souci.

4.7 Statistiques multivariées

4.7.1 Statistiques multivariées

• Pas par défaut
• CREATE STATISTICS
• Trois types de statistiques
  • nombre de valeurs distinctes
  • dépendances fonctionnelles
  • liste MCV
• ANALYZE après la création
• vue pg_stats_ext

La commande ANALYZE de PostgreSQL calcule des statistiques pour l’ensemble des colonnes d’une table et ses index sur fonction. Cependant, les valeurs des colonnes ne sont pas toujours indépendantes et peuvent varier ensemble. Estimer correctement le nombre de lignes retournées par une combinaison de critères devient donc impossible sans connaître cette relation.

Pour cela, il est nécessaire de créer un objet statistique avec l’ordre SQL CREATE STATISTICS. Cet objet indique les colonnes concernées ainsi que le type de statistique souhaité. PostgreSQL supporte trois types de statistiques pour ces objets :

• ndistinct pour le nombre de valeurs distinctes sur ces colonnes ;
• dependencies pour les dépendances fonctionnelles ;
• mcv pour une liste des valeurs les plus fréquentes.

Pour créer une statistique d’un certain type, la syntaxe est :

CREATE STATISTICS stat_services (<type>)
ON champ1, champ2
FROM nom_table ;

On peut calculer les trois types à la fois avec cette syntaxe allégée (ce qui est pratique mais peut consommer inutilement du CPU) :

CREATE STATISTICS stat_services
ON champ1, champ2
FROM nom_table ;

Dans tous les cas, cela peut permettre d’améliorer fortement les estimations de nombre de lignes, ce qui peut amener de meilleurs plans d’exécution.

4.7.2 Dépendances fonctionnelles

CREATE STATISTICS stat_services (dependencies)
ON code_postal, ville, departement
FROM services;

• Indique une dépendance fonctionnelle entre deux colonnes
• La valeur d’une colonne varie en fonction d’une autre

Prenons un exemple. On peut voir sur ces deux requêtes que les statistiques sont à jour :

EXPLAIN (ANALYZE)
SELECT * FROM services_big
WHERE localisation='Paris';

EXPLAIN (ANALYZE)
SELECT * FROM services_big
WHERE departement=75;

-------------------------------------------------------------------
 Seq Scan on services_big (cost=0.00..786.05 rows=10013 width=28)
                     (actual time=0.019..4.773 rows=10001 loops=1)
   Filter: ((localisation)::text = 'Paris'::text)
   Rows Removed by Filter: 30003
 Planning Time: 0.863 ms
 Execution Time: 5.289 ms

-------------------------------------------------------------------
 Seq Scan on services_big (cost=0.00..786.05 rows=10013 width=28)
                     (actual time=0.020..7.013 rows=10001 loops=1)
   Filter: (departement = 75)
   Rows Removed by Filter: 30003
 Planning Time: 0.219 ms
 Execution Time: 7.785 ms

Cela fonctionne bien : l’estimation du nombre de lignes (10 013) est très proche de la réalité (10 001) quand le filtre porte sur une seule colonne. En revanche, si le filtre porte sur le lieu Paris et le département 75, l’estimation diffère d’un facteur 4, à 2 506 lignes :

EXPLAIN (ANALYZE)
SELECT * FROM services_big
WHERE localisation='Paris'
AND departement=75;

-------------------------------------------------------------------
 Seq Scan on services_big (cost=0.00..886.06 rows=2506 width=28)
                     (actual time=0.032..7.081 rows=10001 loops=1)
   Filter: (((localisation)::text = 'Paris'::text)
            AND (departement = 75))
   Rows Removed by Filter: 30003
 Planning Time: 0.257 ms
 Execution Time: 7.767 ms

Il y a une dépendance fonctionnelle entre ces deux colonnes (être dans le département 75 implique d’être à Paris), mais PostgreSQL ne le sait pas car ses statistiques sont monocolonnes par défaut. Pour avoir des statistiques sur les deux colonnes à la fois, il faut créer un objet statistique dédié :

CREATE STATISTICS stat_services_big (dependencies)
ON localisation, departement
FROM services_big;

ANALYZE services_big;

Après création de l’objet, il ne faut pas oublier de calculer les statistiques. On peut consulter les statistiques générées, qui indiquent bien la corrélation entre les champs 3 et 4 de la table :

SELECT * FROM pg_stats_ext
WHERE statistics_name='stat_services_big' \gx

-[ RECORD 1 ]----------+-----------------------------------------
schemaname             | public
tablename              | services_big
statistics_schemaname  | public
statistics_name        | stat_services_big
statistics_owner       | postgres
attnames               | {localisation,departement}
exprs                  | ø
kinds                  | {f}
inherited              | f
n_distinct             | ø
dependencies           | {"3 => 4": 1.000000, "4 => 3": 1.000000}
most_common_vals       | ø
most_common_val_nulls  | ø
most_common_freqs      | ø
most_common_base_freqs | ø

Ceci fait, on peut de nouveau regarder les estimations :

EXPLAIN (ANALYZE)
SELECT * FROM services_big
WHERE localisation='Paris'
AND departement=75;

-------------------------------------------------------------------
 Seq Scan on services_big (cost=0.00..886.06 rows=10038 width=28)
                     (actual time=0.008..6.249 rows=10001 loops=1)
   Filter: (((localisation)::text = 'Paris'::text)
            AND (departement = 75))
   Rows Removed by Filter: 30003
 Planning Time: 0.121 ms
 Execution Time: 6.849 ms

Cette fois, l’estimation (10 038 lignes) est beaucoup plus proche de la réalité (10 001). Cela ne change rien au plan choisi dans ce cas précis, mais dans certains cas la différence peut être énorme.

4.7.3 Valeurs distinctes (N-Distinct)

CREATE STATISTICS stat_services (ndistinct)
ON couleur, cépage
FROM vins;

• Indique que plusieurs colonnes sont liées
• Utile pour les clauses GROUP BY

Poursuivons l’exemple précédent et prenons le cas d’un regroupement sur une seule colonne :

EXPLAIN (ANALYZE)
SELECT localisation, COUNT(*)
FROM   services_big
GROUP BY localisation ;

------------------------------------------------------------------------
 HashAggregate (cost=886.06..886.10 rows=4 width=14)
          (actual time=12.925..12.926 rows=4 loops=1)
   Group Key: localisation
   Batches: 1  Memory Usage: 24kB
   -> Seq Scan on services_big (cost=0.00..686.04 rows=40004 width=6)
                           (actual time=0.010..2.779 rows=40004 loops=1)
 Planning Time: 0.162 ms
 Execution Time: 13.033 ms

L’estimation du nombre de lignes pour un regroupement sur une colonne est très bonne. Testons à présent un regroupement sur deux colonnes :

EXPLAIN (ANALYZE)
SELECT localisation, departement, COUNT(*)
FROM services_big
GROUP BY localisation, departement;

-------------------------------------------------------------------------
 HashAggregate (cost=986.07..986.23 rows=16 width=18)
           (actual time=15.830..15.831 rows=4 loops=1)
   Group Key: localisation, departement
   Batches: 1  Memory Usage: 24kB
   -> Seq Scan on services_big (cost=0.00..686.04 rows=40004 width=10)
                           (actual time=0.005..3.094 rows=40004 loops=1)
 Planning Time: 0.102 ms
 Execution Time: 15.860 ms

Là aussi, on constate un facteur d’échelle de 4 entre l’estimation (16 lignes) et la réalité (4). Un objet statistique peut fortement aider :

DROP STATISTICS IF EXISTS stat_services_big;

CREATE STATISTICS stat_services_big (ndistinct)
ON localisation, departement
FROM services_big;

ANALYZE services_big ;

Les nouvelles statistiques indiquent 4 valeurs distinctes dans le regroupement des deux colonnes, du moins selon l’échantillon :

SELECT * FROM pg_stats_ext
WHERE statistics_name='stat_services_big' \gx

-[ RECORD 1 ]----------+---------------------------
schemaname             | public
tablename              | services_big
statistics_schemaname  | public
statistics_name        | stat_services_big
statistics_owner       | postgres
attnames               | {localisation,departement}
exprs                  | ø
kinds                  | {d}
inherited              | f
n_distinct             | {"3, 4": 4}
dependencies           | ø
most_common_vals       | ø
most_common_val_nulls  | ø
most_common_freqs      | ø
most_common_base_freqs | ø

EXPLAIN (ANALYZE)
SELECT localisation, departement, COUNT(*)
FROM services_big
GROUP BY localisation, departement;

-------------------------------------------------------------------------
 HashAggregate (cost=986.07..986.11 rows=4 width=18)
          (actual time=14.351..14.352 rows=4 loops=1)
   Group Key: localisation, departement
   Batches: 1  Memory Usage: 24kB
   -> Seq Scan on services_big (cost=0.00..686.04 rows=40004 width=10)
                           (actual time=0.013..2.786 rows=40004 loops=1)
 Planning Time: 0.305 ms
 Execution Time: 14.413 ms

L’estimation est bien meilleure grâce à l’ajout de statistiques N-Distinct spécifiques aux deux colonnes.

4.7.4 Most Common Values (MCV)

CREATE STATISTICS stat_services (mcv)
ON couleur, cépage
FROM vins;

• Même principe que pour une seule colonne
• Supporte les opérateurs <, <=, > et >=

Les statistiques multicolonnes de type MCV (most common values) fonctionnent de la même manière que pour une seule colonne. Les valeurs les plus fréquentes et leur fréquence d’occurrence sont calculées en fonction du nombre total de lignes et du contenu de l’échantillon.

Un des intérêts est de mieux estimer le nombre de lignes à partir d’un prédicat utilisant les opérations < et >. Voici un exemple :

DROP STATISTICS IF EXISTS stat_services_big;

EXPLAIN (ANALYZE)
SELECT *
FROM services_big
WHERE localisation='Paris'
AND departement > 74 ;

---------------------------------------------------------------------------
  Seq Scan on services_big (cost=0.00..886.06 rows=2546 width=28)
                            (actual time=0.031..19.569 rows=10001 loops=1)
   Filter: ((departement > 74)
            AND ((localisation)::text = 'Paris'::text))
   Rows Removed by Filter: 30003
 Planning Time: 0.186 ms
 Execution Time: 21.403 ms

Il y a donc une erreur d’un facteur 4 (2 546 lignes estimées contre 10 001 réelles) que l’on peut corriger :

CREATE STATISTICS stat_services_big (mcv)
ON localisation, departement
FROM services_big;

ANALYZE services_big ;

Les statistiques calculées sur l’échantillon sont celles-ci :

SELECT * FROM pg_stats_ext
WHERE statistics_name='stat_services_big' \gx

-[ RECORD 1 ]----------+-------------------------------------------------
schemaname             | public
tablename              | services_big
statistics_schemaname  | public
statistics_name        | stat_services_big
statistics_owner       | postgres
attnames               | {localisation,departement}
exprs                  | ø
kinds                  | {m}
inherited              | f
n_distinct             | ø
dependencies           | ø
most_common_vals       | { {Nantes,44},{Rennes,40},{Limoges,52},{Paris,75} }
most_common_val_nulls  | { {f,f},{f,f},{f,f},{f,f} }
most_common_freqs      | {0.2523,0.2497,0.2495,0.2485}
most_common_base_freqs | {0.06365529000000002,0.062350090000000004,  0.06225025,0.06175225}

EXPLAIN (ANALYZE)
SELECT *
FROM services_big
WHERE localisation='Paris'
AND departement > 74;

---------------------------------------------------------------------------
 Seq Scan on services_big (cost=0.00..886.06 rows=10030 width=28)
                           (actual time=0.017..18.092 rows=10001 loops=1)
   Filter: ((departement > 74)
            AND ((localisation)::text = 'Paris'::text))
   Rows Removed by Filter: 30003
 Planning Time: 0.337 ms
 Execution Time: 18.907 ms

On peut créer les trois types de statistiques en un ordre :

CREATE STATISTICS stat_services_big
ON localisation, departement
FROM services_big;

4.8 Statistiques étendues

4.8.1 Statistiques sur les expressions

CREATE STATISTICS employe_big_extract
ON extract('year' FROM date_embauche)
FROM employes_big;

• Pas créées par défaut
• Résout le problème des statistiques difficiles à estimer
• ANALYZE après la création
• Vue pg_stats_ext_exprs

Il est possible de créer un objet statistique sur des expressions. Les statistiques sur des expressions permettent de résoudre le problème des estimations sur les résultats de fonctions ou d’expressions. C’est un problème récurrent et impossible à résoudre sans statistiques dédiées.

On voit dans cet exemple que les statistiques pour l’expression extract('year' from date_embauche) sont erronées :

EXPLAIN SELECT * FROM employes_big
WHERE extract('year' from date_embauche) = 2006 ;

---------------------------------------------------------------
 Gather (cost=1000.00..9552.15 rows=2495 width=40)
   Workers Planned: 2
   -> Parallel Seq Scan on employes_big
        (cost=0.00..8302.65 rows=1040 width=40)
        Filter: (date_part('year'::text,
                 (date_embauche)::timestamp without time zone)
                 = '2006'::double precision)

En effet, l’optimiseur n’a aucune idée des valeurs qui vont sortir de la fonction extract, même en ayant des statistiques sur date_embauche (c’est pourtant évident pour un humain ici, mais ce n’est pas le cas général). Le planificateur applique donc généralement un « forfait », ici 0,5 % des lignes satisfaisant le critère. Quelques fonctions possèdent une « fonction support » annexe qui renseigne l’optimiseur sur la cardinalité.

L’ordre suivant crée des statistiques supplémentaires sur les résultats de l’expression. Les résultats sont calculés pour un échantillon de lignes et collectés en même temps que les statistiques monocolonnes habituelles. Il ne faut pas oublier de lancer manuellement la collecte la première fois :

CREATE STATISTICS employe_big_extract
ON extract('year' FROM date_embauche)
FROM employes_big;

ANALYZE employes_big;

Les estimations du plan sont désormais correctes :

EXPLAIN SELECT * FROM employes_big
WHERE extract('year' from date_embauche) = 2006 ;

---------------------------------------------------------------------
 Seq Scan on employes_big (cost=0.00..12149.22 rows=498998 width=40)
   Filter: (EXTRACT(year FROM date_embauche) = '2006'::numeric)

Cet objet statistique apparaît dans psql dans un \d employes_big :

…
Objets statistiques :
    "public.employe_big_extract"
    ON EXTRACT(year FROM date_embauche) FROM employes_big

Calculer ces statistiques aurait aussi été possible indirectement, en créant un index fonctionnel sur l’expression, ce qui entraîne aussi le calcul de statistiques dédiées lors du prochain ANALYZE. Or l’index fonctionnel n’a pas toujours d’intérêt : dans l’exemple précédent, presque toutes les dates d’embauche sont en 2006. L’index ne serait donc pas utilisé alors qu’il est peut-être lourd à créer et maintenir.

Ne pas confondre ces statistiques sur les résultats d’une fonction avec les paramètres que l’on peut poser sur une fonction, comme ROWS (qui précise combien de lignes une fonction va retourner) ou COSTS (qui modifie le coût d’utilisation d’une fonction). Tous ces moyens sont complémentaires.

4.8.2 Catalogues pour les statistiques étendues

Vues disponibles :

• pg_stats_ext
• pg_stats_ext_exprs

Les statistiques étendues sont stockées dans les tables pg_statistic_ext et pg_statistic_ext_data ; la vue pg_stats_ext permet de consulter ces informations de manière plus lisible.

SELECT * FROM pg_stats_ext \gx

-[ RECORD 1 ]----------+-----------------------------------------
schemaname             | public
tablename              | employes_big
statistics_schemaname  | public
statistics_name        | employe_big_extract
statistics_owner       | postgres
attnames               |
exprs                  | {"EXTRACT(year FROM date_embauche)"}
kinds                  | {e}
inherited              | f
n_distinct             |
dependencies           |
most_common_vals       |
most_common_val_nulls  |
most_common_freqs      |
most_common_base_freqs |
-[ RECORD 2 ]----------+-----------------------------------------
schemaname             | public
tablename              | services_big
statistics_schemaname  | public
statistics_name        | stat_services_big
statistics_owner       | postgres
attnames               | {localisation,departement}
exprs                  |
kinds                  | {m}
inherited              | f
n_distinct             |
dependencies           |
most_common_vals       | { {Paris,75},{Limoges,52},{Rennes,40},{Nantes,44} }
most_common_val_nulls  | { {f,f},{f,f},{f,f},{f,f} }
most_common_freqs      | {0.2512,0.25116666666666665,0.24886666666666668,  0.24876666666666666}
most_common_base_freqs | {0.06310144,0.06308469444444444,0.061934617777777784,  0.06188485444444444}

On voit qu’il n’y a pas d’informations détaillées sur les statistiques sur expression (première ligne). Elles sont visibles dans pg_stats_ext_exprs :

SELECT * FROM pg_stats_ext_exprs \gx

-[ RECORD 1 ]----------+---------------------------------
schemaname             | public
tablename              | employes_big
statistics_schemaname  | public
statistics_name        | employe_big_extract
statistics_owner       | postgres
expr                   | EXTRACT(year FROM date_embauche)
inherited              | f
null_frac              | 0
avg_width              | 8
n_distinct             | 2
most_common_vals       | {2006}
most_common_freqs      | {0.9999667}
histogram_bounds       |
correlation            | 1
most_common_elems      |
most_common_elem_freqs |
elem_count_histogram   |

4.9 COLLECTE DES STATISTIQUES

4.9.1 Quand collecter les statistiques?

• À la demande
  • ANALYZE
  • VACUUM ANALYZE
  • vacuumdb –analyze / –analyze-only / –missing-stats-only
• Automatiquement
  • processus autovacuum
  • paramètres autovacuum_analyze_*
• Suivi :
  • pg_stat_user_tables

ANALYZE est l’ordre SQL permettant de mettre à jour les statistiques sur les données. Il peut être lancé de trois manières différentes:

4.9.1.1 Ordre ANALYZE

La commande ANALYZE lance l’échantillonnage:

ANALYZE nom_table1, nom_table2 ;         -- table par table
ANALYZE (VERBOSE) ;                      -- toute la base, avec les détails

Un lancement manuel est conseillé après toute modification lourde, entre les étapes d’un batch, voire périodiquement. Commande vacuumdb La commande système vacuumdb s’exécute depuis le shell, et génère:

# Nettoyage (VACUUM) et analyze des tables

vacuumdb –analyze –echo

# ANALYZE uniquement

vacuumdb –analyze-only –echo

# ANALYZE uniquement pour les tables sans statistiques

vacuumdb –missing-stats-only –echo

autovacuum Le processus autovacuum assure certaines opérations de maintenance des tables en tâche de fond, notamment le nettoyage des lignes mortes des tables et le rafraîchissement des statistiques sur les données. Nous en parlerons plus loin. Suivi La vue pg_stat_user_tables contient, entre autres et pour chaque table:

• last_analyze : date et heure de la dernière commande ANALYZE manuelle ou scriptée;
• last_autoanalyze : date et heure du dernier ANALYZE lancé par le démon autovacuum .

SELECT relname, last_analyze, last_autoanalyze

FROM pg_stat_user_tables
WHERE relname = 'service_counters_94' \gx

4.9.2 ANALYZE

ANALYZE; -- base entière
ANALYZE pgbench_tellers; -- une table
ANALYZE pgbench_accounts (abalance) ; -- une colonne

• Un échantillon de table → statistiques
  • Table vide : conserve les anciennes statistiques
  • Nouvelle table : valeur par défaut

Sans argument, l’ordre ANALYZE se fait sur la base complète. Si un ou plusieurs arguments sont donnés, ils doivent correspondre aux noms des tables à analyser (en les séparant par des virgules). Il est même possible d’indiquer les colonnes à traiter. Cette instruction va exécuter les calculs de statistiques que nous avons évoqués plus haut. Elle ne va pas lire la table entière, mais seulement un échantillon, sur lequel chaque colonne sera traitée pour récupérer le pourcentage de valeurs NULL, les valeurs les plus fréquentes, leur fréquence, l’histogramme des valeurs… Toutes ces informations sont stockées dans le catalogue système nommé pg_statistics, accessible par la vue pg_stats, ou les tables pg_stats_ext et pg_stats_ext_exprs, comme vu précédemment.

Dans le cas d’une table vide, les anciennes statistiques sont conservées. S’il s’agit d’une nouvelle table, les statistiques sont initialement vides. Lors de la planification, une table vide est bien considérée comme telle au niveau de son nombre de lignes, mais avec 10 blocs au minimum. Pour les versions antérieures, une nouvelle table (nouvelle dans le sens CREATE TABLE mais aussi VACUUM FULL et TRUNCATE ) n’est jamais considérée vide par l’optimiseur, qui utilise alors des valeurs par défaut dépendant de la largeur moyenne d’une ligne et d’un nombre arbitraire de blocs.

Jusque PostgreSQL 16 inclus, seuls le propriétaire de la table et le superutilisateur peuvent lancer

ANALYZE sur une table. À partir de PostgreSQL 17, un utilisateur de maintenance dédié, sans droit de lecture sur les tables, peut lancer la commande, soit sur toutes les tables s’il a le rôle pg_maintain, soit sur les tables où on lui aura octroyé un GRANT MAINTAIN. Ce droit de maintenance couvre aussi VACUUM, CLUSTER et REINDEX, entre autres.

4.9.3 Fréquence d’analyse

• Dépend principalement de la fréquence des requêtes DML
• Le processus autovacuum fait l’ ANALYZE mais…
  • pas sur les tables temporaires
  • pas assez rapidement parfois
• Planification au niveau système avec cron
  • psql -c “ANALYZE”
  • ou vacuumdb –analyze-only

Les statistiques doivent être mises à jour fréquemment. La fréquence exacte dépend surtout de la fréquence des requêtes d’insertion, de modification ou de suppression des lignes des tables. Planifier un ANALYZE tous les jours par prudence est envisageable.

Sans statistiques à jour, le choix du planificateur a un fort risque d’être mauvais. Il est donc important que les statistiques soient mises à jour régulièrement.

L’exécution périodique peut se faire avec cron (ou les tâches planifiées sous Windows) en utilisant l’option –analyze-only de vacuumdb. Ces deux ordres sont équivalents:

vacuumdb –analyze-only -t matable -d mabase

psql -c "ANALYZE matable" -d mabase

(En toute rigueur, vacuumdb ajoute une commande VACUUM (ONLY_DATABASE_STATS) depuis PostgreSQL 16 pour limiter la fragmentation des tables système, ce qui est généralement à conseiller.) Le démon autovacuum effectue aussi des ANALYZE. Dans beaucoup de cas, il suffit de se reposer sur lui. L’ autovacuum se déclenche quand un certain pourcentage de lignes ont été modifiées depuis le dernier rafracîchissement, soit 10 % par défaut. Ce seuil est très souvent réduit pour les grosses tables. Pour les détails et le paramétrage de l’ autovacuum, voir le module M5 - VACUUM & autovacuum 5. Il faut connaître ses particularités liées aux statistiques: 5 https://dali.bo/m5_html

• L’autovacuum a sa propre connexion à la base. Il ne peut donc pas voir les tables temporaires appartenant aux autres sessions, et ne sera donc pas capable de mettre à jour leurs statistiques.

Il faut donc lancer manuellement ANALYZE sur les tables temporaires dans la session qui les a créées.

• Après une insertion ou une mise à jour massive, autovacuum ne va pas lancer un ANALYZE immédiatement. En effet, il ne cherche les tables à traiter que toutes les minutes (par défaut).

Si, après la mise à jour massive, une requête est immédiatement exécutée, il y a de fortes chances qu’elle s’exécute avec des statistiques obsolètes. Il est préférable dans ce cas de lancer un ANALYZE manuel sur la ou les tables concernées juste après l’insertion ou la mise à jour massive. Ce cas est typique des batchs.

• Les grandes tables souffrent souvent de mises à jour trop peu fréquentes (par défaut, 10 % des lignes de la table doivent être modifié pour déclencher automatiquement un ANALYZE ).

Pour des mises à jour plus régulières, il est assez fréquent de réduire la valeur du paramètre autovacuum_analyze_scale_factor de la table.

4.10 PROBLÈMES LES PLUS COURANTS

• Statistiques obsolètes
• Opérations ponctuelles (batch, import de données)
• Dépendances fonctionnelles

Par défaut, PostgreSQL calcule les statistiques régulièrement grâce au processus autovacuum, une colonne à la fois. Ceci peut avoir ses limites lors de chargement en masse de données ou quand des colonnes sont fortement corrélées entre elles.

4.10.1 Statistiques obsolètes

Les statistiques sont-elles à jour?

• Traitement lourd
  • faire tout de suite ANALYZE
• Table trop grosse
  • régler l’échantillonnage
  • régler l’autovacuum sur cette table
• Retard de mise à jour suite à crash ou restauration

Il est fréquent que les statistiques ne soient pas à jour. Il peut y avoir plusieurs causes. Gros chargement: Cela arrive souvent après le chargement massif d’une table ou une mise à jour massive sans avoir fait une nouvelle collecte des statistiques à l’issue de ces changements. En effet, bien qu’autovacuum soit présent, il peut se passer un certain temps entre le moment où le traitement est fait et le moment où autovacuum déclenche une collecte de statistiques. À fortiori si le traitement complet est imbriqué dans une seule transaction. On utilisera l’ordre ANALYZE table pour déclencher explicitement la collecte des statistiques après un tel traitement. Notamment, un traitement batch devra comporter des ordres ANALYZE juste après les ordres SQL qui modifient fortement les données:

COPY table_travail FROM '/tmp/fichier.csv';
ANALYZE table_travail;
SELECT * FROM table_travail;

Volumétrie importante: Un autre problème qui peut se poser avec les statistiques concerne les tables de très forte volumétrie. Dans certains cas, l’échantillon de données ramené par ANALYZE n’est pas assez précis pour donner à l’optimiseur de PostgreSQL une vision suffisamment juste des données. Il choisira alors de mauvais plans d’exécution.

Il est possible d’augmenter la taille de l’échantillon de données analysées, ainsi que la précision des statistiques, pour les colonnes où cela est important, à l’aide de cet ordre vu plus haut:

ALTER TABLE nom_table ALTER nom_colonne SET STATISTICS valeur;

Autre problème courant avec les grosses tables: l’ autovacuum se base par défaut sur la proportion de lignes modifiées par rapport à celles existantes pour savoir s’il doit déclencher un ANALYZE (10 % par défaut). Au fil du temps, beaucoup de tables grossissent en accumulant des lignes statiques. À volume d’activité constante, les lignes actives représentent alors une proportion de plus en plus faible et l’ autovacuum se déclenche de moins en moins souvent. Il est courant de descendre la valeur de autovacuum_analyze_scale_factor pour compenser. On peut aussi chercher à isoler les données statiques dans leur partition. Perte des statistiques d’activité après un arrêt brutal: Le fonctionnement du collecteur des statistiques d’activité implique qu’un arrêt brutal de PostgreSQL les réinitialisent. (Il s’agit des statistiques sur les lignes insérées ou modifiées, que l’on trouve notamment dans pg_stat_user_tables, pas des statistiques sur les données, qui sont bien préservées.) Elles ne sont pas directement utilisées par le planificateur, mais cette réinitialisation peut mener à un retard dans l’activité de l’autovacuum et la mise à jour des statistiques des données. Après un plantage, un arrêt en mode immédiat ou une restauration physique, il est donc conseillé de relancer un ANALYZE général (et même un VACUUM ensuite si possible).

4.10.2 Colonnes corrélées

SELECT * FROM corr1 WHERE c1=1 AND c2=2

• Si c1 = 1 pour 20 %des lignes
• et c2 = 2 pour 10 %des lignes
  • Alors le planificateur calcule : 2 %des lignes (20 % × 10 %)
  • Mais en réalité?
  • Pour corriger :

CREATE STATISTICS corr1_c1_c2 ON c1,c2 FROM corr1 ;

Avec les statistiques par défaut, le planificateur sait que l’estimation pour c1=1 est de 20 % et que l’estimation pour c2=2 est de 10 %. Par contre, il n’a aucune idée de l’estimation pour c1=1 AND c2=2. Faute de mieux, il multiplie les deux estimations et obtient 2 % (20 % × 10 %), soit environ 2000 lignes, au lieu de 0.

ANALYZE corr1;
EXPLAIN SELECT * FROM corr1 WHERE c1 = 1 AND c2 = 2 ;

QUERY PLAN

———————————————————————————- Bitmap Heap Scan on corr1 (cost=28.70..634.57 rows=1991 width=12) Recheck Cond: ((c1 = 1) AND (c2 = 2)) -> Bitmap Index Scan on corr1_c1_c2_idx (cost=0.00..28.20 rows=1991 width=0) Index Cond: ((c1 = 1) AND (c2 = 2))

Nous avons vu que la création de statistiques multicolonnes n’est pas automatique, il faut créer un objet statistique avec l’ordre CREATE STATISTICS:

CREATE STATISTICS corr1_c1_c2 ON c1,c2 FROM corr1 ;
ANALYZE corr1 ;    -- ne pas oublier !

Dans ce cas précis, les meilleures statistiques permettent une meilleure estimation (une ligne), et donc de basculer du Bitmap Scan à un Index Scan plus léger:

EXPLAIN SELECT * FROM corr1 WHERE c1 = 1 AND c2 = 2 ;

QUERY PLAN

—————————————————————————— Index Scan using corr1_c1_c2_idx on corr1 (cost=0.29..8.22 rows=1 width=12) Index Cond: ((c1 = 1) AND (c2 = 2))

4.11 EXPORT/IMPORT DE STATISTIQUES

• PostgreSQL 18

pg_dump -t demo --statistics-only

SELECT * FROM pg_catalog.pg_restore_relation_stats(
        'version', '180003'::integer,
        'schemaname', 'public',
        'relname', 'demo_bi',
        'relpages', '7210'::integer,
        'reltuples', '1e+06'::real,
        'relallvisible', '0'::integer,
        'relallfrozen', '0'::integer
);

À partir de la version 18, pg_dump –statistics permet d’exporter les statistiques des tables et vues matérialisées. Un exemple avec l’export des statitiques de la table demo:

pg_dump -t demo --statistics-only

Pour chaque table apparaissent en sortie deux appels de fonction. Le premier renseigne des statistiques présentes dans pg_class (taille en ligne et blocs, partie gelée ou visible). Le second se répète pour chaque attribut et contient l’histogramme, les valeurs les plus courantes, la corrélation…, c’està-dire ce qui se retrouve dans pg_statistics:

SELECT * FROM pg_catalog.pg_restore_relation_stats(
        'version', '180003'::integer,
        'schemaname', 'public',
        'relname', 'demo_bi',
        'relpages', '7210'::integer,
        'reltuples', '1e+06'::real,
        'relallvisible', '0'::integer,
        'relallfrozen', '0'::integer
);

SELECT * FROM pg_catalog.pg_restore_attribute_stats(
        'version', '180001'::integer,
        'schemaname', 'public',
        'relname', 'demo',
        'attname', 'j',
        'inherited', 'f'::boolean,
        'null_frac', '0'::real,
        'avg_width', '4'::integer,
        'n_distinct', '10'::real,
        'most_common_vals', '{2,1,9,5,8,0,7,3,6,4}'::text,
        'most_common_freqs',
         '{0.10236666,0.10146666,0.10123333,0.1008,0.09983333,0.099366665,…}'::real[],
        'correlation', '0.09628114'::real
);

Pour chaque index de la table apparaît un appel similaire à pg_restore_relation_stats.

Pour restaurer les statistiques d’un objet, il suffit d’appeler ces fonctions avec les paramètres indiquées. Il y a un numéro de version: en effet, le client pg_dump en version 18 sait exporter les statistiques depuis une instance d’une version précédente, et certains champs peuvent être vides. Par contre, il n’y a pas moyen de restaurer ces statistiques sur une instance antérieure à la 18 (les fonctions n’y existent pas). Limites: Les statistiques étendues ne sont pas incluses, pour des raisons techniques. (Peut-être dans une version future?) Les définitions des objets statistiques sont bien sûr présentes dans la sauvegarde (ordres CREATE STATISTICS ) mais il faut un ANALYZE explicite pour les remplir.

Des statistiques importées ne sont pas pérennes: tôt ou tard elles seront écrasées par un prochain ANALYZE.

Peut-on utiliser les fonctions pour modifier les statistiques d’une table? Ce peut être pratique pour des tests, ou à titre pédagogique, ou pour faire des tests sur une base en réalité presque vide. Par exemple, on peut importer les statistiques d’une base de production de 10 To dans une base de développement de 10 Go pour tenter de reproduire le comportement des requêtes en production. Par contre, « figer » des statistiques en production avec cet outil est déconseillé. Les statistiques modifiées seront remplacées lors du prochain ANALYZE. Ou alors, il faut inhiber l’ ANALYZE automatique sur la table, ce qui est fortement déconseillé. Sur le fond, il n’est pas normal que des statistiques fausses donnent un meilleur plan que des statistiques fraîches, il y a un problème à régler.

4.12 CONCLUSION

• Un échantillonnage des données très efficace…
• …mais pas parfait!
• Il faut bien comprendre son fonctionnement
• Possibilité d’ajuster la collecte des statistiques si besoin

4.13 RÉFÉRENCES

• Plongée profonde dans les plans d’exécution
  • Frédéric Yhuel, PGSessions 17, février 2024
  • https://youtu.be/xneDEIzBw3I
• A Deep Dive into Postgres Statistics
  • Louise Grandjonc Leinweber, PGConf.EU, octobre 2024
  • https://youtu.be/ApAClPFJ_rU

4.13.1 Questions

N’hésitez pas, c’est le moment!

4.14 QUIZ

https://dali.bo/m6_quiz

4.15 TRAVAUX PRATIQUES

La version en ligne des solutions de ces TP est disponible sur https://dali.bo/m6_solutions.

4.15.1 Préambule

Ce TP se base sur la configuration par défaut de PostgreSQL, sauf précision contraire.

• Préciser \timing dans psql pour afficher les temps d’exécution de la recherche.

• Pour rendre les plans plus lisibles, désactiver le JIT et le parallélisme :

SET jit TO off ;
SET max_parallel_workers_per_gather TO 0 ;

• Pour mettre en évidence les effets de cache, lancer plusieurs fois les requêtes. Dans psql , il est possible de les rappeler avec \g , ou la touche flèche haut du clavier.

Les valeurs (taille, temps d’exécution) varieront à cause de plusieurs critères:

• les machines sont différentes;
• le jeu de données peut avoir partiellement changé depuis la rédaction du TP ;

4.15.2 Vue pg_stats

But: Comprendre les informations lisibles dans pg_stats

Pour cet exercice, nous allons étudier deux tables dans la base hr modélisant un service du personnel. Si vous n’avez pas déjà la base hr, son script (de 31 ko) peut être récupéré et installé ainsi:

curl -kL https://dali.bo/tp_hr -o hr.sql
createdb hr         # Création de la base
psql -X1 hr < hr.sql

• Les statistiques ont-elles été calculées? (utiliser la vue pg_stat_user_tables )

• Rafraîchir les statistiques sur les tables employees et locations .

Retrouvons les informations calculées par ANALYZE pour la table employees.

• Comparer le nombre réel de lignes dans la table employees avec l’estimation faite par ANALYZE . (utiliser la vue pg_class )

• Quel est le taux de valeurs nulles pour la colonne commission_pct ?

• Quelles sont les valeurs les plus fréquentes pour la colonne salary ?

• Afficher l’histogramme pour la colonne salary .

• Quelle est l’estimation du nombre de valeurs distinctes de job_id ? Cette estimation est-elle juste?

• Selon quelle colonne les données d’ employees sont-elles le mieux ordonnées sur disque ?

La base hr dispose de nombreux commentaires sur les objets qui la composent. On peut les voir avec les commandes \d+ et \dt+ de psql. Ces commentaires sont de tailles variables et stockés dans la table pg_description du catalogue système.

• Quelle est la largeur moyenne de la colonne description de pg_description ?

4.15.3 Corrélation entre colonnes

But: Optimiser une requête avec corrélations

Nous allons utiliser deux tables listant des colis qui doivent être distribués dans des villes. Si vous ne l’avez pas déjà, la base correlations (dump de 51 Mo pour 865 Mo sur le disque au final) peut être téléchargée et restaurée ainsi:

curl -kL https://dali.bo/tp_correlations -o correlations.dump
createdb correlations
pg_restore -d correlations correlations.dump

• Ne pas oublier les opérations habituelles après un chargement de données.

Dans la table villes, on trouve les villes et leur code postal. Ces colonnes sont très fortement corrélées, mais pas identiques:

• plusieurs villes peuvent partager le même code postal;

• une ville peut avoir plusieurs codes postaux;

• des villes de départements différents ont le même nom, mais pas le même code postal.

• Activer la mesure des durées des I/O dans la session, désactiver le JIT et le parallélisme.

• Dans la requête suivante, quelle est la stratégie principale?

• Est-elle efficace?

-- Cette requête liste les colis d'une liste de villes précisées
EXPLAIN (ANALYZE,BUFFERS)
SELECT *
FROM   colis
WHERE id_ville IN (
       SELECT id_ville
       FROM    villes
       WHERE   localite = 'PARIS'
         AND   codepostal LIKE '75%'
);

• Quelles sont les volumétries attendues et obtenues?

• Comparer avec un filtre uniquement sur la ville ou le département.

• Quel est le problème fondamental?

• Tenter d’améliorer l’estimation avec CREATE STATISTICS .

• Créer une fonction SQL comportant les deux critères : les statistiques associées sont-elles justes?

• Les statistiques améliorées mènent-elles à un résultat plus rapide?

NB: Cet exercice sur les corrélations entre colonnes est malheureusement peu représentatif.

4.16 TRAVAUX PRATIQUES (SOLUTIONS)

4.16.1 Préambule

Ce TP se base sur la configuration par défaut de PostgreSQL, sauf précision contraire.

• Préciser \timing dans psql pour afficher les temps d’exécution de la recherche.

• Pour rendre les plans plus lisibles, désactiver le JIT et le parallélisme :

SET jit TO off ;
SET max_parallel_workers_per_gather TO 0 ;

• Pour mettre en évidence les effets de cache, lancer plusieurs fois les requêtes. Dans psql , il est possible de les rappeler avec \g , ou la touche flèche haut du clavier.

Les valeurs (taille, temps d’exécution) varieront à cause de plusieurs critères:

• les machines sont différentes;
• le jeu de données peut avoir partiellement changé depuis la rédaction du TP ;

4.16.2 Vue pg_stats

Pour cet exercice, nous allons étudier deux tables dans la base hr modélisant un service du personnel. Si vous n’avez pas déjà la base hr, son script (de 31 ko) peut être récupéré et installé ainsi:

curl -kL https://dali.bo/tp_hr -o hr.sql
createdb hr         # Création de la base
psql -X1 hr < hr.sql

• Les statistiques ont-elles été calculées? (utiliser la vue pg_stat_user_tables )

La commande \d+ pg_stat_user_tables dans psql nous permet d’identifier les colonnes que nous devons chercher: View “pg_catalog.pg_stat_user_tables”

                          View "pg_catalog.pg_stat_user_tables"
       Column        |           Type           | Collation | Nullable | Default | Storage | ...
---------------------+--------------------------+-----------+----------+---------+---------
 relid               | oid                      |           |          |         | plain
 schemaname          | name                     |           |          |         | plain

 relname             | name                     |           |          |         | plain
 seq_scan            | bigint                   |           |          |         | plain
 seq_tup_read        | bigint                   |           |          |         | plain
 idx_scan            | bigint                   |           |          |         | plain
 idx_tup_fetch       | bigint                   |           |          |         | plain
 n_tup_ins           | bigint                   |           |          |         | plain
 n_tup_upd           | bigint                   |           |          |         | plain
 n_tup_del           | bigint                   |           |          |         | plain
 n_tup_hot_upd       | bigint                   |           |          |         | plain
 n_live_tup          | bigint                   |           |          |         | plain
 n_dead_tup          | bigint                   |           |          |         | plain
 n_mod_since_analyze | bigint                   |           |          |         | plain
 n_ins_since_vacuum  | bigint                   |           |          |         | plain
 last_vacuum         | timestamp with time zone |           |          |         | plain
 last_autovacuum     | timestamp with time zone |           |          |         | plain
 last_analyze        | timestamp with time zone |           |          |         | plain
 last_autoanalyze    | timestamp with time zone |           |          |         | plain
 vacuum_count        | bigint                   |           |          |         | plain
 autovacuum_count    | bigint                   |           |          |         | plain
 analyze_count       | bigint                   |           |          |         | plain
 autoanalyze_count   | bigint                   |           |          |         | plain

(…)

L’information se trouve dans les colonnes last_analyze et last_autoanalyze. La requête suivante nous permet de vérifier le calcul des statistiques pour chaque table: SELECT relname, last_analyze, last_autoanalyze

FROM pg_stat_user_tables;

(7 rows)

Dans notre cas, les statistiques n’ont pas encore été calculées.

• Rafraîchir les statistiques sur les tables employees et locations .

Lançons la commande ANALYZE sur les tables employees et locations:

ANALYZE employees, locations;
ANALYZE

La vue pg_stat_user_tables nous permet de vérifier l’opération:

SELECT relname, last_analyze, last_autoanalyze

FROM pg_stat_user_tables;

(7 rows)

Dans notre cas, nous remarquons qu’autoanalyze est passé sur employees peu avant la collecte manuelle. Losrqu’on consulte la vue pg_stats on remarque que les statistiques sont présentes pour employees, mais pas encore pour departments:

SELECT attname FROM pg_stats WHERE tablename='employees';

attname —————- employee_id first_name last_name email phone_number hire_date job_id salary commission_pct manager_id department_id (11 rows)

SELECT attname FROM pg_stats WHERE tablename='departments';

attname ——— (0 row)

Retrouvons les informations calculées par ANALYZE pour la table employees.

• Quel est le taux de valeurs nulles pour la colonne commission_pct ?

SELECT null_frac

FROM pg_stats
WHERE tablename='employees'

AND attname=‘commission_pct’;

null_frac ———– 0.6728972 (1 ligne)

• Comparer le nombre réel de lignes dans la table employees avec l’estimation faite par ANALYZE . (utiliser la vue pg_class )

SELECT reltuples FROM pg_class WHERE relname='employees';

reltuples ———– 107 (1 row)

Le calcul exact se fait avec la fonction count() de SELECT:

SELECT count(*) FROM employees;
count

——- 107 (1 row)

La table étant petite, l’échantillon permet d’avoir une estimation exact de la quantité de lignes.

• Quel est le taux réel de valeurs nulles pour la colonne commission_pct ?

SELECT COUNT() AS count_total, COUNT() - COUNT(commission_pct) AS count_null, (COUNT() - COUNT(commission_pct))::float/COUNT()::float AS null_frac

FROM employees;
count_total | count_null |     null_frac

————-+————+——————–

107 | 72 | 0.6728971962616822 (1 ligne)

Le taux réel de valeurs nulles pour la colonne est égal à l’estimation. Ce qui est normal car la table ne comporte que 107 lignes, soit moins que l’échantillon minimal par défaut de 30000.

• Quelles sont les valeurs les plus fréquentes pour la colonne salary ?

SELECT unnest(most_common_vals::text::int[]) AS most_common_val, unnest(most_common_freqs) AS most_common_freq

FROM pg_stats
WHERE tablename = 'employees'
AND attname = 'salary';

(28 rows)

Le nombre de valeurs recensées est variable en fonction de la taille de l’échantillon et de la répartition des valeurs, la colonne des noms last_name recense moins de valeurs fréquentes dans pg_stats.

SELECT unnest(most_common_vals::text::text[]) AS most_common_val, unnest(most_common_freqs) AS most_common_freq

FROM pg_stats
WHERE tablename = 'employees'

AND attname = ’last_name';

(5 rows)

• Afficher l’histogramme pour la colonne salary .

WITH bounds AS (
SELECT
generate_series(1, array_length(histogram_bounds, 1) - 1) AS bucket_idx,

histogram_bounds::text::numeric[] AS bounds

FROM pg_stats
WHERE tablename = 'employees' AND attname = 'salary'

) SELECT bounds[bucket_idx] AS lower_bound, bounds[bucket_idx + 1] AS upper_bound, (SELECT COUNT(*)

FROM employees
WHERE salary >= bounds[bucket_idx]
AND salary < bounds[bucket_idx + 1]) AS count
FROM bounds;

• Quelle est l’estimation du nombre de valeurs distinctes de commission_pct ?

SELECT attname, n_distinct

FROM pg_stats
WHERE tablename='employees'

AND attname=‘commission_pct’;

(1 row)

La valeur est supérieure à 0, ceci signifie que PostgreSQL estime que le nombre de valeurs distinctes pour commission_pct est décorrélé du nombre total de lignes dans la table.

• Quelle est l’estimation du nombre de valeurs distinctes de last_name ?

SELECT attname, n_distinct

FROM pg_stats
WHERE tablename='employees'

AND attname=‘last_name’;

(1 row)

Ici, la valeur est négative, ceci signifie que PostgreSQL estime que le nombre de valeurs distinctes de last_name est proportionnel au nombre total de lignes de la table. Ici le coefficient de proportionnalité est proche de 1.

• Quelle est l’estimation du nombre de valeurs distinctes de job_id ? Cette estimation est-elle juste?

SELECT attname, n_distinct

FROM pg_stats
WHERE tablename='employees'

AND attname=‘job_id’;

(1 row)

Ici, PostgreSQL estime aussi que le nombre de valeurs distinctes de job_id est proportionnel au nombre total de lignes. C’est juste: un COUNT(*) (ou pg_class.reltuples ) montre qu’il y a 107 lignes dans la table. 107 × 0,17757009 donne 19. Et il y a bien 19 valeurs distinctes de job_id dans la table (comme le montre un COUNT(DISTINCT job_id) ).

• Selon quelle colonne les données d’ employees sont-elles le mieux ordonnées sur disque ?

SELECT attname, correlation FROM pg_stats WHERE tablename='employees';

(11 rows)

L’ordre des données sur disque correspond au tri par clé primaire employee_id. La base hr dispose de nombreux commentaires sur les objets qui la composent. On peut les voir avec les commandes \d+ et \dt+ de psql. Ces commentaires sont de tailles variables et stockés dans la table pg_description du catalogue système.

• Quelle est la largeur moyenne de la colonne description de pg_description ?

SELECT attname, avg_width FROM pg_stats WHERE tablename='pg_description' AND attname='description';

description | 25 (1 row)

Les descriptions (commentaires) présents dans l’instance ont une largeur moyenne de 25 octets. Conclusion Cet exercice nous a fait explorer les vues pg_stats et pg_class, qui révèlent les données essentielles collectées par PostgreSQL, comme la distribution des valeurs, le nombre de lignes ou la taille des tables. Ces informations sont au cœur des décisions de l’optimiseur de requêtes et indispensables pour diagnostiquer les performances et optimiser les requêtes.

4.16.3 Corrélation entre colonnes

• Ne pas oublier les opérations habituelles après un chargement de données.

Si la base cible s’appelle par exemple correlations:

$ pg_restore -d correlations correlations.dump
$ vacuumdb --analyze correlations

• Activer la mesure des durées des I/O dans la session, désactiver le JIT et le parallélisme.

SET track_io_timing TO on;
SET jit TO off ;
SET max_parallel_workers_per_gather TO 0;

• Dans la requête suivante, quelle est la stratégie principale?
  • Est-elle efficace?

– Cette requête liste les colis d’une liste de villes précisées EXPLAIN (ANALYZE,BUFFERS) SELECT *

FROM   colis
WHERE id_ville IN (

SELECT id_ville

FROM    villes
WHERE   localite = 'PARIS'

AND codepostal LIKE ‘75%’ );

Le plan est:

QUERY PLAN
-------------------------------------------------------------------------------

Nested Loop (cost=5.85..13653.51 rows=3275 width=16) (actual time=21.898..3889.603
rows=170802.00 loops=1)

Buffers: shared hit=53091 read=120714
I/O Timings: shared read=929.314

-> Seq Scan on villes (cost=0.00..1209.32 rows=18 width=8) (actual
time=20.711..34.577 rows=940.00 loops=1) Filter: ((codepostal ~~ '75%'::text) AND (localite = 'PARIS'::text))

Rows Removed by Filter: 54015

Buffers: shared hit=63 read=322

I/O Timings: shared read=1.311 -> Bitmap Heap Scan on colis (cost=5.85..689.51 rows=183 width=16) (actual
time=0.332..3.598 rows=181.70 loops=940) Recheck Cond: (id_ville = villes.id_ville) Heap Blocks: exact=170515

Buffers: shared hit=53028 read=120392

I/O Timings: shared read=928.004 -> Bitmap Index Scan on idx_colis_ville (cost=0.00..5.81 rows=183 width=0)
(actual time=0.060..0.060 rows=181.70 loops=940) Index Cond: (id_ville = villes.id_ville) Index Searches: 940

Buffers: shared hit=2755 read=150

I/O Timings: shared read=4.173 Planning:

Buffers: shared hit=5 read=9

I/O Timings: shared read=0.197

Planning Time: 1.551 ms
Execution Time: 4089.338 ms

Le plan est un Nested Loop. Pour chacune des lignes dans villes (obtenues par un Seq Scan), une lecture de colis a lieu (par Bitmap Heap Scan). C’est une boucle extrêmement coûteuse: 940 parcours de colis (1 par id_ville ). De plus les tables et index sont volumineux par rapport au cache, il y a des appels au disque (ou plutôt au cache de l’OS) (indicateurs read ). Ce problème peut se mitiger avec le temps, mais même de longs accès en mémoire cache sont à éviter.

• Quelles sont les volumétries attendues et obtenues?
  • Comparer avec un filtre uniquement sur la ville ou le département.
  • Quel est le problème fondamental?

Le nombre de lignes obtenues (170 802) est plus de 52 fois supérieur à celui attendu (3275). Le problème se propage depuis l’estimation fausse sur villes. PostgreSQL fait ce choix parce qu’il estime que la condition

localite ='PARIS' AND codepostal LIKE '75%'

va ramener 18 enregistrements. En réalité, elle en ramène 940, soit 52 fois plus. Pourquoi PostgreSQL fait-il cette erreur? Les volumétries impliquées sont: SELECT COUNT() AS nb_villes, COUNT() FILTER (WHERE localite=‘PARIS’) AS nb_paris, COUNT() FILTER (WHERE codepostal LIKE ‘75%’) AS nb_75, COUNT() FILTER (WHERE localite=‘PARIS’ AND codepostal LIKE ‘75%’) AS nb_paris_75

FROM villes;

Les statistiques reproduisent à peu près cela (les chiffres peuvent varier légèrement entre des installations à cause du choix de l’échantillon statistique):

EXPLAIN SELECT * FROM villes ;

QUERY PLAN
-------------------------------------------------------------
Seq Scan on villes (cost=0.00..934.55 rows=54955 width=27)

EXPLAIN SELECT * FROM villes WHERE localite='PARIS';

QUERY PLAN
------------------------------------------------------------
Seq Scan on villes (cost=0.00..1071.94 rows=971 width=27)
  Filter: (localite = 'PARIS'::text)

EXPLAIN SELECT * FROM villes WHERE codepostal LIKE '75%';

QUERY PLAN
-------------------------------------------------------------
Seq Scan on villes (cost=0.00..1071.94 rows=1042 width=27)
  Filter: (codepostal ~~ '75%'::text)

L’estimation de la combinaison des deux critères est bien fausse:

EXPLAIN SELECT * FROM villes WHERE localite='PARIS'
                               AND codepostal LIKE '75%';

QUERY PLAN
------------------------------------------------------------------------
Seq Scan on villes (cost=0.00..1209.32 rows=18 width=27)
  Filter: ((codepostal ~~ '75%'::text) AND (localite = 'PARIS'::text))

D’après les statistiques, villes contient 54 955 enregistrements, 971 contenant PARIS (presque 2 %), 1042 commençant par 75 (presque 2 %). Il y a donc 2 % d’enregistrements vérifiant chaque critère (c’est normal, ils sont presque équivalents). PostgreSQL, ignorant qu’il n’y a que Paris dans le département 75, part de l’hypothèse que les colonnes ne sont pas liées, et qu’il y a donc 2 % de 2 % (soit environ 0,04 %) des enregistrements qui vérifient les deux. Si on fait le calcul exact, PostgreSQL croit donc avoir (971/54955)×(1042/54955)×54955 = 18,4 enregistrements qui vérifient le critère complet, ce qui est évidemment faux. Et un plan portant uniquement sur Paris (ou le département 75) a une estimation de volumétrie exacte:

EXPLAIN
   SELECT *
   FROM   colis
   WHERE id_ville IN (
         SELECT id_ville
         FROM   villes
         WHERE  localite = 'PARIS'
   );

QUERY PLAN
-----------------------------------------------------------------------
Hash Join (cost=1084.08..181440.21 rows=176679 width=16)
  Hash Cond: (colis.id_ville = villes.id_ville)
  -> Seq Scan on colis (cost=0.00..154105.75 rows=9999375 width=16)
  -> Hash (cost=1071.94..1071.94 rows=971 width=8)
        -> Seq Scan on villes (cost=0.00..1071.94 rows=971 width=8)
              Filter: (localite = 'PARIS'::text)

• Tenter d’améliorer l’estimation avec CREATE STATISTICS .

Pour calculer les corrélations entre les deux colonnes en question, la syntaxe est:

CREATE STATISTICS villes_localite_codepostal ON localite,codepostal FROM villes ;

Le rafraîchissement n’est pas automatique:

ANALYZE villes ;

Le résultat est-il concluant?

EXPLAIN
SELECT *
FROM    colis
WHERE id_ville IN (
         SELECT id_ville
         FROM   villes
         WHERE  localite = 'PARIS'
           AND  codepostal LIKE '75%'
);

La réponse est non:

QUERY PLAN
-------------------------------------------------------------------------------
Nested Loop (cost=5.85..13653.51 rows=3275 width=16)
  -> Seq Scan on villes (cost=0.00..1209.32 rows=18 width=8)
        Filter: ((codepostal ~~ '75%'::text) AND (localite = 'PARIS'::text))
  -> Bitmap Heap Scan on colis (cost=5.85..689.51 rows=183 width=16)
        Recheck Cond: (id_ville = villes.id_ville)
        -> Bitmap Index Scan on idx_colis_ville (cost=0.00..5.81 rows=183 width=0)
              Index Cond: (id_ville = villes.id_ville)

Dans notre cas les statistiques étendues n’aident pas. Par contre, cela aurait fonctionné avec des départements au lieu des codes postaux, ce qui est un contournement possible. Cette colonne supplémentaire peut être alimentée par trigger ou avec une colonne générée stockée définie ainsi: dept text GENERATED ALWAYS AS (left(codepostal,2) STORED

ou, mieux, avec une fonction tenant compte des départements à trois chiffres.

• Créer une fonction SQL comportant les deux critères : les statistiques associées sont-elles justes?

On peut indexer sur une fonction des deux critères. C’est un pis-aller mais la seule solution sûre. PostgreSQL calculera des statistiques sur le résultat de cette fonction à partir de l’échantillon au lieu de les calculer indirectement.

CREATE FUNCTION test_ville (ville text,codepostal text) RETURNS text
IMMUTABLE LANGUAGE SQL as $$
SELECT ville || '-' || codepostal
$$ ;

CREATE INDEX idx_test_ville ON villes (test_ville(localite , codepostal));

ANALYZE villes;

EXPLAIN
SELECT * FROM colis WHERE id_ville IN (
    SELECT id_ville
    FROM villes
    WHERE test_ville(localite,codepostal) LIKE 'PARIS-75%'
);

QUERY PLAN
-------------------------------------------------------------------------------
Hash Join (cost=1360.59..181716.72 rows=201971 width=16)
  Hash Cond: (colis.id_ville = villes.id_ville)
  -> Seq Scan on colis (cost=0.00..154105.75 rows=9999375 width=16)
  -> Hash (cost=1346.71..1346.71 rows=1110 width=8)
        -> Seq Scan on villes (cost=0.00..1346.71 rows=1110 width=8)
              Filter: (((localite || '-'::text) || codepostal) ~~
                       'PARIS-75%'::text)

On constate qu’avec cette méthode il n’y a plus d’erreur d’estimation (1110 est proche du réel 940). Cette méthode est bien sûr pénible à utiliser, et ne doit donc être réservée qu’aux quelques rares requêtes au comportement pathologique. Quitte à modifier le code, la colonne departement évoquée plus haut est peut-être plus simple et claire.

• Les statistiques améliorées mènent-elles à un résultat plus rapide?

De manière générale, des statistiques à jour aident à avoir un meilleur plan. Mais cela va aussi dépendre de la machine et de son paramétrage! Tout ce TP a été effectué avec les paramètres par défaut, destinés à une machine très modeste:

shared_buffers = 128MB
work_mem = 4MB
random_page_cost = 4
seq_page_cost = 1
effective_cache_size = 4GB

Avec cette configuration, un Hash Join, assez consommateur, sera choisi. Sur une machine avec un SSD (voire juste de bons disques, ou si l’OS joue le rôle de cache), ceci peut être moins rapide que le Nested Loop de la requête d’origine, car l’accès à un bloc de table isolé n’est guère plus coûteux qu’au

sein d’un parcours de table. Pour un SSD, random_page_cost peut être passé à 1, et le Nested Loop a plus de chance de se produire. Conclusion Que peut-on conclure de cet exercice?

• que la ré-écriture est souvent la meilleure des solutions : interrogez-vous toujours sur la façon dont vous écrivez vos requêtes, plutôt que de mettre en doute PostgreSQL a priori;
• que la ré-écriture de requête est souvent complexe
• néanmoins, surveillez un certain nombre de choses :
  • transtypages implicites suspects;
  • jointures externes inutiles ;
  • sous-requêtes imbriquées ;
  • jointures inutiles (données constantes).