Statistiques, ANALYZE et autovacuum

Le planificateur estime les coûts à partir de statistiques (distribution des valeurs, nombre de lignes) : si elles sont obsolètes, il choisit de mauvais plans.

ANALYZE commandes;                          -- recalculer les statistiques d'une table
VACUUM (ANALYZE, VERBOSE) commandes;        -- nettoyer et recalculer

SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, last_autoanalyze
FROM   pg_stat_user_tables
ORDER  BY n_dead_tup DESC
LIMIT  10;

Autovacuum#

Un processus d'arrière-plan lance VACUUM et ANALYZE automatiquement quand assez de lignes ont changé (par défaut environ 20 % de la table pour le vacuum, 10 % pour l'analyse). Sur de grosses tables très modifiées, ces seuils sont trop hauts : on les abaisse par table.

ALTER TABLE evenements SET (autovacuum_vacuum_scale_factor = 0.02,
                            autovacuum_analyze_scale_factor = 0.01);

Corrélations et colonnes très biaisées#

Pour des colonnes liées (ville et code postal) ou très déséquilibrées, on peut créer des statistiques étendues :

CREATE STATISTICS commandes_stats (dependencies) ON client_id, statut FROM commandes;
ANALYZE commandes;

Astuce : quand le plan a un mauvais rows=, le premier réflexe est ANALYZE, le deuxième est d'augmenter default_statistics_target (ou ALTER TABLE ... ALTER COLUMN ... SET STATISTICS 500) pour la colonne concernée.