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

Move the optimization of subqueries earlier, before make_join_select()

Details

    • Task
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Won't Fix
    • 10.0.0
    • None
    • None

    Description

      Subqueries in MariaDB 5.5/5.3 are optimized recursively close to the end of JOIN::optimize through the call to JOIN::optimize_unflattened_subqueries.

      This turns out to be a problem for the implementation of MDEV-83 (Cost-based choice for the pushdown of expensive predicates to joined tables), because predicate pushdown is performed by make_join_select() which is called earlier than JOIN::optimize_unflattened_subqueries. If subqueries are optimized after make_join_select(), then their cost is unknown during predicate pushdown, and we cannot estimate if a subquery is expensive or not.

      The goal of this task is to move subquery optimization before make_join_select.

      Attachments

        Issue Links

          is part of

          Task - A task that needs to be done. MDEV-83 Cost-based choice for the pushdown of subqueries to joined tables

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Stalled

          Activity

            timour Timour Katchaounov (Inactive) added a comment -

            Further testing of the implementation revealed that:

            • if subqueries are optimized before predicate pushdown they may become "cheap" (since any non-optimized subquery is considered expensive by default)
            • once a subquery is considered cheap, it will not be pushed to the partial join with the smallest cardinality
            • this defeats the purpose of mdev-83

            In addition, later analysis of DBT3 uncovered that we must pre-optimize all subqueries already before optimizing the outer query. This is reflected in mdev-402.

            timour Timour Katchaounov (Inactive) added a comment - Further testing of the implementation revealed that: if subqueries are optimized before predicate pushdown they may become "cheap" (since any non-optimized subquery is considered expensive by default) once a subquery is considered cheap, it will not be pushed to the partial join with the smallest cardinality this defeats the purpose of mdev-83 In addition, later analysis of DBT3 uncovered that we must pre-optimize all subqueries already before optimizing the outer query. This is reflected in mdev-402.

            People

              timour Timour Katchaounov (Inactive)
              timour Timour Katchaounov (Inactive)
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.