Robust Plan Evaluation Based on Approximate Probabilistic Machine Learning

A multidatabase system (MDBS) is a database system which integrates pre-existing databases, called component local database systems (LDBSs), to support global applications accessing data at more than one LDBS. An important research issue in MDBS is query optimization. The query optimization problem in MDBS is quite different from the case of distributed database system (DDBS) since, due to schema heterogeneity and local autonomy of component LDBSs, is not possible to assume that the query optimizer has a complete information on the execution cost and database statistics. In this paper we present a distributed query optimization algorithm that works under very general assumptions for MDBSs with relational global data model. The algorithm is based on the idea of delegating the evaluation of the execution cost of the elementary steps in a query execution plan to the LDBS where the computation is performed. The optimization process is organized as a sequence of steps, in which at each step all LDBSs work in parallel to evaluate the cost of execution plans for partial queries of increasing size, and send their cost estimates to the other LDBS that need them for the next step. The computation is totally distributed, and organized in order to perform no duplicate computation, and to discard as soon as possible the execution plans that may not lead to an optimal solution.KeywordsExecution ModelExecution PlanQuery OptimizationQuery ExecutionExecution CostThese keywords were added by machine and not by the authors. This process is experimental and the keywords may be updated as the learning algorithm improves.

Since the very beginning of query processing in database systems, cost-based query optimization has been the essential strategy for effectively answering complex queries on large documents. XML documents can be efficiently stored and processed using native XML database management systems. Even though such systems can choose from a huge repertoire of join operators (e. g., Structural Joins and Holistic Twig Joins) and various index access operators to efficiently evaluate queries on XML documents, the development of full-fledged XML query optimizers is still in its infancy. Especially the evaluation of complex XQuery expressions using these operators is not well understood and needs further research. The extensible, rule-based, and cost-based XML query optimization framework proposed in this chapter, serves as a testbed for exploring how and whether well-known concepts from relational query optimization (e. g., join reordering) can be reused and which new techniques can make a significant contribution to speed-up query execution. Using the best practices and an appropriate cost model that will be developed using this framework, it can be turned into a robust cost-based XML query optimizer in the future.

Multi-route query processing and optimization

Query optimizers often produce sub-optimal query plans due to inaccurate cardinality estimates. The goal of exact cardinality query optimization (ECQO) is to produce optimal plans based on guaranteed exact cardinality values, obtained via probe executions. We propose a novel algorithm for ECQO. It improves over prior work by limiting the overheads of probe executions. Instead of fully generating each relation in the optimizer's plan space, it calculates cardinality bounds based on partially generated relations. Thereby, it is often able to prune relations early if they are too large to participate in any optimal plan. Our algorithm exploits dependencies between relations to propagate exclusions, it selects probing targets for maximum information gain, and it caps probing overheads by conservative bounds on execution cost. Those bounds are iteratively increased once lower bounds on optimal query execution cost have been established. The algorithm is non-intrusive and can be used on top of any database management system supporting SQL limit clauses. We formally prove that probing costs are bounded as a function of the optimal execution cost and evaluate our algorithm experimentally. Our algorithm is in average six times and up to 69 times faster than a state-of-the-art baseline for ECQO on the recently proposed join order benchmark.

Deco is a comprehensive system for answering declarative queries posed over stored relational data together with data obtained on-demand from the crowd. In this paper we describe Deco's cost-based query optimizer, building on Deco's data model, query language, and query execution engine presented earlier. Deco's objective in query optimization is to find the best query plan to answer a query, in terms of estimated monetary cost. Deco's query semantics and plan execution strategies require several fundamental changes to traditional query optimization. Novel techniques incorporated into Deco's query optimizer include a cost model distinguishing between "free" existing data versus paid new data, a cardinality estimation algorithm coping with changes to the database state during query execution, and a plan enumeration algorithm maximizing reuse of common subplans in a setting that makes reuse challenging. We experimentally evaluate Deco's query optimizer, focusing on the accuracy of cost estimation and the efficiency of plan enumeration.

Traditional database query optimisation methods use stochastic algorithms to approximate the query optimisation results by continuously adjusting the optimisation plan. Since the stochastic algorithm only performs query optimisation from a single perspective, it leads to no significant improvement of the optimised database query efficiency. To address the above problems, we studied the query optimisation method of foreign enterprises’ German language data database based on hybrid learning. By reducing the database query search space and selecting query optimisation strategy, the data query complexity is reduced. After estimating the cost of database query optimisation, the policy selection algorithm is trained using the hybrid learning theory to obtain the database query optimisation path. The simulation experimental results show that the average query response of the optimised database after applying the studied method saves about 13.6%, and the query cost is lower and the optimisation effect is better.

