SQL Query Cost Predictor (ML)

Last update on October 13 2026 11:32:05 (UTC/GMT +8 hours)

ML Powered SQL Query Cost Predictor : An Introduction

Every SQL query carries a cost. Some queries return in milliseconds, scanning a few hundred rows through an index. Others grind for minutes, performing sequential scans across millions of rows, spilling to disk, and consuming CPU cycles that starve other workloads. The difference between these outcomes is often invisible until the query actually runs — and by then, in a production environment, the damage may already be done. A slow query can lock tables, exhaust connection pools, and cascade into an outage that affects every user of the application.

Traditional query planners offer some protection. PostgreSQL's planner estimates costs before execution, using statistics about table sizes and data distributions. But these estimates are notoriously imprecise. They assume uniform data distribution, independence between columns, and perfect statistics — assumptions that rarely hold in real databases. The result is that the planner's cost estimates can be off by orders of magnitude, and queries that look cheap to the planner can turn out to be catastrophically expensive.

The SQL Query Cost Predictor takes a different approach. Rather than relying solely on the planner's analytical model, it uses machine learning trained on actual query execution data — the outputs of EXPLAIN ANALYZE — to predict how long a query will take, how much CPU it will consume, and how much I/O it will generate. It then goes further: suggesting indexes, recommending query rewrites, and flagging anti-patterns that developers should avoid. The result is a tool that catches expensive queries before they reach production, complementing the traditional planner with data-driven heuristics.

What It Does

The application's interface is deceptively simple: a text box where users paste a SQL query. Behind that simplicity lies a sophisticated pipeline that analyzes the query, extracts features, runs a trained model, and produces actionable recommendations.

The core capability is cost prediction. Given a query, the model estimates:

Execution time in milliseconds
CPU cost, measured in abstract units or estimated cycles
I/O cost, reflecting the number of pages read from disk and memory
Confidence intervals, acknowledging that predictions are probabilistic

These predictions are derived from a model trained on thousands of EXPLAIN ANALYZE outputs — actual execution data from real queries against real databases. Unlike the planner's estimates, which are based on a cost model with fixed constants, the ML model learns the actual relationships between query features and execution costs from empirical data.

Beyond prediction, the tool offers recommendations:

Index suggestions: Based on the query's filters, joins, and sort operations, the tool identifies columns that would benefit from indexing and estimates the performance improvement
Rewrite strategies: The tool detects patterns that can be expressed more efficiently — subqueries that could be joins, OR conditions that could be UNION ALL, correlated subqueries that could be window functions
Anti-pattern flags: Common mistakes that degrade performance — SELECT * when only specific columns are needed, missing WHERE clauses on large tables, implicit type conversions in join conditions, functions applied to indexed columns in WHERE clauses

Crucially, the tool provides explainability. Using SHAP (SHapley Additive exPlanations), it shows which features contributed most to the predicted cost. This transforms the model from a black box into a teaching tool — users learn why a query is expensive, not just that it is.

Architecture and Data Flow

Training Data: EXPLAIN ANALYZE Outputs

The model's foundation is a corpus of query execution data. Every training example pairs a query with its actual execution metrics, captured from EXPLAIN (ANALYZE, FORMAT JSON).

PostgreSQL's EXPLAIN ANALYZE actually executes the query and reports both the planner's estimates and the actual runtime statistics. The JSON format provides structured access to this data:

json:


[
  {
    "Plan": {
      "Node Type": "Hash Join",
      "Startup Cost": 1250.00,
      "Total Cost": 4520.75,
      "Plan Rows": 15000,
      "Actual Startup Time": 45.2,
      "Actual Total Time": 387.6,
      "Actual Rows": 14238,
      "Plans": [
        {
          "Node Type": "Seq Scan",
          "Relation Name": "orders",
          "Actual Rows": 500000,
          "Actual Total Time": 210.3
        },
        {
          "Node Type": "Hash",
          "Actual Rows": 15000,
          "Actual Total Time": 120.1
        }
      ]
    },
    "Execution Time": 412.8
  }
]

From this structure, the training pipeline extracts features: the plan node types, the estimated and actual row counts, the estimated and actual costs, the execution time breakdown by node, and the buffer usage statistics. These features, combined with the query's structural characteristics, form the input to the model. The target variable is the total execution time (or CPU/I/O cost).

Building a good training corpus requires diversity. Queries should span different tables, different data volumes, different plan shapes, and different complexity levels. A model trained only on simple SELECT statements will perform poorly on complex joins. Ideally, the corpus includes queries from the actual target database, so the model learns its specific performance characteristics.

