美文网首页MySQLOracle数据库管理之道系统运维专家
Postgres统计信息的配置和校准

Postgres统计信息的配置和校准

作者: judeshawn | 来源:发表于2019-08-27 15:06 被阅读0次

    前言

    对于大多数关系型数据库，最核心的组件之一是优化器，优化器生成SQL的执行计划，依赖于统计信息，也就是表中的数据分布详情，一般来说，优化器根据统计信息选择执行计划的算法本身不会有什么问题（优化器的模式选择除外，例如Oralce的CBO和RBO），问题往往出现在统计信息是否准确上，不准确的统计信息会导致优化器生成错误的、不合理的执行计划。这个问题对于很多关系型数据库的优化有着重要意义。postgres数据库作为近几年最流行的数据库之一，它在优化器和统计信息方面仍然比较容易存在这种问题。关键在于如何配置和调整统计信息的收集。

    相关统计视图介绍

    • pg_class中统计有每个表和索引中含有的总记录数量——reltuples字段，以及每个表和索引对象在磁盘中占用的数据块数量——relpages字段。
    SELECT relname, relkind, reltuples, relpages
FROM pg_class
WHERE relname LIKE 'tenk1%';

       relname        | relkind | reltuples | relpages
----------------------+---------+-----------+----------
 tenk1                | r       |     10000 |      358
 tenk1_hundred        | i       |     10000 |       30
 tenk1_thous_tenthous | i       |     10000 |       30
 tenk1_unique1        | i       |     10000 |       30
 tenk1_unique2        | i       |     10000 |       30
(5 rows)

    relkind表示对象类型，r表示表，i表示索引。

    出于效率考虑，reltuples和relpages字段并不会实时更新，会有一定的延迟。VACUUM（包括auto acuum后台任务）和ANALYZE操作可以更新表的统计信息。对于reltuples的更新，VACUUM或者ANALYZE操作并不会对整个表进行扫描，而是根据已经扫描的表的一部分对reltuples的值进行增量更新，最后得到的是一个近似值。

    • pg_stats视图记录了以表列为单位的更详细的统计信息，例如视图的n_distinct列，简而言之，体现了该列所有值的选择性，若大于零，表示该列不同值的总数（必定为整数），若小于零，表示该列不同值的总数除以表的总行数的相反数（小于0且大于-1的实数），一般来说，小于0说明选择性较好，即唯一性更好，更容易走索引。若想了解更多列含义请移步官方手册pg_stats系统视图介绍 
    SELECT attname, inherited, n_distinct,
       array_to_string(most_common_vals, E'\n') as most_common_vals
FROM pg_stats
WHERE tablename = 'road';

 attname | inherited | n_distinct |          most_common_vals
---------+-----------+------------+------------------------------------
 name    | f         |  -0.363388 | I- 580                        Ramp+
         |           |            | I- 880                        Ramp+
         |           |            | Sp Railroad                       +
         |           |            | I- 580                            +
         |           |            | I- 680                        Ramp
 name    | t         |  -0.284859 | I- 880                        Ramp+
         |           |            | I- 580                        Ramp+
         |           |            | I- 680                        Ramp+
         |           |            | I- 580                            +
         |           |            | State Hwy 13                  Ramp
(2 rows)

    inherited列表示是否为继承列，f为否，即为表列本身，t为是，表该列继承自其他表列。

    继承是postgres表特有的功能之一，这里暂不讨论

    统计信息的更新和相关配置参数

    前面已经讲到，ACUUM命令和auto acuum后台进程以及ANALYZE命令都会更新统计信息。并且，更新有延迟且不准确。实际上准确程度是可以通过参数来调整控制的，准确性要求越高，更新统计花费时间则越长，则延迟时间越长，因此准确性和实时性不能同时保证，关键在于如何取舍。可以从总体上调整所有统计信息更新操作的准确性，也可以更细粒度地调整特定表或表列的统计信息的准确性。当然，ANALYZE和ACUUM命令也可以手动执行特定表或表列的统计信息的更新。auto acuum后台任务只会在表数据发生较大批量DML语句后才执行。

    • default_statistics_target系统变量用于设置most_common_vals列和histogram_bound列中统计样本的数量，默认值为100，值越大，越准确，但统计信息更新所需时间更长。该变量可在会话或事务级别动态调整，影响的是当前会话或事务中的统计信息更新操作，但在系统级别，需要修改参数文件然后重启才能生效。
    postgres=# show default_statistics_target;
 default_statistics_target 
