[MDEV-20900] IN predicate to IN subquery conversion causes performance regression - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3.18, 10.4.8
Fix Version/s: 10.3.22, 10.4.12, 10.5.1
Component/s: Optimizer
Labels:
- optimizer
- performance
Environment:
Debian Linux Buster and Stretch

Description

This issue affects Magento2 (leading eCommerce CRM): https://magento.com/
Most likely related to ~~MDEV-12176~~.

Magento2 is using Entity-Attribute-Value model (EAV), also known as object-attribute-value.
with 315 tables. Here is the diagram:
https://anna.voelkl.at/magento-ce-2-1-3-database-diagram/

As a result, there are multiple "IN " statements in queries. Here is one of the problematic queries:
SELECT `main_table`.* FROM `url_rewrite` AS `main_table` WHERE (`redirect_type` = '0') AND (`entity_type` = 'product') AND (`entity_id` IN('448', '503', '532', '547 ........... <------ if the number of products here exceeds 1000, then "optimizer" creates subqueries.

In MariaDB 10.1 and 10.2 and also MySQL 8 it's a SIMPLE execution plan that takes 6 seconds:

see screenshot 1

95210 rows in set (5.58 sec)

On MariaDB 10.3 and 10.4 on the same server with the same settings (including optimizer_switch) it's now 35 mins:

see screenshot 2

However, there is no way to disable subqueries in ~~MDEV-12176~~ (@@in_predicate_conversion_threshold doesn't work on production releases)

The database is about 7GB and contains sensitive data. That's why I didn't attach it to this issue. It is available on request.

Attachments

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

Capture0.PNG
18 kB
2019-10-26 21:59
Capture1.PNG
44 kB
2019-10-26 21:59

Issue Links

is caused by

MDEV-12176 Transform [NOT] IN predicate with long list of values INTO [NOT] IN subquery.

Closed

relates to

MDEV-21265 IN predicate conversion to IN subquery should be allowed for a broader set of datatype comparison

Closed

MDEV-20871 Queries with entity_id IN ('1', '2', ..... , '70000') run much slower in MariaDB 10.3.18 than on MariaDB 10.1

Closed

MDEV-23704 Optimizer does not use semijoin optimization for some WHERE (pk1, pk2, pk3) IN ((1,2,3), ...) queries

Closed

Activity

Ascending order - Click to sort in descending order

Alex Babanski created issue - 2019-10-26 21:54

Alex Babanski made changes - 2019-10-26 21:54

Field	Original Value	New Value
Link		This issue is caused by ~~MDEV-12176~~ [ ~~MDEV-12176~~ ]

Alex Babanski made changes - 2019-10-26 21:56

Description

This issue affects Magento2 (leading eCommerce CRM): https://magento.com/
Most likely related to ~~MDEV-12176~~.

Magento2 is using Entity-Attribute-Value model (EAV), also known as object-attribute-value.
with 315 tables. Here is the diagram:
https://anna.voelkl.at/magento-ce-2-1-3-database-diagram/

As a result, there are multiple "IN " statements in queries. Here is one of the problematic queries:
SELECT `main_table`.* FROM `url_rewrite` AS `main_table` WHERE (`redirect_type` = '0') AND (`entity_type` = 'product') AND (`entity_id` IN('448', '503', '532', '547 ........... <------ if the number of products here exceeds 1000, then "optimizer" creates subqueries.

In *MariaDB 10.1* and *10.2* and also *MySQL 8* it's a SIMPLE execution plan that takes *6 seconds*:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: main_table
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 659999
filtered: 0.50
Extra: Using where

95210 rows in set (5.58 sec)

On *MariaDB 10.3* and *10.4* on the same server with the same settings (including optimizer_switch) it's now *35 mins*:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: main_table
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 671326
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31001
        Extra: Using where; FirstMatch(main_table)
*************************** 3. row ***************************
           id: 3
  select_type: DERIVED
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used

However, there is no way to disable subqueries in ~~MDEV-12176~~ (@@in_predicate_conversion_threshold doesn't work on production releases)

The database is about 7GB and contains sensitive data. That's why I didn't attach it to this issue. It is available on request.

This issue affects Magento2 (leading eCommerce CRM): https://magento.com/
Most likely related to ~~MDEV-12176~~.

Magento2 is using Entity-Attribute-Value model (EAV), also known as object-attribute-value.
with 315 tables. Here is the diagram:
https://anna.voelkl.at/magento-ce-2-1-3-database-diagram/

As a result, there are multiple "IN " statements in queries. Here is one of the problematic queries:
SELECT `main_table`.* FROM `url_rewrite` AS `main_table` WHERE (`redirect_type` = '0') AND (`entity_type` = 'product') AND (`entity_id` IN('448', '503', '532', '547 ........... <------ *if the number of products here exceeds 1000, then "optimizer" creates subqueries.*

In *MariaDB 10.1* and *10.2* and also *MySQL 8* it's a SIMPLE execution plan that takes *6 seconds*:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: main_table
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 659999
filtered: 0.50
Extra: Using where

95210 rows in set (5.58 sec)

On *MariaDB 10.3* and *10.4* on the same server with the same settings (including optimizer_switch) it's now *35 mins*:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: main_table
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 671326
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31001
        Extra: Using where; FirstMatch(main_table)
*************************** 3. row ***************************
           id: 3
  select_type: DERIVED
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used

However, there is no way to disable subqueries in ~~MDEV-12176~~ (@@in_predicate_conversion_threshold doesn't work on production releases)

The database is about 7GB and contains sensitive data. That's why I didn't attach it to this issue. It is available on request.

Alex Babanski made changes - 2019-10-26 21:57

Description

This issue affects Magento2 (leading eCommerce CRM): https://magento.com/
Most likely related to ~~MDEV-12176~~.

Magento2 is using Entity-Attribute-Value model (EAV), also known as object-attribute-value.
with 315 tables. Here is the diagram:
https://anna.voelkl.at/magento-ce-2-1-3-database-diagram/

As a result, there are multiple "IN " statements in queries. Here is one of the problematic queries:
SELECT `main_table`.* FROM `url_rewrite` AS `main_table` WHERE (`redirect_type` = '0') AND (`entity_type` = 'product') AND (`entity_id` IN('448', '503', '532', '547 ........... <------ *if the number of products here exceeds 1000, then "optimizer" creates subqueries.*

In *MariaDB 10.1* and *10.2* and also *MySQL 8* it's a SIMPLE execution plan that takes *6 seconds*:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: main_table
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 659999
filtered: 0.50
Extra: Using where

95210 rows in set (5.58 sec)

On *MariaDB 10.3* and *10.4* on the same server with the same settings (including optimizer_switch) it's now *35 mins*:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: main_table
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 671326
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31001
        Extra: Using where; FirstMatch(main_table)
*************************** 3. row ***************************
           id: 3
  select_type: DERIVED
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used

However, there is no way to disable subqueries in ~~MDEV-12176~~ (@@in_predicate_conversion_threshold doesn't work on production releases)

The database is about 7GB and contains sensitive data. That's why I didn't attach it to this issue. It is available on request.

This issue affects Magento2 (leading eCommerce CRM): https://magento.com/
Most likely related to ~~MDEV-12176~~.

Magento2 is using Entity-Attribute-Value model (EAV), also known as object-attribute-value.
with 315 tables. Here is the diagram:
https://anna.voelkl.at/magento-ce-2-1-3-database-diagram/

As a result, there are multiple "IN " statements in queries. Here is one of the problematic queries:
SELECT `main_table`.* FROM `url_rewrite` AS `main_table` WHERE (`redirect_type` = '0') AND (`entity_type` = 'product') AND (`entity_id` IN('448', '503', '532', '547 ........... <------ *if the number of products here exceeds 1000, then "optimizer" creates subqueries.*

In *MariaDB 10.1* and *10.2* and also *MySQL 8* it's a SIMPLE execution plan that takes *6 seconds*:

id: 1
select_type: SIMPLE
table: main_table
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 659999
filtered: 0.50
Extra: Using where

95210 rows in set (5.58 sec)

On *MariaDB 10.3* and *10.4* on the same server with the same settings (including optimizer_switch) it's now *35 mins*:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: main_table
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 671326
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31001
        Extra: Using where; FirstMatch(main_table)
*************************** 3. row ***************************
           id: 3
  select_type: DERIVED
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used

However, there is no way to disable subqueries in ~~MDEV-12176~~ (@@in_predicate_conversion_threshold doesn't work on production releases)

The database is about 7GB and contains sensitive data. That's why I didn't attach it to this issue. It is available on request.

Alex Babanski made changes - 2019-10-26 21:58

Description

This issue affects Magento2 (leading eCommerce CRM): https://magento.com/
Most likely related to ~~MDEV-12176~~.

Magento2 is using Entity-Attribute-Value model (EAV), also known as object-attribute-value.
with 315 tables. Here is the diagram:
https://anna.voelkl.at/magento-ce-2-1-3-database-diagram/

As a result, there are multiple "IN " statements in queries. Here is one of the problematic queries:
SELECT `main_table`.* FROM `url_rewrite` AS `main_table` WHERE (`redirect_type` = '0') AND (`entity_type` = 'product') AND (`entity_id` IN('448', '503', '532', '547 ........... <------ *if the number of products here exceeds 1000, then "optimizer" creates subqueries.*

In *MariaDB 10.1* and *10.2* and also *MySQL 8* it's a SIMPLE execution plan that takes *6 seconds*:

id: 1
select_type: SIMPLE
table: main_table
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 659999
filtered: 0.50
Extra: Using where

95210 rows in set (5.58 sec)

On *MariaDB 10.3* and *10.4* on the same server with the same settings (including optimizer_switch) it's now *35 mins*:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: main_table
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 671326
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31001
        Extra: Using where; FirstMatch(main_table)
*************************** 3. row ***************************
           id: 3
  select_type: DERIVED
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used

However, there is no way to disable subqueries in ~~MDEV-12176~~ (@@in_predicate_conversion_threshold doesn't work on production releases)

The database is about 7GB and contains sensitive data. That's why I didn't attach it to this issue. It is available on request.

This issue affects Magento2 (leading eCommerce CRM): https://magento.com/
Most likely related to ~~MDEV-12176~~.

Magento2 is using Entity-Attribute-Value model (EAV), also known as object-attribute-value.
with 315 tables. Here is the diagram:
https://anna.voelkl.at/magento-ce-2-1-3-database-diagram/

As a result, there are multiple "IN " statements in queries. Here is one of the problematic queries:
SELECT `main_table`.* FROM `url_rewrite` AS `main_table` WHERE (`redirect_type` = '0') AND (`entity_type` = 'product') AND (`entity_id` IN('448', '503', '532', '547 ........... <------ *if the number of products here exceeds 1000, then "optimizer" creates subqueries.*

In *MariaDB 10.1* and *10.2* and also *MySQL 8* it's a SIMPLE execution plan that takes *6 seconds*:

95210 rows in set (5.58 sec)

On *MariaDB 10.3* and *10.4* on the same server with the same settings (including optimizer_switch) it's now *35 mins*:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: main_table
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 671326
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31001
        Extra: Using where; FirstMatch(main_table)
*************************** 3. row ***************************
           id: 3
  select_type: DERIVED
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used

However, there is no way to disable subqueries in ~~MDEV-12176~~ (@@in_predicate_conversion_threshold doesn't work on production releases)

The database is about 7GB and contains sensitive data. That's why I didn't attach it to this issue. It is available on request.

Alex Babanski made changes - 2019-10-26 21:59

Attachment

Capture0.PNG [ 49307 ]

Alex Babanski made changes - 2019-10-26 21:59

Attachment

Capture1.PNG [ 49308 ]

Alex Babanski made changes - 2019-10-26 21:59

Description

This issue affects Magento2 (leading eCommerce CRM): https://magento.com/
Most likely related to ~~MDEV-12176~~.

Magento2 is using Entity-Attribute-Value model (EAV), also known as object-attribute-value.
with 315 tables. Here is the diagram:
https://anna.voelkl.at/magento-ce-2-1-3-database-diagram/

As a result, there are multiple "IN " statements in queries. Here is one of the problematic queries:
SELECT `main_table`.* FROM `url_rewrite` AS `main_table` WHERE (`redirect_type` = '0') AND (`entity_type` = 'product') AND (`entity_id` IN('448', '503', '532', '547 ........... <------ *if the number of products here exceeds 1000, then "optimizer" creates subqueries.*

In *MariaDB 10.1* and *10.2* and also *MySQL 8* it's a SIMPLE execution plan that takes *6 seconds*:

95210 rows in set (5.58 sec)

On *MariaDB 10.3* and *10.4* on the same server with the same settings (including optimizer_switch) it's now *35 mins*:

*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: main_table
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 671326
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived3>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 31001
        Extra: Using where; FirstMatch(main_table)
*************************** 3. row ***************************
           id: 3
  select_type: DERIVED
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: No tables used

However, there is no way to disable subqueries in ~~MDEV-12176~~ (@@in_predicate_conversion_threshold doesn't work on production releases)

The database is about 7GB and contains sensitive data. That's why I didn't attach it to this issue. It is available on request.

Varun Gupta (Inactive) made changes - 2019-10-27 09:15

Assignee

Varun Gupta [ varun ]

Varun Gupta (Inactive) made changes - 2019-11-04 10:48

Summary

~~MDEV-12176~~ affects execution plans

IN predicate to IN subquery conversion causes performance regression

Varun Gupta (Inactive) made changes - 2019-11-04 11:08

Fix Version/s		10.3 [ 22126 ]
Fix Version/s		10.4 [ 22408 ]

Varun Gupta (Inactive) made changes - 2019-11-04 11:09

Status

Open [ 1 ]

In Progress [ 3 ]

Varun Gupta (Inactive) made changes - 2019-11-04 11:09

Assignee	Varun Gupta [ varun ]	Sergei Petrunia [ psergey ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Varun Gupta (Inactive) made changes - 2019-12-10 05:58

Assignee

Sergei Petrunia [ psergey ]

Varun Gupta [ varun ]

Varun Gupta (Inactive) made changes - 2019-12-10 05:58

Status

In Review [ 10002 ]

Stalled [ 10000 ]

Varun Gupta (Inactive) made changes - 2019-12-10 05:59

Fix Version/s		10.3.21 [ 24014 ]
Fix Version/s		10.4.11 [ 24013 ]
Fix Version/s		10.5.1 [ 24029 ]
Fix Version/s	10.3 [ 22126 ]
Fix Version/s	10.4 [ 22408 ]
Resolution		Fixed [ 1 ]
Status	Stalled [ 10000 ]	Closed [ 6 ]

Varun Gupta (Inactive) made changes - 2019-12-10 06:03

Link

This issue relates to ~~MDEV-21265~~ [ ~~MDEV-21265~~ ]

Varun Gupta (Inactive) made changes - 2019-12-10 06:41

Link

This issue relates to ~~MDEV-20871~~ [ ~~MDEV-20871~~ ]

Varun Gupta (Inactive) made changes - 2019-12-10 06:45

Fix Version/s		10.3.22 [ 24018 ]
Fix Version/s	10.3.21 [ 24014 ]

Varun Gupta (Inactive) made changes - 2019-12-10 06:49

Fix Version/s		10.4.12 [ 24019 ]
Fix Version/s	10.4.11 [ 24013 ]

Sergei Petrunia made changes - 2020-10-01 13:08

Link

This issue relates to ~~MDEV-23704~~ [ ~~MDEV-23704~~ ]

Valerii Kravchuk made changes - 2020-10-12 06:29

Comment

[ A comment with security level 'Developers' was removed. ]

Sergei Golubchik made changes - 2021-12-06 21:50

Workflow

MariaDB v3 [ 100606 ]

MariaDB v4 [ 156902 ]

Jira Automation (IT) made changes - 2024-07-04 03:32

Zendesk Related Tickets

184082

People

Assignee:: Varun Gupta (Inactive)

Reporter:: Alex Babanski

Votes:: 1 Vote for this issue

Watchers:: 6 Start watching this issue

Dates

Created:: 2019-10-26 21:54

Updated:: 2024-07-07 22:33

Resolved:: 2019-12-10 05:59

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