arXiv CSopen access

Benchmarking the Full Pipeline of Materialized-View-Based Query Rewriting

Benchmarking the Full Pipeline of Materialized-View-Based Query Rewriting Xinjie Hu

Simon Fraser University Burnaby, Canada [email protected]

arXiv:2607.19679v1 [cs.DB] 22 Jul 2026

ABSTRACT Materialized views (MVs) accelerate OLAP and data-warehouse workloads by precomputing reusable subexpressions, but practical MV-based query acceleration is a multi-stage pipeline: candidate enumeration, view selection under storage budgets, and query rewriting inside the optimizer. Existing evaluations typically study only parts of this pipeline and within a single system, leaving endto-end trade-offs and cross-system behavior unclear. In this paper, we benchmark MV-based query rewriting by jointly evaluating enumeration, selection, and rewriting with a modular evaluation framework and by using controlled ablations. We also introduce a cross-engine protocol allowing us to compare systems that expose only execution plans by contrasting native optimizer-level rewriting with portable SQL rewriting baselines when available. Across representative academic methods and modern open-source and commercial systems, we find strong interaction effects across stages and large variability in MV usage and realized savings. We identify recurring failure modes that explain performance regressions after rewriting. Our results highlight which pipeline stages most often limit performance and provide evidence to guide future MV enumeration, selection, and rewriting designs. PVLDB Reference Format: Xinjie Hu and Zhengjie Miao. Benchmarking the Full Pipeline of Materialized-View-Based Query Rewriting. PVLDB, 19(11): XXX-XXX, 2026. doi:XX.XX/XXX.XX PVLDB Artifact Availability: The source code, data, and/or other artifacts have been made available at https://github.com/edx-h/Benchmarking-MV-Based-Rewriting.

1

INTRODUCTION

As analytical workloads grow in scale and complexity, many queries repeatedly execute expensive subexpressions (e.g., joins and aggregations) over large base tables, leading to high latency and substantial redundant computation. Materialized views (MVs) offer a principled way to eliminate this redundancy: by precomputing and storing reusable subexpressions, a DBMS can rewrite incoming queries to reuse materialized results, replacing repeated computation with a (typically cheaper) view scan. This motivates MV-based This work is licensed under the Creative Commons BY-NC-ND 4.0 International License. Visit https://creativecommons.org/licenses/by-nc-nd/4.0/ to view a copy of this license. For any use beyond those covered by this license, obtain permission by emailing [email protected]. Copyright is held by the owner/author(s). Publication rights licensed to the VLDB Endowment. Proceedings of the VLDB Endowment, Vol. 19, No. 11 ISSN 2150-8097. doi:XX.XX/XXX.XX

Zhengjie Miao

Simon Fraser University Burnaby, Canada [email protected] query rewriting, which is now supported in an increasing number of database systems (automatic MV rewrite support: Oracle 2009, Snowflake 2018, RedShift 2020, BigQuery 2021, StarRocks 2023, Doris 2024 [1, 3, 5, 6, 8, 9].) Conceptually, MV reuse is straightforward, whereas realizing MV-based rewriting in practice requires a multi-stage pipeline whose components depend on one another: given a workload, an enumerator generates candidate views; a selector chooses a subset under a storage budget to maximize expected runtime savings; and a rewriter decides whether and how to rewrite each query to use the selected views. In practice, production systems differ in what they expose: some expose intermediate outputs such as selected views, while others do not; some expose rewritten SQL, while others perform rewriting internally and only reveal the final execution plan. This limited observability makes it difficult to compare systems — especially rewriting behavior across engines — and motivates our cross-platform evaluation protocol. Existing research targets the pipeline asymmetrically: enumeration [13, 22, 44] and selection [41, 46, 60, 77] have received dedicated empirical study and benchmark-style cross-method comparison, whereas the rewriting stage has been studied algorithmically [61, 69–72] but to our knowledge, no comprehensive benchmark study compares industrial rewriters across engines under a common protocol. This leaves practitioners without empirical guidance on the most engine-dependent stage; combined with the absence of an end-to-end evaluation across all three stages under a unified setting, it makes pipeline behavior and practical trade-offs difficult to assess. To fill this gap, we build a unified evaluation protocol that covers all three stages — enumeration, selection, and rewriting — and introduce a cross-platform protocol to support both pluggable components and systems with limited observability. In this paper, we experimentally investigate the full MV-based rewriting pipeline to answer the following research questions: RQ1: Is MV-based query rewriting “solved” in practice? Do end-to-end pipelines deliver consistent workload-level savings across workloads and budgets? How often do rewrites regress, and how do industrial end-to-end solutions compare with modular baselines? RQ2: How does MV-based query rewriting vary across engines under limited observability? When engines expose only plan-level output rather than rewritten SQL, how can we compare their MV exploitation fairly? Under a common MV set, how does native optimizer-level rewriting compare with a portable SQL rewrite baseline executed on the same engine? RQ3: Which stage is the bottleneck for end-to-end performance, and why? When is the limiting factor candidate generation, budget-constrained selection, or the rewriter’s ability to exploit

selected views? What workload and candidate-space characteristics explain these bottlenecks, and what failure modes recur?

reverses when the integrated enumeration/selection in the commercial system produces more compact, useful views (through column pruning) that fit into the storage budget (Section 10).

Contributions. We provide a systematic evaluation of MV-based query rewriting methods, covering both end-to-end pipelines and individual components. Our contributions are:

Outline. Section 2 defines the MV-based rewriting pipeline. Section 3 presents our modular evaluation framework and cross-engine protocol. Sections 4–6 describe the evaluated enumerators, selectors, and rewriters. Section 7 gives experimental setup and metrics. Section 8 shows end-to-end and cross-engine results, establishing why isolated stage rankings are insufficient. Section 9 then diagnoses the mechanisms behind these results through stage-wise and robustness analyses. Section 10 provides representative case studies, and Section 12 concludes with guidance and future directions.

(1) End-to-end empirical evaluation across all three stages. We develop a modular framework that decomposes MV-based query rewriting into enumeration, selection, and rewriting, enabling systematic comparison and controlled ablations (fix two stages, vary the third). To our knowledge this is the first end-to-end empirical study that jointly evaluates representative methods across all stages under a unified protocol (Section 3). (2) Cross-engine protocol and a systematic comparison of industrial and open-source rewriters. We study a broad set of open-source and commercial systems and propose a crossengine evaluation protocol that incorporates systems with limited observability (those exposing only plans rather than rewritten SQL) by comparing rewriting on a common execution engine. Using it, we provide the first benchmark comparison of industrial rewriters — HIV, CAL, DRS, STR, Sys-A, Sys-B, and Sys-C — under a common protocol with shared workloads and view sets, on rewrite success rate, runtime impact, and failuremode signatures (Sections 3, 7, 9.3, E.1). (3) Reusable implementation and metrics. We implement and integrate representative baselines across stages and systems behind a unified interface for plugging in additional methods and engines, and curate stage-wise and end-to-end metrics (e.g., workload time saving, per-view query coverage, regression behavior) under a standardized protocol (Sections 4–7). Summary of findings. Our results show that isolated stage-wise evaluations are insufficient for understanding MV-based rewriting pipelines. Pipeline outcomes depend on how candidate generation, selection under budget, and rewriting interact. We summarize three quantitative, workload-conditioned findings. • Single-stage rankings can be locally valid but globally misleading. On PostgreSQL/JOB/HIV rewriter at 1,GB, fixing BigSubs makes ECSE outperform Basic by 43.85 pp, while fixing Basic makes GnnMV outperform BigSubs by 47.53 pp. Yet both ECSE–BigSubs and Basic–GnnMV are high-performing pipelines (Figure 3). Thus, enumerators and selectors cannot be ranked independently: different candidate spaces interact with different selectors (§ 9). • The dominant bottleneck changes with workload and pipeline context. Enumeration limits performance when highcoverage views are missing (Figure 3, §9.1); selection dominates when such views must be ranked under tight budgets (Table 29 in appendix); and rewriting dominates when selected views are not exploited by the engine (Table 4, §9.3). These shifts explain why single-stage evaluation under one fixed context misses important failure modes. • Modular pipelines often beat commercial integrated systems, but not uniformly. On JOB, the best modular pipelines outperform commercial auto-selection + native rewriting by roughly 40 pp or more (Figure 4); on TPC-DS, the gap narrows or

2

PRELIMINARIES AND BACKGROUND

Given a set of queries Q = {𝑄 1, . . . , 𝑄𝑚 }, a database schema 𝑆, and a database instance 𝐷 of 𝑆, the goal of MV-based query rewriting is to accelerate the execution of queries in Q using materialized views, which is further decomposed into three subtasks. Query Rewrite Given a query 𝑄, a database instance 𝐷 of 𝑆, a set of views V = {V1, V2, . . . , V𝑛 }, views in V are instantiated as materialized views M = {M1 = V1 (𝐷), M2 = V2 (𝐷), . . . , M𝑛 = V𝑛 (𝐷)}, the query rewrite problem is to find a rewritten query R of 𝑄 with the minimum cost(R|𝐷, M), where cost(R|𝐷, M) is the evaluation cost of R over the database instance 𝐷 with views materialized. View Selection Given Q = {𝑄 1, . . . , 𝑄𝑚 } and a set of materialized views M = {M1 = V1 (𝐷), M2 = V2 (𝐷), . . . , M𝑛 = V𝑛 (𝐷)}, for any non-empty subset V ′ ⊆ V, the view selection problem is to find a subset of MVs M ∗ ⊆ M to minimize the cost of evaluating queries in Q within a storage budget constraint: M∗ =

argmin

𝑚 ∑︁

cost(R𝑖 |𝐷, M ′ )

M ′ ⊆ M,size( M ′ ) ≤𝐵 𝑖=1

where R𝑖 is a rewrite of query 𝑄𝑖 , and cost(R𝑖 |𝐷, M ′ ) denotes the execution cost of R𝑖 on 𝐷 when the MVs in M ′ are available, and size(M ′ ) is the storage cost under budget 𝐵. Candidate View Enumeration. Given Q = {𝑄 1, . . . , 𝑄𝑚 } and 𝑆, candidate view enumeration (enumeration for short) is a procedure that outputs a finite set of view definitions V = {V1, . . . , V𝑛 } to be considered by the selector. In practice, V is often derived from query subexpressions (e.g., joins/aggregations) and may apply pruning rules to control the candidate set size.

3

MODULAR EVALUATION FRAMEWORK

We design a modular evaluation framework (Figure 1). Given a query workload, an enumerator generates a set of candidate views. A selector chooses a subset of candidates under a storage budget. Finally, a rewriter rewrites workload queries by substituting view scans for matching subexpressions, returning either rewritten SQL (when exposed) or an execution plan. Pluggability. We call a pipeline component pluggable if it exposes its intermediate outputs through a common interface (e.g., candidate views, selected views, or explicit rewrites), allowing us to substitute alternative implementations without modifying the rest of the pipeline. In practice, pluggability differs across stages: most enumerators and recommenders are pluggable with exposing view definitions and/or selected views, whereas most rewriting is performed

Enumeration §4

Workload §7.1

Evaluation §8,9

Selection §5

Latency-related metrics:

Learned Track Plan Generation

Schema

Optimizer Cost from Engine

Optimizer Cost Difference

Data Collection Storage Budget

Subexpression Pruning

Potential Rewritable Pairs

Offline Training

Heuristic Track

Views

Subexpression Discovery

Feature Extraction

Rewrittwen Query Speedup

+ Enumerated

Original Queries

Time Saving

RL

Benefit Estimation

+

Rewritten Queries

Cartesian Product

Rewriting §6

Selected Views

Latencies

Engine

Positive / Negative Speedup

Latencies NOT rewritable Non-latency metrics: Rewritability Precise Filtering Ability System Runtime Overhead

......

ILP

Subset Selection

B.id=E.id A.id=D.id

⨝

B.id=C.id A.id=B.id

⨝

⨝

⨝

⨝

⨝

......

⨝

Cross Executability

Pure Logical Track

RD

Logical View Registration

Execution Plan + Rewritten SQL

Physical Materialization

RB

RC

RA

Total Covered Count 14 - Keep and Pruning Decision:

RB

RA

5 - Keep

RB

1 - Drop

Physical Materialization

Only Execution Plan

Level of Definition Requirement

Rewritten Query Format

Rewriting

✔

Execution Engine 1

〤

Materialization Required Track

RC

RB

RA

⨝

RE

⨝

Subexpression Discovery

RD

⨝

RE

RE

......

CREATE MATERIALIZED VIEW AS ... WITHOUT / WITH DATA

✔

✔ Execution Engine 2

Query Execution ✔: Cross Executable

〤

: NOT Cross Executable

Figure 1: The modular evaluation framework for MV-based query rewriting pipeline. Blocks at the bottom show the details of the components of the same color in the framework. Numbers denote the corresponding section. only in plan-transparent way, i.e., merely observable through the final EXPLAIN plan rather than an explicit rewritten SQL query. We determine rewrite success by checking whether the returned a rewritten SQL or the plan references a materialized view. Cross-platform evaluation. For fully pluggable pipelines, our framework enables evaluation over combinations of methods, i.e., {𝐸𝑛𝑢𝑚𝑒𝑟𝑎𝑡𝑜𝑟𝑠}×{𝑆𝑒𝑙𝑒𝑐𝑡𝑜𝑟𝑠}×{𝑅𝑒𝑤𝑟𝑖𝑡𝑒𝑟𝑠}. For pipelines with nonpluggable components (most commonly plan-transparent rewriters), we use a cross-platform protocol. At a high level, we evaluate optimizer-driven rewriting on the target engine through plan inspection and runtime, and additionally execute portable rewritten SQL produced by a SQL-transparent rewriter on the same engine. Details of the cross-platform protocol are provided in Section 7.

4 CANDIDATE VIEW ENUMERATION 4.1 View Enumeration Methods LEGEND: Strong Support

Weak Support

NOT Supported

⋈

(1). Vanilla subexpression No predicates, No join reordering

Rmovie_companies (mc1)

production_year BETWEEN ...

(JOB 12b)

Sys-B

Subexpression with column pruning

t1.id = mc1.movie_id

σ

Rkind_type (kt1)

Rt1

Rmovie_info_idx (mi_idx) Rtitle(t1)

t kt join graph

Rt

& Rt1

kind in ('tv series')

Πall_columns mc

Πall_columns

⋈ Rkt1 σproduction_year... Rt1

Rkt1

(2). Subexpression with join reordering

mi_idx t.id = mi_idx.movie_id

σ

Rkt1

......

⋈

Πall_columns

⋈

⋈ kind in ('tv series')

(3). Subexpression with predicate slicing

Πall_columns

t1.kind_id = kt1.id

Rtitle (t1)

UniView

Evaluated Methods and Implementation

Subexpression with predicate slicing

⋈ σ

ECSE

4.2

We evaluate the four representative enumerators in Table 1 (three academic methods plus the commercial Sys-B). The evaluated enumerators differ along three axes: join-structure expansion, predicate slicing, and column projection (Figure 2); we describe each in two to three sentences below and leave the detailed per-query behavior to the figure.

Subexpression with join reordering

Πall_columns ......

A

Basic

Vanilla subexpression

(JOB 33a)

or shared subplans, often with pruning based on frequency, cost, or containment relationships. Join-graph approaches further expand the candidate space by deriving workload-level join structures beyond a single optimizer plan [14]. MVPP-style approaches merge workload queries into a shared DAG and select reusable intermediate nodes as candidates [41, 45, 76, 77]. Finally, production systems may couple enumeration with selection and expose only the resulting recommended views. The appendix provides a more detailed survey; here we focus on the representative enumerators used in our evaluation (Table 1).

⋈ ⋈

Rmi_idx

Table 1: Evaluated enumerators and their taxonomy. Method

Category

Key behavior

Why included

Basic

Plan-subtree

ECSE

Join-graph / workload

Bottom-up plan subexpressions Explores join-order variants Predicate-rich merged DAG views Coupled enum./selection

Classical heuristic baseline Tests structural expansion Recent open-source method Production system behavior

(4). Subexpression with column pruning

Πselected_columns

UniView

MVPP

A

Sys-B

Industrial integrated

Rmc1

Figure 2: Behavior Comparison of Enumerators. We take 33a, 12b from JOB as two original queries for illustration. Candidate view enumeration generates candidate view definitions from a workload. Prior approaches can be organized by how they define and prune the candidate space. Classical approaches enumerate structured search spaces such as data-cube lattices or AND/OR view graphs [36, 38, 43, 44]. Workload-subexpression approaches [13, 31, 32, 46–48, 84] extract candidates from query plans

