Cost-based Optimization Research Articles

AutoQuo: An Adaptive plan optimizer with reinforcement learning for query plan selection

An efficient execution plan generation is crucial for optimizing database queries. In exploring large table spaces to identify the most optimal table join orders, traditional cost-based optimizers may encounter challenges when confronted with complicated queries. Thus, learning-based optimizers have recently been proposed to leverage past experience and generate high-quality execution plans. However, these optimizers demonstrate limited generalization capabilities for workloads with diverse distributions.In this study, an adaptive plan selector based on reinforcement learning is proposed to address these issues. However, three challenges remain: (1) How to generate optimal multi-table join orders? We adopt an exploration–exploitation strategy to traverse the vast search space composed of candidate tables, thereby evaluating the significance of each table. Long short-term memory (LSTM) networks are subsequently used to predict the performance of join orders and generate high-quality candidate plans. (2) How to automatically learn new features in novel datasets? We employ the Actor–Critic strategy, which involves jointly cross-training the policy and value networks. By adjusting the parameters based on real feedback obtained from the database, the new datasets are automatically learnt. (3) How to automatically select the best plan? We introduce a constraint-aware optimal plan selection model that captures the relationship between constraints and plans. This model guides the selection of the best plan under constraints of execution time, cardinality, cost, and mean-squared error (MSE). The experimental results on real datasets demonstrated the superiority of the proposed approach over state-of-the-art baselines. Compared with PostgreSQL, we observed a reduction of 29.73% in total latency and 28.36% in tail latency.

A systematic approach for screening green entrainers combining the environmental-health-safety indexes and separation performance

Here we propose a new approach for extractive distillation (ED) process design, integrating environmental-health-safety (EHS) indexes as early as during the entrainer screening phase. Typically, safety and environmental factors are addressed later in the design process, often during optimization or in a sequential evaluation after cost-based optimization. However, in this study, we propose for the early incorporation of EHS considerations during the entrainer screening phase. This involves assessing the entrainer suitability using traditional metrics alongside additional EHS criteria, such as lethal dose (LD50) and flash point. Two azeotropic mixtures, i.e., tetrahydrofuran (THF)/ethanol (EtOH) and THF/methanol (MeOH), are chosen to exemplify our proposed methodology. Among the 1432 screened entrainers, the top 10 entrainers were identified based on separation index. These candidates were then subjected to further evaluation based on EHS criteria. Ethylenediamine (EDA) emerged as the optimal choice for the separation of THF/MeOH and THF/EtOH mixtures, demonstrating commendable EHS performance. Both separation processes were successfully simulated using Aspen Plus, which confirms the validity and suitability of the screened entrainer.

Cost-based optimization, feasibility study, and sensitivity analysis of forward osmosis/crystallization/reverse osmosis with high-temperature operation for high-salinity seawater desalination

Seawater desalination plants using reverse osmosis (RO) in high temperature and high-salinity conditions, such as in the Middle East, face challenges such as limited achievable recoveries and high energy costs. This study evaluated the economic feasibility under seawater and high-salinity conditions by introducing a forward osmosis/crystallization/reverse osmosis (FO/Cry/RO) hybrid process. The optimization results were then used to compare the specific water cost (SWC), specific energy consumption (SEC), recovery, and permeate quality of the hybrid process with two different draw solutes and a conventional seawater RO process. In addition, a sensitivity analysis was conducted to determine how individual process parameters and operating conditions affect the economic feasibility of the proposed process under seawater and high-salinity conditions. The FO/Cry/RO hybrid process achieved a higher recovery with a lower SWC and SEC as the feed concentration increased compared to the conventional RO process. Utilizing waste heat further increased the economic advantages of the proposed process. In addition, much higher permeate quality can be obtained depending on the type of draw solute. The results of this study revealed that the proposed hybrid system outperformed the conventional RO process in terms of both energy efficiency and cost-effectiveness for seawater desalination under high-temperature and high-salinity conditions.

