PostgreSQL手动收集统计信息的方法与技巧

了解 PostgreSQL 统计信息的重要性

在 PostgreSQL 数据库管理系统中，统计信息对于查询优化器高效运作至关重要。这些统计信息描述了数据库表中数据的分布情况，帮助查询优化器选择最优的查询执行计划。例如，它记录了表中列的基数（不同值的数量）、数据的存储布局以及数据值的频率分布等。

统计信息与查询优化的关系

当一个查询提交到 PostgreSQL 时，查询优化器会根据现有的统计信息来评估不同的查询执行路径，以确定最有效的方式来获取数据。如果统计信息不准确或过时，优化器可能会选择次优的执行计划，导致查询性能下降。例如，假设统计信息表明某列只有少量不同的值，但实际上该列有大量不同值。在这种情况下，查询优化器可能会错误地选择全表扫描而不是索引扫描，因为它基于错误的基数估计认为索引扫描的成本更高。

自动与手动收集统计信息

PostgreSQL 提供了自动和手动收集统计信息的机制。自动统计信息收集通过 autovacuum 守护进程定期进行。autovacuum 会在表发生一定数量的插入、更新或删除操作后，自动触发统计信息的更新。然而，在某些情况下，自动收集可能无法满足需求，需要手动干预。比如，在进行大规模数据加载或数据结构发生重大变化后，立即更新统计信息以确保查询性能，手动收集统计信息就显得尤为重要。

手动收集统计信息的方法

使用 ANALYZE 命令

ANALYZE 命令是 PostgreSQL 中手动收集统计信息的主要工具。它会扫描指定的表或数据库中的所有表，并更新相关的统计信息。

基本语法

对单个表进行统计信息收集：

ANALYZE [VERBOSE] table_name;

其中，VERBOSE 是可选参数，加上它会在执行过程中输出详细信息，帮助用户了解分析进度。例如：

ANALYZE VERBOSE users;

对整个数据库中的所有表进行统计信息收集：

ANALYZE [VERBOSE];

这会对数据库中的每一个表依次执行分析操作。例如：

ANALYZE VERBOSE;

ANALYZE 的工作原理

当执行 ANALYZE 时，PostgreSQL 会读取表中的数据块，抽样数据以估计列的基数、数据分布等统计信息。它会更新系统目录中的相关统计信息表，如 pg_statistic。对于有索引的列，ANALYZE 还会收集关于索引的统计信息，如索引的选择性（即索引中不同值与总行数的比例）。这些更新后的统计信息会被查询优化器用于生成更准确的执行计划。

对特定列进行统计信息收集

有时候，可能只需要更新特定列的统计信息，而不是整个表。这可以通过在 ANALYZE 命令中指定列名来实现。

语法示例

ANALYZE [VERBOSE] table_name (column1, column2);

例如，假设我们只关心 users 表中 email 和 phone_number 列的统计信息，可以这样执行：

ANALYZE VERBOSE users (email, phone_number);

适用场景

这种方式适用于当只有特定列的数据发生了显著变化，而其他列的数据相对稳定的情况。比如，在一个电商订单表中，order_amount 列经常被更新，而其他列如 customer_id、order_date 等变化相对较少。此时，只对 order_amount 列进行统计信息收集可以节省时间和资源，同时确保查询优化器在涉及 order_amount 列的查询中能获得准确的统计信息。

对分区表进行统计信息收集

PostgreSQL 支持分区表，对于分区表的统计信息收集，需要考虑其特殊性。

对整个分区表进行分析

可以像对普通表一样对分区表执行 ANALYZE 命令，这会递归地分析所有分区。

ANALYZE [VERBOSE] partitioned_table;

例如，假设有一个按日期分区的销售记录表 sales，可以这样更新其统计信息：

ANALYZE VERBOSE sales;

对单个分区进行分析

如果只想更新某个特定分区的统计信息，可以直接对该分区执行 ANALYZE。假设 sales 表按日期分区，有一个分区 sales_202301，可以执行：

ANALYZE VERBOSE sales_202301;

注意事项

在对分区表进行统计信息收集时，要注意如果只更新了部分分区的统计信息，可能会导致查询优化器在处理涉及多个分区的查询时出现不准确的执行计划。因此，在大多数情况下，建议对整个分区表或至少相关的一组分区进行统计信息更新，以确保查询优化器能获得一致且准确的统计信息。

