Make subqueries in FROM clause faster

Convert subqueries in FROM clause to joins or/and add proper indexes to the

created temporary tables.



Maybe have an optimizer option to continue materialising tables as this

may be the most efficient solution for some queries.



== Specification substitute ==



As of Riga DevConf, we have 



1. FROM-subqueries that satisfy VIEW mergeability criteria are

(unconditionally) processed in the same way as an equvalent VIEW 

reference to equivalent VIEW would have been processed:

- subquery is converted into an inner join nest 

- subquery's WHERE clause becomes inner join nest's ON expression

- (and then the optimizer's standard routine will open the inner join nest

  and "dissolve" its tables in the set of upper query's tables)



2. All other kinds of FROM subqueries are processed with a modified

materialization procedure.



The difference from regular FROM subquery materialization is that



a. Subquery materialization is performed at execution phase (before it was

performed early, in open_tables(), which caused undesired effects like

EXPLAIN statements executing the FROM subqueries and so forth).



b. The materialized table has indexes. The set of indexes is a heuristic

guess which is based on the following considerations:



for each table OT in the parent select

{

  Walk though the subquery's select list and find elements inner_expr_i 

    that participate in an "inner_expr_i = OT.column" equality in the parent

    select's WHERE clause.

    (The equality may be either explicitly present in the WHERE or inferrable

     via equality propagation).



  Add an index on the list of {inner_expr_i};

}



The motive behind the above scheme is that we should create indexes that

would allow ref access lookups into the materialized table based on a single

outer select's table.

You must be logged in to tag this worklog