When Is One Query per Template Enough? Adapting Learned Cost Models to New Hardware

Krish Patel

cs.DBNov 4, 2026

When Is One Query per Template Enough? Adapting Learned Cost Models to New Hardware

Authors: Krish Patel

Organizations: University of Waterloo, Waterloo, Canada

Abstract

A learned cost model that predicts query runtimes is trained on measurements from one machine. When the database moves to other hardware, its plans stay the same but their runtimes change, each by its own factor. We ask how to adapt such a model from k queries measured on the new machine: which queries to run, how to use their runtimes, and how many are enough. On PostgreSQL we timed the same plans of 1,162 TPC-H and TPC-DS queries in six environments: a laptop and a server, each with all its processors or pinned to one, the server also pinned to two, and the laptop also with a small buffer pool. No single factor describes the slowdown between two environments: most of its variance lies between query templates, so a speed factor stops improving after a few queries. A correction that fits separate lines for serial and parallel plans and adds an offset for every measured template brings the moved model, with one measured query per template, to an error (geometric mean over templates of the median Q-error) of 1.10 on TPC-H and 1.19 on TPC-DS, close to the same correction on a model trained on the new machine; fine-tuning a DACE-style neural cost model on the same queries gains at most 2%. On templates never measured, no correction we tried is on average more than 2% better than the speed factor. We then fixed hypotheses and timed new data. On TPC-H at ten times the scale the errors follow these results, but two of five repeated tests miss. On the Join Order Benchmark, whose queries differ widely within a template, the model errs by 2.36 even when retrained on the new machine, no correction gains much, and only the test on new templates holds; boosted trees and a neural model fit these queries better, and correcting the neural model works as well as fine-tuning it. We report every test as registered.

Figures & tables

				noise, median Q
Env.	machine	CPUs	buffers	TPC-H	TPC-DS	SF10	JOB
M1	laptop	all 22	2 GB	1.07	1.09		1.06
M2	laptop	1	2 GB	1.07	1.08		1.03
M3	server	all 64	2 GB	1.05	1.05	1.05	1.05
M4	server	1	2 GB	1.07	1.03	1.07	1.04
M5	laptop	all 22	128 MB	1.03	1.05

TABLE I: The environments. CPUs: the processors that PostgreSQL’s server processes may use (the client keeps all). Buffers: PostgreSQL’s buffer pool. Noise: the median Q-error between two passes of the same query; an empty cell is a workload the environment did not run.

TPC-H
		Slowdown		Template share			$R^{2}$ of the log slowdown on
Pair	Change	serial	parallel	shift	$\omega^{2}$	noise	parallel	size	both	features (new)
M1 $\to$ M2	processors	1.12	2.66	0.86	0.86	0.08	0.61	0.00	0.61	0.27
M3 $\to$ M4	processors	1.01	2.07	0.84	0.83	0.03	0.57	0.12	0.62	0.53
M3 $\to$ M6	two processors	1.00	1.32	0.69	0.68	0.03	0.51	0.06	0.55	0.31
M1 $\to$ M3	hardware	1.81	2.07	0.80	0.79	0.08	0.07	0.52	0.63	0.45

TABLE II: How the runtime of the same plan changes, for seven of the 30 ordered pairs of each workload. Slowdown: median of target over source runtime, for serial plans and plans with parallel workers. Template share: the share of the variance of the log slowdown between templates (shift), the same corrected for the number of templates (

\omega^{2}

), and the share between two passes on the source alone (noise).

R^{2}

: least-squares fits of the log slowdown on whether the plan is parallel, on the log source runtime (size) and on both with their interaction; features (new): a ridge model on the 19 features scored on templates (TPC-DS: families) left out of its fit, which can be negative.

	Proc. (laptop)		Proc. (server)		Hardware		Buffer pool
Operator	par.	ser.	par.	ser.	par.	ser.	par.	ser.
Seq Scan	2.85	0.99	2.85	1.01	1.75	1.70	1.12	1.23
Index Scan	2.33	1.00	2.32	1.07	1.64	1.01	1.92	1.42
Bitmap Heap Scan	1.98	0.99	2.04	1.20	2.01	1.49	0.98	2.14
Hash Join	2.56	1.01	2.57	0.94	1.62	1.65	1.00	1.00
Hash	2.89	1.05	2.32	1.02	1.32	1.34	1.13	1.06

TABLE III: Median ratio of each operator’s own time (target over source), inside (par.) and outside (ser.) the parallel part of plans, over the nodes of both workloads weighted by the source’s time, from one extra pass with per-node timing. Proc.: one processor (M1

\to

M2, M3

\to

M4); hardware: M1

\to

M3; buffer pool: M1

\to

M5.

Fig. 1: Error (geometric mean over test templates of the median Q-error) against the number

