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. Eger, Eszterházy Károly Catholic University Líceum Publisher. pp. 34-47.

pdf ICAI2026-pp34-47.pdf Download (584kB) [error in script]

Absztrakt (kivonat)

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.

Mű típusa:	Könyvrészlet - Book section
Szerző:	Szerző neve Email MTMT azonosító ORCID azonosító Közreműködés Balla, Tamás NEM RÉSZLETEZETT NEM RÉSZLETEZETT NEM RÉSZLETEZETT Szerző
Kapcsolódó URL-ek:	Befoglaló kötet
Kulcsszavak:	query performance prediction, cost model, machine learning
Nyelv:	angol
DOI azonosító:	10.17048/icai.2026.34
Felhasználó:	Tibor Gál
Dátum:	22 Szep 2026 06:42
Utolsó módosítás:	22 Szep 2026 06:42
URI:	http://publikacio.uni-eszterhazy.hu/id/eprint/9433

Műveletek (bejelentkezés szükséges)

Tétel nézet