On the Production of Anorexic Plan Diagrams

Harish, D and Darera, Pooja N and Haritsa, Jayant R (2007) On the Production of Anorexic Plan Diagrams. In: VLDB '07 Proceedings of the 33rd international conference on Very large data bases, September 2007.

PDF On_the_Production.pdf - Published Version Restricted to Registered users only Download (455kB) | Request a copy

Abstract

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.

Item Type:	Conference Paper
Publisher:	ACM Press
Additional Information:	Copyright of this article belongs to ACM Press.
Department/Centre:	Division of Electrical Sciences > Computer Science & Automation
Date Deposited:	14 Oct 2011 10:11
Last Modified:	14 Oct 2011 10:11
URI:	http://eprints.iisc.ac.in/id/eprint/41457

Actions (login required)

View Item