[MDEV-28928] Plan selection takes 2+ times longer than before MDEV-28852 Created: 2022-06-22  Updated: 2022-07-20  Resolved: 2022-07-20

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: N/A
Fix Version/s: 10.10.0

Type: Bug Priority: Critical
Reporter: Elena Stepanova Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Problem/Incident
causes MDEV-28929 Plan selection takes forever with MDE... Closed
is caused by MDEV-28852 Improve optimization of joins with ma... Closed

 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.

 Comments   
Comment by Michael Widenius [ 2022-06-27 ]

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.

Comment by Elena Stepanova [ 2022-06-28 ]

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

Comment by Sergei Petrunia [ 2022-07-20 ]

Fixed by fix for MDEV-28929 in the feature tree.

Generated at Thu Feb 08 10:04:32 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.