According to the commit comment, it might be intentional:

commit 37f5569909d2b5a80e7f55b7b5d38d25ee2f0b5e

Author: Sergei Golubchik <serg@mariadb.org>

Date:   Sat Nov 4 19:14:34 2017 +0100

    @@in_predicate_conversion_threshold

    * rename in_subquery_conversion_threshold to in_predicate_conversion_threshold

    * make it debug-only, hide from users

    * change from ulong to uint - same type and range on all architectures

Assigning to serg to clarify.a

If there is a way for an engine to access the conversion (I think it is a derived temp table) or some kind of flag an engine can set to turn this off then I would also be happy with that.

As long as you have a different server source tree you can change IN_SUBQUERY_CONVERSION_THRESHOLD or set global_system_variables.in_subquery_conversion_threshold when your engine is initialized.

But we'll have to think of something better for 10.4.

Hi Sergei

Why did you move this variable as debug-only? In some case the original optimization is not as efficient as expected, and it would really be great to be able to control this variable.
See MDEV-17795

Thanks!

joce,

• I think MariaDB has more than enough variables, so I'm generally against adding new variables without a good reason
• In this case I asked various our optimizer developers why would a user might want to tune the @@in_predicate_conversion_threshold. When it should be 1,000 and when 10,000?
• Nobody was able to offer any explanation of why it should be configurable, besides "it's useful in tests". So, we've made it debug-only, for testing purposes.

Of course, I was asking the wrong question. Even if user will never need to tune the conversion threshold, there can still be a good reason to be able to disable it. I agree it would be good to have a way to disable this optimization.

I think this is needed urgently, because this feature is so broken, buggy and random it can turn a tiny select into nested full table scan which requires to scan billions of rows to get some small dataset from 100k rows tables. So it'll randomly turn 2-3s SELECT's into something which could take 5-10 minutes at best. Because i also saw queries which after this "optimization" took so long that they needed to be kill'ed, so im not even sure how bad can it get! Before it was couple seconds!

https://jira.mariadb.org/browse/MDEV-17795
https://jira.mariadb.org/browse/MDEV-20083

Basically i think the feature is providing no benefit and implementation is probably so complicated that it should be removed altogether ASAP, because how many problems it makes, which are in addition very hard to diagnose, due to how random and unexpected they are.

Really if IN (..) is so large memory is a concern, in such rare cases temporary table can be created manually and it can be properly INDEXED and added to a proper join, instead of making totally suboptimal subquery, automatically. For sure, if IN (..) size is a concern then resulting query could probably never finish, due to how bad it works... so this optimization has no purpose because of how randomly it works, and you're risking totally killing your server if it ever gets applied!

Probably not many people are noticing this just because larger IN sets are not very common, and even if it hits them - the query plan could be totally different next time even when query is the same...

@Sergei Golubchik Please speak with people on Maria team, show them all these 3 reports, and try to make this whole "optimization" removed altogether as soon as possible. Even when it works as expected, there's no performance benefit. Moreover these are simplest cases with simple IN on one column. How bad can it get on complex queries?

And to make things worse it makes many real and needed optimizations, simply impossible because it forces suboptimal subqueries!

We have some servers in production which serve hundreds of thousands requests per minute. 10.4 is actually unusable for us because of this "optimization", and we'd need to rewrite each query to manually create temporary table, insert, add index and do a straight join with forced index. And probably it'd work much worse than implementation in 10.1 anyway...

The reason im so sure it needs to be removed and it's so important is that people having a lot of traffic will see the problem straight away and will be able to diagnose it instantly, but people with less loaded servers could be getting random lockups or crashes every couple of months. And they will never know what the problem is... having something which is so bad and so random in production code will make very bad user experience. And some people would even probably need to switch to some other servers because they won't be able to make their code stable.

Slawomir,
In your comments you assume only one pattern of using IN predicates. For this pattern the conversion into IN subquery can not be beneficial especially when the hash join is turned off.
At the same time you claim that " this feature is so broken, buggy and random it ". I need examples where it is:
1. broken
2. buggy
3. random.
It would be helpful.

Ok i oversimplified this looking from perspective of this pattern which i see a lot and for which we're using IN most of the time. It also happened that the second pattern we're using for optimization also was degraded, however that seems irrelevant because differences weren't significiant (<2x). Realized this was designed for different scenarios so shuldn't call for it being removed.

For these 3 points mentioned i meant that even with highest join level enabled a very small differences in table structure or in query, could lead to totally different and unexpected execution times which are very counter intuitive (eg. when you duplicate same WHERE condition twice, query will get executed 40 times faster).

Sent some analyze for these 2 patterns on email, hopefully there's some usable info

May be we should add an optimizer switch to disable this optimization? igor, what do you think?

https://github.com/MariaDB/server/commit/eb0cf76f7dd

Perhaps setting the switch to off until MDEV-20109 is fixed would be better?