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

Updates to derived-with-keys, window functions determining records per key

    XMLWordPrintable

Details

    Description

      Enabling derived keys optimization for derived.col = const pushed conditions.

      Estimating records per key in derived key for the optimizer based on form and/or size of components of a derived table.

      Consider a derived table of the form

      SELECT ..., ROW_NUMBER ()  OVER (PARTITION BY c1,c2 order by ...)
      FROM t1, t2, t3 ...
      WHERE ...
      

      If the optimizer generates a key on this derived table because of a constraint being pushed into it, it currently will not consider key components of the form column = const

      We lift this constraint and add code to TABLE::add_tmp_key to search for a window function ROW_NUMBER() in our item list. From the partition list c1, c2 we can in infer an estimate of the number of rows we expect to see for each key value. The optimizer can then use this number to determine a better table join order.

      Attachments

        Issue Links

          Activity

            People

              psergei Sergei Petrunia
              Johnston Rex Johnston
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - 2d Original Estimate - 2d
                  2d
                  Remaining:
                  Time Spent - 3.25d Remaining Estimate - 0.5d
                  0.5d
                  Logged:
                  Time Spent - 3.25d Remaining Estimate - 0.5d
                  3.25d

                  Git Integration

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