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

Partition pruning is not working as expected in an inner query

    XMLWordPrintable

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

            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.