oli you can disable this optimization on 10.3 by setting in_predicate_conversion_threshold to 0.
I updated the docs to mention it https://mariadb.com/kb/en/conversion-of-big-in-predicates-into-subqueries/

OK. This solves part of the problem: in_predicate_conversion_threshold=1200 or 0 produces the old pre 10.3 QEP. But still slower than competing product. I try to play a bit more with it if I can enforce mrr...

oli, we were also thinking about the cause of the slowdown (conversion of IN predicate into a subquery as such should not cause a slowdown).

Could you try the query without the ORDER BY clause on
A. on MariaDB 10.2
B. on MariaDB 10.3, with the default in_predicate_conversion_threshold setting?

I try to play a bit more with it if I can enforce mrr...

This should do it :

set optimizer_switch='mrr=on' 

SELECT @@version;
-----------------

-----------------

-----------------

SELECT @@optimizer_switch\G

• • • • • • • • • • • • • • • • • • • • • • • • • • • 1. row ***************************
                                                      @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on

------------------------------------------------------------------------------------------+

------------------------------------------------------------------------------------------+

------------------------------------------------------------------------------------------+
about 100 - 110 ms

SELECT @@version;
-----------------

-----------------

-----------------

SELECT @@optimizer_switch\G

• • • • • • • • • • • • • • • • • • • • • • • • • • • 1. row ***************************
                                                      @@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on

SELECT @@in_predicate_conversion_threshold;
-------------------------------------

-------------------------------------

-------------------------------------

-------------------------------------------------------------------------------------------------+

-------------------------------------------------------------------------------------------------+

-------------------------------------------------------------------------------------------------+
101ms 95ms 102ms 102ms 95ms 102ms 105ms (vs. 150 ms WITH ORDER BY)

SET SESSION optimizer_switch='mrr=on';

With ORDER BY:
-----------------------------------------------------------------------------------------------------------+

-----------------------------------------------------------------------------------------------------------+

-----------------------------------------------------------------------------------------------------------+
145 - 150ms

Without ORDER BY:
-------------------------------------------------------------------------------------------------+

-------------------------------------------------------------------------------------------------+

-------------------------------------------------------------------------------------------------+
around 100 to 105 ms

I cannot see any MRR optimization.

MariaDB docu mentions: https://mariadb.com/kb/en/multi-range-read-optimization/

• range, ref and eq_ref access - OK
• for between (Range access) - We do not have, we have IN (...)
• for on t1.id = t2.id (for BKA) 2 times - not our case

We have: WHERE id IN (1100 values). Possibly MRR is not considered in this case?

I can ask customer if I can send you the data if it helps?

Analyzing of the provided data: So, if one removes the ORDER BY,

• 10.2 (no conversion) takes 100-110 ms
• 10.3 (with conversion) takes 95-105 ms

roughly the same.

This seems to prove the theory that:

for MariaDB, there is no time difference between
A: reading rows with range access, and
B: reading rows with ref access.

The difference in query time comes from interplay with filesort:

• in the range plan, the query has "Using filesort", which means the filesort code reads the data from quick range select. This is possible when the data to be sorted come from a single table.

• in the subquery-based plan, the sorted data comes from several tables. In order to sort it, the runtime needs to write it it to the temp. table and then read it back. Because of that, the query has "Using temporary; Using filesort".

So, the reason for MariaDB's slowdown is writing the data and reading it back...

Another thing to check: what is the "Using index condition" for table wp_postmeta?
It is

        "table": {

          "table_name": "wp_postmeta",

          "access_type": "ref",

          "possible_keys": ["post_id"],

          "key": "post_id",

          "key_length": "8",

          "used_key_parts": ["post_id"],

          "ref": ["tvc_0._col_1"],

          "rows": 3,

          "filtered": 100,

          "index_condition": "wp_postmeta.post_id = tvc_0._col_1"

        }

