Existing Query Optimization Research Articles

Bao

Recent efforts applying machine learning techniques to query optimization have shown few practical gains due to substantive training overhead, inability to adapt to changes, and poor tail performance. Motivated by these difficulties, we introduce Bao (the Bandit optimizer). Bao takes advantage of the wisdom built into existing query optimizers by providing per-query optimization hints. Bao combines modern tree convolutional neural networks with Thompson sampling, a well-studied reinforcement learning algorithm. As a result, Bao automatically learns from its mistakes and adapts to changes in query workloads, data, and schema. Experimentally, we demonstrate that Bao can quickly learn strategies that improve end-to-end query execution performance, including tail latency, for several workloads containing longrunning queries. In cloud environments, we show that Bao can offer both reduced costs and better performance compared with a commercial system.

RL_QOptimizer: A Reinforcement Learning Based Query Optimizer

With the current availability of massive datasets and scalability requirements, different systems are required to provide their users with the best performance possible in terms of speed. On the physical level, performance can be translated into queries’ execution time in database management systems. Queries have to execute efficiently (i.e. in minimum time) to meet users’ needs, which puts an excessive burden on the database management system (DBMS). In this paper, we mainly focus on enhancing the query optimizer, which is one of the main components in DBMS that is responsible for choosing the optimal query execution plan and consequently determines the query execution time. Inspired by recent research in reinforcement learning in different domains, this paper proposes A Deep Reinforcement Learning Based Query Optimizer (RL_QOptimizer), a new approach to find the best policy for join order in the query plan which depends solely on the reward system of reinforcement learning. The experimental results show that a notable advantage of the proposed approach against the existing query optimization model of PostgreSQL DBMS.

Towards multi-way join aware optimizer in SAP HANA

Existing binary join based plans may be suboptimal for important, emerging applications. Typical query optimizers enumerate plans using binary joins only. In this paper, we introduce the multi-way join aware optimizer in SAP HANA. The naive way to extend the existing query optimizer to be aware of multi-way joins ( m -way joins for short) is to enumerate m -way joins on top of a traditional binary join enumeration framework. However, many different binary joins correspond to the same m -way join. Thus, unnecessary join enumerations would be required for such naive integration. To solve this problem, we introduce the new concept of an m -way join unit and explain how the construction of join units is plugged into the SAP HANA query optimizer. We also provide a series of optimizer enhancements by exploiting m -way join unit characteristics. Using TPC-H and our customer workloads, we showcase the superiority of our m -way join aware optimizer.

Design and analysis of stochastic DSS query optimizers in a distributed database system

Query optimization is a stimulating task of any database system. A number of heuristics have been applied in recent times, which proposed new algorithms for substantially improving the performance of a query. The hunt for a better solution still continues. The imperishable developments in the field of Decision Support System (DSS) databases are presenting data at an exceptional rate. The massive volume of DSS data is consequential only when it is able to access and analyze by distinctive researchers. Here, an innovative stochastic framework of DSS query optimizer is proposed to further optimize the design of existing query optimization genetic approaches. The results of Entropy Based Restricted Stochastic Query Optimizer (ERSQO) are compared with the results of Exhaustive Enumeration Query Optimizer (EAQO), Simple Genetic Query Optimizer (SGQO), Novel Genetic Query Optimizer (NGQO) and Restricted Stochastic Query Optimizer (RSQO). In terms of Total Costs, EAQO outperforms SGQO, NGQO, RSQO and ERSQO. However, stochastic approaches dominate in terms of runtime. The Total Costs produced by ERSQO is better than SGQO, NGQO and RGQO by 12%, 8% and 5% respectively. Moreover, the effect of replicating data on the Total Costs of DSS query is also examined. In addition, the statistical analysis revealed a 2-tailed significant correlation between the number of join operations and the Total Costs of distributed DSS query. Finally, in regard to the consistency of stochastic query optimizers, the results of SGQO, NGQO, RSQO and ERSQO are 96.2%, 97.2%, 97.45 and 97.8% consistent respectively.

Dynamic and fast processing of queries on large-scale RDF data

