GoGoWen commented on pull request #7293:
URL: https://github.com/apache/incubator-doris/pull/7293#issuecomment-998676375
@morningman after test, seems this pr will cause performance decrease
heavy, i will submit another pr to find a better fix for this. below is the
test result.
cluster info: 1FE 3BE,
data info: file type: parquet, file size: 900M, file num: 64
load type: broker load from hdfs
schema: CREATE TABLE `test_table` (
`pin_id` bigint(20) NOT NULL COMMENT "",
`date_time` datetime NOT NULL COMMENT "",
`cart_day` datetime NOT NULL COMMENT "",
`campaign_id` bigint(20) NOT NULL COMMENT "",
`group_id` bigint(20) NOT NULL COMMENT "",
`ad_id` bigint(20) NOT NULL COMMENT "",
`date` date NOT NULL COMMENT "",
`cart_date` date NOT NULL COMMENT "",
`mobile_type` int(11) NOT NULL COMMENT "",
`dmp_id` bigint(20) NOT NULL COMMENT "",
`ad_traffic_group` int(11) NOT NULL COMMENT "",
`area_id` int(11) NOT NULL COMMENT "",
`pos_id` bigint(20) NOT NULL COMMENT "",
`pos_group_id` bigint(20) NOT NULL COMMENT "",
`ad_business_type` int(11) NOT NULL COMMENT "",
`ad_billing_type` int(11) NOT NULL COMMENT "",
`loc` int(11) NOT NULL COMMENT "",
`retrieval_type` int(11) NOT NULL COMMENT "",
`ad_sku_id` bigint(20) NOT NULL COMMENT "",
`ad_sku_brand_id` bigint(20) NOT NULL COMMENT "",
`ad_sku_cid3_id` int(11) NOT NULL COMMENT "",
`ad_type` int(11) NOT NULL COMMENT "",
`targeting_type` int(11) NOT NULL COMMENT "",
`hit_key` varchar(384) NOT NULL COMMENT "",
`search_word` varchar(2000) NULL COMMENT "",
`search_promote_rank` bigint(20) NOT NULL COMMENT "",
`automated_bidding_type` bigint(20) NOT NULL COMMENT "",
`activity_pin` varchar(153) NOT NULL COMMENT "",
`activity_pin_id` bigint(20) NOT NULL COMMENT "",
`activity_campaignid` bigint(20) NOT NULL COMMENT "",
`activity_adgroupid` bigint(20) NOT NULL COMMENT "",
`activity_adid` bigint(20) NOT NULL COMMENT "",
`activity_id` int(11) NOT NULL COMMENT "",
`campaign_type` int(11) NOT NULL COMMENT "",
`tcpa_phase` bigint(20) NOT NULL COMMENT "",
`dmp_crowd_ids` varchar(512) NOT NULL COMMENT "",
`new_arrival_type` int(11) NOT NULL COMMENT "",
`adv_id` varchar(32) NOT NULL COMMENT "",
`narwal_table_id` int(11) NOT NULL COMMENT "",
`activity_type` int(11) NOT NULL COMMENT "",
`landing_page_type` int(11) NOT NULL COMMENT "",
`promote_rank` int(11) NOT NULL COMMENT "",
`pos_package_id` bigint(20) NOT NULL COMMENT "",
`userpin` varchar(153) NOT NULL COMMENT "",
`delivery_type` int(11) NOT NULL DEFAULT "0" COMMENT "",
`product_delivery_matching_type` int(11) NOT NULL DEFAULT "0" COMMENT "",
`product_delivery_matching_id` bigint(20) NOT NULL DEFAULT "0" COMMENT "",
`product_delivery_trigger_sku_id` bigint(20) NOT NULL DEFAULT "0" COMMENT
"",
`parent_activity_id` bigint(20) NOT NULL DEFAULT "0" COMMENT "",
`branding_order_id` bigint(20) NOT NULL DEFAULT "0" COMMENT "",
`sku_premium_type` int(11) NOT NULL DEFAULT "0" COMMENT "",
`deal_id` bigint(20) NOT NULL DEFAULT "0" COMMENT "",
`selection_rule_bitmap` bigint(20) NOT NULL DEFAULT "0" COMMENT "",
`coupon_flag` int(11) NULL COMMENT "",
`strategy_id` bigint(20) NULL COMMENT "",
`strategy_type` int(11) NULL COMMENT "",
`opt_scenario_type` int(11) NULL COMMENT "",
`docnt` bigint(20) SUM NULL DEFAULT "0" COMMENT "直接加购数",
`idocnt` bigint(20) SUM NULL DEFAULT "0" COMMENT "间接加购数",
`rocnt` bigint(20) SUM NULL DEFAULT "0" COMMENT "影响加购数"
) ENGINE=OLAP
AGGREGATE KEY(`pin_id`, `date_time`, `cart_day`, `campaign_id`, `group_id`,
`ad_id`, `date`, `cart_date`, `mobile_type`, `dmp_id`, `ad_traffic_group`,
`area_id`, `pos_id`, `pos_group_id`, `ad_business_type`, `ad_billing_type`,
`loc`, `retrieval_type`, `ad_sku_id`, `ad_sku_brand_id`, `ad_sku_cid3_id`,
`ad_type`, `targeting_type`, `hit_key`, `search_word`, `search_promote_rank`,
`automated_bidding_type`, `activity_pin`, `activity_pin_id`,
`activity_campaignid`, `activity_adgroupid`, `activity_adid`, `activity_id`,
`campaign_type`, `tcpa_phase`, `dmp_crowd_ids`, `new_arrival_type`, `adv_id`,
`narwal_table_id`, `activity_type`, `landing_page_type`, `promote_rank`,
`pos_package_id`, `userpin`, `delivery_type`, `product_delivery_matching_type`,
`product_delivery_matching_id`, `product_delivery_trigger_sku_id`,
`parent_activity_id`, `branding_order_id`, `sku_premium_type`, `deal_id`,
`selection_rule_bitmap`, `coupon_flag`, `strategy_id`, `strategy_type`,
`opt_scenario_type`)
COMMENT "OLAP"
PARTITION BY RANGE(`cart_day`)
(PARTITION pbefore202108 VALUES [('1900-01-01 00:00:00'), ('2021-08-01
00:00:00')),
PARTITION p202108 VALUES [('2021-08-01 00:00:00'), ('2021-09-01 00:00:00')),
PARTITION p202109 VALUES [('2021-09-01 00:00:00'), ('2021-10-01 00:00:00')),
PARTITION p202110 VALUES [('2021-10-01 00:00:00'), ('2021-11-01 00:00:00')),
PARTITION p202111 VALUES [('2021-11-01 00:00:00'), ('2021-12-01 00:00:00')),
PARTITION p202112 VALUES [('2021-12-01 00:00:00'), ('2022-01-01 00:00:00')))
DISTRIBUTED BY HASH(`pin_id`) BUCKETS 16
PROPERTIES (
"replication_num" = "3",
"in_memory" = "false",
"storage_format" = "V2"
);
cost time before fix: 7m16s
cost time after fix:18m50s
--
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: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
