Statistiques du planificateur dans PostgreSQL : cibles de statistiques, colonnes corrélées et erreurs d'estimation

Ce que c'est

La documentation décrit les entrées du planificateur : reltuples et relpages dans pg_class pour la taille de la table, et pg_statistic (lisible via la vue pg_stats) avec, pour chaque colonne, ses valeurs les plus fréquentes et leurs fréquences, un histogramme des valeurs restantes, la fraction de valeurs nulles et le nombre de valeurs distinctes. ANALYZE, manuel ou via autovacuum, échantillonne la table pour les remplir. Le nombre d'entrées de valeurs les plus fréquentes et d'histogramme par colonne est borné par default_statistics_target (100 par défaut) ou par un ALTER TABLE ... ALTER COLUMN ... SET STATISTICS propre à la colonne. Pour plusieurs conditions dans une même clause WHERE, le planificateur multiplie les sélectivités, ce que la documentation présente comme supposant les conditions indépendantes.

Pourquoi c'est important

Un plan est choisi à partir d'estimations, pas de données. Quand une estimation est fausse de plusieurs ordres de grandeur, le planificateur choisit une nested loop là où un hash join était nécessaire, ou un index scan qui touche la moitié de la table, et la requête est lente bien que tous les index existent. EXPLAIN ANALYZE montre l'écart sous forme de lignes estimées contre lignes réelles, au niveau du nœud où il apparaît.

Comment l'appliquer

• Vérifier d'abord la fraîcheur : last_analyze et last_autoanalyze dans pg_stat_user_tables. Une table qui vient d'être chargée en masse n'a souvent aucune statistique.
• Pour une colonne asymétrique (codes de statut, quelques très gros tenants parmi de nombreux petits) dont l'estimation est erronée pour les valeurs rares, relever la cible sur cette seule colonne, par exemple SET STATISTICS 1000, puis ANALYZE ; la documentation présente le coût comme plus d'espace dans pg_statistic et un temps de planification légèrement plus long.
• Pour des conditions sur des colonnes corrélées (ville et code postal, date de commande et date d'expédition), créer des statistiques étendues : CREATE STATISTICS s (dependencies, ndistinct, mcv) ON a, b FROM t, puis ANALYZE. Les dépendances fonctionnelles ne s'appliquent qu'aux conditions d'égalité et aux listes IN avec des constantes, les listes mcv capturent les combinaisons fréquentes, ndistinct améliore les estimations de GROUP BY.
• Pour un filtre sur une expression (lower(email), date_trunc('day', ts)), créer des statistiques d'expression avec CREATE STATISTICS ON (expr) FROM t, que la documentation décrit comme apportant des bénéfices similaires à un index d'expression sans le coût de maintenance d'un index.
• Relancer EXPLAIN ANALYZE après chaque changement et ne le conserver que si les estimations se sont rapprochées des valeurs réelles.

Pièges

Relever default_statistics_target globalement coûte de l'espace pg_statistic et du temps d'estimation sur chaque colonne pour peu de gain ; cibler les colonnes qui en ont besoin. pg_upgrade ne transfère la plupart des statistiques qu'à partir de PostgreSQL 18, et jamais les statistiques étendues ; une mise à niveau nécessite donc un nouvel ANALYZE. Une instruction préparée peut exécuter un plan générique dont les estimations ignorent la valeur réelle du paramètre. Un échantillon ne peut pas suivre une distribution qui change d'heure en heure.