[MCOL-4527] Simple query performace is degraded between 5.4 and 5.5 Created: 2021-02-02  Updated: 2021-02-20  Resolved: 2021-02-18

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: 5.5.1
Fix Version/s: 5.5.2

Type: Bug Priority: Blocker
Reporter: Gregory Dorman (Inactive) Assignee: Daniel Lee (Inactive)
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Duplicate
is duplicated by MCOL-4503 query executime is not consistent Closed
PartOf
is part of MCOL-4522 calGetTrace shows double LIO from Com... Closed
Sub-Tasks:
Key
 Summary
 Type
 Status
 Assignee
MCOL-4528 Analyze and explain the reasons for t... Sub-Task Closed David Hall  
Sprint: 2021-2, 2021-3

 Description   

There appears to be a large loss of performance between 5.4 and 5.5 (close to 100%).

In this reproduction, the table charset is set to lating1_bin, so 5 characters ZIP does participate in extent elimination in both versions. The 5.5 elapsed time, however, appears twice that of 5.4.

CREATE TABLE `mailing_addresses_enriched` (
`ZIP` varchar(5) NOT NULL,
`CarrierRoute` varchar(4) NOT NULL,
`ZIP4` varchar(4) DEFAULT NULL,
`WalkSequence` int(11) DEFAULT NULL,
`StreetNumber` varchar(10) DEFAULT NULL,
`StreetPreDirectional` varchar(2) DEFAULT NULL,
`StreetName` varchar(28) DEFAULT NULL,
`StreetPostDirectional` varchar(2) DEFAULT NULL,
`StreetSuffix` varchar(4) DEFAULT NULL,
`SecondaryUnitDesignator` varchar(4) DEFAULT NULL,
`SecondaryUnitNumber` varchar(8) DEFAULT NULL,
`AddressVacancyIndicator` varchar(1) DEFAULT NULL,
`ThrowBackIndicator` varchar(1) DEFAULT NULL,
`SeasonalDeliveryIndicator` varchar(1) DEFAULT NULL,
`SeasonalStartSuppression` varchar(5) DEFAULT NULL,
`SeasonalEndSuppression` varchar(5) DEFAULT NULL,
`DNDIndicator` varchar(1) DEFAULT NULL,
`CollegeIndicator` varchar(1) DEFAULT NULL,
`CollegeStartSuppression` varchar(10) DEFAULT NULL,
`CollegeEndSuppression` varchar(10) DEFAULT NULL,
`AddressStyleFlag` varchar(1) DEFAULT NULL,
`SimplifyAddressCount` decimal(5,0) DEFAULT NULL,
`DropIndicator` varchar(1) DEFAULT NULL,
`DeliveryPointUsageCode` varchar(1) DEFAULT NULL,
`DPBCDigit` varchar(2) DEFAULT NULL,
`DPBCCheckDigit` int(11) DEFAULT NULL,
`UpdateDate` varchar(10) DEFAULT NULL,
`FileReleaseDate` varchar(10) DEFAULT NULL,
`OverrideFileReleaseDate` varchar(10) DEFAULT NULL,
`CountyNumber` varchar(3) DEFAULT NULL,
`CountyName` varchar(28) DEFAULT NULL,
`CityName` varchar(28) DEFAULT NULL,
`StateCode` varchar(2) DEFAULT NULL,
`StateNumber` varchar(2) DEFAULT NULL,
`CongressionalDistrictNumber` varchar(2) DEFAULT NULL,
`OWGMIndicator` varchar(1) DEFAULT NULL,
`RecordTypeCode` varchar(1) DEFAULT NULL,
`AddressKey` int(11) NOT NULL,
`AddressType` int(11) DEFAULT NULL,
`DeliveryPointTypeCode` varchar(1) DEFAULT NULL,
`latitude` float DEFAULT NULL,
`longitude` float DEFAULT NULL,
`FIPS` varchar(6) DEFAULT NULL,
`ZIPCR` varchar(10) DEFAULT NULL,
`PO_Box_Flag` int(11) DEFAULT NULL,
`Secondary_Unit_Flag` int(11) DEFAULT NULL,
`DeliveryPointUsageCode2` varchar(1) DEFAULT NULL,
`Zip_Zip4` bigint(20) DEFAULT NULL,
`Zip_Zip4_DPV` bigint(20) DEFAULT NULL,
`ZZ_Street` varchar(29) DEFAULT NULL,
`ZZD_Street_Unit` varchar(29) DEFAULT NULL,
`Z_Street` varchar(60) DEFAULT NULL,
`Z_Street_Unit` varchar(60) DEFAULT NULL,
`address1` varchar(150) DEFAULT NULL,
`address2` varchar(150) DEFAULT NULL,
`UPPER_CityName` varchar(28) DEFAULT NULL,
`mailing_dwelling` varchar(33) DEFAULT NULL,
`SeasonalStartSuppression_DayofYear` int(11) DEFAULT NULL,
`SeasonalEndSuppression_DayofYear` int(11) DEFAULT NULL,
`CollegeStartSuppression_DayofYear` int(11) DEFAULT NULL,
`CollegeEndSuppression_DayofYear` int(11) DEFAULT NULL,
`Mailing_Dwelling_Description` varchar(15) DEFAULT NULL,
`Load_Seq` int(11) DEFAULT NULL,
`Load_Group` int(11) DEFAULT NULL,
`CBSA` int(11) DEFAULT NULL,
`CBSA_Description` varchar(55) DEFAULT NULL,
`CBSA_State_Description` varchar(60) DEFAULT NULL,
`DMA` int(11) DEFAULT NULL,
`DMA_Name` varchar(50) DEFAULT NULL,
`DMA_State_Name` varchar(55) DEFAULT NULL
) ENGINE=Columnstore DEFAULT CHARSET=latin1_bin;

