One Query per Template Enough? Cross-Hardware Adaptation of Learned Cost Models

The Cost Model Challenge of Cross-Hardware Migration

Learned cost models have become a key tool for database query optimization by predicting query runtimes. However, these models are typically trained on a single machine. When a database migrates to new hardware, the query plans themselves remain unchanged, but execution times shift by different proportions due to hardware characteristics. How to adapt the model using measurements of a small number of queries on the new machine has become a core problem in practical deployment. A study published on arXiv systematically answers this question: which queries should be measured, how their runtimes should be used, and how many queries are sufficient.

The research team timed the same set of plans for 1,162 TPC-H and TPC-DS queries on PostgreSQL across six different environments: a laptop and a server, each using all processors or bound to a single core, the server additionally bound to two cores, and the laptop additionally configured with a smaller buffer pool. These environments cover typical hardware differences ranging from single-core to multi-core and from ample memory to constrained memory.

A learned cost model refers to using machine learning models (such as neural networks or gradient-boosted trees) to replace the hand-crafted formulas in traditional cost models, predicting query runtime or cost by learning from large amounts of query execution data. Traditional cost models rely on simplified cost formulas and statistics and struggle to capture complex nonlinear relationships; learned models can automatically fit these relationships and often achieve higher accuracy in a single hardware environment. However, their generalization ability is limited by the training data distribution: when hardware configurations change, the model inputs (query plan features) stay the same but the outputs (execution times) shift, increasing prediction error and thus requiring adaptation strategies.

No Single Scaling Factor Can Describe Performance Changes

Experiments reveal a key finding: no single factor can describe the slowdown between two environments. Most of the variance lies between query templates—queries from different templates vary enormously in their sensitivity to hardware changes. This means that simply using a global speed factor to scale the predicted times of all queries has limited effectiveness. The speed factor stops improving after measuring a small number of queries because differences between templates dominate the error.

This finding challenges intuition: one might expect hardware performance differences to be approximated by a uniform multiplier, but the actual data shows that the diversity of query templates makes this approximation fail at the template level. The study emphasizes that template-level differences must be considered when migrating models.

Q-error is a common metric in the database field for evaluating the accuracy of cardinality or cost estimates, defined as the ratio of the estimated value to the true value (when the estimate is greater than the true value) or the ratio of the true value to the estimated value (when the estimate is less than the true value), i.e., max(estimate/true, true/estimate). A Q-error of 1 indicates a perfect estimate, and larger values indicate greater error. This paper uses "the geometric mean of the median Q-error across templates" as the aggregate metric: first compute the median Q-error for each query template, then take the geometric mean across all templates to balance the weight of different templates. This metric penalizes overestimation and underestimation equally and is suitable for comparing the effectiveness of different correction methods.

Correction Strategy of One Query per Template

The study proposes a correction method: fit separate lines for serial and parallel plans and add an offset for each measured template. With this correction, on TPC-H and TPC-DS, measuring only one query per template reduces the post-migration model error (the geometric mean of the median Q-error across templates) to 1.10 and 1.19, close to the correction effect of retraining the model on the new machine.

By contrast, fine-tuning a DACE-style neural cost model on the same queries yields at most a 2% improvement. This shows that simple per-template correction is more effective than complex neural fine-tuning. However, for templates that have never been measured, no correction method is on average more than 2% better than the speed factor. This means that predictions for unmeasured templates remain difficult and require additional strategies.

DACE (Data-Aware Cost Estimator) is a typical learned cost model that uses a neural network to extract features from query plans and predict execution times, typically trained on large-scale query logs. DACE-style models emphasize data awareness, i.e., considering table statistics and predicate selectivities. This paper uses it as a representative neural cost model for comparison: fine-tuning the DACE model on the same queries yields at most a 2% error improvement, whereas simple per-template linear correction substantially reduces error. This suggests that for cross-hardware adaptation, complex neural fine-tuning is not necessarily better than lightweight template-specific correction, possibly because fine-tuning easily overfits the small number of new samples.

Validation Tests with Fixed Assumptions

To verify the robustness of the conclusions, the research team fixed the assumptions and timed new data. At ten times the scale of TPC-H, the errors were consistent with previous results, but two of five repeated tests failed. This suggests a certain fragility of the model when scaling up.

On the Join Order Benchmark, queries vary greatly within templates, and even when the model is retrained on the new machine, the error reaches 2.36, with no correction method significantly improving it; only tests on new templates hold. For such queries, boosted trees and neural models fit better, and correcting the neural model is as effective as fine-tuning. The study reported all tests as a registered report, enhancing the credibility of the results.

Practical Implications and Limitations

These findings provide guidance for practical system migration: when hardware changes, measuring one representative query per query template and applying per-template correction can significantly improve prediction accuracy at a controllable cost. However, for workloads with large within-template variation (such as the Join Order Benchmark), more complex models are needed. Predictions for unmeasured templates still rely on the speed factor, but with limited accuracy.

The study's limitation is that experiments are based only on PostgreSQL and specific hardware environments, and the generality of the conclusions needs further validation. In addition, the failure rate of repeated tests suggests that in dynamic or large-scale scenarios, the model may need more frequent updates. Nevertheless, this work provides a clear benchmark and direction for cross-hardware adaptation of learned cost models.

One-line Summary

When migrating learned cost models across hardware, per-template correction is more effective than global scaling and neural fine-tuning.

Paragraph Summary

This paper studies the adaptation problem of learned cost models across hardware. The authors timed the same set of plans for 1,162 TPC-H and TPC-DS queries on PostgreSQL across six different hardware environments and found that no single scaling factor can describe performance changes, with variance mainly coming from query templates. They propose a correction method that fits separate lines for serial and parallel plans and adds an offset for each measured template; measuring only one query per template reduces post-migration error to 1.10 (TPC-H) and 1.19 (TPC-DS), close to the effect of retraining. Neural model fine-tuning improves by at most 2%, while per-template correction is more efficient. For unmeasured templates, correction is comparable to the speed factor with limited improvement; on the Join Order Benchmark the model error reaches 2.36 and correction methods are ineffective. The study provides a clear benchmark for practical system migration.