在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 |
实施步骤
步骤一：环境准备
请确认您的集群版本与配置是否满足以下条件：
集群版本：
PostgreSQL 16（内核小版本为2.0.16.8.3.0及以上）
PostgreSQL 14（内核小版本为2.0.14.10.20.0及以上）
原表必须包含主键，且在创建列存索引时需要将主键列加入列存索引中。
wal_level参数的值需设置为logical，即在预写式日志WAL（Write-Ahead Logging）中增加支持逻辑编码所需的信息。说明您可以通过控制台设置wal_level参数。修改该参数后集群将会重启，请在修改参数前做好业务安排，谨慎操作。
开启列存索引功能。
对于不同的PolarDB PostgreSQL版内核版本，开启列存索引的方式不同：
当前版本下的PolarDB PostgreSQL版集群，支持两种开启方式，具体差异如下，请按需选择：
对比项
【推荐】添加列存索引只读节点
直接使用预安装的列存索引插件
操作方式
通过控制台实现可视化操作，手动添加列存索引节点。
无需任何操作，即可直接使用。
资源分配
列存引擎独占所有资源，能够充分利用所有内存。
列存引擎只能使用25%的内存，其余内存则分配给行存引擎使用。
业务影响
TP（事务）与AP（分析）业务在不同节点上相互隔离，互不影响。
TP（事务）与AP（分析）业务在同一节点，会互相影响。
费用
需额外收取列存索引只读节点的费用，按照普通计算节点收费。
无费用。
添加列存索引只读节点
您可选择以下两种方式中任意一种方式添加列存索引只读节点：
说明集群中应包含一个只读节点，即单节点集群不支持添加列存索引只读节点。
控制台添加
登录PolarDB控制台，选择集群所在地域。您可以按照如下两种方式中的任意一种进入增删节点向导页面：
在集群列表页面，单击操作栏的增删节点。
在目标集群的基本信息页面，数据库节点区域，单击增删节点。
选择增加列存索引只读节点选项，并单击确定。
在集群变配页面，添加列存索引只读节点并支付。
单击增加一个列存索引只读节点，选择节点规格。
选择切换时间。
（可选）查看产品服务协议、服务等级协议。
单击立即购买。
支付完成后，返回集群详情页等待列存索引只读节点添加成功，即节点状态为运行中。
购买时添加
在PolarDB购买页的节点个数配置项中自行选择列存索引只读节点数量。
当前版本下的PolarDB PostgreSQL版集群，列存索引作为插件
polar_csi部署在数据库集群中，在使用之前需要在指定的数据库中创建插件。说明polar_csi插件的作用域是Database级别，如果需要在集群的多个Database中使用列存索引，需要为每个Database分别创建polar_csi插件。安装插件使用的数据库账号必须为高权限账号。
您可以选择以下两种方式中的任意一种安装
polar_csi插件。控制台安装
登录PolarDB控制台，在左侧导航栏单击集群列表，选择集群所在地域，并单击目标集群ID进入集群详情页。
在左侧导航栏选择，在管理插件页签，选中未安装插件。
在页面右上角选择目标数据库，单击
polar_csi插件操作列安装，在弹出的安装插件对话框，选择目标数据库账号，单击确定，即将插件安装到目标数据库中。
命令行安装
连接数据库集群，并在具有相应权限的目标数据库中执行以下语句，创建
polar_csi插件。CREATE EXTENSION polar_csi;
步骤二：数据准备
本案例中，会创建一个多级分区表，并模拟插入3.2亿条数据（约16 GB），之后根据分区条件进行统计分析。
测试用分区表结构如下：
sales：主表。sales_2023：按照年份分区。sales_2023_a：按照月份分区，1~6月定义为分区a。sales_2023_b：按照月份分区，7~12月定义为分区b。
sales_2024：按照年份分区。sales_2024_a：按照月份分区，1~6月定义为分区a。sales_2024_b：按照月份分区，7~12月定义为分区b。
创建名为
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;