Query Optimization for Hybrid Plans in Row–Column Dual Store HTAP Databases

Abstract

As data volumes grow and business requirements become increasingly complex, Hybrid Transactional/Analytical Processing (HTAP) technologies, capable of handling both Online Transaction Processing (OLTP) and Online Analytical Processing (OLAP) workloads on a single platform, have gained prominence. HTAP databases typically maintain dual data storage formats and dual query engines: one row-oriented for OLTP, and another column-oriented for OLAP. Query plans, known as hybrid plans, can be segmented and pushed down to execute on these different formats. However, existing HTAP solutions still face challenges in optimizing these hybrid plans, struggling to explore the vast space of potential execution strategies effectively. To address these issues, this study introduces a learning-based query optimizer for row–column dual store HTAP database systems, which automatically generates multiple high-quality query optimizer hints (HINTs) to derive candidate plans. To balance plan generation efficiency with plan quality, a lightweight, learning-based algorithm using Monte Carlo Tree Search (MCTS) for generating hybrid access HINTs is proposed. Moreover, a Transformer-based neural network model coupled with a hybrid plan feature representation method is developed to select the candidate execution plan with the lowest predicted execution time. This work focuses on latency-oriented hybrid-plan selection for analytical queries in a row–column dual-store HTAP architecture; the current evaluation does not cover full mixed OLTP/OLAP workload scheduling, transactional interference, or concurrency control, which are left as future work. Experimental results on AlloyDB Omni, a recent row–column dual-store HTAP database, using the real-world IMDB dataset and JOB benchmark demonstrate that our system reduces execution time by 75.02% compared to the Cost-Based Optimizer (CBO) and by 62.23% compared to the state-of-the-art row-store-based learning query optimizer in this evaluated analytical-query setting.

1. Introduction

With the emergence of large-scale real-time analytical applications in modern society, many business scenarios require the ability to simultaneously handle high-concurrency transactional requests and perform real-time analysis on the latest data. In traditional data processing workflows, Online Transaction Processing (OLTP) systems handle day-to-day business transactions—such as order processing and inventory management—which are typically high-frequency, low-latency operations requiring fast responses and data consistency guarantees. However, to conduct data analysis and support decision-making, data from OLTP systems must be extracted through an Extract-Transform-Load (ETL) process and loaded into Online Analytical Processing (OLAP) systems.

Hybrid Transactional/Analytical Processing (HTAP) technology provides a more efficient alternative. As illustrated in Figure 1, unlike the traditional loosely coupled architecture combining separate OLTP databases and OLAP data warehouses, HTAP technology adopts a unified architecture that simultaneously supports OLTP and OLAP on a single platform. This eliminates the time-consuming ETL process and enables rapid analysis of the latest transactional data, thereby improving overall system throughput and query response time [1,2]. Representative HTAP databases include Oracle [3], SQL Server [4], DB2 BLU [5], SAP HANA [6], TiDB [7], PolarDB-IMCI [8], RateupDB [9], and AlloyDB [10].

Current mainstream HTAP databases such as TiDB [7] and Oracle [3] adopt a row–column co-existing data storage approach [2]. Unlike previous storage approaches based on pure row, pure column, or hybrid models such as PAX [11], the row–column co-existing approach goes a step further: it uses row storage as the primary format and column storage as the auxiliary. In this storage mode, write-intensive OLTP queries can directly access row storage to ensure low latency, while OLAP analytical queries can quickly retrieve data from column store replicas. The column store replicas are periodically updated incrementally from the row store to maintain consistency. Moreover, some HTAP databases support placing column store replicas directly in memory (In-Memory Column Store, IMCS) [3,4,5,8,10,12,13,14,15,16], further accelerating query response times by eliminating disk I/O.

To fully exploit the potential of hybrid physical layouts, many HTAP systems [4,7,10] support executing a single query across different storage formats to generate hybrid plans. Despite this capability, current HTAP systems still have shortcomings in fully leveraging the combined advantages of row and column storage [17].

1.1. Motivation

For an SQL query, a database supporting hybrid queries can generate a hybrid plan based on its cost model. A HINT [18] is an extended syntax in SQL queries used to guide the database system in generating specific query execution plans. Under different HINTs, the query plan and execution performance of the same SQL query can differ significantly. As illustrated in Figure 2, under five different access-path HINTs, a single SQL query generates five distinct execution plans, including row plans, column plans, and hybrid plans. The default plan (generated without a HINT) corresponds to one specific hybrid plan, but it is not necessarily optimal.

Several key observations motivate our work:

1.

Unified cost model lacking. Current HTAP database query optimization systems [3,4,8,10] primarily rely on row-based cost models to generate execution plans. They lack a unified framework that can simultaneously quantify the costs of row-level and column-level operators. As shown in Figure 2, the plan estimated to have the lowest cost by the optimizer does not necessarily have the shortest execution time.

2.

Complex hybrid plan generation space. For a query involving m tables, the number of possible hybrid access HINTs is $2^m$, representing an exponentially large search space. Current HTAP systems [3,4,7,8,10] either do not support hybrid plans, directly route queries to row or column storage [8], or use simple cost models to determine access paths without comprehensively considering computational operators [7].

3.

Limitations of cost-based optimizers. While learning-based query optimizers such as DQ [19], ReJOIN [20], RTOS [21], and Neo [22] have shown promise, these representative learning-based optimizers mainly focus on conventional single-engine or row-store plans. To the best of our knowledge, existing learning-based query optimization studies mainly focus on cardinality estimation, cost estimation, join ordering, plan ranking, or query plan representation in conventional single-engine database settings. Learning-based optimization for hybrid plan generation in row–column dual-store HTAP databases remains relatively underexplored [23,24].

To make the discussion above more concrete,

Table 1. Comparison between existing query optimization approaches and the proposed method.