Identifying the Root Causes of DBMS Suboptimality

The query optimization phase within a database management system (DBMS) ostensibly finds the fastest query execution plan from a potentially large set of enumerated plans, all of which correctly compute the same result of the specified query. Sometimes the cost-based optimizer selects a slower plan, for a variety of reasons. Previous work has focused on increasing the performance of specific components, often a single operator, within an individual DBMS. However, that does not address the fundamental question: from where does this suboptimality arise, across DBMSes generally? In particular, the contribution of each of many possible factors to DBMS suboptimality is currently unknown. To identify the root causes of DBMS suboptimality, we first introduce the notion of empirical suboptimality of a query plan chosen by the DBMS, indicated by the existence of a query plan that performs more efficiently than the chosen plan, for the same query. A crucial aspect is that this can be measured externally to the DBMS, and thus does not require access to its source code. We then propose a novel predictive model to explain the relationship between various factors in query optimization and empirical suboptimality. Our model associates suboptimality with the factors of complexity of the schema, of the underlying data on which the query is evaluated, of the query itself, and of the DBMS optimizer. The model also characterizes concomitant interactions among these factors. This model induces a number of specific hypotheses that were tested on multiple DBMSes. We performed a series of experiments that examined the plans for thousands of queries run on four popular DBMSes. We tested the model on over a million of these query executions, using correlational analysis, regression analysis, and causal analysis, specifically Structural Equation Modeling (SEM). We observed that the dependent construct of empirical suboptimality prevalence correlates positively with nine specific constructs characterizing four identified factors that explain in concert much of the variance of suboptimality of two extensive benchmarks, across these disparate DBMSes. This predictive model shows that it is the common aspects of these DBMSes that predict suboptimality, not the particulars embedded in the inordinate complexity of each of these DBMSes. This paper thus provides a new methodology to study mature query optimizers, identifies underlying DBMS-independent causes for the observed suboptimality, and quantifies the relative contribution of each of these causes to the observed suboptimality. This work thus provides a roadmap for fundamental improvements of cost-based query optimizers.

A novel cost-based optimization model for electric power distribution systems resilience improvement under dust storms

In the recent years, dust storms (DSs) pose a serious threat to critical infrastructure such as power distribution networks (PDNs). During DSs, the contamination of insulators, increases the possibility of damage to the PDNs insulation system and flashover induced power outage may occur. Power outages disrupt the performance of other urban infrastructures and, in addition to heavy financial losses, cause public dissatisfaction. Although this issue is of particular importance in areas with humid climate, a few studies have been reported on PDNs resilience improvement against DSs. This paper proposes a novel cost-based optimization model to make PDNs more resilient to DSs considering uncertainties. The proposed model is based on the two-stage stochastic mixed-integer programming (SMIP). In the first stage, decisions are made to equip repair crews (RCs) with insulator washing machines, hardening distribution lines with silicone-rubber insulators (SIs), and deploy backup distributed generators (DGs). Decisions in the second stage include network reconfiguration, RCs routing, DGs power dispatch, and load shedding as the critical options for PDN outage management during/after DSs. Case studies are evaluated in the IEEE 69-bus test system and a real 209-bus PDN in Khuzestan province, a coastal province in southwestern Iran. The simulation results at different budget levels have confirmed the efficiency of the proposed model for cost-optimal resilience enhancement planning of PDNs against DSs.

The effect of electric vehicle charging demand variability on optimal hybrid power systems with second-life lithium-ion or fresh Na–S batteries considering power quality