Basic [43, 74] is a classical heuristic enumerator that builds candidates bottom-up from the query plan, adding one join relation per step, so every candidate is a subtree of the chosen plan. It preserves all columns and slices predicates only conservatively (after all relations are joined) to limit candidate explosion, and consequently cannot discover join orders absent from that plan. ECSE [14] expands the candidate space by discovering join patterns shared across queries but absent from any single plan [14, 32, 60, 63, 66, 75]: it maintains a workload-level join graph and takes the

transitive closure over equality predicates to synthesize new join structures. Its predicate and projection handling follows Basic. UniView [76], the only open-source enumerator we found, represents MVPP-style methods [41, 45, 77]: it merges the workload’s queries into a single DAG whose non-leaf nodes become candidates. Its implementation slices predicates so that every candidate carries at least one predicate (predicate-free views are pruned). Sys-B couples enumeration with selection inside a commercial engine, generating candidates internally; we characterize its aggressive column pruning in the case study in §10.

5 VIEW SELECTION 5.1 View Selection Methods View selection chooses a subset of candidate MVs under a storage budget. Prior work differs mainly in how it estimates view benefit and how it searches the budget-constrained subset space. Classical methods rely on optimizer cost estimates and select views using greedy, dynamic-programming, or integer-programming algorithms over structured candidate spaces such as cube lattices or AND/OR view graphs [36, 38, 43, 46, 65]. Other approaches use metaheuristics such as genetic search, represented by MVPP [77]. More recent learned methods, including DQM, AutoView, and UniView, learn query/view representations and cost or benefit models, sometimes combining with reinforcement learning [41, 60, 76]. Commercial systems such as Sys-B further couple enumeration and selection into an integrated black-box recommender. Table 2 maps these categories to the selectors evaluated in this paper.

5.2

Evaluated Methods

MV selection can be divided into two sub-stages: estimating the benefit of each candidate view and selecting a subset under the storage budget. We evaluate four representative selectors, including one commercial integrated system. Table 2 summarizes their benefit model, subset-search strategy, and role in our evaluation. Table 2: Evaluated selectors. Method

Benefit model

Subset search

BigSubs

ILP

Classical cost-based method

GnnMV

Optimizer-cost proxy Learned utility

Why included

ILP

UniView

Learned cost model

RL/DQN

Sys-B

System-internal

System-internal

Learned ranking with budgeted selection Recent open-source learned selector Representative industrial selector

BigSubs [46] formulates selection as an ILP that maximizes the total cost-proxy utility of selected views (per-query reduction cost(𝑞) − cost(𝑞 | 𝑣)) under the budget, with overlap constraints discouraging redundant views (full formulation in appendix). GnnMV [40] replaces the cost proxy with a graph neural network (GNN) that predicts per-view utility from the structure of queries, candidate views, and base tables, then selects under budget with ILP. The model is trained offline using features extracted from query workloads, including query structure statistics and estimated costs. UniView [76] uses a deep neural network (DNN) to estimate postrewrite execution cost and a Deep Q-Network (with reinforcement learning) to choose the view set.

Sys-B couples enumeration and selection in the backend and exposes only the selected views, with an undisclosed internal benefit model. By parsing historical query logs, it identifies recurring subplans to extract structured SPJG patterns. Implementation. Most prior MV-selection systems are not publicly available. We therefore implement BigSubs using its ILP formulation and implement GnnMV following the model and training procedure described in its paper. For UniView, we use its public implementation. The learning-based selectors are trained separately for each dataset using the corresponding training workload. For Sys-B, we use the system-provided recommender and treat its internal selection logic as a black box.

6 REWRITER 6.1 Rewriting Queries Using Views Query rewriting using views has a long history in data warehouses and data integration systems [39, 43, 53, 57]. Classical work studies rewriting through query containment and equivalence, first for conjunctive queries and later for richer SQL fragments with aggregation, comparison predicates, and set operations [20, 23, 33, 34, 56, 67]. These extensions substantially increase the complexity of deciding whether and how a query can be rewritten using available views [10, 39]. Modern DBMSs typically implement MV rewriting inside the query optimizer rather than as a standalone rule system [19, 21, 73]. Under this architecture, view substitutions are considered as part of plan search and interact with normalization, rule matching, and cost estimation. Despite this mature algorithmic and system literature, to our knowledge no prior study systematically benchmarks practical MV rewriters across engines under a common protocol. This motivates our evaluation of both SQL-transparent and plan-transparent rewriters under a common cross-engine protocol and the cross-engine analysis in Section 9.3.

6.2

Evaluated methods

Most academic rewriting algorithms are either conceptual or not publicly available as robust SQL-level tools. The only open-source academic system we found with an implemented rewriting component is UniView, but in our preliminary validation it produced a non-negligible number of non-equivalent rewrites, so we exclude it from the rewriter comparison and focus on systems whose rewrites are validated through production query optimizers. We evaluate seven practical rewriters spanning SQL-transparent and plan-transparent systems. CAL (vanilla Calcite [18]) applies built-in SubstitutionVisitor class and default view-related rules over relational-algebra plans, with no engine-specific tuning. HIV (Apache Hive [2]) embeds Calcite-based view rewriting directly inside its optimizer and, with CAL, is one of the two rewriters that emit portable rewritten SQL. STR (StarRocks [9]) and DRS (Doris [1]) are open-source MPP engines with native optimizer-level MV rewriting; STR documents that its design is inspired by prior work on optimizing queries using materialized views [32]1 . 1 https://github.com/StarRocks/starrocks/issues/11386

Sys-A, Sys-B, and Sys-C are commercial cloud systems that provide an automatic query rewrite feature inside the optimizer; their optimizer-level MV rewriting is exposed through execution plans rather than rewritten SQL. We treat them as plan-transparent rewriters in our cross-engine protocol. Cross-engine executability. Only CAL and HIV expose rewritten SQL; these rewrites can be executed on other engines after dialect translation when the translated SQL preserves semantics (we used sqlglot in our implementation). The remaining rewriters are plantransparent: they expose only the final execution plan, and we infer rewrite success when the plan references a materialized view. This observability gap motivates our cross-engine protocol (Section 7): for each plan-transparent engine, we compare its native optimizerlevel rewriting against a portable HIV SQL rewrite executed on the same engine under the same materialized view set.

7 EXPERIMENTAL SETUP 7.1 Datasets and Workloads. We perform the experiments with four workloads, namely JOB [55], SCALE [50], STATS [42, 59] and TPC-DS (SF=10) [25], with only JOB and STATS sharing the same schema and dataset IMDB [55]. We choose them because they are widely used in the query optimization and view-based query rewriting literature [40, 41, 60, 76]. Additionally, these workloads cover different difficulties due to their significant diversities measured by join count (Table 11 in appendix) and operator count in selection predicate (Table 12 in appendix). JOB is the most complex one due to the greatest join count and more logical connectives, null judgement, string operations; STATS and TPC-DS have comparable join counts while the former involves more binary comparisons and type conversion, and the latter presents more arithmetic and range computations; SCALE is the simplest one with the lowest join count and fewest operator occurrences.

7.2

Platform and Implementation

Execution Engine and Cross Executability. We evaluate the following seven engines: PostgreSQL, Hive, Doris, StarRocks, Sys-A, Sys-B and Sys-C. For engines whose rewriting is plan-transparent (Doris, StarRocks, Sys-A, Sys-B, Sys-C), we compare two rewriters on the same engine: (i) the engine’s native optimizer-level rewriting (measured via EXPLAIN and execution latency), and (ii) executing portable rewritten SQL produced by Hive. On PostgreSQL, we additionally include Calcite as a second SQL-transparent baseline. This instantiates the cross-platform protocol defined in Section 3. Training for learned selectors. To support learned view selection baselines (UniView and GnnMV) and ensure a fair comparison, we split the original workload queries into training/validation/test sets with a 3:1:6 ratio, following prior work [40]. All methods are evaluated on the same test set. To construct training instances for learned selectors, we generate query–view pairs and label their utility as described in Section 5: i) use HIV to attempt a rewrite of a query with a candidate view; ii) execute both the original and rewritten queries on PostgreSQL and record their latencies; and iii) use the latency reduction as the utility label for the pair. Storage Budget. The storage budget bounds the total on-disk size of all selected materialized views — nor query-result caches, nor

main-memory buffers. We measure view size using PostgreSQL’s pg_total_relation_size, which includes the table heap and any indexes the selector would create. The primary budget is 1 GB; for sensitivity analysis we additionally sweep 0.1, 0.2, 0.5 GB (the budgets are comparable to those used in prior work on MV selection [40, 41, 76] for the datasets). Given the underlying database sizes (IMDB 8.31 GB, used by JOB/SCALE; TPC-DS 21.96 GB), these budgets correspond to roughly 1.2–12% of IMDB and 0.5– 4.5% of TPC-DS, spanning tight (<2% of the base data) through moderate (∼10%) regimes. This range overlaps the budgets used by BigSubs [46] (typically ≤2%) and UniView [76] (up to ∼10%), enabling direct comparison with prior work. The fact that 1 GB is moderate on IMDB but tight on TPC-DS is itself a finding (see the column-pruning case study in Section 10). Hardware and Software. We run all non-commercial systems on an AWS EC2 instance. The instance model is c5a.16xlarge (64 vCPUs, 128 GB RAM, gp3 EBS and Ubuntu 24.04 OS). For Sys-A and Sys-B, the vendor-provided service stacks are also deployed on AWS.

7.3

Metrics

End-to-end metric. Since our evaluation considers a large number of enumerator–selector–rewriter combinations, we use a single end-to-end metric, relative workload time saving, to summarize the overall effectiveness of each combination. If a rewritten query is slower than its original counterpart, we treat it as no rewrite (i.e., we use the original query latency when aggregating workload time saving). All other metrics (including speedup vs regression) are reported at the stage level (enumeration, selection, and rewriting) to diagnose the sources of performance differences. Unless otherwise stated, we evaluate each component using the best-performing combination of the other two components on each dataset within a fixed search space, e.g., when comparing rewriters, the input to each rewriter is the same set of views selected from a fixed combination of enumerator and selector Enumeration. We evaluate candidate enumerators using metrics for both scalability and candidate-set quality: (i) Candidate set size (number of generated view candidates); (ii) Materialized view size distribution (which affect downstream selection under budgets); (iii) Candidate complexity, including average join size (number of joined tables), the diversity of join orders, and the number/type of predicate operators in WHERE; (iv) Per-view query coverage, measured from the rewrite-relationship graph between views and queries: the distribution of how many queries a view can rewrite. View selection. We evaluate selection methods under the same candidate set across different storage budgets, focusing on both effectiveness and overhead: (i) Selection overhead, including solver runtime for ILP-based methods and training/inference costs for learned methods; (ii) Filtering quality (Section H in appendix), i.e., how effectively a selector discards useless candidates. We set selection, rewriting and acceleration as three checkpoints and compare recommenders’ behaviors. Query rewriting. For rewriters, we report: (i) Rewrite success rate (fraction of queries for which a view is used, as indicated by the plan from EXPLAIN or by rewritten SQL when available); (ii) Runtime impact, including overall speedup distribution and the fraction of queries that improve versus regress.

8

END-TO-END AND CROSS-ENGINE PERFORMANCE (RQ1, RQ2)

We therefore report end-to-end results across all three stages under two complementary settings. (i) PostgreSQL end-to-end. We evaluate the full modular pipeline on PostgreSQL, where candidate views, selected views, and rewritten SQL are observable and portable. This enables controlled comparisons across enumerators, selectors, and SQL-transparent rewriters by executing all rewritten queries on a common engine. (ii) Cross-engine end-to-end. To incorporate systems that offer plan-transparent rewriting (i.e., exposed only through EXPLAIN), we execute the original workload on each engine. We then compare each engine’s native optimizer-level rewriting against a portable SQL-rewrite baseline produced by Hive and execute the rewritten queries on the same engine under the same materialized-view set. Figure 3 reports workload time saving for each pipeline on PostgreSQL with a fixed storage budget of 1 GB. PostgreSQL end-to-end: no consistent winner and strong interaction effects. Across workloads, time savings vary substantially, and no single pipeline consistently dominates. For example, ECSE–GnnMV–HIV achieves the largest savings on STATS, whereas UniView–BigSubs–HIV performs best on JOB. Even within the same workload, changing only one stage can lead to large differences: On JOB, replacing ECSE with Basic in ECSE–BigSubs–HIV reduces time saving by 43.85 pp, whereas switching the selector from BigSubs to GnnMV under Basic increases savings by 47.53 pp, making it competitive with the best pipelines — so stage rankings must be read jointly with their upstream/downstream partners, not in isolation. We give the structural reason for this specific interaction (BigSubs’s creation-cost-dominated utility under-ranking the high-coverage views ECSE contributes; GnnMV’s learned cost recovering them under Basic) in §9.1 and further by a detailed case study in §10.3, and the corresponding workload-feature→methodchoice rule in the practical-guidance summary in §12. These results indicate strong interaction effects among candidate generation, selection under budget, and rewriting, supporting RQ1 and motivating the stage-wise analyses in Sections 9.1–9.3. Cross-engine end-to-end: performance is engine and workload dependent. We next evaluate cross-engine behavior using the best-performing modular pipelines identified on PostgreSQL under the cross-engine protocol. Across workloads, the best top-1 pipeline achieves 58–75% time saving (JOB: 64%, SCALE: 61%, STATS: 75%, TPC-DS: 58%), well above the corresponding per-engine median; the full per-engine top-1 breakdown is reported in appendix. However, these top pipelines do not transfer uniformly across engines: the PostgreSQL top pipeline on JOB remains top-1 on Doris but yields smaller gains on StarRocks and limited gains on Sys-A, while the PostgreSQL top pipeline on SCALE improves on Sys-A but degrades on other engines. Overall, cross-engine MV benefits depend jointly on the selected view set, the rewriter’s ability to exploit MVs, and engine-specific execution behavior. When comparing portable HIV rewrites against each engine’s native (optimizer-level) MV rewriting, we find that the best modular pipelines identified on PostgreSQL often yield larger time savings than the engine’s native rewriter. In several cases the gap is substantial (e.g., JOB on Doris and Sys-B, and SCALE on Sys-A;

Figure 4). On complex workloads such as JOB and STATS, the best modular pipelines achieve markedly higher time savings than the evaluated commercial systems in our setting (e.g., up to ∼60–70% vs. ∼15–20%; Figure 4). In contrast, on TPC-DS the gap narrows and, in some cases, commercial systems perform the best. Overall, these results indicate that MV-based query rewriting effectiveness is highly workload-dependent and engines exhibit different tradeoffs across workloads (RQ2). We investigate the sources of these differences in the subsequent stage-wise analyses, including cases where candidate generation, view selection under budget, or rewrite applicability becomes the limiting factor. Overall performance is determined by long tail minorities. We sorted queries by original latency and plotted the cumulative original latency and time saving achieved by the best combination (Figure 5). Each vertical dashed line separates the top 5% slowest queries from the rest. The (original latency, time saving) shared contributions by the slowest queries are: (36.93%, 51.42%) for JOB, (81.15%, 71.73%) for SCALE, (77.69%, 82.62%) for STATS and (39.13%, 12.97%) for TPC-DS. For those workloads where the best combinations are all non-commercial combinations (JOB, SCALE, STATS), the slowest queries not only contribute the most time saving, but their magnitude is also comparable to the original latency contribution. Only in TPC-DS where the Sys-B pipeline is chosen, is the share of time saving by the slowest queries much smaller than original latency contribution. That indicates although Sys-B achieves the best performance in TPC-DS, it does not optimize the slowest queries very well in proportion to the rest of the queries.

9

STAGE-WISE ANALYSES

The end-to-end results exhibit strong interaction effects. We therefore perform controlled ablations: when analyzing one stage, we fix the other two stages to isolate its contribution.

9.1

Analysis of Enumerators

