Les statistiques au service du plan d'exécution
- 11 minutes de lecture
La lecture d’un plan d’exécution fait partie des meilleures armes du développeur
et de l’administrateur de bases de données pour identifier les problèmes de
performances. Dans un précédent article, je
présentais l’intérêt de positionner un index sur les colonnes d’une table pour
faciliter les recherches, notamment avec l’aide de la commande EXPLAIN.
À cette époque, je ne m’étais pas attardé sur la notion des statistiques de données, que l’on retrouve dans la plupart des moteurs du marché. Voyons de plus près ce que propose PostgreSQL pour garantir les performances de vos requêtes.
Estimer ou ne pas estimer
Une statistique de données résulte d’un calcul en arrière plan sur tout ou partie des données d’une table. Il peut s’agir de la quantité de lignes, du nombre distinct de valeurs dans une colonne, ou bien encore de la distribution des valeurs sous forme d’histogramme. Ainsi, pour chaque table et chaque colonne, il existe des données supplémentaires qui permettent au moteur d’avoir une juste estimation des données qu’il s’apprête à manipuler.
Prenons la table pgbench_history sur laquelle j’ai ajouté un index pour la
colonne aid. Quel plan nous propose le moteur pour récupérer toutes les lignes
dont la valeur aid est inférieure à 1 000 ?
EXPLAIN SELECT * FROM pgbench_history WHERE aid < 1000;
QUERY PLAN
--------------------------------------------------------
Bitmap Heap Scan on pgbench_history
(cost=4.34..11.79 rows=9 width=116)
Recheck Cond: (aid < 1000)
-> Bitmap Index Scan on pgbench_history_aid_idx
(cost=0.00..4.34 rows=9 width=0)
Index Cond: (aid < 1000)
Le résultat qui s’affiche correspond au meilleur plan connu et repose sur un
système de coût d’accès aux lignes. Le plan présentant le coût le plus faible
est considéré comme le meilleur plan ; l’ensemble des nœuds le composant sera
donc respecté pour récupérer le résultat final. Dans le cas de notre requête, le
moteur estime que les opérations Bitmap Index et Bitmap Heap sont les moins
coûteuses en performance.
Comment le moteur peut-il être certain que ce plan est le moins coûteux ?
Comparons son coût d’accès total (ici 11.79) avec un autre plan pour lequel
nous interdisons l’usage de l’index.
SET enable_bitmapscan = off;
EXPLAIN SELECT * FROM pgbench_history WHERE aid < 1000;
QUERY PLAN
------------------------------------------------------------------
Seq Scan on pgbench_history (cost=0.00..19.50 rows=9 width=116)
Filter: (aid < 1000)
Le coût de lecture complète de la table (nœud Seq Scan) vaut 19.50, ce qui
est supérieur au plan précédent. Lorsque l’on exécute un requête SQL, une partie
du moteur, appelé le planificateur consolide en arrière plan une petite quantité
de plans avant de ne retourner que le meilleur. Plusieurs paramètres comme
enable_bitmapscan, peuvent changer complètement le comportement du planificateur
en réduisant le nombre de choix possibles dans l’élaboration de ses plans.
En complément du coût, la commande EXPLAIN indique également l’estimation du
nombre de lignes que retourneront les nœuds. Dans le cas des deux plans, le
planificateur estime qu’il existe 9 lignes répondant au critère de recherche.
Cette statistique peut être déduite des vues système pg_class et pg_stats.
SELECT s.*, c.relpages, c.reltuples
FROM pg_stats s JOIN pg_class c ON s.tablename = c.relname
WHERE tablename = 'pgbench_history' AND attname = 'aid' \gx
-[ RECORD 1 ]----------+------------------------------------
schemaname | public
tablename | pgbench_history
attname | aid
inherited | f
null_frac | 0
avg_width | 4
n_distinct | -0.997
most_common_vals | {66403,80979,82766}
most_common_freqs | {0.002,0.002,0.002}
histogram_bounds | {75,973,1755,… ,98037,98999,99991}
correlation | 0.047431067
most_common_elems |
most_common_elem_freqs |
elem_count_histogram |
relpages | 7
reltuples | 1000
Sans même consulter le contenu de la table pgbench_history, nous sommes en
possession d’informations intéressantes. Nous apprenons que la table contient
exactement 1 000 lignes (reltuples) et que la colonne aid présente un nombre
de valeurs distinctes (n_distinct) qui tend vers -1, c’est-à-dire autant de
valeurs uniques que de lignes dans la table.
La distribution des valeurs de la colonne aid est représentée par le tableau
histogram_bounds de 100 éléments. Ces bornes divisent approximativement les
valeurs dans des groupes de même taille ; comprendre que 1 % des lignes ont une
valeur de colonne aid comprise entre 75 et 972, 1 % des lignes, entre 973 et
1 754, etc. On peut dès lors supposer que les valeurs possibles de la colonne
aid s’étendent de 75 à 99 991.
Si l’on revient à notre critère de recherche, les lignes dont la valeur aid
est inférieure à 1 000 représenteraient un peu plus de 1 % des 1000 lignes de la
table, soit environ 10 lignes si toutes les valeurs étaient distinctes.
L’estimation de 9 lignes proposée par la commande EXPLAIN serait donc juste.
Tout l’intérêt des statistiques est donc d’apporter suffisamment d’éléments précalculés et économes en espace disque pour que le planificateur puisse faire des estimations les plus justes possibles. Dès lors qu’une estimation est calculée, le choix du plan d’exécution le moins coûteux devient évident. Retenons que le meilleur plan doit être le moins coûteux en ressources et donc, le plus optimisé pour la requête SQL.
Qu’advient-il de notre plan si, par erreur ou hasard, les statistiques étaient
erronées ou venaient à disparaître pour la colonne aid ?
DELETE FROM pg_statistic s
WHERE starelid = 'pgbench_history'::regclass AND staattnum = (
SELECT attnum FROM pg_attribute
WHERE attrelid = s.starelid AND attname = 'aid'
);
RESET enable_bitmapscan;
EXPLAIN SELECT * FROM pgbench_history WHERE aid < 1000;
QUERY PLAN
--------------------------------------------------------------------
Seq Scan on pgbench_history (cost=0.00..19.50 rows=333 width=116)
Filter: (aid < 1000)
On constate qu’en l’absence de statistiques sur le critère de sélection, le plan
Bitmap n’est plus le moins coûteux et n’est donc plus proposé par le
planificateur. Le moteur privilégera la lecture complète de la table (Seq Scan)
dont le coût est invariant.
SET enable_seqscan = off;
EXPLAIN SELECT * FROM pgbench_history WHERE aid < 1000;
QUERY PLAN
--------------------------------------------------------
Bitmap Heap Scan on pgbench_history
(cost=10.86..22.02 rows=333 width=116)
Recheck Cond: (aid < 1000)
-> Bitmap Index Scan on pgbench_history_aid_idx
(cost=0.00..10.77 rows=333 width=0)
Index Cond: (aid < 1000)
L’estimation de lignes retournées peut paraître surprenante ! Il s’agit d’un
calcul arbitraire défini dans la classe selfuncs.h (source) avec notamment un
facteur de sélectivité qui s’applique sur le nombre total de lignes présentes dans
la table. Ainsi, pour un critère d’égalité, ce facteur vaudra 0.5 %
(DEFAULT_EQ_SEL=0.005) alors qu’une comparaison de non-égalité comme celle
de notre exemple, vaudra 33.33 % (DEFAULT_INEQ_SEL=0.3333333333333333).
Puisque le planificateur estime devoir parcourir 333 entrées dans l’index à défaut
de meilleure estimation, le coût total de ce plan est surévalué à 22.02, au
lieu de 11.79 auparavant.
Collecte automatique des statistiques
Bien entendu, supprimer des statistiques n’est pas une bonne pratique et ne devrait
pas être envisagé pour « changer le comportement » du planificateur. Depuis la
version 8.3 de PostgreSQL, il n’y a même plus trop de raison de s’inquiéter de
l’absence ou de la fraîcheur des statistiques associées à chaque colonne de vos
tables : le processus autovacuum (désactivé en 8.1 et 8.2) se charge, entre
autres fonctions, de parcourir régulièrement les tables de vos bases pour collecter
et consolider la table pg_statistic. Il se porte ainsi garant de la pertinence
des plans d’exécution.
En réalité, ce processus observe les variations de volumétrie des tables avant
de déclencher l’opération ANALYZE par un processus de maintenance pour cette
table. Ce mécanisme est bien plus pertinent et optimisé qu’une exécution à
intervale régulier pour calculer arbitrairement les statistiques de la base.
Le seuil de déclenchement de l’autoanalyze est obtenu à l’aide d’un calcul
trivial impliquant deux paramètres globaux que l’on peut surcharger,
autovacuum_analyze_threshold = 50 et autovacuum_analyze_scale_factor = 0.1.
analyze threshold = analyze base threshold +
analyze scale factor * number of tuples
Prenons l’exemple de la table pgbench_accounts qui contient un million de lignes
avec une contrainte de clé primaire sur la colonne aid. La vue système
pg_stat_user_tables dispose d’informations complémentaires à celles des
statistiques, notamment la colonne n_mod_since_analyze qui indique la quantité
de tuples ayant été modifiés depuis la dernière opération de collecte ANALYZE.
Voyons son contenu après la modification d’une portion de lignes.
UPDATE pgbench_accounts SET filler = '' WHERE aid <= 10000;
-- UPDATE 10000
SELECT relname, last_autoanalyze, n_live_tup, n_mod_since_analyze
FROM pg_stat_user_tables u
WHERE relname = 'pgbench_accounts' \gx
-[ RECORD 1 ]-------+------------------------------
relname | pgbench_accounts
last_autoanalyze | 2020-06-18 15:12:55.224493+02
n_live_tup | 1000000
n_mod_since_analyze | 10000
Ici, seul 1 % de la table a subi un changement et le mécanisme de collecte
automatique des statistiques semble ne pas s’être déclenché. En effet, le seuil
de déclenchement pour cette table serait plutôt de 100 050 lignes
(50 + 0.1 * 1000000). Recommençons avec un plus large échantillon et observons
les traces d’activité du processus autovacuum.
ALTER SYSTEM SET log_min_messages = debug2;
ALTER SYSTEM SET log_autovacuum_min_duration = 0;
SELECT pg_reload_conf();
UPDATE pgbench_accounts SET filler = '' WHERE aid <= 90051;
-- UPDATE 90051
SELECT relname, last_autoanalyze, n_live_tup, n_mod_since_analyze
FROM pg_stat_user_tables u
WHERE relname = 'pgbench_accounts' \gx
-[ RECORD 1 ]-------+------------------------------
relname | pgbench_accounts
last_autoanalyze | 2020-06-30 17:44:22.424363+02
n_live_tup | 1000000
n_mod_since_analyze | 0
Comme attendu, un traitement ANALYZE s’est exécuté et a mis à jour les données
de la vue pg_stat_user_tables, mettant à zéro la colonne n_mod_since_analyze
jusqu’au prochain déclenchement. Côté trace d’activité, le mode debug2 écrit
une série d’événements tels que les seuils calculés de la table, le démarrage
et la fin du traitement par le worker dédié.
DEBUG: autovacuum: processing database "demo"
DEBUG: pgbench_accounts: vac: 100051 (threshold 200050),
anl: 100051 (threshold 100050)
DEBUG: analyzing "public.pgbench_accounts"
LOG: automatic analyze of table "demo.public.pgbench_accounts"
system usage: CPU: user: 0.09 s, system: 0.00 s, elapsed: 0.25 s
Estimer la prochaine heure de la collecte
Dans la plupart des cas, les paramètres associés au mécanisme d’autovacuum sont adaptés à la plupart des tables et assurent une fréquence correcte du calcul des statistiques. Cependant, au-delà d’une certaine volumétrie, une table peut présenter des incohérences entre son contenu et ses statistiques.
La modification de 10 % de la table pgbench_accounts pourrait prendre des jours
voire des semaines avant que ne survienne le traitement autoanalyze. Il est
donc de la responsabilité du développeur ou du DBA de surveiller l’accroissement
de l’indicateur n_mod_since_analyze pour éviter que les statistiques ne soient
trop décorrélées du contenu.
Pour s’en assurer, je crée deux fonctions dans ma base pour récupérer respectivement
les options de stockage d’une table (reloptions) ou à défaut, les paramètres
d’instance, ainsi que le calcul du seuil de déclenchement sur la base de la
formule précédente. La seconde fonction s’assure notamment que le mécanisme de
collecte automatique n’est pas désactivé (autovacuum_enabled = off).
CREATE OR REPLACE FUNCTION get_reloption(reloptions text[], name text)
RETURNS text LANGUAGE sql
AS $$
SELECT coalesce(min(option_value), current_setting(name))
FROM pg_options_to_table(reloptions) WHERE option_name = name;
$$;
CREATE OR REPLACE FUNCTION get_anl_threshold(o oid)
RETURNS float LANGUAGE sql
AS $$
SELECT get_reloption(reloptions, 'autovacuum_analyze_threshold')::int +
get_reloption(reloptions, 'autovacuum_analyze_scale_factor')::float *
c.reltuples
FROM pg_class c
WHERE oid = o AND NOT EXISTS (
SELECT 1 FROM pg_options_to_table(c.reloptions)
WHERE option_name = 'autovacuum_enabled' AND option_value = 'off'
)
$$;
Avec les résultats de ces fonctions, je peux construire une requête plus évoluée qui estime l’heure du prochain déclenchement en fonction de nombre de modification et de la dernière collecte.
SELECT relname, n_live_tup, n_mod_since_analyze,
get_anl_threshold(relid) threshold, last_autoanalyze,
current_timestamp +
(1 -
CASE WHEN n_mod_since_analyze = 0 THEN null
WHEN n_mod_since_analyze > get_anl_threshold(relid) THEN 1
ELSE n_mod_since_analyze / get_anl_threshold(relid) END) *
(current_timestamp - last_autoanalyze) next_autoanalyze
FROM pg_stat_user_tables WHERE relname = 'pgbench_accounts' \gx
-[ RECORD 1 ]--------+------------------------------
relname | pgbench_accounts
n_live_tup | 1000000
n_mod_since_analyze | 0
threshold | 100050
last_autoanalyze | 2020-07-01 15:07:40.134444+02
next_autoanalyze |
UPDATE pgbench_accounts SET filler = '' WHERE aid <= 25000;
-- UPDATE 25000
-[ RECORD 1 ]--------+------------------------------
relname | pgbench_accounts
n_live_tup | 1000000
n_mod_since_analyze | 25000
threshold | 100050
last_autoanalyze | 2020-07-01 15:07:40.134444+02
next_autoanalyze | 2020-07-01 18:16:43.734491+02
La commande ALTER TABLE suivante permet de modifier les options de stockage de
la table, et d’ajuster le seuil du déclenchement automatique de la collecte. À
vous de voir si l’activité sur votre table le justifie !
ALTER TABLE pgbench_accounts SET (
autovacuum_analyze_scale_factor = 0.01,
autovacuum_analyze_threshold = 0
);
-[ RECORD 1 ]--------+------------------------------
relname | pgbench_accounts
n_live_tup | 1000000
n_mod_since_analyze | 25000
threshold | 10000
last_autoanalyze | 2020-07-01 15:07:40.134444+02
next_autoanalyze | 2020-07-01 17:04:34.038917+02
-[ RECORD 1 ]--------+------------------------------
relname | pgbench_accounts
n_live_tup | 1000000
n_mod_since_analyze | 0
threshold | 10000
last_autoanalyze | 2020-07-01 17:04:41.987032+02
next_autoanalyze |
Conclusion
Les statistiques jouent un rôle essentiel dans les performances d’un moteur relationnel comme PostgreSQL. Réussir à les maintenir pertinentes est la clé dans la gestion au quotidien du système. Avec mon exposé, j’espère vous avoir démontré qu’il n’est pas nécessaire de rafraîchir toutes les statistiques, mais seulement celles dont le seuil n’est plus adapté.
Dans un genre similaire, un récent article de Hubert « depesz » Lubaczewski
présente une série de requêtes permettant d’identifier les tables nécessitant une
action de maintenance (vacuum ou analyze) dans le cas où la routine automatique
ne fait pas correctement son travail. Ce genre de petites astuces peuvent sauver
des vies… (euh) des plans d’exécution !