Project case study
DB Optima
Evaluate PostgreSQL index recommendations against measured query performance.
The approach
Analyzes EXPLAIN ANALYZE plans, generates index candidates and prioritizes them with an ML model. A before/after benchmark makes the final recommendation, and results are persisted.
Results and scope
What the project demonstrates
In one local sample query, median latency decreased from 29.217 ms to 0.03 ms. This is specific to that workload and is not a general performance claim.
How it works
- Inspect the plan and expensive scans with EXPLAIN ANALYZE
- Generate index candidates and rank them with the model
- Benchmark before / after a temporary index; persist the decision
Run locally
Windows PowerShell: run from the repository root. See the README for required settings and dependencies.
docker compose up -d
cd backend
python -m venv .venv
.\.venv\Scripts\Activate.ps1
pip install -r requirements.txt
uvicorn app.main:app --reloadCheck
GET /health
POST /api/databases/{database_id}/optimize-queryScope and limitations
The 29.217 ms → 0.03 ms comparison is one local query example. Data size, cache, hardware and PostgreSQL statistics affect results; benchmark indexes on a controlled database.