Second-life batteries (SLB) in energy storage systems (ESS) can provide an economical solution to hybrid power systems (HPS) by reducing environmental concerns in battery production. Could Na–S batteries be a suitable alternative for ESS applications if the toxicity problem can be solved? This article performs the optimal sizing and feasibility analysis of an electric vehicle charging station with a minimum cost objective under daily, hourly, and hybrid demand variability with multi-year sensitivity analyses for both ESS technologies. Moreover, the scope is deepened with sensitivity analyses based on solar horizontal irradiation, wind speed, and electricity selling prices. Due to the HPS connection to the distribution busbar, power quality issues are included in the cost-based optimization framework, and the effects of power quality issues on the sizing and feasibility outputs are compared at higher demand variability. The results show that using SLB at wind speeds lower than 4.7 m/s and irradiances lower than 3 kWh/m2/day or above 0.035 $/kWh electric sale price is not economical. Grid-connected HPSs can increase current and voltage harmonics at the distribution busbar by up to 55.3 % and 4.3 %, reduce the power factor by up to 46.8 %, and extend the payback period by up to 1 year.

Dimensional Tolerances in Mechanical Assemblies: A Cost-Based Optimization Approach

There is a widely accepted consensus that component manufacturing precision is directly correlated with improved functional performance. However, this increase in precision comes at the expense of higher manufacturing costs, resulting in a trade-off between quality and affordability. In light of this opposing behavior, low-cost products typically exhibit lower quality, whereas high-quality products tend to be more expensive. This study introduces a novel approach for optimizing the dimensional tolerances of mechanical assembly components, taking into account both their manufacturing requirements and the associated costs of non-quality. Furthermore, the method considers the functional constraints imposed by interrelated tolerance chains within the product. Instead of relying on an exact mathematical solution, the proposed solution employs a heuristic approach through a simple and flexible algorithm. This enables practical implementation, as different cost-tolerance functions can be selected based on specific requirements. To provide a comprehensive evaluation of the proposed method, a concise review of the relevant literature in the field was conducted, allowing a comparison with other state-of-the-art approaches.

DATA_SPHERE

This paper presents a comprehensive overview of Database Management Systems (DBMS) and their significance in modern information management. DBMS technology plays a crucial role in the storage, organisation, retrieval, and manipulation of vast amounts of data in various domains, ranging from business operations to scientific research. This abstract highlights the key aspects covered in the paper, including the evolution of DBMS, its architectural components, and the challenges and advancements in the field. The paper begins by discussing the historical development of DBMS, tracing its origins from file-based systems to the emergence of relational databases and the subsequent rise of object-oriented and NoSQL databases. We explore the motivations behind these advancements and their impact on data management. Next, we delve into the fundamental architectural components of a DBMS. We examine the storage layer, which encompasses data structures and access methods, and discuss different indexing techniques for efficient data retrieval. The query processing and optimization module are explored, focusing on query execution plans and cost-based optimization strategies. Additionally, we analyse the transaction management component, highlighting concepts such as ACID properties, concurrency control, and recovery mechanisms. The abstract also highlights the challenges faced by modern DBMS. With the proliferation of big data and the advent of cloud computing, scalability, availability, and performance have become critical concerns. We examine techniques such as parallel and distributed databases, replication, and sharding to address these challenges. Furthermore, we discuss the integration of DBMS with emerging technologies like machine learning and blockchain to leverage their capabilities in data analytics and secure data transactions. Lastly, the abstract touches upon recent advancements in DBMS, including the rise of graph databases for managing interconnected data, the adoption of in-memory databases for high-performance applications, and the exploration of new database models to handle unstructured and semi-structured data. In conclusion, this paper provides a comprehensive overview of DBMS, covering its historical evolution, architectural components, challenges, and recent advancements. By understanding the principles and advancements in DBMS, researchers and practitioners can effectively harness the power of data management systems to tackle the complexities of modern data-driven applications.

FASTgres: Making Learned Query Optimizer Hinting Effective

