Query Execution Research Articles

PLAQUE: Automated Predicate Learning at Query Time

Predicate pushing down is a key optimization used to speed up query processing. Much of the existing practice is restricted to pushing predicates explicitly listed in the query. In this paper, we consider the challenge of learning predicates during query execution which are then exploited to accelerate execution. Prior related approaches with a similar goal are restricted (e.g., learn only from only join columns or from specific data statistics). We significantly expand the realm of predicates that can be learned from different query operators (aggregations, joins, grouping, etc.) and develop a system, entitled PLAQUE, that learns such predicates during query execution. Comprehensive evaluations on both synthetic and real datasets demonstrate that the learned predicate approach adopted by PLAQUE can significantly accelerate query execution by up to 33x, and this improvement increases to up to 100x when User-Defined Functions (UDFs) are utilized in queries.

SWIX: A Memory-efficient Sliding Window Learned Index

Data stream processing systems enable querying over sliding windows of streams of data. Efficient index structures for the streaming window are a crucial building block to enable querying the sliding window for operations such as aggregation and joins. This paper proposes SWIX, a novel memory-efficient learned index for sliding windows. Unlike conventional learned indexes that rely on tree structures to achieve logarithmic query cost, SWIX has a flat structure that uses substantially less memory and enables efficient query execution while having a low cost for index maintenance when inserting (and retraining). SWIX dynamically adapts itself to the real-time distribution shifts of data streams. SWIX outperforms existing indexes in terms of query execution time and memory footprint for workloads characterized by very frequent updates. Our results show that SWIX has a significantly smaller memory footprint than conventional, streaming, and learned indexes, using only 22% to 42% of the size compared to state-of-the-art approaches, yet outperforming them by up 1.2× to 1.6× on average (and up to 52×) in terms of query time, making it a space- and time-efficient method for indexing data streams. For concurrent learned indexes, Parallel SWIX can achieve up to 3.45× throughput with only 34% of memory consumption.

LPLM: A Neural Language Model for Cardinality Estimation of LIKE-Queries

Cardinality estimation is an important step in cost-based database query optimization. The accuracy of the estimates directly affects the ability of an optimizer to identify the most efficient query execution plan correctly. In this paper, we study cardinality estimation of LIKE-queries, i.e., queries that use the LIKE-operator to match a pattern with wildcards against string-valued attributes. While both traditional and machine-learning-based approaches have been proposed to tackle this problem, we argue that they all suffer from drawbacks. Most importantly, many state-of-the-art approaches are not designed for patterns that contain wildcards in-between characters. Based on past research on neural language models, we introduce the LIKE-Pattern Language Model (LPLM) that uses a new language and a novel probability distribution function to capture the semantics of general LIKE-patterns. We also propose a method to generate training data for our model. We demonstrate that our method outperforms state-of-the-art approaches in terms of precision (Q-error), while offering comparable runtime performance and memory requirements.

Starling: An I/O-Efficient Disk-Resident Graph Index Framework for High-Dimensional Vector Similarity Search on Data Segment

High-dimensional vector similarity search (HVSS) is gaining prominence as a powerful tool for various data science and AI applications. As vector data scales up, in-memory indexes pose a significant challenge due to the substantial increase in main memory requirements. A potential solution involves leveraging disk-based implementation, which stores and searches vector data on high-performance devices like NVMe SSDs. However, implementing HVSS for data segments proves to be intricate in vector databases where a single machine comprises multiple segments for system scalability. In this context, each segment operates with limited memory and disk space, necessitating a delicate balance between accuracy, efficiency, and space cost. Existing disk-based methods fall short as they do not holistically address all these requirements simultaneously. In this paper, we present Starling, an I/O-efficient disk-resident graph index framework that optimizes data layout and search strategy within the segment. It has two primary components: (1) a data layout incorporating an in-memory navigation graph and a reordered disk-based graph with enhanced locality, reducing the search path length and minimizing disk bandwidth wastage; and (2) a block search strategy designed to minimize costly disk I/O operations during vector query execution. Through extensive experiments, we validate the effectiveness, efficiency, and scalability of Starling. On a data segment with 2GB memory and 10GB disk capacity, Starling can accommodate up to 33 million vectors in 128 dimensions, offering HVSS with over 0.9 average precision and top-10 recall rate, and latency under 1 millisecond. The results showcase Starling's superior performance, exhibiting 43.9x higher throughput with 98% lower query latency compared to state-of-the-art methods while maintaining the same level of accuracy.

