[MCOL-4595] query execution time takes much longer in 5.5.x than 1.2.5 Created: 2021-03-09  Updated: 2021-11-24

Status: Open
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 5.5.1
Fix Version/s: Icebox

Type: Bug Priority: Major
Reporter: Allen Lee (Inactive) Assignee: Unassigned
Resolution: Unresolved Votes: 0
Labels: None
Environment:

CentOS, Virtualized, On Premise

Attachments: File View.sql     PNG File compare calGetTrace.png    

 Description   

Customer reported that query time takes much long in v5.5.x than v1.2.5.
Here is calSetTrace result after calFlushCache from v1.2.5 and v.5.5.1.
[v1.2.5]

Old System Columnstore:
 
select calFlushCache();
 
select calSetTrace(1);
 
SELECT v.`WHO1_ID` AS `1102_WHO1`
 
,SUM(`VAL_61_200`) AS `VALUE_2018_01_M_200_1_1`
 
...
 
...
 
select calGetStats();
 
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| calGetStats()                                                                                                                                                                                             |
 
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-305648; CacheI/O-305627; BlocksTouched-305544; PartitionBlocksEliminated-0; MsgBytesIn-861KB; MsgBytesOut-19MB; Mode-Distributed   |
 
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
select convert(calGetTrace() ,CHAR);
 
 
 
Desc Mode Table               TableOID ReferencedColumns                                                                                                                                      PIO    LIO  PBE Elapsed Rows
 
CES   UM  -                   -        -                                                                                                                                                      -       -     - 0.161 160341
 
BPS   PM fact_de_nvikt_1102     888958 (PZN_ID,VAL_13_200,VAL_14_200,VAL_15_200,VAL_16_200,VAL_17_200,VAL_18_200,VAL_19_200,VAL_1_101,VAL_20_200,VAL_21_200,VAL_22_200,VAL_23_200,VAL_24_200) 305648 305627 0 9.649 1980
 
HJS   PM fact_de_nvikt_1102-ct  888958 ------- -
 
TAS   UM -                    -        -                                                                                                                                                      -       -     - 8.646 15
 
TNS   UM -                    -        -                                                                                                                                                      -       -     - 0.000 15

[v5.5.1]

select calFlushCache();
 
select calSetTrace(1);
 
SELECT v.`WHO1_ID` AS `1102_WHO1`
 
,SUM(`VAL_61_200`) AS `VALUE_2018_01_M_200_1_1`
 
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| calGetStats()                                                                                                                                                                                             |
 
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-305606; CacheI/O-305588; BlocksTouched-305528; PartitionBlocksEliminated-0; MsgBytesIn-801KB; MsgBytesOut-14MB; Mode-Distributed |
 
+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
 
 
select convert(calGetTrace() ,CHAR);
 
Desc Mode Table                 TableOID  ReferencedColumns                                                                                                                                     PIO     LIO   PBE Elapsed Rows
 
CES   UM  -                     -         -                                                                                                                                                     -       -     -     0.101 160341
 
BPS   PM fact_de_nvikt_1102     23610     (pzn_id,val_13_200,val_14_200,val_15_200,val_16_200,val_17_200,val_18_200,val_19_200,val_1_101,val_20_200,val_21_200,val_22_200,val_23_200,val_24_200) 305606 305588 0  115.846 1485
 
HJS   PM fact_de_nvikt_1102-ct  23610 - - - - ----- -
 
TAS   UM -                      -         -                                                                                                                                                      -      -      -  113.812 15
 
TNS   UM -                      -         -                                                                                                                                                      -      -      -    0.000 15
 
|



 Comments   
Comment by David Hall (Inactive) [ 2021-03-09 ]

