实践案例

宽表模式下，一张表拥有数十个列甚至上百个列，但查询负载中只需要统计/分析部分列，此时可以使用加速索引进行加速。

以一张包含24个列的widecolumntable表为例，包含BIGINT、DECIMAL、TEXT、JSONB、TEXT[]等类型，现要对其中的id_1，domain，consumption，start_time和end_time等五个列进行统计分析，统计每个客户在过去一年多个domain里消费的金额。

1. 创建一张名为widecolumntable表，表格结构定义如下，然后按照您的需求插入测试数据。

   CREATE TABLE widecolumntable (
     id_1 BIGINT NOT NULL PRIMARY KEY,
     id_2 BIGINT,
     id_3 BIGINT,
     id_4 BIGINT,
     id_5 BIGINT,
     id_6 BIGINT,
     version INT,
     domain TEXT,
     consumption DECIMAL(18,3),
     c_level CHARACTER varying(1) NOT NULL,
     priority BIGINT,
     operator TEXT,
     notify_policy TEXT,
     call_id UUID NOT NULL,
     provider_id BIGINT NOT NULL,
     name_1 TEXT NOT NULL,
     name_2 TEXT NOT NULL,
     name_3 TEXT,
     start_time TIMESTAMP WITH TIME ZONE NOT NULL,
     end_time TIMESTAMP WITH TIME ZONE NOT NULL,
     comment JSONB NOT NULL,
     description TEXT[] NOT NULL,
     created_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL,
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT now() NOT NULL
   );

2. 将id_1，domain，consumption，start_time和end_time列加入到加速索引中。

   CREATE INDEX idx_wide_csi ON widecolumntable USING CSI(id_1, domain, consumption,  start_time, end_time);

3. 统计过去一年客户在不同domain中消费金额，并按消费额进行排序。

   SET polar_csi.exec_parallel to 4;
   SET polar_csi.scan_parallel to 4;
   
   SELECT id_1, domain,SUM(consumption)  FROM widecolumntable WHERE start_time > '20230101' and end_time < '20240101' GROUP BY id_1, domain Order By SUM(consumption);

4. 测试结果：在一亿条数据规模和4并行度模式下，采用向量化引擎+加速索引方式的性能为PostgreSQL原生并行执行的30倍。

   查询语句	PostgreSQL原生并行	向量化引擎+加速索引
   Q1	243 s	7.9 s

在PostgreSQL中，分区表是应对数据不断增长的有效手段，会通过分区裁剪来加速查询。PolarDB PostgreSQL版向量化引擎也支持分区表，能够进一步应对分区表的统计和分析需求。

本案例中，会创建一个多级分区表，并模拟插入3.2亿条数据（约16 GB），之后根据分区条件进行统计分析。

