search

apache / doris

Apache Doris is an easy-to-use, high performance and unified analytics database.
https://doris.apache.org
Apache License 2.0
12.8k stars 3.29k forks source link

[Bug] 查询物化视图耗时17S,查询Doris表耗时30S+ #24126

Open yusong-md opened 1 year ago

yusong-md commented 1 year ago

Search before asking

Version

Doris1.2.4.1

What's Wrong?

Doris集群配置 3台物理机：64核CPU、256G内存、SSD盘、3个FE、4个BE

创建表 CREATE TABLE ads_arrange_home_dev_content (
navid int(11) NULL COMMENT '导航id', channelid int(11) NULL COMMENT '频道id', datecode date NULL COMMENT '日期', timedim varchar(20) NULL COMMENT '日期粒度：1-日，2-周，3-月', timecode varchar(128) NULL COMMENT '日期，对应timedim的日期', deviceid varchar(128) REPLACE NULL COMMENT '设备id', device_bitmap bitmap BITMAP_UNION NULL COMMENT '设备bitmap', times bigint(20) SUM NULL COMMENT '次数' ) ENGINE=OLAP AGGREGATE KEY(navid, channelid, datecode, timedim, timecode) COMMENT 'OLAP' PARTITION BY RANGE(datecode) (PARTITION p20230830 VALUES [('2023-08-30'), ('2023-08-31')), PARTITION p20230831 VALUES [('2023-08-31'), ('2023-09-01')), PARTITION p20230901 VALUES [('2023-09-01'), ('2023-09-02')), PARTITION p20230902 VALUES [('2023-09-02'), ('2023-09-03')), PARTITION p20230903 VALUES [('2023-09-03'), ('2023-09-04')), PARTITION p20230904 VALUES [('2023-09-04'), ('2023-09-05')), PARTITION p20230905 VALUES [('2023-09-05'), ('2023-09-06')), PARTITION p20230906 VALUES [('2023-09-06'), ('2023-09-07')), PARTITION p20230907 VALUES [('2023-09-07'), ('2023-09-08')), PARTITION p20230908 VALUES [('2023-09-08'), ('2023-09-09')), PARTITION p20230909 VALUES [('2023-09-09'), ('2023-09-10')), PARTITION p20230910 VALUES [('2023-09-10'), ('2023-09-11')), PARTITION p20230911 VALUES [('2023-09-11'), ('2023-09-12')), PARTITION p20230912 VALUES [('2023-09-12'), ('2023-09-13'))) DISTRIBUTED BY HASH(navid) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 1", "dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "DAY", "dynamic_partition.time_zone" = "Etc/GMT", "dynamic_partition.start" = "-10", "dynamic_partition.end" = "3", "dynamic_partition.prefix" = "p", "dynamic_partition.replication_allocation" = "tag.location.default: 1", "dynamic_partition.buckets" = "32", "dynamic_partition.create_history_partition" = "true", "dynamic_partition.history_partition_num" = "90", "dynamic_partition.hot_partition_num" = "90", "dynamic_partition.reserved_history_periods" = "NULL", "dynamic_partition.storage_policy" = "", "dynamic_partition.storage_medium" = "HDD", "in_memory" = "false", "storage_format" = "V2", "disable_auto_compaction" = "false" );

表大小：16874455行

创建物化视图 create materialized view advertiser_uv as select navid,datecode,license,timecode, navititle , sum(times) , bitmap_union(device_bitmap) as device_bitmap from deviceprofile.ads_arrange_home_dev_content group by datecode,timecode, navid, navititle, license

查询语句 select navid,navititle, datecode, license, sum(times) , bitmap_union_count(device_bitmap) from deviceprofile.ads_arrange_home_dev_content where navid = 350 and datecode>='2023-09-01' and datecode<='2023-09-06' group by datecode, navititle,license, navid;

QueryProfile Query: Summary:

select navid,navititle, datecode, license, sum(times) , bitmap_union_count(device_bitmap)
from deviceprofile.ads_arrange_home_dev_content where navid = 350 and datecode>='2023-09-01' and datecode<='2023-09-06'
group by datecode, navititle,license, navid

What You Expected?

确认查询耗时是否正常

How to Reproduce?

No response

Anything Else?

No response

Are you willing to submit PR?

Code of Conduct

LemonLiTree commented 1 year ago

Look at the statement：insert into ads_arrange_home_dev_content