which is a bit odd as wp_postmeta.post_id was defined as

  `post_id` bigint(20) unsigned NOT NULL DEFAULT 0,

and normally the optimizer is able to detect that in such cases the returned row satisfies the WHERE condition..

The query profile supports your idea:

+------------------------+----------+

| Status                 | Duration |

+------------------------+----------+

| Starting               | 0.000343 |

| Checking permissions   | 0.000005 |

| Opening tables         | 0.000015 |

| After opening tables   | 0.000004 |

| System lock            | 0.000004 |

| Table lock             | 0.000005 |

| Init                   | 0.000198 |

| Optimizing             | 0.000135 |

| Statistics             | 0.000026 |

| Preparing              | 0.000022 |

| Creating tmp table     | 0.000011 |

| Sorting result         | 0.000008 |

| Executing              | 0.000002 |

| Sending data           | 0.000135 |

| Removing tmp table     | 0.000003 |

| Sending data           | 0.049319 |

| Creating sort index    | 0.082734 |

| Removing tmp table     | 0.013269 |

| Creating sort index    | 0.000023 |

| End of update loop     | 0.000016 |

| Removing tmp table     | 0.000006 |

| End of update loop     | 0.000003 |

| Query end              | 0.000003 |

| Commit                 | 0.000004 |

| Closing tables         | 0.000002 |

| Removing tmp table     | 0.000024 |

| Closing tables         | 0.000003 |

| Unlocking tables       | 0.000002 |

| Closing tables         | 0.000008 |

| Starting cleanup       | 0.000002 |

| Freeing items          | 0.000042 |

| Updating status        | 0.000016 |

| Reset for next command | 0.000020 |

+------------------------+----------+

+------------------------+----------+

| Status                 | Duration |

+------------------------+----------+

| Starting               | 0.000359 |

| Checking permissions   | 0.000005 |

| Opening tables         | 0.000015 |

| After opening tables   | 0.000004 |

| System lock            | 0.000003 |

| Table lock             | 0.000013 |

| Init                   | 0.000197 |

| Optimizing             | 0.000157 |

| Statistics             | 0.000026 |

| Preparing              | 0.000026 |

| Executing              | 0.000003 |

| Sending data           | 0.000108 |

| Removing tmp table     | 0.000003 |

| Sending data           | 0.105910 |

| End of update loop     | 0.000011 |

| Removing tmp table     | 0.000023 |

| End of update loop     | 0.000003 |

| Query end              | 0.000003 |

| Commit                 | 0.000003 |

| Closing tables         | 0.000002 |

| Removing tmp table     | 0.000003 |

| Closing tables         | 0.000002 |

| Unlocking tables       | 0.000002 |

| Closing tables         | 0.000007 |

| Starting cleanup       | 0.000002 |

| Freeing items          | 0.000034 |

| Updating status        | 0.000012 |

| Reset for next command | 0.000011 |

+------------------------+----------+

By the way: I got OK from customer to upload you the data if needed?

I cannot see any MRR optimization.
...
We have: WHERE id IN (1100 values). Possibly MRR is not considered in this case?

Sorry, I was not clear enough. I intended to say this:

set in_predicate_conversion_threshold=0;

set optimizer_switch='mrr=off';

Then, MRR is used (The EXPLAIN shows it as "Rowid-ordered scan"):

+------+-------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+

| id   | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra                                                     |

+------+-------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+

|    1 | SIMPLE      | wp_postmeta | range | post_id       | post_id | 8       | NULL | 8896 | Using index condition; Rowid-ordered scan; Using filesort |

+------+-------------+-------------+-------+---------------+---------+---------+------+------+-----------------------------------------------------------+

Without in_predicate_conversion_threshold=0, one can get MRR to be used by enabling batched key access:

set join_cache_level=6;

set optimizer_switch='mrr=on';

+------+--------------+-------------+------+---------------+---------+---------+--------------+------+------------------------------------------------------------------------------------+

