WL#7231: Support ICP for partitioned tables
Status: Complete — Priority: Medium
Partitioned tables do not support Index Condition Pushdown (ICP). ICP was implemented in WL#2475, but never for ha_partition. So this worklog will implement ICP for partitioned tables (if the underlying engine supports it). Related bug reports: BUG#70001/Bug#17306882: PARTITIONED TABLES DO NOT USE ICP - SEVERE PERFORMANCE LOSS AFTER PARTITIONING
Functional requirements: F1) Index condition pushdown will be supported for partitioned tables when the underlying storage engines support it. F2) Traditional Explain: When Index condition pushdown is used for partitioned tables it will be visible in the "Extra" column as "Using index condition". F3) Explain JSON: When Index condition pushdown is used for partitioned tables the pushed index condition will be written in the "index_condition" field. Non-functional requirements: NF1) Performance: When index condition pushdown is not used for a partitioned table there should be no performance degradation. NF2) Performance: When index condition pushdown is used for a partitioned table the performance should either be the same or improved depending on how many of the records get filtered out by the underlying storage engine.
ICP uses an Item tree that assumes the record is stored in table->record, which means ha_index* reads must be read to that buffer (or Field::move_field_offset()/set_key_field_ptr()). Other than that just forward the idx_cond_push() call to all non-pruned partitions. Error handling: Current implementation in ha_myisam and ha_innobase takes the full Item tree or nothing. So to avoid complex handling only support full pushdown or no pushdown. If one partition fails to accept push down, then cancel the push down for the already pushed partitions and then return the full expression from ha_partition::idx_cond_push(). As a possible extension, I've added code to do an extra pruning try to see if the pushed condition would make it possible to prune away even more partitions. I have not yet found a query where it helps, but I've added DBUG_ASSERTS which will be triggered when such query is executed. If no such queries is found during test and QA I will remove that code.
See attached patch.
Copyright (c) 2000, 2017, Oracle Corporation and/or its affiliates. All rights reserved.