Beam指定列收集统计信息

背景介绍

多维排序键最多支持8列且在自增列上表现不佳。因此如果查询中存在多列筛选，其中部分列不适合加入排序键时，可以指定收集、统计这些列的信息而不加入排序键，在查询时可以依赖统计信息过滤更多数据块，取得更好的扫描性能。具体内容，请参见Beam排序优化（7.0版）。

前提条件

仅云原生数据仓库 AnalyticDB PostgreSQL 版存储弹性模式v7.0.5及以上版本支持Beam指定列收集统计信息功能。

注意事项

• 使用Beam收集指定列的统计信息时，目前只在指定列上按数据块收集MIN和MAX信息。

• 使用Beam收集指定列的统计信息后会产生多余的存储容量，但是相比数据本身容量来说，可以忽略不计。

指定列收集统计信息

创建表指定列收集统计信息

在创建Beam表时，在列定义中添加option (stat_enabled=true)可以设置该列收集统计信息，如下SQL语句为ftime时间戳列收集统计信息。

CREATE TABLE beam_example (
    id integer,
    name text,
    ftime timestamp option (stat_enabled=true)
)
USING beam 
DISTRIBUTED BY (id) 
ORDER BY(id);

添加指定列收集统计信息

针对已经存在的Beam表，可以通过ALTER TABLE ... ALTER COLUMN ... SET(stat_enabled=true)的方式添加某列收集统计信息，如下所示SQL语句为在name列设置收集统计信息。

ALTER TABLE beam_example ALTER COLUMN name SET(stat_enabled=true);

关闭指定列收集统计信息

如果不需要收集某列的统计信息，可以通过添加RESET (stat_enabled)的方式关闭某列收集统计信息，如下所示SQL语句为name列关闭收集统计信息。

ALTER TABLE beam_example ALTER COLUMN name RESET(stat_enabled);

查看列收集统计信息情况

如果您使用psql客户端连接数据库，执行\d+ tablename，其中Zonemap enabled for: xxx即为收集统计信息列。

postgres=# \d+ beam_example
                                          TABLE "public.beam_example"
 COLUMN |            Type             | COLLATION | Nullable | DEFAULT | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              | 
 name   | text                        |           |          |         | extended |              | 
 ftime  | timestamp WITNOUT time zone |           |          |         | plain    |              | 
DISTRIBUTED BY: (id)
ORDER BY: (id)
Zonemap enabled FOR: (ftime)
Access METHOD: beam
Options: compresstype=lz4, compresslevel=1, ctidversion=1

如果您想要通过SQL查询某张表某列收集统计信息的开关情况，可以查询pg_attribute的attoptions字段，attoptions为空表示收集统计信息没有开启，具体SQL语句如下所示：

SELECT attrelid, attname, attoption FROM pg_attribute 
WHERE attrelid = 'beam_example'::regclass AND attname = 'ftime';

相关参考

如何确定某列需要收集统计信息

以下是Beam存储引擎选择收集统计信息列的最佳实践：

• 如果您已经设置两列及以上的组合排序键时，查询业务上还存在对其他列筛选过滤条件，并且针对该列的筛选率在30%以下，建议指定该列收集统计信息。

• 如果您已经设置四列及以上的多维排序键时，查询业务上还存在对其他列筛选过滤条件，并且针对该列的筛选率在30%以下，建议指定该列收集统计信息。

• 如果查询业务的筛选条件中包含高度相关的多列（例如国家、省、市、年月日、时间戳等），建议将基数高的列加入排序键，将基数低的列指定收集统计信息。

附录：收集统计信息的查询性能

以SSB Benchmark lineorder_flat表为例，在SSB查询3.2和查询3.3中分别对c_nation，c_city两列进行查询筛选，而c_nation，c_city列是高度相关的，这时的最佳实践是将基数更高的c_city列加入排序键，并设置收集c_nation列的统计信息，即可以对查询3.2和查询3.3都有较好的过滤效果。具体SQL语句如下所示。

-- lineorder_flat表的建表语句
CREATE TABLE lineorder_flat (
    lo_orderkey bigint,
    lo_linenumber integer,
    lo_custkey integer,
    lo_partkey integer,
    lo_suppkey integer,
    lo_orderdate date,
    lo_orderpriority text,
    lo_shippriority integer,
    lo_quantity integer,
    lo_extendedprice integer,
    lo_ordtotalprice integer,
    lo_discount integer,
    lo_revenue integer,
    lo_supplycost integer,
    lo_tax integer,
    lo_commitdate date,
    lo_shipmode text,
    c_name text,
    c_address text,
    c_city text,
    c_nation text option (stat_enabled=true),
    c_region text,
    c_phone text,
    c_mktsegment text,
    s_name text,
    s_address text,
    s_city text,
    s_nation text,
    s_region text,
    s_phone text,
    p_name text,
    p_mfgr text,
    p_category text,
    p_brand text,
    p_color text,
    p_type text,
    p_size integer,
    p_container text)
USING beam DISTRIBUTED BY (lo_orderdate, lo_orderkey) ORDER BY (c_city);

查询3.2的SQL语句如下：

SELECT  c_city, s_city, extract(year FROM lo_orderdate) AS YEAR, SUM(lo_revenue) AS revenue
FROM lineorder_flat
WHERE c_nation = 'UNITED STATES' AND s_nation = 'UNITED STATES' AND lo_orderdate  >= '1992-01-01' AND lo_orderdate <= '1997-12-31'
GROUP BY c_city, s_city, year
ORDER BY year ASC, revenue DESC;

查询3.3的SQL语句如下：

SELECT c_city, s_city, extract(year FROM lo_orderdate) AS year, SUM(lo_revenue) AS revenue
FROM lineorder_flat
WHERE c_city in ( 'UNITED KI1' ,'UNITED KI5') AND s_city in ( 'UNITED KI1' ,'UNITED KI5') AND lo_orderdate  >= '1992-01-01' AND lo_orderdate <= '1997-12-31'
GROUP BY c_city, s_city, year
ORDER BY year ASC, revenue DESC;

以1GB SSB数据集为例，是否收集统计信息列的查询效果如下：