pg_plan_cache Models
Three machine learning models for the pg_plan_cache PostgreSQL extension — a query execution plan cache backed by Redis.
Models
1. SQL Cache Advisor
- Task: Classification (high / medium / low)
- Algorithm: Random Forest (200 trees)
- Purpose: Predicts whether caching a query's execution plan will be beneficial
2. Cache TTL Recommender
- Task: Regression (seconds)
- Algorithm: Gradient Boosting
- Purpose: Recommends optimal cache TTL based on query characteristics
3. Query Complexity Estimator
- Task: Regression (1-100 score)
- Algorithm: Gradient Boosting
- Purpose: Estimates query complexity to prioritize caching resources
Features
All models use 28 structural features extracted from raw SQL text:
| Feature | Description |
|---|---|
query_length |
Character count |
query_type |
SELECT=0, INSERT=1, UPDATE=2, DELETE=3 |
num_tables |
Tables referenced |
num_joins |
JOIN clause count |
num_conditions |
AND/OR conditions |
num_aggregates |
Aggregate function count |
num_subqueries |
Subquery count |
has_window_func |
Window functions present |
has_cte |
Common Table Expressions |
nesting_depth |
Max parenthesis depth |
| ... | 18 more features |
Usage
from predict import predict, format_prediction
result = predict("SELECT u.name, COUNT(o.id) FROM users u JOIN orders o ON u.id = o.user_id GROUP BY u.name")
print(format_prediction(result))
# Cache Benefit: HIGH
# Recommended TTL: 4200s (1h 10m)
# Complexity: 62/100 (complex)
Training
Trained on 8,000 synthetic SQL queries across 18 complexity tiers:
- Simple SELECTs, filtered queries, ORDER BY
- Single and multi-table JOINs
- Aggregations with GROUP BY / HAVING
- Subqueries, correlated subqueries, EXISTS
- CTEs, window functions, UNION
- Complex analytics queries
- INSERT / UPDATE / DELETE (non-cacheable)
pip install -r requirements.txt
python train.py
About pg_plan_cache
pg_plan_cache is a PostgreSQL extension that caches query execution plans in Redis. It hooks into the PostgreSQL planner, normalizes queries, computes SHA-256 hashes, and stores serialized plans with configurable TTL and automatic schema-change invalidation.
- Downloads last month
- -