[#MDEV-30218] Incorrect optimization for rowid

Comment by Sergei Petrunia [ 2022-12-13 ]

My poking around in the related code was logged as ~~MDEV-30142~~.

Comment by Sergei Petrunia [ 2022-12-13 ]

https://github.com/MariaDB/server/commit/87eccd78a791ff3acfa00914254f0bf858a14aa4

Comment by Sergei Golubchik [ 2022-12-27 ]

There must be a test case, proving that the new code makes queries faster

Comment by Sergei Golubchik [ 2023-01-17 ]

The following test case

--source include/have_innodb.inc

--source include/have_sequence.inc

create table t1 (p int primary key, a int, b int, unique(a),key(b)) engine=innodb;

insert into t1 select seq,seq,seq/10 from seq_1_to_100000;

analyze table t1;

explain select straight_join count(*) from t1 as t3, t1, t1 as t2 where t1.a=t2.a and t2.b between 1 and 1500;

set @now=unix_timestamp(now(6));

select straight_join count(*) from t1 as t3, t1, t1 as t2 where t1.a=t2.a and t2.b between 1 and 1500;

select unix_timestamp(now(6))-@now;

drop table t1;

becomes 15% slower after the patch

Comment by Sergei Petrunia [ 2023-01-24 ]

+------+-------------+-------+---------------+---------------+------+---------+-----------+---------+----------+--------------------------------------------------------------+

| id   | select_type | table | type          | possible_keys | key  | key_len | ref       | rows    | filtered | Extra                                                        |

+------+-------------+-------+---------------+---------------+------+---------+-----------+---------+----------+--------------------------------------------------------------+

|    1 | SIMPLE      | t3    | index         | NULL          | a    | 5       | NULL      | 100256  |   100.00 | Using index                                                  |

|    1 | SIMPLE      | t1    | range         | a             | a    | 5       | NULL      | 50128   |   100.00 | Using where; Using index; Using join buffer (flat, BNL join) |

|    1 | SIMPLE      | t2    | eq_ref|filter | a,b           | a|b  | 5|5     | test.t1.a | 1 (28%) |    27.86 | Using where; Using rowid filter                              |

+------+-------------+-------+---------------+---------------+------+---------+-----------+---------+----------+--------------------------------------------------------------+

Comment by Sergei Petrunia [ 2023-01-25 ]

A testcase like just like the above one from serg where original code (before the patch for this MDEV) will not use rowid filter, despite that the query plan is chooses is the same in all other aspects:
(This demonstrates a bug that eq_ref code uses the value of keyread_tmp that is left over from the previously considered index, whatever it was.)

create table t1 (

  p int primary key,

  badcol1 int,

  badcol2 int,

  a int,

  b int,

  unique(badcol1, badcol2),

  unique(a),

  key(b)

) engine=innodb;

insert into t1 select seq, (seq/30000), seq, seq,seq/10 from seq_1_to_100000;

analyze table t1;

explain

select straight_join count(*)

from

  t1 as t3, t1, t1 as t2

where

  t1.a=t2.a and

  t2.b between 1 and 1500 and

  t1.badcol1=t2.badcol1 ;

Comment by Sergei Petrunia [ 2023-01-26 ]

Part#2 patch:
https://github.com/MariaDB/server/commit/678f2220cc25b3495f1f16fe2732e49a0cd120f8

The change in subselect2.result looks odd. I've investigated - it has nothing
to do with rowid filtering code. The ref|filter access was estimated as eq_ref|filter (and it should have been eq_ref|filter). Filed MDEV-30476 with all the details.

Comment by Oleksandr Byelkin [ 2023-02-16 ]

I pushed it to 10.4

[MDEV-30218] Incorrect optimization for rowid_filtering Created: 2022-12-13 Updated: 2023-04-26 Resolved: 2023-02-16
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.4, 10.5, 10.6, 10.7, 10.8
Fix Version/s:	10.11.3, 10.4.29, 10.5.20, 10.6.13, 10.7.8, 10.8.8, 10.9.6, 10.10.4

[MDEV-30218] Incorrect optimization for rowid_filtering Created: 2022-12-13 Updated: 2023-04-26 Resolved: 2023-02-16