[MDEV-30759] Slow query due to Optimizer picking the wrong table for join order Created: 2023-03-01  Updated: 2023-03-01  Resolved: 2023-03-01

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

Type: Bug Priority: Blocker
Reporter: Patrick Thomas Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None
Environment:

Ubuntu 22.04 (10.6.12-MariaDB-0ubuntu0.22.04.1-log)

Attachments: PNG File ANALYZE_Result.png     File mariadb-10.3.37.json     PNG File mariadb-10.3.37.png     File mariadb-10.6.12.json     PNG File mariadb-10.6.12.png     File test-db.sql.bz2    

 Description   

We have two big tables (> 1 and 10 mio entries). Query looks like this:

EXPLAIN SELECT
 
	`assets`.*,
 
	`asset_media`.`media_id` AS `pivot_media_id`,
 
	`asset_media`.`asset_id` AS `pivot_asset_id` 
FROM
 
	`assets`
 
	INNER JOIN `asset_media` ON `assets`.`id` = `asset_media`.`asset_id` 
WHERE
 
	`asset_media`.`media_id` = 479877 
	AND `assets`.`deleted_at` IS NULL

After upgrade to MariaDB 10.6 (From 10.3) the query optimizer is not able to choose the correct order (See screenshots and dump). Dump needed to get reduced to get it uploaded to Jira (10MB).

Expected:
First use `asset_media` and then `assets` table.

Result:
Wrong order

This causes our statement(s) to significantly loose performance (from 0.5sec to 9sec)

 Comments   
Comment by Daniel Black [ 2023-03-01 ]

Does running analyze table on both result in a different query plan?

Can you attach a analyze format=json $query as text for the query and datasets you have running.

Comment by Patrick Thomas [ 2023-03-01 ]

I attached the analyse results

Comment by Patrick Thomas [ 2023-03-01 ]

ANALYZE TABLE assets, asset_media PERSISTENT FOR ALL;

Resulted in attached screenshot which seem to have fixed the problem!!

Thanks so much!

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