Re: On query rewrite - Mailing list pgsql-hackers

>>>>> "Alvaro" == Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
   >> I understand that there is a cost-based optimizer anyway that   >> does the planning and selects the right plan
..but does this   >> come _after_ all these transformations ? Or does it happen   >> along with the transformations ?
 
   Alvaro> No, there's no rules optimizer, only the cost-based one   Alvaro> you already know of.

Okay ...
   Alvaro> The query's path is SQL -> parse -> rewrite -> optimize ->   Alvaro> execute   >>  Can you please point me
tothe code that indeed does such   >> transformations ?
 
   Alvaro> Sorry, I don't know the optimizer code.  You can find a   Alvaro> lot of detail in backend/optimizer/README.
Probably you   Alvaro> want to look at what happens to JOIN_IN nodes, for   Alvaro> example, regarding the conversion
ofa
 

Couldn't find the README but I'm operating on an older souce base. 

Anyway, thanks for the tips. I think I found what I'm looking for: the
function is probably pull_up_subqueries .. and what it tries to do is
check if a subquery is "simple" or not .. "simple" means not having
Aggs or something more complicated. If that's the case, and if some
NULLable conditions are safe then, the subquery gets pulled up -
essentially, a subquery to join transformation.


Now my next question is more subtle .. 

Are these alternatives (pulling up vs not pulling up subqueries)
considered in different plans ? 

Because, if not, it seems that this is really just a query rewrite
.. it's just that it happens as part of the "optimizer" component. In
fact, there is an implicit rule here in operation (by rule, I don't
mean a pgsql RULE).

Are more such transformations spread around the optimizer component ?
Is there any reason to have it integrated with the planner as opposed
to having it be part of the rewrite component (apart from historical
.. plus the code is solid and works etc.) ? 

Sorry for all the questions .. as I stated before I'm working on a
chapter of a text book that is a case-study of pgsql (the 4th ed
contains case studies of Oracle, DB2 and MSSQL) and the 5th ed is
gonna have pgsql. 

Another question about regular RULE processing .. suppose after
applying a rule the resultant query tree is eligible for another rule,
does pgsql's rule system keep iterating over and over until it reaches
a fixed point or is there some heuristic in operation (just apply the
rules twice ..) ? From my cursory inspection of the code it looks like
the latter, but I'd like to know for sure. 

Thanks ! 

-- 
Pip-pip
Sailesh
http://www.cs.berkeley.edu/~sailesh