Building query optimizers with combinators
Mitch Cherniack, Stan Zdonik · 1999
Query optimizers generate plans to retrieve data requested by queries. Optimizers are hard to build because for any given query, there can be a prohibitively large number of plans to choose from. Typically, the complexity of optimization is handled by dividing optimization into two phases: a heuristic phase (called query rewriting) that narrows the space of plans to consider, and a cost-based phase that compares the relative merits of plans that lie in the narrowed space. The goal of query rewriting is to transform queries into equivalent queries that are more amenable to plan generation. This process has proven to be error-prone. Rewrites over nested queries and queries returning duplicates have been especially problematic, as evidenced by the well-known COUNT bug of the unnesting rewrites of Kim. The advent of object-oriented and object-relational databases only exacerbates this issue by introducing more complex data and by implication, more complex queries and query rewrites. This thesis addresses the correctness issue for query rewriting. We introduce a novel framework (COKO-KOLA) for expressing query rewrites that can be verified with an automated theorem prover. At its foundation lies KOLA: our combinator-based query algebra that permits expression of simple query rewrites (rewrite rules) without imperative code. While rewrite rules are easily verified, they lack the expressivity to capture many query rewrites used in practice. We address this issue in two ways: (1) We introduce a language (COKO) to express complex query transformations using KOLA rule sets and an algorithm to control rule firing. COKO supports expression of query rewrites that are too general to be expressed with rewrite rules alone. (2) We extend KOLA to permit expression of rewrite rules whose firing requires inferring semantic conditions. This extension permits expression of query rewrites that are too specific to be expressed with rewrite rules alone. The recurring theme of this work is that all of the proposed techniques are made possible by a combinator-based representation of queries.