The traditional and well-established cost-based query optimizer approach enumerates different execution plans for each query, assesses each plan with costs, and selects the plan that promises the lowest costs for execution. However, the optimal execution plan is not always selected. To steer the optimizer in the right direction, many query optimizers provide configuration parameters called query optimizer hints. These hints can be set for every single query separately. To show the great potential of these hints for the optimization of analytical queries, we present results of a comprehensive and in-depth evaluation using three benchmarks and two different versions of the open-source database system PostgreSQL. In particular, we highlight that query optimizer hinting is a non-trivial challenge. To solve this challenge, we propose FASTgres , a learning-based context-aware classification strategy for hint set prediction. Compared to related work, FASTgres provides transparent and direct hint set predictions with consistent performance improvements. In our end-to-end evaluation, we demonstrate that FASTgres effectively reduces benchmark runtimes by a factor of up to 3.25x with only steering the cost-based optimizer.

Exploiting Structure in Regular Expression Queries

Regular expression, or regex, is widely used to extract critical information from a large corpus of formatted text by finding patterns of interest. In tasks like log processing, the speed of regex matching is crucial. Data scientists and developers regularly use regex libraries that implement optimized regular expression matching using modern automata theory. However, computing state transitions in the underlying regex evaluation engine can be inefficient when a regex query contains a multitude of string literals. This inefficiency is further exasperated when analyzing large data volumes. This paper presents BLARE, Blazingly Fast Regular Expression, a regular expression matching framework that is inspired by the mechanisms that are used in database engines, which use a declarative framework to explore multiple equivalent execution plans, all of which produce the correct final result. Similarly, BLARE decomposes a regex into multiple regex and string components and then creates evaluation strategies in which the components can be evaluated in an order that is not strictly a left-to-right translation of the input regex query. Rather than using a cost-based optimization approach, BLARE uses an adaptive runtime strategy based on a multi-armed bandit approach to find an efficient execution plan. BLARE is also modular and can be built on top of any existing regex library. We implemented BLARE on four commonly used regex libraries, RE2, PCRE2, Boost Regex, and ICU Regex, and evaluated it using two production workloads and one open-source workload. BLARE was 1.6× to 3.7× faster than RE2 and 3.4× to 7.9× faster than Boost Regex. PCRE2 did not finish on one of the workloads, but on the remaining two workloads, BLARE improved the performance of PCRE2 by 3.1× to over 100×. For the open-source dataset, BLARE provided a speed up of 61.7× for ICU Regex. BLARE code is publicly available at https://github.com/mush-zhang/Blare.

Using Cloud Functions as Accelerator for Elastic Data Analytics

Cloud function (CF) services, such as AWS Lambda, have been applied as the new computing infrastructure in implementing analytical query engines. For bursty and sparse workloads, CF-based query engine is more elastic than the traditional query engines running in servers, i.e., virtual machines (VMs), and might provide a higher performance/price ratio. However, it is still controversial whether CF services are good suites for general analytical workloads, in respect of the limitations of CFs in storage, network, and lifetime, as well as the much higher resource unit prices than VMs. In this paper, we first present micro-benchmark evaluations of the features of CF and VM. We reveal that for query processing, though CF is more elastic than VM, it is less scalable and is more expensive for continuous workloads. Then, to get the best of both worlds, we propose Pixels-Turbo - a hybrid query engine that processes queries in a scalable VM cluster by default and invokes CFs to accelerate the processing of unpredictable workload spikes. In the query engine, we propose several optimizations to improve the performance and scalability of the CF-based operators and a cost-based optimizer to select the appropriate algorithm and parallelism for the physical query plan. Evaluations on TPC-H and real-world workload show that our query engine has a 1-2 orders of magnitude higher performance/price ratio than state-of-the-art serverless query engines for sustained workloads while not compromising the elasticity for workload spikes.

Optimizing Tensor Programs on Flexible Storage

