Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.5.9
-
RHEL8
Description
The following type of query is using where in EXPLAIN instead of index for some large quantity of OR's (i.e. on 10.5.9 uses WHERE, on 10.3.x uses INDEX also on 10.5.9 takes many minutes, on 10.3.x takes seconds)
query: SELECT col2 FROM mytable WHERE col1=a OR's col1=b ... with lots of OR's
If we force the query to use the index, "FORCE INDEX(mytable_col1_idx')", then the 10.5.9 query will finish in time similar to 10.3.x
See attached files to reproduce.
Attachments
Activity
Suggested workaround: set eq_range_index_dive_limit to a value that's higher than the number of elements in the IN-list (5641). Or, set it it to 0, which was the default before MariaDB 10.4.2.
Note: MySQL-8 suffers from the same issue (with innodb: except when one runs the query after loading the data. After one runs ANALYZE TABLE, the bad query plan is chosen). PostgreSQL picks a good query plan, but it will switch to poor query plan if the query has more "col1=const" disjuncts.
Notes from the optimizer call:
One option we could use is to estimate a few ranges with records_in_range calls, and then extrapolate the result to all ranges.
This won't be 100% bulletproof, though (One can imagine cases where the optimizer would still pick the wrong plan).
Also, this will add quite a bit of complexity. Do we really need to add it to handle this case? Could it be that this is a niche case that's not worth it?
The cause
This bug is a consequence of eq_range_index_dive_limit optimization (let's denote it as EQ-OPT).
The goal of EQ-OPT was to speed up the query optimization (right! optimization, not execution) by using index statistics estimates (this is table-wide average, cheap to use but imprecise), instead of using so-called "records-in-range estimates" (more precise but expensive to get) for queries that scan a lot of equality ranges.
Using imprecise estimates can cause the optimizer to pick a bad query plan. It happens when the reality is far from the estimate.
Your query is an example of this. On average, a single col1=value matches 70 rows. However, the query scans "rare" values, where 5641 values produce 107 matching rows in total.
The impact
The optimizer will use bad query plans for workloads that scan the "outliers" - values that are much more or much less common than others.
The workaround
Setting the eq_range_index_dive_limit will cause the optimizer to spend more time but construct better query plans.
The estimate of 74 rows per lookup key agrees with the real dataset.
The Cardinality in the index statistics (23798) agrees with the actual cardinality value of 23765.
However the query itself will return only 107 rows.