When is one query per template enough? Adapting learned cost models to new hardware

The question

Knowing how long a query will run before it runs helps with admission control, scheduling and provisioning. Learned models predict runtime from plan features far better than raw optimizer costs, as long as they are trained on the system they predict. Machines change, though: a database moves to new hardware, to a cloud instance of a different size, or into a container with fewer processors. The plans stay, and every runtime the model learned from is now off by an amount that depends on the query.

Systems handle this by retraining on the new machine, which needs many measured queries, or by rescaling every prediction with one fitted factor. The study asks what lies between: if you can afford to run k queries on the new machine, which queries should they be, how should their runtimes be used, and how large does k have to be?

In machine-learning terms the inputs do not change. The same queries get the same plans and the same features everywhere, and only the distribution of runtime given those features shifts. Reweighting unlabeled inputs, the usual answer to covariate shift, has nothing to correct, so the model needs runtimes measured on the new machine, and each one costs time there.

What I measured

To make every change in runtime come from the environment, the plans were held fixed. I timed 1,162 queries from 115 templates of TPC-H and TPC-DS at scale factor 1 on PostgreSQL 18.6, with one build, one set of settings and copies of the same database files, so that every saved plan, with its estimates and costs, is identical in all six environments.

Every environment ran every query in three passes in different random orders. In a pass a query ran four times with EXPLAIN (ANALYZE, TIMING OFF), the first only to warm the cache, and a label is the median over passes of the median of the three timed runs. A pass was measured again if its level shifted partway through, or if the laptop slept or ran on battery. Between two passes of the same query the median Q-error is 1.03 to 1.09, which sets the floor any model can reach.

Getting clean timings took more care than the modelling. The laptop's virtual machine clock ran 1.2% to 2% fast until I fixed its clock source, and closing the lid suspends the laptop whatever a keep-awake request asks. A level shift partway through a pass, the laptop going to sleep, other programs, and that clock each distorted an early attempt before a check caught it.

How runtimes shift

No single factor describes the move between any two environments. Over all 30 ordered pairs of each workload, 0.69 to 0.94 of the variance of the log slowdown lies between templates, against at most 0.08 (TPC-H) and 0.22 (TPC-DS) between two passes on one machine.

What separates the templates depends on the change. Pinning PostgreSQL to one processor slows plans with parallel workers by a median factor of 2.05 to 2.66, while serial plans change by 0.92 to 1.12, and that one bit explains 0.44 to 0.61 of the variance. Moving from the laptop to the server slows serial and parallel plans alike, but short queries more than long ones. Per-operator timings show the same mechanism on both machines: on one processor, operators inside the parallel part of a plan take 2.0 to 3.2 times as long, and serial operators keep their speed.

The corrections compared

The source model fits a line on the optimizer's total cost and a random forest on that line's residuals, from 19 features of the plain EXPLAIN plan. Every correction works on the source model's log errors on the k measured queries:

• Speed factor: one constant, the mean log error.
• Ridge correction: ridge regression of the errors on the 19 features. As the penalty grows it becomes the speed factor, so the speed factor is one end of its family.
• Affine+parallel: separate lines in the predicted runtime for serial and parallel plans, with at most four parameters.
• Template offsets: the speed factor plus, for every template among the k queries, the mean residual of that template's queries.
• Template correction, the primary method: affine+parallel plus the same per-template offsets.
• 1-NN: the error of the nearest measured query in feature space, which needs no template labels.

