postgresql数据库中analyze统计信息详解

原创已于 2023-09-21 20:57:04 修改 · 2.7k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #postgresql

于 2023-08-12 22:46:36 首次发布

本文介绍了PostgreSQL中ANALYZE命令用于更新统计信息以优化查询性能，以及EXPLAINANALYZE用于获取实际执行计划的时间和成本。还讨论了如何调整default_statistics_target以提高统计准确性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

简介

在postgresql数据库中，规划器会根据统计信息对我们执行语句选择最有执行路径。所以统计信息的有效，及时的更新对于我们优化数据库有较大帮助。在进程 autovacuum launcher中会数据库空闲时清理dead touple 的同时更新统计信息

ANALYZE语法

1 ANALYZE TABLENAME

使用语法：

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

where option can be one of:

    VERBOSE [ boolean ]
    SKIP_LOCKED [ boolean ]

and table_and_columns is:

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

涉及参数

这里可以添加 VERBOSE，SKIP_LOCKED 两个参数

VERBOSE

输出分析的详细进程

SKIP_LOCKED

analyze 命令只需在表上加一个读锁，当执行到当前表时，当前表的读锁被堵塞时，将直接跳过，不会等待。

使用影响

analyze 命令只需在表上加一个读锁，因此它可以与表上的其他SQL命令并发执行。ANALYZE命令会收集表的每个字段的直方图和最常用数值的列表

对于大表，ANALYZE命令只读取表的部分内容做一个随机抽样，不读取表的所有内容，这样就保证了即使是在很大的表上也只需要很少时间就可以完成统计信息的收集。统计信息只是近似的结果，即使表内容实际上没有改变，运行ANALYZE命令后EXPLAIN命令显示的执行计划中的COST值也会有一些变化。为了增加所收集的统计信息的准确度，可以增大随机抽样比例，这可以通过调整参数“default_statistics_target”来实现，该参数可在session级别设置，比如在分析不同的表时设置不同的值。

示例

创建测试表并插入数据

create table text(id int);
insert into text(id) select generate_series(1,1234567);

调整参数default_statistics_target 并analyze查看执行时长对比。

当default_statistics_target值越大时，设置的抽样数据也就越长，analyze的执行时间也就越长.

2 EXPLAIN ANALYZE

使用语法

主要用于查看语句的实际运行时间

EXPLAIN [ ANALYZE ] [ VERBOSE ] statement

使用影响

EXPLAIN ANALYZE选项通过实际执行SQL来获得SQL命令的实际执行计划。ANALYZE选项

查看到的执行计划因为真正被执行过，所以可以看到执行计划每一步耗费了多长时间，以

及它实际返回的行数。EXPLAIN 没有增加analyze关键字的时候并不会真正执行语句，给出的执行计划cost也是一个预估值。

示例

由于想看到实际的执行计划，但是EXPLAIN ANALYZE会在真正的执行SQL，所以我们可以放到一个事务中去，执行完后再执行回滚操作。

postgres=# select  count(1) from text where id is null ;
 count 
-------
     0
(1 row)

Time: 41.889 ms
postgres=# begin;   --开启事务
BEGIN
Time: 0.122 ms
postgres=*# explain analyze update text set id=null where id%41=0 ;  --查看语句执行计划
                                                   QUERY PLAN                                                   
----------------------------------------------------------------------------------------------------------------
 Update on text  (cost=0.00..8718.85 rows=0 width=0) (actual time=65.310..65.311 rows=0 loops=1)
   ->  Seq Scan on text  (cost=0.00..8718.85 rows=2232 width=10) (actual time=0.023..47.690 rows=10889 loops=1)
         Filter: ((id % 41) = 0)
         Rows Removed by Filter: 435501
 Planning Time: 0.055 ms
 Execution Time: 65.339 ms
(6 rows)

Time: 65.740 ms
postgres=*# select  count(1) from text where id is null ;  --语句已经变更表数据
 count 
-------
 10889
(1 row)

Time: 36.887 ms
postgres=*# rollback            --表数据回滚
;
ROLLBACK
Time: 0.153 ms
postgres=# end;
WARNING:  there is no transaction in progress
COMMIT
Time: 0.372 ms
postgres=# select  count(1) from text where id is null ;  --表数据恢复为事务开启前状态
 count 
-------
     0
(1 row)

Time: 117.977 ms
postgres=#

可以看到当在查看执行计划的时候，analyze参数会真正的执行后排的语句。但是我们可以对事务进行回滚，这样我们能看到最真实的语句执行计划，同时也不会破坏数据。