Looking ahead makes query plans robust

Abstract

Query optimizers and query execution engines cooperate to deliver high performance on complex analytic queries. Typically, the optimizer searches through the plan space and sends a selected plan to the execution engine. However, optimizers may at times miss the optimal plan, with sometimes disastrous impact on performance. In this paper, we develop the notion of robustness of a query evaluation strategy with respect to a space of query plans. We also propose a novel query execution strategy called Lookahead Information Passing (LIP) that is robust with respect to the space of (fully pipeline-able) left-deep query plan trees for in-memory star schema data warehouses. LIP ensures that execution times for the best and the worst case plans are far closer than without LIP. In fact, under certain assumptions of independent and uniform distributions, any plan in that space is theoretically guaranteed to execute in near-optimal time. LIP ensures that the execution time for every plan in the space is nearly-optimal. In this paper, we also evaluate these claims using workloads that include skew and correlation. With LIP we make an initial foray into a novel way of thinking about robustness from the perspective of query evaluation, where we develop strategies (like LIP) that collapse plan sub-spaces in the overall global plan space.

Bitvector-aware Query Optimization for Decision Support Queries (extended version)

“…Efficient full integration of bitvector filters for commercial databases with various architectures remains an open problem. Since our analysis shows that bitvector filters result in more robust query plans, which is also observed in [38], understanding how bitvector filters impact robust and interleaved query optimization is also an interesting direction.…”

“…In this work, we adapt the cost function used in analyzing join order enumeration [28,30] for our analysis. We analyze the space of right deep trees without cross products, which has been shown to be a favorable plan space for decision support queries and bitvector filters [12,17,38].…”

“…Lookahead Information Passing (LIP) [38] is the closest prior work to our work. LIP studies the star schema where Bloom filters created from dimension tables are all applied to the fact table.…”

“…Surprisingly, prior work has shown that, under limited conditions, different join orders results in similar execution cost when bitvector filters are used. LIP [38] analyzes the impact of Bloom filters for star schema with a specific type of left deep trees, where the fact table is at the bottom. They observe that, if bitvector filters created from dimension tables are pushed down to the fact table upfront, plans with different permutations of dimension tables have similar cost.…”

In-Depth Analysis of Energy Efficiency Related Factors in Commercial Buildings Using Data Cube and Association Rule Mining

“…The star schema resembles a starburst with the dimension tables shown in an outspread pattern around the central fact table. It is defined by dimensions and facts: (1) dimensions mean the perspectives and objects with respect to what a user hopes to keep records; and (2) facts include the names of the facts or measures as well as the keys to each of the related dimension tables [26,27].…”