案例:使用partial cluster key
列存表可以选取某一列或几列设置为partial cluster key(column_name[, ...])。在导入数据时,按设置的列进行局部排序(默认每70个CU即420万行排序一次),生成的CU会聚集在一起,即CU的min,max会在一个较小的区间内。当查询时,where条件含有这些列时,可产生良好的过滤效果。
优化前
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
CREATE TABLE lineitem ( L_ORDERKEY BIGINT NOT NULL , L_PARTKEY BIGINT NOT NULL , L_SUPPKEY BIGINT NOT NULL , L_LINENUMBER BIGINT NOT NULL , L_QUANTITY DECIMAL(15,2) NOT NULL , L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL , L_DISCOUNT DECIMAL(15,2) NOT NULL , L_TAX DECIMAL(15,2) NOT NULL , L_RETURNFLAG CHAR(1) NOT NULL , L_LINESTATUS CHAR(1) NOT NULL , L_SHIPDATE DATE NOT NULL , L_COMMITDATE DATE NOT NULL , L_RECEIPTDATE DATE NOT NULL , L_SHIPINSTRUCT CHAR(25) NOT NULL , L_SHIPMODE CHAR(10) NOT NULL , L_COMMENT VARCHAR(44) NOT NULL ) with (orientation = column) distribute by hash(L_ORDERKEY); select sum(l_extendedprice * l_discount) as revenue from lineitem where l_shipdate >= '1994-01-01'::date and l_shipdate < '1994-01-01'::date + interval '1 year' and l_discount between 0.06 - 0.01 and 0.06 + 0.01 and l_quantity < 24; |
导入数据后执行查询,查看执行时间:
优化后
where条件中l_shipdate和l_quantity的distinct值数量较少且可以做min max过滤,将字段l_shipdate、l_quantity设置为PCK修改表定义如下:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
CREATE TABLE lineitem ( L_ORDERKEY BIGINT NOT NULL , L_PARTKEY BIGINT NOT NULL , L_SUPPKEY BIGINT NOT NULL , L_LINENUMBER BIGINT NOT NULL , L_QUANTITY DECIMAL(15,2) NOT NULL , L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL , L_DISCOUNT DECIMAL(15,2) NOT NULL , L_TAX DECIMAL(15,2) NOT NULL , L_RETURNFLAG CHAR(1) NOT NULL , L_LINESTATUS CHAR(1) NOT NULL , L_SHIPDATE DATE NOT NULL , L_COMMITDATE DATE NOT NULL , L_RECEIPTDATE DATE NOT NULL , L_SHIPINSTRUCT CHAR(25) NOT NULL , L_SHIPMODE CHAR(10) NOT NULL , L_COMMENT VARCHAR(44) NOT NULL , partial cluster key(l_shipdate, l_quantity) ) with (orientation = column) distribute by hash(L_ORDERKEY); |
重新导入数据后执行查询,查看执行时间:
使用partial cluster key后,5-- CStore Scan on public.lineitem的时间减少了1.2s,得益于有84个CU被过滤掉了。
优化建议
- 选取partial cluster key列。
- 列存表支持创建partial cluster key的类型character varying(n), varchar(n), character(n), char(n), text, nvarchar2, timestamp with time zone, timestamp without time zone, date, time without time zone, time with time zone。
- 数据的distinct值数量较少,这样能产生较好的过滤效果。
- 出现在查询where条件中,优先选取能过滤大量数据的列。
- partial cluster key中设置多个列时,是先按第一个列排序,当第一个列值相同时,使用第二列比较,后续列依次类推。推荐不要超出3个列。
- 添加partial cluster key后,优化导入性能。
由于添加了partial cluster key,在导入时会增加排序计算,会对导入性能产生影响。当排序完全在内存中进行时影响较小,如果无法在内存中完成排序时,会下盘写临时文件,这时就会产生较大的影响。
排序使用的内存通过GUC参数psort_work_mem来设置,可以设置较大的值来使用更大的内存进行排序。
排序的数据量是通过表的存储参数PARTIAL_CLUSTER_ROWS来设置,降低这个数值,可减少一次排序的数据量。这个参数通常与存储参数MAX_BATCHROW配置使用。PARTIAL_CLUSTER_ROWS设置值必须是MAX_BATCHROW的整数倍,MAX_BATCHROW是设置单个CU中数据的最大行数。