Enumerators differ in candidate count, coverage, and structure. The best enumerator varies by workload (ECSE/UniView on JOB/SCALE, ECSE on STATS, Sys-B2 on TPC-DS), indicating that enumerators differ not just in how many candidates they generate but in the types of views they expose. We analyze candidate count, coverage, and structure to explain these differences. Candidate-space and reuse potential. Table 3 reports candidate counts and the per-view covered-query ratio. ECSE and Basic enumerate far more candidates than UniView, and Sys-B the fewest; the ECSE–Basic gap widens on the most complex workload (JOB). Across workloads, candidate count does not directly imply higher reuse: on JOB, ECSE enumerates 373 candidates (vs. 146 for Basic) but has lower median and q95 coverage (q50: 9.09% vs. 18.18%; q95: 38.64% vs. 56.82%). In contrast, on STATS and TPC-DS, ECSE produces substantially higher tail coverage than Basic (e.g., max 47.23% vs. 13.49% on STATS; max 15.78% vs. 7.22% on TPC-DS), indicating enumeration strategy can shift reuse from the “typical” candidate to a small set of “high-leverage” candidates. A consistent pattern across datasets is that coverage is skewed: most candidates cover few queries, while a small fraction achieve very high coverage (e.g., the max coverage reaches 70.45% on JOB). Whether 2 Sys-B exposes views only after its internal selection.

BigSubs GnnMV Sys-B UniView

60 40 20 0

Recommender

Time Saving (%)

5 BigSubs GnnMV Sys-B UniView

15 10 5 0

Recommender

BigSubs GnnMV Sys-B UniView

Recommender

15 10 5 0

D: TPCDS, HIV

60 40 20 0

Recommender

F: SCALE, CAL

10

BigSubs GnnMV Sys-B UniView

Time Saving (%)

0

UniView

BigSubs GnnMV Sys-B UniView

Recommender

G: STATS, CAL Time Saving (%)

10

E: JOB, CAL Time Saving (%)

Basic

C: STATS, HIV Time Saving (%)

BigSubs GnnMV Sys-B UniView

20

Recommender

0

Sys-B

B: SCALE, HIV

Time Saving (%)

60 40 20 0

ECSE

A: JOB, HIV Time Saving (%)

Time Saving (%)

Enumerators:

BigSubs GnnMV Sys-B UniView

H: TPCDS, CAL

5

0

Recommender

BigSubs GnnMV Sys-B UniView

Recommender

Figure 3: Workload time Saving (%) of all (Enumerator,Selector,Rewriter) pipeline combinations on PostgreSQL.

STR

Sys-A

Sys-B

0

Avg

DRS

50

0

DRS

STR

Sys-A

Sys-A

Sys-B

TPCDS Time Saving (%)

Time Saving (%)

STATS

STR

Sys-B

DRS

STR

Sys-A

downstream selection actually exploits this heavy-tailed structure depends on the selector’s utility model: we show in §10.3 that BigSubs’s creation-cost-dominated utility under-ranks exactly the high-coverage views in this tail on JOB, whereas GnnMV’s learned cost prioritizes them — so the same enumerated candidate pool yields different downstream behavior depending on selector choice (the 47.53 pp difference under Basic reported in §8). Mechanism: structural bias, not candidate count. ECSE expands the space mainly through join-structure variants — on JOB it produces 243 distinct join orders vs. Basic’s 78 and UniView’s 43 at comparable join counts (Table 18 in appendix) — which is why its high-coverage candidates concentrate in the tail. Enumerators also differ in predicate richness (UniView ∼8 WHERE operators per view vs. Sys-B’s ∼0), so the same selector and rewriter can yield very different end-to-end savings across enumerators. Candidate structural characteristics. Table 18 (in appendix) summarizes structural properties of the enumerated candidates. On JOB, ECSE generates far more distinct join orders than other

JO

J

W

JOB

3.31 4.55 4.55 4.55 5 5.27 0.64 14.84 9.09 38.64 70.45 243 2.34 0.06 19.52 18.18 56.82 61.36 78 2.47 0.37 3.78 2.27 13.64 15.91 43 2.13 7.98

SCALE

Sys-B ECSE Basic UniView

70 566 569 419

1.44 3.89 3.38 1.29

1.00 4.28 6.00 2.00 13.00 39.50 1.50 11.50 39.50 0.50 4.50 36.00

18 30 56 34

STATS

Sys-B ECSE Basic

24 394 392

0.84 2.38 1.34

0.69 0.87 0.69

1.56 1.73 9.34 47.23 3.88 13.49

19 2.83 0.00 45 2.44 5.36 32 2.50 5.80

TPC-DS

Sys-B ECSE Basic

25 410 305

3.49 1.28 0.77

3.48 0.27 0.27

6.52 6.68 5.61 15.78 3.96 7.22

18 3.32 0.00 54 2.30 5.99 19 2.44 7.23

Sys-B

Figure 4: Cross-engine time saving under two rewrite modes. For each engine, three MV categories are examined to compare the performance of Optimizer Native (let engine’s native optimizer to rewrite) against HIV (directly executing portable rewritten SQL generated by HIV). For MV categories, +: ranking top 1 under PG-HIV, △: ranking top 1 under this engine, □: Sys-B’s auto MV selection. Budget=1GB.

Complexity

q95 Max

11 373 146 95

40 20

q50

Sys-B ECSE Basic UniView

1.30 1.42 1.41 1.58

0.00 5.27 5.41 7.69

enumerators (243 vs. 78 for Basic and 43 for UniView), while the average join count is comparable (2.34 vs. 2.47/2.13). Combined with the coverage per view, this indicates that ECSE expands the Metric:

Cumulative Original Latency

JOB

Best (Enum, Recom): (UniView, BigSubs)

95

40

50

20 0

0

50

100

STATS

95

0

Quantile by Sorting on Original Latency (%)

Best (Enum, Recom): 1000 (ECSE, GnnMV)

750 500

500 0

250

0

50

100

0

Quantile by Sorting on Original Latency (%)

Cumulative Original Latency (s)

DRS

Coverage (%)

Workload Enumerator # Candidates

Cumulative Original Latency (s)

0

25

Cumulative Time Saving

SCALE

3000 2000

Best (Enum, Recom): (UniView, GnnMV)

95

0

600 400

1000

Cumulative Original Latency (s)

25

50

Cumulative Time Saving (s)

50

Table 3: Candidate-space statistics. Coverage is the fraction of workload queries covered by each candidate view. JO: number of unique join orders; J: mean number of joins; W: mean number of unique WHERE operators.

Cumulative Time Saving (s)

SCALE Time Saving (%)

Time Saving (%)

JOB

Optimizer Native Sys-B

200

0

50

100

TPCDS

95

0

Quantile by Sorting on Original Latency (%)

Best (Enum, Recom): 750 (Sys-B, Sys-B)

400

500

200

250 0

Cumulative Time Saving (s)

Engine Top 1

Cumulative Time Saving (s)

HIV PG-HIV Top 1

Cumulative Original Latency (s)

Rewriter: Category:

0

50

100

0

Quantile by Sorting on Original Latency (%)

Figure 5: Cumulative Original Latency and Time Saving Distribution by Quantile. Queries are sorted by original latency in ascending order to determine the quantile.

ECSE

Materialized Size (MB)

Query Coverage (%)

60

STATS

400 200 0

0

20

Query Coverage (%)

40

ECSE UniView

GnnMV

JOB

SCALE

4000 2000 0

20

Query Coverage (%)

40

20 0

5

10

Query Coverage (%)

0.1

0.2

0.5

15 10 5

1

0.1

0.2

STATS

5000 0

40

TPCDS

10000

Time Saving (%)

40

Basic BigSubs

15

Figure 6: Query coverage vs. materialized size for the top 10% highest query coverage views from each enumerator. Size measured on PostgreSQL.

Takeaways. (RQ1/RQ3) Enumeration sets the achievable upper bound on end-to-end savings — a cap, not a cause. Across the end-to-end pipeline combinations evaluated in §8 (Figure 3), no downstream selector or rewriter recovers the savings lost when the enumerator omits a high-coverage candidate: e.g., Basic’s top-1 candidate on JOB covers 18.18% of queries vs. ECSE’s 70.45%, and no selector–rewriter pair on the Basic candidate set matches the ECSE-best pipeline’s end-to-end time saving on JOB. (RQ3) Candidate count is not a proxy for usefulness: larger candidate spaces do not necessarily yield higher reuse or savings; reuse (coverage) is typically heavy-tailed, driven by a small set of high-impact views. (RQ3) Structural bias matters: enumerators differ in join-order exploration and predicate specialization, which shifts where reuse appears and affects which queries downstream stages can accelerate.

40 20 0.1

0.2

1

0.5

1

0.5

1

6 4 2 0

0.1

0.2

Figure 7: Time Saving of Each Enumerator-Recommender Combination in Different Storage Budgets

9.2 candidate space primarily through structural variants rather than enumerating larger join subexpressions, and helps explain why ECSE contains higher-tail-coverage candidates on JOB. Coverage vs. size: high reuse may not imply large views. A common intuition is that high-coverage views must be more general (fewer predicates, more projected columns) and therefore larger in size. Figure 6 tests this by plotting the materialized size distribution of the top 10% candidates ranked by coverage for each enumerator (Table 3). We find that the size–coverage trade-off is not universal: on JOB, STATS, and TPC-DS, ECSE produces many high-coverage candidates that are also relatively small, whereas other enumerators more closely follow the expected trade-off. On SCALE, where query structure is simpler, ECSE behaves similarly to Basic. Overall, these results suggest that for complex workloads, ECSE can expose more space-effective candidates (high reuse with modest materialization size), which helps downstream selection under budget.

60

0

0.5

TPCDS Time Saving (%)

20

Enumerator: Recommender:

UniView

Time Saving (%)

0

Basic SCALE

Time Saving (%)

10000

Materialized Size (MB)

Sys-B JOB

Materialized Size (MB)

Materialized Size (MB)

Enumerators:

Analysis of Selectors

Selectors differ more in robustness than in average performance. In the end-to-end results (Figure 3), no selector wins universally: the ranking of BigSubs, GnnMV, and UniView changes across workloads and (enumerator, rewriter) settings. We therefore study selector robustness through a storage-budget sweep over the three budget-aware selectors, excluding Sys-B because its integrated recommender does not expose an explicit storage-budget interface. Budget sensitivity and interaction with enumerators. Figure 7 varies the storage budget (0.1/0.2/0.5/1 GB) under two enumerators (ECSE and Basic), while fixing rewriter to HIV and engine to PG. Overall, selector behavior is highly workload-dependent. On JOB, SCALE, and STATS, GnnMV is usually best or near-best across budgets, while BigSubs is competitive but occasionally yields limited savings; UniView generally underperforms. On TPC-DS, BigSubs performs best, but the absolute savings are smaller, indicating fewer exploitable MV opportunities under this budget range. Two patterns explain why selector choice matters. First, savings often show threshold effects: moving from 0.2 to 0.5 GB or from 0.5 to 1 GB can produce abrupt gains, suggesting that a few high-impact views sit near budget boundaries. Second, selectors interact strongly with enumerators. For example, on JOB, Basicbased pipelines perform poorly with BigSubs and UniView, but become competitive when paired with GnnMV at 1 GB. Thus, a selector can sometimes compensate for a weaker candidate set by prioritizing the remaining high-impact views. Diagnosing cases with large selector gaps. To understand when selector matters most, we drill down into the outlier where GnnMV substantially outperforms BigSubs under a tight budget. Since both methods saturate the storage budget and are cost-aware, the gap cannot be explained by budget under-utilization; instead, it reflects differences in which views are prioritized. We find evidence that BigSubs can under-rank certain high-impact views due to its simplified utility model, leading to suboptimal selections when benefits depend on factors that are not well captured by the estimator (§10.3). Latency overhead. Selection overhead is consistent across workloads: BigSubs is cheapest (no training; sub-second ILP), GnnMV adds 288–1,551 s of training, and UniView is the most expensive;

the integrated Sys-B scales more gently with workload size (2.27× from JOB to STATS vs. ∼5.3–5.5× for the learned selectors). Full numbers are in appendix. Takeaways. (RQ1) Learned selectors yield more stable performance across budgets on complex workloads, with moderate training/inference overhead, compared to ILP-based BigSubs. (RQ3) Budget sensitivity is significant: selector rankings can flip between 0.1–0.2 GB and 0.5–1 GB, and savings often exhibit threshold effects when a small set of high-impact views becomes feasible. (RQ3) Under tight budgets, performance is driven primarily by view ranking rather than budget utilization; a cost-based utility model can under-rank a few high-impact views.

9.3

Analysis of Rewriter

Rewriter capability often caps end-to-end time savings. Regarding RQ3, we find that the rewriter frequently determines how much benefit upstream enumeration and selection can translate into actual workload savings. With the enumerator and selector fixed, replacing the rewriter can change the time saving substantially. For example, on JOB with the same selected view set (Basic + GnnMV), switching from HIV to Sys-A’s native rewriter reduces workload time saving from ∼40% to ∼5% (Figure 4). This suggests that, in many scenarios, the limiting factor is not only cost estimation (since HIV does not require physical materialization to produce a rewrite) or view ranking, but whether the rewriter can recognize applicable views and apply effective substitutions. We further analyze rewrite success, view usage, and representative failure modes below. Table 4: Rewrite success rate (%) and count of successful rewrites for each rewriter. Green and yellow shading mark the highest success rate among non-commercial and commercial rewriters, respectively. Bold entries denote the overall highest success rate across all rewriters. WL

Enum.

CAL

Sys-A

Sys-B

Sys-C

DRS

HIV

STR

4 UniView (2%) 4 JOB ECSE (7%) 5 Sys-B (26%) 6 Basic (10%) 0 UniView (0%) 51 SCALE ECSE (4%) 0 Sys-B (0%) 76 Basic (5%) 110 ECSE (3%) STATS 0 Sys-B (0%) 54 Basic (5%) 14 ECSE (12%) TPCDS 0 Sys-B (0%) 14 Basic (16%)

31 (15%) 49 (80%) 16 (84%) 50 (79%) 15 (3%) 47 (4%) 281 (100%) 116 (8%) 2552 (78%) 230 (100%) 661 (66%) 97 (83%) 469 (100%) 71 (83%)

146 (71%) 15 (25%) 16 (84%) 11 (17%) 404 (87%) 1231 (94%) 281 (100%) 1413 (93%) 2237 (69%) 230 (100%) 788 (79%) 61 (52%) 469 (100%) 58 (67%)

2 (1%) 1 (2%) 4 (21%) 5 (8%) 45 (10%) 49 (4%) 0 (0%) 63 (4%) 260 (8%) 0 (0%) 145 (15%) 49 (42%) 0 (0%) 67 (78%)

7 (3%) 0 (0%) 0 (0%) 2 (3%) 23 (5%) 51 (4%) 281 (100%) 104 (7%) 747 (23%) 224 (97%) 389 (39%) 114 (97%) 303 (65%) 59 (69%)

152 (73%) 39 (64%) 19 (100%) 50 (79%) 186 (40%) 78 (6%) 281 (100%) 152 (10%) 3200 (98%) 230 (100%) 887 (89%) 103 (88%) 469 (100%) 86 (100%)

146 (71%) 26 (43%) 16 (84%) 14 (22%) 404 (87%) 1243 (95%) 281 (100%) 1420 (93%) 2214 (68%) 230 (100%) 775 (78%) 61 (52%) 469 (100%) 58 (67%)

Rewrite success rate varies across engines and workloads. Table 4 reports rewrite success rates under each rewriter, grouped by workload and enumerator.3 Across JOB, STATS, and TPC-DS, HIV is the most consistently effective rewriter, achieving the highest success rates in 9 out of 14 workload–enumerator settings; STR and DRS lead in fewer cases. Among commercial systems, Sys-A more frequently exhibits higher rewrite-success rates than Sys-B in these workloads. Sys-C exhibits the most variable behavior across workloads: only 1–2% rewrite success on JOB (limited support for the workload’s complex multi-join patterns), 4–15% on SCALE and STATS, but 42–78% on TPC-DS, where its MV-rewriter handles aggregate-heavy templates well. However, there is no universal winner: in some settings (e.g., parts of STATS and TPC-DS), multiple systems reach near-100% success rates, indicating that these workloads contain patterns well covered by several optimizers. In contrast, on SCALE the ranking changes: STR becomes the top-performing open-source rewriter and Sys-B becomes the topperforming commercial rewriter, with comparable success rates. Overall, these results reinforce that rewriter behavior is strongly workload- and engine-dependent, and can be a dominant source of end-to-end variability even when the view set is fixed. We conduct a case study in Section F (appendix) to investigate why HIV achieves higher success rates in most settings, and compare HIV with DRS in depth on STATS. Additionally, via a case study in Section G in appendix, we show that MVs can also be "forced" to be used to rewrite queries under certain engines, which in turn does not contribute to performance, and sometimes we can easily detect those risky rewrites. Runtime impact varies substantially, revealing different robustness profiles. Figure 8 presents the rewrite impact on each query on JOB and SCALE. Each vertical bar corresponds to a successfully rewritten query, and its height is the relative speedup (positive) or slowdown (negative) compared to executing the original query on the same engine. The distributions expose substantial heterogeneity within a single workload: (i) many successful rewrites yield large gains, but a non-trivial fraction produce regressions, showing that rewrite “success” does not imply performance improvement; (ii) many rewrites deliver limited or even zero benefit, e.g., HIV and STR on JOB, HIV and DRS on SCALE; (iii) rewriters differ not only in the fraction of positive pairs, but also in tail behavior, e.g., some exhibit both heavy negative and positive tails (severe regressions as well as large improvements, e.g., HIV and STR for SCALE), whereas others exhibit non-symmetric tails (e.g., HIV on Doris engine for JOB, STR for JOB). Takeaways. (RQ2) Rewriter behavior is engine- and workloaddependent, with no universal winner. Under a fixed view set (Table 4), HIV achieves the highest rewrite-success rate in 9 of 14 workload–enumerator settings across JOB/STATS/TPC-DS, yet STR leads on SCALE and several systems tie near 100% elsewhere, supporting auto-MV rewrite does not imply consistent rewrite usage.

