MySQL Bugs: #115782: unfair comparison between materialization and EXISTS

Bug #115782	unfair comparison between materialization and EXISTS
Submitted:	6 Aug 2024 11:24	Modified:	6 Aug 2024 11:54
Reporter:	tianfeng li (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S3 (Non-critical)
Version:	8.0.30,8.4.0	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	Contribution

Description:
In the function compare_costs_of_subquery_strategies(), we have two cost calculations:

1. cost_of_materialization = materialization_cost + subq_executions * lookup_cost;
2. cost_of_EXISTS = subq_executions * saved_best_read;
These two costs are then compared to determine the subquery strategy.

However, when subq_executions is rather small, less than 1, which could occur in some cases where the subquery condition is attached to a table being EQ-joined, the cost_of_EXISTS would be less than cost_of_one_EXISTS (saved_best_read). As a result, the optimizer is very likely to choose an EXISTS subquery.

In the definitions of #1 and #2, cost_of_materialization always includes materialization_cost, which implicitly indicates that the subquery will be materialized at least once.

Therefore, I believe subq_executions should be adjusted to at least 1 when deciding the subquery strategy between materialization and EXISTS to ensure a fair comparison.

How to repeat:
see description.

Adjust the number of subquery evaluations to ensure that the EXISTS strategy is fairly comparable to the materialization strateg (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: 0001-Adjust-the-number-of-subquery-evaluations-to-ensure-.patch (application/octet-stream, text), 1.34 KiB.

Hello tianfeng li,

Thank you for the report and contribution.

regards,
Umesh