Recommend prewarm targets
recommend_prewarm_targetsIdentifies PostgreSQL relations that would benefit from pg_prewarm by analyzing cache miss rates and sequential scan patterns, then recommends prewarm targets within a configurable shared_buffers budget.
Instructions
Recommend relations whose first-query latency would benefit from pg_prewarm. Inspects pg_stat_user_tables + pg_statio_user_tables to find high cold-miss-rate / seq_scan-dominant relations, and caps the cumulative cost at shared_buffers_budget_pct * shared_buffers so the recommendation never silently exceeds shared_buffers. The advisor is read-only — never invokes pg_prewarm itself. Returns an object with shared_buffers_blocks (configured shared_buffers in 8 KiB pages), budget_blocks (the cap), total_cost_blocks (sum of recommendations actually returned), and candidates — a list of objects with schema, relation, reason (seq_scan_dominant / high_cold_miss_rate / small_hot_relation_uncached / index_in_critical_path), prewarm_mode, estimated_buffer_cost, heap_blks_read, heap_blks_hit, cache_miss_ratio, and ready_to_run_sql.
Example: recommend_prewarm_targets(shared_buffers_budget_pct=60.0, limit=10)
Input Schema
| Name | Required | Description | Default |
|---|---|---|---|
| limit | No | ||
| database | No | Optional: target a configured secondary (read-only) database by name; omit for the primary. Call list_databases to see the configured ids. | |
| prewarm_mode | No | buffer | |
| min_heap_blks_read | No | ||
| shared_buffers_budget_pct | No |
Output Schema
| Name | Required | Description | Default |
|---|---|---|---|
| candidates | Yes | ||
| budget_blocks | Yes | ||
| total_cost_blocks | Yes | ||
| shared_buffers_blocks | Yes |