Postgres 规划器统计

Postgres查询规划器使用规划器统计来估算每个查询步骤返回的行数,并选择执行计划。 本页介绍了 Postgres 何时自动生成规划统计数据,如何发现缺少统计数据的表,以及如何更改表的规划统计数据生成时间。 有关 Lakebase 如何在计算资源重启和缩减到零期间保留统计信息,请参阅统计信息收集。

什么时候会在表上运行 ANALYZE?

Autovacuum 在足够多的行变更后,通过在表上运行 ANALYZE 自动生成规划统计数据。 当表自上次分析以来插入、更新或删除的行数超过以下值时,会对该表进行分析:

autovacuum_analyze_threshold + autovacuum_analyze_scale_factor × number of rows in the table

默认设置(autovacuum_analyze_threshold = 50 和 autovacuum_analyze_scale_factor = 0.1)下,插入、更新或删除超过 50 行后会分析新表;现有表则在其变更行数超过 50 行加上其总行数的 10% 后会被分析。 因此,对于只加载一次、行数不超过50且从未修改过的表,自动清理进程永远不会对其进行分析。 如果没有规划器统计信息,查询规划器将依赖默认估计,这可能导致当该表与更大的表进行联接时,查询计划效率低下。

Databricks 建议在初始数据加载后对新表运行 ANALYZE,并在行数不超过 50 行(默认自动分析阈值)的表上运行 ANALYZE,以确保在查询表前能获得准确的规划统计数据。

分析分区表

Autovacuum会随着分区表的每个分区中的行发生变化而分析该分区,但它从不分析分区表本身(父表)。 父表的统计数据描述整个表,只有在对父项运行 ANALYZE 时才会生成。 没有它们,引用分区表的查询可能会得到低效的执行计划。 首次将数据加载到父节点后,先在其上运行 ANALYZE,并在其各分区的数据分布发生显著变化时再次运行:

ANALYZE my_schema.my_partitioned_table;

当你在父分区运行 ANALYZE 时,Postgres 会从所有分区中抽取行来构建父分区的统计数据。 默认情况下,它还会分析每个分区并更新分区的统计数据,这在大表中可能耗时较长。

在 Postgres 18 及以上版本中,使用 ANALYZE ONLY 更新父节点的统计数据,无需分析每个分区。 Postgres 仍然从所有分区中采样行,Autovacuum 会随着分区中行的变化持续分析这些分区。

ANALYZE ONLY my_schema.my_partitioned_table;

在 Postgres 16 和 17 中,对于分区表,ANALYZE 总是分析每个分区。 在任何版本中,要更新某个分区的统计数据,只需在该分区运行 ANALYZE 。

查找没有规划器统计信息的表

要查找没有规划器统计信息的表,可以寻找在 pg_stats 视图中没有行的表。 规划统计数据保存得很持久,因此即使累计统计被重置后,这种检验依然可靠。 查询排除系统模式以及 Lakebase 内部用于数据 API 和同步表的模式。

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;

以 databricks_superuser 角色的成员或表所有者身份运行查询。 视图 pg_stats 只显示当前角色能读取的列的统计数据。 对于启用行级安全的表,除非该角色能绕过行级安全,否则它也会隐藏统计数据。 当前角色无法读取的表,或启用了行级安全的表,即使有统计数据,也可能出现在结果中。

从未包含过数据的表也会出现在结果中,因为 ANALYZE 对于空表不会生成统计数据。 重点关注包含数据并在查询中使用的表。

要为查询返回的表生成规划统计,运行 ANALYZE:

ANALYZE my_schema.my_table;

要分析当前数据库中的每个表,运行 ANALYZE,不带表名。

自定义表的 autovacuum 设置

如果某个表的变更频率太低,达不到默认阈值,你可以仅调低该表的分析阈值。 例如,分析一个小参考表,无论其大小,经过超过10行修改后:

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

要查看表格上的自定义设置,请查询 pg_class.reloptions:

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

要恢复默认行为,请重置设置:

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

即使调低了阈值,如果加载的行数未超过该阈值,并且此后该表从未被修改,autovacuum 也不会分析该表。 对于静态表,加载数据后运行 ANALYZE 。

更多信息请参见Postgres文档: