Building OptiDB AI: Understanding PostgreSQL Query Plan Analysis
OptiDB AI explores automated PostgreSQL query performance analysis by examining execution plans. It uses Spring Boot to capture query plans, FastAPI and Python to analyze them, and rule based detection to identify issues such as inefficient sequential scans, row estimation errors, and disk based sorts. An AI layer is being developed to help rank and explain findings while keeping the underlying analysis traceable.
Database queries can look simple on the surface, but the way a database executes them can be very different from what the SQL statement suggests. A query that appears harmless can scan hundreds of thousands of rows, perform unnecessary sorting, or use an inefficient join strategy.
This project, OptiDB AI, explores how these problems can be detected automatically by analyzing PostgreSQL execution plans.
The Problem
PostgreSQL has a query planner that decides how a SQL query should be executed. It considers factors such as available indexes, estimated row counts, table sizes, and the operations required by the query.
The planner produces an execution plan containing information about operations such as sequential scans, joins, sorts, estimated rows, actual rows, execution time, and more.
The problem is that execution plans can become difficult to interpret manually, especially when investigating many queries.
OptiDB AI aims to make this process easier by taking an execution plan and identifying patterns that may indicate inefficient query behavior.
How OptiDB AI Works
The project is divided into two main services.
Spring Boot handles communication with PostgreSQL and captures the execution plan for a given query.
Python with FastAPI analyzes the captured plan and produces findings based on predefined rules.
The basic flow is:
SQL Query → PostgreSQL → Execution Plan → Spring Boot → FastAPI Analyzer → Findings The Java service captures the plan using PostgreSQL's
EXPLAIN output and sends the resulting JSON to the Python service.Keeping the database interaction and analysis separate makes the system easier to develop and test. The analysis rules can be changed without modifying the part responsible for connecting to PostgreSQL.
Why Analyze the Execution Plan?
Looking only at query execution time is not always enough.
For example, a query might execute in around 60 milliseconds because PostgreSQL uses parallel workers. That does not necessarily mean the query is efficient. In one of the project's test cases, PostgreSQL performed a sequential scan and removed roughly 666,000 rows while returning only a small number of results.
This is a useful signal because an appropriate index could potentially reduce the amount of data that PostgreSQL needs to examine.
Therefore, OptiDB AI looks at the structure and statistics of the execution plan rather than relying on execution time alone.
Rule-Based Analysis
The first version of the analyzer uses deterministic rules.
1. Sequential Scan With High Filter Rejection
A sequential scan reads rows from a table and checks them against a filter.
Sequential scans are not automatically bad. They can be perfectly reasonable for small tables or queries that need a large portion of the table.
The analyzer therefore uses additional conditions before reporting a problem. It checks whether the table is sufficiently large, whether a filter exists, and whether a large number of rows are rejected compared with the number returned.
This helps avoid false positives.
The project currently uses a threshold of 5,000 rows for the small table check and a filter rejection ratio of 20 as the initial signal for this rule.
2. Row Estimate Mismatch
PostgreSQL's planner estimates how many rows an operation will produce. Those estimates influence decisions such as which join strategy to use.
If the estimated number of rows differs significantly from the actual number, PostgreSQL may choose a less suitable execution strategy.
OptiDB AI compares
Plan Rows with Actual Rows and reports significant mismatches.Possible causes include outdated statistics or relationships between columns that the planner cannot accurately model.
3. Sort Spilling to Disk
Sorting can be performed in memory, but if the required memory is insufficient, PostgreSQL can spill the operation to disk.
Disk based sorting can be considerably more expensive than an in memory operation.
The analyzer checks the execution plan for sort operations whose sort method or sort space indicates disk usage and reports them as potential performance issues.
An Important Lesson: Parallel Execution
One of the more interesting problems encountered during development involved PostgreSQL's parallel execution.
Some values in an execution plan, including
Actual Rows and Rows Removed by Filter , can be reported per loop. A parallel plan may therefore make the numbers appear smaller than the actual totals.The first version of the analyzer incorrectly interpreted these values without accounting for
Actual Loops .The solution was to normalize the values:
Total Value = Value Per Loop × Actual Loops This matters because an analyzer that produces incorrect statistics can be worse than having no analyzer at all. It may confidently recommend changes based on numbers that were interpreted incorrectly.
Why Python and FastAPI?
Python is used for the analysis service because it provides a simple environment for experimenting with data processing and machine learning components.
FastAPI provides the HTTP interface between the Java application and the analyzer.
The service exposes an endpoint that accepts the SQL query and its execution plan, analyzes the plan recursively, and returns structured findings.
The analyzer walks through the PostgreSQL plan tree and applies each detection rule to every node. This makes it possible to add new rules without redesigning the entire analysis system.
Where the AI Layer Fits
The project does not rely on an AI model to decide whether a query is inefficient.
The initial analysis is deterministic. Rules identify measurable characteristics of the execution plan, while a later AI layer can help rank, summarize, or explain those findings.
This separation is intentional.
A database optimization tool should be able to explain why it identified a problem. Using deterministic rules for the underlying detection provides a traceable basis for the result, while an AI model can be used where language understanding and explanation are more useful.
The current architecture is being extended toward an AI assisted ranking and explanation layer, including local model integration.
What the Project Demonstrates
OptiDB AI is an exploration of how database internals can be turned into structured information that software can analyze automatically.
The project combines:
- PostgreSQL execution plans
- Java and Spring Boot
- Python and FastAPI
- Rule based query analysis
- JSONbased service communication
- Query performance concepts
- AI assisted analysis
More importantly, the project demonstrates that performance analysis is not simply about finding the slowest query. Understanding why PostgreSQL selected a particular execution strategy is often more useful.
The current implementation is still being developed, with additional query plan patterns, testing, and the AI analysis layer planned as the project progresses.
Conclusion
OptiDB AI started from a simple question: Can PostgreSQL execution plans be analyzed automatically to identify useful performance signals?
The current implementation suggests that they can.
By combining deterministic rules with an AI layer for explanation and prioritization, the project aims to make execution plans easier to understand without hiding the underlying database behavior.
The most useful part of the project so far has not been simply getting the analyzer to detect a sequential scan. It has been learning how easy it is to misinterpret database execution data and how important it is to build safeguards against false positives and incorrect assumptions.
That makes OptiDB AI less of a finished optimization product and more of a practical exploration into automated database performance analysis.
Enjoyed reading this content? Let the author know!
Your likes, comments, shares, and saves help creators reach more builders.
Loading recommendations
Loading article