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

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.1(EOL)
Fix Version/s: 10.0.33, 10.1.29, 10.2.10
Component/s: Optimizer
Labels:
None

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).

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

mdev11574-debug-printout.diff
11 kB
2016-12-14 17:56
mdev11574-sel_tree.txt
16 kB
2016-12-14 17:59
mdev11574-sel_tree-read.txt
3 kB
2016-12-14 17:59

Issue Links

relates to

MDEV-9764 MariaDB does not limit memory used for range optimization

Closed

Activity

Ascending order - Click to sort in descending order

Sergei Petrunia added a comment - 2016-12-14 18:01 - edited

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?

Sergei Petrunia added a comment - 2016-12-14 18:01 - edited 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?

Igor Babaev (Inactive) added a comment - 2017-09-22 11:31

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

Igor Babaev (Inactive) added a comment - 2017-09-22 11:31 Julien, The chances are good that this will be fixed in 10.0.33

Igor Babaev (Inactive) added a comment - 2017-10-18 08:49

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

Igor Babaev (Inactive) added a comment - 2017-10-18 08:49 A fix for this bug was pushed into the 10.0 tree.

Julien Fritsch added a comment - 2017-10-18 13:48

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

Julien Fritsch added a comment - 2017-10-18 13:48 igor the fixVersion is 10.0.31, but this one has been released in May is that normal ?

People

Assignee:: Igor Babaev (Inactive)

Reporter:: Sergei Petrunia

Votes:: 3 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2016-12-14 17:42

Updated:: 2024-07-08 01:11

Resolved:: 2017-10-18 08:49

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration