maria-developers team mailing list archive

[Sergey Petrunia <sergey@xxxxxxxxxxx>]

Hello,

Just taking notes from the optimizer call:

Check out this branch: 10.4-mdev30218-print-negative-gain (git log -3
output below)

It has:
- 10.4-release contents
- then mdev30218_bb-10.4-release.diff patch provided by Igor
- then a debug patch by me.

I run:
./mtr --mem --parallel=5 --force main/rowid_filter*test 
grep -rn filter_gain var/

I get this: negative gains from filters:

var/4/log/mysqld.1.err:52:filter_gain:-0.0569276, query=select 1
var/4/log/mysqld.1.err:58:filter_gain:-0.0569276, query=explain extended select 1
var/4/log/mysqld.1.err:64:filter_gain:-0.250816, query=SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3)
var/4/log/mysqld.1.err:65:filter_gain:-0.250816, query=EXPLAIN EXTENDED
var/4/log/mysqld.1.err:67:filter_gain:-10.207, query=explain
var/4/log/mysqld.1.err:69:filter_gain:-10.207, query=SELECT * FROM t1 WHERE a > 0 AND b=0
var/4/log/mysqld.1.err:70:filter_gain:-1.17502, query=select count(*) from t1 where a between 21 and 30 and b=2
var/4/log/mysqld.1.err:71:filter_gain:-1.17502, query=explain extended select count(*) from t1 where a between 21 and 30 and b=2
var/4/log/mysqld.1.err:72:filter_gain:-1.17502, query=select * from t1 where a between 21 and 30 and b=2
var/4/log/mysqld.1.err:73:filter_gain:-2.18259, query=SELECT * FROM t1
var/4/log/mysqld.1.err:77:filter_gain:-2.18259, query=EXPLAIN EXTENDED SELECT * FROM t1
var/4/log/mysqld.1.err:81:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for explain extended select t.id, fi.*
var/4/log/mysqld.1.err:90:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for select t.id, fi.*
var/4/log/mysqld.1.err:99:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for explain extended select t.id, fi.*
var/4/log/mysqld.1.err:108:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for select t.id, fi.*
var/4/log/mysqld.1.err:117:filter_gain:-0.151308, query=set statement optimizer_switch='rowid_filter=on' for analyze format=json select t.id, fi.*
var/3/log/mysqld.1.err:51:filter_gain:-0.0569276, query=select 1
var/3/log/mysqld.1.err:57:filter_gain:-0.0569276, query=explain extended select 1
var/3/log/mysqld.1.err:63:filter_gain:-0.250816, query=SELECT * FROM t1 HAVING (7, 9) IN (SELECT t2.i1, t2.i2 FROM t2 WHERE t2.i1 = 3)
var/3/log/mysqld.1.err:64:filter_gain:-0.250816, query=EXPLAIN EXTENDED
var/3/log/mysqld.1.err:66:filter_gain:-12.7213, query=explain
var/3/log/mysqld.1.err:68:filter_gain:-12.7213, query=SELECT * FROM t1 WHERE a > 0 AND b=0



=== git log -3 output ===

commit 82d5977b9fd6be5f5152d6831cd744fc739a2f24 (HEAD -> 10.4-mdev30218-print-negative-gain, origin/10.4-mdev30218-print-negative-gain, bb-10.4-release)
Author: Sergei Petrunia <sergey@xxxxxxxxxxx>
Date:   Tue Jan 31 20:11:17 2023 +0300

    Debug patch: print filter_gain if it is less than 0.

commit 47a40d90627dcf3c870cecd33c345c2e224986af
Author: Sergei Petrunia <sergey@xxxxxxxxxxx>
Date:   Tue Jan 31 20:07:02 2023 +0300

    Apply mdev30218_bb-10.4-release.diff

commit c8f2e9a5c0ac5905f28b050b7df5a9ffd914b7e7 (origin/bb-10.4-release)
Author: Oleksandr Byelkin <sanja@xxxxxxxxxxx>
Date:   Mon Jan 30 11:49:42 2023 +0100

    Fix number of rows passing in case of EQ_REF


BR
 Sergei
-- 
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://petrunia.net