[MDEV-23513] slower query execution plan selected compared to older versions Created: 2020-08-19  Updated: 2023-12-05

Status: Stalled
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.5
Fix Version/s: 10.5

Type: Bug Priority: Major
Reporter: Zoltán Szalai (Inactive) Assignee: Sergei Petrunia
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows 10

Attachments: PNG File 10.4-explain.png     PNG File 10.5-explain.png     PNG File explain-10.5_force_index.png     File trace-MariaDB-10.4.6.json     File trace-MariaDB-10.5.5.json     File trace-MySQL-5.7.19.json    

 Description   

MariaDB 10.4.0 and MySQL 5.7.19 uses eq_ref access , where MariaDB 10.5.5 uses ref access with a different(slower) query plan and a different key.
Query execution time is around 47 msec (10.4.0) vs. 204 msec (10.5.5) on my test system.

Query:
explain
select distinct hidref, workerid, 2 as db
from
(select distinct idworker from shifts WHERE ( ('2020-08-12' between shifts.startdate and shifts.enddate) AND ((shifts.lineID) = 11) ) ) as shifts
inner join
( select workerid, hidref from attendreg
where ( (attendreg.expired is Null) OR (attendreg.expired >= '2020-08-12') ) AND ((attendreg.attdate)<='2020-08-12') AND ((attendreg.idattendreg)>=294928)
) as attendreg
on attendreg.workerid = shifts.idworker
inner join
docchange on docchange.hid = attendreg.hidref
WHERE ( ((docchange.hstat)=1) AND ((docchange.hid)>=5838) ) ;

Explain Output: (eq vs. eq_ref difference with docchange table)
10.5.5
[

{ "id" : 1, "select_type" : "PRIMARY", "table" : "docchange", "type" : "ref", "possible_keys" : "PRIMARY,statdid,hstat_idx", "key" : "statdid", "key_len" : "4", "ref" : "const", "rows" : "1255", "Extra" : "Using where; Using index; Using temporary" }

,

{ "id" : 1, "select_type" : "PRIMARY", "table" : "attendreg", "type" : "ref", "possible_keys" : "PRIMARY,hidworker_idx,workerkey_idx,cover_idx", "key" : "hidworker_idx", "key_len" : "4", "ref" : "mesterlista.docchange.HID", "rows" : "44", "Extra" : "Using index condition; Using where" }

,

{ "id" : 1, "select_type" : "PRIMARY", "table" : "<derived2>", "type" : "ref", "possible_keys" : "key0", "key" : "key0", "key_len" : "4", "ref" : "mesterlista.attendreg.workerid", "rows" : "1", "Extra" : "Distinct" }

,

{ "id" : 2, "select_type" : "DERIVED", "table" : "shifts", "type" : "ref", "possible_keys" : "lineidkey_idx,groupline", "key" : "lineidkey_idx", "key_len" : "4", "ref" : "const", "rows" : "241", "Extra" : "Using where; Using temporary" }

]

10.4.0
[

{ "id" : 1, "select_type" : "PRIMARY", "table" : "<derived2>", "type" : "ALL", "possible_keys" : NULL, "key" : NULL, "key_len" : NULL, "ref" : NULL, "rows" : 241, "Extra" : "Using temporary" }

,

{ "id" : 1, "select_type" : "PRIMARY", "table" : "attendreg", "type" : "ref", "possible_keys" : "PRIMARY,hidworker_idx,workerkey_idx,cover_idx", "key" : "workerkey_idx", "key_len" : "4", "ref" : "shifts.idworker", "rows" : 121, "Extra" : "Using index condition; Using where" }

,

{ "id" : 1, "select_type" : "PRIMARY", "table" : "docchange", "type" : "eq_ref", "possible_keys" : "PRIMARY,statdid,hstat_idx", "key" : "PRIMARY", "key_len" : "4", "ref" : "mesterlista.attendreg.hidref", "rows" : 1, "Extra" : "Using where; Distinct" }

,

{ "id" : 2, "select_type" : "DERIVED", "table" : "shifts", "type" : "ref", "possible_keys" : "lineidkey_idx,groupline", "key" : "lineidkey_idx", "key_len" : "4", "ref" : "const", "rows" : 241, "Extra" : "Using where; Using temporary" }

]