What is the definition of the fields v1`.`PZN_ID` and `ct`.`PZN_ID`. These are the fields in the join. If they are char or varchar, is it possible to use int instead?

Comment by Thorsten [ 2021-03-10 ]

Both PZN_IDs are integer.

Comment by Thorsten [ 2021-03-10 ]

I think it is not a join problem, we get the same strange time with a sql without join.

MCS

select calFlushCache();
select calSetTrace(1);

SELECT v.`WHO1_ID` AS `1102_WHO1`,SUM(`VAL_61_200`) AS `VALUE_2018_01_M_200_1_1`,SUM(`VAL_62_200`) AS `VALUE_2018_02_M_200_1_2`,SUM(`VAL_63_200`) AS `VALUE_2018_03_M_200_1_3`,SUM(`VAL_64_200`) AS `VALUE_2018_04_M_200_1_4`,SUM(`VAL_65_200`) AS `VALUE_2018_05_M_200_1_5`,SUM(`VAL_66_200`) AS `VALUE_2018_06_M_200_1_6`,SUM(`VAL_67_200`) AS `VALUE_2018_07_M_200_1_7`,SUM(`VAL_68_200`) AS `VALUE_2018_08_M_200_1_8`,SUM(`VAL_69_200`) AS `VALUE_2018_09_M_200_1_9`,SUM(`VAL_70_200`) AS `VALUE_2018_10_M_200_1_10`,SUM(`VAL_71_200`) AS `VALUE_2018_11_M_200_1_11`,SUM(`VAL_72_200`) AS `VALUE_2018_12_M_200_1_12`,SUM(`VAL_73_200`) AS `VALUE_2019_01_M_200_1_13`,SUM(`VAL_74_200`) AS `VALUE_2019_02_M_200_1_14`,SUM(`VAL_75_200`) AS `VALUE_2019_03_M_200_1_15`,SUM(`VAL_76_200`) AS `VALUE_2019_04_M_200_1_16`,SUM(`VAL_77_200`) AS `VALUE_2019_05_M_200_1_17`,SUM(`VAL_78_200`) AS `VALUE_2019_06_M_200_1_18`,SUM(`VAL_79_200`) AS `VALUE_2019_07_M_200_1_19`,SUM(`VAL_80_200`) AS `VALUE_2019_08_M_200_1_20`,SUM(`VAL_81_200`) AS `VALUE_2019_09_M_200_1_21`,SUM(`VAL_82_200`) AS `VALUE_2019_10_M_200_1_22`,SUM(`VAL_83_200`) AS `VALUE_2019_11_M_200_1_23`,SUM(`VAL_84_200`) AS `VALUE_2019_12_M_200_1_24`,SUM(`VAL_85_200`) AS `VALUE_2020_01_M_200_1_25`,SUM(`VAL_86_200`) AS `VALUE_2020_02_M_200_1_26`,SUM(`VAL_87_200`) AS `VALUE_2020_03_M_200_1_27`,SUM(`VAL_88_200`) AS `VALUE_2020_04_M_200_1_28`,SUM(`VAL_89_200`) AS `VALUE_2020_05_M_200_1_29`,SUM(`VAL_90_200`) AS `VALUE_2020_06_M_200_1_30`,SUM(`VAL_91_200`) AS `VALUE_2020_07_M_200_1_31`,SUM(`VAL_92_200`) AS `VALUE_2020_08_M_200_1_32`,SUM(`VAL_93_200`) AS `VALUE_2020_09_M_200_1_33`,SUM(`VAL_94_200`) AS `VALUE_2020_10_M_200_1_34`,SUM(`VAL_95_200`) AS `VALUE_2020_11_M_200_1_35`,SUM(`VAL_96_200`) AS `VALUE_2020_12_M_200_1_36`,SUM(`VAL_97_200`) AS `VALUE_2021_01_M_200_1_37`
FROM `DE_NVIKT_1102_202101`.`vpa_fact_de_NVIKT_1102_201903_3` AS v
WHERE 1=1 GROUP BY `1102_WHO1`;
#############################################################
select calGetStats();
Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-589573; CacheI/O-589451; BlocksTouched-589369; PartitionBlocksEliminated-0; MsgBytesIn-1MB; MsgBytesOut-231KB; Mode-Distributed

#############################################################
select convert(calGetTrace() ,CHAR);

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
BPS PM fact_de_nvikt_1102 1049535 (VAL_10_200,VAL_11_200,VAL_12_200,VAL_13_200,VAL_14_200,VAL_15_200,VAL_16_200,VAL_17_200,VAL_18_200,VAL_19_200,VAL_1_101,VAL_1_200,VAL_20_200,VAL_21_200,VAL_22_200,VAL_23_200,VAL_24_200,VAL_25_200,VAL_2_200,VAL_3_200,VAL_4_200,VAL_5_200,VAL_6_200,VAL_7_200,VAL_8_200,VAL_9_200,WHO1_ID) 589573 589451 0 10.230 2112
TAS UM - - - - - - 7.806 16
TNS UM - - - - - - 0.000 16

##########################################################################################################################
##########################################################################################################################
##########################################################################################################################

MariaDB 10.5.8

select calFlushCache();
select calSetTrace(1);

SELECT v.`WHO1_ID` AS `1102_WHO1`,SUM(`VAL_61_200`) AS `VALUE_2018_01_M_200_1_1`,SUM(`VAL_62_200`) AS `VALUE_2018_02_M_200_1_2`,SUM(`VAL_63_200`) AS `VALUE_2018_03_M_200_1_3`,SUM(`VAL_64_200`) AS `VALUE_2018_04_M_200_1_4`,SUM(`VAL_65_200`) AS `VALUE_2018_05_M_200_1_5`,SUM(`VAL_66_200`) AS `VALUE_2018_06_M_200_1_6`,SUM(`VAL_67_200`) AS `VALUE_2018_07_M_200_1_7`,SUM(`VAL_68_200`) AS `VALUE_2018_08_M_200_1_8`,SUM(`VAL_69_200`) AS `VALUE_2018_09_M_200_1_9`,SUM(`VAL_70_200`) AS `VALUE_2018_10_M_200_1_10`,SUM(`VAL_71_200`) AS `VALUE_2018_11_M_200_1_11`,SUM(`VAL_72_200`) AS `VALUE_2018_12_M_200_1_12`,SUM(`VAL_73_200`) AS `VALUE_2019_01_M_200_1_13`,SUM(`VAL_74_200`) AS `VALUE_2019_02_M_200_1_14`,SUM(`VAL_75_200`) AS `VALUE_2019_03_M_200_1_15`,SUM(`VAL_76_200`) AS `VALUE_2019_04_M_200_1_16`,SUM(`VAL_77_200`) AS `VALUE_2019_05_M_200_1_17`,SUM(`VAL_78_200`) AS `VALUE_2019_06_M_200_1_18`,SUM(`VAL_79_200`) AS `VALUE_2019_07_M_200_1_19`,SUM(`VAL_80_200`) AS `VALUE_2019_08_M_200_1_20`,SUM(`VAL_81_200`) AS `VALUE_2019_09_M_200_1_21`,SUM(`VAL_82_200`) AS `VALUE_2019_10_M_200_1_22`,SUM(`VAL_83_200`) AS `VALUE_2019_11_M_200_1_23`,SUM(`VAL_84_200`) AS `VALUE_2019_12_M_200_1_24`,SUM(`VAL_85_200`) AS `VALUE_2020_01_M_200_1_25`,SUM(`VAL_86_200`) AS `VALUE_2020_02_M_200_1_26`,SUM(`VAL_87_200`) AS `VALUE_2020_03_M_200_1_27`,SUM(`VAL_88_200`) AS `VALUE_2020_04_M_200_1_28`,SUM(`VAL_89_200`) AS `VALUE_2020_05_M_200_1_29`,SUM(`VAL_90_200`) AS `VALUE_2020_06_M_200_1_30`,SUM(`VAL_91_200`) AS `VALUE_2020_07_M_200_1_31`,SUM(`VAL_92_200`) AS `VALUE_2020_08_M_200_1_32`,SUM(`VAL_93_200`) AS `VALUE_2020_09_M_200_1_33`,SUM(`VAL_94_200`) AS `VALUE_2020_10_M_200_1_34`,SUM(`VAL_95_200`) AS `VALUE_2020_11_M_200_1_35`,SUM(`VAL_96_200`) AS `VALUE_2020_12_M_200_1_36`,SUM(`VAL_97_200`) AS `VALUE_2021_01_M_200_1_37`
FROM `DE_NVIKT_1102_202101`.`vpa_fact_de_NVIKT_1102_201903_3` AS v
WHERE 1=1 GROUP BY `1102_WHO1`;

#############################################################
select calGetStats();
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

calGetStats()

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-589520; CacheI/O-589427; BlocksTouched-589367; PartitionBlocksEliminated-0; MsgBytesIn-978KB; MsgBytesOut-189KB; Mode-Distributed

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

#############################################################

MariaDB [(none)]> select convert(calGetTrace() ,CHAR);

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
BPS PM fact_de_nvikt_1102 189062 (val_10_200,val_11_200,val_12_200,val_13_200,val_14_200,val_15_200,val_16_200,val_17_200,val_18_200,val_19_200,val_1_101,val_1_200,val_20_200,val_21_200,val_22_200,val_23_200,val_24_200,val_25_200,val_2_200,val_3_200,val_4_200,val_5_200,val_6_200,val_7_200,val_8_200,val_9_200,who1_id) 589520 589427 0 63.251 1584
TAS UM - - - - - - 61.414 16
TNS UM - - - - - - 0.000 16

Generated at Thu Feb 08 02:51:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.