pg_stash_advice
pg_stash_advice : Store and automatically apply plan advice by query ID
Overview
| ID | Extension | Package | Version | Category | License | Language |
|---|---|---|---|---|---|---|
| 2989 | pg_stash_advice | pg_stash_advice | 1.0 |
FEAT | PostgreSQL | C |
| Attribute | Has Binary | Has Library | Need Load | Has DDL | Relocatable | Trusted |
|---|---|---|---|---|---|---|
| --sLd-r | No | Yes | Yes | Yes | yes | no |
| Relationships | |
|---|---|
| Requires | pg_plan_advice |
| See Also | pg_plan_advice pg_hint_plan plan_filter pg_stat_plans |
Packages
| PG19 | PG18 | PG17 | PG16 | PG15 |
|---|---|---|---|---|
| 1.0 | N/A | N/A | N/A | N/A |
Install
Config this extension to shared_preload_libraries:
Create this extension with:
Usage
Sources:
pg_stash_advice stores plan-advice strings in named, dynamically allocated shared-memory stashes keyed by PostgreSQL query ID. A session selects a stash with pg_stash_advice.stash_name; when a matching query is planned, the saved advice is applied automatically.
Create and Select a Stash
Preloading is the normal way to make advice application and automatic persistence available in every session.
After restarting, obtain the query ID with EXPLAIN (VERBOSE) and the advice string with EXPLAIN (PLAN_ADVICE), then store them:
Inspection functions are pg_get_advice_stashes() and pg_get_advice_stash_contents(text). pg_drop_advice_stash(text) removes a stash. The install script revokes public execution of all management and inspection functions.
Caveats
- This extension is included in PostgreSQL 19 with SQL version
1.0. It is not supplied by PostgreSQL 18 or earlier. - Stashes consume dynamic shared memory. Applying advice adds planning overhead and can prevent the planner from adapting as data distribution changes, so constrain both stash size and scope.
- Persistence is enabled by default and writes
pg_stash_advice.tsvin the data directory through a background worker. Automatic worker startup requires preloading;pg_start_stash_advice_worker()can start persistence manually but does not load an existing file. - The security model is intentionally limited. Although management functions are not public by default, any user may select a known stash name, which may reveal its advice strings. Do not store secrets in plan advice.
- Query IDs require
compute_query_id; loading the module enables computation when that setting isauto. Advice tied to obsolete query IDs must be maintained as queries and schemas change.
The reviewed release is PostgreSQL 19.0. Load pg_plan_advice as well as pg_stash_advice: the former implements plan advice, while the latter stores and selects it. A nonempty pg_stash_advice.stash_name selects the stash to apply.