# Statistiques pour le planificateur > Statistiques de volumétrie, statistiques sur les données, échantillonnage, dépendances et statistiques étendues. --- Index LLMS : [llms.txt](/fr/llms.txt) --- La première illustration de ce chapitre est la [Figure 4-1](#fig-perf1-p229-01). **Figure 4-1.** Illustration extraite du manuel PERF1\, page PDF 229\. ![Illustration extraite du manuel PERF1\, page PDF 229\.](/images/perf1/manual/perf1-p229-01.png) ## 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 **Exemple 4-1.** Extrait de code ou de sortie du manuel\, page PDF 232\. ```text 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 ). 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* : **Exemple 4-2.** Plan séquentiel avec un filtre peu sélectif\. ```sql EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM employes_big WHERE date_embauche='2006-09-01'; ``` ```text 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 : **Exemple 4-3.** Plan par index avec un filtre sélectif\. ```sql EXPLAIN (ANALYZE, TIMING OFF) SELECT * FROM employes_big WHERE date_embauche='2006-01-01'; ``` ```text 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) **Exemple 4-4.** Statistique N\-Distinct de la colonne date\_embauche\. ```text -[ 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 : **Exemple 4-5.** Lecture des statistiques d’une colonne\. ```sql 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 : **Exemple 4-6.** Forçage de la valeur N\-Distinct\. ```sql ALTER TABLE nom_table ALTER COLUMN nom_colonne SET (n_distinct = ); ``` 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) **Exemple 4-7.** Liste des valeurs les plus courantes\. ```text -[ 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 : **Exemple 4-8.** Contenu de la table articles\. ```sql SELECT * FROM articles; ``` ```text 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 : **Exemple 4-9.** Éléments et fréquences les plus courants\. ```sql 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'; ``` ```text 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 **Exemple 4-10.** Bornes d’histogramme de la colonne prix\. ```text -[ 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 : **Exemple 4-11.** Construction d’un histogramme de démonstration\. ```sql -- 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; ``` ```text -[ 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 : **Exemple 4-12.** Contrôle de l’équilibre d’un histogramme\. ```sql 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; ``` ```text 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 **Exemple 4-13.** Fraction de valeurs nulles\. ```text -[ 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 **Exemple 4-14.** Corrélation entre ordre physique et ordre logique\. ```text -[ 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` : **Exemple 4-15.** Corrélation négative sur le champ stock\. ```sql SELECT schemaname, tablename, attname, correlation FROM pg_stats WHERE tablename = 'produits' AND attname = 'stock' \gx ``` ```text -[ 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 : **Exemple 4-16.** Corrélation proche de zéro\. ```sql SELECT schemaname, tablename, attname, correlation FROM pg_stats WHERE tablename = 'commandes' AND attname = 'date_achat' \gx ``` ```text -[ 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` : **Exemple 4-17.** Tri physique d’une table avec CLUSTER\. ```sql -- 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 ``` ```text -[ RECORD 1 ]----------- schemaname | public tablename | commandes attname | date_achat correlation | 1 ``` Comparons les plans d’exécution avant et après `CLUSTER` : **Exemple 4-18.** Plans avant et après CLUSTER\. ```sql -- Requête EXPLAIN ANALYZE SELECT * FROM commandes WHERE date_achat BETWEEN '2023-06-01' AND '2023-06-30'; -- Plan avant CLUSTER (simplifié) ``` ```text 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 **Exemple 4-19.** Largeur moyenne d’une colonne\. ```text -[ 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 : **Exemple 4-20.** Modification ciblée de l’échantillon\. ```sql ALTER TABLE nom_table ALTER nom_colonne SET STATISTICS ; 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 : **Exemple 4-21.** Syntaxe d’une statistique spécialisée\. ```sql CREATE STATISTICS stat_services () 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) : **Exemple 4-22.** Syntaxe allégée d’une statistique multivariée\. ```sql 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 **Exemple 4-23.** Création d’une statistique de dépendance fonctionnelle\. ```sql 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 : **Exemple 4-24.** Plans avec un filtre sur une seule colonne\. ```sql EXPLAIN (ANALYZE) SELECT * FROM services_big WHERE localisation='Paris'; EXPLAIN (ANALYZE) SELECT * FROM services_big WHERE departement=75; ``` ```text ------------------------------------------------------------------- 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 : **Exemple 4-25.** Plan avec deux colonnes corrélées\. ```sql EXPLAIN (ANALYZE) SELECT * FROM services_big WHERE localisation='Paris' AND departement=75; ``` ```text ------------------------------------------------------------------- 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é : **Exemple 4-26.** Création de la statistique dédiée\. ```sql 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 : **Exemple 4-27.** Statistique de dépendance générée\. ```sql SELECT * FROM pg_stats_ext WHERE statistics_name='stat_services_big' \gx ``` ```text -[ 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 : **Exemple 4-28.** Estimation améliorée avec la statistique\. ```sql EXPLAIN (ANALYZE) SELECT * FROM services_big WHERE localisation='Paris' AND departement=75; ``` ```text ------------------------------------------------------------------- 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) **Exemple 4-29.** Création d’une statistique N\-Distinct multicolonne\. ```sql 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 : **Exemple 4-30.** Regroupement sur une seule colonne\. ```sql EXPLAIN (ANALYZE) SELECT localisation, COUNT(*) FROM services_big GROUP BY localisation ; ``` ```text ------------------------------------------------------------------------ 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 : **Exemple 4-31.** Regroupement sur deux colonnes corrélées\. ```sql EXPLAIN (ANALYZE) SELECT localisation, departement, COUNT(*) FROM services_big GROUP BY localisation, departement; ``` ```text ------------------------------------------------------------------------- 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 : **Exemple 4-32.** Création d’une statistique N\-Distinct\. ```sql 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 : **Exemple 4-33.** Statistique N\-Distinct générée\. ```sql SELECT * FROM pg_stats_ext WHERE statistics_name='stat_services_big' \gx ``` ```text -[ 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 | ø ``` **Exemple 4-34.** Regroupement avec la statistique N\-Distinct\. ```sql EXPLAIN (ANALYZE) SELECT localisation, departement, COUNT(*) FROM services_big GROUP BY localisation, departement; ``` ```text ------------------------------------------------------------------------- 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) **Exemple 4-35.** Création d’une statistique MCV multicolonne\. ```sql 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 : **Exemple 4-36.** Plan sans statistique MCV multicolonne\. ```sql DROP STATISTICS IF EXISTS stat_services_big; EXPLAIN (ANALYZE) SELECT * FROM services_big WHERE localisation='Paris' AND departement > 74 ; ``` ```text --------------------------------------------------------------------------- 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 : **Exemple 4-37.** Création et calcul d’une statistique MCV\. ```sql 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 : **Exemple 4-38.** Statistique MCV générée\. ```sql SELECT * FROM pg_stats_ext WHERE statistics_name='stat_services_big' \gx ``` ```text -[ 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} ``` **Exemple 4-39.** Plan avec la statistique MCV\. ```sql EXPLAIN (ANALYZE) SELECT * FROM services_big WHERE localisation='Paris' AND departement > 74; ``` ```text --------------------------------------------------------------------------- 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 : **Exemple 4-40.** Création des trois types à la fois\. ```sql CREATE STATISTICS stat_services_big ON localisation, departement FROM services_big; ``` ### 4.8 Statistiques étendues #### 4.8.1 Statistiques sur les expressions **Exemple 4-41.** Syntaxe pour une statistique sur expression\. ```sql 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 : **Exemple 4-42.** Plan sans statistique sur expression\. ```sql EXPLAIN SELECT * FROM employes_big WHERE extract('year' from date_embauche) = 2006 ; ``` ```text --------------------------------------------------------------- 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 : **Exemple 4-43.** Création et calcul de la statistique sur expression\. ```sql 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 : **Exemple 4-44.** Plan avec statistique sur expression\. ```sql EXPLAIN SELECT * FROM employes_big WHERE extract('year' from date_embauche) = 2006 ; ``` ```text --------------------------------------------------------------------- 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` : **Exemple 4-45.** Affichage psql d’une statistique sur expression\. ```text … 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. **Exemple 4-46.** Consultation de pg\_stats\_ext\. ```sql SELECT * FROM pg_stats_ext \gx ``` ```text -[ 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` : **Exemple 4-47.** Consultation de pg\_stats\_ext\_exprs\. ```sql SELECT * FROM pg_stats_ext_exprs \gx ``` ```text -[ 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: **Exemple 4-48.** Extrait de code ou de sortie du manuel\, page PDF 255\. ```text 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: **Exemple 4-49.** Extrait de code ou de sortie du manuel\, page PDF 255\. ```text # Nettoyage (VACUUM) et analyze des tables ``` vacuumdb --analyze --echo **Exemple 4-50.** Extrait de code ou de sortie du manuel\, page PDF 255\. ```text # ANALYZE uniquement ``` vacuumdb --analyze-only --echo **Exemple 4-51.** Extrait de code ou de sortie du manuel\, page PDF 255\. ```text # 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 **Exemple 4-52.** Extrait de code ou de sortie du manuel\, page PDF 256\. ```text FROM pg_stat_user_tables WHERE relname = 'service_counters_94' \gx ``` **Tableau 4-1.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 256\. | -[ RECORD 1 ]-------+-----------------------------relname | service_counters_94 | | --- | --- | | last_analyze | 2025-03-12 10:23:50.401407+01 | | last_autoanalyze | 2025-03-11 23:49:18.162277+01 | #### 4.9.2 ANALYZE **Exemple 4-53.** Extrait de code ou de sortie du manuel\, page PDF 256\. ```text 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 **Exemple 4-54.** Extrait de code ou de sortie du manuel\, page PDF 257\. ```text 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: **Exemple 4-55.** Extrait de code ou de sortie du manuel\, page PDF 259\. ```text 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: **Exemple 4-56.** Extrait de code ou de sortie du manuel\, page PDF 260\. ```text 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 **Exemple 4-57.** Extrait de code ou de sortie du manuel\, page PDF 260\. ```text 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 : **Exemple 4-58.** Extrait de code ou de sortie du manuel\, page PDF 260\. ```text 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. **Exemple 4-59.** Extrait de code ou de sortie du manuel\, page PDF 260\. ```text ANALYZE corr1; EXPLAIN SELECT * FROM corr1 WHERE c1 = 1 AND c2 = 2 ; ``` **Exemple 4-60.** Extrait de code ou de sortie du manuel\, page PDF 260\. ```text 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: **Exemple 4-61.** Extrait de code ou de sortie du manuel\, page PDF 261\. ```text 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: **Exemple 4-62.** Extrait de code ou de sortie du manuel\, page PDF 261\. ```text EXPLAIN SELECT * FROM corr1 WHERE c1 = 1 AND c2 = 2 ; ``` **Exemple 4-63.** Extrait de code ou de sortie du manuel\, page PDF 261\. ```text 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 **Exemple 4-64.** Extrait de code ou de sortie du manuel\, page PDF 262\. ```text pg_dump -t demo --statistics-only ``` **Exemple 4-65.** Extrait de code ou de sortie du manuel\, page PDF 262\. ```text 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: **Exemple 4-66.** Extrait de code ou de sortie du manuel\, page PDF 262\. ```text 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: **Exemple 4-67.** Extrait de code ou de sortie du manuel\, page PDF 262\. ```text 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 ); ``` **Exemple 4-68.** Extrait de code ou de sortie du manuel\, page PDF 262\. ```text 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 : **Exemple 4-69.** Extrait de code ou de sortie du manuel\, page PDF 267\. ```text 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: **Exemple 4-70.** Extrait de code ou de sortie du manuel\, page PDF 267\. ```text 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: **Exemple 4-71.** Extrait de code ou de sortie du manuel\, page PDF 268\. ```text 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? **Exemple 4-72.** Extrait de code ou de sortie du manuel\, page PDF 269\. ```text -- 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 : **Exemple 4-73.** Extrait de code ou de sortie du manuel\, page PDF 270\. ```text 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: **Exemple 4-74.** Extrait de code ou de sortie du manuel\, page PDF 270\. ```text 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" **Tableau 4-2.** Vue pg\_stat\_user\_tables \(1\/2\)\. ```text View "pg_catalog.pg_stat_user_tables" Column | Type | Collation | Nullable | Default | Storage | ... ---------------------+--------------------------+-----------+----------+---------+--------- relid | oid | | | | plain schemaname | name | | | | plain ``` **Tableau 4-3.** Vue pg\_stat\_user\_tables \(2\/2\)\. ```text 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 **Exemple 4-75.** Extrait de code ou de sortie du manuel\, page PDF 271\. ```text FROM pg_stat_user_tables; ``` **Tableau 4-4.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 271\. | relname | last_analyze | last_autoanalyze | | --- | --- | --- | | job_history | | | | countries | | | | departments | | | | locations | | | **Tableau 4-5.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 272\. | employees | | | --- | --- | | regions | | | jobs | | (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: **Exemple 4-76.** Extrait de code ou de sortie du manuel\, page PDF 272\. ```text ANALYZE employees, locations; ANALYZE ``` La vue pg_stat_user_tables nous permet de vérifier l’opération: SELECT relname, last_analyze, last_autoanalyze **Exemple 4-77.** Extrait de code ou de sortie du manuel\, page PDF 272\. ```text FROM pg_stat_user_tables; ``` **Tableau 4-6.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 272\. | relname | last_analyze | last_autoanalyze | | --- | --- | --- | | job_history | | | | countries | | | | departments | | | | locations | 2024-12-17 08:42:00.108691+01 | | | employees | 2024-12-17 08:42:00.107698+01 | 2024-12-17 08:39:04.767175+01 | | regions | | | | jobs | | | (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: **Exemple 4-78.** Extrait de code ou de sortie du manuel\, page PDF 272\. ```text 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) **Exemple 4-79.** Extrait de code ou de sortie du manuel\, page PDF 272\. ```text 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 **Exemple 4-80.** Extrait de code ou de sortie du manuel\, page PDF 273\. ```text 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 ) **Exemple 4-81.** Extrait de code ou de sortie du manuel\, page PDF 273\. ```text SELECT reltuples FROM pg_class WHERE relname='employees'; ``` reltuples ----------- 107 (1 row) Le calcul exact se fait avec la fonction count() de SELECT: **Exemple 4-82.** Extrait de code ou de sortie du manuel\, page PDF 273\. ```text 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 **Exemple 4-83.** Extrait de code ou de sortie du manuel\, page PDF 273\. ```text 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 **Exemple 4-84.** Extrait de code ou de sortie du manuel\, page PDF 274\. ```text FROM pg_stats WHERE tablename = 'employees' AND attname = 'salary'; ``` **Tableau 4-7.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 274\. | most_common_val | most_common_freq | | --- | --- | | 2500 | 0.056074765 | | 2600 | 0.037383176 | | 2800 | 0.037383176 | | 3100 | 0.037383176 | | 3200 | 0.037383176 | | 9000 | 0.037383176 | | 10000 | 0.037383176 | | 2900 | 0.028037382 | | 7000 | 0.028037382 | | 8000 | 0.028037382 | | 9500 | 0.028037382 | | 11000 | 0.028037382 | | 12000 | 0.028037382 | | 2200 | 0.018691588 | | 2400 | 0.018691588 | | 2700 | 0.018691588 | | 3000 | 0.018691588 | | 3300 | 0.018691588 | | 3600 | 0.018691588 | | 4200 | 0.018691588 | | 4800 | 0.018691588 | | 6000 | 0.018691588 | | 6200 | 0.018691588 | | 6500 | 0.018691588 | | 7500 | 0.018691588 | | 8200 | 0.018691588 | | 10500 | 0.018691588 | | 17000 | 0.018691588 | (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 **Exemple 4-85.** Extrait de code ou de sortie du manuel\, page PDF 274\. ```text FROM pg_stats WHERE tablename = 'employees' ``` AND attname = 'last_name'; **Tableau 4-8.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 274\. | most_common_val | most_common_freq | | --- | --- | | Cambrault | 0.018691588 | **Tableau 4-9.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 275\. | Grant | 0.018691588 | | --- | --- | | King | 0.018691588 | | Smith | 0.018691588 | | Taylor | 0.018691588 | (5 rows) - Afficher l’histogramme pour la colonne salary . **Exemple 4-86.** Extrait de code ou de sortie du manuel\, page PDF 275\. ```text WITH bounds AS ( SELECT generate_series(1, array_length(histogram_bounds, 1) - 1) AS bucket_idx, ``` histogram_bounds::text::numeric[] AS bounds **Exemple 4-87.** Extrait de code ou de sortie du manuel\, page PDF 275\. ```text 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(*) **Exemple 4-88.** Extrait de code ou de sortie du manuel\, page PDF 275\. ```text 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 **Exemple 4-89.** Extrait de code ou de sortie du manuel\, page PDF 275\. ```text FROM pg_stats WHERE tablename='employees' ``` AND attname='commission_pct'; **Tableau 4-10.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 275\. | attname | n_distinct | | --- | --- | | commission_pct | 7 | (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 **Exemple 4-90.** Extrait de code ou de sortie du manuel\, page PDF 275\. ```text FROM pg_stats WHERE tablename='employees' ``` AND attname='last_name'; **Tableau 4-11.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 275\. | attname | n_distinct | | --- | --- | | last_name | -0.95327103 | (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 **Exemple 4-91.** Extrait de code ou de sortie du manuel\, page PDF 276\. ```text FROM pg_stats WHERE tablename='employees' ``` AND attname='job_id'; **Tableau 4-12.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 276\. | attname | n_distinct | | --- | --- | | job_id | -0.17757009 | (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 ? **Exemple 4-92.** Extrait de code ou de sortie du manuel\, page PDF 276\. ```text SELECT attname, correlation FROM pg_stats WHERE tablename='employees'; ``` **Tableau 4-13.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 276\. | attname | correlation | | --- | --- | | department_id | 0.09441016 | | commission_pct | -0.4140056 | | employee_id | 1 | | first_name | 0.061756697 | | last_name | -0.10506672 | | email | 0.052459884 | | phone_number | 0.094104506 | | hire_date | 0.15537138 | | job_id | 0.12764749 | | salary | -0.030016262 | | manager_id | 0.5118422 | (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 ? **Exemple 4-93.** Extrait de code ou de sortie du manuel\, page PDF 276\. ```text SELECT attname, avg_width FROM pg_stats WHERE tablename='pg_description' AND attname='description'; ``` **Tableau 4-14.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 276\. | attname | avg_width | | --- | --- | 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: **Exemple 4-94.** Extrait de code ou de sortie du manuel\, page PDF 277\. ```text $ 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. **Exemple 4-95.** Extrait de code ou de sortie du manuel\, page PDF 277\. ```text 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 * **Exemple 4-96.** Extrait de code ou de sortie du manuel\, page PDF 277\. ```text FROM colis WHERE id_ville IN ( ``` SELECT id_ville **Exemple 4-97.** Extrait de code ou de sortie du manuel\, page PDF 277\. ```text FROM villes WHERE localite = 'PARIS' ``` AND codepostal LIKE '75%' ); Le plan est: **Exemple 4-98.** Extrait de code ou de sortie du manuel\, page PDF 277\. ```text QUERY PLAN ------------------------------------------------------------------------------- ``` **Exemple 4-98-x01.** Extrait de code ou de sortie du manuel\. ```text Nested Loop (cost=5.85..13653.51 rows=3275 width=16) (actual time=21.898..3889.603 rows=170802.00 loops=1) ``` **Exemple 4-99.** Extrait de code ou de sortie du manuel\, page PDF 277\. ```text Buffers: shared hit=53091 read=120714 I/O Timings: shared read=929.314 ``` **Exemple 4-99-x01.** Extrait de code ou de sortie du manuel\. ```text -> 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 **Exemple 4-100.** Extrait de code ou de sortie du manuel\, page PDF 278\. ```text Buffers: shared hit=63 read=322 ``` **Exemple 4-100-x01.** Extrait de code ou de sortie du manuel\. ```text 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 ``` **Exemple 4-101.** Extrait de code ou de sortie du manuel\, page PDF 278\. ```text Buffers: shared hit=53028 read=120392 ``` **Exemple 4-101-x01.** Extrait de code ou de sortie du manuel\. ```text 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 ``` **Exemple 4-102.** Extrait de code ou de sortie du manuel\, page PDF 278\. ```text Buffers: shared hit=2755 read=150 ``` I/O Timings: shared read=4.173 Planning: **Exemple 4-103.** Extrait de code ou de sortie du manuel\, page PDF 278\. ```text Buffers: shared hit=5 read=9 ``` I/O Timings: shared read=0.197 **Exemple 4-104.** Extrait de code ou de sortie du manuel\, page PDF 278\. ```text 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 **Exemple 4-105.** Extrait de code ou de sortie du manuel\, page PDF 278\. ```text 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 **Exemple 4-106.** Extrait de code ou de sortie du manuel\, page PDF 278\. ```text FROM villes; ``` **Tableau 4-15.** Tableau ou sortie tabulaire extraite du manuel PERF1\, page PDF 278\. | nb_villes | nb_paris | nb_75 | nb_paris_75 | | --- | --- | --- | --- | | 54955 | 940 | 998 | 940 | Les statistiques reproduisent à peu près cela (les chiffres peuvent varier légèrement entre des installations à cause du choix de l’échantillon statistique): **Exemple 4-107.** Extrait de code ou de sortie du manuel\, page PDF 279\. ```text EXPLAIN SELECT * FROM villes ; ``` **Exemple 4-108.** Extrait de code ou de sortie du manuel\, page PDF 279\. ```text QUERY PLAN ------------------------------------------------------------- Seq Scan on villes (cost=0.00..934.55 rows=54955 width=27) ``` **Exemple 4-109.** Extrait de code ou de sortie du manuel\, page PDF 279\. ```text EXPLAIN SELECT * FROM villes WHERE localite='PARIS'; ``` **Exemple 4-110.** Extrait de code ou de sortie du manuel\, page PDF 279\. ```text QUERY PLAN ------------------------------------------------------------ Seq Scan on villes (cost=0.00..1071.94 rows=971 width=27) Filter: (localite = 'PARIS'::text) ``` **Exemple 4-111.** Extrait de code ou de sortie du manuel\, page PDF 279\. ```text EXPLAIN SELECT * FROM villes WHERE codepostal LIKE '75%'; ``` **Exemple 4-112.** Extrait de code ou de sortie du manuel\, page PDF 279\. ```text 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: **Exemple 4-113.** Extrait de code ou de sortie du manuel\, page PDF 279\. ```text EXPLAIN SELECT * FROM villes WHERE localite='PARIS' AND codepostal LIKE '75%'; ``` **Exemple 4-114.** Extrait de code ou de sortie du manuel\, page PDF 279\. ```text 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: **Exemple 4-115.** Extrait de code ou de sortie du manuel\, page PDF 279\. ```text EXPLAIN SELECT * FROM colis WHERE id_ville IN ( SELECT id_ville FROM villes WHERE localite = 'PARIS' ); ``` **Exemple 4-117.** Extrait de code ou de sortie du manuel\, page PDF 280\. ```text 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: **Exemple 4-118.** Extrait de code ou de sortie du manuel\, page PDF 280\. ```text CREATE STATISTICS villes_localite_codepostal ON localite,codepostal FROM villes ; ``` Le rafraîchissement n’est pas automatique: **Exemple 4-119.** Extrait de code ou de sortie du manuel\, page PDF 280\. ```text ANALYZE villes ; ``` Le résultat est-il concluant? **Exemple 4-120.** Extrait de code ou de sortie du manuel\, page PDF 280\. ```text 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: **Exemple 4-122.** Extrait de code ou de sortie du manuel\, page PDF 280\. ```text 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. **Exemple 4-123.** Extrait de code ou de sortie du manuel\, page PDF 281\. ```text 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)); ``` **Exemple 4-124.** Extrait de code ou de sortie du manuel\, page PDF 281\. ```text ANALYZE villes; ``` **Exemple 4-125.** Extrait de code ou de sortie du manuel\, page PDF 281\. ```text EXPLAIN SELECT * FROM colis WHERE id_ville IN ( SELECT id_ville FROM villes WHERE test_ville(localite,codepostal) LIKE 'PARIS-75%' ); ``` **Exemple 4-126.** Extrait de code ou de sortie du manuel\, page PDF 281\. ```text 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: **Exemple 4-127.** Extrait de code ou de sortie du manuel\, page PDF 281\. ```text 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).