手动收集统计信息的技巧

优化分析频率

确定合适的手动收集统计信息的频率是一个关键技巧。过于频繁地执行 ANALYZE 会消耗系统资源，影响正常的数据库操作；而频率过低则可能导致统计信息过时，影响查询性能。

根据业务活动确定频率

对于数据插入、更新和删除操作频繁的表，应适当提高分析频率。例如，在一个实时交易系统中，交易记录表可能每秒都有大量的新记录插入，这种情况下可能需要每隔几分钟或几小时就执行一次 ANALYZE。而对于数据相对静态的表，如地区代码表，可能几个月甚至一年才需要手动分析一次。

结合自动收集机制

可以结合 autovacuum 的自动收集机制来优化手动分析频率。例如，在数据加载高峰期，可以暂时禁用 autovacuum，手动执行 ANALYZE 以确保统计信息及时更新。在正常业务时段，可以让 autovacuum 按照默认设置工作，只在必要时手动干预。

处理大数据量的表

当面对大数据量的表时，手动收集统计信息可能会花费较长时间，甚至影响数据库的正常运行。

抽样分析

PostgreSQL 允许在 ANALYZE 时进行抽样，通过指定 SAMPLE 子句来控制抽样比例。例如，对一个非常大的用户表 users，只抽样 10%的数据进行分析：

ANALYZE VERBOSE users SAMPLE 10 PERCENT;

抽样分析可以显著减少分析时间，但可能会降低统计信息的准确性。因此，在选择抽样比例时，需要权衡时间和准确性的关系。对于数据分布相对均匀的表，较低的抽样比例可能也能提供较为准确的统计信息；而对于数据分布不均匀的表，可能需要较高的抽样比例。

分阶段分析

对于超大型表，可以将分析过程分成多个阶段。例如，先对表的一小部分数据（如前 10000 行）进行分析，获取初步的统计信息。然后，基于这些初步信息，对剩余数据进行抽样分析。这种方法可以在一定程度上平衡分析时间和统计信息的准确性。

利用并行分析

从 PostgreSQL 10 开始，支持并行 ANALYZE。这对于大型表的统计信息收集非常有用，可以显著缩短分析时间。

启用并行分析

要启用并行 ANALYZE，需要设置 parallel_setup_cost 和 parallel_tuple_cost 参数，并且确保 max_parallel_workers_per_gather 参数设置为大于 0 的值。例如，设置 parallel_setup_cost 为 1000，parallel_tuple_cost 为 0.1，max_parallel_workers_per_gather 为 4：

SET parallel_setup_cost = 1000;
SET parallel_tuple_cost = 0.1;
SET max_parallel_workers_per_gather = 4;

然后执行 ANALYZE 命令，PostgreSQL 会根据设置并行执行分析操作：

ANALYZE VERBOSE large_table;

注意事项

并行分析虽然可以加快速度，但会消耗更多的系统资源，如 CPU 和内存。在启用并行分析时，要确保数据库服务器有足够的资源来支持并行操作，避免对其他数据库业务产生负面影响。同时，并非所有的表结构和数据分布都适合并行分析，对于一些小型表或数据分布非常不均匀的表，并行分析可能并不会带来显著的性能提升。

监控和验证统计信息

在手动收集统计信息后，需要监控和验证这些信息是否准确有效，以确保查询性能得到提升。

查看统计信息

可以通过查询系统目录表 pg_statistic 来查看具体的统计信息。例如，要查看 users 表中 age 列的统计信息，可以执行以下查询：

SELECT starelid::regclass, staattnum, stadistinct, stakind1, stavalues1
FROM pg_statistic
WHERE starelid = 'users'::regclass AND staattnum = attnum
AND attname = 'age'
FROM pg_attribute
WHERE attnum > 0 AND NOT attisdropped AND attrelid = 'users'::regclass;

stadistinct 字段表示该列不同值的估计数量，stakind1 和 stavalues1 等字段提供了关于数据分布的信息。通过查看这些信息，可以了解统计信息是否符合预期。

验证查询性能

手动收集统计信息的最终目的是提升查询性能。因此，可以通过执行一些关键查询，并比较收集统计信息前后的查询执行时间来验证效果。例如，对于一个复杂的多表关联查询，可以使用 EXPLAIN ANALYZE 命令来获取查询执行计划和实际执行时间：

