Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
Description
The test uses all default server startup options, loads original data set from MDEV-28073 (xpo_full_testcase.sql), runs ANALYZE PERSISTENT FOR ALL on all tables, and executes EXPLAIN for a query structured identically to MDEV-28073 (minus a couple of last JOINs), but the tables and fields in the query are mixed up – that is, different tables and fields from the dataset appear in different JOIN positions and conditions. The exact query will be provided.
The query was executed with EXPLAIN FORMAT=JSON on several different versions/builds for comparison. All builds are non-debug.
|
10.10 main branch d371e352 |
MariaDB [test]> EXPLAIN FORMAT=JSON SELECT * FROM ((((((((((((((((
|
...
|
1 row in set (25.947 sec)
|
|
10.6 main branch 0e4cf497 |
MariaDB [test]> EXPLAIN FORMAT=JSON SELECT * FROM ((((((((((((((((
|
...
|
1 row in set (9.275 sec)
|
|
10.6.8 release |
MariaDB [test]> EXPLAIN FORMAT=JSON SELECT * FROM ((((((((((((((((
|
...
|
1 row in set (2 min 51.670 sec)
|
|
preview-10.10-optimizer f332260c98 |
MariaDB [test]> EXPLAIN FORMAT=JSON SELECT * FROM ((((((((((((((((
|
...
|
1 row in set (1 min 2.267 sec)
|
With optimizer_prune_level=2 (default) and optimizer_prune_level=1 the time on the above build is approximately the same.
So, the fastest is current 10.6 main (maybe it has a fix which isn't yet in 10.10 main). But even 10.10 main is over 2 times faster than preview-10.10-optimizer.
For an additional reference (since preview-10.10-optimizer contains two new features),
|
preview-10.10-optimizer, MDEV-28852 baseline (07a31de109) |
MariaDB [test]> EXPLAIN FORMAT=JSON SELECT * FROM ((((((((((((((((
|
...
|
1 row in set (28.092 sec)
|
This ^ is a build with MDEV-26278, but without MDEV-28852 – that is, a baseline for MDEV-28852. It takes roughly the same time as 10.10 main, so MDEV-26278 isn't a culprit.
Attachments
Issue Links
- causes
-
-
- Closed
-
- is caused by
-
MDEV-28852 Improve optimization of joins with many tables, including eq_ref tables
-
- Closed
-
Activity
Adding actual execution time upon monty's request. Same machine, both optimized non-debug builds. The machine is not tuned for precise benchmarking, the numbers are just for a rough comparison.
Preview build (preview-10.10-optimizer f332260c9)
EXPLAIN: 63.009 sec
SELECT: 63.057 sec
Baseline build (07a31de10)
EXPLAIN: 27.125 sec
SELECT: 27.542 sec
10.6 can for some bad queries be faster than 10.10 with new greedy optimizer enhancements.
It all depends on how likely the optimizer finds a good plan.
10.6 is totally depending on the number of rows in the table and how good this responds to the optimal plan.
10.10 is smarter as it optimise the plan according to tables current context (and just not the table sizes at the start of the plan) and in most cases should find a good plan first. Find a good plan allows the optimizer to get rid of bad partial plans fast while searching for the optimal plan.
What is important is to optimise for the normal case where most tables has some kind of index that can be used. In this case 10.10-optimizer should be superior in almost all cases.