宽表模式中对指定列的统计分析

背景

在很多SaaS业务系统中，一张表有几十甚至上百个列，在查询时往往会面临较大的技术挑战：

• 查询时只需要分析其中的几个列，但在使用行存结构时，会读取大量无关的列，增加IO负担。

• 查询模式不固定，上百个列在查询时会存在多种过滤条件，构建组合索引时需要预先匹配所有的查询场景，一旦改变查询条件，组合索引将失效。

向量化引擎和列存索引可以很好地应对上述两个场景。由于列存索引的内部结构以列为单位，所以读取一个列时并不会影响另一个列，同时列之间也没有顺序关系，所以多个查询条件的顺序性也不会影响列存索引的效果。

实践案例

案例效果

在一亿条数据规模和4并行度模式下，采用向量化引擎和列存索引方式的查询性能为PostgreSQL原生并行执行的30倍。

步骤

以一张包含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中消费金额，并按消费额进行排序。

   • 使用向量化引擎

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