Créer des statistiques étendues pour des colonnes corrélées (CREATE STATISTICS)

Commande

-- city and district are correlated: the planner assumes independence and underestimates rows
CREATE STATISTICS customer_city_district_stx (dependencies, ndistinct, mcv) ON city, district FROM customer;
ANALYZE customer;

-- Collected statistics
SELECT statistics_name, attnames, kinds, n_distinct, dependencies
FROM pg_stats_ext
WHERE tablename = 'customer';

-- Verify: estimated rows should now be close to actual rows
EXPLAIN (ANALYZE, TIMING OFF)
SELECT * FROM customer WHERE city = 'Ankara' AND district = 'Cankaya';

Ce que fait la commande

Informe le planificateur de la corrélation entre colonnes (dépendances fonctionnelles, nombre de valeurs distinctes combinées, valeurs les plus fréquentes conjointes) ; corrige les estimations de lignes pour les conditions WHERE et GROUP BY multi-colonnes.

Quand l'utiliser

Lorsque EXPLAIN ANALYZE montre des lignes estimées très inférieures aux lignes réelles pour des filtres multi-colonnes (par ex. 12 contre 570) et qu'une mauvaise méthode de jointure/un mauvais index est choisi en conséquence.

Attention

Ne prend effet qu'après ANALYZE. Lorsque le nombre de combinaisons de colonnes dépasse largement la cible de statistiques (100 par défaut), la liste mcv ne peut pas en couvrir la plupart, et mcv peut alors ne pas corriger une estimation que dependencies corrigerait. Vérifiez avec EXPLAIN ; si nécessaire, retirez mcv de la définition ou relevez la cible avec ALTER STATISTICS customer_city_district_stx SET STATISTICS 1000;. dependencies n'est utilisé que pour les conditions d'égalité/IN, ndistinct pour les estimations de GROUP BY. Chaque définition allonge ANALYZE ; ne l'ajoutez qu'aux groupes de colonnes présentant une erreur d'estimation avérée.