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

Add basic optimizer support for virtual columns

Details

    Description

      This MDEV covers basic optimizer support for virtual columns added in 11.8.

      If a user declares a virtual column and an index on it

      ALTER TABLE t1
        ADD COLUMN vcol INT AS (col1+1),
        ADD INDEX idx1(vcol);
      

      and then a query uses the exact same expression:

      SELECT * FROM t1 WHERE col1+1 <= 100
      

      Then the optimizer will rewrite the above into

      ... WHERE vcol <= 100
      

      which enables the rest of the optimizer to construct query plans that use index idx1.

      The rewrite is performed in WHERE/ON clauses (and only there).
      The rewrite targets indexed virtual columns.
      The rewrite targets a subset of conditions usable by the range optimizer:

      vcol_expr $CMP const1 where $CMP is = or [less|greater][or equal].
      vcol_expr BETWEEN const1 AND const2
      vcol_expr IN (const-list)
      vcol_expr IS [NOT] NULL
      

      Attachments

        Issue Links

          Activity

            The commit is in bb-11.8-MDEV-6017-variant3 branch:

            commit 75773e1d28877d7f3c4599b554921c472adcc6bb (HEAD -> bb-11.8-MDEV-6017-variant3, origin/bb-11.8-MDEV-6017-variant3)
            Author: Sergei Petrunia <sergey@mariadb.com>
            Date:   Tue Nov 26 14:50:41 2024 +0200
             
                MDEV-35616: Add basic optimizer support for virtual column
                
                (Review input addressed)
                
                After this patch, the optimizer can handle virtual column expressions
                in WHERE/ON clauses. If the table has an indexed virtual column:
                
                  ALTER TABLE t1
                    ADD COLUMN vcol INT AS (col1+1),
                    ADD INDEX idx1(vcol);
                
                and the query uses the exact virtual column expression:
                
                  SELECT * FROM t1 WHERE col1+1 <= 100
                
                then the optimizer will be able use index idx1 for it.
                
                This is achieved by walking the WHERE/ON clauses and replacing instances
                of virtual column expression (like "col1+1" above) with virtual column's
                Item_field (like "vcol"). The latter can be processed by the optimizer.
                
                Replacement is considered (and done) only in items that are potentially
                usable to the range optimizer.
            

            psergei Sergei Petrunia added a comment - The commit is in bb-11.8- MDEV-6017 -variant3 branch: commit 75773e1d28877d7f3c4599b554921c472adcc6bb (HEAD -> bb-11.8-MDEV-6017-variant3, origin/bb-11.8-MDEV-6017-variant3) Author: Sergei Petrunia <sergey@mariadb.com> Date: Tue Nov 26 14:50:41 2024 +0200   MDEV-35616: Add basic optimizer support for virtual column (Review input addressed) After this patch, the optimizer can handle virtual column expressions in WHERE/ON clauses. If the table has an indexed virtual column: ALTER TABLE t1 ADD COLUMN vcol INT AS (col1+1), ADD INDEX idx1(vcol); and the query uses the exact virtual column expression: SELECT * FROM t1 WHERE col1+1 <= 100 then the optimizer will be able use index idx1 for it. This is achieved by walking the WHERE/ON clauses and replacing instances of virtual column expression (like "col1+1" above) with virtual column's Item_field (like "vcol"). The latter can be processed by the optimizer. Replacement is considered (and done) only in items that are potentially usable to the range optimizer.

            People

              lstartseva Lena Startseva
              psergei Sergei Petrunia
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.