Predicate Migration: Optimizing Queries with Expensive Predicates

The traditional focus of relational query optimization schemes has been on the choice of join methods and join orders. Restrictions have typically been handled in query optimizers by “predicate pushdown” rules, which apply restrictions in some random order before as many joins as possible. These rules work under the assumption that restriction is essentially a zero-time operation. However, today’s extensible and object-oriented database systems allow users to define time-consuming functions, which may be used in a query’s restriction and join predicates. Furthermore, SQL has long supported subquery predicates, which may be arbitrarily time-consuming to check. Thus restrictions should not be considered zero-time operations, and the model of query optimization must be enhanced. In this paper we develop a theory for moving expensive predicates in a query plan so that the total cost of the plan — including the costs of both joins and restrictions — is minimal. We present an al...

• Type: Conference
• Year: 1993
• Where: SIGMOD
• Authors: Joseph M. Hellerstein, Michael Stonebraker
• Keywords: Database, Expensive Predicates, Query Optimization, Query Optimizer, SIGMOD 1993