Share on Facebook Tweet on Twitter Share on LinkedIn Share by email
Optimization of Queries with User-defined Predicates

Surajit Chaudhuri and Kyuseok Shim


Relational databases provide the ability to store user-defined functions and predicates which can be invoked in SQL queries. When evaluation of a user-defined predicate is relatively expensive, the traditional method of evaluating predicates as early as possible is no longer a sound heuristic. There are two previous approaches for optimizing such queries. However, none of these approaches is able to guarantee the optimal plan over the desired execution space. We present efficient techniques that are able to guarantee the choice of an optimal plan over the desired execution space. The naïve optimization algorithm is very general and therefore is most widely useful. The optimization algorithm with rank ordering improves upon the naïve optimization algorithm by exploiting the nature of the cost formulas for join methods and is polynomial in the number of user-defined predicates. We also propose pruning rules that significantly reduce the cost of searching execution space for both the naïve algorithm as well as the optimization algorithm with rank-ordering, without compromising the optimality. We also propose a conservative local heuristic that is even simpler but produces nearly optimal plans. We have implemented the algorithms by extending a System-R style optimizer. We present complexity analysis and experimental comparison of the algorithms.


Publication typeTechReport
InstitutionMicrosoft Research
PublisherMorgan Kaufmann Publishers
> Publications > Optimization of Queries with User-defined Predicates