Plan Bouquets: A Fragrant Approach to Robust Query Processing

Dutt, Anshuman and Haritsa, Jayant R (2016) Plan Bouquets: A Fragrant Approach to Robust Query Processing. In: ACM TRANSACTIONS ON DATABASE SYSTEMS, 41 (2).

PDF ACM_Tra_Dat_Sys_41-2_11_2016.pdf - Published Version Restricted to Registered users only Download (2MB) | Request a copy

Abstract

Identifying efficient execution plans for declarative OLAP queries typically entails estimation of several predicate selectivities. In practice, these estimates often differ significantly from the values actually encountered during query execution, leading to poor plan choices and grossly inflated response times. We propose here a conceptually new approach to address this classical problem, wherein the compile-time estimation process is completely eschewed for error-prone selectivities. Instead, from the set of optimal plans in the query's selectivity error space, a limited subset, called the ``plan bouquet,'' is selected such that at least one of the bouquet plans is 2-optimal at each location in the space. Then, at run time, a sequence of cost-budgeted executions from the plan bouquet is carried out, eventually finding a plan that executes to completion within its assigned budget. The duration and switching of these executions is controlled by a graded progression of isosurfaces projected onto the optimal performance profile. We prove that this construction results, for the first time, in guarantees on worst-case performance sub-optimality. Moreover, it ensures repeatable execution strategies across different invocations of a query. We then present a suite of enhancements to the basic plan bouquet algorithm, including randomized variants, that result in significantly stronger performance guarantees. An efficient isosurface identification algorithm is also introduced to curtail the bouquet construction overheads. The plan bouquet approach has been empirically evaluated on both PostgreSQL and a commercial DBMS, over the TPC-H and TPC-DS benchmark environments. Our experimental results indicate that it delivers substantial improvements in the worst-case behavior, without impairing the average-case performance, as compared to the native optimizers of these systems. Moreover, it can be implemented using existing optimizer infrastructure, making it relatively easy to incorporate in current database engines. Overall, the plan bouquet approach provides novel performance guarantees that open up new possibilities for robust query processing.

Item Type:	Journal Article
Publication:	ACM TRANSACTIONS ON DATABASE SYSTEMS
Publisher:	ASSOC COMPUTING MACHINERY
Additional Information:	Copy right for this article belongs to the ASSOC COMPUTING MACHINERY, 2 PENN PLAZA, STE 701, NEW YORK, NY 10121-0701 USA
Department/Centre:	Division of Electrical Sciences > Computer Science & Automation
Date Deposited:	24 Aug 2016 09:53
Last Modified:	24 Aug 2016 09:53
URI:	http://eprints.iisc.ac.in/id/eprint/54466

Actions (login required)

View Item