Optimizing Nested Recursive Queries

Datalog is a declarative programming language that has gained popularity in various domains due to its simplicity, expressiveness, and efficiency. But "pure" Datalog is limited to monotone queries, and cannot be used in most practical applications. For that reason, newer systems are relaxing the language by allowing non-monotone queries to be freely combined with recursion. But by departing from the elegant fixpoint semantics of pure datalog, these systems often result in inefficient query execution, for example they perform redundant computations, or use redundant storage. In this paper, we propose Temporel, a system that allows recursion to be freely combined with non-monotone operators. Temporel optimizes the program by compiling it into a novel intermediate representation that we call TempoDL. Our experimental results show that our system outperforms a state-of-the-art Datalog engine as well as a vectorized and a compiled in-memory database system for a wide range of applications from machine learning to graph processing.

Integrating Novel Machine Learning for Big Data Analytics and IoT Technology in Intelligent Database Management Systems

Database Management Systems (DBMS) advancement has been crucial to Information Technology (IT). Traditional DBMS needed help managing large and varied datasets under strict time constraints due to the emergence of Big Data and the widespread use of Internet of Things (IoT) devices. The growing intricacy of data and the need for instantaneous processing presented substantial obstacles. This research suggests a Machine Learning-based Intelligent Database Management Systems (ML-IDMS) technique. This invention combines the skills of Machine Learning with DBMS, improving flexibility and decision-making capacities. The ML-IDMS is specifically developed to tackle current obstacles by providing capabilities such as instantaneous data retrieval, intelligent heat measurement, and effective neural network initialization. The simulation results showcase the effectiveness of ML-IDMS, as shown by impressive metrics such as query execution time (19.27 sec), storage efficiency (83.78%), data accuracy (90%), redundancy reduction (66.42%), network throughput (7.93 Gbps), and end-to-end delay (14.4 ms). The results highlight the efficacy of ML-IDMS in managing various data circumstances. ML-IDMS addresses current obstacles and establishes a standard for future intelligent data management and analytics progress.

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.

Query Optimization in Distributed Database Based on Improved Artificial Bee Colony Algorithm

Query optimization is one of the key factors affecting the performance of database systems that aim to enact the query execution plan with minimum cost. Particularly in distributed database systems, due to the multiple copies of the data that are stored in different data nodes, resulting in the dramatic increase in the feasible query execution plans for a query statement. Because of the increasing volume of stored data, the cluster size of distributed databases also increases, resulting in poor performance of current query optimization algorithms. In this case, a dynamic perturbation-based artificial bee colony algorithm is proposed to solve the query optimization problem in distributed database systems. The improved artificial bee colony algorithm improves the global search capability by combining the selection, crossover, and mutation operators of the genetic algorithm to overcome the problem of falling into the local optimal solution easily. At the same time, the dynamic perturbation factor is introduced so that the algorithm parameters can be dynamically varied along with the process of iteration as well as the convergence degree of the whole population to improve the convergence efficiency of the algorithm. Finally, comparative experiments conducted to assess the average execution cost of Top-k query plans generated by the algorithms and the convergence speed of algorithms under the conditions of query statements in six different dimension sets. The results demonstrate that the Top-k query plans generated by the proposed method have a lower execution cost and a faster convergence speed, which can effectively improve the query efficiency. However, this method requires more execution time.

A systematic overview of data federation systems