---------------------------
 100
(1 row)

    会话级别修改

    postgres=# set default_statistics_target=2000;
SET

    事务级别修改

    postgres=# set local default_statistics_target=2000;
WARNING:  SET LOCAL can only be used in transaction blocks
SET

    更新表的统计信息

    analyze road;

    更新表的某列的统计信息

    analyze road(name);
    • ALTER TABLE tbl_name ALTER COLUMN col_name SET STATISTICS integer命令形式用于设置特定表的列在更新统计信息时采集样本的数量，和default_statistics_target参数一样，只是作用范围不一样。
    alter table road alter column road(name) set STATISTICS 2000;

    创建多列统计

    • 什么是多列统计？为什么使用多列统计？

      查询中如果where条件中使用到了多个列时，往往容易遇到多列之间存在相关性而导致查询缓慢的情况。优化器默认认为列与列之间是相互独立的，不存在相关性。什么是相关性？对于表中一行，一个列出现某一个值时，对应的另一列的值从统计的角度并非随机，存在某种关联，正如那个经典的案例，买啤酒的客户具有买纸尿裤的倾向性。像这种存在相关性的多个列，在业务数据表中是普遍存在的，再比如像这种有现实依据的，性别列和身高列，男性普遍高于女性。这就叫相关性。与相关性对应的概念就叫独立性，优化器默认认为任意两列的值的分布从统计学角度上是相互独立的，基于一个错误的假设，得到的结果往往也是错误的，因此优化器容易生成错误的查询执行计划。如果发现SQL语句是由于列与列之间存在较强相关性导致的查询缓慢，能解决这个问题的途径就是，打破传统，把你认为具有相关性的两列或多列联合起来创建并更新统计信息，这就叫多列统计，这样，样本空间不再是一维，而是二维或多维。就拿二维来说，样本为两列所有不同值的两两组合，在这样的样本空间上选取样本统计出来的样本分布肯定是更加准确的。

    • 如何创建多列统计？
      语法：

    postgres=# \h create statistics
Command:     CREATE STATISTICS
Description: define extended statistics
Syntax:
CREATE STATISTICS [ IF NOT EXISTS ] statistics_name
    [ ( statistics_kind [, ... ] ) ]
    ON column_name, column_name [, ...]
    FROM table_name

    \h 用于查看SQL命令帮助，create statistics命令仅在postgres 10.0或以上版本支持。命令参数详解可参阅官方文档https://www.postgresql.org/docs/10/sql-createstatistics.html

    • 演示
      下面我们通过一个实验来对演示多列统计信息的使用场景：

    先创建一张表，并加载数据，然后手动收集统计信息（两列值完全相等，两列具有相关性的极端情况）

    test=# CREATE TABLE t (a INT, b INT);
CREATE TABLE
test=# INSERT INTO t SELECT i % 100, i % 100 FROM generate_series(1, 10000) s(i);
INSERT 0 10000
test=# ANALYZE t;

    然后分别执行下面两个查询，并查看执行计划以及执行统计

    test=# explain analyze select * from t where a=1;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..170.00 rows=100 width=8) (actual time=0.013..1.412 rows=100 loops=1)
   Filter: (a = 1)
   Rows Removed by Filter: 9900
 Planning time: 0.048 ms
 Execution time: 1.437 ms
(5 rows)

test=# explain analyze select * from t where a=1 and b=1;
                                          QUERY PLAN                                           
-----------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..195.00 rows=1 width=8) (actual time=0.015..1.502 rows=100 loops=1)
   Filter: ((a = 1) AND (b = 1))
   Rows Removed by Filter: 9900
 Planning time: 0.072 ms
 Execution time: 1.529 ms
