R* optimizer validation and performance evaluation for local queries
SIGMOD '86 Proceedings of the 1986 ACM SIGMOD international conference on Management of data
Optimization of dynamic query evaluation plans
SIGMOD '94 Proceedings of the 1994 ACM SIGMOD international conference on Management of data
A tight analysis of the greedy algorithm for set cover
STOC '96 Proceedings of the twenty-eighth annual ACM symposium on Theory of computing
Efficient mid-query re-optimization of sub-optimal query execution plans
SIGMOD '98 Proceedings of the 1998 ACM SIGMOD international conference on Management of data
A threshold of ln n for approximating set cover
Journal of the ACM (JACM)
Least expected cost query optimization: an exercise in utility
PODS '99 Proceedings of the eighteenth ACM SIGMOD-SIGACT-SIGART symposium on Principles of database systems
Least expected cost query optimization: what can we expect?
Proceedings of the twenty-first ACM SIGMOD-SIGACT-SIGART symposium on Principles of database systems
Computers and Intractability: A Guide to the Theory of NP-Completeness
Computers and Intractability: A Guide to the Theory of NP-Completeness
Access path selection in a relational database management system
SIGMOD '79 Proceedings of the 1979 ACM SIGMOD international conference on Management of data
Dynamic Query Optimization in Rdb/VMS
Proceedings of the Ninth International Conference on Data Engineering
Design and Analysis of Parametric Query Optimization Algorithms
VLDB '98 Proceedings of the 24rd International Conference on Very Large Data Bases
LEO - DB2's LEarning Optimizer
Proceedings of the 27th International Conference on Very Large Data Bases
VLDB '92 Proceedings of the 18th International Conference on Very Large Data Bases
A characterization of the sensitivity of query optimization to storage access cost parameters
Proceedings of the 2003 ACM SIGMOD international conference on Management of data
Analyzing plan diagrams of database query optimizers
VLDB '05 Proceedings of the 31st international conference on Very large data bases
Parametric query optimization for linear and piecewise linear cost functions
VLDB '02 Proceedings of the 28th international conference on Very Large Data Bases
AniPQO: almost non-intrusive parametric query optimization for nonlinear cost functions
VLDB '03 Proceedings of the 29th international conference on Very large data bases - Volume 29
Identifying robust plans through plan diagram reduction
Proceedings of the VLDB Endowment
Efficiently approximating query optimizer plan diagrams
Proceedings of the VLDB Endowment
Query optimizers: time to rethink the contract?
Proceedings of the 2009 ACM SIGMOD International Conference on Management of data
On the stability of plan costs and the costs of plan stability
Proceedings of the VLDB Endowment
The Picasso database query optimizer visualizer
Proceedings of the VLDB Endowment
Hi-index | 0.00 |
A "plan diagram" is a pictorial enumeration of the execution plan choices of a database query optimizer over the relational selectivity space. We have shown recently that, for industrial-strength database engines, these diagrams are often remarkably complex and dense, with a large number of plans covering the space. However, they can often be reduced to much simpler pictures, featuring significantly fewer plans, without materially affecting the query processing quality. Plan reduction has useful implications for the design and usage of query optimizers, including quantifying redundancy in the plan search space, enhancing useability of parametric query optimization, identifying error-resistant and least-expected-cost plans, and minimizing the overheads of multi-plan approaches. We investigate here the plan reduction issue from theoretical, statistical and empirical perspectives. Our analysis shows that optimal plan reduction, w.r.t. minimizing the number of plans, is an NP-hard problem in general, and remains so even for a storage-constrained variant. We then present a greedy reduction algorithm with tight and optimal performance guarantees, whose complexity scales linearly with the number of plans in the diagram for a given resolution. Next, we devise fast estimators for locating the best tradeoff between the reduction in plan cardinality and the impact on query processing quality. Finally, extensive experimentation with a suite of multi-dimensional TPCH-based query templates on industrial-strength optimizers demonstrates that complex plan diagrams easily reduce to "anorexic" (small absolute number of plans) levels incurring only marginal increases in the estimated query processing costs.