SELECT `AddressKey`,
`CountyName`,
`StateCode`
FROM `mailing_addresses_enriched` WHERE zip = '33776';

Server 1: Enterprise CS 5.5.1
Table CHAR SET latin1/latin1_bin
5679 rows in set, 1 warning (1.780 sec)

Get Trace

                                                      • 1. row ***************************
                                                        calGetTrace():
                                                        Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
                                                        BPS PM mailing_addresses_cistest 6432 (addresskey,cityname,statecode,zip) 0 158478 0 1.744 5679
                                                        TNS UM - - - - - - 1.724 5679

Server 3: Enterprise CS 5.4.1
Table CHAR SET utf8/utf8mb4
5679 rows in set, 1 warning (0.866 sec)

Get Trace

                                                      • 1. row ***************************
                                                        calGetTrace():
                                                        Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
                                                        BPS PM mailing_addresses_cis 4343 (addresskey,cityname,statecode,zip) 0 109325 49152 0.851 5679
                                                        TNS UM - - - - - - 0.828 5679

 Comments   
Comment by Gregory Dorman (Inactive) [ 2021-02-02 ]

4522 is a larger problem - ever since we started to respect real byte count in utf8, a varchar(5) field no longer participates in extent elimination.

However, even if this is work-arounded by using latin1, we are still seeing a large degradation of raw performance between 5.4 and 5.5.

Comment by Alexander Barkov [ 2021-02-03 ]

I can't repeat the problem reported here.

RelWithDebInfo builds for 5.4 and 5.5 were used in the tests below.

Preparation - make a dump file using a MyISAM table

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (c INT) ENGINE=MyISAM;
 
 
 
DELIMITER $$
 
FOR i IN 0..99999 DO
 
  FOR j IN 0..99 DO
 
    INSERT INTO t1 VALUES (i);
 
  END FOR;
 
END FOR;
 
$$
 
DELIMITER ;
 
 
 
SELECT c INTO OUTFILE 't1.txt' FROM t1;

ColumnStore tables in 5.4

DROP TABLE IF EXISTS t2_bigint;
 
CREATE TABLE t2_bigint (zip BIGINT) ENGINE=ColumnStore;
 
LOAD DATA INFILE 't1.txt' INTO TABLE t2_bigint;
 
 
 
DROP TABLE IF EXISTS t3_char;
 
CREATE TABLE t3_char (zip CHAR(5)) ENGINE=ColumnStore;
 
LOAD DATA INFILE 't1.txt' INTO TABLE t3_char;

Querying 5.4 tables

select calSetTrace(1);
 
SELECT * FROM t2_bigint WHERE zip=1111;
 
