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.

            The testcases in the patch use JSON_EXTRACT() and JSON_UNQUOTE when necessary. TODO: also add test coverage with JSON_VALUE().

            psergei Sergei Petrunia added a comment - The testcases in the patch use JSON_EXTRACT() and JSON_UNQUOTE when necessary. TODO: also add test coverage with JSON_VALUE().
            psergei Sergei Petrunia added a comment - - edited

            Hit this with testing: for indexes on virtual columns, index-only scans are supported: https://jira.mariadb.org/browse/MDEV-8326?focusedCommentId=297240&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-297240

            but Index Condition Pushdown is intentionally disabled in InnoDB (although it can work, and does work with MyISAM).
            It's the same in MySQL 8.4.3.

            ha_innodb.cc has this comment (which makes little sense to me):

            	/* MDEV-31154: For pushed down index condition we don't support virtual
            	column and idx_cond_push() does check for it. For row ID filtering we
            	don't need such restrictions but we get into trouble trying to use the
            	ICP path.
             
            	1. It should be fine to follow no_icp path if primary key is generated.
            	However, with user specified primary key(PK), the row is identified by
            	the PK and those columns need to be converted to mysql format in
            	row_search_idx_cond_check before doing the comparison. Since secondary
            	indexes always have PK appended in innodb, it works with current ICP
            	handling code when fetch_primary_key_cols is set to TRUE.
             
            	2. Although ICP comparison and Row ID comparison works on different
            	columns the current ICP code can be shared by both.
             
            	3. In most cases, it works today by jumping to goto no_icp when we
            	encounter a virtual column. This is hackish and already have some
            	issues as it cannot handle PK and all states are not reset properly,
            	for example, idx_cond_n_cols is not reset.
             
            	4. We already encountered MDEV-28747 m_prebuilt->idx_cond was being set.
             
            	Neither ICP nor row ID comparison needs virtual columns and the code is
            	simplified to handle both. It should handle the issues. */
            

            Fundamentally, there should be no problem to enable ICP for indexes
            with vcols. Index-only scans work, so one can unpack the VCOL_FIELD from index entry into its field.

            psergei Sergei Petrunia added a comment - - edited Hit this with testing: for indexes on virtual columns, index-only scans are supported: https://jira.mariadb.org/browse/MDEV-8326?focusedCommentId=297240&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-297240 but Index Condition Pushdown is intentionally disabled in InnoDB (although it can work, and does work with MyISAM). It's the same in MySQL 8.4.3. ha_innodb.cc has this comment (which makes little sense to me): /* MDEV-31154: For pushed down index condition we don't support virtual column and idx_cond_push() does check for it. For row ID filtering we don't need such restrictions but we get into trouble trying to use the ICP path.   1. It should be fine to follow no_icp path if primary key is generated. However, with user specified primary key(PK), the row is identified by the PK and those columns need to be converted to mysql format in row_search_idx_cond_check before doing the comparison. Since secondary indexes always have PK appended in innodb, it works with current ICP handling code when fetch_primary_key_cols is set to TRUE.   2. Although ICP comparison and Row ID comparison works on different columns the current ICP code can be shared by both.   3. In most cases, it works today by jumping to goto no_icp when we encounter a virtual column. This is hackish and already have some issues as it cannot handle PK and all states are not reset properly, for example, idx_cond_n_cols is not reset.   4. We already encountered MDEV-28747 m_prebuilt->idx_cond was being set.   Neither ICP nor row ID comparison needs virtual columns and the code is simplified to handle both. It should handle the issues. */ Fundamentally, there should be no problem to enable ICP for indexes with vcols. Index-only scans work, so one can unpack the VCOL_FIELD from index entry into its field.
            alice Alice Sherepa added a comment -

            bb-11.8-MDEV-6017-variant3 4119c0185cb0559b9fde397f4892c2ab01fbf8e2 is ok to push into 11.8

            alice Alice Sherepa added a comment - bb-11.8- MDEV-6017 -variant3 4119c0185cb0559b9fde397f4892c2ab01fbf8e2 is ok to push into 11.8
            psergei Sergei Petrunia added a comment - Documentation, work in progress: https://mariadb.com/kb/en/virtual-column-support-in-the-optimizer/

            People

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