Re: GEQO: ERX - Mailing list pgsql-hackers

Hello Adriano,
thank you very much for posting your patch. I think it will help to make
further work easier, too. I hope you don't mind when I ask you some
questions.

When you said that this new approach is worse or equal than GEQO, did
you refer to performance or to the quality of results?
Why do you think that compressed annealing might be the better approach?

TIA and best regards,
Tobias Zahn


Adriano Lange schrieb:
> Robert Haas escreveu:
>> On Wed, May 13, 2009 at 4:14 PM, Tobias Zahn <tobias-zahn@arcor.de>
>> wrote:
>>> Hello,
>>> thank you for posting the paper, it was quite interesting to read. I
>>> think it would be a good idea to give the two-phase optimization a try.
>>> As far as I know and understand the current (geqo) optimizer source,
>>> many important parts are already there. For example, we can calculate
>>> the costs of a given join order. Therefore, it would only be necessary
>>> to write an algorithm witch chooses the right input for the cost
>>> function.
>>> I would be interested in your opinion.
>>
>> I'm very interested in any improvements we can make to planning large
>> join nests.  Unfortunately the paper seems to conclude that it's not
>> really feasible to use heuristics, as had been my hope, but I'd be
>> very interested in any other approaches we can come up with.  I
>> probably do not have time to implement anything myself, but I'm happy
>> to help with ideas and code review.
>>
> 
> I implemented the 2PO algorithm last month but I didn't have much time
> to do an extensive test and to comment all code. The code was posted in
> this list in a previous thread. In that occasion, I was interested in a
> kind of cache structure to avoid the constructing a complete RelOptInfo
> from scratch every time when the cheapest total_cost must be calculated
> (this occur in GEQO).
> 
> I’m sending a patch for the 8.3 release.
> 
> I also changed some GUC variables to facilitate some tests:
> (remove) geqo
> (remove) geqo_threshold
> (add) ljqo_enable (bool) – activate Large Join Query Optimizer
> (add) ljqo_algorithm {geqo|twopo}
> (add) ljqo_threshold (int) – like geqo_threshold
> (add) twopo_heuristic_states (bool) – initial heuristic states
> (add) twopo_ii_stop (int) – II phase loop
> (add) twopo_sa_phase (bool) – enable SA phase
> (add) twopo_sa_initial_temperature (float)
> (add) twopo_sa_temperature_reduction (float)
> (add) twopo_sa_equilibrium (int)
> 
> In my little tests, this algorithm seems equal or worse than geqo,
> except when using heuristic in order to bias the initial state. Maybe
> some tunings are needed but I prefer spend yet some time reading more
> about the compressed annealing, cited in TODO list. Anyway, I think that
> to build another annealing-like algorithm might be easier if some
> structures and functions in 2PO source code are correct.
> 
> Sincerely,
> 
> Adriano Lange
> 
>