Approach	Main Idea	Limitation for Row–Column Dual-Store HTAP	Difference of Our Work
Traditional cost-based optimization	Uses statistics and handcrafted cost models	Hard to accurately model hybrid row/column access choices	Learns execution behavior directly from candidate hybrid plans
Learned cardinality/cost estimation	Improves optimizer components using machine learning	Usually targets conventional plans or single-engine settings	Predicts execution time for hybrid row/column access alternatives
Learning-to-rank optimization	Learns relative quality of candidate plans	Often relies on plans generated by native optimizers	Combines MCTS-based HINT search with a learned predictor
HTAP query optimization	Optimizes analytical queries over mixed storage/workloads	Often requires system-specific rules and cost models	Focuses on hybrid plan selection in row–column dual-store architecture
Proposed method	Generates and evaluates hybrid row–column plans	Current work focuses on analytical queries	Provides a learning-based hybrid-plan selection framework

contrasts the proposed method with representative families of existing query-optimization techniques along three dimensions: main idea, limitation in a row–column dual-store HTAP setting, and the specific difference of our work.

1.2. Solution Overview

To address these challenges, we propose a machine learning-based hybrid plan query optimization system. The system models hybrid access HINT generation as a tree search problem and employs Monte Carlo Tree Search (MCTS) to efficiently explore good hybrid access HINTs, achieving a balance between exploration and exploitation. For each candidate HINT on the tree, a neural network quickly computes its benefit score. We further adopt an enhanced Transformer-based [25] neural network that captures both global query features and local hybrid physical plan features, enabling accurate execution time prediction for hybrid plans.

1.3. Contributions

The main contributions of this paper are as follows:

1.

We design and implement a learning-based hybrid-plan query optimization framework for row–column dual-store HTAP databases, addressing a setting that, to the best of our knowledge, has received limited attention from prior learning-based query optimizers.

2.

We propose a hybrid access HINT generation method combining MCTS with neural networks, which can quickly and effectively explore good full-length hybrid access HINTs, achieving a balance between exploration and exploitation.

3.

We adapt an existing Transformer-based plan-representation model [26] to candidate hybrid plans, and integrate it with MCTS-based hybrid HINT generation. We do not claim the Transformer architecture itself as a new general-purpose query-plan representation model: components such as height encoding, tree-aware attention, and the global query node are inspired by prior neural query-plan representation studies; the contribution lies in adapting and integrating these techniques in the row–column dual-store HTAP setting.

4.

Experimental results on AlloyDB Omni, using the real-world IMDB dataset and JOB benchmark, demonstrate that the proposed method reduces execution time by 75.02% compared with the cost-based optimizer and by 62.23% compared with the state-of-the-art row-store-based learning query optimizer in the evaluated analytical-query setting.

1.4. Scope and Terminology

Throughout this paper, a hybrid plan refers to a complete physical execution plan that may combine row-store and column-store access operators. A hybrid path (or hybrid access path) refers to an access path or partial execution path within a hybrid plan, specifying for each base relation whether it is accessed from row storage or column storage. A hybrid HINT (or hybrid access HINT) is the optimizer hint, generated by our MCTS-based search process, that instructs the DBMS to follow a specific hybrid access pattern. These three terms are used consistently in this sense throughout the paper. The optimization target of this work is latency-oriented hybrid-plan selection for analytical queries; mixed OLTP/OLAP workload scheduling, transactional interference, and concurrency control are out of scope and are discussed as future work.

1.5. Problem Statement

We state the optimization problem here for clarity; the detailed search-space construction (hybrid access HINT tree) and the role of each module are introduced in Section 2.

For a query q over a schema S with relations $T=\{t_1,\dots ,t_m\}$, let $P_q^S$ denote the set of all candidate execution plans that the underlying HTAP optimizer can generate, and let $t\left(p\right)$ denote the end-to-end execution latency of a plan $p\in P_q^S$. The objective is to identify the plan with minimum execution latency:

$$p^{\ast }=arg\underset{p\in P_q^S}{min}t\left(p\right).$$

(1)

Intuitively, $P_q^S$ is exponentially large because each of the m base relations can be accessed from either row or column storage, yielding $2^m$ different hybrid access patterns. To make the problem tractable, we reformulate it through hybrid access HINTs: let $\mathcal{O}(q,S,T_r,T_c)$ denote the plan produced by the cost-based optimizer when relations in $T_r$ are forced to row storage and relations in $T_c$ to column storage. The reformulated objective is then

$$\underset{(T_r,T_c)}{min}t\left(\mathcal{O}(q,S,T_r,T_c)\right),$$

(2)

where $(T_r,T_c)$ ranges over all partitions of T. The MCTS-based search (Section 3) explores promising $(T_r,T_c)$ partitions, and the Transformer-based predictor (Section 4) approximates the unknown latency function $t(·)$ so that the best-predicted candidate plan can be selected. Equation (2) captures only execution latency; system-level factors such as memory budget, transactional interference, data-freshness constraints, and concurrency-control overhead can be incorporated as constraints in future work.

The remainder of this paper is organized as follows. Section 2 presents the system overview. Section 3 describes the hybrid path and candidate plan generation module. Section 4 details the execution time prediction model. Section 5 presents the experimental evaluation. Section 6 reviews related work, and Section 7 concludes the paper.

2. System Overview

In this section, we first explain key preliminary concepts, then present the overall system architecture.

2.1. Preliminaries

2.1.1. Hybrid Access HINT

A hybrid access HINT refers to extended syntax that forces an SQL query to use access paths with different storage formats. Formally, given the current schema S with m relations represented by $T=\{t_1,t_2,\dots ,t_m\}$, let $T_r$ and $T_c$ be two disjoint subsets of T. For any query q, a hybrid access HINT is denoted as $H_q^{(T_r,T_c)}$, indicating that tables in $T_r$ are read from row storage and tables in $T_c$ are read from column storage. For example, in AlloyDB, the HINT is expressed as:

