对齐JOIN字段类型，使用数组代替字符串，降低字符串处理开销，列存降低扫描开销

2025-04-12 技术教程

背景

Greenplum通常被用作OLAP，在一些用户使用过程中，可能因为数据结构设计，SQL问题等原因导致性能不佳，虽然通过增加节点可以解决问题，但是如果能优化的话，可以节约不少硬件资源。

例如

1、对齐JOIN字段类型。如果等值JOIN的字段类型不一致，无法使用HASH JOIN。

2、对齐where条件字段类型。同上，无法使用HASH JOIN，或者索引扫描。

3、使用数组代替字符串，降低字符串处理开销。如果字符串本身需要大量的格式化处理FILTER，那么使用数组的性能会好很多。

4、列存降低扫描开销，统计型的SQL由于涉及的字段有限，使用列存比行存储性能好很多。

例子

1、这个查询耗费230秒。

SELECTcol4,count(DISTINCTc.col1)ptnumfromtbl1aINNERJOINtbl2bonb.col2=a.idinnerjointbl3t2ont2.ID<=(length(b.col3)-length(replace(b.col3,',',''))+1)INNERJOINtbl4conreplace(replace(Split_part(reverse(Split_part(reverse(Split_part(b.col3,',',cast(t2.idasint))),',',1)),':',1),'{',''),'"','')=c.idINNERJOINtbl5sona.col4=s.idwherereplace(replace(reverse(Split_part(Split_part(reverse(Split_part(b.col3,',',cast(t2.idasint))),',',1),':',1)),'"',''),'}','')>'0'andc.col1notin('xxxxxx')GROUPBYcol4;

2、使用explain analyze分析瓶颈

3、问题：

3.1、JOIN类型不一致，导致未使用HASH JOIN。

3.2、有两个表JOIN时产生笛卡尔积来进行不等于的判断，数据量叠加后需要计算几十万亿次。

tbl2.col3字符串格式如下(需要计算几十万亿次)

{"2":"1","10":"1","13":"1","16":"1","21":"1","26":"1","28":"1","30":"1","32":"1","33":"1","34":"1","35":"1","36":"1","37":"1","39":"1","40":"1","99":"2","100":"2","113":"1","61":"1","63":"4","65":"2"}

3.3、使用了行存储，查询时扫描的量较大，并且无法使用向量计算。

优化

1、使用列存代替行存(除nestloop的内表tbl3，继续使用索引FILTER)

createtabletmp_tbl1(liketbl1)WITH(APPENDONLY=true,ORIENTATION=column);insertintotmp_tbl1select*fromtbl1;createtabletmp_tbl4(liketbl4)WITH(APPENDONLY=true,ORIENTATION=column);insertintotmp_tbl4select*fromtbl4;createtabletmp_tbl5(liketbl5)WITH(APPENDONLY=true,ORIENTATION=column);insertintotmp_tbl5select*fromtbl5;createtabletmp_tbl2(liketbl2)WITH(APPENDONLY=true,ORIENTATION=column)distributedby(col2);insertintotmp_tbl2select*fromtbl2;

2、使用array代替text

altertabletmp_tbl2altercolumncol3typetext[]using(casecol3when'[]'then'{}'elsereplace(col3,'"','')end)::text[];

修改后的类型、内容如下

digoal=>selectcol3fromtmp_tbl2limit2;col3------------------------------------------------------------------------------------------------------------------------{63:1,65:1,70:1,71:1,73:1,75:1,77:1,45:3,78:1,54:2,44:1,80:1,36:1,84:1,96:2}{2:2,10:1,13:1,16:1,30:1,107:1,26:1,28:1,32:1,33:1,34:1,35:1,36:1,37:1,39:1,99:2,100:2,113:1,40:1,57:1,63:2,64:1,65:4}(2rows)

3、join 字段保持一致

altertabletmp_tbl2altercolumncol2typeint8;

4、将原来的查询SQL修改成如下（字符串处理变成了数组）

（本例也可以使用二维数组，完全规避字符串处理。）

