wlxkl opened a new issue, #13389: URL: https://github.com/apache/doris/issues/13389
### Search before asking - [X] I had searched in the [issues](https://github.com/apache/incubator-doris/issues?q=is%3Aissue) and found no similar issues. ### Version branch-1.1-lts (1.1.3-rc02-Unknown) ### What's Wrong? cpu 100% ,other query timeout ### What You Expected? fix it ### How to Reproduce? **1. create table** CREATE TABLE `info` ( `network` varchar(20) NULL COMMENT "", `account_id` varchar(128) NULL COMMENT "", `platform` varchar(20) NULL COMMENT "", `company` varchar(255) NULL COMMENT "", `operate_type` varchar(20) NULL COMMENT "", `media_short_name` varchar(256) NULL COMMENT "", `short_name` varchar(100) NULL COMMENT "", `product_name` varchar(50) NULL COMMENT "", `tag` varchar(100) NULL COMMENT "", `customer_region` varchar(50) NULL COMMENT "", `channel` varchar(50) NULL COMMENT "", `channel_company` varchar(100) NULL COMMENT "", `first_industry_name` varchar(50) NULL COMMENT "", `second_industry_name` varchar(50) NULL COMMENT "", `media_industry_type` varchar(50) NULL COMMENT "", `media_first_industry_name` varchar(100) NULL COMMENT "", `media_second_industry_name` varchar(200) NULL COMMENT "", `sales_name` varchar(20) NULL COMMENT "", `operate_region` varchar(10) NULL COMMENT "", `sale_region` varchar(50) NULL COMMENT "", `operate_team_id` int(11) NULL COMMENT "", `operate_team` varchar(255) NULL COMMENT "", `operate_department` varchar(255) NULL COMMENT "", `sale_department` varchar(255) NULL COMMENT "", `operate_person_num` int(11) NULL COMMENT "", `user_name` varchar(255) NULL COMMENT "", `contract_parties` varchar(50) NULL COMMENT "", `category` varchar(100) NULL COMMENT "", `white` int(11) NULL COMMENT "", `sign_box` int(11) NULL COMMENT "", `frame_cost` double NULL COMMENT "", `promise_cost` double NULL COMMENT "", `inovance_pt` double NULL COMMENT "", `wolong_pt` double NULL COMMENT "", `network_product` varchar(255) NULL COMMENT "", `service_type` varchar(20) NULL COMMENT "", `create_time` char(20) NULL COMMENT "", `type` varchar(20) NULL COMMENT "", `last_modified_time` datetime NULL COMMENT "", `gmt_create` datetime NULL COMMENT "", `gmt_update` datetime NULL COMMENT "", `ad_position` varchar(50) NULL COMMENT "" ) ENGINE=OLAP UNIQUE KEY(`network`, `account_id`, `platform`) COMMENT "OLAP" DISTRIBUTED BY HASH(`account_id`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "in_memory" = "false", "storage_format" = "V2" ); CREATE TABLE `detail_bak` ( `date` char(10) NULL COMMENT "", `month` char(7) NULL COMMENT "", `network` varchar(20) NULL COMMENT "", `account_id` varchar(128) NULL COMMENT "", `company` varchar(255) NULL COMMENT "", `data_level` int(11) NULL COMMENT "", `as_customer` boolean NULL COMMENT "", `operate_type` varchar(20) NULL COMMENT "", `media_short_name` varchar(256) NULL COMMENT "", `product_id` int(11) NULL COMMENT "", `product_name` varchar(50) NULL COMMENT "", `platform` varchar(200) NULL COMMENT "", `cost` decimal(20, 2) NULL COMMENT "", `operate_region` varchar(10) NULL COMMENT "", `backend` varchar(50) NULL COMMENT "", `channel` varchar(50) NULL COMMENT "", `channel_company` varchar(100) NULL COMMENT "", `sales_name` varchar(20) NULL COMMENT "", `media_industry_type` varchar(50) NULL COMMENT "", `media_first_industry_name` varchar(100) NULL COMMENT "", `media_second_industry_name` varchar(200) NULL COMMENT "", `operate_team` varchar(50) NULL COMMENT "", `tag` varchar(100) NULL COMMENT "", `product_type` varchar(20) NULL COMMENT "", `business_id` varchar(20) NULL COMMENT "", `product_line` varchar(20) NULL COMMENT "", `media_cost` char(20) NULL COMMENT "", `pt` char(10) NULL COMMENT "", `create_time` datetime NULL COMMENT "", `gmt_create` datetime NULL COMMENT "", `gmt_update` datetime NULL COMMENT "", `ad_position` varchar(50) NULL COMMENT "", `account_cost` decimal(20, 2) NULL COMMENT "" ) ENGINE=OLAP DUPLICATE KEY(`date`, `month`, `network`, `account_id`) COMMENT "OLAP" DISTRIBUTED BY HASH(`account_id`) BUCKETS 10 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "in_memory" = "false", "storage_format" = "V2" ); **2. load data** [数据](https://bj.bcebos.com/pzoom-files/outside/data.zip?authorization=bce-auth-v1%2F5b2e3dd3bb124ae3a761323f66066070%2F2022-10-15T06%3A05%3A36Z%2F1800%2Fhost%2F16443a107fd0b5ea5f474178f66db0955119593130a6aceddc3b5ac964f4557f) curl --location-trusted -u root:root -T info.txt http://xx.xx.xx.xx:8030/api/xx/info/_stream_load curl --location-trusted -u root:root -T detail_bak.txt http://xx.xx.xx.xx:8030/api/xx/detail_bak/_stream_load **3.data operate** create table if not EXISTS detail like detail_bak; insert into detail select * from detail_bak; delete from detail where `date` >='2022-01-01' and date <= '2022-10-10' and network = 'BDM' and operate_type != '服务类' and data_level = 1 and tag is null ; delete from detail where `date` >='2022-01-01' and date <= '2022-10-10' and network = 'BDM' and operate_type != '服务类' and data_level = 1 and tag = 'BAIDU_PINZHUAN'; delete from detail where `date` >='2022-01-01' and date <= '2022-10-10' and network = 'BDM' and operate_type != '服务类' and data_level = 1 and tag = 'BAIDU_SHOW'; delete from detail where `date` >='2021-12-01' and date <= '2022-10-10' and network = 'BDM' and operate_type != '服务类' and data_level = 2 and tag is null ; insert into detail select * from detail_bak; **4. sql query (cpu very heigh x00% and query time out)** select sum(t1.cost) from detail t1 inner join info t2 on t1.account_id=t2.account_id; ### Anything Else? _No response_ ### Are you willing to submit PR? - [ ] Yes I am willing to submit a PR! ### Code of Conduct - [X] I agree to follow this project's [Code of Conduct](https://www.apache.org/foundation/policies/conduct) -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: commits-unsubscr...@doris.apache.org For additional commands, e-mail: commits-h...@doris.apache.org