| id   | select_type  | table       | type | possible_keys | key     | key_len | ref          | rows | Extra                                                                              |

+------+--------------+-------------+------+---------------+---------+---------+--------------+------+------------------------------------------------------------------------------------+

|    1 | PRIMARY      | <subquery2> | ALL  | distinct_key  | NULL    | NULL    | NULL         | 1125 | Using temporary; Using filesort                                                    |

|    1 | PRIMARY      | wp_postmeta | ref  | post_id       | post_id | 8       | tvc_0._col_1 |    3 | Using index condition(BKA); Using join buffer (flat, BKA join); Rowid-ordered scan |

|    2 | MATERIALIZED | <derived3>  | ALL  | NULL          | NULL    | NULL    | NULL         | 1125 |                                                                                    |

|    3 | DERIVED      | NULL        | NULL | NULL          | NULL    | NULL    | NULL         | NULL | No tables used                                                                     |

+------+--------------+-------------+------+---------------+---------+---------+--------------+------+------------------------------------------------------------------------------------+

...But looking at the numbers of rows to be read, I doubt that this will reliably help.

oli, can you try with "set optimizer_switch='mrr=off'" on MySQL 8? Does it have any effect on the query execution time?

By the way: I got OK from customer to upload you the data if needed?

If it is not too bothersome - let's give it a try, please upload. (Because I haven't been able to reproduce with the test data I have generated).

Tried to reproduce: mdev23228-fill-tables.sql generate-query.txt .
The table is 500K rows, 230MB on disk.

The query: mdev23228-q-timings.sql run script: run-timings.sql

The results (all values are time in Microseconds):

mariadb <- c(39564,29761,51184,67787,45390,45925,28415,34310,47551,28976,28867,28716,28740,47749,32246,29879,28710,28456,27248,27210,27068,28185,27041,43309,27428,27405,27292,43344,28557,37942,50461,28806,44525,48870,28603,28640,28852,32789,29075,28708,44070,45492,45734,28364,28545,28099,28890,29055,43470,28090,27918,28130,28797,28680,28018,29292,37149,33905,30729,44792,28428,46337,28211,28296,26706,27054,45606,27876,26668,45248,45038,27915,27691,27656,28180,27833,27939,28058,27654,27751,44741,27874,28128,28000,34058,36523,29250,29171,26854,30993,44383,45185,45307,27665,43790,26546,43534,27905,28103,28173)
mariadb_no_conv <- c(36456,26442,73276,28969,48066,26377,25266,42052,24613,24880,25725,25696,24780,26234,44136,25894,42799,44983,25591,25129,25877,25083,26108,24976,25959,44426,26157,43046,26191,26798,25118,39947,42305,31773,25149,26337,25083,27010,25757,26790,26262,25135,25892,25855,24984,26275,25121,26022,24955,26216,25405,26293,25197,26367,24967,25901,43534,24907,26172,40087,26704,26557,25126,37524,25357,47087,27259,26959,24965,25987,25920,24814,24919,25958,41877,44327,25834,25809,24997,25960,25689,41102,25805,25737,24792,25771,25704,24908,25762,44579,26531,25064,58891,30449,24896,50861,26546,25188,26023,43467).
mysql <- c(37523,33895,33970,34134,34351,34193,34137,34026,33559,33321,34961,33748,34029,33919,33325,32717,33407,33895,33830,34279,33014,33054,33229,34878,33397,33414,32744,32630,32802,33783,33523,33556,33424,33511,33646,33458,33501,33656,33491,33594,33747,33748,33550,33411,33579,33606,33720,33436,33428,33163,33198,33175,33729,33726,33460,33507,33212,33555,33388,33303,33294,33373,33468,33430,33298,33450,33723,33779,33612,33740,33526,33726,33572,33507,33285,33640,33656,33680,33663,33621,34003,33654,34795,33782,34126,33915,33837,33630,33981,33305,33694,34016,34571,34196,34167,33842,33933,33709,33887,33862)

