文档

Beam指定列收集统计信息

更新时间:

多列筛选查询中,在设定排序键的基础上,如果频繁地针对Beam表的其他几列执行高筛选率的查询,您可以使用Beam收集指定列统计信息的能力作为对排序键的补充,获得更优的查询性能。本文介绍如何使用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);
重要
  • 添加指定列收集统计信息后,仅会对新写入的数据生效,已经存在的数据需要等待后台处理后才能生效。

  • 当您为已有的分区表添加分区时,使用ALTER TABLE ... ATTACH PARTITION...并不能继承父表的指定列收集统计信息的定义,需要单独为新增的子分区添加指定列收集统计信息。

关闭指定列收集统计信息

如果不需要收集某列的统计信息,可以通过添加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数据集为例,是否收集统计信息列的查询效果如下:

SSB查询

不开启c_nation列收集统计信息(ms)

开启c_nation列收集统计信息(ms)

Q3.2

993

36.7

Q3.3

12.3

12.5

说明
  • 该结果仅比较开启指定列收集的相对性能差异,不代表云原生数据仓库 AnalyticDB PostgreSQL 版在该数据集下的最佳性能。

  • 该结果为1GB SSB数据的测试结果,通常情况下数据量越大,指定列收集过滤效果越明显。