Quand le collecteur de statistiques de **PostgreSQL** produit-il des estimations erronées du nombre de lignes pour des filtres multicolonnes fortement corrélés, entraînant des jointures en **Nested Loop** sur de grands ensembles de données, et quel objet statistique élimine cette erreur d'estimation ?

Réponse à la question

PostgreSQL maintient des histogrammes par colonne dans pg_statistic et suppose une indépendance statistique entre les colonnes lors de l'estimation de la sélectivité pour des filtres multicolonnes. Lorsque vous filtrez des colonnes fortement corrélées — telles que city et state_code ou make et model dans une base de données de véhicules — le planificateur multiplie les sélectivités individuelles, sous-estimant considérablement le nombre réel de lignes. Cette erreur de cardinalité pousse souvent l'optimiseur à privilégier des jointures en Nested Loop plutôt que des jointures par Hash ou par Merge, entraînant des performances catastrophiques sur de grandes tables.

Pour résoudre ce problème, vous créez un objet de statistiques étendues en utilisant CREATE STATISTICS, qui construit des données de corrélation multivariées. Plus précisément, le type dependencies suit les dépendances fonctionnelles entre les colonnes, permettant au planificateur de reconnaître que le filtrage sur state_code = 'CA' contraint déjà city aux valeurs de Californie, évitant ainsi l'erreur de multiplication.

-- Créer des statistiques étendues pour les colonnes corrélées
CREATE STATISTICS stats_vehicle_make_model 
ON make, model 
FROM vehicles;

-- Remplir les statistiques
ANALYZE vehicles;

Situation de la vie réelle

Une plateforme logistique avait des difficultés avec un tableau de bord de suivi des envois qui joignait une table shipments de 50 millions de lignes contre customers. La requête filtrée par origin_state et origin_city, où 95 % des lignes pour 'Springfield' en tant que ville étaient en réalité dans 'IL', mais le planificateur supposait que seulement 2 % des envois correspondaient aux deux prédicats indépendamment. Il a estimé 500 lignes et a choisi une jointure en Nested Loop, itérant à travers des millions d'enregistrements de clients et dépassant le délai imparti après 90 secondes.

Une solution envisagée était de désactiver entièrement les jointures en Nested Loop via SET enable_nestloop = off dans la session. Cela a forcé une jointure par Hash et a été terminé en 3 secondes pour cette requête spécifique, mais comportait des risques importants : des changements de configuration globaux se propagent dans les pools de connexions, régressant potentiellement d'autres plans légitimes en Nested Loop qui fonctionnent bien sur de petites tables avec des recherches indexées. De plus, cette solution de contournement nécessitait des modifications de code au niveau de l'application pour définir le paramètre avant l'interrogation.

Une autre option impliquait la création d'un index composite sur (origin_city, origin_state). Bien que cela ait amélioré la sélection de l'index, cela n’a pas résolu la sous-estimation de la cardinalité ; le planificateur pensait toujours que peu de lignes émergeraient de l'analyse de l'index et maintenait la stratégie de Nested Loop, ne l'exécutant que plus rapidement grâce à l'index couvrant. De plus, le large index composite consommait 4 Go d'espace disque supplémentaire et ralentissait les opérations d'écriture sur la table shipments à haute vitesse.

L’équipe a finalement déployé des statistiques étendues en exécutant CREATE STATISTICS stats_origin_correlation ON origin_city, origin_state FROM shipments, suivi de ANALYZE. Cette approche ne nécessitait aucune réécriture de requête et ajoutait une surcharge de stockage négligeable. Après le déploiement, le planificateur a correctement estimé 45 000 lignes et a choisi une jointure par Hash, réduisant la latence de la requête à 400 millisecondes tout en préservant les plans optimaux pour des charges de travail non liées.

Ce que les candidats oublient souvent

Comment la commande ANALYZE rafraîchit-elle les statistiques étendues, et pourquoi un objet statistique peut-il sembler inutilisé immédiatement après sa création ?

ANALYZE calcule les statistiques étendues uniquement lorsqu'il est invoqué explicitement sur la table cible ou lorsque les processus d'autovacuum traitent la table après que l'objet statistique existe. De nombreux candidats supposent que CREATE STATISTICS affecte instantanément la planification, mais les tables de catalogue pg_statistic_ext et pg_statistic_ext_data restent vides jusqu'au prochain cycle d'analyse. Par conséquent, le planificateur continue d'utiliser les histogrammes par colonne et les hypothèses d'indépendance jusqu'à ce que ANALYZE shipments; remplisse les données multivariées. Vous pouvez vérifier l'utilisation en consultant la vue pg_stats_ext pour des valeurs dependencies ou ndistinct non nulles.

Quelle est la différence fonctionnelle entre dependencies et ndistinct dans CREATE STATISTICS, et quels modèles de requête bénéficient de chacun ?

Les Dependencies capturent les relations fonctionnelles où une colonne détermine une autre (par exemple, zip_code détermine city), corrigeant directement les estimations de sélectivité de la clause WHERE. Ndistinct calcule le nombre exact de combinaisons distinctes pour les groupes de colonnes, ce qui améliore les estimations de GROUP BY et DISTINCT plutôt que la sélectivité des filtres. Les candidats souvent confondent ces deux concepts, créant des dependencies lorsque leur requête lente contient un GROUP BY sur des colonnes corrélées, ou vice versa. Pour des résultats optimaux, spécifiez les deux types : CREATE STATISTICS stats_complex WITH (dependencies, ndistinct) ON (...).

Pourquoi les statistiques étendues pourraient-elles ne pas aider avec des requêtes utilisant des conditions OR entre colonnes corrélées ?

Les statistiques étendues n’assistent actuellement que les clauses AND où la multiplication de sélectivité se produit. Lorsque vous filtrez avec OR (par exemple, city = 'Springfield' OR state = 'IL'), PostgreSQL calcule la sélectivité en utilisant la formule P(A) + P(B) - P(A ∩ B), et il ne peut pas appliquer les coefficients de dépendance au terme d'intersection car les statistiques suivent la sélectivité conjointe pour les conjonctions, pas les disjonctions. Les candidats oublient fréquemment cette limitation et tentent d'utiliser CREATE STATISTICS pour corriger les erreurs de cardinalité basées sur OR, ce qui nécessite une réécriture des requêtes (par exemple, en les divisant en branches UNION ALL) ou des index partiels à la place.