Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-8306 Complete cost-based optimization for ORDER BY with LIMIT
  3. MDEV-19853

Heuristic pruning prunes partial plans that could be used sorting

    XMLWordPrintable

Details

    • Technical task
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • None
    • 11.6
    • Optimizer
    • None

    Description

      Currently in the code where we pick the best join order (the funtion is best_extension_by_limited_search), there are ways in which we do pruning so that we don't need to go through all the possible combinations of join order. There are 2 cases currently

      1) Partial plan pruned by the cost of the best plan

      2) Partial plan is pruned by another partial plan at the same level (this happens when we set optimizer_prune_level =1).

      Case 1 is not a problem when we consider the join order that takes case of the ordering
      Case 2 is a problem because the partial plan got pruned but maybe after the ordering is achieved and limit would shortcut the rest of the execution we could get a better plan, the cases are not considered.

      An example would be
      Tables: t1,t2,t3
      For sorting you need t1,t3

      let say
      t1,t2 pruned t1,t3 by case 2, then we would not be able to consider the case of putting a nest around t1,t3. that is order_nest<t1,t3>, t2 would not be considered.

      Attachments

        Activity

          People

            psergei Sergei Petrunia
            varun Varun Gupta (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

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