[#MDEV-11574] Queries with IN clauses that are pushing the optimizer to its limit

[MDEV-11574] Queries with IN clauses that are pushing the optimizer to its limit Created: 2016-12-14 Updated: 2020-08-25 Resolved: 2017-10-18
Status:	Closed
Project:	MariaDB Server
Component/s:	Optimizer
Affects Version/s:	10.1
Fix Version/s:	10.0.33, 10.1.29, 10.2.10

Type:

Bug

Priority:

Major

Reporter:

Sergei Petrunia

Assignee:

Igor Babaev

Resolution:

Fixed

Votes:

Labels:

None

Attachments:

mdev11574-debug-printout.diff

mdev11574-sel_tree-read.txt

mdev11574-sel_tree.txt

Issue Links:

Relates
relates to	~~MDEV-9764~~	MariaDB does not limit memory used fo...	Closed

Description

Consider a table

create table t1234 (

  campaignid varchar(32) not null,

  adgroupid varchar(32) not null,

  col1 int not null,

  PRIMARY KEY (campaignid,adgroupid),

  UNIQUE KEY adgroupid (adgroupid)

);

and a query in form

explain

select * from t1234

where

   campaignid='Finland' AND adgroupid IN ('Lahti','Imatra')

OR

   campaignid='Russia' AND adgroupid IN ('Petersburg', 'Moscow')

OR

   campaignid='Sweden' AND adgroupid IN ('Stockholm', 'Uppsala')

OR

   campaignid='Germany' AND adgroupid IN ('Berlin', 'Frankfurt')

OR

   campaignid='Belgium' AND adgroupid IN ('Brussels', 'Gent')

OR

   campaignid='Portugal' AND adgroupid IN ('Lisbon', 'Porto')

...

The query

takes a lot of time to run (more details to follow about this).
May switch to using full table scan when it is still better to use the range access.

The reason seems to be that MariaDB's optimizer is trying to construct index_merge access plans which are not particularly meaningful ( adding ignore index (adgroupid) resolves the issue).

Comments

Comment by Sergei Petrunia [ 2016-12-14 ]

Patch to print SEL_TREEs into json files in /tmp : mdev11574-debug-printout.diff
One file printed by the patch: mdev11574-sel_tree.txt
The above file with extra stuff removed: mdev11574-sel_tree-read.txt

Questions:

The index_merge data structure is really deep. Does the optimizer really consider all the depth levels?

Looking into file, one can see pieces like this

                              "index_merge": {

                                "tree": {

                                  "ranges": {

                                    "PRIMARY": {

                                      "Finland": ["Imatra", "Lahti"]

},

                                    "adgroupid": ["Imatra", "Lahti"]

},

                                "tree": {

                                  "ranges": {

                                    "PRIMARY": {

                                      "Russia": ["Moscow", "Petersburg"]

},

                                    "adgroupid": ["Moscow", "Petersburg"]

},

                                "tree": {

                                  "ranges": {

                                    "PRIMARY": {

                                      "Sweden": ["Stockholm", "Uppsala"]

},

                                    "adgroupid": ["Stockholm", "Uppsala"]

},

On, in the simplified file:

                              "index_merge": {

                                "tree": { F },

                                "tree": { R },

                                "tree": { S }

What is the point of building a 3-way index_merge when there are only two indexes?

Comment by Igor Babaev [ 2017-09-22 ]

Julien,
The chances are good that this will be fixed in 10.0.33

Comment by Igor Babaev [ 2017-10-18 ]

A fix for this bug was pushed into the 10.0 tree.

Comment by Julien Fritsch [ 2017-10-18 ]

igor the fixVersion is 10.0.31, but this one has been released in May is that normal ?

Generated at Thu Feb 08 07:51:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[MDEV-11574] Queries with IN clauses that are pushing the optimizer to its limit Created: 2016-12-14 Updated: 2020-08-25 Resolved: 2017-10-18