Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.37, 10.0.11
-
None
-
None
-
Linux, MariaDB
Description
We noticed an issue with the partition pruning on Maria DB with Toku DB engine. Partition pruning doesn’t seem to work as expected for an inner query, this is illustrated with a simple example below.
Let us consider two tables employee and dept with company_id being the column on which partition is defined. When we try to fetch the list of departments for a given company_id in an inner query then all the partitions are being accessed instead of narrowing down to the partition specified in the criteria. When we run the same query as an outer query it is working fine, it is working fine on MySQL 5.6.16. Please let us know if this is a defect.
DDL and DML statements are attached in "Partition Pruning issue data setup.sql". This file also has an explain plan for a simple select statement. Please note that for the inner query all 3 partitions are being listed. Explain plan is attached in "Explain Plan for the query.csv". We are running into this issue with our complex queries, the one listed here is just for explaining the problem.
This issue is causing a huge performance problems for our system and right now it is a blocker for us to pursue MaraDB further.
Thanks for your time, please let us know if you need any more details.
Updated the ticket with the "Explain Plan for query in MySQL5.6.16.csv". Please note that in MySQL inner query access only the relevant partition.
Attachments
Issue Links
- relates to
-
MDEV-6279 Possible performance issue with semi-join (TokuDB's records_in_range)
-
- Closed
-
Debugging...
=== MariaDB 5.5 ===
in JOIN::optimize(), prune_partitions() is called only for table `employee`.
Debugging JOIN::optimize, one can see this:
List_iterator_fast<TABLE_LIST> li(select_lex->leaf_tables);
while ((tbl= li++))
{
/*
If tbl->embedding!=NULL that means that this table is in the inner
part of the nested outer join, and we can't do partition pruning
(TODO: check if this limitation can be lifted)
*/
if (!tbl->embedding)
{
Item *prune_cond= tbl->on_expr? tbl->on_expr : conds;
tbl->table->no_partitions_used= prune_partitions(thd, tbl->table,
prune_cond);
leaf_tables includes table `dept`, but dept has tbl->embedding!=NULL, so
prune_partitions() is not called.
== MySQL 5.6 ==
JOIN::optimize()/JOIN::prune_table_partitions() behave like in MariaDB (dept is
not pruned).
But, 5.6 also has "partition lock pruning", which is called from JOIN::prepare,
which is before subquery is converted into semi-join. partition lock pruning
is invoked for both tables, and it prunes away partitions in table 'dept'.