Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-6239

Partition pruning is not working as expected in an inner query

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5.37, 10.0.11
    • 5.5.38, 10.0.12
    • 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

          Activity

            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'.

            psergei Sergei Petrunia added a comment - 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'.

            The fix for the partition pruning problem has been pushed into the 5.5 tree.

            psergei Sergei Petrunia added a comment - The fix for the partition pruning problem has been pushed into the 5.5 tree.

            As for possible semi-join optimization problems, lets discuss those in another MDEV. I've created MDEV-6279.

            psergei Sergei Petrunia added a comment - As for possible semi-join optimization problems, lets discuss those in another MDEV. I've created MDEV-6279 .

            Closing this issue (since there was a specific problem in partition pruning which was fixed and pushed). If that's not the only issue, let's discuss in MDEV-6279, and also feel free to open more issues.

            psergei Sergei Petrunia added a comment - Closing this issue (since there was a specific problem in partition pruning which was fixed and pushed). If that's not the only issue, let's discuss in MDEV-6279 , and also feel free to open more issues.

            Can you please confirm that the fix for MDEV-6239 is available 10.0.12, couldn't find this in the change log of the release (https://mariadb.com/kb/en/mariadb-10012-changelog/).

            Sivaram_d Sivaram Dandibhotla added a comment - Can you please confirm that the fix for MDEV-6239 is available 10.0.12, couldn't find this in the change log of the release ( https://mariadb.com/kb/en/mariadb-10012-changelog/ ).

            People

              psergei Sergei Petrunia
              Sivaram_d Sivaram Dandibhotla
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.