$$\begin{array}{cc}\hfill /\ast +& \mathtt{ColumnarScan}\left(\mathtt{a}\right),\mathtt{ColumnarScan}\left(\mathtt{b}\right),\hfill \\ \hfill & \mathtt{NoColumnarScan}\left(\mathtt{c}\right),\mathtt{NoColumnarScan}\left(\mathtt{d}\right)\ast /\hfill \end{array}$$

while in TiDB it is:

$$\begin{array}{cc}\hfill /\ast +& \mathtt{READ}\_\mathtt{FROM}\_\mathtt{STORAGE}\left(\mathtt{TIFLASH}\right[\mathtt{a},\mathtt{b}],\mathtt{TIKV}[\mathtt{c},\mathtt{d}\left]\right)\ast /\hfill \end{array}$$

2.1.2. Hybrid Access HINT Tree

Given m relations under schema S for query q, a full-length hybrid access HINT $H_q^{(T_r,T_c)}$ where $(T_r,T_c)$ is a partition of T has $2^m$ possible values. Exhaustively exploring this space is prohibitively expensive. Therefore, we construct a hybrid access HINT tree ${\mathcal{T}}_q$ to accelerate the search.

The tree has height $m+2$. Leaf nodes are called terminal nodes; all other nodes are search nodes. The root node represents the empty set. Each search node can be expanded by adding a table from q (with ordering constraints) or by stopping the search. Search nodes represent sets of tables assigned to column storage ($T_c$), while terminal nodes represent complete full-length hybrid access HINTs. The tree structure is shown in the upper-left part of Figure 3.

2.1.3. Problem Definition

The formal problem statement was introduced in Section 1.5; we recall it here in the notation just established and connect it to the search-space construction.

For any query q under schema S, let $P_q^S$ denote the set of all possible execution plans and $t\left(p\right)$ denote the execution time of plan p. The optimization objective for hybrid plan query optimization in HTAP databases is:

$$p^{\ast }=\arg \underset{p\in P_q^S}{\min }t\left(p\right)$$

(3)

Since directly solving Equation (3) is impractical, we reformulate the problem using HINTs. The optimizer with a hybrid access HINT applied is denoted as $\mathcal{O}(q,S,T_r,T_c)$. Our optimization objective becomes:

$$\underset{T_r,T_c}{\min }t\left(\mathcal{O}(q,S,T_r,T_c)\right)$$

(4)

Intuitively, instead of searching the full plan space $P_q^S$, we search over $(T_r,T_c)$ partitions on the hybrid access HINT tree ${\mathcal{T}}_q$: each terminal node corresponds to one full-length HINT $H_q^{(T_r,T_c)}$ that, when fed to the optimizer $\mathcal{O}$, yields one candidate plan. Two complementary learned components then make this search tractable in practice: the MCTS-based hybrid HINT generator (Section 3) chooses a small number of promising $(T_r,T_c)$ partitions, and the Transformer-based predictor (Section 4) approximates the unknown latency function $t(·)$ to rank the resulting candidate plans.

2.2. System Architecture

As shown in Figure 3, the system consists of three key modules:

Hybrid path and candidate plan generation. For each incoming query, a hybrid access HINT tree is constructed, and Monte Carlo Tree Search is employed to search for good HINTs. The database query optimizer then generates complete candidate plans from the selected HINTs.

Execution time prediction. A Transformer-based neural network with attention mechanisms predicts the execution time of each candidate plan. The plan with the minimum predicted execution time is selected as the final plan.

Incremental model training and update. After executing the selected plan, the actual execution time is used as training feedback to incrementally improve both the HINT estimator and the Transformer model asynchronously, without blocking the online workload.

2.3. Overall Algorithm

Algorithm 1 shows the overall workflow. The system first uses MCTS with the hybrid access HINT estimator to generate candidate partitions, then selects the top-H HINTs. For each candidate HINT, the optimizer generates a complete hybrid plan. The TreeTransformer model estimates the execution time of each candidate, and the plan with the best predicted performance is selected. During training, the selected plan is executed asynchronously, and feedback updates both models.    

Algorithm 1: Hybrid Access HINT Query Optimizer

3. Hybrid Path and Candidate Plan Generation

The goal of this module is to select H good hybrid access HINTs to generate H candidate plans using the cost-based optimizer.

3.1. Monte Carlo Tree Search for HINT Exploration

Since the full hybrid access path search space is large, we apply Monte Carlo Tree Search (MCTS) on the HINT tree ${\mathcal{T}}_q$. MCTS follows an exploration–exploitation strategy: it focuses on paths that lead to high estimated query performance (exploitation) while also visiting less-explored directions (exploration). The overall module architecture is shown in Figure 4.

3.1.1. Node Utility on the HINT Tree

The node utility $\mathcal{U}\left(\upsilon \right)$ is computed from two factors:

Node benefit score $\mathcal{B}\left(\upsilon \right)$. Each node $\upsilon$ has a benefit score representing the expected performance of full-length hybrid access paths passing through $\upsilon$. For leaf nodes, $\mathcal{B}\left(\upsilon \right)$ is directly computed by the hybrid access HINT estimator (Section 3.2). For non-leaf nodes, let $L\left(\upsilon \right)$ denote the set of leaf nodes that have been estimated and contain $\upsilon$ on their search path:

$$\mathcal{B}\left(\upsilon \right)={\displaystyle \frac{1}{\left|L\right(\upsilon \left)\right|}}\sum _{{\upsilon }^{\prime }\in L\left(\upsilon \right)}\mathcal{B}\left({\upsilon }^{\prime }\right)$$

(5)

Intuitively, $\mathcal{B}\left(\upsilon \right)$ summarizes what is currently known about the sub-tree rooted at $\upsilon$: a non-leaf node inherits the average estimated performance of all full-length hybrid HINTs that have so far been simulated through it, so promising sub-trees accumulate higher scores as more leaves under them are evaluated.

Node exploration degree $\mathcal{F}\left(\upsilon \right)$. This counts the number of times node $\upsilon$ has been visited, ensuring that less-explored nodes are also considered.