As RDF data continue to gain popularity, we witness the fast growing trend of RDF datasets in both the number of RDF repositories and the size of RDF datasets. Many known RDF datasets contain billions of RDF triples (subject, predicate and object). One of the grant challenges for managing these huge RDF data is how to execute RDF queries efficiently. In this paper, we address the query processing problems against the billion triple challenges. We first identify some causes for the problems of existing query optimization schemes, such as large intermediate results, initial query cost estimation errors. Then, we present our block-oriented dynamic query plan generation approach powered with pipelining execution. Our approach consists of two phases. In the first phase, a near-optimal execution plan for queries is chosen by identifying the processing blocks of queries. We group the join patterns sharing a join variable into building blocks of the query plan since executing them first provides opportunities to reduce the size of intermediate results generated. In the second phase, we further optimize the initial pipelining for a given query plan. We employ optimization techniques, such as sideways information passing and semi-join, to further reduce the size of intermediate results, improve the query processing cost estimation and speed up the performance of query execution. Experimental results on several RDF datasets of over a billion triples demonstrate that our approach outperforms existing RDF query engines that rely on dynamic programming based static query processing strategies.

Processing SPARQL queries with regular expressions in RDF databases

BackgroundAs the Resource Description Framework (RDF) data model is widely used for modeling and sharing a lot of online bioinformatics resources such as Uniprot (dev.isb-sib.ch/projects/uniprot-rdf) or Bio2RDF (bio2rdf.org), SPARQL - a W3C recommendation query for RDF databases - has become an important query language for querying the bioinformatics knowledge bases. Moreover, due to the diversity of users’ requests for extracting information from the RDF data as well as the lack of users’ knowledge about the exact value of each fact in the RDF databases, it is desirable to use the SPARQL query with regular expression patterns for querying the RDF data. To the best of our knowledge, there is currently no work that efficiently supports regular expression processing in SPARQL over RDF databases. Most of the existing techniques for processing regular expressions are designed for querying a text corpus, or only for supporting the matching over the paths in an RDF graph.ResultsIn this paper, we propose a novel framework for supporting regular expression processing in SPARQL query. Our contributions can be summarized as follows. 1) We propose an efficient framework for processing SPARQL queries with regular expression patterns in RDF databases. 2) We propose a cost model in order to adapt the proposed framework in the existing query optimizers. 3) We build a prototype for the proposed framework in C++ and conduct extensive experiments demonstrating the efficiency and effectiveness of our technique.ConclusionsExperiments with a full-blown RDF engine show that our framework outperforms the existing ones by up to two orders of magnitude in processing SPARQL queries with regular expression patterns.

Query Processing Using Distance Oracles for Spatial Networks

The popularity of location-based services and the need to do real-time processing on them has led to an interest in performing queries on transportation networks, such finding shortest paths and finding nearest neighbors. The challenge here is that the efficient execution of spatial operations usually involves the computation of distance along a spatial network instead of as the crow flies, which is not simple. Techniques are described that enable the determination of the network distance between any pair of points (i.e., vertices) with little O(n) space rather than having to store the n2 distances between all pairs. This is done by being willing to expend a bit more time to achieve this goal such O(log n) instead of O(1), well by accepting an error e in the accuracy of the distance that is provided. The strategy that is adopted reduces the space requirements and is based on the ability to identify groups of source and destination vertices for which the distance is approximately the same within some e. The reductions are achieved by introducing a construct termed a distance oracle that yields an estimate of the network distance (termed the e-approximate distance) between any two vertices in the spatial network. The distance oracle is obtained by showing how to adapt the well-separated pair technique from computational geometry to spatial networks. Initially, an e-approximate distance oracle of size O(n/(e2)) is used that is capable of retrieving the approximate network distance in O(log n) time using a B-tree. The retrieval time can be theoretically reduced further to O(1) time by proposing another e-approximate distance oracle of size O((n log n)/(e2)) that uses a hash table. Experimental results indicate that the proposed technique is scalable and can be applied to sufficiently large road networks. For example, a 10-percentapproximate oracle (e = 0.1) on a large network yielded an average error of 0.9 percent with 90 percent of the answers having an error of 2 percent or less and an average retrieval time of 68 μ seconds. The fact that the network distance can be approximated by one value is used to show how a number of spatial queries can be formulated using appropriate SQL constructs and a few built-in primitives. The result is that these operations can be executed on almost any modern database with no modifications, while taking advantage of the existing query optimizers and query processing strategies.