plot(density(mariadb_no_conv), col="blue", ylim=c(0, 0.001))

lines(density(mariadb), col="cyan")

lines(density(mysql), col="red")

legend(60000, 8e-04, legend=c("MariaDB-no-conv", "MariaDB", "MySQL-8"),

       col=c("blue", "cyan", "red"), lty=c(1,1,1))

Mean query time, microsec

MariaDB:            33631.31

MariaDB-no-conv:    30145.06 

MySQL-8:            33691.13

Fastest query time, microsec

MariaDB:            26546

MariaDB-no-conv:    24613

MySQL-8:            32630

So, for me
with averages, MariaDB with in-to-subquery is slightly worse than the original, but it's on par with MySQL.

Query time distribution is interesting. MySQL is highly predictable while MariaDB has "unlucky runs" which are much slower than normal.

Dump, Create Table and Query: cat xa? > wp_postmeta.tar.gz

8.0.20-commercial Enterprise Server

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+

| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                            |

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+

|  1 | SIMPLE      | wp_postmeta | NULL       | range | post_id       | post_id | 8       | NULL | 6768 |   100.00 | Using index condition; Using MRR; Using filesort |

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------------------------------+

70 - 80 ms

SET SESSION optimizer_switch='mrr=off,mrr_cost_based=off';

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+

| id | select_type | table       | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                                 |

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+

|  1 | SIMPLE      | wp_postmeta | NULL       | range | post_id       | post_id | 8       | NULL | 6768 |   100.00 | Using index condition; Using filesort |

+----+-------------+-------------+------------+-------+---------------+---------+---------+------+------+----------+---------------------------------------+

70 - 80 ms

+--------------------------------+----------+

| Status                         | Duration |

+--------------------------------+----------+

| starting                       | 0.000508 |

| Executing hook on transaction  | 0.000007 |

| starting                       | 0.000011 |

| checking permissions           | 0.000009 |

| Opening tables                 | 0.000189 |

| init                           | 0.000009 |

| System lock                    | 0.000011 |

| optimizing                     | 0.000088 |

| statistics                     | 0.001689 |

| preparing                      | 0.000114 |

| executing                      | 0.071103 |

| end                            | 0.000013 |

| query end                      | 0.000005 |

| waiting for handler commit     | 0.000075 |

| closing tables                 | 0.000010 |

| freeing items                  | 0.000037 |

| cleaning up                    | 0.000009 |

+--------------------------------+----------+

oli, thanks for the dataset.

Played with it on my side. (The query times are microseconds, each query is tried 100 times)
https://gist.github.com/spetrunia/774fad85731338785fa8f0434358c23e

Results

• For me, MariaDB was faster.
• With ORDER BY clause , In->to->subquery conversion gives about 1.4x slowdown.
• Without ORDER BY, In->to->subquery conversion gives 0.93x slowdown (that is, a speedup)

Two angles:

• would be interesting to know why the results are different. (My setup is slightly different - self-built binaries, not exactly same 8.0 version, etc)
• What (if anything) could be done about the slowdown with ORDER BY.

Roel, could you try on your side (the details are in the email)

Original posted issue confirmed. I did this on a large Google cloud instance which was otherwise idle.
1) Used the 'Dump, Create Table and Query' data attached to issue.
2) Started self-build 10.2.33 and 10.3.24 with fresh database instances, no tuning, optimized (release) builds.
3) Remarked EXPLAIN in the query sql file (wp_postmeta_query.sql)
4) Brought up CLI command line and ran SOURCE wp_postmeta_dump.sql;
5) Used SOURCE wp_postmeta_query.sql; to execute the query.
6) Waited for output to finish on one mysqld instance before moving to next one (to avoid tests affecting each other)
7) Compared the results (after significant visual output);

...

25301 rows in set (0.16 sec)

...

25301 rows in set (0.230 sec)