Following the Upper Confidence Bound (UCB) approach [27], the node utility is defined as:

$$\mathcal{U}\left(\upsilon \right)=\mathcal{B}\left(\upsilon \right)+\gamma \sqrt{{\displaystyle \frac{ln\left(\mathcal{F}\left({\upsilon }_f\right)\right)}{\mathcal{F}\left(\upsilon \right)}}}$$

(6)

where ${\upsilon }_f$ is the parent of $\upsilon$ and $\gamma$ controls the exploration degree.

Intuitively, the first term exploits sub-trees that have so far yielded fast hybrid plans, while the second term grows whenever a sibling is repeatedly visited and $\upsilon$ is not, so under-explored branches are eventually revisited. The hyper-parameter $\gamma$ trades off exploitation against exploration: small $\gamma$ collapses MCTS toward greedy selection of the current best sub-tree, while large $\gamma$ pushes the search to cover more of the $2^m$ hybrid access patterns.

3.1.2. MCTS Workflow

The MCTS workflow consists of five steps:

• Selection. Starting from the root, iteratively select the child with the highest utility until reaching a node with unexpanded children.
• Expansion. Randomly select an unexpanded child and recursively expand to a leaf node.
• Simulation. Use the hybrid access HINT estimator to evaluate the leaf node’s corresponding hybrid access path.
• Update. Backpropagate the leaf node’s utility to update ancestor nodes.
• Termination. Repeat until the search budget b is exhausted.

After termination, the system selects the top-H hybrid access HINTs with the highest utility for candidate plan generation.

3.2. Hybrid Access HINT Estimator

This subsection constructs a lightweight estimator to predict the performance of a query under a given hybrid access HINT. It takes as input the query features and hybrid access path and outputs the estimated performance via a recurrent neural network.

3.2.1. Query Encoding

Following prior work [21,28,29], we encode query q considering join predicates ($E_q^J$) and filter predicates ($E_q^F$). Specifically, $E_q^J$ is an $m\times m$ matrix where $E_q^J[i,j]=1$ if tables i and j are joined in q. $E_q^F$ is a length-m vector where $E_q^F\left[i\right]$ represents the selectivity of filters on table i (default 1 if no filter exists). The query encoding $E_q$ is obtained by flattening $E_q^J$ and concatenating $E_q^F$.

For example, consider a query SELECT * FROM a, b, c WHERE a.id = b.id AND b.id = c.id AND b.attr2 BETWEEN B1 AND B2 with selectivity 0.6 on table b. Then $E_q^J[0,1]=E_q^J[1,0]=E_q^J[1,2]=E_q^J[2,1]=1$, and $E_q^F\left[1\right]=0.6$, as illustrated in Figure 4.

3.2.2. Hybrid Access Path Representation

Each hybrid access path is a length-$(m+1)$ vector $H_q^{(T_r,T_c)}=[t_1,t_2,\dots ,t_c,\dots ,t_m,t_{m+1}]$, where elements before $t_c$ represent column-store tables ($T_c$), $t_c$ is the transition point, and elements after $t_c$ represent row-store tables ($T_r$). For example, a full column plan is represented as $[a,b,c,d,t_c]$, while a hybrid plan with $\{a,d\}$ in column and $\{b,c\}$ in row is $[a,d,t_c,b,c]$.

We use Long Short-Term Memory (LSTM) networks to capture the sequential features of each path. Each table’s embedding $e\left(t_i\right)$ is learned from training data. The LSTM encodes the path as: $(h_i,m_i)=\mathrm{LSTMUnit}(e\left(t_i\right),h_{i-1},m_{i-1})$. The final path representation is $E_H=h_{m+1}$.

3.2.3. Node Utility Prediction

Given the query encoding $E_q$ and path representation $E_H$, the estimator uses a fully connected network to predict the node benefit score:

$$\mathcal{B}\left(\upsilon \right)=\mathrm{FC}\left(\mathrm{Concat}(E_q,E_H)\right)$$

(7)

Intuitively, $E_q$ encodes what the query joins and filters, while $E_H$ encodes how the partial hybrid path so far assigns base relations to row or column storage; the fully connected layer learns a fast, lightweight mapping from this pair to an estimated benefit score, which MCTS uses as a cheap surrogate to decide which leaves of ${\mathcal{T}}_q$ to expand.

4. Execution Time Prediction

Although prior work [26] has adapted Transformers to query plan representation, it does not accommodate hybrid plans. This section presents our enhanced Transformer-based neural network for predicting the execution time of each candidate hybrid plan.

4.1. Why a Transformer-Based Predictor?

Candidate hybrid plans are not flat feature vectors. Each plan is a tree of physical operators whose nodes carry heterogeneous features—operator type, row/column access decisions, predicate descriptors, estimated cardinalities, join conditions, and per-table statistics—and whose execution cost depends on long-range, cross-operator interactions (e.g., the choice of a columnar scan deep in the tree affects the cost of an upstream hash join). Lightweight models such as linear regression, random forests, or multilayer perceptrons are easier to train and faster to evaluate, but they treat plan features as fixed-length vectors and therefore struggle to model structural dependencies among plan nodes and the interaction between row-store and column-store sub-trees. Tree-RNN and Tree-CNN models capture parent–child dependencies but, as discussed in Section 6.3, suffer from forgetting effects or limited receptive fields. We therefore adopt a Transformer-based predictor with tree-aware attention and a global query node, which (i) jointly models all plan nodes via self-attention, (ii) uses structural biases to respect the tree topology, and (iii) summarizes the entire hybrid plan through a single global representation. We do not claim this Transformer architecture itself as new: height encoding, tree-biased attention, and the global query node are inspired by prior neural query-plan representation work [25,26,30]; our contribution is to adapt these techniques to candidate hybrid plans in a row–column dual-store HTAP database and to couple the predictor with MCTS-based hybrid HINT generation.

