A Performance Study of Query Optimization Algorithms on a Database System Supporting Procedures
Anant Jhingran · 1988
Abstract relational model. POSTGRES allows fields of a relation to have pro-cedural (executable) objects. POSTQUEL is the query language supporting access to these fields, and in this paper we consider the optimizing process for such queries. The simplest algorithm for optimization assumes that the procedural objects are executed in full, whenever needed. As a refinement to this basic process, we pro-pose an algorithm wherein cost savings are achieved by modifying the procedural queries before executing them. In another direction of refinement, we consider the cach-ing of the materialized results. Two caching strategies-caching in tuples, and separate caching-are considered. The fifth algorithm is flattening, where a POSTQUEL query is modified into an equivalent flat query, and then optimized through a traditional optimizer. We study the relative performances of these algorithms under varying conditions and parameters. Our results show that caching wins when updates do not occur with a high frequency, and that separate caching is, in general, better than cach-ing in tuples. We further show that when the composition of the objects in the procedural field is predictable and parameterizable, flattening is a good option. 1.