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
ALTERTABLE t1
ADDCOLUMN vcol INTAS (col1+1),
ADDINDEX 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
blocks
MXS-5346Properly support [create|drop|list]Indexes
Closed
causes
MDEV-35833Assertion `marked_for_read()' failed after DELETE/UPDATE from table with virtual columns
Closed
MDEV-35913Assertion `m_comparator.cmp_type() != ROW_RESULT' failed in bool Item_func_in::compatible_types_scalar_bisection_possible()
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.
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().
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().
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.
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.
New Feature
Critical
MDEV-35833 Assertion `marked_for_read()' failed after DELETE/UPDATE from table with virtual columns
MDEV-6017 Add support for Indexes on Expressions
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 +0200MDEV-35616: Add basic optimizer support for virtual column(Review input addressed)After this patch, the optimizer can handle virtual column expressionsin WHERE/ON clauses. If the table has an indexed virtual column:ALTER TABLE t1ADD 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 <= 100then the optimizer will be able use index idx1 for it.This is achieved by walking the WHERE/ON clauses and replacing instancesof virtual column expression (like "col1+1" above) with virtual column'sItem_field (like "vcol"). The latter can be processed by the optimizer.Replacement is considered (and done) only in items that are potentiallyusable to the range optimizer.