3We measure success rate as the fraction of query–view pairs for which the rewriter

produces a query/plan that references an MV, under a fixed pool of MVs.

(a) JOB

(b) SCALE

Figure 8: Per-query runtime impact of each rewriter on JOB and SCALE. Each bar is one successfully rewritten query; bar height is the relative speedup. Positive (accelerated) and negative (slowdown) speedup are marked as green and red respectively. Queries on the X-axis are sorted left-to-right by its speedup magnitude; the ordering is independent per subplot. (RQ1/RQ2) Rewrite success is decoupled from benefit, and robustness lives in the tails. Under data-distribution drift, HIV’s rewrite-success rate stays nearly flat (89% → 91%) while DRS’s collapses (99% → 28%) on the same ECSE-enumerated view set (Table 23 in appendix); and on JOB, switching only the rewriter (HIV→Sys-A) under a fixed view set cuts time saving ∼40%→∼5% (Figure 4). Rare but severe negative-speedup tails on specific (rewriter, engine) pairs can therefore dominate workload-level outcomes. (RQ2) Under the cross-engine protocol, HIV-rewritten SQL provides a controlled baseline against native optimizer-level rewriting, and its advantage is workload-dependent: HIV dominates plan-transparent rewriters on STATS-ECSE (98% vs. 23– 78%) but trails on TPC-DS-ECSE (88% vs. DRS 97%) (Table 4)— most useful where engine-native rewriting is weakest.

9.4

Robustness Analysis

The preceding analyses use a controlled steady-state setting: the MV-construction workload matches the evaluation workload, the database instance is fixed, and the same resource environment is used throughout. We add three robustness checks beyond this: workload drift, data-distribution drift, and memory pressure. Table 5 summarizes the main results. We elaborate the workload-drift analysis here because it directly tests the representativeness assumption of offline MV construction; full data-skew and hardware-pressure case studies are provided in appendix. 9.4.1 Workload Drift. Workload drift tells whether the workload used for MV construction is representative of the workload used for evaluation. We study this using two workloads (JOB and SCALE) with the rewriter fixed to HIV, the execution engine to PG, and the storage budget to 1 GB. We report relative workload time saving and average results across selectors. The shrink factor is computed as the matched-setting saving divided by the mismatched-setting saving; larger values indicate stronger degradation under drift.

Table 5: Summary of robustness experiments. Full data-skew and hardware-pressure results are in appendix. Stress

What changes

Workload drift MV construction (§9.4.1) workload differs from evaluation workload on IMDB. Datadistribution drift (§9.4.2 and §E.1.1 in appendix)

Key result

Takeaway

ECSE is stable (shrink factors 1.38/0.73), while UniView is sensitive (3.18/+∞).

Join-graph candidates transfer better than predicate-specialized candidates.

Same TPC-DS tem- On skew-sensitive Skew affects both realplates; balanced PG queries, GnnMV ized speedup and selecTPC-DS vs. skewed drops 71.67%→49.71%, tor robustness. DSB instance. while BigSubs drops 61.04%→53.38%.

Hardware / mem- MV recommendation Recommended MV sets ory pressure and evaluation run often remain useful across abundant vs. across environments, but constrained EC2 native rewrite success can settings. change for some engines.

MV selection transfers reasonably across resources; rewriting remains engine-sensitive.

Table 6: Time saving (%) under workload drift. Shrink factor is computed as matched / mismatched . Enumerator

Construction workload

Eval. workload JOB

SCALE

Shrink

JOB+SCALE

UniView

JOB SCALE

20.02 0.00

6.30 13.32

3.18

+∞

23.69 6.39

ECSE

JOB SCALE

49.21 13.47

35.63 9.86

1.38 0.73

46.63 10.73

Two patterns stand out. First, ECSE is substantially more robust to workload drift than UniView: its shrink factors stay near 1 (1.38 and 0.73), whereas UniView degrades sharply (3.18 and +∞). The likely reason is that ECSE derives candidates from workload-level join graphs, which can transfer across related query mixes, while UniView produces more predicate-specialized candidates that may fail to match shifted workloads. Second, cross-workload construction can sometimes outperform in-distribution construction: for ECSE on SCALE evaluation, constructing from JOB yields 13.47%

VSys-B (0.13 MB)

VECSE (180MB)

Πproduction_year, kind_id,

Πall columns (including t.id)

info_type_id, count(*)

Γ(production_year, kind_id,

Rtitle (t)

......

⋈

t.id = mi_idx.movie_id

info_type_id)

⋈

Original Query

t.id = mi_idx.movie_id

Rmovie_info_idx (mi_idx)

Rtitle (t)

t.id = mi.movie_id

Rmovie_info_idx (mi_idx)

t.id = mk.movie_id

t.id = mi_idx.movie_id

⋈

⋈

⋈

Rmovie_info (mi) Rmovie_keyword (mk)

Rtitle (t) Rmovie_info_idx (mi_idx) replaceable by the VECSE but not VSys-B

Figure 9: Rewriting Failure Case of Sys-B time saving, higher than 9.86% from SCALE construction. This suggests that a richer historical workload can expose reusable join structures that benefit a simpler future workload. 9.4.2 Data-distribution skew and hardware resource pressure. The other two robustness checks affect different stages. Under DSB skew, realized time saving and native rewrite success both vary substantially across engines, indicating that skew affects not only original-query runtime but also optimizer-level MV usage. Under memory pressure, selected MV sets often remain useful across environments, but native rewrite success can still change, reinforcing that rewriting remains engine-sensitive. Detailed evidence is reported in appendix (§E.1.1).

10 CASE STUDY 10.1 Case 1: Comparing Non-commercial vs. Sys-B Enumerator Across Workloads From the results in Figure 3 and Figure 4, we observe that Sys-B exhibits large time savings and high query coverage on TPC-DS as an enumerator. A natural question is why these benefits are drastic on TPC-DS but do not consistently transfer to the other workloads. A key design choice of Sys-B is aggressive column pruning when generating view candidates, producing substantially lighter weight materialized views (Section 4 and Figure 2). This strategy is particularly effective on TPC-DS. For example, the 25 views enumerated and recommended by Sys-B occupy only 30 MB of storage space in total, whereas a comparable view generated by ECSE — sharing the same join pattern but without column pruning — takes 2.3 GB. Under a space budget of up to 1 GB, Sys-B therefore provides many more feasible candidates. However, the same column pruning can also lead to more rewrite failures and, consequently, weaker performance on other workloads than column-pruning-free enumerators. To illustrate, Figure 14 (in appendix) and Figure 9 show an original query from workload SCALE and two selected views: one enumerated by ECSE (𝑉ECSE ) and the other by Sys-B (𝑉Sys-B ). While the two views share the same join over two relations, 𝑉ECSE retains all columns, whereas 𝑉Sys-B keeps only three and omits title.id. In the original query, title.id is required for joining additional relations; as a result, only 𝑉ECSE can be used for rewriting. This explains why Sys-B appears particularly strong on TPC-DS. Its database is substantially larger, so a fixed 1 GB budget is relatively tight compared to the database scale. Under such constraints, pruning-free enumerators are more likely to generate candidates that exceed the budget, even if they provide better coverage in general. In contrast, Sys-B’s pruning yields many budget-feasible

candidates and thus performs well. For the other workloads with smaller scale, the same 1 GB budget is effectively more generous: many pruning-free candidates remain feasible, and the dominant limitation shifts from storage to query coverage. In short, no single enumerator dominates across all workloads, which motivates evaluating multiple enumerators under different budget settings.

10.2

Case 2: Diagnosing Rewrite Gaps Between HIV and DRS

We diagnose the rewrite gap between HIV (3,200 pairs) and DRS (747 pairs) on STATS+ECSE, which shows two structural advantages of HIV. First, join-matching flexibility: DRS rewrites a view only when the join graph matches a small set of pre-recognized patterns; additional joined relation breaks the match. Second, predicate pushdown: when a query carries additional selection predicates beyond the view definition, HIV applies them atop the view scan, while DRS rejects the rewrite due to stricter predicate-compatibility enforcement. More details are elaborated in appendix.

10.3

Case 3: BigSubs Under-ranks High-Coverage Views (STATS, 102 MB)

To make the BigSubs ranking-failure mechanism (introduced in Section 9) concrete, we present the smallest-budget instance of the gap. Setup: workload STATS, enumerator Basic, rewriter HIV, engine PG, and a tight 102 MB budget. Both BigSubs and GnnMV are pure-ILP selectors that consume the same candidate pool; the question is which views each selects. Headline gap. Under this configuration, BigSubs achieves 1.71 % workload time saving while GnnMV achieves 44.11 % — a 42.4 pp gap. Both selectors saturate the budget (BigSubs 23 views at 100 % utilization; GnnMV 58 views at 99.97 %; Table 7), so the gap cannot be explained by budget under-utilization. The disagreement is in which views the two selectors pick. Table 7: Budget utilization rate and time saving achieved by each selector on STATS + Basic at the 102 MB budget. Rewriter = HIV, engine = PG. Selector

MV count

Utilization (%)

Time saving (%)

BigSubs GnnMV UniView

23 58 1

100.00 99.97 3.43

1.71 44.11 0.02

Where the rankings disagree. Table 8 shows the three highestutility views chosen by BigSubs (top half) and by GnnMV (bottom half). Two patterns are immediate. First, BigSubs’s top three views (MV 121, 263, 7340) collectively contribute essentially zero workload time saving — 0.00, 0.03, 0.00 % respectively — yet BigSubs ranks them highest because their utility numbers (4.64, 4.15, 3.03) are the largest in its model. Second, the three views that account for the bulk of GnnMV’s 44.11 % time saving (MV 1017, 3232, 906; actual contributed 8.94, 19.26, 12.55 %) appear far down BigSubs’s utility ranking (estimated benefit 2.9, 1.35, 1.04). BigSubs has these views in its candidate pool but under-ranks them out of the budget. Mechanism. BigSubs’s utility is computed as 𝑢 = (creation_cost − scan_cost) × count. Table 9 decomposes this for the relevant views: BigSubs’s top pick MV 7340 has high per-view utility (𝑢 = 3.03)

Table 8: Top-three views ranked by BigSubs versus top-three ranked by GnnMV on STATS + Basic + 102 MB. Estimated benefit is each selector’s utility score; actual contributed time saving is measured post-rewrite on PG. MV idx

Est. benefit (BigSubs)

Est. benefit (GnnMV)

Mat. size (MB)

Eventually selected by

Actual contributed TS (%)

121 263 7340

4.64 4.15 3.03

6.15 0.35 0

44.50 41.00 10.00

Only BigSubs

0.00 0.03 0.00

1017 3232 906

2.9 1.35 1.04

73.05 218.77 40.44

35.50 36.50 10.50

Only GnnMV

8.94 19.26 12.55

because its creation cost (3.11) is large, but it covers only 1 query. GnnMV’s actually helpful picks (MV 1017, 3232, 906) cover many queries (39, 12, 28) but their per-view utility (𝑎 −𝑏 = 0.07, 0.11, 0.04) is tiny because their creation cost is modest. Multiplying by count cannot lift them above BigSubs’s creation-cost-dominated picks, so the moderate-cost / high-coverage views never enter BigSubs’s top set. This is the same mechanism that explains the 47.53 pp BigSubs/GnnMV gap on JOB+1 GB reported in Section 8; the STATS + 102 MB instance shown here is simply the most extreme case in our budget sweep. The takeaway is that BigSubs-style costonly utility models systematically penalize the precise heavy-tail views (moderate creation cost, high query coverage) that ECSE-class enumeration is best at producing. Table 9: BigSubs utility breakdown for the relevant views on STATS + Basic + 102 MB. Recall 𝑢 = (𝑎 − 𝑏) × count, where 𝑎 is creation cost and 𝑏 is sequential scanning cost. MV idx

11

Creation cost (𝑎 )

Scan cost (𝑏 )

Perview utility (𝑎 − 𝑏 )

Covered query count

Estimated utility (𝑐𝑛𝑡 · (𝑎 − 𝑏 ))

7340

3.11

0.08

3.03

1

3.03

3232 1017 906

0.40 0.36 0.12

0.29 0.29 0.08

0.11 0.07 0.04

12 39 28

1.35 2.90 1.04

DISCUSSION

Our evaluation shows that MV-based rewriting remains workloadand engine-dependent: end-to-end gains are governed by interactions among candidate enumeration, budget-constrained selection, and rewrite applicability. We therefore summarize the main diagnostic signals for practitioners in Table 10; the goal is not to prescribe a universal pipeline, but to identify which stage is likely to be limiting under observable workload and engine properties. Practical Guidance Drawing only on evidence already presented above, we summarize the cross-stage findings in a compact decision table (Table 10) and a metrics-to-watch checklist for practitioners building MV-based query-rewriting pipelines. Key metrics. Users should monitor three classes of diagnostics: candidate reuse potential, budget sensitivity and rewrite risk. Candidate reuse (enumeration) is captured by view coverage and coveragevs-size; budget sensitivity (selection) is captured by time-saving curves under different storage budgets; and rewrite risk (rewriting) is captured by MV usage rate and the negative-speedup tail.

Table 10: Workload-feature → method-choice decision suggestion, with paper-side evidence pointers. Workload feature

Symptom / metric

Recommended tion

Join-heavy queries; heavytailed view coverage

Top-1 view coverage substantially above the median (e.g., >30 % on JOB/STATS)

Use cross-querypattern enumerators such as ECSE; planconfined enumerators may miss high-leverage views

Table 3; Fig. 6

Tight budget vs. large base data

Budget-to-DB ratio is small; useful candidates exceed budget

Prefer compact or column-pruned candidates; inspect coveragevs-size trade-off

Case study §10.1; Table 17

High predicate / type-conversion diversity

Predicate structure is diverse (STATS-like profile)

Validate selector ranking carefully; learned selectors such as GnnMV may help.

Table 12; selector analysis in §9

Plan-transparent engine, or low rewrite-success

Native rewrite-success is much lower than portable HIV on the same MV set

Validate with a portable HIV rewrite baseline; inspect the negativespeedup tail before deploying.

Table 4; Fig. 4; Table 23

ac-

Evidence

Limitations and Future Work First, cross-engine comparison cannot fully eliminate system-specific effects. Even though our protocol has controlled multiple factors, some optimizer decisions still remain opaque in plan-transparent systems. Our results should therefore be interpreted as a comparison of practical MV-exploitation behavior, not as a fully isolated comparison of rewrite algorithms. Second, we evaluate MVs statically. We do not model MV refresh cost, update frequency, or maintenance overhead. Incorporating maintenance-aware objectives and richer optimizer observability is important directions for extending this benchmark. We leave these valuable topics for future work.

12

CONCLUSION

We presented an end-to-end, cross-engine evaluation of MV-based query rewriting across candidate enumeration, view selection, and query rewriting. We show pipeline components cannot be ranked independently: a method that appears best under one fixed context may not be best once the candidate space, selector, rewriter, and engine change together. Making MV-based rewriting predictable in practice will therefore require co-designing high-leverage candidate generation, budget-robust selection, and rewrite applicability with explicit regression control. Our modular framework and crossengine protocol provide a basis for reproducible comparison.

ACKNOWLEDGMENTS We acknowledge the support of the Natural Sciences and Engineering Research Council of Canada (NSERC).