Data federation addresses the problem of uniformly accessing multiple, possibly heterogeneous data sources, by mapping them into a unified schema, such as an RDF(S)/OWL ontology or a relational schema, and by supporting the execution of queries, like SPARQL or SQL queries, over that unified schema. Data explosion in volume and variety has made data federation increasingly popular in many application domains. Hence, many data federation systems have been developed in industry and academia, and it has become challenging for users to select suitable systems to achieve their objectives. In order to systematically analyze and compare these systems, we propose an evaluation framework comprising four dimensions: (i) federation capabilities, i.e., query language, data source, and federation techniques; (ii) data security, i.e., authentication, authorization, auditing, encryption, and data masking; (iii) interface, i.e., graphical interface, command line interface, and application programming interface; and (iv) development, i.e., main development language, deployment, commercial support, open source, and release. Using this framework, we thoroughly studied 51 data federation systems from the Semantic Web and Database communities. This paper shares the results of our investigation and aims to provide reference material and insights for users, developers and researchers selecting or further developing data federation systems.

LSQ 2.0: A linked dataset of SPARQL query logs

We present the Linked SPARQL Queries (LSQ) dataset, which currently describes 43.95 million executions of 11.56 million unique SPARQL queries extracted from the logs of 27 different endpoints. The LSQ dataset provides RDF descriptions of each such query, which are indexed in a public LSQ endpoint, allowing interested parties to find queries with the characteristics they require. We begin by describing the use cases envisaged for the LSQ dataset, which include applications for research on common features of queries, for building custom benchmarks, and for designing user interfaces. We then discuss how LSQ has been used in practice since the release of four initial SPARQL logs in 2015. We discuss the model and vocabulary that we use to represent these queries in RDF. We then provide a brief overview of the 27 endpoints from which we extracted queries in terms of the domain to which they pertain and the data they contain. We provide statistics on the queries included from each log, including the number of query executions, unique queries, as well as distributions of queries for a variety of selected characteristics. We finally discuss how the LSQ dataset is hosted and how it can be accessed and leveraged by interested parties for their use cases.

MonARCh: an actor based architecture for dynamic linked data monitoring.

Monitoring the data sources for possible changes is an important consumption requirement for applications running in interaction with the Web of Data. In this article, MonARCh which is an architecture for monitoring the result changes of registered SPARQL queries in the Linked Data environment, is proposed. MonARCh can be comprehended as a publish/subscribe system in the general sense. However, it differs in how communication with the data sources is realized. Data sources in the Linked Data environment do not publish the changes in the data. MonARCh provides the necessary communication infrastructure between the data sources and the consumers for the notification of changes. Users subscribe SPARQL queries to the system which are then converted to federated queries. MonARCh periodically checks for updates by re-executing SERVICE clauses and notifying users in case of any result change. In addition, to provide scalability, MonARCh takes the advantage of concurrent computation of the actor model. The parallel join algorithm utilized speeds up query execution and result generation processes. The design science methodology is used during the design, implementation and evaluation of the architecture. When compared to the literature MonARCh meets all the sufficient requirements from the linked data monitoring and state of the art perspectives while having many outstanding features from both points of view. The evaluation results show that even while working under the limited two-node cluster setting MonARCh could reach from 300 to 25,000 query monitoring capacity according to the diverse query selectivities executed within our test bench.

Proposed framework for enhancing integrity technique using distributed query operation

Database growth and Storage problems are main concern for large and small enterprises nowadays, which significantly influences the efficiency of database applications. Database archiving is one of the solutions available for management. Many issues have been identified as a result of the use of archive databases, including the elimination of inactive data from online transaction processing systems (OLTP), performance and integrity management. The aim of this paper is to propose a framework for storing OLTP and archiving data in a distributed database environment as part of an integrated system. Maintaining the integrity between an OLTP database and an archiving database is crucial, but even more critical is the performance of the required query. The main aspect of the proposed framework is that it will not only ensure data integrity for primary and unique keys between OLTP and archive database, but it will also improve query performance by introducing parallel processing and query execution plans to maintain that integrity.