(5 rows)

    explain analyze命令可用于对比实际执行的统计信息和优化器在生成执行计划时预估的统计信息之间的区别。

    在第一个查询中，条件只有a=1，预估的rows=100，以及实际执行的结果rows=100，这是相当准确的，因为查询条件只有单列，单列的数据本身分布很均匀，并且pg_stats系统视图中有单列的统计信息。

    而在第二个查询中，使用了a=1 and b=1 两个条件，查询的结果实际上和第一个查询完全相同，实际执行结果也是rows=100，但是预估值为rows=1。

    为什么第二个查询预估的结果行会是1呢？因为a、b两列各自的统计信息中n_distinct均为100，因此选择性（selectivity）为1%，在各列相互独立的默认假设下，优化器计算出a=1且b=1的联合概率为1%X1%=0.01%，因此预估条件筛选出的结果行为总行数的0.01%：10000*0.01%=1

    test=# select tablename,attname,n_distinct from pg_stats where tablename like 't';
 tablename | attname | n_distinct 
-----------+---------+------------
 t         | a       |        100
 t         | b       |        100
(2 rows)

    现在我们对a,b两列创建多列统计，看看优化器的预估情况会如何变化

    test=# create statistics stats_t_a_b on a,b from t;
CREATE STATISTICS
test=# 
test=# analyze t;
test=# explain analyze select * from t where a=1;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..170.00 rows=100 width=8) (actual time=0.019..1.483 rows=100 loops=1)
   Filter: (a = 1)
   Rows Removed by Filter: 9900
 Planning time: 0.111 ms
 Execution time: 1.522 ms
(5 rows)

test=# explain analyze select * from t where a=1 and b=1;
                                           QUERY PLAN                                            
-------------------------------------------------------------------------------------------------
 Seq Scan on t  (cost=0.00..195.00 rows=100 width=8) (actual time=0.023..1.665 rows=100 loops=1)
   Filter: ((a = 1) AND (b = 1))
   Rows Removed by Filter: 9900
 Planning time: 0.129 ms
 Execution time: 1.705 ms
(5 rows)

    不出所料，创建多列统计并重新更新统计信息后，第一个查询预估依然准确，第二个查询的预估值和实际值也变得准确了，因为测试数据分布绝对均匀，实际生产环境很少有这样的情况，不太可能出现预估和实际一模一样，但我们要做的是，尽可能让统计信息更准确，让执行计划更合理。

    另外，如果想要查看已创建的多列统计信息的详情，可以查看pg_statistic_ext系统视图

    test=# select stxname,stxkind,stxndistinct from pg_statistic_ext where stxname like 'stats_t_a_b';
   stxname   | stxkind | stxndistinct  
-------------+---------+---------------
 stats_t_a_b | {d,f}   | {"1, 2": 100}