REFERENCES [1] [2] [3] [4] [5] [6] [7] [8] [9] [10]

2025. Apache Doris. https://doris.apache.org/. 2025. Apache Hive. https://hive.apache.org/. 2025. AWS Redshift. https://aws.amazon.com/redshift/. 2025. CelerData. https://celerdata.com/. 2025. Google Cloud BigQuery. https://cloud.google.com/bigquery?hl=en. 2025. Oracle Database. https://www.oracle.com/ca-en/database/. 2025. PostgreSQL. https://www.postgresql.org/. 2025. Snowflake. https://www.snowflake.com/en/. 2025. StarRocks. https://www.starrocks.io/. Serge Abiteboul, Richard Hull, and Victor Vianu. 1995. Foundations of databases. Vol. 8. Addison-Wesley Reading.

[11] Foto Afrati and Rada Chirkova. 2005. Selecting and using views to compute aggregate queries. In International Conference on Database Theory. Springer, 383–397. [12] Foto Afrati, Chen Li, and Prasenjit Mitra. 2002. Answering queries using views with arithmetic comparisons. In Proceedings of the Twenty-First ACM SIGMODSIGACT-SIGART Symposium on Principles of Database Systems (Madison, Wisconsin) (PODS ’02). Association for Computing Machinery, New York, NY, USA, 209–220. https://doi.org/10.1145/543613.543641 [13] Sanjay Agrawal, Surajit Chaudhuri, and Vivek R. Narasayya. 2000. Automated Selection of Materialized Views and Indexes in SQL Databases. In Proceedings of the 26th International Conference on Very Large Data Bases (VLDB ’00). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA, 496–505. [14] Rafi Ahmed, Randall Bello, Andrew Witkowski, and Praveen Kumar. 2020. Automated generation of materialized views in Oracle. Proc. VLDB Endow. 13, 12 (Aug. 2020), 3046–3058. https://doi.org/10.14778/3415478.3415533 [15] Kamel Aouiche, Pierre-Emmanuel Jouve, and Jérôme Darmont. 2006. Clusteringbased materialized view selection in data warehouses. In Advances in Databases and Information Systems. Springer Berlin Heidelberg, 81–95. [16] Xavier Baril and Zohra Bellahsène. 2003. Selection of materialized views: a cost-based approach. In Proceedings of the 15th International Conference on Advanced Information Systems Engineering (Klagenfurt, Austria) (CAiSE’03). Springer-Verlag, Berlin, Heidelberg, 665–680. [17] A. Bauer and W. Lehner. 2003. On solving the view selection problem in distributed data warehouse architectures. In 15th International Conference on Scientific and Statistical Database Management, 2003. 43–51. https://doi.org/10.1109/ SSDM.2003.1214953 [18] Edmon Begoli, Jesús Camacho-Rodríguez, Julian Hyde, Michael J. Mior, and Daniel Lemire. 2018. Apache Calcite: A Foundational Framework for Optimized Query Processing Over Heterogeneous Data Sources. In Proceedings of the 2018 International Conference on Management of Data (Houston, TX, USA) (SIGMOD ’18). Association for Computing Machinery, New York, NY, USA, 221–230. https: //doi.org/10.1145/3183713.3190662 [19] Randall G. Bello, Karl Dias, Alan Downing, James J. Feenan, James L. Finnerty, William D. Norcott, Harry Sun, Andrew Witkowski, and Mohamed Ziauddin. 1998. Materialized Views in Oracle. In Proceedings of the 24rd International Conference on Very Large Data Bases (VLDB ’98). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA, 659–664. [20] Ashok K. Chandra and Philip M. Merlin. 1977. Optimal implementation of conjunctive queries in relational data bases. In Proceedings of the Ninth Annual ACM Symposium on Theory of Computing (Boulder, Colorado, USA) (STOC ’77). Association for Computing Machinery, New York, NY, USA, 77–90. https: //doi.org/10.1145/800105.803397 [21] Surajit Chaudhuri, Ravi Krishnamurthy, Spyros Potamianos, and Kyuseok Shim. 1995. Optimizing Queries with Materialized Views. In Proceedings of the Eleventh International Conference on Data Engineering (ICDE ’95). IEEE Computer Society, USA, 190–200. [22] Leonardo Weiss F. Chaves, Erik Buchmann, Fabian Hueske, and Klemens Böhm. 2009. Towards materialized view selection for distributed databases. In Proceedings of the 12th International Conference on Extending Database Technology: Advances in Database Technology (Saint Petersburg, Russia) (EDBT ’09). Association for Computing Machinery, New York, NY, USA, 1088–1099. https: //doi.org/10.1145/1516360.1516484 [23] Sara Cohen, Werner Nutt, and Yehoshua Sagiv. 2006. Rewriting queries with arbitrary aggregation functions using views. ACM Trans. Database Syst. 31, 2 (June 2006), 672–715. [24] Sara Cohen, Werner Nutt, and Alexander Serebrenik. 1999. Rewriting aggregate queries using views. In Proceedings of the Eighteenth ACM SIGMOD-SIGACTSIGART Symposium on Principles of Database Systems (Philadelphia, Pennsylvania, USA) (PODS ’99). Association for Computing Machinery, New York, NY, USA, 155–166. https://doi.org/10.1145/303976.303992 [25] Transaction Processing Performance Council(TPC). 2025. TPC-DS Vesion 2 and Version 3. http://www.tpc.org/tpcds/ (2025). [26] Roozbeh Derakhshan, Frank Dehne, Othmar Korn, and Bela Stantic. 2006. Simulated annealing for materialized view selection in data warehousing environment. In Proceedings of the 24th IASTED International Conference on Database and Applications (Innsbruck, Austria) (DBA’06). ACTA Press, USA, 89–94. [27] Roozbeh Derakhshan, Bela Stantic, Othmar Korn, and Frank Dehne. 2008. Parallel Simulated Annealing for Materialized View Selection in Data Warehousing Environments. In Proceedings of the 8th International Conference on Algorithms and Architectures for Parallel Processing (Agia Napa, Cyprus) (ICA3PP ’08). SpringerVerlag, Berlin, Heidelberg, 121–132. https://doi.org/10.1007/978-3-540-695011_14 [28] Bailu Ding, Surajit Chaudhuri, Johannes Gehrke, and Vivek Narasayya. 2021. DSB: a decision support benchmark for workload-driven and traditional database systems. Proc. VLDB Endow. 14, 13 (Sept. 2021), 3376–3388. https://doi.org/10. 14778/3484224.3484234 [29] Oliver M. Duschka and Michael R. Genesereth. 1997. Query planning in infomaster. In Proceedings of the 1997 ACM Symposium on Applied Computing (San

Jose, California, USA) (SAC ’97). Association for Computing Machinery, New York, NY, USA, 109–111. https://doi.org/10.1145/331697.331719 [30] Daniela Florescu, Alon Y. Levy, Dan Suciu, and Khaled Yagoub. 1999. Optimization of Run-time Management of Data Intensive Web-sites. In Proceedings of the 25th International Conference on Very Large Data Bases (VLDB ’99). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA, 627–638. [31] Georgios Giannikis, Darko Makreshanski, Gustavo Alonso, and Donald Kossmann. 2014. Shared workload optimization. Proceedings of the VLDB Endowment 7, 6 (2014), 429–440. [32] Jonathan Goldstein and Per-Åke Larson. 2001. Optimizing queries using materialized views: a practical, scalable solution. In Proceedings of the 2001 ACM SIGMOD International Conference on Management of Data (Santa Barbara, California, USA) (SIGMOD ’01). Association for Computing Machinery, New York, NY, USA, 331–342. https://doi.org/10.1145/375663.375706 [33] Stéphane Grumbach, Maurizio Rafanelli, and Leonardo Tininini. 1999. Querying aggregate data. In Proceedings of the Eighteenth ACM SIGMOD-SIGACT-SIGART Symposium on Principles of Database Systems (Philadelphia, Pennsylvania, USA) (PODS ’99). Association for Computing Machinery, New York, NY, USA, 174–184. https://doi.org/10.1145/303976.303994 [34] Ashish Gupta, Venky Harinarayan, and Dallan Quass. 1995. Aggregate-Query Processing in Data Warehousing Environments. In Proceedings of the 21th International Conference on Very Large Data Bases (VLDB ’95). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA, 358–369. [35] Ashish Gupta, Venky Harinarayan, and Dallan Quass. 1995. Aggregate-query processing in data warehousing environments. (1995). [36] Himanshu Gupta. 1997. Selection of views to materialize in a data warehouse. In International Conference on Database Theory. Springer, 98–112. [37] Himanshu Gupta and Inderpal Singh Mumick. 1999. Selection of Views to Materialize Under a Maintenance Cost Constraint. In Proceedings of the 7th International Conference on Database Theory (ICDT ’99). Springer-Verlag, Berlin, Heidelberg, 453–470. [38] H Gupta and I S Mumick. 2005. Selection of views to materialize in a data warehouse. IEEE Trans. Knowl. Data Eng. 17, 1 (Jan. 2005), 24–43. [39] Alon Y. Halevy. 2001. Answering queries using views: A survey. The VLDB Journal 10, 4 (Dec. 2001), 270–294. https://doi.org/10.1007/s007780100054 [40] Yue Han, Chengliang Chai, Jiabin Liu, Guoliang Li, Chuangxian Wei, and Chaoqun Zhan. 2023. Dynamic materialized view management using graph neural network. In ICDE. [41] Yue Han, Guoliang Li, Haitao Yuan, and Ji Sun. 2021. An Autonomous Materialized View Management System with Deep Reinforcement Learning. In 2021 IEEE 37th International Conference on Data Engineering (ICDE). 2159–2164. https://doi.org/10.1109/ICDE51399.2021.00217 [42] Yuxing Han, Ziniu Wu, Peizhi Wu, Rong Zhu, Jingyi Yang, Liang Wei Tan, Kai Zeng, Gao Cong, Yanzhao Qin, Andreas Pfadler, Zhengping Qian, Jingren Zhou, Jiangneng Li, and Bin Cui. 2021. Cardinality estimation in DBMS: a comprehensive benchmark evaluation. Proc. VLDB Endow. 15, 4 (Dec. 2021), 752–765. https://doi.org/10.14778/3503585.3503586 [43] Venky Harinarayan, Anand Rajaraman, and Jeffrey D. Ullman. 1996. Implementing data cubes efficiently. In Proceedings of the 1996 ACM SIGMOD International Conference on Management of Data (Montreal, Quebec, Canada) (SIGMOD ’96). Association for Computing Machinery, New York, NY, USA, 205–216. https://doi.org/10.1145/233269.233333 [44] Ching-Tien Ho, Rakesh Agrawal, Nimrod Megiddo, and Ramakrishnan Srikant. 1997. Range queries in OLAP data cubes. ACM SIGMOD Record 26, 2 (1997), 73–88. [45] J. T. Horng, Y. J. Chang, and B. J. Liu. 2003. Applying evolutionary algorithms to materialized view selection in a data warehouse. Soft Computing 7, 8 (July 2003), 574–581. https://doi.org/10.1007/s00500-002-0243-1 [46] Alekh Jindal, Konstantinos Karanasos, Sriram Rao, and Hiren Patel. 2018. Selecting subexpressions to materialize at datacenter scale. Proc. VLDB Endow. 11, 7 (March 2018), 800–812. https://doi.org/10.14778/3192965.3192971 [47] Alekh Jindal, Konstantinos Karanasos, Sriram Rao, and Hiren Patel. 2018. Selecting subexpressions to materialize at datacenter scale. Proceedings VLDB Endowment 11, 7 (March 2018), 800–812. [48] Alekh Jindal, Shi Qiao, Hiren Patel, Zhicheng Yin, Jieming Di, Malay Bag, Marc Friedman, Yifung Lin, Konstantinos Karanasos, and Sriram Rao. 2018. Computation Reuse in Analytics Job Service at Microsoft. In Proceedings of the 2018 International Conference on Management of Data. ACM. [49] Panos Kalnis, Nikos Mamoulis, and Dimitris Papadias. 2002. View selection using randomized search. Data Knowl. Eng. 42, 1 (July 2002), 89–111. https: //doi.org/10.1016/S0169-023X(02)00045-9 [50] Andreas Kipf, Thomas Kipf, Bernhard Radke, Viktor Leis, Peter Boncz, and Alfons Kemper. 2019. Learned Cardinalities: Estimating Correlated Joins with Deep Learning. In Proceedings of the Conference on Innovative Data Systems Research (CIDR). [51] Anthony Klug. 1988. On conjunctive queries containing inequalities. J. ACM 35, 1 (Jan. 1988), 146–160. https://doi.org/10.1145/42267.42273

[52] Phokion G. Kolaitis, David L. Martin, and Madhukar N. Thakur. 1998. On the complexity of the containment problem for conjunctive queries with built-in predicates. In Proceedings of the Seventeenth ACM SIGACT-SIGMOD-SIGART Symposium on Principles of Database Systems (Seattle, Washington, USA) (PODS ’98). Association for Computing Machinery, New York, NY, USA, 197–204. https: //doi.org/10.1145/275487.275510 [53] Eric Lambrecht, Subbarao Kambhampati, and Senthil Gnanaprakasam. 1999. Optimizing recursive information gathering plans. In Proceedings of the 16th International Joint Conference on Artificial Intelligence - Volume 2 (Stockholm, Sweden) (IJCAI’99). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA, 1204–1210. [54] MINSOO LEE and JOACHIM HAMMER. 2001. SPEEDING UP MATERIALIZED VIEW SELECTION IN DATA WAREHOUSES USING A RANDOMIZED ALGORITHM. International Journal of Cooperative Information Systems 10, 03 (2001), 327–353. https://doi.org/10.1142/S0218843001000370 arXiv:https://doi.org/10.1142/S0218843001000370 [55] Viktor Leis, Andrey Gubichev, Atanas Mirchev, Peter Boncz, Alfons Kemper, and Thomas Neumann. 2015. How good are query optimizers, really? Proc. VLDB Endow. 9, 3 (Nov. 2015), 204–215. https://doi.org/10.14778/2850583.2850594 [56] Alon Y. Levy, Alberto O. Mendelzon, and Yehoshua Sagiv. 1995. Answering queries using views (extended abstract). In Proceedings of the Fourteenth ACM SIGACT-SIGMOD-SIGART Symposium on Principles of Database Systems (San Jose, California, USA) (PODS ’95). Association for Computing Machinery, New York, NY, USA, 95–104. https://doi.org/10.1145/212433.220198 [57] Alon Y. Levy, Anand Rajaraman, and Joann J. Ordille. 1996. Querying Heterogeneous Information Sources Using Source Descriptions. In Proceedings of the 22th International Conference on Very Large Data Bases (VLDB ’96). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA, 251–262. [58] Alon Y. Levy and Yehoshua Sagiv. 1993. Queries Independent of Updates. In Proceedings of the 19th International Conference on Very Large Data Bases (VLDB ’93). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA, 171–181. [59] Runzhong Li, Qilong Li, Haotian Liu, Rui Mao, Qing Li, and Bo Tang. 2025. Athena: An Effective Learning-based Framework for Query Optimizer Performance Improvement. Proc. ACM Manag. Data 3, 3, Article 132 (June 2025), 24 pages. https://doi.org/10.1145/3725395 [60] Xi Liang, Aaron J. Elmore, and Sanjay Krishnan. 2019. Opportunistic View Materialization with Deep Reinforcement Learning. arXiv:1903.01363 [cs.DB] https://arxiv.org/abs/1903.01363 [61] Spyros Ligoudistianos, Dimitri Theodoratos, and Timos Sellis. 1998. Experimental Evaluation of Data Warehouse Configuration Algorithms. In Proceedings of the 9th International Workshop on Database and Expert Systems Applications (DEXA ’98). IEEE Computer Society, USA, 218. [62] Imene Mami, Remi Coletta, and Zohra Bellahsene. 2011. Modeling view selection as a constraint satisfaction problem. In Proceedings of the 22nd International Conference on Database and Expert Systems Applications - Volume Part II (Toulouse, France) (DEXA’11). Springer-Verlag, Berlin, Heidelberg, 396–410. [63] Hoshi Mistry, Prasan Roy, S. Sudarshan, and Krithi Ramamritham. 2001. Materialized view selection and maintenance using multi-query optimization. SIGMOD Rec. 30, 2 (May 2001), 307–318. https://doi.org/10.1145/376284.375703 [64] Chang-Sup Park, Myoung Ho Kim, and Yoon-Joon Lee. 2001. Rewriting OLAP queries using materialized views and dimension hierarchies in data warehouses. In Proceedings 17th International Conference on Data Engineering. IEEE, 515–523. [65] Luis L. Perez and Christopher M. Jermaine. 2014. History-aware query optimization with materialized intermediate views. In 2014 IEEE 30th International Conference on Data Engineering. 520–531. https://doi.org/10.1109/ICDE.2014.6816678 [66] Prasan Roy, S. Seshadri, S. Sudarshan, and Siddhesh Bhobe. 2000. Efficient and extensible algorithms for multi query optimization. SIGMOD Rec. 29, 2 (May 2000), 249–260. https://doi.org/10.1145/335191.335419 [67] Yehoshua Sagiv and Mihalis Yannakakis. 1980. Equivalences Among Relational Expressions with the Union and Difference Operators. J. ACM 27, 4 (Oct. 1980), 633–655. https://doi.org/10.1145/322217.322221 [68] P. Griffiths Selinger, M. M. Astrahan, D. D. Chamberlin, R. A. Lorie, and T. G. Price. 1979. Access path selection in a relational database management system. In Proceedings of the 1979 ACM SIGMOD International Conference on Management of Data (Boston, Massachusetts) (SIGMOD ’79). Association for Computing Machinery, New York, NY, USA, 23–34. https://doi.org/10.1145/582095.582099 [69] Dimitri Theodoratos, Spyros Ligoudistianos, and Timos Sellis. 1999. Designing the Global Data Warehouse with SPJ Views. In Advanced Information Systems Engineering, Matthias Jarke and Andreas Oberweis (Eds.). Springer Berlin Heidelberg, Berlin, Heidelberg, 180–194. [70] Dimitri Theodoratos, Spyros Ligoudistianos, and Timos Sellis. 2001. View selection for designing the global data warehouse. Data Knowl. Eng. 39, 3 (Dec. 2001), 219–240. https://doi.org/10.1016/S0169-023X(01)00041-6 [71] Dimitri Theodoratos and Timos K. Sellis. 1997. Data Warehouse Configuration. In Proceedings of the 23rd International Conference on Very Large Data Bases (VLDB ’97). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA, 126–135. [72] Dimitri Theodoratos and Timos K. Sellis. 1998. Data Warehouse Schema and Instance Design. In Proceedings of the 17th International Conference on Conceptual

