[MDEV-387] Move the optimization of subqueries earlier, before make_join_select() Created: 2012-07-10  Updated: 2012-07-31  Due: 2012-07-10  Resolved: 2012-07-31

Status: Closed
Project: MariaDB Server
Component/s: None
Fix Version/s: 10.0.0

Type: Task Priority: Major
Reporter: Timour Katchaounov (Inactive) Assignee: Timour Katchaounov (Inactive)
Resolution: Won't Fix Votes: 0
Labels: None

Issue Links:
PartOf
is part of MDEV-83 Cost-based choice for the pushdown of... Stalled

 Description   

Subqueries in MariaDB 5.5/5.3 are optimized recursively close to the end of JOIN::optimize through the call to JOIN::optimize_unflattened_subqueries.

This turns out to be a problem for the implementation of MDEV-83 (Cost-based choice for the pushdown of expensive predicates to joined tables), because predicate pushdown is performed by make_join_select() which is called earlier than JOIN::optimize_unflattened_subqueries. If subqueries are optimized after make_join_select(), then their cost is unknown during predicate pushdown, and we cannot estimate if a subquery is expensive or not.

The goal of this task is to move subquery optimization before make_join_select.

 Comments   
Comment by Timour Katchaounov (Inactive) [ 2012-07-31 ]

Further testing of the implementation revealed that:

  • if subqueries are optimized before predicate pushdown they may become "cheap" (since any non-optimized subquery is considered expensive by default)
  • once a subquery is considered cheap, it will not be pushed to the partial join with the smallest cardinality
  • this defeats the purpose of mdev-83

In addition, later analysis of DBT3 uncovered that we must pre-optimize all subqueries already before optimizing the outer query. This is reflected in mdev-402.

Generated at Thu Feb 08 06:28:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.