Enhancing MongoDB query performance through index optimization

This article delves into the critical aspect of enhancing query performance in MongoDB through meticulous index optimization. It begins with an introduction to MongoDB's unique document-oriented data storage approach and its inherent scalability, which sets the stage for understanding the importance of efficient query processing. The discussion progresses to highlight the pivotal role of indexes in MongoDB, emphasizing their function in expediting data retrieval and the necessity for their optimization to ensure peak database performance. A detailed exploration is provided on the methodologies for identifying fields suitable for indexing, considering factors such as query frequency and the specific use of fields in query operations. The article further elaborates on the selection of optimal index types, tailored to the diverse needs of varying data and query scenarios, thereby underscoring the versatility of MongoDB's indexing capabilities. Management of index size is discussed as a critical component of optimization, addressing the balance between index efficiency and resource consumption. The utilization of MongoDB's query planner is showcased as a powerful tool for achieving an in-depth understanding of query execution and identifying potential optimizations. In conclusion, the article encapsulates the essence of continuous index management and the strategic use of MongoDB's analytical tools to maintain and enhance database performance. It underscores the ongoing nature of optimization efforts required to keep pace with evolving data patterns and application demands, ultimately ensuring a responsive, efficient, and scalable database environment.

A Systematic Approach for Converting Relational to Graph Databases

In database design, a system can be abstracted into three conceptual elements: a collection of entities, the relationships among them, and the attributes describing each entity. The database serves as a system for storing data through the mentioned conceptual elements. Different database design approaches are customized to suit particular use cases e.g. the comparison between graph databases and relational databases. Graph databases are particularly wellsuited for handling data with dense relationships, as they are designed to store and represent complex networks of interconnected data. Relational databases pose a challenge in scenarios where the graph would be better suited. The migration process involves restructuring the data and adapting the application logic which can be resource-intensive and time-consuming. Current solutions for database migration are often too generalized, resulting in a lack of effectiveness in addressing common migration cases. These solutions fail to provide the necessary level of specificity required to overcome the challenges that arise during the migration process. This paper proposes a structured approach for transferring data from a relational to a graph database. The proposed approach introduces strategies dedicated to the conversion of specific relational elements, such as associations, specializations, and many-to-many relationships. The approach was tested using Microsoft’s Northwind sample database. Upon transferring the data from a relational to a graph database, the paper reports that queries produced identical results, indicating that the details of the data were accurately preserved during the migration. Following an experimental analysis, the results indicate that the proposed approach exhibits better performance, as evidenced by shorter query execution times. These findings affirm the feasibility and veracity of the proposed approach.

Resource Indexing and Querying in Large Connected Environments

The proliferation of sensor and actuator devices in Internet of things (IoT) networks has garnered significant attention in recent years. However, the increasing number of IoT devices, and the corresponding resources, has introduced various challenges, particularly in indexing and querying. In essence, resource management has become more complex due to the non-uniform distribution of related devices and their limited capacity. Additionally, the diverse demands of users have further complicated resource indexing. This paper proposes a distributed resource indexing and querying algorithm for large connected environments, specifically designed to address the challenges posed by IoT networks. The algorithm considers both the limited device capacity and the non-uniform distribution of devices, acknowledging that devices cannot store information about the entire environment. Furthermore, it places special emphasis on uncovered zones, to reduce the response time of queries related to these areas. Moreover, the algorithm introduces different types of queries, to cater to various user needs, including fast queries and urgent queries suitable for different scenarios. The effectiveness of the proposed approach was evaluated through extensive experiments covering index creation, coverage, and query execution, yielding promising and insightful results.

Concurrency versus consistency in NoSQL databases

