| ---
|
| library_name: sklearn
|
| tags:
|
| - postgresql
|
| - sql
|
| - query-cache
|
| - plan-cache
|
| - redis
|
| - database
|
| - tabular-classification
|
| - tabular-regression
|
| pipeline_tag: tabular-classification
|
| license: mit
|
| ---
|
|
|
| # 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
|
|
|
| ```python
|
| 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)
|
|
|
| ```bash
|
| 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.
|
|
|