4.2. Feature Extraction and Encoding

A query execution plan node typically contains operators, tables, predicates, and join conditions:

• Operators describe the operation at each plan node (e.g., Merge Join, Index Scan, Columnar Scan, Vectorized Hash Join), treated as categorical variables with approximately 30 types.
• Predicates describe filter conditions as <column, comparison operator, value> triples [31]. The column and operator are categorical; the value is range-normalized to $[0,1]$.
• Joins are join conditions treated as categorical variables (finite domain given the schema).
• Tables are relation identifiers, also treated as categorical variables.

4.2.1. Learned Embeddings for Categorical Variables

Instead of one-hot encoding, which suffers from dimension explosion and difficulty handling new categories, we use fixed-size learned embeddings [26] for each categorical variable. New categories can be accommodated by simply adding a randomly initialized embedding without affecting existing learned embeddings.

4.2.2. Predicate Encoding

The predicate representation $E_p$ concatenates the column embedding $E_c$, operator embedding $E_o$, and normalized value $v_{\mathrm{norm}}$:

$$E_p=[E_c\parallel E_o\parallel v_{\mathrm{norm}}]$$

(8)

4.2.3. Statistical Information Encoding

Histogram encoding. Equal-height histograms from database statistics are reorganized into N uniform buckets using linear interpolation. For a predicate $\mathrm{score}>35$ on a histogram with boundaries $(20,30,40,50,60)$ and $N=4$ buckets, the encoding would be $E_h=[0,0.5,1,1]$.

Sampling encoding. Random tuple samples are maintained from each table. For each predicate, each sample tuple is checked for satisfaction, producing an m-bit bitmap $E_s$ indicating the percentage of satisfying tuples.

4.2.4. Final Node Embedding

The components—operators ($E_o$), joins ($E_j$), tables ($E_t$), predicates ($E_p$), histograms ($E_h$), and samples ($E_s$)—are concatenated and passed through a linear layer:

$$E=\mathrm{Linear}(E_o\parallel E_j\parallel E_t\parallel E_p\parallel E_h\parallel E_s)$$

(9)

Intuitively, plan nodes are heterogeneous: a Hash Join carries join conditions, a Columnar Scan carries a table and predicates, and an aggregation may carry none of these. Concatenating the per-component embeddings and projecting them through a shared linear layer maps every node, regardless of operator type, into a single d-dimensional space, so the subsequent attention layers can compare and combine plan nodes without operator-specific branching logic.

For nodes without predicates or join conditions (e.g., a Hash node), zero padding is used. This produces uniform-size embeddings for all node types, forming a vector tree as input to the tree attention network.

4.3. Tree Attention Network

Building on prior Transformer work [25,26], we adapt the architecture to accept hybrid query plans. Figure 5 shows the model architecture, which introduces three key components: height encoding, tree-biased attention, and a global query node.

4.3.1. Height Encoding

The height of a node is defined as the length of the longest path from the node to any leaf. All leaf nodes have height 0, and the root has the maximum height. Height information implicitly encodes parent–child dependencies, as parent nodes always have greater height than their descendants. A learned height embedding $E_h$ is added to each node embedding:

$$x_i^{\prime }=x_i+E_h$$

(10)

4.3.2. Tree-Biased Attention

In standard self-attention (Equation (11)), every node can attend to all other nodes. However, in query plans, information should flow primarily bottom-up. We introduce tree-biased attention to control information flow using the tree structure.

Given the standard attention mechanism:

$$\mathrm{Attention}(Q,K,V)=\mathrm{softmax}\left({\displaystyle \frac{Q{K}^T}{\sqrt{d_k}}}\right)V$$

(11)

We compute pairwise distances $d_{ij}$ between plan nodes. If node j is reachable from node i (i.e., j is a descendant of i), $d_{ij}$ is the tree distance; otherwise, $d_{ij}$ is marked as unreachable. Each distance value is associated with a learnable scalar bias $b_d$:

$$A_{ij}^{\prime }=A_{ij}+b_d$$

(12)

This allows the model to learn larger biases for small distances (e.g., direct parent–child) and small negative values to discourage attention between distant or unreachable nodes.

Intuitively, $b_d$ injects the tree topology directly into self-attention: an operator can give more weight to its immediate children (which dominate its runtime) and less weight to nodes in an unrelated sub-tree (which usually have weak cost coupling). Compared with vanilla self-attention, this biases the model toward physically meaningful information flow—bottom-up cost propagation along the plan tree—without removing the ability to model long-range interactions across distant operators.

4.3.3. Global Query Node

To capture both global context (the query, which is invariant across the plan search space) and local context (the plan), we introduce a global query node $E_Q$. Using the query encoding $E_q$ from Section 3.2.1, we project it to the same dimension as plan node embeddings:

$$E_Q=\mathrm{Linear}\left(E_q\right)$$

(13)

The global query node is connected to all plan nodes and has learnable node and height embeddings. Its output vector serves as the representation of the entire query plan. This concept is analogous to the [CLS] token in BERT [30].

4.4. Query Time Prediction

The global query node output $O_Q$ is fed into a two-layer fully connected network for time prediction:

$$\begin{array}{cc}\hfill t^{\prime }& =\mathrm{ReLU}(W_1{O}_Q+b_1)\hfill \\ \hfill t& =\mathrm{Sigmoid}(W_2{t}^{\prime }+b_2)\hfill \end{array}$$

(14)

where $W_1,W_2,b_1,b_2$ are learnable parameters, and $t\in [0,1]$ is the normalized execution time. The training loss is mean squared error:

$$\mathrm{Loss}={\displaystyle \frac{1}{N}}{\displaystyle \sum _{i=1}^N}{(t_i-t_i^{\prime })}^2$$

(15)

Intuitively, the Sigmoid output produces a bounded latency estimate on a normalized scale, which keeps the regression stable across the wide latency range of analytical queries; mean squared error then penalizes large prediction gaps more heavily than small ones, encouraging the predictor to avoid catastrophic mis-ranking of candidate plans rather than merely matching their average latency.

