Practical Prediction of Query Execution Time in PostgreSQL Using Lightweight Machine Learning Models

Balla, Tamás (2026) Practical Prediction of Query Execution Time in PostgreSQL Using Lightweight Machine Learning Models. In: Proceedings of the 13th International Conference on Applied Informatics. Líceum Kiadó, Eger, pp. 34-47. ISBN 9789634963271

Preview

Text ICAI2026-pp34-47.pdf - Published Version Download (584kB) | Preview

Abstract

Accurate prediction of query execution time is important for workload scheduling, admission control, and service-level management in modern database systems. In PostgreSQL, the optimizer selects execution plans using internal cost estimates that reflect relative expected work rather than actual wall-clock runtime. This paper investigates whether these optimizer cost estimates, combined with a small set of plan-structure features extracted from machine-readable EXPLAIN output, can be calibrated into practical runtime predictions for analytical SQL queries. The study uses the TPC-H benchmark at scale factor 1 in a controlled single-user PostgreSQL environment. Features are extracted from EXPLAIN (FORMAT JSON), while runtime labels are obtained from EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON). A linear baseline is compared with a Gradient Boosting Decision Tree (GBDT) regressor. The results show that optimizer cost and measured runtime are strongly correlated (Pearson’s r = 0.8625), but a direct linear model fails to capture their relationship, yielding very poor predictive performance (MAE = 97,616.04 ms, R2 = −8, 869.94). In contrast, the GBDT model achieves substantially better accuracy on the random evaluation split (MAE = 74.07 ms, R2 = 0.9924), indicating that the cost-to-runtime relationship is strongly nonlinear. These findings support the view that PostgreSQL’s optimizer cost contains useful predictive signal, but practical runtime prediction requires nonlinear calibration. The study is intentionally modest in scope and should be interpreted as a proof of concept.

Actions (login required)

View Item