[#MDEV-31090] wrong query plan, seems a bug in optimizer

[MDEV-31090] wrong query plan, seems a bug in optimizer Created: 2023-04-19 Updated: 2023-05-26
Status:	Open
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.6.11, 10.6.12
Fix Version/s:	None

Type:

Bug

Priority:

Major

Reporter:

firk

Assignee:

Unassigned

Resolution:

Unresolved

Votes:

Labels:

None

Environment:

FreeBSD 12.3

Attachments:

bad.txt

good.txt

Description

The bug is partially intersects with MDEV-26256

Table definition (seemingly unrelated fields replaced with placeholders):

CREATE TABLE `ttt` (

  `id` int(11) NOT NULL AUTO_INCREMENT,

  `id2` int(11) NOT NULL DEFAULT 0,

  `ctime` int(11) NOT NULL DEFAULT 0,

  `f1` varchar(8192) NOT NULL DEFAULT '',

  `f2` int(11) NOT NULL DEFAULT 0,

  `f3` int(11) NOT NULL DEFAULT 0,

  `f4` int(11) NOT NULL DEFAULT 0,

  `f5` int(11) NOT NULL DEFAULT 0,

  `f6` int(11) NOT NULL DEFAULT 0,

  `f7` int(11) NOT NULL DEFAULT 0,

  `f8` int(11) NOT NULL DEFAULT 0,

  `f9` int(11) NOT NULL DEFAULT 0,

  `f10` int(11) NOT NULL DEFAULT 0,

  `f11` int(11) NOT NULL DEFAULT 0,

  `f12` int(11) NOT NULL DEFAULT 0,

  `f13` int(11) NOT NULL DEFAULT 0,

  `f14` varchar(8192) NOT NULL DEFAULT '',

  `f15` varchar(8192) NOT NULL DEFAULT '',

  PRIMARY KEY (`id`),

  KEY `id2_ctime` (`id2`,`ctime`),

  KEY `time` (`ctime`)

) ENGINE=MyISAM AUTO_INCREMENT=8238691 DEFAULT CHARSET=cp1251 COLLATE=cp1251_general_ci;

Query:

SELECT * FROM `ttt` WHERE `id2`='8' AND `ctime`>TTTTTT ORDER BY `ctime` DESC LIMIT 10;

Where TTT is some value. The 'WHERE' condition never covers more than 20 rows, and, as you may see, WHERE+ORDER perfectly matches `id2_ctime` index. Usually, it handled normally via the mentioned index:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	ttt	range	id2_ctime,time	id2_ctime	8	NULL	8	Using where

Sometimes (looks like pure random, <1% of requests), things goes wrong:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	ttt	ref	id2_ctime,time	id2_ctime	4	const	1025552	Using where

It stripped the half of useful index and bruteforced rows that matched only the first part of the index.

I got optimizer traces for 'good' and 'bad' cases (attached). There is two interesting parts:

1) wrong rows estimation (more specific, longer key gets 74 rows while shorted `ctime`-only key gets 72 rows) - this looks like the same issue MDEV-26256

2) after rows_estimation, the next step "considered_execution_plans": here we see that rows_estimation results seems ignored (i don't see the plan using "better" `ctime`-only key here), but instead now we comparing the plan using `id2_ctime` key and the plan using its truncated version `id2`-only key.

"considered_access_paths": [

    "access_type": "ref",

    "index": "id2_ctime",

    "rows": 1025552,

    "cost": 824471.8898,

    "chosen": true

},

    "access_type": "range",

    "resulting_rows": 663.3084173,

    "cost": 1.79769e308,

    "chosen": false

],

"chosen_access_method": {

  "type": "ref",

  "records": 1025552,

  "cost": 824471.8898,

  "uses_join_buffering": false

For some reason, long-key version gets completely wrong resulting_rows=663.3084173, and yet more irrelevant cost=1.79769e308. Due to this, truncated-key version with it properly calculated bad cost=824471 wins. I don't think that the "1.79769e308" value may be the result of any normal calculations, it looks like the result of something completely wrong.

Generated at Thu Feb 08 10:21:12 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-31090] wrong query plan, seems a bug in optimizer Created: 2023-04-19 Updated: 2023-05-26