Modeling (ER ’98). Springer-Verlag, Berlin, Heidelberg, 363–376. [73] Odysseas G. Tsatalos, Marvin H. Solomon, and Yannis E. Ioannidis. 1996. The GMAP: a versatile tool for physical data independence. The VLDB Journal 5, 2 (April 1996), 101–118. https://doi.org/10.1007/s007780050018 [74] Hidetoshi Uchiyama, Kanda Runapongsa, and Toby J. Teorey. 1999. A progressive view materialization algorithm. In Proceedings of the 2nd ACM International Workshop on Data Warehousing and OLAP (Kansas City, Missouri, USA) (DOLAP ’99). Association for Computing Machinery, New York, NY, USA, 36–41. https: //doi.org/10.1145/319757.319786 [75] Satyanarayana R Valluri, Soujanya Vadapalli, and Kamalakar Karlapalem. 2002. Sprinkling Selections over Join DAGs for Efficient Query Optimization. arXiv:cs/0202035 [cs.DB] https://arxiv.org/abs/cs/0202035 [76] Zhenrong Xu, Pengfei Wang, Guoze Xue, Qitong Yan, Shenghao Gong, Yelan Jiang, Yuren Mao, Yunjun Gao, Shu Shen, Wei Zhang, Dan Luo, and Lu Chen. 2024. UniView: A Unified Autonomous Materialized View Management System for Various Databases. Proc. VLDB Endow. 17, 12 (Aug. 2024), 4353–4356. https: //doi.org/10.14778/3685800.3685873 [77] Jian Yang, Kamalakar Karlapalem, and Qing Li. 1997. Algorithms for Materialized View Design in Data Warehousing Environment. In Proceedings of the 23rd International Conference on Very Large Data Bases (VLDB ’97). Morgan Kaufmann Publishers Inc., San Francisco, CA, USA, 136–145. [78] Jian Yang, Kamalakar Karlapalem, and Qing Li. 1997. A Framework for Designing Materialized Views in Data Warehousing Environment. In Proceedings of the 17th International Conference on Distributed Computing Systems (ICDCS ’97) (ICDCS ’97). IEEE Computer Society, USA, 458. [79] Wei Ye, Ning Gu, Genxing Yang, and Zhenyu Liu. 2005. Extended derivation cube based view materialization selection in distributed data warehouse. In Proceedings of the 6th International Conference on Advances in Web-Age Information Management (Hangzhou, China) (WAIM’05). Springer-Verlag, Berlin, Heidelberg, 245–256. https://doi.org/10.1007/11563952_22 [80] Jeffrey Xu Yu, Xin Yao, Chi-Hon Choi, and Gang Gou. 2003. Materialized view selection as constrained evolutionary optimization. IEEE Transactions on Systems, Man, and Cybernetics, Part C (Applications and Reviews) 33, 4 (2003), 458–467. https://doi.org/10.1109/TSMCC.2003.818494 [81] Chuan Zhang and Jian Yang. 1999. Genetic Algorithm for Materialized View Selection in Data Warehouse Environments. In Proceedings of the First International Conference on Data Warehousing and Knowledge Discovery (DaWaK ’99). Springer-Verlag, Berlin, Heidelberg, 116–125. [82] Chuan Zhang, Xin Yao, and Jian Yang. 2001. An evolutionary approach to materialized views selection in a data warehouse environment. Trans. Sys. Man Cyber Part C 31, 3 (Aug. 2001), 282–294. https://doi.org/10.1109/5326.971656 [83] Xubo Zhang and Z. Meral Ozsoyoglu. 1993. On efficient reasoning with implication constraints. In Deductive and Object-Oriented Databases, Stefano Ceri, Katsumi Tanaka, and Shalom Tsur (Eds.). Springer Berlin Heidelberg, Berlin, Heidelberg, 236–252. [84] Jingren Zhou, Per-Ake Larson, Johann-Christoph Freytag, and Wolfgang Lehner. 2007. Efficient exploitation of similar subexpressions for query processing. In Proceedings of the 2007 ACM SIGMOD international conference on Management of data. ACM.

A EXTENDED RELATED WORK A.1 View Enumeration Early work in data warehouses considered enumerating candidates by exploring data cubes. These works [43, 44] model group-by aggregates as nodes in a data cube lattice and consider all lattice nodes as view candidates, capturing the dependency between ancestry and descendants in the lattice. Later works [36, 38] generalize the notion of lattice to graphs that capture the relationships between views, i.e., whether a view can be computed from other views or tables. Since the space of the lattice and the generalized graphs is huge, researchers propose methods from the perspective of exploring the query workload. Agrawal et al. [13] proposed to find the syntactical sub-structures (single-block materialized views consisting of selection, join, grouping, and aggregation, which use a subset of tables from a query in the workload) as candidate views and then prune and merge the views based on a query optimizer. Aouiche et al. [15] extend this method by clustering queries by similarity (using heuristics like overlapping attributes, tables, predicates) to capture closely related queries. Another line of work [31, 46–48, 84] shares a similar idea, originated from multi-query optimization, by looking into the query workload and finding the common subexpressions (e.g., SPJ+GroupBy in [32]) in queries as the source of candidates. The identified common subexpressions can be helpful for either temporary reuse or materialization. Each of the subexpression-based works may propose techniques for cheaply detecting potentially sharable subexpressions and pruning the enumeration space. For example, [84] proposed the table signatures as a fast filter to prune non-sharable expressions, and BigSubs [46] filters out infrequent subexpressions (subtrees in the logical query plan). The four representative methods evaluated in Section 4 (Table 1) instantiate, respectively, the plan-subtree, join-graph/workload, MVPP/learnedstyle, and integrated-industrial categories of this design space.

query-plan overlap (one view being a subtree of another), discouraging selecting both overlapping views, and the ILP enforces the storage-budget constraint directly. This cost-only utility is the source of the under-ranking behavior analyzed in §9 and §10.3. Our implementation. Most academic and industrial selectors are closed-source. We implemented the non-learning BigSubs (ILP formulation) and the learning-based GnnMV; for UniView we adopted its publicly available RL-based implementation (similar in framework to AutoView). For both learning-based methods we followed the offline training process in the original papers and trained models separately per dataset.

A.3

Query Rewriting

Using views in query answering has been widely applied in query optimization in data warehouses [39, 43] and data integration systems [29, 30, 53, 57]. Early works developed query rewriting algorithms based on query containment and equivalence on conjunctive queries [20, 51, 52, 58, 67, 83], but the queries handled then were too simple for real-world cases. Later works extended these foundations to richer query classes—grouping and aggregation [11, 24, 33, 34, 37], comparison conditions [12, 23, 56], and set operations [67]—which often lead to higher complexity or undecidability [10, 39]. In terms of implementation, evolving from early pure rule-based approaches [32, 35, 64], the prevailing architecture today integrates the rules into a System-R [68]-style query optimizer [19, 21, 73], where view-based rewrite is part of the plan search space and cooperates with cost estimation when forming the final execution plan.

B

WORKLOAD CHARACTERISITCS

Table 11: Query Count and Join count Summary Statistic of Each Workload Query Count

Workload Name

A.2

View Selection

View selection chooses a budget-constrained subset of candidates to materialize. While some works target the storage-budget-unconstrained scenario [22, 26, 27, 45, 61, 66, 71, 78, 81, 82], most consider the budgeted setting, which is also ours. An early branch uses traditional heuristic search over candidates modeled as Cube Lattices [17, 43, 49, 79, 80] or AND/OR view graphs [16, 36, 54, 62], with greedy [38, 63], dynamic-programming [65], or integer-programming [46] costbased pruning. Another early branch resorts to genetic algorithms over the MVPP [77] to navigate spaces where heuristics stagnate. More recent learned approaches—DQM [60], AutoView [41], and UniView [76]—use neural networks to learn query/view representations and a Q-learning RL framework to estimate view benefit and select views. Beyond academia, several commercial systems (e.g., Sys-B) couple enumeration and selection into a single integrated, black-box recommender. BigSubs ILP formulation. BigSubs [46] formulates selection as an integer linear program maximizing the total utility of selected views. The utility of a view 𝑣 for a query 𝑞 is the execution-cost reduction from rewriting 𝑞 with 𝑣, i.e., cost(𝑞) − cost(𝑞 | 𝑣) (with multiple possible rewrites, only the maximum reduction for 𝑞 is counted). It further models view interactions by accounting for

Join Count

Total

Train

Validation

Test

min

max

median

113 500 1,449 938

33 150 434 281

11 50 144 93

69 300 871 564

3 0 1 2

16 4 6 7

7 2 3 3

JOB SCALE STATS TPCDS

Table 12: Average occurrence frequency of each selectionpredicate operator per query, by category. WHERE operator counts are extracted from the logical query plan parsed by sqlglot.

C

Category

Operators

JOB

SCALE

STATS

TPCDS

General Arithmetic Comparison Logical Null Judgement Range String Operation Time Operation Type Conversion

+, -, Negation =, !=, <, >, <=, >= And, Or, Not Is Null In, Between Substring, Like Interval Cast, DataType

0 3.57 6.30 1.17 1.90 2.40 0 0

0 2.10 1.85 0 0 0 0 0

1.00 6.20 5.44 0 0 0 0 5.79

1.96 4.20 3.89 0 2.67 1.43 1.17 4.00

DETAILED COMPARISON ON REWRITERS

We now compare these query rewriters conceptually in this subsection. Unlike view enumeration and view selection, which are largely evaluated through academic prototypes, query rewriting is

Table 13: Adoption timeline of MV capabilities in production systems (year = first public documentation; “–” = not exposed or not supported). System

MV support

Auto MV selection

Auto MV rewrite

2021 2018 2019 1998 2024 2022 2017 2022 2013

2024 – 2021 2021 – – – 2022 –

2021 2018 2020 2009 2024 2023 2018 2023 –

BigQuery [5] Snowflake [8] Redshift [3] Oracle [6] Doris [1] StarRocks [9] Hive [2] Celerdata [4] PostgreSQL [7]

tightly coupled with query optimizers. We therefore focus on fullfledged systems that support view-based rewriting for general SQL workloads. As a result, these systems differ in multiple dimensions beyond algorithmic design, including optimizer integration, user visibility, coverage of query classes, and scope of rewrite rules. Table 14 provides a high-level comparison based on our examination of the official documentation of Apache Calcite and the evaluated data warehouse systems, as well as their codebases and GitHub issues (if open-sourced). Table 14: Comparison of selected rewriters on key dimensions. SPJA = plan-level transformation rules for relational operators selection, projection, join, and aggregation. Rewriter

Rewrite level

Costaware?

Pre-rewrite plan normalization

Rewrite rule composition

CAL

No

No

SPJA, no join reordering

Yes

Yes

DRS

SQL & logical plan SQL & logical plan Physical plan

Yes

No

STR

Physical plan

Yes

No

Sys-A Sys-B Sys-C

Physical plan Physical plan Physical plan

Unknown Unknown Unknown

Unknown Unknown Unknown

SPJA + predicate compensation SPJA + predicate compensation + extended operators like window function SPJA + predicate compensation + AST-level text matching Unknown Unknown Unknown

HIV

Cost-aware rewrite: All selected approaches adopt a System-Rstyle framework [68], where view-based query rewrite is implemented as a transformation integrated into the query optimizer. The vanilla CAL slightly deviates from this paradigm: it applies rewrites without cost-based decision making, whereas the other approaches explicitly compare plan costs to determine whether a plan involving materialized views should be chosen. Overall rewriting workflow: All evaluated rewriters follow a rulebased subexpression matching and substitution paradigm: subexpressions in the logical plan are matched against available materialized views and replaced when a match is found. When a view is more general than the matched subexpression (e.g., it omits some selection predicates), the rewriter may perform predicate compensation: additional selection predicates that are present in the original query but missing from the view definition are derived and applied on top of the view scan to guarantee an equivalent rewrite. The systems differ in the preprocessing steps before rule-based rewriting. HIV performs a series of normalizations prior to rewrite, transforming expressions into canonical forms (e.g., expanding IN and BETWEEN predicates), followed by further plan transformations

such as predicate pull-up to increase rewrite opportunities. In contrast, CAL, STR, and DRS perform fewer such preprocessing steps and apply rule-based matching and replacement directly to the original logical plan. Table 15: Classification of predicate types. Source: Code base of STR. link. Predicate Type

Definition

Example Form

1. equal

column equality predicates conjuncts

Ti.Cp =Tj.Cq

2. range

range predicates conjuncts

Ti.Cp op 𝑐 , where 𝑐 is a constant and op is one of the operators: <

3. residual

conjuncts can be pulled up but do not belong to equal and range

Ti.Cp LIKE "%abc%"

4. not-pulled-up

conjuncts cannot be pulled up and should match exactly between query and view

window function

, ≤, =, ≥, >

Supported predicate types in compensation: Predicate compensation plays an important role in the success rate of view-based query rewrites. Predicates can be classified into four categories, as summarized in Table 15, with the first three types commonly appearing in analytical query workloads. In our evaluation, Vanilla Calcite, CAL, does not perform predicate compensation; all evaluated rewriters support compensation for equality and range predicates. STR and DRS additionally provide limited support for residual predicates, while only DRS supports compensation for not-pulled-up predicates. Overall, DRS supports a broader range of less common predicate patterns. Rule composition strategies: All evaluated rewriters support rule-based view replacement for core relational operators, including selection, projection, join, and aggregation. Predicate compensation is supported by most systems, but not by our CAL baseline, which uses only Calcite’s SubstitutionVisitor with default viewrelated rules. For join-related rewrites, the DBMS-integrated rewriters (e.g., Hive, StarRocks, and Doris) can match join subexpressions up to join reordering by exploiting join commutativity and associativity. Besides these standard capabilities, Doris additionally supports window joins, while StarRocks provides an alternative rewrite path, text-match-based rewriting, which performs view matching and replacement directly at the query’s abstract syntax tree (AST) level. This AST-level path bypasses logical plan construction and thus avoids subsequent steps such as predicate compensation. However, this alternative approach is restrictive, as it requires the view definition to differ only minimally from the original query. Table 16: Qualitative comparison on rewriters. Rewriter

Existing Form

Cross Executable

Level of View Definition Requirement

CAL HIV DRS STR Sys-A Sys-B Sys-C

