Query Plan Research Articles

SAQE

A private data federation enables clients to query the union of data from multiple data providers without revealing any extra private information to the client or any other data providers. Unfortunately, this strong end-to-end privacy guarantee requires cryptographic protocols that incur a significant performance overhead as high as 1,000 x compared to executing the same query in the clear. As a result, private data federations are impractical for common database workloads. This gap reveals the following key challenge in a private data federation: offering significantly fast and accurate query answers without compromising strong end-to-end privacy. To address this challenge, we propose SAQE, the Secure Approximate Query Evaluator, a private data federation system that scales to very large datasets by combining three techniques --- differential privacy, secure computation, and approximate query processing --- in a novel and principled way. First, SAQE adds novel secure sampling algorithms into the federation's query processing pipeline to speed up query workloads and to minimize the noise the system must inject into the query results to protect the privacy of the data. Second, we introduce a query planner that jointly optimizes the noise introduced by differential privacy with the sampling rates and resulting error bounds owing to approximate query processing. Our research shows that these three techniques are synergistic: sampling within certain accuracy bounds improves both query privacy and performance, meaning that SAQE executes over less data than existing techniques without sacrificing efficiency, privacy, or accuracy. Using our optimizer, we leverage this counter-intuitive result to identify an inflection point that maximizes all three criteria prior query evaluation. Experimentally, we show that this result enables SAQE to trade-off among these three criteria to scale its query processing to very large datasets with accuracy bounds dependent only on sample size, and not the raw data size.

AutoToken

Right-sizing resource allocation for big-data queries, particularly in serverless environments, is critical for improving infrastructure operational efficiency, capacity availability, query performance predictability, and for reducing unnecessary wait times. In this paper, we present AutoToken --- a simple and effective predictor for estimating the peak resource usage of recurring big data queries. It uses multiple query plan identifiers to identify recurring query templates and to learn models with the goal of reducing over-allocation in future instances of those queries. AutoToken is computationally light, for both training and scoring, is easily deployable at scale, and is integrated with the Peregrine workload optimization infrastructure at Microsoft. We extensively evaluate AutoToken on SCOPE jobs from our production clusters and show that it outperforms state-of-the-art solutions for peak resource estimation. We also discuss our plans towards supporting repeatable and extensible research on resource prediction for SCOPE jobs, including describing a simulation methodology for generating arbitrary-sized datasets with similar characteristics as the production datasets.

CrocodileDB in action

Existing stream processing and continuous query processing systems eagerly maintain standing queries by consuming all available resources to finish the jobs at hand, which can be a major source of wasting CPU cycles and memory resources. However, users sometimes do not need to see the up-to-date query result right after the data is ready, and thus allow a slackness of time before the result is returned, which provides new opportunities to avoid wasting resources. We proposed CrocodileDB, a resource-efficient database, where users specify a performance goal representing the maximally allowed slackness of time and the system generates a query plan to minimize resource consumption (e.g. memory consumption or CPU cycles) while meeting this performance goal at the same time. In this paper, we demonstrate how users interact with CrocodileDB and show how the time slackness enables our optimization of reducing CPU consumption: Incrementability-aware Query Processing (InQP). With the slackness specified by users, InQP can reduce computing resource waste by selectively deferring the execution of parts of a query that are not amenable to incremental executions (i.e. outputting tuples that can be deleted by later executions in a high probability). In this demonstration, users can set the performance goal as a trade-off between CPU consumption and query latency, and observe the CPU usages and other statistics to understand how InQP reduces computing resources.

One size does not fit all: accelerating OLAP workloads with GPUs