<p>With the advent of cloud services, the proliferation of data has reached unprecedented levels. The load distribution across multiple servers, driven by web and mobile applications, has become a defining characteristic of contemporary data management. In contrast to this surge in data complexity, traditional relational databases have proven inadequate in handling vast amounts of unstructured data due to their inherent focus on structured data models. Additionally, the concept of clustering, vital for efficient unstructured data management, eluded relational databases, rendering them ill-equipped for customized clustering techniques and the optimal execution of queries. SQL (Structured Query Language) databases earlier emerged as a groundbreaking solution, introducing the relational database model that organized data into structured tables. They employed ACID (atomicity, consistency, isolation, durability) properties to maintain data integrity and enabled intricate querying through SQL. However, as applications grew in complexity, SQL databases encountered hurdles in handling various data types, rapid data expansion, and concurrent workloads. The limitations of SQL databases propelled the rise of NoSQL (Not Only Structured Query Language) databases, which prioritized adaptability, scalability, and performance. NoSQL databases embraced diverse data models such as documents, key-values, column families, and graphs, enabling effective management of structured, semi-structured, and unstructured data. The transition to NoSQL databases was justified by several factors; horizontally scaled across nodes, handling extensive read-write operations effectively, Agile development of accommodating changing data structures without schema constraints, optimization for specific tasks, providing low-latency access and high throughput, dynamic schemas aligned with modern iterative development, promoting adaptability, and adeptly managed diverse data types, spanning text, geospatial, time-series, and multimedia data. These databases are purposefully designed to accommodate the escalating demands of data storage. Notably, this data emanates from diverse nodes and is susceptible to concurrent access by numerous users. However, a critical challenge surfaces as the data present on one node may diverge from its counterpart on another node replica. In this context, the simultaneous execution of database operations, while preserving the integrity of the data, emerges as a pivotal concern. Maintaining data consistency amid concurrent access hinges upon the synchronization of operations across all replica nodes. Achieving this synchronization necessitates the adoption of a robust concurrency control technique. Concurrency control acts as the linchpin for upholding accuracy and reliability within a system where operations unfold concurrently. Hence, the focal point of this investigation lies in examining the assorted concurrency control methodologies employed by NoSQL systems. The objective is to dissect the intricate interplay between concurrency and consistency, shedding light on the strategies these systems employ to strike an optimal balance between the two. In summation, as the landscape of data management witnesses an era of exponential growth catalyzed by cloud services, the dynamics of load distribution and unstructured data have necessitated a departure from traditional relational databases. NoSQL databases have risen to the fore, demonstrating the ability to grapple with these challenges. However, the quest for concurrent data access without compromising data consistency propels the exploration of various concurrency control methods. The aim of this study is to look at some of the different concurrency control approaches employed by NoSQL systems, highlighting how they priorities concurrency and consistency.</p>

HPCache: memory-efficient OLAP through proportional caching revisited

Analytical engines rely on in-memory data caching to avoid storage accesses and provide timely responses by keeping the most frequently accessed data in memory. Purely frequency- and time-based caching decisions, however, are a proxy of the expected query execution speedup only when storage accesses are significantly slower than in-memory query processing. On the other hand, fast storage offers loading times that approach fully in-memory query response times, rendering purely frequency-based statistics incapable of capturing the impact of a caching decision on query execution. For example, caching the input of a frequent query that spends most of its time processing joins is less beneficial than caching a page for a slightly less frequent but scan-heavy query. Thus, existing caching policies waste valuable memory space to cache input data that offer little-to-no acceleration for analytics. This paper proposes HPCache, a buffer management policy that enables fast analytics on high-bandwidth storage by efficiently using the available in-memory space. HPCache caches data based on the speedup potential instead of relying on frequency-based statistics. We show that, with fast storage, the benefit of in-memory caching varies significantly across queries; therefore, we quantify the efficiency of caching decisions and formulate an optimization problem. We implement HPCache in Proteus and show that (i) estimating speedup potential improves memory space utilization, and (ii) simple runtime statistics suffice to infer speedup. We show that HPCache achieves up to a 1.75x speed-up over frequency-based caching policies by caching column proportions and automatically tuning them. Overall, HPCache enables efficient use of the in-memory space for input caching in the presence of fast storage, without requiring workload predictions.