(1 row)

    stxndistinct列为{"1, 2": 100}，表示表中的第一列和第二列作的联合，联合之后的二维样本空间的ndistinct，仍然为100，因为两列的值完全相等。

    pg_statistic_ext更多字段含义见官方手册

    统计信息校准生产案例

    B表是生产环境的一张分区表，分区键为时间字段按月分区，每个分区平均约300万行，存放了最近2年的数据，数据总量上亿。由于统计信息的不准确，导致查询该表的语句在和另一张表A关联的时候走了全表扫描。B表是被驱动表，由于表A上的条件能够筛选出较少行（约15000行，A表使用了索引，没有问题），因此表A为驱动表，A和B使用字段do_id(订单号)关联，实际的执行结果为50000行左右，从上亿的数据量里获取50000行数据，却走了全表扫描，让人难以接受。从执行计划里的统计信息来看，预估的关联结果为将近920万，do_id列本身是有索引的，因此断定为B表上do_id的统计信息非常不准确。

    下面是截取的执行计划的关键节点，tt子查询即为上述基于A表上的子查询，cdc_tm_do_item分区表即为上述B表

    ->  Hash Join  (cost=9955271.45..11817023.19 rows=9200732 width=257)
   Hash Cond: ((tt.do_id)::text = (tdi.do_id)::text)
    ->  Subquery Scan on tt  (cost=349867.12..353482.41 rows=16985 width=212)
    ->  Hash  (cost=7057951.26..7057951.26 rows=114411926 width=55)
                  ->  Append  (cost=0.00..7057951.26 rows=114411926 width=55)                         
                          ->  Seq Scan on cdc_tm_do_item_1801 tdi_3  (cost=0.00..294617.30 rows=6042930 width=54)
                          ...
                          ->  Seq Scan on cdc_tm_do_item_1912 tdi_26  (cost=0.00..10.10 rows=10 width=622)
                          ->  Seq Scan on cdc_tm_do_item_min tdi_27  (cost=0.00..10.10 rows=10 width=622)

    • 验证猜测：

      在B表上选择了一个分区进行验证，按照do_id分组求和，发现少量订单号出现次数特别多，从业务逻辑上讲就是这些订单中的商品数量非常多，但这样的订单是很少的，应该是B2B订单，而大部分单个订单的商品数量并不多（B2C），1~3个占绝大多数（下图中未体现）。因此数据的倾斜程度是比较严重的。


    再查看了一下统计信息，n_distinct值约为16万，和实际值426万相差二十几倍

    itldw=# explain analyze select count(*),do_id from tms.cdc_tm_do_item_1906 group by do_id  order by 1 desc;
                                                                            QUERY PLAN                                                                             
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=723663.61..724070.27 rows=162664 width=19) (actual time=9330.948..9904.324 rows=4262016 loops=1)
   Sort Key: (count(*)) DESC
   Sort Method: quicksort  Memory: 529579kB
   ->  Finalize HashAggregate  (cost=707957.15..709583.79 rows=162664 width=19) (actual time=7518.696..8493.436 rows=4262016 loops=1)
         Group Key: do_id
         ->  Gather  (cost=672171.07..706330.51 rows=325328 width=19) (actual time=2781.572..5614.352 rows=4815026 loops=1)
               Workers Planned: 2
               Workers Launched: 2
               ->  Partial HashAggregate  (cost=671171.07..672797.71 rows=162664 width=19) (actual time=2761.308..3325.646 rows=1605009 loops=3)
                     Group Key: do_id
                     ->  Parallel Seq Scan on cdc_tm_do_item_1906  (cost=0.00..644443.38 rows=5345538 width=11) (actual time=0.023..1472.686 rows=4275688 loops=3)
 Planning time: 0.427 ms
 Execution time: 10126.092 ms
(13 rows)
    • 解决方法

    会话级别修改default_statistics_target变量为2000，然后执行analyze命令更新B表do_id列的统计信息

    itldw=# set  default_statistics_target=2000;
SET
itldw=# analyze tms.cdc_tm_do_item_1906(do_id);
ANALYZE

    然后验证统计信息变化情况，n_distinct值变为了-0.162037

    itldw=# SELECT tablename,attname, n_distinct 
FROM pg_stats
WHERE tablename = 'cdc_tm_do_item_1906' and attname like 'do_id';
      tablename      | attname | n_distinct 
---------------------+---------+------------
 cdc_tm_do_item_1906 | do_id   |  -0.162037
(1 row)

    然后对比一下统计值和实际值的差别，统计值为207万，和实际值靠近了许多。

    itldw=# explain analyze select count(*),do_id from tms.cdc_tm_do_item_1906 group by do_id  order by 1 desc;
                                                                   QUERY PLAN                                                                   
------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=1022283.12..1027479.28 rows=2078464 width=19) (actual time=12689.806..13178.947 rows=4262016 loops=1)
   Sort Key: (count(*)) DESC
   Sort Method: quicksort  Memory: 529579kB
   ->  HashAggregate  (cost=783393.96..804178.60 rows=2078464 width=19) (actual time=10358.070..11831.147 rows=4262016 loops=1)
         Group Key: do_id
         ->  Seq Scan on cdc_tm_do_item_1906  (cost=0.00..719258.64 rows=12827064 width=11) (actual time=0.059..6093.486 rows=12827064 loops=1)
 Planning time: 0.435 ms
 Execution time: 13508.033 ms
