Index-only scan evaluates generated column expressions for every row when the index contains their base columns (regression after MDEV-36132)

Since 12.1.1, an index that contains all base columns of a generated column is treated as covering that column (MDEV-36132, commit 8cdee25952763a0401e4c2a4d61e92c13499bdc6). Two side effects make index-only scans much slower:

1. An index-only scan over such an index evaluates the generated column expression for every row read, even when the query does not reference the column (for example SELECT COUNT(*)). This happens for STORED columns too, whose values are already in the row.
2. The optimizer prefers such an index over an index on the generated column itself, without accounting for the cost of evaluating the expression.

With an expensive expression (REGEXP_REPLACE) a COUNT(*) over 300k rows becomes 4.5x slower. On a production table (295k rows, five STORED columns derived from one VARCHAR(8) column, 25 secondary indexes) COUNT(*) through any index that contains the base column went from 26-33 ms on 12.0.2 to 265-388 ms on 12.1.2 to 12.3.3. A full scan through PRIMARY is unaffected (26 ms).

No optimizer_switch flag or new_mode value turns this off in 12.3.3.

How to repeat:

Part 1, unused generated column:

CREATE TABLE t_gen (

  id INT UNSIGNED NOT NULL PRIMARY KEY,

  k  INT UNSIGNED NOT NULL,

  s  VARCHAR(8) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,

  g  VARCHAR(8) CHARACTER SET ascii COLLATE ascii_bin

     GENERATED ALWAYS AS (REGEXP_REPLACE(s, '[^0-9]', '')) STORED,

  KEY k_s (k, s),

  KEY k_only (k)

) ENGINE=InnoDB;

INSERT INTO t_gen (id, k, s)

  SELECT seq, seq MOD 10, CONCAT('AB', LPAD(seq MOD 10000, 4, '0'), 'CD') FROM seq_1_to_300000;

ANALYZE TABLE t_gen;

ANALYZE FORMAT=JSON SELECT COUNT(*) FROM t_gen FORCE INDEX (k_s);     -- slow on 12.1+

ANALYZE FORMAT=JSON SELECT COUNT(*) FROM t_gen FORCE INDEX (k_only);  -- fast everywhere

Control tables, same data and keys:

• t_plain: no generated column;
• t_cheap: g AS (SUBSTRING(s, 3, 4)) STORED;
• t_virtual: g AS (REGEXP_REPLACE(s, '[^0-9]', '')) VIRTUAL.

Part 2, plan choice:

CREATE TABLE t_choice (

  id  INT UNSIGNED NOT NULL PRIMARY KEY,

  k   INT UNSIGNED NOT NULL,

  s   VARCHAR(8) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,

  pad CHAR(16) CHARACTER SET ascii COLLATE ascii_bin NOT NULL DEFAULT '',

  g   VARCHAR(8) CHARACTER SET ascii COLLATE ascii_bin

      GENERATED ALWAYS AS (REGEXP_REPLACE(s, '[^0-9]', '')) STORED,

  KEY k_s (k, s),

  KEY g_pad (g, pad)

) ENGINE=InnoDB;

INSERT INTO t_choice (id, k, s)

  SELECT seq, seq MOD 10, CONCAT('AB', LPAD(seq MOD 10000, 4, '0'), 'CD') FROM seq_1_to_300000;

ANALYZE TABLE t_choice;

ANALYZE FORMAT=JSON SELECT COUNT(*) FROM t_choice WHERE g LIKE '%77%';

-- 11.8.9 / 12.0.2: index g_pad; 12.1+: index k_s, g evaluated from s for every row

Results: official Docker images (ubu2404), same host, --innodb-buffer-pool-size=1024M. Values are r_total_time_ms from ANALYZE FORMAT=JSON, in ms, median of 5 runs after a warm-up.

Expected result:

• An index-only scan evaluates a generated column only if the query needs its value.
• A STORED generated column is not re-evaluated on read.
• When the optimizer considers computing a generated column from base columns in an index, the cost of evaluating the expression is part of the plan cost.

Workaround: make the expression also reference a column that is not part of the index. For example, CONCAT_WS('', <expr>, LEFT(other_col, 0)) keeps the value, the index no longer covers the generated column, and the 12.0 timings come back.

Related: MDEV-36132, MDEV-35615, MDEV-38752, MDEV-39361.