Statistiques du planificateur Postgres

Le planificateur de requêtes Postgres utilise les statistiques du planificateur pour estimer le nombre de lignes que chaque étape d’une requête retourne et pour choisir un plan d’exécution. Cette page décrit quand Postgres génère automatiquement des statistiques du planificateur, comment trouver des tables qui en manquent, et comment modifier leur génération pour une table. Pour savoir comment Lakebase préserve les statistiques lors des redémarrages de calcul et des mises à l’échelle à zéro, voir Collecte des statistiques.

Quand ANALYZE s’exécute sur un tableau ?

Autovacuum génère automatiquement des statistiques de planification en exécutant ANALYZE sur une table après qu’un nombre suffisant de lignes a été modifié. Une table est analysée lorsque le nombre de lignes insérées, mises à jour ou supprimées depuis la dernière analyse dépasse :

autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × number of rows in the table

Avec les paramètres par défaut (autovacuum_analyze_threshold = 50 et autovacuum_analyze_scale_factor = 0.1), une nouvelle table est analysée après plus de 50 lignes insérées, mises à jour ou supprimées, et une table existante après que plus de 50 lignes plus 10 % de ses lignes ont été modifiées. En conséquence, une table chargée une seule fois avec 50 lignes ou moins et jamais modifiée n’est jamais analysée par l’autovacuum. Sans statistiques de planificateur, celui-ci s’appuie sur des estimations par défaut, ce qui peut entraîner des plans de requête inefficaces lorsque cette table est jointe à des tables plus grandes.

Databricks recommande d’exécuter ANALYZE sur de nouvelles tables après le chargement initial des données, et sur des tables de 50 lignes ou moins (seuil d’autoanalyse par défaut), afin que des statistiques précises du planificateur soient en place avant que les tables ne soient interrogées.

Analyser les tables partitionnées

Autovacuum analyse chaque partition d’une table partitionnée à mesure que les lignes de cette partition changent, mais il n’analyse jamais la table partitionnée elle-même (le parent). Les statistiques du parent décrivent la table dans son ensemble, et elles ne sont générées que lorsque vous exécutez ANALYZE sur le parent. Sans eux, les requêtes qui font référence à la table partitionnée peuvent obtenir des plans inefficaces. Exécutez ANALYZE sur le parent après avoir chargé les données pour la première fois, et à nouveau chaque fois que la répartition des données entre ses partitions change significativement :

ANALYZE my_schema.my_partitioned_table;

Lorsque vous exécutez ANALYZE sur le parent, Postgres échantillonne les lignes de toutes les partitions pour construire les statistiques du parent. Par défaut, il analyse également chaque partition et met à jour ses statistiques, ce qui peut prendre beaucoup de temps sur une grande table.

Dans Postgres 18 et versions ultérieures, utilisez ANALYZE ONLY pour mettre à jour les statistiques du parent sans analyser chaque partition. Postgres continue d’échantillonner les lignes de toutes les partitions, et Autovacuum continue d’analyser les partitions au fur et à mesure que leurs lignes changent.

ANALYZE ONLY my_schema.my_partitioned_table;

Dans Postgres 16 et 17, ANALYZE sur une table partitionnée analyse toujours chaque partition. Dans n’importe quelle version, pour mettre à jour les statistiques d’une partition, exécutez ANALYZE sur cette partition.

Trouvez des tables sans statistiques de planificateur

Pour trouver des tables sans statistiques du planificateur, cherchez des tables sans lignes dans la vue pg_stats. Les statistiques du planificateur sont stockées de façon durable, donc cette vérification reste fiable même après réinitialisation des statistiques cumulatives. La requête exclut les schémas système ainsi que les schémas que Lakebase utilise en interne pour l’API de données et les tables synchronisées.

SELECT t.schemaname, t.tablename
FROM pg_tables t
WHERE t.schemaname NOT IN ('pg_catalog', 'information_schema', '__db_system', 'pgrst')
  AND t.schemaname NOT LIKE '\_\_db\_user\_%'
  AND NOT EXISTS (
    SELECT 1 FROM pg_stats s
    WHERE s.schemaname = t.schemaname AND s.tablename = t.tablename
  )
ORDER BY 1, 2;

Lancez la requête en tant que membre du rôle databricks_superuser ou en tant que propriétaire de la table. La pg_stats vue ne montre que les statistiques des colonnes que le rôle actuel peut lire. Pour une table avec la sécurité au niveau ligne activée, elle masque également les statistiques, sauf si le rôle peut contourner la sécurité au niveau des lignes. Un tableau que le rôle actuel ne peut pas lire, ou qui a la sécurité au niveau des lignes activée, peut apparaître dans les résultats même s’il contient des statistiques.

Les tables qui n’ont jamais contenu de données apparaissent également dans les résultats, car ANALYZE ne génère pas de statistiques pour une table vide. Concentrez-vous sur les tables qui contiennent des données et qui sont utilisées dans les requêtes.

Pour générer des statistiques de planificateur pour une table retournée par la requête, exécutez ANALYZE:

ANALYZE my_schema.my_table;

Pour analyser chaque table de la base de données actuelle, exécutez ANALYZE sans nom de table.

Personnaliser les paramètres d’autovacuum pour une table

Si une table change trop rarement pour les seuils par défaut, vous pouvez abaisser le seuil d’analyse uniquement pour cette table. Par exemple, pour analyser une petite table de référence après plus de 10 modifications de lignes, quelle que soit sa taille :

ALTER TABLE my_schema.my_table SET (
  autovacuum_analyze_threshold = 10,
  autovacuum_analyze_scale_factor = 0
);

Pour visualiser les paramètres personnalisés sur une table, interrogez pg_class.reloptions:

SELECT relname, reloptions
FROM pg_class
WHERE oid = 'my_schema.my_table'::regclass;

Pour restaurer le comportement par défaut, réinitialisez les paramètres :

ALTER TABLE my_schema.my_table RESET (
  autovacuum_analyze_threshold,
  autovacuum_analyze_scale_factor
);

Même avec un seuil plus bas, l’autovacuum n’analyse pas une table si le nombre de lignes chargées ne dépasse pas le seuil et que la table n’est jamais modifiée par la suite. Pour les tables statiques, exécutez ANALYZE après avoir chargé les données.

Pour plus d’informations, consultez la documentation Postgres :

  • Dernière mise à jour :