我们在做doris1.2.7到doris2.1.9的升级验证，发现部分sql会比1慢，排查下来是由于外层列数量导致的，请教一下这种应该怎么优化？尝试了关闭2的优化器和调整了topN的优化，都没什么变化。
如下所示相同数据量和建表语句情况下，在doris1中执行完整sql是2.7s
如果我去掉列，只保留个1，doris1也是2.7s.
在doris2中，如果我执行完整sql是5.8s左右，慢一倍。
如果我去掉列，只保留个1，doris2就只要1.2s，快了一倍。
sql就是里面几个表 union all，外层显示所有列，最下面order by排序的写法。
doris2 列查询性能问题
Viewed 43
2 Answers
-
版本跨度太大，看看排查下并发：show variables like "parallel_pipeline_task_num";
-
可能是一些bad case，explain 下看看慢sql 是走的什么优化器
1.并发配置是0 ，默认。试着调大也没效果。
2.看分析图，貌似是由于我列上放了个1 ， 就走了并行？单个olab_scan在我列上用1或者全部列的时候耗时没太大差异。但是用全部列，耗时就变成了串行。
PlanInfo
TABLE: miaozhangceshidb01.t_crm_client_classify(t_crm_client_classify), PREAGGREGATION: ON
PREDICATES: ((ownerId = 59667) AND (DORIS_DELETE_SIGN = 0))
runtime filters: RF030[min_max] -> id, RF031[bloom] -> id
partitions=1/1 (p1)
tablets=1/10, tabletList=371786494
cardinality=813, avgRowSize=0.0, numNodes=1
pushAggOp=NONE
projections: id
project output tuple id: 61
BlocksProduced: sum 1, avg 1, max 1, min 1
CloseTime: avg 14.417us, max 14.417us, min 14.417us
ExecTime: avg 928.370ms, max 928.370ms, min 928.370ms
InitTime: avg 85.832us, max 85.832us, min 85.832us
MemoryUsage: sum , avg , max , min
PeakMemoryUsage: sum 0.00 , avg 0.00 , max 0.00 , min 0.00
OpenTime: avg 226.176us, max 226.176us, min 226.176us
ProjectionTime: avg 0ns, max 0ns, min 0ns
RowsProduced: sum 4, avg 4, max 4, min 4
RuntimeFilterInfo: sum , avg , max , min
filter id = 31 filtered: sum 5, avg 5, max 5, min 5
filter id = 31 input: sum 9, avg 9, max 9, min 9
WaitForDependency[OLAP_SCAN_OPERATOR_DEPENDENCY]Time: avg 324.962us, max 324.962us, min 324.962us
PlanInfo
TABLE: miaozhangceshidb01.t_crm_client_classify(t_crm_client_classify), PREAGGREGATION: ON
PREDICATES: ((ownerId = 59667) AND (DORIS_DELETE_SIGN = 0))
runtime filters: RF030[min_max] -> id, RF031[bloom] -> id
partitions=1/1 (p1)
tablets=1/10, tabletList=371786494
cardinality=813, avgRowSize=0.0, numNodes=1
pushAggOp=NONE
projections: id
project output tuple id: 61
BlocksProduced: sum 1, avg 1, max 1, min 1
CloseTime: avg 16.161us, max 16.161us, min 16.161us
ExecTime: avg 979.709ms, max 979.709ms, min 979.709ms
InitTime: avg 100.529us, max 100.529us, min 100.529us
MemoryUsage: sum , avg , max , min
PeakMemoryUsage: sum 0.00 , avg 0.00 , max 0.00 , min 0.00
OpenTime: avg 134.923us, max 134.923us, min 134.923us
ProjectionTime: avg 0ns, max 0ns, min 0ns
RowsProduced: sum 14, avg 14, max 14, min 14
RuntimeFilterInfo: sum , avg , max , min
WaitForDependency[OLAP_SCAN_OPERATOR_DEPENDENCY]Time: avg 540.828us, max 540.828us, min 540.828us
完整的快慢profile如下：
慢：https://iosupdate.bizgo.com/test/profile_befeec1bc97c424c-85b02ba8f20e594b_man.txt
快：https://iosupdate.bizgo.com/test/profile_53e8cb582e3d4b38-9dbaad20030b647f_kuai.txt