Note the rather large increase for 10.3, which indeed seems like a performance regression.

Subsequent runs of the same query still vary though less; caching of any sort likely kicked in, especially as run times are shorter. The results in that case were 0.11 seconds (10.2.33) and 0.170 seconds (10.3.24). Still a significant difference. This result is repeatable (0.11/0.166 on next run), and is deemed non-sporadic.

I next tested 10.5.5 (again on a freshly created instance) and the result there was:

25301 rows in set (0.628 sec)

A significantly worse result on the first query. Subsequent runs gave 0.186 sec and 0.179 sec respectively, which is close to 10.3 performance. It thus seems like there is a regression starting with 10.3 only. I next test 10.4 to see what the outcome was there. For the first query there was;

25301 rows in set (0.231 sec)

And subsequent queries gave 0.170 sec and 0.170 sec again on the third run. I thus conclude that the performance regression started in 10.3 and continues to exist today.

Note that the use of the CLI may mean that the output is slow (has regressed) versus the actual query being slow. To analyze this further, I re-enabled EXPLAIN to see if there was a difference in query execution plan on my side also. I also re-initalized the server completely to have a fresh environment and avoid any caching from affecting things. Results:

10.2.33>source /home/roel/test/wp_postmeta_query.sql

*************************** 1. row ***************************

           id: 1

  select_type: SIMPLE

        table: wp_postmeta

         type: range

possible_keys: post_id

          key: post_id

      key_len: 8

          ref: NULL

         rows: 25301

        Extra: Using index condition; Using filesort

1 row in set (0.01 sec)

10.3.24>source /home/roel/test/wp_postmeta_query.sql

*************************** 1. row ***************************

           id: 1

  select_type: PRIMARY

        table: <subquery2>

         type: ALL

possible_keys: distinct_key

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1128

        Extra: Using temporary; Using filesort

*************************** 2. row ***************************

           id: 1

  select_type: PRIMARY

        table: wp_postmeta

         type: ref

possible_keys: post_id

          key: post_id

      key_len: 8

          ref: tvc_0._col_1

         rows: 1   *REF*

        Extra: Using index condition

*************************** 3. row ***************************

           id: 2

  select_type: MATERIALIZED

        table: <derived3>

         type: ALL

possible_keys: NULL

          key: NULL

      key_len: NULL

          ref: NULL

         rows: 1128

        Extra: 

*************************** 4. 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

4 rows in set (0.001 sec)

In summary; the changed query execution plan seems to lead to a significant performance regression from 10.3 onwards. Looking at the number of rows, perhaps the issue is that in 10.3 onwards, there are 1128 rows x? 1128 = 1272384? rows accessed, versus 25301 rows in the first one? Just an idea. Another reason may be the "using temporary"?

I also checked 10.4 and 10.5 query execution plans, and they almost completely match, though for the number of rows (marked by 'REF' in the last EXPLAIN about above) there are 2 rows instead of 1 row for 10.4.14, and 3 rows instead of 1 row for 10.5.5 which seems very odd. Note also that in the original issue posted it's 4 . Again, all other markers are the same as the last EXPLAIN output above.

I've updated the bug tags.

oli Please note that the uploaded data is public and available to anyone even without logging in. Just want to make sure you're clear on that. Thanks.

I wonder if this is related,
just did an upgrade from ubuntu 20.04 packaged mariadb (identifies as 10.3.25-MariaDB-0ubuntu0.20.04.1-log ) to mariadbs packaged 10.5.9
and now facing a lot of queries not using indexes anymore leading to extensive CPU usage in certain situations rendering the service unuseable.

possible_keys -> yes
key/key_len/ref NULL
rows -> 33703609
Using where

Only solution is to use force key to get the resource usage down to a sane level again.

Used Query is something like
SELECT `field_a`, SUM(`field_b`) as value FROM `table` WHERE `indexed_field` = 'somevalue' ;