k

of measured target queries, both axes logarithmic, for a processor change (M1

\to

M2) and a hardware change (M1

\to

M3) on each workload; the TPC-H panels are pairs the methods were designed on. Line style gives the selection: solid random, dashed round robin, dotted k-center. The horizontal dotted line is the oracle.

TPC-H
		$k=10$ , random			$k=25$		$k=100$		New templates, $k=25$
Change	Pairs	Speed	Aff.+par.	Primary	Ridge	Primary	Primary	Oracle	Speed	Aff.+par.	Ridge	Oracle
processors	4	1.41	1.28	1.23	1.20	1.09	1.07	1.05	1.99	2.00	2.13	1.98
two processors	2	1.14	1.12	1.11	1.12	1.08	1.06	1.05	1.76	1.76	1.77	1.76
hardware	4	1.22	1.19	1.17	1.15	1.09	1.07	1.05	1.99	1.98	1.99	1.98
both	4	1.46	1.33	1.26	1.21	1.10	1.08	1.05	2.01	2.01	2.11	1.98

TABLE IV: Error by kind of change, as the geometric mean over its ordered pairs. Speed, Aff.+par. and Ridge use random selection; Primary (the template correction) uses random selection at

k=10

and round robin at

k=25

and

k=100

. Oracle: the forest model refitted on the whole target pool. New templates: each template (TPC-DS: its family) held out from the source model, the

k=25

randomly chosen queries, the oracle and every correction. Mixed: the remaining 14 pairs, all involving M5 or M6.

Method	Selection	TPC-H	TPC-DS
		$k=25$	$k=100$
No adaptation		1.83	1.96
Speed factor	random	1.37	1.45
Ridge correction	random	1.24	1.38
Target only	random	1.51	1.80
Pooled refit	random	1.36	1.36

TABLE V: Error at the budget where each template can have one query, as the geometric mean over the 30 ordered pairs of a workload; the last row applies the primary method to a model trained on the target itself (no move).

Strategy	Primary	1-NN	Ridge	Seconds
TPC-H, $k=25$
random	1.17	1.19	1.24	22
round robin	1.10	1.10	1.17	24
cheapest per template	1.17	1.17	1.23	16
k-center	1.13	1.13	1.21	21
D-optimal	1.14	1.13	1.20	16

TABLE VI: Error of three corrections (columns) when the

k

queries are chosen by each strategy (rows), as the geometric mean over the 30 ordered pairs of a workload, at the smallest budget of our grid that holds one query per template. Seconds: total runtime of the

k

queries on the target, one run each.

Fig. 2: Error against

k

with round-robin selection, geometric mean over the 30 development pairs of each workload. Blue: the speed factor on the forest model (the cost line plus forest); solid purple: the template correction on the forest model; dashed purple: the same correction on the neural model; orange: the neural model fine-tuned on the same queries, all weights (solid) or the head only (dotted). The black dotted line is the neural model retrained on the whole target pool.

Cost line plus forest
	TPC-H			TPC-DS
$k$	10	25	100	10	25	100
speed factor	1.38	1.37	1.37	1.48	1.46	1.44
template correction	1.22	1.10	1.08	1.42	1.35	1.19
Neural (DACE-style)
no adaptation	1.84	1.84	1.84	1.89	1.89	1.89

TABLE VII: Error with round-robin selection, geometric mean over the 30 development pairs of each workload, for two source models: the cost line with a forest on its residuals, and the DACE-style neural model. Fine-tuning uses the same

k

queries as the corrections.

	E1: SF10, 6 pairs		E2: JOB, 20 pairs
Method	$k=10$	$k=100$	$k=10$	$k=33$
No adaptation	1.66	1.66	2.71	2.71
Speed factor	1.31	1.30	2.51	2.48
Affine+parallel	1.31	1.26	2.55	2.42
Ridge correction	1.31	1.16	2.88	2.45
Primary	1.25	1.12	2.49	2.41

TABLE VIII: Error in the second round, as the geometric mean over the ordered pairs of each experiment, at

k=10

and at

k_{H}

. The speed factor, affine+parallel and the ridge correction use random selection, the primary method and the fine-tuned network round robin, and 1-NN k-center. Oracle: the forest model refitted on the whole target pool; no move: the primary method on a model trained on the target itself.

	Hypothesis and result
H1	Template share above 0.5 in every pair (both directions of a pair share one value); smallest 0.74.	holds
H2	$k=100$ : primary (round robin) beats the speed factor (random) in every pair and the ridge correction (random) in at least two thirds; 18 and 16 of 18.	holds
H3	$k=10$ : affine+parallel (random) has a lower error than the speed factor (random) in at least three quarters of the pairs that change the processor count; 10 of 12.	holds
H4	New templates: the speed factor’s error is at most 1.15 times the oracle’s in every pair; largest 1.08.	holds
H5	$k=100$ : k-center with 1-NN has a lower error than random selection with the ridge correction in at least two thirds; 18 of 18.	holds

