Configuration-parametric query optimization for physical design tuning

Abstract

Automated physical design tuning for database systems has recently become an active area of research and development. Existing tuning tools explore the space of feasible solutions by repeatedly optimizing queries in the input workload for several candidate configurations. This general approach, while scalable, often results in tuning sessions waiting for results from the query optimizer over 90% of the time. In this paper we introduce a novel approach, called Configuration-Parametric Query Optimization, that drastically improves the performance of current tuning tools. By issuing a single optimization call per query, we are able to generate a compact representation of the optimization space that can then produce very efficiently execution plans for the input query under arbitrary configurations. Our experiments show that our technique speedsup query optimization by 30x to over 450x with virtually no loss in quality, and effectively eliminates the optimization bottleneck in existing tuning tools. Our techniques open the door for new, more sophisticated optimization strategies by eliminating the main bottleneck of current tuning tools.

Caching all plans with just one optimizer call

“…Typically the complexity overhead of using intersections outweigh the modest improvement in performance they provide [5]. We therefore focus on improving INUM's performance instead of extending it to include such complex index operations.…”

“…To reduce this overhead, designers aggressively prune candidate configurations through greedy algorithms, often reducing the quality of the final solution. Two recentlyproposed approaches reduce the overhead of optimizer invocation without aggressive premature pruning: parametric query optimization (C-PQO) [5] and caching partial query plan costs (INUM) [4].…”

Constrained physical design tuning

“…One approach is to use information about previous optimizations to infer, in some cases, the cost of a query under a given configuration without issuing an optimization call (examples of such techniques use atomic configurations [8] or a top-down relaxation approach [4]). A recent approach introduced in [7] results in accurate approximations of the cost of a query at very low overhead (typically orders of magnitude faster than a regular optimization call).…”

“…The server code-base was extended to support the techniques in [4,7] to provide what-if functionality and the ability to exploit local transformations. For our experiments we used a TPC-H database and workloads generated with the QGen utility 6 .…”

“…We used a 1GB TPC-H data and tuned a 22-query workload with both our framework and the relaxation approach of [4] augmented with the techniques of [7] so that both approaches rely on the same underlying query optimization strategy. We used three minutes for each tuning session, and simulated the approach in [4] with the following constraint specification:…”