Open BS490 opened 4 days ago
2.0.8
create table sql: CREATE TABLE dws_mediago_bidder_dsp_multi_measures_hourly ( campaign_id int(11) NULL, asset_id int(11) NULL, ssp int(11) NULL, ad_id int(11) NULL, crid varchar(50) NULL, domain varchar(2000) NULL, ip_country varchar(200) NULL, account_id varchar(32) NULL, account_name varchar(500) NULL, company_id varchar(32) NULL, company_name varchar(500) NULL, am_name varchar(50) NULL, platform_type varchar(10) NULL, account_category varchar(100) NULL, company_region varchar(20) NULL, charge_type varchar(20) NULL, target_cpa DECIMAL(15, 9) NULL, d_s date NULL, h_s int(11) NULL, ad_count bigint(20) SUM NULL, all_req_num bigint(20) SUM NULL, account_gross_click_cost double SUM NULL, click bigint(20) SUM NULL, click_cost double SUM NULL, conversion bigint(20) SUM NULL, cv bigint(20) SUM NULL, imp bigint(20) SUM NULL, imp_cost double SUM NULL, vimp bigint(20) SUM NULL, mcv bigint(20) SUM NULL, flr_sum_fix double SUM NULL, bid_price_sum double SUM NULL, req_num double SUM NULL, prctr double SUM NULL, pclick double SUM NULL, req_ad_num bigint(20) SUM NULL ) ENGINE=OLAP AGGREGATE KEY( campaign_id , asset_id , ssp , ad_id , crid , domain , ip_country , account_id , account_name , company_id , company_name , am_name , platform_type , account_category , company_region , charge_type , target_cpa , d_s , h_s ) COMMENT 'OLAP' PARTITION BY RANGE( d_s , h_s )() DISTRIBUTED BY HASH( campaign_id ) BUCKETS 36 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "bloom_filter_columns" = "ssp, domain", "is_being_synced" = "false", "storage_format" = "V2", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false" );
CREATE TABLE dws_mediago_bidder_dsp_multi_measures_hourly ( campaign_id int(11) NULL, asset_id int(11) NULL, ssp int(11) NULL, ad_id int(11) NULL, crid varchar(50) NULL, domain varchar(2000) NULL, ip_country varchar(200) NULL, account_id varchar(32) NULL, account_name varchar(500) NULL, company_id varchar(32) NULL, company_name varchar(500) NULL, am_name varchar(50) NULL, platform_type varchar(10) NULL, account_category varchar(100) NULL, company_region varchar(20) NULL, charge_type varchar(20) NULL, target_cpa DECIMAL(15, 9) NULL, d_s date NULL, h_s int(11) NULL, ad_count bigint(20) SUM NULL, all_req_num bigint(20) SUM NULL, account_gross_click_cost double SUM NULL, click bigint(20) SUM NULL, click_cost double SUM NULL, conversion bigint(20) SUM NULL, cv bigint(20) SUM NULL, imp bigint(20) SUM NULL, imp_cost double SUM NULL, vimp bigint(20) SUM NULL, mcv bigint(20) SUM NULL, flr_sum_fix double SUM NULL, bid_price_sum double SUM NULL, req_num double SUM NULL, prctr double SUM NULL, pclick double SUM NULL, req_ad_num bigint(20) SUM NULL ) ENGINE=OLAP AGGREGATE KEY( campaign_id , asset_id , ssp , ad_id , crid , domain , ip_country , account_id , account_name , company_id , company_name , am_name , platform_type , account_category , company_region , charge_type , target_cpa , d_s , h_s ) COMMENT 'OLAP' PARTITION BY RANGE( d_s , h_s )() DISTRIBUTED BY HASH( campaign_id ) BUCKETS 36 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "bloom_filter_columns" = "ssp, domain", "is_being_synced" = "false", "storage_format" = "V2", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false" );
first sql:
second sql:
you can see different select express leading to dozens of times performance difference
first sql explain:
second sql explain:
difference: VOlapScanNode PREAGGREGATION of first sql is off, second sql is on
1.why VOlapScanNode PREAGGREGATION of first sql is off? 2.is VOlapScanNode PREAGGREGATION the key of performance diff? 3.is there any way to solve it?
create table sql: CREATE TABLEtest2( k1date NULL, v2int(11) SUM NULL DEFAULT "1" ) ENGINE=OLAP AGGREGATE KEY(k1) COMMENT 'OLAP' DISTRIBUTED BY HASH(k1) BUCKETS 32 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "is_being_synced" = "false", "storage_format" = "V2", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false" );
CREATE TABLE
(
date NULL,
int(11) SUM NULL DEFAULT "1" ) ENGINE=OLAP AGGREGATE KEY(
) COMMENT 'OLAP' DISTRIBUTED BY HASH(
) BUCKETS 32 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "is_being_synced" = "false", "storage_format" = "V2", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false" );
explain select sum(if(k1='12',v2,0)) from test2;
No response
Search before asking
Version
2.0.8
What's Wrong?
create table sql:
CREATE TABLE dws_mediago_bidder_dsp_multi_measures_hourly ( campaign_id int(11) NULL, asset_id int(11) NULL, ssp int(11) NULL, ad_id int(11) NULL, crid varchar(50) NULL, domain varchar(2000) NULL, ip_country varchar(200) NULL, account_id varchar(32) NULL, account_name varchar(500) NULL, company_id varchar(32) NULL, company_name varchar(500) NULL, am_name varchar(50) NULL, platform_type varchar(10) NULL, account_category varchar(100) NULL, company_region varchar(20) NULL, charge_type varchar(20) NULL, target_cpa DECIMAL(15, 9) NULL, d_s date NULL, h_s int(11) NULL, ad_count bigint(20) SUM NULL, all_req_num bigint(20) SUM NULL, account_gross_click_cost double SUM NULL, click bigint(20) SUM NULL, click_cost double SUM NULL, conversion bigint(20) SUM NULL, cv bigint(20) SUM NULL, imp bigint(20) SUM NULL, imp_cost double SUM NULL, vimp bigint(20) SUM NULL, mcv bigint(20) SUM NULL, flr_sum_fix double SUM NULL, bid_price_sum double SUM NULL, req_num double SUM NULL, prctr double SUM NULL, pclick double SUM NULL, req_ad_num bigint(20) SUM NULL ) ENGINE=OLAP AGGREGATE KEY( campaign_id , asset_id , ssp , ad_id , crid , domain , ip_country , account_id , account_name , company_id , company_name , am_name , platform_type , account_category , company_region , charge_type , target_cpa , d_s , h_s ) COMMENT 'OLAP' PARTITION BY RANGE( d_s , h_s )() DISTRIBUTED BY HASH( campaign_id ) BUCKETS 36 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "bloom_filter_columns" = "ssp, domain", "is_being_synced" = "false", "storage_format" = "V2", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false" );first sql:
second sql:
you can see different select express leading to dozens of times performance difference
first sql explain:
second sql explain:
difference: VOlapScanNode PREAGGREGATION of first sql is off, second sql is on
What You Expected?
1.why VOlapScanNode PREAGGREGATION of first sql is off? 2.is VOlapScanNode PREAGGREGATION the key of performance diff? 3.is there any way to solve it?
How to Reproduce?
create table sql:
CREATE TABLEtest2(k1date NULL,v2int(11) SUM NULL DEFAULT "1" ) ENGINE=OLAP AGGREGATE KEY(k1) COMMENT 'OLAP' DISTRIBUTED BY HASH(k1) BUCKETS 32 PROPERTIES ( "replication_allocation" = "tag.location.default: 3", "is_being_synced" = "false", "storage_format" = "V2", "light_schema_change" = "true", "disable_auto_compaction" = "false", "enable_single_replica_compaction" = "false" );explain select sum(if(k1='12',v2,0)) from test2;Anything Else?
No response
Are you willing to submit PR?
Code of Conduct