TABLE IX: The five hypotheses of the first round, fixed before the labels that complete the 18 confirmatory pairs existed (the 12 ordered TPC-DS pairs among M1 to M4 and the 6 TPC-H pairs with M4), and their results. H2 counts pairs whose 95% interval lies below 1; H3 and H5 count pairs whose ratio is below 1, as registered; H4 bounds the speed factor’s error over the oracle’s.

		E1: TPC-H SF10		E2: JOB
	Test	Count	Result	Count	Result
H1	template share $>0.5$ , every pair	6/6	holds	18/20	fails
H2	$k_{H}$ : primary below speed (interval), every pair; below ridge, $\geq 2/3$	5/6, 4/6	fails	2/20, 0/20	fails
H3	$k=10$ : aff.+par. below speed, $\geq 3/4$ of processor pairs	2/6	fails	6/16	fails
H4	new templates: speed / oracle $\leq 1.15$ , every pair	6/6	holds	20/20	holds
H5	$k_{H}$ : k-center 1-NN below ridge, $\geq 2/3$	6/6	holds	0/20	fails

TABLE X: The second round’s tests, fixed before any of its labels existed: TPC-H at SF10 (E1, the 6 ordered pairs among M3, M4 and M6) and JOB (E2, the 20 pairs among M1, M2, M3, M4 and M6), with

k_{H}

100 on E1 and 33, one query per template, on E2. Counts as in Table IX ; H2 gives the pairs below the speed factor and below the ridge correction, H6a the pairs whose ratio is below 1 and the geometric mean of the ratios. H6, as first registered, counts all new pairs together.

Database Systems

See all 46 papers

Pre-Execution Query Slot-Time Prediction in Cloud Data Warehouses: A Feature-Scoped Machine Learning Approach
Apr 22, 2026Prashant Kumar PathakCloud Computing
Independent Researcher · Santa Clara, CA, USA
Agentic Cost-Aware Query Planning with Knowledge Distillation for Big Data Analytics
May 18, 2026Mahdi Naser-MoghadasiLearning to Optimize
Research Division, BrightMind AI, Seattle, WA · Texas Tech University, Lubbock, TX
DataKernelBench: Can LLMs Optimize Database Queries on GPUs?
Aug 25, 2026Gokul Karthik Kumar, Yotam Perlitz, Corey Lammie +2GPU Kernel Optimization GPU Kernel Generation
IBM Research, Zurich, Switzerland · TU Wien, Vienna, Austria

Apr 22, 2026J/K move · Enter open · S save

Pre-Execution Query Slot-Time Prediction in Cloud Data Warehouses: A Feature-Scoped Machine Learning Approach

Prashant Kumar Pathak

Independent Researcher · Santa Clara, CA, USA

Cloud Computing

Open paper AskPDF arXiv

Cloud data warehouses bill compute based on slot-time consumed. In shared multi-tenant environments, query cost is highly variable and hard to estimate before execution, causing budget overruns and degraded scheduling. Static query-planner heuristics fail to capture complex SQL structure, data skew, and workload contention. We present a feature-scoped machine learning approach that predicts BigQuery slot-time before execution using only pre-execution observable signals: a structured query complexity score derived from SQL operator costs, data volume features from planner estimates and workload metadata, and textual features from query text. We deliberately exclude runtime factors (slot-pool utilization, cache state, realized skew) unknowable at submission. The model uses a HistGradientBoostingRegressor trained on log-transformed slot-time, with a TF-IDF + TruncatedSVD-512 text pipeline fused with numeric and categorical features. Trained on 749 queries across seven deployment environments and evaluated out-of-distribution on 746 queries from two held-out environments, the model achieves MAE 1.17 slot-minutes, RMSE 4.71, and 74% explained variance on the full workload. On cost-significant queries (slot-time >= 0.01 min, N=282) the model achieves MAE 3.10 versus 4.95 for a predict-mean baseline and 4.54 for predict-median, a 30-37% reduction. On long-tail queries (>= 20 min, N=22) the model does not outperform trivial baselines, consistent with the hypothesis that long-tail queries are dominated by unobserved runtime factors outside the current feature scope. A complexity-routed dual-model architecture is described as a practical refinement, and directions for closing the long-tail gap are identified as future work.

Abstract

Database Systems

Explore similar work

Pre-Execution Query Slot-Time Prediction in Cloud Data Warehouses: A Feature-Scoped Machine Learning Approach

Agentic Cost-Aware Query Planning with Knowledge Distillation for Big Data Analytics

DataKernelBench: Can LLMs Optimize Database Queries on GPUs?