加索引前.txt (27.3 KB) 加索引后.txt (27.3 KB)
【版本】2.0.5
【操作】对下面语句进行压测
select month,sum(NBEV) nbev1
from wn.agg_mis_r_sales_a_achment_lep_prem_index_m
WHERE MARGIN_VERSION_DESC = ‘当年假设’ and BUSINESS_SRC_DESC_1= ‘个险’
group by month;
agg_mis_r_sales_a_achment_lep_prem_index_m 表 有4000万数据
MARGIN_VERSION_DESC字段只有两个值
BUSINESS_SRC_DESC_1有6个不同值
分别对这两个字段加了bitmap 索引。
加完以后再压测，反而性能有点下降
您好，bitmap索引会构建全局字典，所以查可能会慢点，多查几次性能还是没有提升吗？
你好，我是做了压测，开50,100,200,300并发，响应速度都没有提升，甚至有些用例还有些下降
建表语句.txt (8.1 KB)
分区数据量：
p202003 1500万条
p202102 500万
p202103 500万
p202203 500万
压测语句：
select a.month,sum(nbev1) as dcz,sum(nbev2) as tq,(sum(nbev1)-sum(nbev2))/sum(nbev2) as tb
from
( select month,(month-100) as month1,nbev1
from(
select month,sum(NBEV) nbev1
from wn.agg_mis_r_sales_a_achment_lep_prem_index_m
WHERE MARGIN_VERSION_DESC = ‘当年假设’ and BUSINESS_SRC_DESC_1= ‘个险’
group by month
) s
) a
join
(
select month,sum(NBEV) nbev2
from wn.agg_mis_r_sales_a_achment_lep_prem_index_m
WHERE MARGIN_VERSION_DESC = ‘来年假设’ and BUSINESS_SRC_DESC_1= ‘个险’
group by month
order by month desc
limit 27
) b on a.month1 =b.month
GROUP BY a.month
ORDER BY month desc;
修改下物化视图，把输出列中的month放到BUSINESS_SRC_DESC_1后面
create materialized view jiebao2 as
select MARGIN_VERSION_DESC,BUSINESS_SRC_DESC_1,month,sum(NBEV)
from agg_mis_r_sales_a_achment_lep_prem_index_m
group by month,MARGIN_VERSION_DESC,BUSINESS_SRC_DESC_1;
用例：
select margin_version_desc,count(nbev) from agg group by margin_version_desc order by margin_version_desc limit 10;
表数据量：2000万
关闭pipeline
parallel_fragment_exec_instance_num=1
enable_pipeline_engine=false
cbo_cte_reuse=true
关闭pipeline.txt (24.1 KB)
打开pipeline
parallel_fragment_exec_instance_num=1
enable_pipeline_engine=true
pipeline_dop=1
cbo_cte_reuse=true
打开pipeline.txt (18.9 KB)
关闭pipeline
parallel_fragment_exec_instance_num=24
关闭pipeline（parallel=24）.txt (173.4 KB)
开启pipeline
enable_pipeline_engine=true
pipeline_dop=0
打开pipeline（dop=0）.txt (15.4 KB)