分区表使用列存索引_云原生数据库 PolarDB(PolarDB)-阿里云帮助中心

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

背景

随着业务系统的不断运转，将产生大量的历史数据，导致表的规模越来越大，一般会将数据按照时间、user_id 等维度进行分区，每个分区只保留部分的数据。原生PostgreSQL在查询时也会通过分区裁剪来避免无关数据的读取。

PolarDB PostgreSQL版的向量化引擎/列存索引也支持对分区表的加速分析，使用方式与现有分区表索引一致。

实践案例

案例效果

在4并行度模式下，向量化引擎在三条查询语句中都比原生PostgreSQL并行执行快35倍以上。

查询语句	PostgreSQL原生并行	向量化引擎+列存索引
Q1	2.13 s	0.05 s
Q2	6.42 s	0.18 s
Q3	10.51 s	0.30 s

步骤

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

测试用分区表结构如下：

sales：主表。
- sales_2023：按照年份分区。
  - sales_2023_a：按照月份分区，1~7月定义为分区a。
  - sales_2023_b：按照月份分区，7~12月定义为分区b。
- sales_2024：按照年份分区。
  - sales_2024_a：按照月份分区，1~7月定义为分区a。
  - sales_2024_b：按照月份分区，7~12月定义为分区b。

说明

请在目标数据库安装向量化引擎插件polar_csi，详细操作请参考开启和使用向量化引擎。

创建名为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');

生成数据并写入到分区表，约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;

为表创建列存索引，将sale_id，product_id，sale_date和amount字段加入到列存索引。
```
CREATE INDEX ON sales USING CSI(sale_id, product_id, sale_date, amount);
```

使用不同执行引擎进行查询。根据不同的分区条件生成三条查询语句Q1，Q2和Q3。

使用向量化引擎

--- 开启向量化引擎，设置查询并行度为4
SET polar_csi.enable_query to on;
SET polar_csi.exec_parallel 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;

关闭向量化引擎，使用行存引擎

--- 关闭向量化引擎，使用行存引擎，并设置查询并行度为4
SET polar_csi.enable_query to off;
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;