Improving Worst-case Bounds for Plan Bouquet based Techniques

Given an SQL query, current database systems execute it using a least cost plan which is largely based on estimates of predicate selectivities. Due to insufficient statistics and invalid assumptions, errors in estimates can lead to highly sub-optimal plans. In the paper[1], a strategy named “Plan Bouquets” has been proposed which provides guarantees on the worst case execution performance which does not rely on the estimates of predicate selectivities. The PlanBouquet algorithm, in its basic form, implicitly discovers predicate selectivities by observing the completion status of a sequence of cost-budgeted plan executions. Our contribution includes improving two variants of PlanBouquet. In the first contribution, we analyze this “non-intrusive” bouquet technique in presence of assumptions on the acclivities of cost functions which generally hold in practice. Further, we show that we can achieve significant reduction in the preprocessing time and will get upper bound on worst-case performance which is independent of the plan densities. Next, we investigate an intrusive variant of PlanBouquet named SpillBound[2], which changes the plan execution component and gives worst-case performance bound of O(D), which is only dependent on D, the dimensionality of selectivity space. We propose Opt-SB, which dynamically optimizes SpillBound, such that, the worst-case bound oscillates between O(D) and O(D) based on the optimizer’s behaviour profile within the selectivity space.