Sub-query Based Approach for Robust Query Processing

Selectivity estimates play a critical role in SQL query optimization. They often (mis)lead the query optimizer to produce highly sub-optimal execution plans. Previously, “Plan Bouquet” [2] approach demonstrated that it is possible to guarantee upper bounds on execution sub-optimality – surprisingly, by choosing to discard compile-time estimates and discovering the selectivity values at run-time. Existing techniques based on this approach rely on an optimization-intensive preprocessing phase to deliver the promise of bounded sub-optimality, making them unsuitable for ad hoc queries. In addition, these techniques make selectivity independence assumption. Selectivity independence assumption says that the joint selectivity of a conjunction of query predicates is given by the product of their individual selectivities. All relational database engines make this assumption in their query optimizer and, is also one of the reasons for their sub-optimality. In this work, first, we argue that the preprocessing phase is not an integral part of plan bouquet approach and is actually wasteful in ad hoc query scenarios. We analyze and implement a revamped plan bouquet algorithm that retains the ability to provide sub-optimality guarantees with low optimization overhead. This is done by executing sub-queries with only one unknown join predicate in a cost budgeted manner. Second, we also exploit the topological structure of the input query graph to derive significantly improved sub-optimality bounds. Experiments with TPC-DS benchmark queries show that, compared to previous techniques, optimization overheads are enormously reduced and there is virtually no loss in the empirical sub-optimality performance. Finally, we extend this approach to work without selectivity independence assumption and also, give query graph dependent worst case sub-optimality bounds for it.