Query optimization is considered as the most significant part in a model of distributed database. The optimizer tries to find an optimal join order, which reduces the query execution cost. Several factors may affect the cost of query execution, including number of relations, communication costs, resources, and access to large distributed data sets. The success of a processed query depends heavily on the search methodology that is implemented by the query optimizer. Query processing is considered as NP-hard problem and many researchers are focusing on this problem. Researches are trying to find an appropriate algorithm to seek an ideal solution especially when the size of the database increases. In case of large queries, classical heuristic methods such as ant colony and genetic algorithm can't cover all search space and may lead to falling in a local minimum. In this paper, quantum inspired ant colony algorithm (QIACO), as one of the hybrid strategy of probabilistic algorithms, is utilized to improve the query join cost in the distributed database model. The ability of quantum computing to diversify leads to cover query large search space, which helps in selecting the best trail and thus improves the slow convergence speed and avoid falling into a local optimum. Using this strategy, the algorithm aims to find an optimal join order which minimizes the total execution time. Experimental results show that the proposed model convergence faster with better goodness than the classic ant colony model for same number of ants used.

Interacting with Large Language Models (LLMs) via declarative queries is increasingly popular for tasks like question answering and data extraction, thanks to their ability to process vast unstructured data. However, LLMs often struggle with answering complex factual questions, exhibiting low precision and recall in the returned data. This challenge highlights that executing queries on LLMs remains a largely unexplored domain, where traditional data processing assumptions often fall short. Conventional query optimization, typically cost-driven, overlooks LLM-specific quality challenges such as contextual understanding. Just as new physical operators are designed to address the unique characteristics of LLMs, optimization must consider these quality challenges. Our results highlight that adhering strictly to conventional query optimization principles fails to generate the best plans in terms of result quality. To tackle this challenge, we present a novel approach to enhance SQL results by applying query optimization techniques specifically adapted for LLMs. We introduce a database system, GALOIS, that sits between the query and the LLM, effectively using the latter as a storage layer. We design alternative physical operators tailored for LLM-based query execution and adapt traditional optimization strategies to this novel context. For example, while pushing down operators in the query plan reduces execution cost (fewer calls to the model), it might complicate the call to the LLM and deteriorate result quality. Additionally, these models lack a traditional catalog for optimization, leading us to develop methods to dynamically gather such metadata during query execution. Our solution is compatible with any LLM and balances the trade-off between query result quality and execution cost. Experiments show up to 144% quality improvement over questions in Natural Language and 29% over direct SQL execution, highlighting the advantages of integrating database solutions with LLMs.

This paper addresses the problem of optimizing multiple distributed stream queries that are executing simultaneously in distributed data stream systems. We argue that the static query optimization approach of "plan, then deployment" is inadequate for handling distributed queries involving multiple streams and node dynamics faced in distributed data stream systems and applications. Thus, the selection of an optimal execution plan in such dynamic and networked computing systems must consider operator ordering, reuse, network placement, and search space reduction. We propose to use hierarchical network partitions to exploit various opportunities for operator-level reuse while utilizing network characteristics to maintain a manageable search space during query planning and deployment. We develop top-down, bottom-up, and hybrid algorithms for exploiting operator-level reuse through hierarchical network partitions. Formal analysis is presented to establish the bounds on the search space and suboptimality of our algorithms. We have implemented our algorithms in the IFLOW system, an adaptive distributed stream management system. Through simulations and experiments using a prototype deployed on Emulab, we demonstrate the effectiveness of our framework and our algorithms.

Classical query optimization compares query plans according to one cost metric and associates each plan with a constant cost value. In this paper, we introduce the Multi-Objective Parametric Query Optimization (MPQ) problem where query plans are compared according to multiple cost metrics and the cost of a given plan according to a given metric is modeled as a function that depends on multiple parameters. The cost metrics may for instance include execution time or monetary fees; a parameter may represent the selectivity of a query predicate that is unspecified at optimization time. MPQ generalizes parametric query optimization (which allows multiple parameters but only one cost metric) and multi-objective query optimization (which allows multiple cost metrics but no parameters). We formally analyze the novel MPQ problem and show why existing algorithms are inapplicable. We present a generic algorithm for MPQ and a specialized version for MPQ with piecewise-linear plan cost functions. We prove that both algorithms find all relevant query plans and experimentally evaluate the performance of our second algorithm in a Cloud computing scenario.