GPU has been considered as one of the next-generation platforms for real-time query processing databases. In this paper we empirically demonstrate that the representative GPU databases [e.g., OmniSci (Open Source Analytical Database & SQL Engine, https://www.omnisci.com/platform/omniscidb, 2019)] may be slower than the representative in-memory databases [e.g., Hyper (Neumann and Leis, IEEE Data Eng Bull 37(1):3–11, 2014)] with typical OLAP workloads (with Star Schema Benchmark) even if the actual dataset size of each query can completely fit in GPU memory. Therefore, we argue that GPU database designs should not be one-size-fits-all; a general-purpose GPU database engine may not be well-suited for OLAP workloads without careful designed GPU memory assignment and GPU computing locality. In order to achieve better performance for GPU OLAP, we need to re-organize OLAP operators and re-optimize OLAP model. In particular, we propose the 3-layer OLAP model to match the heterogeneous computing platforms. The core idea is to maximize data and computing locality to specified hardware. We design the vector grouping algorithm for data-intensive workload which is proved to be assigned to CPU platform adaptive. We design the TOP-DOWN query plan tree strategy to guarantee the optimal operation in final stage and pushing the respective optimizations to the lower layers to make global optimization gains. With this strategy, we design the 3-stage processing model (OLAP acceleration engine) for hybrid CPU-GPU platform, where the computing-intensive star-join stage is accelerated by GPU, and the data-intensive grouping & aggregation stage is accelerated by CPU. This design maximizes the locality of different workloads and simplifies the GPU acceleration implementation. Our experimental results show that with vector grouping and GPU accelerated star-join implementation, the OLAP acceleration engine runs 1.9×, 3.05× and 3.92× faster than Hyper, OmniSci GPU and OmniSci CPU in SSB evaluation with dataset of SF = 100.

Bounded Evaluation: Querying Big Data with Bounded Resources

This work aims to reduce queries on big data to computations on small data, and hence make querying big data possible under bounded resources. A query Q is boundedly evaluable when posed on any big dataset {cal D}, there exists a fraction {{cal D}_Q} of {cal D} such that Q({cal D}) = Q({{cal D}_Q}), and the cost of identifying {{cal D}_Q} is independent of the size of {cal D}. It has been shown that with an auxiliary structure known as access schema, many queries in relational algebra (RA) are boundedly evaluable under the set semantics of RA. This paper extends the theory of bounded evaluation to RAaggr, i.e., RA extended with aggregation, under the bag semantics. (1) We extend access schema to bag access schema, to help us identify {{cal D}_Q} for RAaggr queries Q. (2) While it is undecidable to determine whether an RAaggr query is boundedly evaluable under a bag access schema, we identify special cases that are decidable and practical. (3) In addition, we develop an effective syntax for bounded RAaggr queries, i.e., a core subclass of boundedly evaluable RAaggr queries without sacrificing their expressive power. (4) Based on the effective syntax, we provide efficient algorithms to check the bounded evaluability of RAaggr queries and to generate query plans for bounded RAaggr queries. (5) As proof of concept, we extend PostgreSQL to support bounded evaluation. We experimentally verify that the extended system improves performance by orders of magnitude.

A novel deep learning method for query task execution time prediction in graph database

The execution time prediction for query tasks in graph database has become difficult and challenging due to the complexity of query plan and system. It is difficult for Database Administrators (DBA) or Database Management System (DBMS) to catch the accurate execution time during and before the execution of a query task. Before executing a query task, predicting its execution time can help the DBA or DBMS to efficiently management in the fields of load management, task scheduling, permission control, progress monitoring, system scale customization, etc. Therefore, accurately and efficiently predicting the execution time for query tasks is a key technology in these fields. In this paper, motivated by the combination of artificial intelligence technologies and graph database theories, we first propose a novel deep learning method to predict the execution time for query tasks in graph database. First, each query plan tree of tasks is encoded into an operation sequence. Second, top-20 features are selected from 68 candidate system features using random forest (RF), and the selected top-20 features are reduced to five principal components using principal component analysis (PCA). Finally, an accurate and efficient model based on the long short-term memory (LSTM) is designed and implemented to predict the execution time. The model can predict the execution time in advance before executing a query task in graph database. The experimental results from six kinds of benchmarks with the public data set Yelp show that the average accuracy of the proposed model can reach 81.34% with a high prediction efficiency rate, which proves the feasibility of the deep learning method. In particular, the proposed model can achieve the state-of-the-art prediction performance for query task execution time.

Efficient Regular Expression Matching Based on Positional Inverted Index

We study efficient regular expression (regex) matching problem. Existing algorithms are scanning-based algorithms which typically use an equivalent automaton compiled from the regex query to verify a document. Although some works propose various strategies to quickly jump to candidate locations in a document where a query result may appear, they still need to utilize the scanning-based method to verify candidate locations. These methods become inefficient when there are still many candidate locations needed to be verified. In this paper, we propose a novel approach to efficiently compute all matching positions for a regex query purely based on a positional q-gram inverted index. We propose a gram-driven NFA to represent the language of a regex and show all regex matching locations can be obtained by finding positions on q-grams of GNFA that satisfy certain positional constraints. Then we propose several GNFA-based query plans to answer the query using the positional inverted index. In order to improve the query efficiency, we design the algorithm to build a tree-based query plan by carefully choosing a checking order for positional constraints. Experimental results on real-world datasets show that our method outperforms state-of-the-art methods by up to an order of magnitude in query efficiency.

Query performance prediction for concurrent queries using graph embedding

Query performance prediction is vital to many database tasks (e.g., database monitoring and query scheduling). Existing methods focus on predicting the performance for a single query but cannot effectively predict the performance for concurrent queries, because it is rather hard to capture the correlations between different queries, e.g., lock conflict and buffer sharing. To address this problem, we propose a performance prediction system for concurrent queries using a graph embedding based model. To the best of our knowledge, this is the first graph-embedding-based performance prediction model for concurrent queries. We first propose a graph model to encode query features, where each vertex is a node in the query plan of a query and each edge between two vertices denotes the correlations between them, e.g., sharing the same table/index or competing resources. We then propose a prediction model, in which we use a graph embedding network to encode the graph features and adopt a prediction network to predict query performance using deep learning. Since workloads may dynamically change, we propose a graph update and compaction algorithm to adapt to workload changes. We have conducted extensive experiments on real-world datasets, and experimental results showed that our method outperformed the state-of-the-art approaches.

Similarity Join and Similarity Self-Join Size Estimation in a Streaming Environment

We study the problem of similarity self-join and similarity join size estimation in a streaming setting where the goal is to estimate, in one scan of the input and with sublinear space in the input size, the number of record pairs that have a similarity within a given threshold. The problem has many applications in data cleaning and query plan generation, where the cost of a similarity join may be estimated before actually doing the join. On unary input where two records either match or don't match, the problem becomes join and self-join size estimation for which one-pass algorithms are readily available. Our work addresses the problem for d-ary input, for d >= 1, where the degree of similarity can vary from 1 to d. We show that our proposed algorithm gives an accurate estimate and scales well with the input size. We provide error bounds and time and space costs, and conduct an extensive experimental evaluation of our algorithm, comparing its estimation accuracy to a few competitors, including some multi-pass algorithms. Our results show that given the same space, the proposed algorithm has an order of magnitude less error for a large range of similarity thresholds.

Quantifying TPC-H choke points and their optimizations

TPC-H continues to be the most widely used benchmark for relational OLAP systems. It poses a number of challenges, also known as "choke points", which database systems have to solve in order to achieve good benchmark results. Examples include joins across multiple tables, correlated subqueries, and correlations within the TPC-H data set. Knowing the impact of such optimizations helps in developing optimizers as well as in interpreting TPC-H results across database systems. This paper provides a systematic analysis of choke points and their optimizations. It complements previous work on TPC-H choke points by providing a quantitative discussion of their relevance. It focuses on eleven choke points where the optimizations are beneficial independently of the database system. Of these, the flattening of subqueries and the placement of predicates have the biggest impact. Three queries (Q2, Q17, and Q21) are strongly influenced by the choice of an efficient query plan; three others (Q1, Q13, and Q18) are less influenced by plan optimizations and more dependent on an efficient execution engine.

Multi-objective Multi-Join Query Optimization using Modified Grey Wolf Optimization

Nowadays information retrieved by a query is based upon extracting data across the world, which are located in different data sites. In distributed database management systems (DDBMS), due to partitioning or replication of data among several sites the relations required for an answer of a query may be stored at several data sites (DS). Many experimental results have showed that combination of optimal join order (OJO) and optimal selection of relations in query plan (QP) gives out better results compare to the several existing query optimising methodologies like teacher-learner based optimisation (TLBO), genetic algorithm (GA), etc. In this paper an approach has been proposed to compute a best optimal QP that could answer the user query with minimal cost values and minimum time using modified grey wolf optimisation algorithm (MGWO) which is multi-objective constrained. Proposed approach also aims for producing OJO in order to reduce the dimensionality complexity of the QP.

Query optimisation with weighted fish school search in ontological database with application of bioinformatics

Making queries from large ontological database has a severe problem of generating query plans as it is made in form of left tree search form. This restricts the querying for composite applications and speed of acquiring query results. In such a scenario the most prominent approach is to optimise the indexing of graph nodes in ontological database and many evolutionary and particle of swarm optimisation (PSO)-based approach had already been attempted. However, loss of diversity and unanticipated convergence causes the solution to remain sub-optimal. In this study we present a weighted fish school searching-based query optimisation technique owing to its scalability and self control functioning with the application of bioinformatics. It creates probabilistic logic-based weight system for the fish school search in a hierarchical tree form which results in increased accuracy when put in comparison with standard PSO-based methods and its other variants.

Кэширование машинного кода в динамическом компиляторе SQL-запросов для СУБД PostgreSQL

As the efficiency of main and external memory grows, alongside with decreasing hardware costs, the performance of database management systems (DBMS) on certain kinds of queries is more determined by CPU characteristics and the way it is utilized. Relational DBMS utilize diverse execution models to run SQL queries. Those models have different properties, but in either way suffer from substantial overhead during query plan interpretation. The overhead comes from indirect calls to handler functions, runtime checks and large number of branch instructions. One way to solve this problem is dynamic query compilation that is reasonable only in those cases when query interpretation time is larger than the time of compilation and optimized machine code execution. This requirement can be satisfied only when the amount of data to be processed is large enough. If query interpretation takes milliseconds to finish, then the cost of dynamic compilation can be hundreds of times more than the execution time of generated machine code. To pay off the cost of dynamic compilation, the generated machine code has to be reused in subsequent executions, thus saving the cost of code compilation and optimization. In this paper, we examine the method of machine code caching in our query JIT-compiler for DBMS PostgreSQL. The proposed method allows us to eliminate compilation overhead. The results show that dynamic compilation of queries with machine code caching feature gives a significant speedup on OLTP queries.

Analysis of Two Phase Query Optimization Algorithm for Generating Optimal Query Plan using Randomized Algorithm

Analysis of Two Phase Query Optimization Algorithm for Generating Optimal Query Plan using Randomized Algorithm

Implementing Query Terms Linked to Virtual Databases by Metaheuristic Techniques

In general case, the database trigger may be quite applicable to signified queries to validate the database requests. Specifically, these may be essential to adopt search mechanisms to identify the query terms. In such cases it may also be required to eradicate ambiguities during updates by checking consistencies, durability. Many database systems support aggregate functions as it may be really linked to statistical analysis of large scale data. Again as per the requirement and schedule, multilevel aggregation may be thought of towards report generation and implementation of join predicates. In case of complexity, direct requests may be accessed to schedule the entire database operations. While optimizing the database queries, alternative query plans may be thought of implementing specific routines to eradicate the duplicity of query terms. It may be quite possible to containerize the query plans linked to several data servers exploring the inter operator parallelism. Also the assemblers linked to the query plans in the servers may steer the process accordingly. Considering the implementation mechanisms of database query plans inside a cloud storage system, the data may be automatically partitioned and replicated. The servers may change dynamically the existing load in response to the query plans. The queries as well as the transactions may be uncommon during optimization process and applications may be communicated following standard activity protocols linked to the database servers. Linking the query terms to the databases, it may also be required to incorporate metadata towards plan execution. Many times transactional database applications linked to relational cloud may have the provision of configuring and accessing the data and may face the challenges like scalability and privacy. To overcome these issues, the tasks may be relocated and rearranged linked to database servers by which better performance may be achieved dealing with complex transactions. Also the aggregation methods or techniques linked to data partitioning may enable the structured queries to yield better performance. In this paper it is intended to obtain query terms along with the threshold values linked to virtual databases.

Semantic connection set-based massive RDF data query processing in Spark environment

Resource Description Framework (RDF) is a data representation of the Semantic Web, and its data volume is growing rapidly. Cloud-based systems provide a rich platform for managing RDF data. However, there is a performance challenge in the distributed environment when RDF queries, which contain multiple join operations, such as network reshuffle and memory overhead, are processed. To get over this challenge, this paper proposes a Spark-based RDF query architecture, which is based on Semantic Connection Set (SCS). First of all, the proposed Spark-based query architecture adopts the mechanism of re-partitioning class data based on vertical partitioning, which can reduce memory overhead and spend up index data. Secondly, a method for generating query plans based on semantic connection set is proposed in this paper. In addition, some statistics and broadcast variable optimization strategies are introduced to reduce shuffling and data communication costs. The experiments of this paper are based on the latest SPARQLGX on the Spark platform RDF system. Two synthetic benchmarks are used to evaluate the query. The experiment results illustrate that the proposed approach in this paper is more efficient in data search than contrast systems.

General dynamic Yannakakis: conjunctive queries with theta joins under updates

The ability to efficiently analyze changing data is a key requirement of many real-time analytics applications. In prior work, we have proposed general dynamic Yannakakis (GDyn), a general framework for dynamically processing acyclic conjunctive queries with $$\theta $$-joins in the presence of data updates. Whereas traditional approaches face a trade-off between materialization of subresults (to avoid inefficient recomputation) and recomputation of subresults (to avoid the potentially large space overhead of materialization), GDyn is able to avoid this trade-off. It intelligently maintains a succinct data structure that supports efficient maintenance under updates and from which the full query result can quickly be enumerated. In this paper, we consolidate and extend the development of GDyn. First, we give full formal proof of GDyn ’s correctness and complexity. Second, we present a novel algorithm for computing GDyn query plans. Finally, we instantiate GDyn to the case where all $$\theta $$-joins are inequalities and present extended experimental comparison against state-of-the-art engines. Our approach performs consistently better than the competitor systems with multiple orders of magnitude improvements in both time and memory consumption.

Database processing-in-memory

The rapid growth of "big-data" intensified the problem of data movement when processing data analytics: Large amounts of data need to move through the memory up to the CPU before any computation takes place. To tackle this costly problem, Processing-in-Memory (PIM) inverts the traditional data processing by pushing computation to memory with an impact on performance and energy efficiency. In this paper, we present an experimental study on processing database SIMD operators in PIM compared to current x86 processor (i.e., using AVX512 instructions). We discuss the execution time gap between those architectures. However, this is the first experimental study, in the database community, to discuss the trade-offs of execution time and energy consumption between PIM and x86 in the main query execution systems: materialized, vectorized, and pipelined. We also discuss the results of a hybrid query scheduling when interleaving the execution of the SIMD operators between PIM and x86 processing hardware. In our results, the hybrid query plan reduced the execution time by 45%. It also drastically reduced energy consumption by more than 2x compared to hardware-specific query plans.

An end-to-end learning-based cost estimator

Cost and cardinality estimation is vital to query optimizer, which can guide the query plan selection. However traditional empirical cost and cardinality estimation techniques cannot provide high-quality estimation, because they may not effectively capture the correlation between multiple tables. Recently the database community shows that the learning-based cardinality estimation is better than the empirical methods. However, existing learning-based methods have several limitations. Firstly, they focus on estimating the cardinality, but cannot estimate the cost. Secondly, they are either too heavy or hard to represent complicated structures, e.g., complex predicates. To address these challenges, we propose an effective end-to-end learning-based cost estimation framework based on a tree-structured model, which can estimate both cost and cardinality simultaneously. We propose effective feature extraction and encoding techniques, which consider both queries and physical operations in feature extraction. We embed these features into our tree-structured model. We propose an effective method to encode string values, which can improve the generalization ability for predicate matching. As it is prohibitively expensive to enumerate all string values, we design a patten-based method, which selects patterns to cover string values and utilizes the patterns to embed string values. We conducted experiments on real-world datasets and experimental results showed that our method outperformed baselines.

A general fragments allocation method for join query in distributed database

The quality of fragments allocation is key for improving performance of join query in distributed database. Current strategies concentrate on using heuristic rules to allocate fragments to corresponding locations, such as picking the location with maximum required data or with greedy algorithm. Notwithstanding their benefits, under distributed environment, facing various query plans, different data distributions and expensive network cost, their scene-sensitive character may easily generate low quality allocation plan due to lack of generalization ability.In this paper, for breaking this limitation, we propose a general strategy for allocating fragments(AlCo, Allocate fragments based on Cost). AlCo evaluates multiple candidate allocation plans based on cost, which is realized by a modified genetic algorithm employed from PostgreSQL. Our fitness function (cost model) synthetically considers various changeable factors to support generalization ability. For reducing the risks caused by randomization of genetic algorithm, AlCo provides an upper bound computed through current heuristic methods to improve the robustness of our genetic algorithm. We implement AlCo in a distributed database system, and the experiments show that, on TPC-H benchmark, AlCo is up to 2x–4x better on performance than existing strategies and performs well in robustness and scalability.