(8 rows)

    前面只是对其中一个分区进行了效果和效率验证，default_statistics_target=2000时，对B表的do_id列更新统计信息大约花费了20s左右。因此校准所有分区统计信息大概需要5~10分钟。然后便开始对所有分区进行统计信息更新

    itldw=# set  default_statistics_target=2000;
itldw=# analyze tms.cdc_tm_do_item(do_id);
ANALYZE

    更新完之后，再检查目标SQL语句的执行计划：哈希关联变成了嵌套循环，B表全表扫描变成了索引扫描
    最终执行时间从原来的300s左右变成了2s多。

      ->  Sort  (cost=8857890.24..8892214.55 rows=13729723 width=257) (actual time=2355.325..2355.566 rows=4231 loops=1)
        ->  Nested Loop  (cost=362999.33..5540775.72 rows=13729723 width=257) (actual time=1922.736..2348.394 rows=4231 loops=1)
              ->  Finalize GroupAggregate  (cost=362999.20..366933.92 rows=19397 width=353) (actual time=1922.099..1952.705 rows=2505 loops=1)
              ->  Append  (cost=0.14..265.32 rows=140 width=56) (actual time=0.143..0.156 rows=2 loops=2505)
                    ->  Index Scan using cdc_tm_do_item_1801_do_id_idx on cdc_tm_do_item_1801 tdi_3  (cost=0.43..10.58 rows=5 width=54) (actual time=0.006..0.006 rows=0 loops=2505)
                    ...
                    ->  Index Scan using cdc_tm_do_item_1912_do_id_idx on cdc_tm_do_item_1912 tdi_26  (cost=0.14..0.15 rows=1 width=622) (actual time=0.000..0.000 rows=0 loops=2505)

    因为只是修改了会话级别的参数，default_statistics_target只会对当前会话的手工执行analyze命令生效，后续大批量的DML语句触发的auto acuum后台任务，仍然会按照default_statistics_target=100的系统默认值来更新统计信息，因此最新的经常DML的分区仍然容易再次出现不准确的情况。但我们可以使用alter table set statistics语句来更细粒度配置特定表列在更新统计信息时使用的统计参数（和default_statistics_target一样）：

    alter table tms.cdc_tm_do_item alter column do_id set STATISTICS 2000;

    这样，就不用担心后面B表do_id列的统计信息不准确了。

    总的来说，一旦你发现某表某列统计信息不准确，就先调高会话级别default_statistics_target参数，手工更新一次该列的统计信息，然后使用alter table set statistics语句修改该列的统计配置参数即可。

    附件

    SQL文本：

    SELECT
    tt.do_id AS 交货单号,
    tt.ref_no AS 外部单据号,
    tt.create_do_time AS 创单时间,
    tt.order_type_name AS 单据类型,
    tt.platform AS 平台,
    tt.warehouse AS 仓库号,
    tt.warehouse_desc AS 仓库名称,
    tt.cust_name AS 工厂,
    tt.deliver_cubage AS 发货体积,
    tt.deliver_weight AS 发货重量,
    tt.sys_cubage AS 系统体积,
    tt.sys_weight AS 系统重量,
    tt.deliver_cubage - tt.sys_cubage AS 体积差异,
    tt.deliver_weight - tt.sys_weight AS 重量差异,
    COUNT (DISTINCT tdi.prod_name) AS 物料种类数,
    SUM (tdi.num) AS 物料数