Against these I ran refits (on the k queries alone, or pooled with the old machine's), a lookup of each template's measured runtime with no model, an oracle retrained on the whole target pool, and a no-move control that applies the correction to a model trained on the target itself. The gap to the no-move control is the cost of the move.

Results at one query per template

The speed factor finds the average slowdown within about five queries and then stops improving, because one factor cannot fit templates whose slowdowns differ. Once each template has a measured query, the per-template offsets carry the gain: the template correction reaches 1.10 on TPC-H and 1.19 on TPC-DS, against 1.08 and 1.18 for the same correction on a model trained on the new machine. Refitting on the measured queries does far worse at this budget, including a forest that is told the templates.

The model still matters inside a template. A lookup of each template's measured runtime errs by 1.24 on TPC-H, because the widened queries vary inside their templates. The advantage also does not depend on the source model: with a random forest, gradient-boosted trees, ridge regression or the cost line as the source, the template correction beats the speed factor in all 240 combinations of source model, workload and pair.

Which queries to measure

Covering the templates is what matters. On TPC-H at k = 25, choosing one query per template in turn lowers the primary method's error from 1.17 with random selection to 1.10. Without template labels, k-center selection on the plan features with the 1-NN correction comes close (1.13), since queries of one template have similar plans. Measuring the cheapest query of each template saves time (16.4 against 23.6 seconds on TPC-H) but costs accuracy, because short queries shift differently from long ones.

A neural cost model

I reimplemented the architecture of DACE, a transformer over the plan tree, as a representative of the plan-reading models in the literature, which adapt to new hardware by fine-tuning. Fine-tuned on the same queries, it gains at most 2% over the corrected forest model: at k = 100 it reaches 1.06 on TPC-H and 1.17 on TPC-DS against 1.08 and 1.19, and at k = 10 it falls behind, 1.28 and 1.46 against 1.22 and 1.42. The template correction applied to the neural model itself does as well as fine-tuning it.

What did not work

On templates with no measured query, no correction I tried is on average more than 2% better than the speed factor. Retraining helps little there too: the oracle, retrained on the target pool without the held-out template, errs by 1.76 to 2.11 on TPC-H and 2.52 to 2.99 on TPC-DS, about as much as the speed factor. Plan-level models are known to err on templates they have not seen, and the move to a new machine barely adds to that error. The course project's second primary test, whether a model anchored to the optimizer's cost predicts unseen templates better than a line on that cost, failed: the ratio was 1.04, with a 95% interval from 0.90 to 1.19.

Testing the findings on new data

I designed the corrections on TPC-H pairs among three of the environments, so those results are exploratory. Before the labels of 18 further pairs existed, I committed the primary method and five hypotheses to the repository. All five hold on those pairs.

A second round committed its hypotheses before any of its queries ran, and repeated the tests on TPC-H at scale factor 10 and on the Join Order Benchmark (JOB), which runs on real IMDB data. Most of its tests fail:

• At ten times the data the errors follow the first round (the template correction 1.12 against 1.30 for the speed factor, with 100 queries), but two of the five repeated tests miss.
• On JOB, a template's queries differ in runtime by a factor of 2.31, and the source model errs by 2.36 even when retrained on the new machine. With that much error inside templates, no correction gains much, and only the test on new templates holds. Gradient-boosted trees and the neural model fit JOB better.

The paper reports every test as it was registered, including the ones that failed.

Limits

The study covers one database system. The scale-factor-1 data fit in memory in every environment, and there are two physical x86 hosts, both virtualized; a third experiment on a machine that is not virtualized was registered in case one became available and was not run. Queries ran warm and one at a time. Plans are fixed by design, so a move that also changes settings or plans is out of scope. The pairs of a workload reuse the labels of its environments, so they are not independent replications, and the results are reported per pair rather than pooled. Operator-level corrections, other database systems, plans that change with the machine, and data larger than memory are the obvious next steps.

Code and data

The queries, plans, labels, per-operator timings, the study plan with its timestamped amendments, and the scripts that build every number, table and figure are public. No number in the manuscript is typed by hand; each one is generated from the saved measurements, and a fresh clone reproduces every reported number of the scale-factor-1 study exactly on Linux. The code is under the MIT license and the measurements under CC BY 4.0.

github.com/KrishPatel13/query-runtime-transfer

References

1. Krish Patel. When Is One Query per Template Enough? Adapting Learned Cost Models to New Hardware. Manuscript, 2026. PDF
2. Zibo Liang, Xu Chen, Yuyang Xia, Runfan Ye, Haitian Chen, Jiandong Xie, and Kai Zheng. DACE: A Database-Agnostic Cost Estimator. In Proceedings of ICDE, pages 4925-4937, 2024. doi:10.1109/ICDE60146.2024.00374
3. Viktor Leis, Andrey Gubichev, Atanas Mirchev, Peter Boncz, Alfons Kemper, and Thomas Neumann. How Good Are Query Optimizers, Really? PVLDB 9(3), pages 204-215, 2015. doi:10.14778/2850583.2850594

This project began as my course project for CS 680 in Fall 2026. Thanks to Professor Yao-Liang Yu, whose feedback on the proposal framed it as an adaptation problem: move to another machine, adapt quickly, and keep learning. Every number on this page comes from the manuscript and the public repository.