In this paper, we study the query optimization problem in HTAP systems and propose a cost-based approach that can judiciously generate a hybrid plan by leveraging a primary row store and an in-memory column store to maximize the query performance. We propose a new hybrid plan based optimization system, named SmartPlan, which is built upon such a hybrid architecture. We make three contributions. First, we design a new cost model to quantify the hybrid plan cost, and propose a plan search method that efficiently finds the optimal plan in a huge planning space. Second, we take into account the memory budget and design a plan-aware Monte Carlo Tree Search method to select the most beneficial columns into the memory. Third, we have implemented our method in PostgreSQL v15 and have evaluated its effectiveness using standard benchmarks. Experiments demonstrate that SmartPlan outperforms the state-of-the-art approaches in terms of end-to end performance with both analytical and HTAP benchmarks.

Since Gartner coined the term, Hybrid Transactional and Analytical Processing (HTAP), numerous HTAP databases have been proposed to combine transactions with analytics in order to enable real-time data analytics for various data-intensive applications. HTAP databases typically process the mixed workloads of transactions and analytical queries in a unified system by leveraging both a row store and a column store. As there are different storage architectures and processing techniques to satisfy various requirements of diverse applications, it is critical to summarize the pros and cons of these key techniques. This paper offers a comprehensive survey of HTAP databases. We mainly classify state-of-the-art HTAP databases according to four storage architectures: (a) Primary Row Store and In-Memory Column Store; (b) Distributed Row Store and Column Store Replica; (c) Primary Row Store and Distributed In-Memory Column Store; and (d) Primary Column Store and Delta Row Store. We then review the key techniques in HTAP databases, including hybrid workload processing, data organization, data synchronization, query optimization, and resource scheduling. We also discuss existing HTAP benchmarks. Finally, we provide the research challenges and opportunities for HTAP techniques.

Query rewriting is a challenging task that transforms a SQL query to improve its performance while maintaining its result set. However, it is difficult to rewrite SQL queries, which often involve complex logical structures, and there are numerous candidate rewrite strategies for such queries, making it an NP-hard problem. Existing databases or query optimization engines adopt heuristics to rewrite queries, but these approaches may not be able to judiciously and adaptively apply the rewrite rules and may cause significant performance regression in some cases (e.g., correlated subqueries may not be eliminated). To address these limitations, we introduce LearnedRewrite, a query rewrite system that combines traditional and learned algorithms (i.e., Monte Carlo tree search + hybrid estimator) to rewrite queries. We have implemented the system in Calcite, and experimental results demonstrate LearnedRewrite achieves superior performance on three real datasets.