Bug #106039 Skip using priority queue and visiting unused partitions when single partition
Submitted: 5 Jan 2022 7:08 Modified: 11 Jan 2022 5:19
Reporter: Chen Wang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Partitions Severity:S5 (Performance)
Version:8.0.27 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution, partition table

[5 Jan 2022 7:08] Chen Wang
Description:
When there is only single partition left after pruning or assigned in a query, we may not need to priority queue or not visit unused partitions. This can improve the performance for the table with large number of partitions.

@@ -2227,10 +2240,9 @@ int Partition_helper::partition_scan_set_up(uchar *buf, bool idx_read_flag) {
     get_partition_set(m_table, buf, m_handler->active_index, &m_start_key,
                       &m_part_spec);
   else {
    // TODO: set to get_first_used_part() instead!
    m_part_spec.start_part = 0;
    // TODO: Implement bitmap_get_last_set() and use that here!
    m_part_spec.end_part = m_tot_parts - 1;

How to repeat:
let $maxrows=65535;

create table t2 (a int not null, primary key(a)) engine=innodb
partition by hash (a) partitions 8192;
show create table t2;
let $count= $maxrows;
--echo $count inserts;
--disable_query_log
while ($count)
{
eval insert into t2 values ($count);
dec $count;
}
--enable_query_log
select count(*) from t2;
select count(*) from t2 partition (p0);
select count(*) from t2 partition (p10);
select count(*) from t2 partition (p100);
select count(*) from t2 partition (p1000);
select count(*) from t2 partition (p4000);
select count(*) from t2 partition (p8000);
select count(*) from t2 partition (p8191);

Suggested fix:
I have written a simple patch for this enhancement. Hope it can help. Thanks a lot!
[5 Jan 2022 7:09] Chen Wang
patch for partition enhancement

Attachment: patch.txt (text/plain), 9.66 KiB.

[5 Jan 2022 8:18] MySQL Verification Team
Hello Chen Wang,

Thank you very much for the report and your patch contribution, we appreciate it!

In order for us to continue the process of reviewing your contribution to MySQL, please send us a signed copy of the Oracle Contributor Agreement (OCA) as outlined in https://oca.opensource.oracle.com

Signing an OCA needs to be done only once and it's valid for all other Oracle governed Open Source projects as well.

Getting a signed/approved OCA on file will help us facilitate your contribution - this one, and others in the future.  

Please let me know, if you have any questions.

regards,
Umesh
[5 Jan 2022 11:17] Chen Wang
Ok, sure, I've sent the request and will add the agreement when ready.

Thanks a lot!
[11 Jan 2022 5:19] Chen Wang
Dear Umesh,

I have requested the the Oracle Contributor Agreement (OCA) by email and been added to the list of contributors for the existing Alibaba Group agreement.

Please help check it, thanks a lot!
[11 Jan 2022 6:10] MySQL Verification Team
Hello Chen Wang,

Please note that usually it takes sometime to reflect OCA status but I'll follow up with the community team on this and let you know if anything further action needed from your end. Thank you.

regards,
Umesh