And the tables involved:
CREATE TABLE `attendreg` (
`idattendreg` int(10) unsigned NOT NULL AUTO_INCREMENT,
`workerid` int(10) unsigned NOT NULL,
`hidref` int(10) unsigned NOT NULL,
`attdate` date NOT NULL,
`expired` date DEFAULT NULL,
`editor` text COLLATE utf8_unicode_ci DEFAULT NULL,
`mystamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`idattendreg`),
KEY `hidworker_idx` (`hidref`,`workerid`),
KEY `workerkey_idx` (`workerid`),
KEY `cover_idx` (`workerid`,`hidref`,`attdate`),
CONSTRAINT `hidkey` FOREIGN KEY (`hidref`) REFERENCES `docchange` (`HID`),
CONSTRAINT `workerkey` FOREIGN KEY (`workerid`) REFERENCES `t_workers` (`boschid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=450517 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `docchange` (
`HID` int(10) unsigned NOT NULL AUTO_INCREMENT,
`DID` int(10) unsigned NOT NULL,
`maker` int(10) unsigned NOT NULL,
`dep` int(10) unsigned NOT NULL,
`hdate_begin` date NOT NULL,
`hdate_end` date NOT NULL,
`chdate` date NOT NULL,
`doknr` varchar(255) CHARACTER SET utf8 NOT NULL,
`dokcm` varchar(255) CHARACTER SET utf8 NOT NULL,
`hrem` text COLLATE utf8_unicode_ci DEFAULT NULL,
`link` text CHARACTER SET utf8 DEFAULT NULL,
`hstat` int(10) unsigned NOT NULL,
`doktype` int(10) unsigned NOT NULL,
`hdate_milestone` date NOT NULL,
PRIMARY KEY (`HID`),
KEY `hdate_end` (`hdate_end`),
KEY `dokcm` (`dokcm`),
KEY `dokid` (`doknr`),
KEY `hdate_begin` (`hdate_begin`),
KEY `hdate_milestone` (`hdate_milestone`),
KEY `chdate` (`chdate`),
KEY `T_doctypeDocChange` (`doktype`),
KEY `T_depDocChange` (`dep`),
KEY `T_emailsDocChange` (`maker`),
KEY `statdid` (`hstat`,`DID`),
KEY `DID_idx` (`DID`),
KEY `hstat_idx` (`hstat`),
CONSTRAINT `docchange-dochistory` FOREIGN KEY (`DID`) REFERENCES `dochistory` (`ID`),
CONSTRAINT `docchange-t_dep` FOREIGN KEY (`dep`) REFERENCES `t_dep` (`dep_id`),
CONSTRAINT `docchange-t_doctype` FOREIGN KEY (`doktype`) REFERENCES `t_doctype` (`type_id`),
CONSTRAINT `docchange-t_emails` FOREIGN KEY (`maker`) REFERENCES `t_emails` (`userid`),
CONSTRAINT `docchange-t_hstat` FOREIGN KEY (`hstat`) REFERENCES `t_hstat` (`hstat_id`)
) ENGINE=InnoDB AUTO_INCREMENT=11519 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE `shifts` (
`idshifts` int(10) unsigned NOT NULL AUTO_INCREMENT,
`lineid` int(10) unsigned NOT NULL,
`team` tinyint(1) NOT NULL,
`idworker` int(10) unsigned NOT NULL,
`startdate` date NOT NULL,
`enddate` date NOT NULL,
`groupid` int(10) unsigned NOT NULL,
`xplvl` tinyint(1) unsigned NOT NULL DEFAULT 1,
`editor` text COLLATE utf8_unicode_ci DEFAULT NULL,
`mystamp` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
PRIMARY KEY (`idshifts`),
KEY `workeridkey_idx` (`idworker`),
KEY `lineidkey_idx` (`lineid`),
KEY `tgroupkey_idx` (`groupid`),
KEY `wsteamkey` (`team`),
KEY `xpidx` (`xplvl`),
KEY `idstart` (`idworker`,`startdate`,`enddate`),
KEY `groupline` (`lineid`,`groupid`,`team`),
KEY `idline` (`idworker`,`lineid`) COMMENT 'TRS2,3,5,7 külső join használja',
CONSTRAINT `lineidkey` FOREIGN KEY (`lineid`) REFERENCES `linehistory` (`lineID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `tgroupkey` FOREIGN KEY (`groupid`) REFERENCES `t_target` (`target_id`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `workeridkey` FOREIGN KEY (`idworker`) REFERENCES `t_workers` (`boschid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `wsteamkey` FOREIGN KEY (`team`) REFERENCES `schedules` (`wsid`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `xpkey` FOREIGN KEY (`xplvl`) REFERENCES `t_xplvl` (`lvlid`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8110 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

 Comments   
Comment by Varun Gupta (Inactive) [ 2020-08-20 ]

Can you try to run the queries on 10.5 with optimizer_use_condition_selectivity=1 and see if you get the good plan.

set optimizer_use_condition_selectivity=1;

Also can you share the optimizer trace output for the queries.

set optimizer_trace=1;
 
EXPLAIN query...
 
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

More info here https://mariadb.com/kb/en/optimizer-trace-overview/

Comment by Zoltán Szalai (Inactive) [ 2020-08-24 ]

Setting "set optimizer_use_condition_selectivity=1;" didn't change the query plan.

Trace output for MariaDB 10.5.5 and MySQL 5.7.19 trace-MariaDB-10.5.5.json trace-MySQL-5.7.19.json attached.

Comment by Varun Gupta (Inactive) [ 2020-08-24 ]

Eperbab Can you share the optimizer trace output for 10.4 too, this will help in investigating why the plan changed.

Comment by Zoltán Szalai (Inactive) [ 2020-08-26 ]

Trace output for MariaDB 10.4.6 attached. trace-MariaDB-10.4.6.json
I had to install 10.4.6, because optimizer trace isn't available in 10.4.0. This version has started with the slower query plan, but it has switched to the faster plan after "set optimizer_use_condition_selectivity=1;". Now it sticks to the faster plan even with "set optimizer_use_condition_selectivity=0;".
10.5.5 still chooses the slower plan.

Comment by Varun Gupta (Inactive) [ 2020-08-26 ]

OK found some irregularities on first glance:

On 10.5 opt_trace output

"considered_access_paths": [
 
                        {
 
                          "access_type": "ref",
 
                          "index": "statdid",
 
                          "used_range_estimates": true,
 
                          "rows": 1255,
 
                          "cost": 74.42722765,
 
                          "chosen": true
 
                        },

10.4 opt_trace output

"considered_access_paths": [
 
                        {
 
                          "access_type": "ref",
 
                          "index": "statdid",
 
                          "used_range_estimates": true,
 
                          "rows": 1255,
 
                          "cost": 526.3,
 
                          "chosen": true
 
                        },

So for the same number of rows the difference in cost is ~7x

Comment by Sergei Petrunia [ 2020-09-01 ]

EXPLAINs in the readable form (made using json2table.com)

Comment by Zoltán Szalai (Inactive) [ 2020-09-02 ]

I can force MariaDB 10.5.5 to choose the faster query plan by forcing index usage. In the above query, I changed one line to
" docchange use index(primary) on docchange.hid = attendreg.hidref ". See explain-10.5_force_index.png

Comment by Julien Fritsch [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Comment by JiraAutomate [ 2023-12-05 ]

Automated message:
----------------------------
Since this issue has not been updated since 6 weeks, it's time to move it back to Stalled.

Generated at Thu Feb 08 09:22:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.