SELECTcol4,count(DISTINCTc.col1)ptnumfromtmp_tbl1aINNERJOINtmp_tbl2bonb.col2=a.idinnerjointbl3t2ont2.ID<=array_length(col3,1)--更改INNERJOINtmp_tbl4consplit_part(b.col3[cast(t2.idasint)],':',1)=c.idINNERJOINtmp_tbl5sona.col4=s.idwheresplit_part(b.col3[cast(t2.idasint)],':',2)>'0'andc.col1notin('xxxxxx')GROUPBYcol4;

执行计划

QUERYPLAN-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------GatherMotion32:1(slice7;segments:32)(cost=543258065.87..543259314.50rows=41621width=12)->GroupAggregate(cost=543258065.87..543259314.50rows=1301width=12)GroupBy:a.col4->Sort(cost=543258065.87..543258169.93rows=1301width=12)SortKey:a.col4->RedistributeMotion32:32(slice6;segments:32)(cost=542355803.38..543254872.50rows=1301width=12)HashKey:a.col4->GroupAggregate(cost=542355803.38..543254040.08rows=1301width=12)GroupBy:a.col4->Sort(cost=542355803.38..542655042.19rows=3740486width=11)SortKey:a.col4->RedistributeMotion32:32(slice5;segments:32)(cost=6247.23..518770960.13rows=3740486width=11)HashKey:c.col1->HashJoin(cost=6247.23..516377049.63rows=3740486width=11)HashCond:split_part(b.col3[t2.id::integer],':'::text,1)=c.id::text->NestedLoop(cost=5494.14..476568597.41rows=3852199width=491)JoinFilter:split_part(b.col3[t2.id::integer],':'::text,2)>'0'::text->BroadcastMotion32:32(slice3;segments:32)(cost=5494.14..115247.73rows=277289width=483)->HashJoin(cost=5494.14..23742.36rows=8666width=483)HashCond:b.col2=a.id->SeqScanontmp_tbl2b(cost=0.00..14088.89rows=8666width=487)->Hash(cost=4973.86..4973.86rows=1301width=12)->RedistributeMotion32:32(slice2;segments:32)(cost=2280.93..4973.86rows=1301width=12)HashKey:a.id->HashJoin(cost=2280.93..4141.42rows=1301width=12)HashCond:s.id=a.col4->Append-onlyColumnarScanontmp_tbl5s(cost=0.00..1220.97rows=1491width=4)->Hash(cost=1760.66..1760.66rows=1301width=12)->RedistributeMotion32:32(slice1;segments:32)(cost=0.00..1760.66rows=1301width=12)HashKey:a.col4->Append-onlyColumnarScanontmp_tbl1a(cost=0.00..928.22rows=1301width=12)->IndexScanusingidx_codeidontbl3t2(cost=0.00..23.69rows=42width=8)IndexCond:t2.id<=array_length(b.col3,1)->Hash(cost=364.69..364.69rows=972width=11)->BroadcastMotion32:32(slice4;segments:32)(cost=0.00..364.69rows=972width=11)->Append-onlyColumnarScanontmp_tbl4c(cost=0.00..44.26rows=31width=11)Filter:col1<>'xxxxxx'::textSettings:effective_cache_size=8GB;enable_nestloop=off;gp_statistics_use_fkeys=onOptimizerstatus:legacyqueryoptimizer(39rows)性能提升

原来SQL响应时间： 230秒

修改后SQL响应时间： < 16秒

小结瓶颈分析

1、JOIN时不等条件，必须使用笛卡尔的方式逐一判断，所以如果FILTER条件很耗时（CPU），那么性能肯定好不到哪去。

2、原来大量的reverse, split, replace字符串计算，很耗时。刚好落在笛卡尔上，计算数十万亿次。

3、JOIN字段类型不一致。未使用HASH JOIN。

4、分析SQL，未使用列存储。

优化手段

1、array 代替字符串。

2、改写SQL

3、对齐JOIN类型。

4、使用列存储。