Redefining Cost Estimation in Database Systems: The Role of Execution Plan Features and Machine Learning
Utsav Pathak, Amit Mankodi
TL;DR
This work tackles the inaccuracy of traditional PostgreSQL cost models by training learned runtime predictors directly from real execution plans and SQL semantics. It develops an end-to-end pipeline that generates diverse TPC-H queries, collects EXPLAIN ANALYZE traces, extracts scalar/structural/semantic features, and trains multiple models, with XGBoost using plan features and SentenceBERT embeddings achieving the best results (MSE ≈ 0.300 and ≈65% within ±10%). The findings demonstrate that combining plan topology, actual execution signals, and query semantics yields robust runtime predictions, outperforming baseline and deep-sequence models in realistic data regimes. Practically, integrating such models into the optimizer could improve plan selection, resource planning, and SLA adherence with low inference overhead, while highlighting avenues for future work in more expressive architectures and cross-DBMS generalization.
Abstract
Accurate query runtime prediction is a critical component of effective query optimization in modern database systems. Traditional cost models, such as those used in PostgreSQL, rely on static heuristics that often fail to reflect actual query performance under complex and evolving workloads. This remains an active area of research, with recent work exploring machine learning techniques to replace or augment traditional cost estimators. In this paper, we present a machine learning-based framework for predicting SQL query runtimes using execution plan features extracted from PostgreSQL. Our approach integrates scalar and structural features from execution plans and semantic representations of SQL queries to train predictive models. We construct an automated pipeline for data collection and feature extraction using parameterized TPC-H queries, enabling systematic evaluation of multiple modeling techniques. Unlike prior efforts that focus either on cardinality estimation or on synthetic cost metrics, we model the actual runtimes using fine-grained plan statistics and query embeddings derived from execution traces, to improve the model accuracy. We compare baseline regressors, a refined XGBoost model, and a sequential LSTM-based model to assess their effectiveness in runtime prediction. Our dataset includes over 1000 queries generated from TPC-H query templates executed in PostgreSQL with EXPLAIN ANALYZE. Experimental results show that the XGBoost model significantly outperforms others, achieving a mean squared error of 0.3002 and prediction accuracy within 10% of the true runtime in over 65% of cases. The findings highlight the potential of tree-based learning combined with execution plan features for improving cost estimation in query optimizers.