Tensor programs often need to process large tensors (vectors, matrices, or higher order tensors) that require a specialized storage format for their memory layout. Several such layouts have been proposed in the literature, such as the Coordinate Format, the Compressed Sparse Row format, and many others, that were especially designed to optimally store tensors with specific sparsity properties. However, existing tensor processing systems require specialized extensions in order to take advantage of every new storage format. In this paper we describe a system that allows users to define flexible storage formats in a declarative tensor query language, similar to the language used by the tensor program. The programmer only needs to write storage mappings, which describe, in a declarative way, how the tensors are laid out in main memory. Then, we describe a cost-based optimizer that optimizes the tensor program for the specific memory layout. We demonstrate empirically significant performance improvements compared to state-of-the-art tensor processing systems.

K-NN Query Optimization for High-Dimensional Index Using Machine Learning

In this study, we propose three k-nearest neighbor (k-NN) optimization techniques for a distributed, in-memory-based, high-dimensional indexing method to speed up content-based image retrieval. The proposed techniques perform distributed, in-memory, high-dimensional indexing-based k-NN query optimization: a density-based optimization technique that performs k-NN optimization using data distribution; a cost-based optimization technique using query processing cost statistics; and a learning-based optimization technique using a deep learning model, based on query logs. The proposed techniques were implemented on Spark, which supports a master/slave model for large-scale distributed processing. We showed the superiority and validity of the proposed techniques through various performance evaluations, based on high-dimensional data.

Comparative Analysis Optimal Designs for Passive, Electrified, and Net Zero Energy Residential Buildings

Abstract In this paper, a life cycle cost-based optimization analysis is carried out to compare the energy and cost performance of diverse sustainable designs of a residential building. These designs include code optimal, net zero energy building, and passive house. It is found that in the case where natural gas is employed, a total energy savings of 77% is optimal. The cost optimal design for electrification achieves 100.12% of energy savings relative to the baseline design but results slightly high life cycle cost than that of the gas cost optimal design. In addition, the results indicate that due to the additional capital costs for the required energy efficient measures, the passive house case is less economically optimal than NZEB design options. Overall, the most cost-optimal designs are found to be for natural gas heated homes with marginally better energy performance than the applicable currently energy efficiency code with 10 kW solar panels.

Optimal planning and operation of distribution systems using network reconfiguration and flexibility services

This paper proposes a novel approach for the reliability cost-based optimization of Distribution Systems (DS), considering tie line-based network reconfiguration method with integration of Distributed Energy Resources (DER). An optimal Energy not Supplied (ENS) index is proposed, where the capacity is handled by curtailment devices in the network such as sectionalizers and the energy supplied by DERs which considers Flexibility Services (FS) within a market environment. The decision variables include the investment and operation of tie-lines and buying regulation services from DER such as Distributed Generation (DG) and Battery Energy Storage Systems (BESS). The results validate the cost-effectiveness of the proposed method through implementation of these technologies to improve the reliability of the DS, within a comprehensive set of case-study scenarios for a 16-bus UK generic distribution system (UKGDS). The case study results indicate that significant savings can be achieved through the proposed method, ranging between 36%–71% depending on the level of automation in tie-line operations in combination with the settlement price for the power-balance of FS. This illustrates that the proposed DS reliability cost-based optimization method could have a significant impact for real world DG and BESS applications.

Query Optimization Framework for Graph Database in Cloud Dew Environment

The query optimizer uses cost-based optimization to create an execution plan with the least cost, which also consumes the least amount of resources. The challenge of query optimization for relational database systems is a combinatorial optimization problem, which renders exhaustive search impossible as query sizes rise. Increases in CPU performance have surpassed main memory, and disk access speeds in recent decades, allowing data compression to be used—strategies for improving database performance systems. For performance enhancement, compression and query optimization are the two most factors. Compression reduces the volume of data, whereas query optimization minimizes execution time. Compressing the database reduces memory requirement, data takes less time to load into memory, fewer buffer missing occur, and the size of intermediate results is more diminutive. This paper performed query optimization on the graph database in a cloud dew environment by considering, which requires less time to execute a query. The factors compression and query optimization improve the performance of the databases. This research compares the performance of MySQL and Neo4j databases in terms of memory usage and execution time running on cloud dew servers.