4.5. Model Training and Update

The system maintains a memory pool M collecting training instances $D_i=(q,p,t\left(p\right))$. For each training cycle, $b_s$ triples are sampled from M to form a batch. Both the HINT estimator and the tree attention network are trained asynchronously to avoid blocking online workloads.

The hyperparameters and training settings of the Transformer predictor used in our experiments are summarized in

Table 2. Hyperparameters and training settings of the Transformer predictor.

Item	Value
Number of Transformer layers	8
Number of attention heads	8
Hidden dimension	128
Feed-forward dimension	512
Dropout rate	0.1
Batch size	128
Optimizer	AdamW
Learning rate	$1\times {10}^{-3}$
Weight decay	$1\times {10}^{-4}$
Max training rounds	30 (per online iteration)
Model selection	Lowest validation loss
Train/validation/test split	70/15/15
Random seed	42

. The configuration follows the conventions of prior tree-Transformer plan-representation work [26]. To mitigate overfitting we apply dropout inside the attention and feed-forward sub-layers, use weight decay in the optimizer, and select the final model checkpoint according to its loss on a held-out validation split rather than relying on a fixed number of epochs.

Table 3. Training data statistics for the Transformer predictor.

Item	Value
Benchmark	IMDB/JOB
Number of query templates	33
Number of query instances	113
Average number of candidate hybrid plans per query	up to $H=5$ retained from MCTS
Total generated candidate plans (MCTS rollouts)	≈1500
Retained executed (query, plan, latency) triples	≈565 (up to 5 per query)
Training samples	70% (≈395 triples)
Validation samples	15% (≈85 triples; used for checkpoint selection)
Testing samples	15% (≈85 triples)
Evaluation metrics	query latency, GMRL, plan regret

reports the size and composition of the training data used to evaluate the predictor on the JOB workload. For each query, multiple candidate hybrid plans are generated by the MCTS-based HINT search and executed on the target DBMS to collect ground-truth latencies, which are then normalized to $[0,1]$ before training.

4.5.1. Training Convergence

During training, the validation loss generally stabilized within the 30 online training rounds used in our experiments, and the final checkpoint was selected according to the lowest validation loss to reduce overfitting; this validation-based checkpointing also serves as our early-stopping mechanism in lieu of a fixed-patience criterion.

4.5.2. Cold-Start and Generalization

The proposed method requires an initial set of executed plans to train the predictor. In cold-start scenarios—when a new schema or workload is deployed—the system can initially fall back on the native cost-based optimizer or use a small bootstrap workload to populate the memory pool M. As more (query, plan, latency) triples are collected, the predictor is incrementally updated, and the MCTS-based search benefits from increasingly accurate node-utility estimates. For unseen queries that share tables and join graphs with the training distribution, the learned table embeddings and tree-aware attention enable reasonable generalization; for queries whose structure differs substantially from the training distribution, additional online execution is required before the predictor stabilizes. A comprehensive online-learning and drift-adaptation strategy is beyond the scope of this paper and is left as future work.

5. Experimental Evaluation

• Evaluation Scope.

The experiments in this section evaluate analytical-query latency under hybrid row–column access. They do not evaluate concurrent OLTP/OLAP execution, transactional interference, resource isolation, or freshness maintenance. The results should therefore be interpreted as evidence for latency-oriented hybrid-plan selection rather than as a complete evaluation of end-to-end HTAP workload management.

5.1. Experimental Setup

5.1.1. Environment

Our system is deployed on AlloyDB Omni 15.2.0 (Google LLC, Mountain View, CA, USA) [32], a locally deployable version of AlloyDB [10] compatible with PostgreSQL 15 (PostgreSQL Global Development Group) [33]. It supports both disk-based row storage and in-memory column storage with hybrid plan query optimization. The database and optimizer system are deployed on separate servers connected via a 10 Gb/s network. Both servers use Intel Xeon Gold 6148 CPUs @ 2.40 GHz (Intel Corporation, Santa Clara, CA, USA) with 1 TB memory. The optimizer server additionally uses an NVIDIA GeForce RTX 4090 GPU (NVIDIA Corporation, Santa Clara, CA, USA). The learning components are implemented in PyTorch 1.13 (Meta Platforms, Inc., Menlo Park, CA, USA) on Python 3.9 (Python Software Foundation, Wilmington, DE, USA).

5.1.2. Dataset and Workload

We use the IMDB (Internet Movie Database) dataset, a real-world database containing information about movies, actors, directors, and production companies (

Table 4. Workload statistics.

Statistic	Value
Dataset	IMDB
Attributes	108
Relations	21
Queries	113
Dataset Size (GB)	3.6

). The JOB (Join Order Benchmark) [34] is an OLAP benchmark using IMDB. JOB contains 33 query structures with 2–6 variants each (e.g., 1a, 1b, 1c), totaling 113 queries. Variants share the same tables and joins but differ in filter conditions. The number of relations per query ranges from 4 to 17.

5.1.3. Baseline Methods

We compare against four baselines:

• Row Plan: AlloyDB Omni with all tables forced to row storage via HINTs, equivalent to traditional PostgreSQL plans.
• Column Plan: AlloyDB Omni with all tables forced to column storage via HINTs.
• Cost-based Hybrid Plan: AlloyDB Omni’s default cost-based hybrid plan optimizer.
• HybridQO: A learning-based row plan optimizer [35] adapted for hybrid plan optimization. It searches join order prefixes, completes them with the cost-based optimizer, and uses a neural network to evaluate plan performance and uncertainty.

5.1.4. Evaluation Metrics

We use query latency (execution time) as the primary metric and GMRL (Geometric Mean Relative Latency) [21] for normalized per-query performance:

$$\mathrm{GMRL}={\left({\displaystyle \prod _{i=1}^n}{\displaystyle \frac{E_{\mathrm{cur}}\left(q_i\right)}{E_{\max }\left(q_i\right)}}\right)}^{{\displaystyle \frac{1}{n}}}$$