select calgettrace();


+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| calgettrace()                                                                                                                                                                                                                      |
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| 
Desc Mode Table     TableOID ReferencedColumns PIO LIO  PBE  Elapsed Rows 
BPS  PM   t2_bigint 3000     (zip)             0   8192 1575 0.024   100  
TNS  UM   -         -        -                 -   -    -    0.020   100  
 |
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


select calSetTrace(1);
 
SELECT * FROM t3_char WHERE zip='1111';
 
select calgettrace();


+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| calgettrace()                                                                                                                                                                                                                |
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| 
Desc Mode Table   TableOID ReferencedColumns PIO LIO  PBE  Elapsed Rows 
BPS  PM   t3_char 3002     (zip)             0   8192 1575 0.053   100  
TNS  UM   -       -        -                 -   -    -    0.049   100  
 |
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

ColumnStore tables - 5.5

DROP TABLE IF EXISTS t2_bigint;
 
CREATE TABLE t2_bigint (zip BIGINT) ENGINE=ColumnStore;
 
LOAD DATA INFILE 't1.txt' INTO TABLE t2_bigint;
 
 
 
DROP TABLE IF EXISTS t3_char_latin1_bin;
 
CREATE TABLE t3_char_latin1_bin (zip CHAR(5)) ENGINE=ColumnStore;
 
LOAD DATA INFILE 't1.txt' INTO TABLE t3_char_latin1_bin;
 
 
 
DROP TABLE IF EXISTS t3_char_latin1_swedish_ci;
 
CREATE TABLE t3_char_latin1_swedish_ci (zip CHAR(5) CHARACTER SET latin1 COLLATE latin1_swedish_ci) ENGINE=ColumnStore;
 
LOAD DATA INFILE 't1.txt' INTO TABLE t3_char_latin1_swedish_ci;
 
 
 
DROP TABLE IF EXISTS t3_char_utf8_bin;
 
CREATE TABLE t3_char_utf8_bin (zip CHAR(5) CHARACTER SET utf8 COLLATE utf8_bin) ENGINE=ColumnStore;
 
LOAD DATA INFILE 't1.txt' INTO TABLE t3_char_utf8_bin;

Querying 5.5 tables

select calSetTrace(1);
 
SELECT * FROM t2_bigint WHERE zip=1111;
 
select calgettrace();


+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| calgettrace()                                                                                                                                                                                                                      |
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| 
 
 
Desc Mode Table     TableOID ReferencedColumns PIO LIO  PBE  Elapsed Rows 
BPS  PM   t2_bigint 3037     (zip)             0   8192 1575 0.021   100  
TNS  UM   -         -        -                 -   -    -    0.019   100  
 |
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


select calSetTrace(1);
 
SELECT * FROM t3_char_latin1_bin WHERE zip='1111';
 
select calgettrace();


+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| calgettrace()                                                                                                                                                                                                                                                 |
 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| 
Desc Mode Table              TableOID ReferencedColumns PIO LIO  PBE  Elapsed Rows 
BPS  PM   t3_char_latin1_bin 3039     (zip)             0   8192 1575 0.059   100  
TNS  UM   -                  -        -                 -   -    -    0.055   100  
 |
 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


select calSetTrace(1);
 
SELECT * FROM t3_char_latin1_swedish_ci WHERE zip='1111';
 
select calgettrace();


+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| calgettrace()                                                                                                                                                                                                                                                                      |
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| 
Desc Mode Table                     TableOID ReferencedColumns PIO LIO  PBE  Elapsed Rows 
BPS  PM   t3_char_latin1_swedish_ci 3041     (zip)             0   8192 1575 0.052   100  
TNS  UM   -                         -        -                 -   -    -    0.049   100  
 |
 
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+


select calSetTrace(1);
 
SELECT * FROM t3_char_utf8_bin WHERE zip='1111';
 
select calgettrace();


+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| calgettrace()                                                                                                                                                                                                                                           |
 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| 
Desc Mode Table            TableOID ReferencedColumns PIO LIO   PBE Elapsed Rows 
BPS  PM   t3_char_utf8_bin 3043     (zip)             0   20577 0   0.154   100  
TNS  UM   -                -        -                 -   -     -   0.148   100  
 |
 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