SkinnerMT

SkinnerMT is an adaptive query processing engine, specialized for multi-core platforms. SkinnerMT features different strategies for parallel processing that allow users to trade between average run time and performance robustness.First, SkinnerMT supports execution strategies that execute multiple query plans in parallel, thereby reducing the risk to find near-optimal plans late and improving robustness. Second, SkinnerMT supports data-parallel processing strategies. Its parallel multi-way join algorithm is sensitive to the assignment from tuples to threads. Here, SkinnerMT uses a cost-based optimization strategy, based on runtime feedback. Finally, SkinnerMT supports hybrid processing methods, mixing parallel search with data-parallel processing.The experiments show that parallel search increases robustness while parallel processing increases average-case performance. The hybrid approach combines advantages from both. Compared to traditional database systems, SkinnerMT is preferable for benchmarks where query optimization is hard. Compared to prior adaptive processing baselines, SkinnerMT exploits parallelism better.

Cost-Based Optimization of Isolated Footing in Cohesive Soils Using Generalized Reduced Gradient Method

This study presents a cost-based optimization model for the design of isolated foundations in cohesive soils. The optimization algorithm not only incorporates safety requirements in the form of ultimate limit state (ULS) and serviceability limit state (SLS) criteria but also deals with the economics simultaneously. In that regard, the generalized reduced gradient (GRG) method is used for the optimization purpose to achieve the least construction cost of an isolated foundation along with the integration of design parameters as optimization variables. The optimization technique is elaborated using a design example in silty clayey soil and the results of the optimized design are compared with those of the conventional design. The optimization model shows that the optimized design can reduce the construction cost by up to 44% as compared to the conventional design cost for the particular example. Moreover, a sensitivity analysis is also performed to evaluate the quantitative impact of cohesive soil properties, design load, and groundwater table on the construction cost. The results indicate that the construction cost majorly depends on the combined effect of four key parameters: Young’s modulus, recompression index, design load, and groundwater table.

Cost-based Optimization of Multistore Query Plans

Multistores are data management systems that enable query processing across different and heterogeneous databases; besides the distribution of data, complexity factors like schema heterogeneity and data replication must be resolved through integration and data fusion activities. Our multistore solution relies on a dataspace to provide the user with an integrated view of the available data and enables the formulation and execution of GPSJ queries. In this paper, we propose a technique to optimize the execution of GPSJ queries by formulating and evaluating different execution plans on the multistore. In particular, we outline different strategies to carry out joins and data fusion by relying on different schema representations; then, a self-learning black-box cost model is used to estimate execution times and select the most efficient plan. The experiments assess the effectiveness of the cost model in choosing the best execution plan for the given queries and exploit multiple multistore benchmarks to investigate the factors that influence the performance of different plans.

Cost-Based or Learning-Based?

Traditional cost-based optimizers are efficient and stable to generate optimal plans for simple SQL queries, but they may not generate high-quality plans for complicated queries. Thus learning-based optimizers have been proposed recently that can learn high-quality plans based on past experiences. However, learning-based optimizers cannot work well for dynamic workloads that have different distributions with training examples. In this paper, we propose a hybrid optimizer that adopts the advantages and avoids the shortcomings of these two types of optimizers, which first generates high-quality candidate plans from each type of optimizers and then selects the best plan from the candidates. There are two challenges. (1) How to generate high-quality candidates? We propose a hint-based candidate generation method that leverages the learning-based method to generate highly beneficial hints and then uses a cost-based method to supplement the hints to generate complete plans as candidates. (2) How to evaluate different candidate plans and select the best one? We propose an uncertainty-based optimal plan selection model, which predicts the execution time and the uncertainty for each plan. The uncertainty reflects the confidence of the execution time prediction. We select the plan using the uncertainty model. Experiment results on real datasets showed that our method outperformed the state-of-the-art baselines, and reduced the total latency by 25% and the tail latency by 65% compared to PostgreSQL.