1. 创建名为sales的多级分区表，以时间列sale_date作为分区键，定义如下。

   CREATE TABLE sales (
       sale_id serial,
       product_id int NOT NULL,
       sale_date date NOT NULL,
       amount numeric(10,2) NOT NULL,
       primary key(sale_id, sale_date)
   ) PARTITION BY RANGE (sale_date);
   
   CREATE TABLE sales_2023 PARTITION OF sales
       FOR VALUES FROM ('2023-1-1') TO ('2024-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2023_a PARTITION OF sales_2023
       FOR VALUES FROM ('2023-1-1') TO ('2023-7-1');
   CREATE TABLE sales_2023_b PARTITION OF sales_2023
       FOR VALUES FROM ('2023-7-1') TO ('2024-1-1');
   
   CREATE TABLE sales_2024 PARTITION OF sales
       FOR VALUES FROM ('2024-1-1') TO ('2025-1-1')
       PARTITION BY RANGE (sale_date);
   CREATE TABLE sales_2024_a PARTITION OF sales_2024
       FOR VALUES FROM ('2024-1-1') TO ('2024-7-1');
   CREATE TABLE sales_2024_b PARTITION OF sales_2024
       FOR VALUES FROM ('2024-7-1') TO ('2025-1-1');

2. 生成数据并写入到分区表，约16 GB。

   INSERT INTO sales (product_id, sale_date, amount)
   SELECT
     (random()*100)::int AS product_id,
     '2023-01-1'::date + i/3200000*7 AS sale_date,
     (random()*1000)::numeric(10,2) AS amount
   FROM
     generate_series(1, 320000000) i;

3. 为表创建加速索引，将sale_id，product_id，sale_date和amount字段加入到加速索引。

   CREATE INDEX ON sales USING CSI(sale_id, product_id, sale_date, amount);

4. 查询。根据不同的分区条件生成三条查询语句，为Q1，Q2和Q3。

   SET polar_csi.enable_query to on;
   SET polar_csi.exec_parallel to 4;
   SET polar_csi.scan_parallel to 4;
   SET max_parallel_workers_per_gather to 4;
   
   -- Q1
   EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-3-1' AND amount > 100 GROUP BY sale_date;
   -- Q2
   EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-9-1' AND amount > 100 GROUP BY sale_date;
   -- Q3
   EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2024-3-1' AND amount > 100 GROUP BY sale_date;

5. 测试结果：4并行度模式下，向量化引擎在三条查询语句中都比原生PostgreSQL并行执行快25倍以上。

   查询语句	PostgreSQL原生并行	向量化引擎+加速索引
   Q1	9.29 s	0.26 s
   Q2	29.19 s	1.03 s
   Q2	48.30 s	2.02 s

PolarDB PostgreSQL版冷热分层存储可以将历史数据转储到OSS中，这类数据每天访问频次较低，主要用于数据归档与分析使用。PolarDB PostgreSQL版向量化引擎支持与冷热分层存储的结合，用户可以方便地分析存储在OSS中的冷数据。

本案例在案例二的基础上，展示如何将分区表以及对应的加速索引转储到冷存中。

使用前提：集群已经开启冷数据分层存储功能，开启冷数据分层存储功能请参考开启和使用冷数据分层存储。

1. 创建polar_osfs_toolkit插件。

   CREATE EXTENSION IF NOT EXISTS polar_osfs_toolkit;

2. 将sales表的所有子分区以及对应的加速索引都转储到OSS中。

   SELECT polar_alter_subpartition_to_oss_with_indexes('sales',0);

3. 查看子分区表sales_2023_a的信息，表空间为OSS。

   

4. 查询，无需更改查询语句。

   SET polar_csi.enable_query to on;
   SET polar_csi.exec_parallel to 4;
   SET polar_csi.scan_parallel to 4;
   SET max_parallel_workers_per_gather to 4;
   
   -- Q1
   EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-3-1' AND amount > 100 GROUP BY sale_date;
   -- Q2
   EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2023-9-1' AND amount > 100 GROUP BY sale_date;
   -- Q3
   EXPLAIN ANALYZE SELECT sale_date, COUNT(*) FROM sales WHERE sale_date BETWEEN '2023-1-1' and '2024-3-1' AND amount > 100 GROUP BY sale_date;

5. 除了手动将分区表数据转储到OSS之外，还可以结合pg_cron插件自动化将历史分区表数据转储到OSS。详细案例请参考分区表按时间线自动冷存。

在很多统计类的查询负载中，会将分析后的数据保存到其他结果表中。

PolarDB PostgreSQL版向量化引擎支持INSERT INTO SELECT和CREATE TABLE AS SELECT语句，可以在一条SQL语句中，利用向量化引擎高效分析并将结果写入到结果表中。

• 利用CREATE TABLE AS SELECT将案例一的统计结果转到新的表中。

  SET polar_csi.exec_parallel to 4;
  SET polar_csi.scan_parallel to 4;
  
  CREATE TABLE result_table AS SELECT id_1, domain,SUM(consumption)  FROM widecolumntable WHERE start_time > '20230101' and end_time < '20240101' GROUP BY id_1, domain Order By SUM(consumption);

• 利用INSERT INTO SELECT将案例一的统计结果插入到已有的表中。

  CREATE TABLE result_table(id_1 BIGINT, domain TEXT, sum DECIMAL(18,3));
  
  INSERT INTO result_table SELECT id_1, domain,SUM(consumption) as sum  FROM widecolumntable WHERE start_time > '20230101' and end_time < '20240101' GROUP BY id_1, domain Order By SUM(consumption);

PolarDB PostgreSQL版的ePQ是基于云原生一写多读架构下，为了充分利用所有RO节点的计算资源而研发的跨机并行能力。其能够将一个查询语句分发到所有的RO节点执行，而开源PostgreSQL的并行能力只能使用一个节点的计算资源，因此PolarDB PostgreSQL版的ePQ比开源PostgreSQL具有更好的scale out能力。

PolarDB PostgreSQL版的向量化引擎与ePQ并非同一种技术，向量化引擎的目标是在同样的硬件资源下算得更快，而ePQ的目标是能充分利用数据库集群的所有资源，二者是相互补充的关系。

向量化引擎不侵入开源PostgreSQL，只是作为PostgreSQL的一个索引，如果不需要性能加速，删除索引即可，不影响PostgreSQL中已有的基础数据。后续会纳入到PolarDB PostgreSQL版开源版本中。

请按如下步骤检查：

• 查看表是否创建加速索引。

• 查看所涉及的列是否都在加速索引中。

• 是否开启向量化查询，执行SET polar_csi.enable_query = ON开启向量化引擎功能。

• polar_csi.cost_threshold过大，查询语句会被PostgreSQL原生执行引擎处理，可以将该值调小。

• 如果还无法使用向量化引擎加速，可执行SET client_min_messages = debug5来进一步排查。