Feature Engineering

The quality of the model depends heavily on feature engineering — extracting the right signals from queries and plans.

Query-level features come from parsing the SQL with sqlparse:

Number of tables in the FROM clause
Number of join conditions
Presence and complexity of WHERE clauses
Number of GROUP BY and ORDER BY columns
Presence of subqueries, CTEs, window functions
Use of aggregate functions
Presence of SELECT *
Number of distinct columns referenced

Schema-aware features require metadata:

Row counts of referenced tables
Presence of indexes on filtered/joined columns
Column cardinality (number of distinct values)
Data types of compared columns
Whether columns are nullable

Plan-level features come from EXPLAIN output (without ANALYZE, since the goal is to predict before execution):

Plan node types (Seq Scan, Index Scan, Hash Join, Nested Loop, etc.)
Estimated row counts at each node
Estimated costs at each node
Depth and breadth of the plan tree
Join algorithms chosen

Normalized query text is captured by sqlparse, which strips literals and standardizes formatting, allowing the model to recognize query patterns regardless of specific parameter values.

Model Training

With features extracted, the training pipeline fits a regression model. Gradient boosting — XGBoost or LightGBM — is a natural choice for this task. These models handle heterogeneous feature types, capture non-linear relationships, and provide feature importance scores that contribute to explainability.

Training involves:

Splitting the corpus into training and validation sets
Fitting the model on training data
Evaluating on validation data using metrics like MAE (mean absolute error) and MAPE (mean absolute percentage error)
Hyperparameter tuning via cross-validation
Retraining on the full dataset with the best hyperparameters

A critical consideration is prediction target transformation. Execution times span orders of magnitude — from sub-millisecond to minutes. Predicting raw milliseconds is difficult because the model would be dominated by the largest values. A common approach is to predict log(execution_time), which compresses the range and makes the regression more tractable. Predictions are then exponentiated back to the original scale.

Another consideration is classification vs. regression. For many use cases, the exact predicted time matters less than a categorical assessment: "This query is fast / acceptable / slow / dangerous." A classification model trained on buckets (e.g., <10ms, 10-100ms, 100ms-1s, >1s) can be more robust than a regression model, especially when training data is limited.

Prediction Pipeline

When a user submits a query, the prediction pipeline runs:

Parse: sqlparse normalizes the query and extracts structural features
Enrich: Schema metadata is fetched to add table sizes, index presence, and column statistics
Plan: EXPLAIN (without ANALYZE) generates the query plan, from which plan-level features are extracted
Predict: The feature vector is passed to the trained model, which outputs predicted cost
Explain: SHAP values are computed to attribute the prediction to specific features
Recommend: Rule-based and ML-based heuristics generate index suggestions, rewrite strategies, and anti-pattern flags

The entire pipeline runs in well under a second for typical queries, making the tool responsive enough for interactive use.

Explainability with SHAP

SHAP (SHapley Additive exPlanations) provides a principled way to explain model predictions. Based on cooperative game theory, SHAP values attribute a prediction to each input feature by computing its marginal contribution across all possible feature subsets.

For a query cost prediction, SHAP might reveal that 60% of the predicted cost comes from the large table scan, 25% from the join condition lacking an index, and 15% from the sort operation. This attribution is invaluable for developers: it tells them where to focus optimization efforts.

SHAP also produces visualizations — waterfall plots, force plots, summary plots — that make the explanation accessible. A waterfall plot, for example, shows how each feature pushes the prediction up or down from a baseline, giving an intuitive sense of what drives cost.

The combination of accurate prediction and clear explanation makes the tool genuinely useful rather than merely interesting. Users do not have to trust the model blindly; they can see the reasoning behind its predictions.

Anti-Pattern Detection

SELECT * on Wide Tables

SELECT * retrieves all columns, even those the application does not use. On a table with many columns — especially wide text or JSON columns — this multiplies I/O and network transfer. The tool flags SELECT * and estimates the cost savings from selecting only needed columns.

Missing WHERE Clauses

A query without a WHERE clause on a large table performs a full table scan. On a table with millions of rows, this can be catastrophic. The tool flags queries that scan large tables without filters and suggests appropriate filtering strategies.

Functions on Indexed Columns

A WHERE clause like WHERE UPPER(email) = '[email protected]' cannot use an index on email, because the function transforms the column value. The tool detects this pattern and suggests alternatives: functional indexes, expression indexes, or query rewrites.

