GoGoWen opened a new pull request, #39310:
URL: https://github.com/apache/doris/pull/39310
## Proposed changes
when we upgrade from 1.2 to 2.0, following case that "date cast to
datetime" will not push down any more, which cause performance degradation.
step to repo
step 1 (in 1.2):
CREATE TABLE `search_analysis_after_adv` ( `pin_id` BIGINT NULL COMMENT
'广告主pin_id', `date` date NULL COMMENT '点击日期', `search_date` date NULL
COMMENT '搜索时间', `pt` INT NULL COMMENT '1:点击 0:曝光', `dim_type` INT NULL
COMMENT '1:品牌 0:品牌+三级类目', `business_type` INT NULL COMMENT '业务线类型',
`campaign_type` INT NULL COMMENT '计划类型', `delivery_system_type` INT NULL
COMMENT '平台来源', `ad_plan_id` BIGINT NULL COMMENT '广告计划id', `ad_group_id`
BIGINT NULL COMMENT '广告单元id', `deal_id` BIGINT NULL COMMENT '排期id',
`branding_order_id` BIGINT NULL COMMENT '品牌广告订单id', `sku_brand_id` BIGINT
NULL COMMENT '点击sku对应的品 牌ID', `ad_sku_cid3` BIGINT NULL COMMENT
'点击sku对应的三级类目ID', `key_word` VARCHAR(512) NULL COMMENT '搜索词',
`search_num` HLL HLL_UNION NOT NULL COMMENT '搜索id' ) ENGINE=OLAP AGGREGATE
KEY(`pin_id`, `date`, `search_d
ate`, `pt`, `dim_type`, `business_type`, `campaign_type`,
`delivery_system_type`, `ad_plan_id`, `ad_group_id`, `deal_id`,
`branding_order_id`, `sku_brand_id`, `ad_sku_cid3`, `key_word`) COMMENT 'olap'
PARTITION BY RANGE(`date`) (PARTITION p202304 VALUES [('2023-04-01'),
('2023-05-01')), PARTITION p202305 VALUES [('2023-05-01'), ('2023-06-01')),
PARTITION p202306 VALUES [('2023-06-01'), ('2023-07-01')), PARTITION p202307
VALUES [('2023-07-01'), ('2023-08-01')), PARTITION p202308 VALUES
[('2023-08-01'), ('2023-09-01')), PARTITION p202309 VALUES [('2023-09-01'),
('2023-10-01')), PARTITION p202310 VALUES [('2023-10-01'), ('2023-11-01')),
PARTITION p202311 VALUES [('2023-11-01'), ('2023-12-01')), PARTITION p202312
VALUES [('2023-12-01'), ('2024-01-01')), PARTITION p202401 VALUES
[('2024-01-01'), ('2024-02-01')), PARTITION p202402 VALUES [('2024-02-01'),
('2024-03-01')), PARTITION p202403 VALUES [('2024-03-01'), ('2024-04-01')),
PARTITION p202404 VALUES [('2024-04-01'), ('2024-05-01')), P
ARTITION p202405 VALUES [('2024-05-01'), ('2024-06-01')), PARTITION p202406
VALUES [('2024-06-01'), ('2024-07-01')), PARTITION p202407 VALUES
[('2024-07-01'), ('2024-08-01')), PARTITION p202408 VALUES [('2024-08-01'),
('2024-09-01')), PARTITION p202409 VALUES [('2024-09-01'), ('2024-10-01')),
PARTITION p202410 VALUES [('2024-10-01'), ('2024-11-01')), PARTITION p202411
VALUES [('2024-11-01'), ('2024-12-01')), PARTITION p202412 VALUES
[('2024-12-01'), ('2025-01-01')), PARTITION p202501 VALUES [('2025-01-01'),
('2025-02-01'))) DISTRIBUTED BY HASH(`pin_id`) BUCKETS 32 PROPERTIES (
"replication_allocation" = "tag.location.default: 1",
"dynamic_partition.enable" = "true", "dynamic_partition.time_unit" = "month",
"dynamic_partition.time_zone" = "Asia/Shanghai", "dynamic_partition.start" =
"-2147483648", "dynamic_partition.end" = "5", "dynamic_partition.prefix" = "p",
"dynamic_partition.replication_allocation" = "tag.location.default: 1",
"dynamic_partition.buckets" = "32", "dynamic_partiti
on.create_history_partition" = "true",
"dynamic_partition.history_partition_num" = "12",
"dynamic_partition.hot_partition_num" = "0",
"dynamic_partition.reserved_history_periods" = "NULL",
"dynamic_partition.storage_policy" = "", "dynamic_partition.storage_medium" =
"hdd", "dynamic_partition.start_day_of_month" = "1", "storage_medium" = "hdd",
"storage_format" = "V2", "light_schema_change" = "true",
"disable_auto_compaction" = "false" );
insert into db.search_analysis_after_adv values(1,
"2024-08-01","2024-08-01",1,1,1,1,1,11,11,11,11,11,11,"me", hll_hash("1"));
insert into db.search_analysis_after_adv values(1,
"2024-08-01","2024-08-01",1,1,1,1,1,11,11,11,11,11,11,"me", hll_hash("111"));
SELECT HLL_UNION_AGG(search_num) AS search_num FROM
db.search_analysis_after_adv WHERE date >= '2024-06-05 00:00:00' AND date <
'2024-08-06 00:00:00';
step 2:
upgrade to 2.0,
set global experimental_enable_nereids_planner=false;
set global experimental_enable_nereids_planner=false;
query "SELECT HLL_UNION_AGG(search_num) AS search_num FROM
db.search_analysis_after_adv WHERE date >= '2024-06-05 00:00:00' AND date <
'2024-08-06 00:00:00';"
<!--Describe your changes.-->
--
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
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
