Devel-Op: An Optimizer Development Environment

Abstract

Recent advances in underlying architectures of database management systems (DBMS) have motivated the redesign of key DBMS components such as the query optimizer (optimizer). Optimizers are hard to implement well as they are inherently complex, error-prone, and difficult to maintain. Debugging an optimizer is another complex and time-consuming task in optimizer development. But there exists no software engineering tools to facilitate their development.

In this thesis, we present a two-pronged approach to facilitate optimizer development: 1. facilitate the specification and generation of optimizer components that can be tested under a variety of conditions and over a variety of queries, and 2. provide tools for profiling and debugging generated optimizers that include visualization and debugger-like optimizer tracing at various granularities.

We introduce our development environment for query optimizers (A [Devel]opment Envi-ronment for [Op]timizers, or Devel-Op) consisting of a suite of tools designed to facilitate the Profiling and Rapid Prototyping of optimizer components. Our current version of Devel-Op supports the debugging and profiling optimizers and their components, and permits declarative specification and generation of two key optimizer components: the Logical Plan Enumerator (LPE) and the Physical Plan Generator (PPG). We present our work that enables profiling and debugging of optimizer components by describing the [D]evel-Op [O]ptimizer [V]isualization [E]nvironment (DOVE) and demonstrating possible debugging traces in optimizer development. We present our work that facilitates specification and generation of optimizer components by describing tools for declaratively specifying enumeration and plan generation optimizer components and compilers for generating optimizer components from these specifications. We also present the usability experiments we conducted to evaluate the usability of Devel-Op tools comparing with another debugger GDB.