Workload acceleration by optimizing materialized view selection using local search
2026-06-02 • Databases
Databases
AI summaryⓘ
The authors studied how to pick which parts of databases to save (materialize) to make queries faster without causing too much slow-down when the database updates. They noticed that previous methods didn't accurately consider the costs of updating these saved parts and used random techniques that weren't very effective. Their new method uses a precise mathematical approach and smart searching to find better choices for what to save. Tests on a real-world database system showed their approach works better than earlier methods.
view selectionmaterialized viewsview maintenanceinteger linear programminglocal searchsub-query containmentquery optimizationRedbenchAmazon Redshiftdatabase workloads
Authors
Kaina Anderson, Yohanes Yohanie Fridelin Panduman, Yuya Sasaki, Makoto Onizuka
Abstract
The growing size of database workloads has made view selection a key performance challenge. Materializing frequent sub-queries in workloads improves query efficiency, but it incurs significant view maintenance costs due to updates. Although existing methods such as BIGSUBS address this trade-off between the benefit of using materialized views and the overhead of view maintenance, they have two drawbacks: insufficient maintenance cost modeling and ineffective view selection due to probabilistic techniques. We propose a novel view selection method that incorporates incremental view maintenance cost directly into the optimization objective of an integer linear program and applies local search to efficiently explore the solution space. In order to apply local search to the view selection problem, we develop neighboring solutions using sub-query containment, and select initial solutions based on sub-query frequency, utility, or utility per storage unit. Experiments using Redbench, a benchmark simulating real-world query workloads on Amazon Redshift, show that our approach outperforms BIGSUBS in both optimization utility and the quality of selected views.