Exhaustive greedy algorithm for optimizing intermediate result sizes of join queries

Abstract

The size of the intermediate results produced while executing queries has a direct impact on query optimizers. Larger size of intermediate results requires more memory usage and more computational power to evaluate their join predicates. Furthermore, if memory size is not big enough, secondary storage will be needed. This paper proposes the Exhaustive Greedy (EG) algorithm to optimize the intermediate result sizes of join queries. Exhaustive search and greedy algorithm are combined and modified to identify good join orders. Based on the similar concept of the Greedy Operator Ordering (GOO) algorithm, in order to determine join order selection at subsequent steps, our algorithm also updates join graphs to reflect new size of join nodes and new join selectivities of edges associated with the join nodes at each step. Experiments are conducted and the results reveal that the EG algorithm determines the good join orders. In addition, most intermediate result sizes of join queries estimated by the EG algorithm are comparable to the results estimated by the Exhaustive Search algorithm that is modified to update join graphs, we name it ESU algorithm.