EXPLAIN ANALYZE SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.customer_id
WHERE orders.order_date > '2023 - 01 - 01';

在执行 ANALYZE 前后分别运行上述命令，对比查询执行时间和执行计划的变化。如果查询执行时间明显缩短，并且执行计划变得更合理，说明统计信息的收集起到了积极作用。

监控系统资源使用

在手动收集统计信息过程中，要监控系统资源的使用情况，如 CPU、内存和磁盘 I/O。可以使用操作系统的监控工具，如 top（在 Linux 系统中）或 Activity Monitor（在 macOS 系统中）来实时查看资源占用。如果发现资源使用过高，影响了数据库的正常运行，可能需要调整分析策略，如降低抽样比例或避免在业务高峰期执行分析操作。

处理统计信息收集过程中的问题

分析过程中的锁问题

在执行 ANALYZE 时，PostgreSQL 会对表获取共享锁。这意味着在分析过程中，其他事务可以读取表数据，但不能对表进行写入操作。如果分析时间过长，可能会导致写操作长时间等待。

缩短分析时间

如前文所述，可以通过抽样分析或并行分析等方法来缩短 ANALYZE 的执行时间，从而减少锁的持有时间。例如，对于一个写操作频繁的表，可以采用较高的抽样比例进行快速分析，以尽快释放锁。

选择合适的时间

尽量选择在数据库写操作较少的时间段进行手动统计信息收集。例如，在凌晨业务低谷期执行 ANALYZE，这样可以减少对正常业务的影响。

统计信息不准确的问题

尽管采取了各种措施，有时统计信息仍然可能不准确，导致查询性能不佳。

重新分析

如果发现统计信息不准确，可以再次执行 ANALYZE，尝试使用不同的参数，如调整抽样比例或不使用抽样进行全表分析。例如，如果之前使用了 10%的抽样比例进行分析，但结果不准确，可以尝试执行全表分析：

ANALYZE VERBOSE table_name;

检查数据分布

仔细检查表的数据分布情况，看是否存在异常值或数据倾斜的问题。对于数据分布不均匀的列，可能需要更精细的统计信息收集策略。例如，可以对数据进行分组，分别对不同组的数据进行统计信息收集，以提高统计信息的准确性。

与其他数据库操作的配合

手动收集统计信息需要与其他数据库操作配合，以确保数据库的整体性能和稳定性。

与备份和恢复操作的配合

在进行数据库备份之前，最好确保统计信息是最新的。这样，在恢复数据库后，查询优化器可以立即使用准确的统计信息。如果在备份之后进行了大量的数据修改，然后再恢复数据库，可能需要在恢复后手动执行 ANALYZE 来更新统计信息。

与索引维护的配合

索引的创建、删除或重建会影响数据的存储布局和访问方式，进而影响统计信息的准确性。在进行索引维护操作后，应该及时执行 ANALYZE 来更新相关的统计信息。例如，在创建一个新的索引后，执行：

ANALYZE VERBOSE table_name;

这样可以确保查询优化器能正确评估使用新索引的成本，从而选择最优的查询执行计划。

与数据加载操作的配合

在进行大规模数据加载后，数据的分布和基数等统计信息会发生变化。此时，应尽快执行 ANALYZE 来更新统计信息。例如，使用 COPY 命令加载大量数据到一个表后，立即执行：

ANALYZE VERBOSE loaded_table;

以确保查询优化器能基于最新的统计信息生成执行计划，避免因统计信息过时导致的查询性能问题。

总结手动收集统计信息的要点

手动收集 PostgreSQL 统计信息是数据库管理中的一项重要任务，需要综合考虑多方面因素。合理使用 ANALYZE 命令，掌握对特定列、分区表的分析方法，运用优化分析频率、处理大数据量、利用并行分析等技巧，同时做好统计信息的监控和验证，妥善处理分析过程中的问题，并与其他数据库操作良好配合，才能确保数据库查询性能的稳定和提升，为应用程序提供高效的数据访问支持。通过不断实践和优化这些方法与技巧，可以更好地管理 PostgreSQL 数据库，满足不同业务场景的需求。在实际操作中，要根据数据库的具体情况和业务需求灵活调整策略，以达到最佳的性能效果。