Implicit Type Conversions

Comparing a string column to a numeric literal — WHERE id = '123' where id is an integer — can prevent index usage or cause unexpected behavior. The tool detects type mismatches and flags them.

Correlated Subqueries

A subquery that references columns from the outer query executes once per outer row, which can be extremely expensive. The tool identifies correlated subqueries and suggests rewriting them as joins or window functions.

Leading Wildcards in LIKE

WHERE name LIKE '%smith' cannot use a standard B-tree index because the wildcard is at the beginning. The tool flags leading wildcards and suggests trigram indexes or full-text search alternatives.

Missing LIMIT on Ordered Queries

A query with ORDER BY but no LIMIT sorts the entire result set, even if only the top N rows are needed. The tool flags this pattern and suggests adding a LIMIT.

OR Conditions Across Columns

WHERE a = 1 OR b = 2 often prevents effective index usage, because the planner may choose a sequential scan. The tool suggests rewriting as UNION ALL of two indexed queries.

Modern Technology Stack

PostgreSQL EXPLAIN (ANALYZE, FORMAT JSON)

The source of training data and the basis for plan-level features. The JSON format provides structured access to plan nodes, costs, row estimates, and actual execution statistics. Using EXPLAIN (without ANALYZE) for prediction avoids executing potentially dangerous queries.

scikit-learn and XGBoost

The machine learning libraries for model training and prediction. XGBoost's gradient boosting handles the heterogeneous, non-linear relationships between query features and execution cost. scikit-learn provides the surrounding infrastructure: train/test splitting, cross-validation, metrics, and preprocessing pipelines.

FastAPI

The web framework for the API layer. FastAPI handles query submission, orchestrates the prediction pipeline, and returns predictions with explanations. Its async architecture suits the I/O-bound operations involved in schema enrichment and plan generation.

sqlparse

The SQL parsing library for query normalization and structural feature extraction. sqlparse handles the messy reality of SQL text — comments, whitespace variations, nested parentheses — and produces a normalized representation suitable for feature extraction.

SHAPSHAP

The explainability library that attributes predictions to features. SHAP's game-theoretic foundation ensures that attributions are consistent and fair, and its visualizations make explanations accessible to non-specialists.

User Benefits

Catching Expensive Queries Before Production

The primary benefit is prevention. By predicting query cost before execution, the tool allows developers to identify problematic queries during development, when fixing them is cheap. A query that would have caused a production incident is caught in code review or testing, and the fix is applied before deployment.

Complementing Traditional Query Planners

The ML model does not replace PostgreSQL's planner — it complements it. The planner remains the authoritative source for query execution; the ML model provides an independent assessment based on empirical data. When the two agree, confidence is high. When they disagree, investigation is warranted — often revealing statistics staleness, data skew, or planner limitations.

Learning Query Optimization

The explainability features transform the tool into a teaching aid. Developers see not just that a query is expensive, but why. They learn to recognize anti-patterns, understand the impact of indexes, and internalize the principles of query optimization. Over time, this knowledge reduces the frequency of problematic queries.

Actionable Recommendations

Prediction alone is not enough — developers need to know what to do about it. The tool's index recommendations, rewrite strategies, and anti-pattern flags provide concrete next steps. Rather than simply saying "this query is slow," it says "add an index on orders(customer_id, created_at) and rewrite the correlated subquery as a join."

Faster Development Cycles

Query optimization traditionally requires running queries, examining plans, and iterating. The cost predictor accelerates this loop by providing immediate feedback before execution. Developers can test variations of a query and see predicted costs without waiting for actual execution — a significant speedup for complex queries.

Conclusion

The SQL Query Cost Predictor brings machine learning to the traditionally analytical problem of query optimization. By training on actual execution data, it captures performance characteristics that the planner's cost model cannot, and by providing explanations and recommendations, it transforms predictions into actionable guidance.

The technology stack — PostgreSQL's EXPLAIN, XGBoost, FastAPI, sqlparse, and SHAP — is composed of mature, well-understood tools. There is no exotic dependency, no experimental framework. The innovation is in the combination: using empirical data to predict query cost, and using explainability to make those predictions useful.

For development teams managing performance-critical databases, the cost predictor offers a valuable addition to the toolkit. It catches expensive queries before they reach production, teaches developers to recognize anti-patterns, and provides the concrete recommendations that turn insight into action. In a world where database performance directly affects user experience, tools that make query cost visible and understandable become essential.