Separate Embedded Embedded Embedded Embedded Embedded Embedded

✓ ✓ ✗ ✗ ✗ ✗ ✗

Logical Registration Logical Registration Physical Materialization Physical Materialization Physical Materialization Physical Materialization Physical Materialization

As illustrated in Table 16, we qualitatively compare these approaches from the following three aspects.

Level of Definition Required. It indicates in order to successfully carry out query rewriting, whether a rewriter requires the resulting tuples be populated to the view table (physical materialization) or not (logical registration). Although we execute CREATE MATERIALIZED VIEW in either case, the latter one indicates registering the view to the schema conceptually is enough. We measure this dimension by comparing the count of rewritten queries achieved by each rewriter with and without populating tuples. We find that CAL and HIV return equal count while the rest of approaches return much fewer without populating tuples. Using logical registration level approaches can save time because there is much fewer I/O and computation overhead when creating the MV. Moreover, as we will show in empirical analysis, such approach can still achieve better performance even with the drawback of failing to exploit precise view cardinalities.

ALL COMBINATIONS TIME SAVING RESULT

D

Please refer to Figure 10.

Top 1 Combination

Time Saving (%)

Workload

Engine

Enumerator Recommender Rewriter

Top 1

Median

JOB

DRS PG STR Sys-A Sys-B

UniView UniView ECSE Basic ECSE

BigSubs BigSubs GnnMV GnnMV GnnMV

HIV HIV HIV HIV HIV

61 62 22 39 64

15 7 9 5 10

SCALE

DRS PG STR Sys-A Sys-B

ECSE UniView ECSE ECSE Basic

BigSubs GnnMV BigSubs BigSubs GnnMV

HIV HIV STR HIV HIV

9 20 13 61 24

4 7 8 24 14

STATS

DRS PG STR Sys-A Sys-B

ECSE ECSE ECSE ECSE ECSE

GnnMV GnnMV GnnMV BigSubs GnnMV

HIV HIV HIV HIV HIV

36 73 55 75 15

14 6 30 37 8

TPCDS

DRS PG STR Sys-A Sys-B

Sys-B Sys-B Sys-B Sys-B Sys-B

Sys-B Sys-B Sys-B Sys-B Sys-B

HIV HIV STR HIV HIV

47 58 52 26 13

4 1 5 13 1

E

ADDITIONAL STAGE-WISE ANALYSIS RESULTS

This appendix holds two secondary stage-wise tables referenced from Section 9: the structural complexity of enumerated candidates (Table 18) and the runtime overhead of view selectors (Table 19).

E.1

Robustness Analysis

E.1.1 Data-Distribution Drift: DSB vs TPC-DS. DSB [28] shares the same schema as TPC-DS but applies a more skewed data distribution to selected fact columns. We use the TPC-DS instance for enumeration, training, and view selection, then benchmark original and rewritten latencies on both TPC-DS and DSB instance4 . We 4 scaling factors are aligned as 10.

Workload

Enumerator

# of Unique Join Order

Mean # of Joins

Mean # of Unique WHERE Operators

JOB

Sys-B ECSE Basic UniView

5 243 78 43

5.27 2.34 2.47 2.13

0.64 0.06 0.37 7.98

SCALE

Sys-B ECSE Basic UniView

18 30 56 34

1.30 1.42 1.41 1.58

0.00 5.27 5.41 7.69

STATS

Sys-B ECSE Basic

19 45 32

2.83 2.44 2.50

0.00 5.36 5.80

TPCDS

Sys-B ECSE Basic

18 54 19

3.32 2.30 2.44

0.00 5.99 7.23

Table 19: Runtime overhead of selectors per workload (sec). Phase Model Training

Table 17: Top 1 pipeline combination across execution engines and workloads. Storage budget = 1 GB. Median = the median of time savings among all pipeline combinations evaluated. Scenario Setting

Table 18: Complexity of candidate views.

Model Inference/ View Selection Enumeration + Selection

Selector

JOB

SCALE

STATS

TPCDS

BigSubs GnnMV UniView BigSubs GnnMV UniView Sys-B

N/A 287.95 2551.00 0.26 3.89 20.58 582.78

N/A 1495.67 6756.21 0.32 5.14 60.94 1204.17

N/A 1551.04 13392.20 0.56 6.06 750.84 1320.97

N/A 486.26 6924.05 0.40 4.34 45.68 1080.62

pick up PostgreSQL as the main engine to be studied and Doris as a reference to be compared with. Workload preparation. We split the workload into queries that are sensitive to the distribution drift (sensitive workload) and those that are not (not-sensitive workload); the split is by relative original-latency change across the two instances in each engine. Table 20 reports the per-engine change in original-workload latency when switching from TPC-DS to DSB. The not-sensitive workload changes little (PG +8.71%, DRS -3.26%), while the sensitive workload becomes substantially slower in both engine, most dramatically on PG (+57.65%). Table 20: Original-workload latency change (%) when the underlying data is switched from TPC-DS (balanced) to DSB (skewed) for each workload Workload

Doris

PostgreSQL

not-sensitive sensitive

-3.26% 16.70%

8.71% 57.65%

Data skewness can discount performance but harms more on GnnMV. Table 21 shows the average MV-driven workload time saving for the two top selectors (GnnMV and BigSubs) with ECSE as enumerator and HIV as rewriter. Unlike Doris, as a more classical cost-based optimizer, PostgreSQL substantially suffers from MV benefits decrease on sensitive workloads when facing instance mismatch. However, we observe although GnnMV enjoys 10% advantage than BigSubs (71.67% vs 61.04%) under matched scenario, it drops over 20% (71.67% to 49.71%) while BigSubs only drops 8% under mismatch. This is a sign showing the curated optimized training process makes learned model a fine-tuned one perfectly running on designed scenario, but essentially makes it an "overfit" version sticking to one distribution pattern, lacking transferability.

Figure 10: Time Saving (%) Main Result (a) JOB

0

BigSubs COM-ii GnnMV UniView

20 0

BigSubs COM-ii GnnMV UniView

0

F: rewriter=CAL & engine=PG

BigSubs COM-ii GnnMV UniView

Recommender

0

BigSubs COM-ii GnnMV UniView

20 0

BigSubs COM-ii GnnMV UniView

Recommender

Time Saving (%) Time Saving (%) Time Saving (%)

Time Saving (%) Time Saving (%)

0

BigSubs COM-ii GnnMV UniView

BigSubs COM-ii GnnMV UniView

Recommender

I: rewriter=COM-i & engine=COM-i

J: rewriter=COM-ii & engine=COM-ii 20 10 0

BigSubs COM-ii GnnMV UniView

Recommender

20 10 0

BigSubs COM-ii GnnMV UniView

Hawc

60 40 20 0

BigSubs COM-ii GnnMV UniView

E: rewriter=HIV & engine=COM-ii 15 10 5 0

J: rewriter=COM-ii & engine=COM-ii 15 10 5 0

Recommender

H: rewriter=DRS & engine=DRS

I: rewriter=COM-i & engine=COM-i 60 40 20 0 BigSubs COM-ii GnnMV UniView

Recommender (d) TPC-DS

BigSubs COM-ii GnnMV UniView

Recommender

BigSubs COM-ii GnnMV UniView

Recommender

Recommender

COM-ii

C: rewriter=HIV & engine=DRS 40 20 0

BigSubs COM-ii GnnMV UniView

40

20 10 0

0

BigSubs COM-ii GnnMV UniView

Recommender

BigSubs COM-ii GnnMV UniView

E: rewriter=HIV & engine=COM-ii 15 10 5 0

J: rewriter=COM-ii & engine=COM-ii 15 10 5 0

Recommender

H: rewriter=DRS & engine=DRS

20

Hawc

D: rewriter=HIV & engine=COM-i

Recommender

G: rewriter=STR & engine=STR 40

10

D: rewriter=HIV & engine=COM-i

Recommender

Recommender

Time Saving (%)

Time Saving (%)

0

BigSubs COM-ii GnnMV UniView

20

Recommender

Time Saving (%)

20

Recommender

5

0

ECSE Time Saving (%)

BigSubs COM-ii GnnMV UniView

20

B: rewriter=HIV & engine=STR 40

E: rewriter=HIV & engine=COM-ii

COM-ii

Recommender

A: rewriter=HIV & engine=PG

BigSubs COM-ii GnnMV UniView

20

C: rewriter=HIV & engine=DRS

G: rewriter=STR & engine=STR 40

UniView

D: rewriter=HIV & engine=COM-i

40

Recommender (c) STATS

Recommender

Time Saving (%)

Time Saving (%) Time Saving (%)

0

BigSubs COM-ii GnnMV UniView

Time Saving (%)

20

Enumerators:

20

0

Time Saving (%)

Time Saving (%)

Time Saving (%)

40

F: rewriter=CAL & engine=PG

40

5

60

ECSE

Recommender

60

10

Recommender

Recommender

H: rewriter=DRS & engine=DRS

B: rewriter=HIV & engine=STR

Recommender

BigSubs COM-ii GnnMV UniView

60 40 20 0

Recommender

Recommender

A: rewriter=HIV & engine=PG

15 10 5 0

BigSubs COM-ii GnnMV UniView

BigSubs COM-ii GnnMV UniView

Time Saving (%)

BigSubs COM-ii GnnMV UniView

Enumerators:

BigSubs COM-ii GnnMV UniView

0

0

Time Saving (%)

Recommender

60 40 20 0

5

5

Time Saving (%)

15 10 5 0

BigSubs COM-ii GnnMV UniView

10

Hawc

20 10 0

BigSubs COM-ii GnnMV UniView

Recommender

BigSubs COM-ii GnnMV UniView

Recommender

I: rewriter=COM-i & engine=COM-i Time Saving (%)

BigSubs COM-ii GnnMV UniView

BigSubs COM-ii GnnMV UniView

10

G: rewriter=STR & engine=STR Time Saving (%)

Time Saving (%)

15 10 5 0

0

J: rewriter=COM-ii & engine=COM-ii 15

Recommender

C: rewriter=HIV & engine=DRS

Recommender

F: rewriter=CAL & engine=PG

10

BigSubs COM-ii GnnMV UniView

Time Saving (%)

Recommender

20

COM-ii

B: rewriter=HIV & engine=STR

Time Saving (%)

BigSubs COM-ii GnnMV UniView

Time Saving (%)

Time Saving (%)

0

15 10 5 0

BigSubs COM-ii GnnMV UniView

60 40 20 0

Recommender

I: rewriter=COM-i & engine=COM-i

Recommender (b) SCALE

ECSE

A: rewriter=HIV & engine=PG 10

0

Recommender

Enumerators:

20

BigSubs COM-ii GnnMV UniView

10

BigSubs COM-ii GnnMV UniView

E: rewriter=HIV & engine=COM-ii

Recommender

Time Saving (%)

Recommender

Time Saving (%)

BigSubs COM-ii GnnMV UniView

0

H: rewriter=DRS & engine=DRS

Time Saving (%)

0

Time Saving (%)

Time Saving (%)

5

20

Recommender

G: rewriter=STR & engine=STR 15 10 5 0

BigSubs COM-ii GnnMV UniView

40

UniView

D: rewriter=HIV & engine=COM-i

Time Saving (%)

Recommender

F: rewriter=CAL & engine=PG 10

BigSubs COM-ii GnnMV UniView

Time Saving (%)

0

Recommender

Time Saving (%)

10

60 40 20 0

Hawc

C: rewriter=HIV & engine=DRS

Time Saving (%)

20

Time Saving (%)

BigSubs COM-ii GnnMV UniView

COM-ii

B: rewriter=HIV & engine=STR Time Saving (%)

A: rewriter=HIV & engine=PG

Time Saving (%)

60 40 20 0

ECSE Time Saving (%)

Time Saving (%)

Enumerators:

BigSubs COM-ii GnnMV UniView

Recommender

Table 21: Time Saving (%) on TPC-DS (balanced) vs DSB (skewed) instances. Storage budgets = 8 GB; enumerator = ECSE, rewriter = HIV.

Workload sensitive not-sensitive sensitive not-sensitive

BigSubs recommender

GnnMV recommender

Engine

TPCDS ins.

DSB ins.

TPCDS ins.

DSB ins.

PostgreSQL PostgreSQL Doris Doris

61.04 30.11 10.08 49.90

53.38 38.68 11.40 49.49

71.67 29.16 6.45 26.42

49.71 37.51 8.82 22.02

Long-tail queries domination. Another discovery from Table 21 is that sensitive workload always performs better than notsensitive one in PostgreSQL, and this still holds even under instance mismatched scenario. It is an engine optimizer level heterogeneity as in Doris the situation is reversed. Our further breakdown shows such difference is determined by the key minorities. Splitting the workload by original latency (Long-Tailed = top 10% by original latency; Rest = bottom 90%), Figure 11 shows that (Long-Tailed queries, PostgreSQL) is the only group where it is the sensitive workload that enjoys higher performance, which in turn pulls up the overall performance. Long-tailed queries are therefore the key axis of variation under data-distribution drift whose plans are most exposed to skew-induced cardinality-estimation errors.

Time Saving (%)

Workloads:

not-sensitive

sensitive

25 0

0 Long-Tailed

DRS

Rest

Long-Tailed

PG

Rest

Figure 11: MV-driven time saving split by query latency quantile (Long-Tailed = top 10% by original latency vs Rest = bottom 90%) on PostgreSQL and Doris; TPC-DS (balanced) vs DSB (skewed) instances. Case study: how MVs offset skew on a long-tailed sensitive query. To explain the mechanism we profile a single query drawn from the long-tailed sensitive workload on PG (Table 22). The original query is 13.75× (72.48/5.27) slower on DSB than on TPC-DS and processes 10.73× (118/11) more buffer blocks. After MV rewriting, both latency and buffer-block counts return to the TPC-DS-level baseline. Plan inspection reveals the mechanism: on TPC-DS, PG’s optimizer estimates that the index scan on store_sales will return 29 rows and chooses a nested-loop join (manageable: 20 448 actual loops). On DSB, the same plan combined with skewed cardinality (estimated 1 row, actual 34 M loops) explodes into 101 M buffer blocks. The MV-rewritten plan switches to a hash join with a sequential scan over the materialized view, saving 68.68 s and 112 M buffer blocks on this single query. Rewritability under data-distribution drift varies sharply by engine. Under instance mismatched scenario, the per-engine rewrite-success rate also diverges dramatically when the same ECSE-enumerated view set is shifted from not-sensitive to sensitive workload. Table 23 summarizes the rewrite-success counts and percentages.

Table 22: Per-plan profile for the long-tailed sensitive casestudy query on PG before and after MV rewriting, on TPC-DS (balanced) and DSB (skewed) instances. “Key Node” is the dominant join operator on store_sales. Original Query

Rewritten Query

Metrics

TPC-DS

DSB

Latency (s) Buffer Blocks (M) Key Node Estimated Rows Actual Loops Node Buffer Blocks (M)

5.27 72.48 11 118 Nested Loop + Index Scan 29 1 20 448 34 M 6 101

TPC-DS

DSB

3.92 3.80 6 6 Hash Join + Seq Scan 147 104 53 654 8 8 0.9 0.9

Table 23: Rewrite-success rate (%) and rewritten pair count under ECSE enumeration with each engine’s native rewriter, comparing not-sensitive and sensitive workloads. DRS’s rewritability collapses by 71 pp under skew, while STR’s rises by 31 pp. Workload not-sensitive sensitive

DRS

HIV

Sys-A

STR

4298 (99%) 1113 (28%)

3890 (89%) 3562 (91%)

3994 (92%) 2736 (70%)

1119 (26%) 2249 (57%)

Three patterns are visible: (i) DRS’s rewritability collapses from 99% to 28% — its strict plan-pattern matching fails when skewinduced cardinality changes push the optimizer toward alternative join orders that the rewriter no longer recognizes. (ii) STR increases from 26% to 57% — skew may force simpler join plans whose narrower shape falls within STR’s stricter rewriter window. (iii) HIV stays nearly flat (89%→91%) and Sys-A drops moderately (92%→70%), confirming that the rewriter’s pattern-matching flexibility — not just the underlying data — mediates the datadistribution-drift effect. E.1.2 Hardware and Memory Pressure. To stress the pipeline under resource pressure, we decouple the environment used to collect training data, make recommendation decisions and rewrite from the environment used to benchmark the performance of recommended MVs. Each environment has two options: constrained (16 vcpus and 32GB) versus abundant (64 vcpus and 128GB). Selector behavior under memory pressure. Table 24 reports workload time saving in the cross-hardware transfer study. "Native" scenario means the machine used for MV recommendation is the same as the evaluation environment while "Cross" is not. Firstly, the cross-hardware settings achieve workload time savings comparable to the non-cross settings, with UniView-based pipelines even benefits from memory pressure (cross-native difference 7.65% and 8.38%). In other words, MVs recommended on the abundant machine remain highly effective when evaluated on the constrained machine, and vice versa. Our breakdown analysis (Table 25) further shows in either evaluation environments, it is the MVs recommended from both environments that contribute the most time saving (34.91% and 36.86%)—valuable MVs can always be discovered no matter which hardware is adopted. All theses strongly indicate that MV recommendation exhibits a considerable degree of transferability across hardware environments. Rewriting behavior under memory pressure. We also reexamine rewrite success rate (defined same as the one in Table 4) of each rewriter and conclude the following findings (Table 26):