Over the last decades, improvements in CPU speed have outpaced improvements in main memory and disk access rates by orders of magnitude, enabling the use of data compression techniques to improve the performance of database systems. Previous work describes the benefits of compression for numerical attributes, where data is stored in compressed format on disk. Despite the abundance of string-valued attributes in relational schemas there is little work on compression for string attributes in a database context. Moreover, none of the previous work suitably addresses the role of the query optimizer: During query execution, data is eithereagerlydecompressed when it is read into main memory, or datalazilystays compressed in main memory and is decompressed on demand onlyIn this paper, we present an effective approach for database compression based on lightweight, attribute-level compression techniques. We propose a IIierarchical Dictionary Encoding strategy that intelligently selects the most effective compression method for string-valued attributes. We show that eager and lazy decompression strategies produce sub-optimal plans for queries involving compressed string attributes. We then formalize the problem of compression-aware query optimization and propose one provably optimal and two fast heuristic algorithms for selecting a query plan for relational schemas with compressed attributes; our algorithms can easily be integrated into existing cost-based query optimizers. Experiments using TPC-H data demonstrate the impact of our string compression methods and show the importance of compression-aware query optimization. Our approach results in up to an order speed up over existing approaches.

The method of finding the optimal processing method to answer a query is called Query optimization, whereas a collection of various sites, distributed over a computer network is called Distributed database. In Distributed Database, the site communicates with each other through networks. The processing cost and the transmission cost are the important issues arise during evaluation of query cost. Several algorithms have been developed to find the best optimal solution for a particular query; however they all have their certain limitations. Hence, to find the optimal cost for a particular query is emerging as an open challenge for many researchers. Therefore the cost-based query optimization technique has emerged as an important concept for dealing with the query optimization. With the help of fragmentations one can replicate each fragment to various distributed sites, since the same data may be accessed from applications that executes at a number of sites. In this paper, the concepts of randomized algorithms have been explored. The two best know randomized algorithms are Simulated Annealing and Iterative Improvement, which have been implemented and their results have been compared on the number of optimal query plan generated and the average query processing cost. The attempts have been made to proposed and implemented the concept of two phase query optimization algorithm, which is also known as hybrid approach which works in two, phase: in first phase we apply iterative improvement algorithm followed by simulated annealing algorithm. The results of the experiments are also compared on the previous two factors i.e no. of query plan generated and average query processing cost by varying the number of relation and the no of distributed sites participating.

In a multidatabase system, the participating databases are autonomous. The schemas of these databases may be different in various ways, while the same information is represented. A global query issued against the global database needs to be translated to a proper form before it can be executed in a local database. Since data requested by a query (or a part of a query) is sometimes available in multiple sites, the site (database) that processes the query with the least cost is the desired query processing site. The authors study the effect of differences in schemas on the cost of query processing in a multidatabase environment. They first classify schema conflicts to different types. For each type of conflict, they show how much more or less complex a translated query can become in comparison with the originally user-issued global query. Based on this observation, they propose an analytical method that considers the conflicts between local databases and finds the database(s) that renders the least execution cost in processing a global query. This research introduces a new level of query optimization (termed the schema-level optimization) in multidatabase environments. The results provide a new dimension of enhancement for the capability of a query optimizer in multidatabase systems.

We study the problem of optimizing one-time and continuous subgraph queries using the new worst-case optimal join plans. Worst-case optimal plans evaluate queries by matching one query vertex at a time using multiway intersections. The core problem in optimizing worst-case optimal plans is to pick an ordering of the query vertices to match. We make two main contributions: 1. A cost-based dynamic programming optimizer for one-time queries that (i) picks efficient query vertex orderings for worst-case optimal plans and (ii) generates hybrid plans that mix traditional binary joins with worst-case optimal style multiway intersections. In addition to our optimizer, we describe an adaptive technique that changes the query vertex orderings of the worst-case optimal subplans during query execution for more efficient query evaluation. The plan space of our one-time optimizer contains plans that are not in the plan spaces based on tree decompositions from prior work. 2. A cost-based greedy optimizer for continuous queries that builds on the delta subgraph query framework. Given a set of continuous queries, our optimizer decomposes these queries into multiple delta subgraph queries, picks a plan for each delta query, and generates a single combined plan that evaluates all of the queries. Our combined plans share computations across operators of the plans for the delta queries if the operators perform the same intersections. To increase the amount of computation shared, we describe an additional optimization that shares partial intersections across operators. Our optimizers use a new cost metric for worst-case optimal plans called intersection-cost . When generating hybrid plans, our dynamic programming optimizer for one-time queries combines intersection-cost with the cost of binary joins. We demonstrate the effectiveness of our plans, adaptive technique, and partial intersection sharing optimization through extensive experiments. Our optimizers are integrated into GraphflowDB.

LSIR