Re: GEQO: ERX - Mailing list pgsql-hackers

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