FROM
    (
        SELECT
            td.do_id,
            td.ref_no,
            td.order_type_name,
            td.cust_name,
            td.vendee_name,
            td.send_name,
            td.carrier_name,
            td.create_do_time,
            w.platform,
            CASE
        WHEN w.warehouse_type = 'B2B' THEN
            w.warehouse_id
        ELSE
            w.deliver_warehouse_no
        END AS warehouse,
        w.warehouse_desc,
        w.warehouse_type,
        td.sum_cubage AS sys_cubage,
        td.sum_weight AS sys_weight,
        SUM (tdtt.cubage) AS deliver_cubage,
        SUM (tdtt.weight) AS deliver_weight,
        CASE
    WHEN td.sum_cubage = 0 THEN
        CASE
    WHEN SUM (tdtt.cubage) <= 0.1 THEN
        '否'
    ELSE
        '是'
    END
    ELSE
        CASE
    WHEN ABS (
        SUM (tdtt.cubage) - td.sum_cubage
    ) / td.sum_cubage > 0.1 THEN
        '是'
    ELSE
        '否'
    END
    END AS cubage_change,
    CASE
WHEN td.sum_weight = 0 THEN
    CASE
WHEN SUM (tdtt.weight) <= 0.1 THEN
    '否'
ELSE
    '是'
END
ELSE
    CASE
WHEN ABS (
    SUM (tdtt.weight) - td.sum_weight
) / td.sum_weight > 0.1 THEN
    '是'
ELSE
    '否'
END
END AS weight_change
FROM
    tms.cdc_tm_do td
JOIN tms.cdc_tm_do_trans_task tdtt ON td.do_id = tdtt.do_id
JOIN xconfig.warehouse w ON COALESCE (
    td.wm_houseid,
    td.deliver_warehouse_no
) = w.warehouse_id
WHERE
    do_flag = 'C'
AND w.warehouse_type IN ('B2B', 'DC')
AND td.create_do_time >= ('2019-07' || '-01') :: TIMESTAMP
AND td.create_do_time < ('2019-07' || '-01') :: TIMESTAMP + '1 month'
AND w.platform IN ('上海', '北京')
AND CASE
WHEN w.warehouse_type = 'B2B' THEN
    w.warehouse_id
ELSE
    w.deliver_warehouse_no
END IN ('B07B', '0001')
GROUP BY
    td.do_id,
    td.ref_no,
    td.order_type_name,
    td.cust_name,
    td.vendee_name,
    td.send_name,
    td.carrier_name,
    td.create_do_time,
    w.platform,
    CASE
WHEN w.warehouse_type = 'B2B' THEN
    w.warehouse_id
ELSE
    w.deliver_warehouse_no
END,
 w.warehouse_desc,
 w.warehouse_type,
 td.sum_cubage,
 td.sum_weight
    ) tt
JOIN tms.cdc_tm_do_item tdi ON tt.do_id = tdi.do_id
WHERE
    (tt.cubage_change = '是'
OR tt.weight_change = '是')
GROUP BY
    tt.do_id,
    tt.ref_no,
    tt.create_do_time,
    tt.order_type_name,
    tt.platform,
    tt.warehouse,
    tt.warehouse_desc,
    tt.cust_name,
    tt.deliver_cubage,
    tt.deliver_weight,
    tt.sys_cubage,
    tt.sys_weight;

    相关文章

      网友评论

        本文标题：Postgres统计信息的配置和校准

        本文链接：https://www.haomeiwen.com/subject/itzlectx.html

        延伸阅读

        深度阅读

        • 您也可以注册成为美文阅读网的作者，发表您的原创作品、分享您的心情！

        栏目导航

        热点阅读

        MySQL

        Oracle数据库管理之道

        系统运维专家

        数据库

        关于我们|服务条款|联系我们|Postgres统计信息的配置和校准|投稿指南|网站地图|RSS订阅|排版工具|手机版

        提供经典美文摘抄,优美散文欣赏,现代诗歌精选,短篇小说,心情随笔,表白情书范文,故事会在线阅读欣赏

        Copyright © 2014-2023 Haomeiwen.com All Rights Reserved. 好美文阅读网 版权所有

        备案信息：桂公网安备 45052102000051号 · 桂ICP备13007215号-3

        本站所收录作品、热点评论等信息部分来源互联网，目的只是为了系统归纳学习和传递资讯

        所有作品版权归原创作者所有，与本站立场无关，如不慎侵犯了你的权益，请联系我们告知，我们将做删除处理！