Depth estimation for ranking query optimization

A relational ranking query uses a scoring function to limit the results of a conventional query to a small number of the most relevant answers. The increasing popularity of this query paradigm has led to the introduction of specialized rank join operators that integrate the selection of top tuples with join processing. These operators access just "enough" of the input in order to generate just "enough" output and can offer significant speed-ups for query evaluation. The number of input tuples that an operator accesses is called the input depth of the operator, and this is the driving cost factor in rank join processing. This introduces the important problem of depth estimation, which is crucial for the costing of rank join operators during query compilation and thus for their integration in optimized physical plans. We introduce an estimation methodology, termed Deep, for approximating the input depths of rank join operators in a physical execution plan. At the core of Deep lies a general, principled framework that formalizes depth computation in terms of the joint distribution of scores in the base tables. This framework results in a systematic estimation methodology that takes the characteristics of the data directly into account and thus enables more accurate estimates. We develop novel estimation algorithms that provide an efficient realization of the formal Deep framework, and describe their integration on top of the statistics module of an existing query optimizer. We validate the performance of Deep with an extensive experimental study on data sets of varying characteristics. The results verify the effectiveness of Deep as an estimation method and demonstrate its advantages over previously proposed techniques.

Piggyback Statistics Collection for Query Optimization: Towards a Self-Maintaining Database Management System

A database management system (DBMS) performs query optimization based on statistical information about data in the underlying database. Out-of-date statistics may lead to inefficient query processing in the system. The existing utility method, which collects statistics in batch mode, suffers from drawbacks such as heavy administrative burden, high system load and tardy updates. In this paper, we study approaches to performing statistical analysis on the fly during query execution, taking advantage of data already resident in main memory. We propose a framework for on-the-fly statistics collection, which we term piggybacking, and analyze the tradeoffs of piggybacking various statistics collection techniques on top of query execution plans. We present a multiple-granularity interleaving algorithm to integrate a set of piggyback operations with an execution plan, and show how the algorithm can be incorporated into an existing query optimizer. Our experiments demonstrate that useful statistics can be obtained via the piggyback method with a small overhead.

An application of fragmentation transparency in a distributed database system: A case study

Distributed database theory for a single view of a distributed database consisting of homogeneous relational databases has been known for over a decade. The application of some of this theory in commercial products has not been established. Although some commercial distributed database products support naming and location transparency, these products do not provide full support for fragmentation transparency. Providing support for declaring and reasoning about fragments requires adding data definition capabilities and updating complex query optimizers. This article presents a syntax for the specification of fragmentation and describes a prototype for reasoning about the fragmentation criteria. This prototype examines the feasibility of not having to update the existing query optimizer by taking a distributed SQL query and fragmentation specification and generating a sequence of SQL queries expressed over the fragments, which is then input to the established query optimizer. This article provides a case study of this approach on a commercial distributed database product. A benchmark for distributed databases for decision support, known as D 3S, is introduced and used in the case study. The study shows that a two-step process of first optimizing with respect to the fragmentation criteria, followed by the existing distributed query optimization, results in performant query evaluation plans.

Reducing multidatabase query response time by tree balancing

Execution of multidatabase queries differs from that of traditional queries in that sort merge and hash joins are more often favored, as nested loop join requires repeated accesses to external data sources. As a consequence, left deep join trees obtained by traditional (e.g., System-R style) optimizers for multidatabase queries are often suboptimal, with respect to response time, due to the long delay for a sort merge (or hash) join node to produce its last result after the subordinate join node did. In this paper, we present an optimization strategy that first produces an optimal left deep join tree and then reduces the response time using simple tree transformations. This strategy has the advantages of guaranteed minimum total resource usage, improved response time, and low optimization overhead. We describe a class of basic transformations that is the cornerstone of our approach. Then we present algorithms that effectively apply basic transformations to balance a left deep join tree, and discuss how the technique can be incorporated into existing query optimizers.