Estimating the Cardinality of Queries Based on a Sample from a Full Outer Join of Tables

Cardinality estimation (CardEst) plays an important role in creating high-quality query execution plans in the DBMS. In the last decade, a large number of methods have been developed: traditional methods (histograms, samples), machine learning methods based on queries or data. But all of them are based on different restrictions and assumptions, and the cardinality estimation with their help worsens with an increase in the number of joined tables. The article proposes two new methods based on the theory of approximate calculation of aggregates and allowing to remove most of the restrictions. Method 1 fetches blocks after executing the subqueries of the original query and does not require a preliminary analysis of the filter conditions. The condition for joining tables can be arbitrary (not necessarily the equality of attributes). Method 2 allows you to calculate the probabilities of reading blocks based on the metadata accumulated in the process of populating the database. Metadata takes up little memory, and the overhead of maintaining it is low. Method 2, in contrast to method 1, takes into account the true cardinality of the connection of neighboring blocks in the sample obtained from metadata. Therefore, the prospect opens up for a more accurate assessment of the cardinality of joining a large number of tables. Method 1 (EVACAR method) is implemented and compared with modern machine learning methods BayesCard, DeepDB, FLAT on a special STATS test. The results of the experiments confirmed the effectiveness of the EVACAR method. The EVACAR method is more accurate or its maximum q-error is comparable to machine learning methods for 75-88 % of evaluated queries (subplans). In the future, it is planned to implement the 2nd method for assessing the cardinality of queries.

Natural Language-Driven Dialogue Systems for Support in Physical Medicine and Rehabilitation

This paper presents a natural language-driven dialogue system designed to support healthcare professionals and students in the field of physical medicine and rehabilitation. The system seamlessly integrates concepts from intelligent information systems, data mining, ontologies, and human-computer interaction, employing at its core a rule-based dialogue mechanism. The system harnesses the power of ontology-based graph knowledge, underscoring its domain-specific efficacy. This article delves into the automated knowledge base formation, utilising Python scripts to translate EBSCO’s dataset of articles on physical medicine and rehabilitation into an OWL ontology. This methodology ensures adaptability to the ever-evolving landscape of medical insights. The system’s approach to natural language processing encompasses text preprocessing, semantic category discernment, andSPARQL query creation, providing 26 predefined categories. As an innovation in performance optimisation, the system integrates a strategy to cache precomputed responses using a PostgreSQL database, which aids in resource conservation and reduction in query execution latency. The system’s user engagement avenues are further detailed, showcasing a Telegram bot and an API, enhancing accessibility and user experience. In essence, this article illuminates an advanced, efficient dialogue system for physical medicine and rehabilitation, synthesising multiple computational paradigms, and standing as a beacon for healthcare practitioners and students alike.

Quartet: A Query Aware Database Adaptive Compilation Decision System

The executor is an important component of a database. Typical executors that are applied in modern database systems follow either the VOLCANO model or Compiled model, each of which fits some scenarios but not all. Even the widely employed PostgreSQL (PGSQL) and CockroachDB (CRDB) have to rely on human experts to achieve the optimal execution mode. Nevertheless, the accuracy of these decisions is only 32.8% on average, even with an expert involved. Moreover, due to the exclusive use of rule-based strategies, it is not feasible to reasonably switch between two working modes when confronted with different queries. To solve this problem, we propose a QUery awARe daTabase adaptivE compilaTion decision system (Quartet), which can determine the most suitable execution mode with respect to the current workload at runtime. Quartet generates operator-cost and tree-based vectors by analysing the query execution plan (QEP) and then uses the fully connected neural network (FCNN) and tree-based convolutional neural network (TBCNN) to learn the relationship between the QEP and the optimal execution. Our evaluations show that Quartet can improve execution decision accuracy by 60% on average under TPC-H (under 3 GB) workloads.