(16)

where lower GMRL indicates better performance.

5.1.5. Experimental Settings

The database working memory and cache are set to the dataset size with row plan warm-up for all queries, eliminating disk I/O effects. Row and column plans are executed three times with averaged results. Learning methods (HybridQO and ours) run for 30 iterations with the last round’s results used. MCTS budget is 75 ms. Neural networks are trained every 10 queries using 128 samples from the experience pool. The timeout is set to 1.5× the maximum baseline execution time, and execution times are normalized to $[0,1]$.

5.2. Overall Query Latency

Figure 6 shows the total latency after completing all 113 queries. Our hybrid access HINT query optimizer outperforms all baselines:

• vs. Row Plan: 71.32% time saved (3.49× speedup, from 214.99 s to 61.65 s).
• vs. Column Plan: 96.83% time saved (31.57× speedup, from 1946.40 s to 61.65 s).
• vs. Cost-based Hybrid Plan: 75.02% time saved (4.00× speedup, from 246.84 s to 61.65 s).
• vs. HybridQO: 62.23% time saved (2.65× speedup, from 163.24 s to 61.65 s).

Key findings include: (1) hybrid plans leverage column store advantages such as SIMD operations, parallelization, and reduced I/O; (2) the column plan performs worst due to expensive tuple reconstruction (column-to-row conversion); (3) the cost-based hybrid plan performs worse than even the row plan due to its inability to unify row and column costs; and (4) HybridQO performs reasonably but is limited because it was not designed for hybrid plans.

5.3. Per-Query Latency Analysis

Figure 7 presents 20 queries on a log scale. Our optimizer achieves excellent performance on nearly all queries, with only query 22c being suboptimal (the time prediction module did not correctly identify the default plan as superior). HybridQO only improves three queries (15c, 26c, 30b), with query 26c alone contributing 99% of its total improvement.

5.4. Dominance Proportions

Table 5. Dominance and strict dominance proportions of different methods.

Method	Dominance (%)	Strict Dominance (%)
Our work	76.11	30.97
Hybrid plan	50.44	4.42
HybridQO	47.79	1.77
Row plan	15.04	15.04
Column plan	1.77	1.77

presents the dominance proportions. Our optimizer achieves 76.11% dominance (proportion of queries where it is the best or tied for best) and 30.97% strict dominance (proportion where it is strictly the best). This demonstrates both strong exploitation (reusing existing good plans, 76.11%) and strong exploration (discovering new better plans, 30.97%). In contrast, HybridQO has only 1.77% strict dominance, indicating very limited exploration capability for hybrid plans.

5.5. Stability and Complex Query Handling

Table 6. Descriptive statistics of execution time (ms) across five methods.

Statistic	Row Plan	Column Plan	Hybrid Plan	HybridQO	Our Work
Count	113	113	113	113	113
Mean	1902.53	17,224.78	2184.42	1444.61	545.59
Std Dev	8301.75	42,218.32	8843.31	3564.76	1066.33
Min	15.53	45.05	0.12	0.12	0.12
25th %ile	108.32	680.18	57.36	56.61	56.61
Median	222.65	1434.84	181.77	181.77	125.16
75th %ile	1138.32	5459.22	1117.25	1117.25	447.45
Max	86,063.15	160,000.00	87,490.30	26,763.19	7550.71
GMRL	0.1297	0.7692	0.0863	0.0835	0.0565

and Figure 8a show that our optimizer has the lowest mean (545.59 ms), lowest standard deviation (1066.33), lowest median (125.16 ms), and lowest maximum (7550.71 ms), demonstrating the best stability across all methods. Figure 8b further confirms that at the 50th, 75th, 90th, 99th, and 99.5th percentiles, our optimizer consistently achieves the lowest latency. Notably, the time savings ratio increases with percentile, indicating stronger advantages on complex queries.

5.6. End-to-End Query Time

Table 7. End-to-end time comparison between AlloyDB Omni and our optimizer (seconds).

AlloyDB Omni			Our Optimizer
Plan Gen.	Execution	Total	Search	Plan Gen.	Inference	Execution	Total
2.99	246.84	249.83	7.86	2.85	2.80	61.65	75.15

compares end-to-end times. Despite introducing additional search time (7.86 s) and inference time (2.80 s), our optimizer achieves a total of 75.15 s versus AlloyDB Omni’s 249.83 s—a 69.92% reduction (3.32× speedup). The plan generation time is nearly identical (2.85 s vs. 2.99 s), as HINTs reduce the optimizer’s plan exploration space. The additional overhead of 10.66 s is vastly offset by the 185.19 s execution time saving, yielding a net gain of 174.53 s.

6. Related Work

6.1. HTAP Databases with Row–Column Co-Existing Storage

The concept of HTAP was first proposed by Gartner [36], predicting that HTAP technology would be widely adopted for real-time data analysis. In a strict sense, an HTAP database system should satisfy real-time transaction and analytical processing requirements without requiring data ETL processes. To simultaneously support both OLTP and OLAP performance, mainstream HTAP databases have adopted row–column co-existing storage [2].

Table 8. Overview of key technologies in HTAP databases [2].

Task Type	Key Technology	Corresponding HTAP Databases
Transaction Processing	MVCC + Log	Oracle Dual-Format [3], SQL Server [4], DB2 BLU [5], MySQL HeatWave [37], SAP HANA [6]
	2PC + Raft + Log	TiDB [7]
Analytical Processing	In-memory delta + column scan	Oracle [3], SQL Server [4], DB2 [5], MySQL HeatWave [37], SAP HANA [6]
	Log-based delta + column scan	TiDB [7]
	Column scan	SingleStore [38]
Data Synchronization	In-memory delta merge	Oracle [3], SQL Server [4], DB2 [5], MySQL HeatWave [37], SAP HANA [6]
	Log-based delta merge	TiDB [7]
	Rebuild from primary key row store	SingleStore [38], Oracle [3]