Summary

Version test                      LIO    PBE    Elapsed
 
------- ----                      ---    ---    ------- 
5.4     t2_bigint                 8192   1575   0.024
 
5.4     t3_char                   8192   1575   0.053 
5.5     t2_bigint                 8192   1575   0.021
 
5.5     t3_char_latin1_bin        8192   1575   0.059
 
5.5     t3_char_latin1_swedish_ci 8192   1575   0.052
 
5.5     t3_char_utf8_bin          20577  0      0.154

Comment by Alexander Barkov [ 2021-02-03 ]

Note, every script like this:

select calSetTrace(1);
 
SELECT * FROM t3_char_utf8_bin WHERE zip='1111';
 
select calgettrace();

was run multiple times (~20 times) and the best result was recorded in the statistics above.

Note, exact timing jumped in a very wide range within a consequent run.
So 0.053 vs 0.059 vs 0.059 can be considered as equal (within the margin of error).
To get a more precise statistics, more rows should be used.

Comment by Alexander Barkov [ 2021-02-03 ]

Note, the initial script reported in this MCOL (see above) has an error:

) ENGINE=Columnstore DEFAULT CHARSET=latin1_bin;

So an attempt to create the table returns an error:

ERROR 1115 (42000): Unknown character set: 'latin1_bin'

The correct syntax is:

) ENGINE=Columnstore DEFAULT CHARACTER SET latin1 COLLATE latin1_bin;

Comment by Gregory Dorman (Inactive) [ 2021-02-11 ]

Wait... <bar@mariadb.com> @Alexander Barkov - we cannot make this change quickly enough?

Comment by David Hall (Inactive) [ 2021-02-17 ]

QA:
To test, you must have a large dataset that covers multiple extents. You must have a character field that contains only latin1 characters that you want to filter on.

There are two tests.
1) ENGINE=Columnstore DEFAULT CHARACTER SET latin1 COLLATE latin1_nopad_bin;
2) ENGINE=Columnstore DEFAULT CHARACTER SET UTF8 with the field defined with COLLATE latin1_nopad_bin:
`ZIP` varchar(5) CHARACTER SET latin1 COLLATE latin1_nopad_bin NOT NULL,

Comment by David Hall (Inactive) [ 2021-02-17 ]

This fixes only this simple case. Further work will be done in MCOL-4534 for more general cases.

Comment by Daniel Lee (Inactive) [ 2021-02-18 ]

Build verified: 5.5.2-1 (Drone builds Drone engine=1678), 5.4.1

5.5.2 is a little faster than 5.4.1. Tests were done using VMs, with the only VM running on the test server.

5.4.1

drop table if exists lineitem;
create table lineitem (
l_orderkey char(8),
l_partkey int,
l_suppkey int,
l_linenumber bigint,
l_quantity decimal(12,2),
l_extendedprice decimal(12,2),
l_discount decimal(12,2),
l_tax decimal(12,2),
l_returnflag char (1),
l_linestatus char (1),
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char (25),
l_shipmode char (10),
l_comment varchar (44)
) engine=Columnstore DEFAULT CHARACTER SET latin1 COLLATE latin1_nopad_bin;

cpimport 10g lineitem twice, a total of 119972104 rows, or 16 extends

[centos8:root~]# /usr/bin/colxml mytest -t lineitem -b4|grep l_orderkey
<Column colName="l_orderkey" colOid="3038" dataType="char" compressType="2" width="8"/>

