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

Move the optimization of subqueries earlier, before make_join_select()

        Details

        • Type: Task
        • Status: Closed
        • Priority: Major
        • Resolution: Won't Fix
        • Fix Version/s: 10.0.0
        • Component/s: None
        • Labels:
          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.

            Gliffy Diagrams

              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

                  Hide
                  Permalink
                  timour Timour Katchaounov 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.

                  Show
                  timour Timour Katchaounov 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

                    • Assignee:
                      timour Timour Katchaounov
                      Reporter:
                      timour Timour Katchaounov
                    • Votes:
                      0 Vote for this issue
                      Watchers:
                      1 Start watching this issue

                      Dates

                      • Due:
                        Created:
                        Updated:
                        Resolved:

                        Time Tracking

                        Estimated:
                        Original Estimate - 1 day Original Estimate - 1 day
                        1d
                        Remaining:
                        Remaining Estimate - 0 minutes
                        0m
                        Logged:
                        Time Spent - 2 days, 1 hour
                        2d 1h