Query Optimization	In-memory column selection	Oracle 21c [39], MySQL HeatWave [37]
	Hybrid row/column scan	TiDB [7], SQL Server [4]
	CPU/GPU acceleration	RateupDB [9], Caldera [40]
Resource Scheduling	Freshness scheduling	RDE [41]
	Workload scheduling	SAP HANA [6], Siper [42]

summarizes key technologies in representative HTAP database systems across five dimensions: transaction processing, analytical processing, data synchronization, query optimization, and resource scheduling.

6.2. Hybrid Plans in Row–Column HTAP Databases

As shown in Figure 9, the query-processing component of such an HTAP database features a hybrid operator query optimizer on the replica node. The row execution engine can receive, process, and return row-format tuples. For column operators in the physical plan, the row engine pushes them down to the column execution engine, which processes data in columnar format using SIMD instructions and vectorized execution. The processed columnar data then undergoes a column-to-row conversion before being returned to the row engine and finally to the user.

Current hybrid plan generation approaches include heuristic methods based on column defaults (e.g., Oracle 21c [39], SQL Server [4]) and cost function-based optimization (e.g., TiDB [7]). The cost function approach compares costs across storage formats:

$$\begin{array}{cc}\hfill C_{\mathrm{opt}\_\mathrm{scan}}& =min(C_{\mathrm{col}\_\mathrm{scan}},C_{\mathrm{row}\_\mathrm{scan}},C_{\mathrm{index}\_\mathrm{scan}})\hfill \\ \hfill C_{\mathrm{row}\_\mathrm{scan}}& =S_{\mathrm{tuple}}·N_{\mathrm{tuple}}·f_{\mathrm{scan}}+N_{\mathrm{reg}}·f_{\mathrm{seek}}\hfill \\ \hfill C_{\mathrm{col}\_\mathrm{scan}}& ={\displaystyle \sum _{j=1}^m}(S_{{\mathrm{col}}_j}·N_{\mathrm{tuple}}·f_{\mathrm{scan}}+N_{{\mathrm{reg}}_j}·f_{\mathrm{seek}})\hfill \end{array}$$

(17)

However, these cost formulas are based on independence and uniform distribution assumptions without considering multi-column correlations or data skew.

6.3. Learning-Based Query Optimization

Learning-based query optimizers have made significant advances. DQ [19] and ReJOIN [20] use reinforcement learning to find optimal join orders. Neo [22] is the first end-to-end learning-based optimizer, using tree convolutional neural networks to estimate full plan latency. RTOS [21] applies graph neural networks to train reinforcement learning agents for join order selection. Bao [43] sits atop PostgreSQL, using HINTs to enable or disable sets of join and scan operations. Balsa [28] uses a similar architecture to Neo with modified training. Lero [44] formalizes the problem as a learning-to-rank task. LEON [29] is another learning-to-rank approach that prunes possible physical plans in a dynamic programming fashion.

For query plan representation, existing approaches include: (1) flattened methods such as AVGDL [45], which serialize plans into sequences but fail to capture tree structure; (2) Tree-RNNs such as Tree-LSTM [46] used by RTOS [21] and E2E-Cost [31], which aggregate information bottom-up but suffer from forgetting problems [47]; (3) Tree-CNNs [48] used by Neo [22] and Bao [43], which capture parent–child dependencies via triangular kernels but have limited receptive fields; and (4) feature vector methods such as AIMeetsAI [49] and LQPP [50], which encode features using predefined rules but fail to capture structural dependencies.

However, the above representative methods do not directly target hybrid row–column execution optimization and are therefore not directly applicable to hybrid plan selection in row–column dual-store HTAP databases. The most recent work on query optimization in HTAP databases [24] does not involve machine learning. More recent learned-optimizer studies, including learning-to-rank optimizers such as Lero [44], robust plan-generation models such as LOGER [51], and unified learned-optimizer services for lakehouse-style engines [52], also focus on conventional single-engine or row-store settings and do not target hybrid row–column plan selection in row–column dual-store HTAP systems.

7. Conclusions

This paper investigated the hybrid query optimization problem in HTAP databases with row–column co-existing configurations. We designed and implemented a hybrid access HINT query optimizer that combines Monte Carlo Tree Search with neural networks to efficiently generate high-quality hybrid access HINTs and produce superior candidate plans. The system extracts and encodes both local features of hybrid plans and global features of queries, then employs an enhanced TreeTransformer to evaluate candidate plans and select the best predicted hybrid execution plan.

The system was experimentally validated on AlloyDB Omni, a recent row–column co-existing HTAP database, using the real-world IMDB dataset and the JOB benchmark. In this evaluation setting, the proposed method improves latency-oriented hybrid-plan selection for analytical queries: it reduces execution time by 75.02% compared with the cost-based optimizer and by 62.23% compared with a state-of-the-art row-store-based learning optimizer, and it also achieves the best stability and the strongest performance on complex queries among all baselines considered.

We emphasize the scope of these claims. The current study targets analytical, latency-oriented hybrid-plan selection in a single row–column dual-store HTAP architecture; it does not fully evaluate mixed OLTP/OLAP workloads, transactional interference, concurrency, or system-level resource constraints such as memory and freshness budgets. The Transformer-based representation reuses height encoding, tree-aware attention, and a global query node from prior plan-representation work, and our contribution lies in adapting and integrating these components with MCTS-based hybrid HINT generation rather than in proposing a new general-purpose plan-representation architecture. Future work will (i) extend the optimization objective to incorporate concurrency and resource constraints, (ii) evaluate the framework under realistic mixed OLTP/OLAP workloads on additional HTAP systems, (iii) investigate online-learning strategies for cold-start and workload-drift scenarios, and (iv) study the trade-off between the proposed Transformer predictor and lightweight alternatives such as gradient-boosted trees or compact MLPs.