[centos8:root~]# /usr/bin/editem -o 3038
Col OID = 3038, NumExtents = 15, width = 8
471040 - 479231 (8192) min: 49, max: 58511299524921, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 0, seg#: 0, HWM: 0; status: avail
616448 - 624639 (8192) min: 3472328296227680305, max: 14978892678379833, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 0, seg#: 1, HWM: 0; status: unavail
761856 - 770047 (8192) min: 3906084542581519921, max: 3761405335071438130, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 0, seg#: 2, HWM: 0; status: unavail
907264 - 915455 (8192) min: 3761405335071438130, max: 3546644317281268531, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 0, seg#: 3, HWM: 0; status: unavail
1052672 - 1060863 (8192) min: 3546644317281268531, max: 3904961971207352628, seqNum: 1, state: valid, fbo: 8192, DBRoot: 1, part#: 0, seg#: 0, HWM: 16383; status: avail
1181696 - 1189887 (8192) min: 3977019565245280564, max: 3689626999773671477, seqNum: 1, state: valid, fbo: 8192, DBRoot: 1, part#: 0, seg#: 1, HWM: 16383; status: avail
1310720 - 1318911 (8192) min: 3689626999773671477, max: 3834871399159642165, seqNum: 1, state: valid, fbo: 8192, DBRoot: 1, part#: 0, seg#: 2, HWM: 16383; status: avail
1439744 - 1447935 (8192) min: 49, max: 58511299524921, seqNum: 2, state: valid, fbo: 8192, DBRoot: 1, part#: 0, seg#: 3, HWM: 16383; status: avail
1568768 - 1576959 (8192) min: 3472328296227680305, max: 14978892678379833, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 1, seg#: 0, HWM: 0; status: unavail
1714176 - 1722367 (8192) min: 3760559784776250673, max: 4120855443108541234, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 1, seg#: 1, HWM: 0; status: unavail
1859584 - 1867775 (8192) min: 4120855443108541234, max: 3905528202582438451, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 1, seg#: 2, HWM: 0; status: unavail
2004992 - 2013183 (8192) min: 3905528202582438451, max: 3833468426684608564, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 1, seg#: 3, HWM: 8191; status: avail
2150400 - 2158591 (8192) min: 3833468426684608564, max: 4122255138539714868, seqNum: 1, state: valid, fbo: 8192, DBRoot: 1, part#: 1, seg#: 0, HWM: 16383; status: avail
2279424 - 2287615 (8192) min: 3762811593280207156, max: 4049356435269891893, seqNum: 1, state: valid, fbo: 8192, DBRoot: 1, part#: 1, seg#: 1, HWM: 16383; status: avail
2408448 - 2416639 (8192) min: 4121414029307819829, max: 3472328296227680310, seqNum: 1, state: valid, fbo: 8192, DBRoot: 1, part#: 1, seg#: 2, HWM: 10676; status: avail

MariaDB [mytest]> select calsettrace(1);
----------------

calsettrace(1)

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

0

----------------
1 row in set (0.001 sec)

MariaDB [mytest]> select count(l_orderkey) from lineitem where l_orderkey >= '12000000';
-------------------

count(l_orderkey)

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

115530374

-------------------
1 row in set, 1 warning (6.601 sec)

MariaDB [mytest]> select calGetStats();
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

calGetStats()

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

Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-117173; CacheI/O-117173; BlocksTouched-117173; PartitionBlocksEliminated-0; MsgBytesIn-2MB; MsgBytesOut-15KB; Mode-Distributed

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.001 sec)

MariaDB [mytest]> select calgettrace();
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

calgettrace()

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

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
BPS PM lineitem 3037 (l_orderkey) 117173 117173 0 6.437 14647
TAS UM - - - - - - 6.338 1

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.001 sec)

5.5.2

create table lineitem (
l_orderkey char(8) CHARACTER SET latin1 COLLATE latin1_nopad_bin NOT NULL,
l_partkey int,
l_suppkey int,
l_linenumber bigint,
l_quantity decimal(12,2),
l_extendedprice decimal(12,2),
l_discount decimal(12,2),
l_tax decimal(12,2),
l_returnflag char (1),
l_linestatus char (1),
l_shipdate date,
l_commitdate date,
l_receiptdate date,
l_shipinstruct char (25),
l_shipmode char (10),
l_comment varchar (44)
) engine=Columnstore DEFAULT CHARACTER SET UTF8;

cpimport 10g lineitem twice, a total of 119972104 rows, or 16 extends

[centos8:root~]# /usr/bin/colxml mytest -t lineitem -b4|grep l_orderkey
<Column colName="l_orderkey" colOid="3058" dataType="char" compressType="2" width="8" notnull="1"/>