Table 24: Time Saving in Cross Hardware Transfer Study (%) MV Recommended By Evaluation env.

Enumerator

Recommender

Native env.

Cross env.

CrossNative

abundant abundant abundant abundant

ECSE UniView ECSE UniView

BigSubs BigSubs GnnMV GnnMV

49.78 61.60 53.48 60.18

49.11 61.32 53.89 59.71

-0.67 -0.28 0.41 -0.47

constrained constrained constrained constrained

ECSE UniView UniView ECSE

BigSubs BigSubs GnnMV GnnMV

56.91 63.34 62.85 57.83

52.17 70.99 71.23 58.66

-4.74 7.65 8.38 0.83

Table 27: (𝑞, 𝑣) pairs rewritten by HIV but not DRS. Failure reason from Doris

# Pairs

Rewrite success but not selected

7

-

View struct info is invalid

2408

A1: Join reordering and A2: Predi-

View struct info is invalid, Predicate compensate fail

38

Evaluation env.

both env. abundant only constrained only

abundant

constrained

34.91 18.57 18.98

36.86 21.80 20.97

F

(a). Regardless of rewriting environment, HIV is the only rewriter consistently enjoys high level success rate across both enumerator, while Sys-A and STR only performs well in ECSE and UniView respectively. (b). Under memory pressure, only HIV and Sys-A do not experience degradation and HIV even increases 11% on ECSE.

Table 26: Rewrite Success Rate (%) Under Different Hardwares. Rewriting Env.

Enumerator DRS

HIV

Sys-A

STR

abundant constrained abundant constrained

ECSE ECSE UniView UniView

66% 77% 85% 85%

70% 69% 6% 6%

35% 46% 73% 66%

15% 10% 2% 1%

cate pushdown

A3: Precise predicate compensation

Robustness Takeaways. (RQ1) ECSE is more robust to workload drift than UniView: ECSE’s drift-induced shrink factors (1.38 and 0.73 on JOB and SCALE respectively) stay near 1, while UniView’s reach 3.18 and +∞ (§9.4.1). Cross-workload training can even outperform in-distribution training when the training workload exposes a richer join structure (ECSE on SCALE-rewrite, shrink factor 0.73). (RQ1/RQ3) MV rewriting can offset, not merely suffer from, data-distribution skew. On the long-tailed sensitive workload on PG, the original query is 13.75× slower on DSB than TPC-DS, but MV rewriting returns latency to the TPC-DS-baseline by switching nested-loop+index-scan to hash-join+seq-scan, saving 68.56 s and 112 M buffer blocks on a single query (§E.1.1). (RQ2) It is safe to apply recommended MVs on cross hardware evaluation as recommenders show robust transferability on memory pressure scenario. However, rewriter should be picked up with caution as certain cost-sensitive optimizers (DRS and STR) are expected to flucturate on the amount of rewritten queries. Overall, HIV is still the best recommendation as it is not only robust to hardware but also to different enumerators. (§E.1.2).

Table 25: Time Saving (%) Breakdown By the Source of Recommended MV. Enumerator=ECSE, Recommender=GNN.

MV recommended by

Advantages of Hive

DIAGNOSING REWRITE GAPS BETWEEN HIVE AND DORIS

In section 9.3, we showed that Hive’s SQL-transparent rewriter often outperforms other engines’ native optimizer-level rewriters. In this case study, we focus on the STATS workload with ECSE candidates and compare DRS against HIV. We analyze query–view pairs that are successfully rewritten by HIV but not by DRS. We choose this setting for two reasons: (i) (STATS, ECSE) yields the largest number of candidate rewrite pairs (3,256), providing a large and diverse sample; and (ii) DRS exposes diagnostic information in its optimizer output, which helps attribute rewrite failures. High-level failure breakdown. Table 27 reports three categories of HIV-only pairs and the number of affected pairs. We identify 7 pairs that DRS can rewrite but does not choose in the final plan, suggesting a rewrite-found-but-pruned behavior (may be due to costbased optimization). Since these account for only 0.3% of HIV-only cases and cost models are highly engine-specific, we do not treat cost-based pruning as a primary source of the observed gap. Diagnosing structural non-rewritability in Doris. The remaining gap is dominated by structural rewrite failures in DRS. While DRS labels most failures with coarse reasons (e.g., “View struct info is invalid”), only 38 instances (1.55%) provide a more specific message (“Predicate compensate fail”); the remaining 2,408 instances lack an actionable explanation. We therefore inspect the query and

1

v1 : SELECT * FROM users AS u JOIN badges AS b ON u. Id = b. UserId ;

1

v2 : SELECT * FROM posts AS p JOIN votes AS v ON p . OwnerUserId = v. UserId JOIN users AS u ON p. OwnerUserId = u. Id WHERE p. CommentCount >= 27; -- more general filtering value q1 : SELECT COUNT (*) FROM comments as c , postHistory as ph , badges as b , users as u WHERE u. Id = c. UserId AND u. Id = ph . UserId AND u. Id = b. UserId AND c. Score = 18 AND c. CreationDate >= ' 2009 -04 -23 07:14:48 ':: timestamp AND c. CreationDate <= ' 2011 -09 -05 18:06:40 ':: timestamp AND ph . PostHistoryTypeId = 1 AND ph . CreationDate >= ' 2012 -03 -25 03:12:39 ':: timestamp AND ph . CreationDate <= ' 2012 -12 -24 19:24:20 ':: timestamp AND b. Date <= ' 2012 -01 -08 08:42:47 ':: timestamp ; -- q2 involves one additional relation posts compared to q1 q2 : SELECT COUNT (*) FROM comments as c , posts as p , postHistory as ph , badges as b , users as u WHERE u. Id = ph . UserId AND u. Id = b. UserId AND u. Id = p. OwnerUserId AND u. Id = c. UserId AND c. Score =12 AND p. PostTypeId =1 AND p. ViewCount >=46 AND p. ViewCount <=33342 AND p . FavoriteCount =6 AND p. CreationDate <= ' 2013 -09 -24 04:55:16 ':: timestamp ; -- q3 involves more selection predicates on users compared to q1 q3 : SELECT COUNT (*) FROM comments as c , postHistory as ph , badges as b , users as u WHERE u. Id = b. UserId AND u. Id = ph . UserId AND u. Id = c. UserId AND c. CreationDate <= ' 2013 -04 -09 10:50:59 ':: timestamp AND b. Date >= ' 2013 -10 -27 08:37:08 ':: timestamp AND u. Reputation >=23996 AND u. Reputation <=66025 AND u. DownVotes >=20 AND u. DownVotes <=100;

2 3 1 2 3

1 2 3 4

1 2 3 4

5

Figure 12: Queries and views for which HIV and DRS exhibit different rewriting behavior. view structures and summarize the dominant failure patterns into two advantages of HIV (A1 and A2). Drilling down via high-impact views. We group the 2,408 failures by view and find that a small number of views accounts for a large fraction of failures: the top 5 (out of 53) cover 56% of all failures. We select the most frequent view 𝑣 1 with a simple join structure for detailed inspection because (i) it alone accounts for 16% of failures (402 pairs), (ii) it also has 13 successful rewrites in DRS, enabling controlled comparison under the same view. A1 Flexibility in join matching. Queries 𝑞 1 and 𝑞 2 in Figure 12 differ only in that 𝑞 2 joins one additional relation (posts). Both queries contain the join users Z badges, which is exactly the view 𝑣 1 , and both queries require only attributes covered by 𝑣 1 for this join. Thus, replacing users Z badges with a scan of 𝑣 1 should be valid in both cases. Empirically, however, 𝑞 1 is rewritten by both HIV and DRS, while 𝑞 2 is rewritten only by HIV. Inspecting the 13 DRS successes for 𝑣 1 , we find that DRS rewrites only when the surrounding join graph belongs to two specific relation-set patterns; queries with other relation sets (including 𝑞 2 ) are rewritten only by HIV. This suggests that DRS is less flexible in substituting a matched view into larger joins (or requires stricter join-order/pattern conditions). A2 Predicate pushdown limitations. 𝑞 1 and 𝑞 3 share the same join, but 𝑞 3 has additional selection predicates on users. Since 𝑣 1 has no predicates, these extra filters are safe to apply on top of a scan of 𝑣 1 . However, HIV rewrites 𝑞 3 using 𝑣 1 , while DRS does not, indicating a more restrictive predicate-compatibility implementation.

G

RISKY REWRITE FEATURES

In Table 4 §9.3, we observe STR and Sys-B achieve best success rewrite ratio on SCALE, unlike their performance on other workloads. However, the net time saving result on their successfully

Table 28: Net Time Saving (%) of Queries Extra Rewritten by STR and Sys-B Rewriter

Extra Rewritten Pair Count

Positive Speedup Query Ratio (%)

Net Time Saving (%)

STR COM-ii

1176 1175

29.25 9.19

-111.83 -50.36

rewritten queries shows such high rewrite yields to a catastrophic performance regression (Table 28). Specifically, STR reports a catastrophic net time saving of −111.83%, effectively more than doubling the query execution time. Similarly, Sys-B exhibits a net time saving of −50.36%. An inspection of the generated physical plans reveals that the rewriter introduces an approach we term Inverse Query Reconstruction (illustrated in Figure 13). When the available MV contains only a strict subset of the target relation (title), the rewriter attempts to compensate for the missing tuples. It achieves this by synthesis: generating a complex UNION ALL subquery that pairs the scan of the MV with a fallback scan over the original base table title, guarded by a disjunctive chain of inverse predicates (kind_id <= 1 OR production_year <= 2013 OR production_year IS NULL). In fact, such rewritings are certainly highly risky and prone to suffer from optimization barrier: Encapsulating the base relation within a structural UNION ALL subquery introduces an optimization barrier. This complex set operations pipeline limits the CBO capacity to perform join reordering and blocks crucial downstream predicate pushdowns from outer relational operators into the base table scan. Additionally, reconstructing the relation dynamically introduces substantial materialization overhead. The engine must maintain extra pipeline buffers to merge the data streams from both the MV scan and the high-volume base table scan before routing the unified stream to subsequent join stages, massively driving up memory consumption and network shuffle traffic. How to detect such risky rewrites? To prevent such malicious rewrites in production frameworks, we propose users can develop a static structural checker. If a candidate plan introduces a UNION ALL structure specifically for base-table data compensation using inverse predicates, and those predicates feature non-trivial disjunctions (OR or NOT IN), the rewrite should be eagerly flagged as high-risk.

H

FILTERING QUALITY OF VIEW SELECTORS.

Table 29 reveals a three-linked discoveries connecting view filtering to final savings. First, a high SVR does not imply high TS: Sys-B selects nearly all candidates on STATS (SVR = 100%) yet achieves only 0.78% TS, while GnnMV’s more selective filtering (SVR = 90.54%) precisely generates 8.6× more rewrites (1,984 vs. 230) and reaches the workload-best 67.87% TS (highlighted in green). Second, rewrite volume (number of successful rewrites) must be paired with per-rewrite quality (SQR): on TPC-DS, Sys-B combines the highest rewrite count (469) with a perfect SQR of 100%, delivering the best 57.72% TS (highlighted in green), whereas GnnMV’s far fewer rewrites (67, SQR = 74.40%) yield only 1.90% TS despite comparable SVR. Third, the dominant selector is workload-dependent: GnnMV leads on JOB (55.71%, green) and STATS (67.87%, green)

1 2 3 4

5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

I

-- original query SELECT COUNT (*) FROM title t , movie_companies mc , cast_info ci , movie_info mi , movie_keyword mk WHERE t . id = mc . movie_id AND t . id = ci . movie_id AND t . id = mi . movie_id AND t . id = mk . movie_id AND ci . person_id < 926305 AND ci . role_id < 10 AND mi . info_type_id < 7 AND mk . keyword_id > 1074; 1 2 3

-- recommended view ( name : `mv `) SELECT ... FROM title AS t WHERE t . kind_id > 1 AND t . production_year > 2013

4

-- rewritten query from STR / COM - ii using `mv ` SELECT COUNT (*) FROM ( SELECT id FROM title WHERE kind_id <= 1 OR production_year <= 2013 OR production_year IS NULL UNION ALL -- KEY : inversely reconstruct the complete relation `title ` partially using `mv ` SELECT id FROM mv ) as t JOIN ...... WHERE ci . person_id < 926305 AND ci . role_id < 10 AND mi . info_type_id < 7 AND mk . keyword_id > 1074;

22

Figure 13: Risky Rewrite Pattern Introduced by STR and Sys-B

by prioritizing high-coverage views that enable broad and beneficial rewrites; Sys-B dominates on TPC-DS (57.72%, green) through compact, column-pruned views that unlock near-perfect rewrite applicability; and BigSubs edges ahead on SCALE (16.73%, green) through a consistent balance of volume and speedup quality. Together, these results show that no single filtering metric — SVR, number of rewrites, or SQR in isolation — predicts end-to-end savings; only their joint alignment with the structural characteristics of the target workload determines which selector prevails.

Table 29: Filtering quality of view selectors. SVR (selectedview ratio) SVR is the fraction of candidate views that are selected. #rewrites is the number of queries that are successfully rewritten using selected views. SQR (speedup-query ratio) is the proportion of rewritten queries that yield a speedup. TS = Time saving ratio. Workload JOB

SCALE

STATS

TPCDS

Selector

SVR (%)

#rewrites

SQR (%)

TS (%)

BigSubs Sys-B GnnMV UniView BigSubs Sys-B GnnMV UniView BigSubs Sys-B GnnMV UniView BigSubs Sys-B GnnMV UniView

62.04 91.67 90.09 86.82 94.03 90.54 87.82 82.99 99.07 100.00 95.54 100.00 97.67 100.00 100.00 94.44

126 19 154 106 269 281 278 177 1946 230 1984 554 101 469 67 91

34.04 100.00 41.06 40.42 65.67 93.95 58.23 59.25 49.13 96.96 56.91 46.34 65.70 100.00 74.40 32.14

39.10 1.46 55.71 31.35 16.73 3.51 16.43 7.45 62.23 0.78 67.87 27.68 4.99 57.72 1.90 0.41

5 6 7 8 9 10 11 12 13 14

SUPPLEMENTARY MATERIAL FOR CASE 1: COMPARING NON-COMMERCIAL VS. SYS-B ENUMERATOR ACROSS WORKLOADS -- original query SELECT COUNT (*) FROM title t , movie_companies mc , movie_info mi , movie_info_idx mi_idx , movie_keyword mk WHERE t . id = mc . movie_id AND t . id = mi . movie_id AND t . id = mi_idx . movie_id AND t. id = mk . movie_id AND t . production_year <2011 AND mc . company_id >1060 AND mk . keyword_id <31445; -- view enumerated by Sys - B (0.13 MB ) SELECT t . kind_id , t . production_year , mi_idx . info_type_id FROM movie_info_idx AS mi_idx JOIN title AS t ON mi_idx . movie_id = t. id GROUP BY production_year , kind_id , info_type_id -- view from ECSE (180 MB ) SELECT * FROM title AS t JOIN movie_info_idx AS mi_idx ON t . id = mi_idx . movie_id

Figure 14: Example of view definitions by Sys-B and ECSE.

Related documents

• TrajectoryDB: A New Database for Agent Trajectories#673620
• Preserving contextual information in cultural heritage metadata through multidimensional knowledge graphs#673621
• X-DigCheck: Co-Evolving Application Profiles and Knowledge Graphs, Demonstrated on the RTI Documentation of Rupe Magna#673622
• Open Tabular Insight Extraction: Where Do We Stand, and Where Should We Go?#673623
• Protocol effects on feature-based hardware-Trojan detection across Trust-Hub families#673624