PostgreSQL 的 ANALYZE 命令

PostgreSQL 的 ANALYZE 命令

ANALYZE 是 PostgreSQL 中用于收集数据库对象统计信息的关键命令，这些统计信息对于查询优化器生成高效执行计划至关重要。

一 ANALYZE 命令

1.1 基本语法

ANALYZE [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
ANALYZE [ VERBOSE ] [ table_and_columns [, ...] ]

where option can be one of:

    VERBOSE [ boolean ]
    SKIP_LOCKED [ boolean ]
    BUFFER_USAGE_LIMIT size

and table_and_columns is:

    table_name [ ( column_name [, ...] ) ]

1.2 锁级别

1. 不会锁表（不阻塞 DML 操作）

• 只获取 ShareUpdateExclusiveLock 锁
  • 允许并发读取和写入
  • 仅阻塞 ALTER TABLE、DROP TABLE、VACUUM FULL 等DDL操作

2. 并发影响：
   • ✅ 允许并发 SELECT/INSERT/UPDATE/DELETE
   • ✅ 允许并发 CREATE INDEX CONCURRENTLY
   • ❌ 阻塞 ALTER TABLE、DROP TABLE、VACUUM FULL

1.3 对索引的影响

当对表执行 ANALYZE 时：

• 会收集该表所有索引的统计信息
• 不会重建或修改索引本身
• 仅更新 pg_statistic 系统目录中的统计信息

二 类似操作的锁对比

三 使用场景

3.1 常规维护

-- 分析单个表
ANALYZE customers;

-- 分析特定列
ANALYZE customers (customer_id, name);

-- 分析整个数据库
ANALYZE;

3.2 数据大量变更后

-- 批量导入数据后
COPY employees FROM '/path/to/data.csv';
ANALYZE employees;

3.3 性能调优

-- 查询性能下降时
ANALYZE VERBOSE orders;

四 配置参数

1. 自动分析：

   autovacuum_analyze_scale_factor = 0.1  -- 10%行变化后触发
   autovacuum_analyze_threshold = 50      -- 最少50行变化
   

2. 采样设置：

   default_statistics_target = 100        -- 统计信息详细程度
   alter table large_table set (analyze_sample_percentage = 5); -- 对大表减少采样
   

五 监控分析状态

1. 查看最后分析时间：

   SELECT schemaname, relname, last_analyze, analyze_count 
   FROM pg_stat_user_tables;
   

输出示例：

 schemaname |     relname      |         last_analyze          | analyze_count 
------------+------------------+-------------------------------+---------------
 yewu1      | t4               |                               |             0
 yewu1      | t1               | 2025-05-03 18:51:47.366276-07 |             1
 yewu1      | t2               |                               |             0
 public     | pgbench_history  |                               |             0
 yewu1      | test6            |                               |             0
 public     | pgbench_tellers  |                               |             0
 yewu1      | test5            |                               |             0
 public     | pgbench_branches |                               |             0
 yewu1      | test3            |                               |             0
 yewu1      | test2            |                               |             0
 yewu1      | t3               |                               |             0
 public     | pgbench_accounts |                               |             0
 yewu1      | test10           |                               |             0
 yewu1      | test4            |                               |             0
(14 rows)

2. 检查待分析变更量：

   SELECT schemaname, relname, 
          n_mod_since_analyze,
          n_live_tup,
          round(n_mod_since_analyze*100.0/nullif(n_live_tup,0),2) as mod_percent
   FROM pg_stat_user_tables
   ORDER BY n_mod_since_analyze DESC;
   

输出示例：

 schemaname |     relname      | n_mod_since_analyze | n_live_tup | mod_percent 
------------+------------------+---------------------+------------+-------------
 yewu1      | t3               |                 190 |         10 |     1900.00
 yewu1      | test10           |                   4 |          4 |      100.00
 yewu1      | t2               |                   0 |      10000 |        0.00
 public     | pgbench_history  |                   0 |          0 |            
 yewu1      | test6            |                   0 |          0 |            
 public     | pgbench_tellers  |                   0 |          0 |            
 yewu1      | test5            |                   0 |          0 |            
 public     | pgbench_branches |                   0 |          0 |            
 yewu1      | test3            |                   0 |          0 |            
 yewu1      | test2            |                   0 |          0 |            
 public     | pgbench_accounts |                   0 |          0 |            
 yewu1      | t4               |                   0 |      10000 |        0.00
 yewu1      | test4            |                   0 |          0 |            
 yewu1      | t1               |                   0 |          0 |            
(14 rows)

六 性能考虑

1. 资源使用：

   • 会消耗CPU和I/O资源
   • 对大表可能耗时较长

2. 最佳实践：

   • 在低峰期执行大表分析
   • 对关键表设置更频繁的自动分析
   • 超大表考虑减小采样比例

七 与VACUUM的区别

更多详细信息请查看官方文档：

https://www.postgresql.org/docs/16/sql-analyze.html

PostgreSQL 中的 ANALYZE 命令不会锁表，是安全的维护操作，可以随时在生产环境执行。如果需要重建索引（而非更新统计信息），则应使用 REINDEX 命令，并注意其锁行为。

谨记：心存敬畏，行有所止。