[centos8:root~]# /usr/bin/editem -o 3058
Col OID = 3058, NumExtents = 15, width = 8
329728 - 337919 (8192) min: 49, max: 58511299524921, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 0, seg#: 0, HWM: 0; status: avail
495616 - 503807 (8192) min: 3472328296227680305, max: 14978892678379833, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 0, seg#: 1, HWM: 0; status: unavail
643072 - 651263 (8192) min: 3762251954763806257, max: 3834870286829827378, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 0, seg#: 2, HWM: 0; status: unavail
794624 - 802815 (8192) min: 3906927880867755314, max: 4049359708035035955, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 0, seg#: 3, HWM: 0; status: unavail
946176 - 954367 (8192) min: 4049359708035035955, max: 3979272447407501620, seqNum: 1, state: valid, fbo: 8192, DBRoot: 1, part#: 0, seg#: 0, HWM: 16383; status: avail
1081344 - 1089535 (8192) min: 3979272447407501620, max: 4049917151823474741, seqNum: 1, state: valid, fbo: 8192, DBRoot: 1, part#: 0, seg#: 1, HWM: 16383; status: avail
1216512 - 1224703 (8192) min: 4121974745861402677, max: 3906368225104312373, seqNum: 1, state: valid, fbo: 8192, DBRoot: 1, part#: 0, seg#: 2, HWM: 16383; status: avail
1351680 - 1359871 (8192) min: 49, max: 58511299524921, seqNum: 2, state: valid, fbo: 8192, DBRoot: 1, part#: 0, seg#: 3, HWM: 16383; status: avail
1486848 - 1495039 (8192) min: 3472328296227680305, max: 14978892678379833, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 1, seg#: 0, HWM: 0; status: unavail
1638400 - 1646591 (8192) min: 3545512893996152113, max: 3689636912659182386, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 1, seg#: 1, HWM: 0; status: unavail
1789952 - 1798143 (8192) min: 3689636912659182386, max: 3762247539553874483, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 1, seg#: 2, HWM: 0; status: unavail
1941504 - 1949695 (8192) min: 3834305133591802419, max: 3978993180094115892, seqNum: 1, state: valid, fbo: 0, DBRoot: 1, part#: 1, seg#: 3, HWM: 8191; status: avail
2093056 - 2101247 (8192) min: 4051050774132043828, max: 4049918259975174452, seqNum: 1, state: valid, fbo: 8192, DBRoot: 1, part#: 1, seg#: 0, HWM: 16383; status: avail
2228224 - 2236415 (8192) min: 4121975854013102388, max: 3762817099428542261, seqNum: 1, state: valid, fbo: 8192, DBRoot: 1, part#: 1, seg#: 1, HWM: 16383; status: avail
2363392 - 2371583 (8192) min: 3834874693466470197, max: 3472328296227680310, seqNum: 1, state: valid, fbo: 8192, DBRoot: 1, part#: 1, seg#: 2, HWM: 10668; status: avail

MariaDB [mytest]> select calsettrace(1);
----------------

calsettrace(1)

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

0

----------------
1 row in set (0.001 sec)

MariaDB [mytest]> select count(l_orderkey) from lineitem where l_orderkey >= '12000000';
-------------------

count(l_orderkey)

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

115530374

-------------------
1 row in set, 1 warning (5.564 sec)

MariaDB [mytest]> select calGetStats();
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

calGetStats()

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

Query Stats: MaxMemPct-0; NumTempFiles-0; TempFileSpace-0B; ApproxPhyI/O-117165; CacheI/O-117165; BlocksTouched-117165; PartitionBlocksEliminated-0; MsgBytesIn-2MB; MsgBytesOut-15KB; Mode-Distributed

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.001 sec)

MariaDB [mytest]> select calgettrace();
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

calgettrace()

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

Desc Mode Table TableOID ReferencedColumns PIO LIO PBE Elapsed Rows
BPS PM lineitem 3057 (l_orderkey) 117165 117165 0 5.365 14646
TAS UM - - - - - - 5.327 1
TNS UM - - - - - - 0.000 1

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.001 sec)

Comment by Daniel Lee (Inactive) [ 2021-02-18 ]

Build verified: 5.5.2-1 (Drone builds 1681)

Ran the same test for 5.5.2 on 6.1.1 and got similar result.

MariaDB [mytest]> select count(l_orderkey) from lineitem where l_orderkey >= '12000000';
-------------------

count(l_orderkey)

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

115530374

-------------------
1 row in set, 1 warning (6.146 sec)

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